4. The Look E-Commerce
Background
The Look is a fictitious e-Commerce clothing site developed by the Looker team. The dataset contains information about customers, products, orders, logistics, web events and digital marketing campaigns.
Problem Definition
As a data analyst in this company, my task is to provide users with an overall picture of the current business performance. Additionally, due to the potential crisis in 2023, the company is in optimization mode. The management has decided to cut resources in categories with the lowest growth in the past 1 year. On the other hand, they also want to continue the analysis by understanding user retention behaviors and finding ways to increase the retention rate.
Objective
Provide queries to find the best-selling category, the most shrinking category, and the top 3 user's first traffic sources that generated the most revenue in the past 3 months
Provide queries to retrieve revenue and profit growth over the past year and give recommendations on which category needs to be deprioritized
Provide queries to create a monthly retention cohort and determine the percentage of users who return in the following month after their initial purchase in 2022
Skill Set
Basic SQL: basic functions and operators
Intermediate SQL:
Aggregate Function
Date Function
SQL Data Cleaning
JOIN
Advanced SQL:
CTE (Common Table Expression)
SQL Window Functions
Result
The category with the highest total sales in December 2022, filtered by the status 'shipped', is Outerwear & Coats
Shorts is the category that faced the lowest percentage (most shrinking) in terms of order numbers with the status 'shipped' in December 2022 compared to the previous month
The top 3 user’s first traffic sources that provide the most revenue in the past 3 months are Email, Adwords and YouTube
Based on revenue and profit growth in the past year, the company needs to limit the production quantity of three product categories: Leggings, Active, and Jumpsuits & Rompers
Users who made their first purchase in January have a lower retention rate compared to other user categories. To address this issue, it is recommended to review the ratings provided by customers who purchased the product for the first time in January to determine their level of satisfaction
Another recommendation to increase the retention rate is to offer personalized communication based on their preferences, purchase history, and behavior. Provide exclusive discounts, loyalty rewards, or free shipping to customers who make repeat purchases
