📊 Retail Sales Analytics Dashboard

An end-to-end retail sales analysis project — from raw transactional data to a business-ready dashboard and actionable insights. Built to demonstrate the full analytics workflow: data modeling → SQL analysis → Python EDA → visualization → recommendations.

---

📌 Overview

A retail business selling across 4 regions and 5 product categories needed a single view of performance to replace slow, manual spreadsheet reporting. This project analyzes 10,000 orders from FY2023–2024 to answer the core questions leadership cares about — where revenue and profit come from, how they're trending, and what's quietly eroding margin — and presents the findings in a clean executive dashboard.

🖥️ Dashboard

Metric	Value
Total Revenue	₹7.71M
Total Profit	₹2.32M
Total Orders	10,000
Avg Order Value	₹771
Profit Margin	30.1%
YoY Growth (2024 vs 2023)	+18.2%

🎯 Business Questions Answered

1. How is revenue trending month-over-month, and is the business growing year-over-year?
2. Which regions and product categories drive the most revenue?
3. Where is the business actually profitable — and where is margin thin?
4. How are discounts affecting profitability?
5. How concentrated is revenue among top customers and segments?

🛠️ Tools & Techniques

• SQL — KPIs, month-over-month growth (LAG), revenue share (SUM() OVER()), product ranking (RANK), customer concentration (running totals)
• Python (pandas) — data loading, aggregation, profitability and discount-band analysis
• Matplotlib — dashboard-quality charts with a consistent visual style
• Data modeling — clean star-schema-ready transaction table

---

💡 Key Insights

1. Steady growth with strong seasonality. Revenue grew +18.2% year-over-year (₹3.53M → ₹4.18M). Demand peaks every October–December (festive season) and shows a smaller March uplift — clear signals for inventory and staffing planning.

2. The West region leads, but revenue is well-distributed. West is the top market at 31.5% of revenue, followed by North, South, and East. No single region dominates, which lowers concentration risk.

3. Electronics drives revenue — but it's not the most profitable. Electronics generates the highest revenue, yet Clothing delivers the best profit margin (51%) while Groceries is the thinnest at just 13%. Chasing top-line revenue alone would hide where the real profit is.

4. ⚠️ Deep discounts destroy profit. This is the most important finding. Average profit per order collapses as discounts rise — from ₹270 at 0–10% discount down to just ₹7 at 30%+. Around 0.9% of orders are sold at a loss, almost all at an average discount of ~24%. Recommendation: cap discounts on low-margin categories and review the 30%+ discount band.

5. Consumer segment dominates; revenue isn't over-concentrated. The Consumer segment drives 61% of sales (Corporate 25%, Small Business 14%), and the top 10% of customers account for ~19% of revenue — a healthy, non-fragile base.

6. Wireless Headphones is the single best-selling product (₹762K), with electronics products filling most of the top 10.

---

🗂️ Dataset

A synthetically generated dataset (10,000 orders) modeled with realistic patterns — seasonality, YoY growth, and category-level margins. No real or personal data is used. See data/data_dictionary.md for the full column reference.

📁 Repository Structure

retail-sales-analytics/
├── data/
│   ├── retail_sales_data.csv      # the dataset (10,000 orders)
│   └── data_dictionary.md         # column definitions
├── scripts/
│   ├── generate_data.py           # reproducibly generates the dataset
│   └── analysis.py                # EDA + chart generation
├── sql/
│   └── business_analysis.sql      # 8 business questions answered in SQL
├── images/                        # dashboard + individual charts
├── requirements.txt
└── README.md

🚀 How to Run

# 1. Install dependencies
pip install -r requirements.txt

# 2. (Optional) regenerate the dataset
python scripts/generate_data.py

# 3. Run the analysis and build all charts
python scripts/analysis.py

The SQL in sql/business_analysis.sql can be run against any database after loading retail_sales_data.csv into a sales table.

---

📈 Extending to Power BI

This project is built to drop straight into Power BI:

1. Load retail_sales_data.csv via Get Data → Text/CSV.
2. Add a Date table and mark it as the date table for time-intelligence.
3. Recreate the KPIs as DAX measures, for example:

Total Revenue = SUM ( sales[sales] )

Total Profit  = SUM ( sales[profit] )

Profit Margin % = DIVIDE ( [Total Profit], [Total Revenue] )

Revenue YoY % =
VAR CurrYear = [Total Revenue]
VAR PrevYear = CALCULATE ( [Total Revenue], DATEADD ( 'Date'[Date], -1, YEAR ) )
RETURN DIVIDE ( CurrYear - PrevYear, PrevYear )

4. Rebuild the visuals (trend line, region/category bars, segment donut, top-products bar) to get an interactive version of the dashboard above.

---

📫 Contact

Monil Soni — Power BI Developer | BI & Analytics LinkedIn · sonimonil247@gmail.com