E-Commerce Database Analysis

I worked as an eCommerce Database Analyst for Maven Fuzzy Factory, a new online retailer that had just launched its first product. I collaborated with the CEO, Marketing Manager, and Website Manager to analyze and optimize marketing channels, measure and test website conversion performance, and explore the product portfolio to understand the impact of new product launches to help the business make better decisions.

I worked with?six related tables?contains the essential key information (website activity, products, orders, and refunds) that an e-commerce database.

Tools:?MySQL workbench & Tableau.

Before jumping into the project, I'd brief some marketing concepts: -

UTM?parameters: These are small pieces of code added to the end of a URL used to track the source of the traffic. By tracking them, businesses can see which of their paid marketing campaigns are most effective. They can also see how different traffic sources and campaigns are performing in terms of website activity and conversions.

Bounce rate is the percentage of a web page's total visits, with the visitor leaving, or clicking the back button without taking an action. It's a valuable metric for marketers to assess the effectiveness of their web pages and make improvements to enhance user experience and encourage deeper engagement.

Conversion Funnel is a visual representation of the steps a potential customer takes to complete a desired action, such as making a purchase or signing up for a service.

In this article, I will talk about the following topics of E-commerce data analysis: -

1. Traffic source analysis
2. Website performance and analysis
3. Analysis for channel portfolio
4. Analyzing business patterns and seasonality
5. Product-level analysis
6. User-level analysis

Now let’s discuss each topic in detail: -

• Traffic source analysis

It's crucial to understand where your customers coming from, and which marketing channels are driving the highest quality traffic. Also find the conversion rate for each marketing campaign. It helps us shift our budget towards high-quality traffic and making informed decision making. Analyzing user behavior and patterns in different traffic sources to generate strategy. Comparing the most to least effective traffic to identify and eliminate wasteful traffic sources and scale the most effective source. View code and results click here.

• Website performance and analysis

There are various ways of analyzing website performance.

1. Analyzing top website

Website content analysis is having knowledge of pages that are seen the most by users. It involves recognizing the most common entry pages to your business (the first thing a user sees) and know how they perform for your business objectives.

2. Analyzing the conversion funnels

Understanding each step of your user’s experience on their journey towards buying products. Identifying the most common paths users take before purchasing products and analyze how many users continue on each step in your conversion flow and how many users abandon at each step.

View code and results click here.

• Analysis for channel portfolio

Identifying which marketing channels are driving the most sessions and orders through the website. Understanding the difference in user characteristics and conversion performance across marketing channels. Analyzing our brand or direct traffic to see how well brand is doing with consumer and how brand drives a business. Optimizing bids and allocating marketing spend across multi-channel portfolios to achieve maximum performance. View code and results click here.

• Analyzing business patterns and seasonality

Business trends help in generating insights to help you maximize efficiency and predict future trends. Daily analysis to identify most busy time periods to make strategic business decisions. Analyzing seasonality to better understand the pattern, spikes, or slowdowns in demand. View code and results click here.

• Product-level analysis

Analyzing product sales to understand how each product contributes to business, and how product launches impact the overall portfolio. Analyze products page pathing to learn how customers interact with each of products, and how each product converts customers. Find a cross-sell, it is about knowing which products users are likely to purchase together and provide smart product recommendations. Analyzing product refund rates for controlling quality and understand where you might have problems to address. View code and results click here.

• User-level analysis

User analysis helps us to understand user behavior and identify some of our most valuable customers. It includes analyzing repeat behavior activity and which channel they use when they come back, to show if it's all in direct type-in or if we're paying for those customers with paid search ads multiple times. View code and results click here.

A story about a data-driven company that has been producing rapid growth.

Before visualizing, visit Tableau to display the original and apply filters.

Displaying the original in Tableau Click here.

Displaying the original in Tableau Click here.

Displaying the original in Tableau Click here.

Displaying the original in Tableau Click here.

Displaying the original in Tableau Click here.

Conclusion

Seeing the business change and grow based on my analyses has given me a clear understanding of the value that analysts can bring to a company. I’ve also improved my SQL data programming skills and developed a good understanding of how eCommerce businesses work.