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
Can you provide overall session and order volume, trended by year and quarter for the life of the business?
Can you provide quarterly figures since we launched for session-to-order conversion rate, revenue per order, and revenue per session?
Can you provide a monthly breakdown of revenue and margin for each product?
Can you provide the monthly order counts and refund rates for each product?
What I Learned
Gained a deeper understanding of relational databases and the importance of primary and foreign keys in establishing connections between tables.
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.
The Session-to-Order Conversion Rate has been on the rise, indicating an increasingly effective website in converting visitors to customers.
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.
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.