Wheel & Spoke Sales Performance Analysis and Customer Segmentation Using Advance SQL Techniques
Project Background:
The Wheel & Spoke aims to gain deeper insights into its sales performance and customer behavior to drive strategic business decisions. This project focuses on analyzing sales data to understand key metrics related to both customers and products. By segmenting customers and products based on their performance, the company can identify high-value segments, optimize marketing efforts, and improve overall profitability.
Project Objectives:
-
Develop comprehensive reports to track key customer and product metrics.
-
Segment customers based on their purchasing behavior and demographics.
-
Segment products based on their revenue performance.
-
Calculate valuable KPIs to measure customer and product performance.
-
Provide actionable insights to support strategic decision-making.
Data Sources:
gold.fact_sales: Contains transactional data, including order details, sales amounts, and quantities.
gold.dim_customers: Contains customer demographic information, such as names, ages, and customer numbers.
gold.dim_products: Contains product information, such as names, categories, and costs.
SQL Scripts and Documentation:
1. Customer Report (gold.report_customers)
This report view consolidates key customer metrics and behaviors to provide a comprehensive view of
customer performance.
Highlights: Gathers essential customer information (names, ages, transaction details).
Segments customers into categories (VIP, Regular, New)
Aggregates customer-level metrics:
-
Total orders
-
Total sales
-
Total quantity purchased
-
Total products purchased
-
Lifespan (in months)
Calculates valuable KPIs:
-
Recency (months since last order)
-
Average order value (AOV)
-
Average monthly spend
Implementation:
/* Insert SQL Script here*/
Business Questions Answered:
-
Who are our VIP customers?
-
What are the purchasing habits of different age groups?
-
What is the average monthly spend of our customers?
-
How recently have customers made purchases?
2. Product Report (gold.report_products)
This report view consolidates key product metrics and behaviors to provide a comprehensive view of product performance.
Highlights:Gathers essential product information (name, category, subcategory, cost).
Segments products by revenue (High-Performers, Mid-Range, Low-Performers).
Aggregates product-level metrics:
-
Total orders
-
Total sales
-
Total quantity sold
-
Total customers (unique)
-
Lifespan (in months)
Calculates valuable KPIs:
-
Recency (months since last sale)
-
Average order revenue (AOR)
-
Average monthly revenue
Implementation:
/* Insert SQL Script here*/
Business Questions Answered:
-
Which products are our top performers?
-
What is the average order revenue for each product?
-
How frequently are our products being sold?
-
How many unique customers have purchased each product?
Expected Outcomes:
-
Improved understanding of customer and product performance.
-
Data-driven insights to support marketing and sales strategies.
-
Identification of high-value customer and product segments.
-
Enhanced ability to track and measure key business metrics.