top of page
  • Writer's pictureJiasheng Qu

From Clicks to Digital Cart: Exploring E-commerce Activities

Introduction


In this project, I serve as the Data Analyst for a startup named Maven Fuzzy Factory, an online toy retailer. Collaborating closely with the CEO, Head of Marketing, and Website Manager, I aim to gain data-driven insights to guide the company's strategic decisions. I strongly believe that data analysis projects should address business challenges and be applicable in real-world settings. Therefore, the business questions tackled in this project are inspired by both ChatGPT and Maven Analytics. I've tailored these questions to not only demonstrate my proficiency in SQL but also to suit the specific dataset at hand.


Through the completion of these tasks, my objective is to delve deeply into the database, understand the relationships between tables, identify patterns, and draw meaningful conclusions. The objective is to analyze and optimize the business's marketing channels, website activities, and product portfolio.


Database Schema and Data Description


The dataset was sourced from an online learning platform called Maven Analytics. You can download the complete schema code at this link.

Database Name: mavenfuzzyfactory

Tables and Columns

1. website_sessions

  • website_session_id: (PRIMARY KEY) Unique session identifier

  • created_at: Timestamp of session creation

  • user_id: User ID associated with the session

  • is_repeat_session: Indicates if it's a repeat session (0 or 1)

  • utm_source: Source of user arrival

  • utm_campaign: Marketing campaign

  • utm_content: Specific campaign content

  • device_type: Type of device used

  • http_referer: Referring website URL

2. website_pageviews

  • website_pageview_id: (PRIMARY KEY) Unique page view identifier

  • created_at: Timestamp of page view

  • website_session_id: Session ID associated with the page view

  • pageview_url: URL of the viewed page

3. products

  • product_id: (PRIMARY KEY) Unique product identifier

  • created_at: Timestamp

  • product_name: Product name

4. orders

  • order_id: (PRIMARY KEY) Unique order identifier

  • created_at: Order creation timestamp

  • website_session_id: Session ID associated with the order

  • user_id: User ID who placed the order

  • primary_product_id: Identifier of the primary product

  • items_purchased: Number of items in the order

  • price_usd: Total order price (USD)

  • cogs_usd: Cost of goods sold (COGS) for the order (USD)

5. order_items

  • order_item_id: (PRIMARY KEY) Unique item identifier

  • created_at: Item creation timestamp

  • order_id: Order identifier

  • product_id: Product identifier

  • is_primary_item: Indicates if it's the primary item (1 or 0)

  • price_usd: Item price (USD)

  • cogs_usd: Cost of goods sold (COGS) for the item (USD)

6. order_item_refunds

  • order_item_refund_id: (PRIMARY KEY) Unique refund identifier

  • created_at: Refund creation timestamp

  • order_item_id: Identifier of the refunded item

  • order_id: Identifier of the associated order

  • refund_amount_usd: Refunded amount (USD)

 

Business Questions

  1. Can you provide overall session and order volume, trended by year and quarter for the life of the business?

  2. Can you provide quarterly figures since we launched for session-to-order conversion rate, revenue per order, and revenue per session?

  3. Can you provide a monthly breakdown of revenue and margin for each product?

  4. Can you provide the monthly order counts and refund rates for each product?

What I Learned

  1. Gained a deeper understanding of relational databases and the importance of primary and foreign keys in establishing connections between tables.

  2. The company shows consistent growth from 2012 to 2014, both in terms of website sessions and order volume. The fourth quarter (Q4) of every year demonstrates a marked increase in both metrics, potentially due to holiday season shopping or special promotions.

  3. The Session-to-Order Conversion Rate has been on the rise, indicating an increasingly effective website in converting visitors to customers.

  4. The monthly breakdown of revenue and margin for each product showed the introduction of new products over time, contributing significantly to overall growth. Seasonal spikes in November and December indicate potential holiday season promotions or sales.

  5. While the volume of orders for products has been consistent, the refund rates vary. Notably, Product 1 had a few months with higher refund rates. Products 2 and 4 maintain a low refund rate, pointing to higher customer satisfaction.

The Analysis


Task 1: Can you provide overall session and order volume, trended by year and quarter for the life of the business?
Insights:

The business has been growing steadily from 2012 to 2014. We can notice this both from the number of sessions and the number of orders. Q4 (4th Quarter) consistently sees a significant jump in sessions and orders every year. This could be due to seasonal factors like holiday shopping. I also want to see the Year-over-Year (YoY) growth rate, so I adjusted the code. By looking at the YoY growth rate, I want to understand how the business is performing compared to the same time frame in the previous year.


Insights:

The YoY (Year-over-Year) growth rates indicate a rapidly growing business during its early stages, particularly in 2013 and 2014. Although there was a noticeable slowdown in Q1 2015, the company remains on an upward growth trajectory. It would be beneficial to analyze various factors to better understand the reasons behind the growth.

 
Task 2: Can you provide quarterly figures since we launched, for session-to-order conversion rate, revenue per order, and revenue per session?

Formula:

Session-to-Order Conversion Rate= (Number of Orders/ Number of Sessions)×100

Revenue Per Order= Total Revenue/Number of Orders

Revenue Per Session = Total Revenue/Number of Sessions


Insights:

The Session-to-Order Conversion Rate measures the percentage of website sessions that result in a purchase. It provides insights into the effectiveness of the website in converting visitors into customers. The year 2014 seems to have been the most successful year in terms of growth, with a noticeable bump in all metrics. While it's evident that the business has been on an upward trajectory, it's essential to keep an eye on the slightly decreased growth in revenue per order in Q1 2015.

 
Task 3: Can you provide a monthly breakdown of revenue and margin for each product?

First, I tried to retrieve the list of products and found that we have four products.

Based on the list, I wrote a SQL query for the task.


Insights:

There are multiple NULL values for product revenue and margins in the early stages because these products were not yet available for sale. The total revenue has been showing an upward trend year over year, reached a peak of $144,823.02 in December 2014. By looking at the trends in revenue and margin over the years, it's evident that new products were introduced to the portfolio, significantly contributing to this growth. Notably, there are seasonal spikes in sales, particularly in November and December. This suggests potential increases in sales during the holiday season or possibly due to special promotional activities.

 
Task 4: Can you provide the monthly order counts and refund rates for each product?


Insights:

A rising refund rate might indicate issues with product quality or customer satisfaction. The refund rate for Product 1 varies, but it generally stays below 10%. However, in certain months, like August and September 2014, there's a significant spike in the refund rate. This might point to potential challenges with the product or its delivery during those times. In contrast, Product 2's refund rate mostly stays below 5%, this indicates that customers are generally satisfied with this product. Product 3 has a refund rate that fluctuates around 5% to 7%. Product 4 has a very low refund rate, mostly under 2%. This indicate a high level of customer satisfaction with this product.

 

Conclusion


In this project, I mainly utilized SQL to analyze sales trends, profit margins, refund rates, and other relevant metrics. The data reveals that the company has experienced substantial growth since its inception. In addition to this project, I plan to use PowerBI to visualize the findings and compile them into a dashboard.


Further areas of study could include evaluating the effectiveness of marketing campaigns, referral URLs, UTM sources, and content in terms of sales generation. I am also interested in exploring which products are frequently purchased together and studying user behavior across diverse device types. By delving into these areas, the company can gain a deeper understanding of its operations, customer behavior, and market trends. The insights will guide strategic decisions, optimize marketing efforts, and enhance overall business performance.


Thank you for taking the time to read my project! If you have any feedback or suggestions, please don't hesitate to reach out to me. You can contact me at jiashengqu@outlook.com, or if you'd like to connect on LinkedIn, please feel free to send me a connection request.

Comments


bottom of page