[SQL] Explore Bicycle Manufacturer Dataset

1. Introduction

Conducted an in-depth analysis of the Bicycle Manufacturer dataset using SQL on Google BigQuery to uncover insights that guide business strategies.

2. Dataset access

The eCommerce dataset is stored in a public Google BigQuery dataset. To access the dataset, follow these steps:

• Log in to your Google Cloud Platform account and create a new project.
• Navigate to the BigQuery console and select your newly created project.
• In the navigation panel, select "Add Data" and then "Start a project by name".
• Enter the project ID "adventureworks2019" and click "Enter".
• Click on the "adventureworks2019" workspace to open it.

3. Exploring the Dataset

In this project, I will write 08 query in Bigquery base on Google Analytics dataset

Query 01: Calculate Quantity of items, Sales value & Order quantity by each Subcategory in last 12M

• SQL code

• Query results

Query 02: Calculate % YoY growth rate by SubCategory & release top 3 category with highest grow rate.

• SQL code

• Query results

Query 3: Ranking Top 3 TeritoryID with biggest Order quantity of every year. If there's TerritoryID with same quantity in a year, do not skip the rank number

• SQL code

• Query results

Query 04: Calculate Total Discount Cost belongs to Seasonal Discount for each SubCategory

• SQL code

• Query results

Query 5: Retention rate of Customer in 2014 with status of Successfully Shipped (Cohort Analysis)

• SQL code

• Query results

Query 6: Trend of Stock level & MoM diff % by all product in 2011. If %gr rate is null then 0. Round to 1 decimal

• SQL code

• Query results

Query 7: Calculate Ratio of Stock / Sales in 2011 by product name, by month. Order results by month desc, ratio desc. Round Ratio to 1 decimal mom yoy

• SQL code

• Query results

Query 8: No of order and value at Pending status in 2014

• SQL code

• Query results

4. Insights

• The top 3 categories with the highest growth rates are Mountain Frames, Socks, and Road Frames.
• The top 3 Territory IDs with the largest order quantities from 2011 to 2024 are 4, 6, and 1, suggesting strong market demand and effective sales strategies in these areas.
• Customers tend to return for purchases at a rate of 10% three months after their first purchase. However, this retention rate remains low, with subsequent months showing only about 3-4%.
• In 2014, there were a total of 224 orders in "Pending" status, with an order value of approximately 387,359.01. The number of orders in "Pending" status indicates that there may be delays in the order processing workflow. This could impact revenue if not addressed promptly.

5. Recommendations

• Increase marketing efforts for Mountain Frames, Socks, and Road Frames. Consider targeted campaigns highlighting their unique features and benefits to capture more market share.
• Develop region-specific marketing and sales strategies for Territories 4, 6, and 1. Utilize data analytics to understand customer preferences in these areas and tailor promotions accordingly. Ensure that inventory levels are well-managed in these territories to meet demand and avoid stockouts, enhancing customer satisfaction.
• Implement a customer loyalty program to incentivize repeat purchases. Offer rewards, discounts, or exclusive access to new products to encourage customers to return.
• Analyze the order processing workflow to identify bottlenecks that contribute to orders being in "Pending" status. Implement process improvements or automation to enhance efficiency.
• Continuously monitor sales data, customer feedback, and order processing metrics to assess the effectiveness of implemented strategies. Be prepared to adapt based on what the data reveals.