🍬 Candy Sales Analytics Project

📊 Project Overview

This project explores sales performance, customer behavior, shipping efficiency, and product profitability within a fictional candy company. Using SQL, I analyzed key metrics across multiple tables (candy_sales, candy_products, candy_factories, candy_targets, uszips) to uncover strategic business insights.

---

📊 Project Summary

✅ Dataset Snapshot

Metric	Value
Factories	5
Products	15
Sales Records	9,994
Date Range	2021-01-03 → 2024-12-30
Null Order IDs	0
Null Customer IDs	0
Zero Unit Sales (%)	0.00%

---

💰 Top Revenue Products

Product Name	Total Revenue
Wonka Bar - Triple Dazzle Caramel	$142,357.50
Wonka Bar - Scrumdiddlyumptious	$137,509.20
Wonka Bar - Milk Chocolate	$135,820.75
Wonka Bar - Fudge Mallows	$124,459.20
Wonka Bar - Nutty Crunch Surprise	$116,007.60

---

🗺️ Top Performing States

State	Total Sales
California	$144,367.08
New York	$79,313.65
Texas	$68,054.61
Pennsylvania	$40,822.09
Ohio	$35,125.41

---

👥 Customer Segmentation by Spend Profile

Customer Segment	Number of Customers	Total Segment Sales	Total Segment Gross Profit	Avg Segment Profit Margin (%)
Medium Spender	1,316	$44,125.03	$29,345.14	66.50
Small Spender	2,858	$37,484.95	$24,949.60	66.56
Large Spender	561	$30,485.82	$20,178.67	66.19
Very Large Spender	274	$26,734.54	$17,034.82	63.72

---

🏙️ Largest Market Cities

City	State	Population
Houston	Texas	2,973,937
Brooklyn	New York	2,679,609
Chicago	Illinois	2,551,785
Los Angeles	California	2,155,221
San Antonio	Texas	1,785,699

🚚 Shipping Efficiency & Profitability

Shipping Mode	Avg Days to Ship	Gross Margin (%)
Same Day	0.04	13.73
First Class	2.18	13.24
Second Class	3.24	12.54
Standard Class	5.01	12.85

Note: Shipping durations were normalized due to a data inconsistency where DATEDIFF() returned values inflated by ~2000 days. This was adjusted by subtracting 2000 in final calculations.

---

📈 Monthly Revenue Trend

A steady growth pattern in monthly sales was observed, peaking in November and December 2024.

Highest Month: November 2024 → $36,634.88
Lowest Month: February 2021 → $2,557.89

---

📂 Analysis Files

1. 01_data_exploration_cleaning.sql - Data validation and preparation
2. 02_basic_sales_analysis.sql - Core revenue metrics and trends
3. 03_product_analysis.sql - SKU performance and inventory insights
4. 04_customer_analysis.sql - Buyer segmentation and behavior
5. 05_regional_shipping_analysis.sql - Geographic trends and logistics
6. 06_market_expansion_analysis.sql - Growth opportunities
7. 07_division_target_analysis.sql - Performance vs business goals
8. 08_business_insights_metrics.sql - Executive-level KPIs

🧠 Key SQL Techniques Used

• Subqueries & CTEs
• Window Functions (ROW_NUMBER(), RANK())
• Joins & Aggregations
• Data Cleaning & Column Renaming
• Time-based and Geospatial Analysis
• Business KPIs: AOV, margin %, loyalty, sales per capita, repeat purchases

---

📂 File Structure

candy_sales/
├── sql_queries/        # Analysis scripts
├── images/            # Charts and graphs
├── csv/              # Csv files for analysis
└── README.md