Regional Sales Analysis — End-to-End (Python + Power BI)

An end-to-end retail sales analytics project that takes a messy, multi-sheet Excel workbook → cleans & models the data in Python → performs exploratory data analysis → and surfaces business insights in a 3-page interactive Power BI dashboard.

This project is designed to reflect real-world retail & commercial analytics workflows used by sales, finance, and operations teams.

---

🔍 Why This Project Matters

Retail leadership asks practical questions every week:

• Which products and channels drive revenue and margin?
• Which regions are over or under-performing?
• Which customers and states deserve commercial focus?

This project demonstrates the full analytics lifecycle required to answer those questions: data modeling, feature engineering, EDA, and executive dashboarding.

It is built to show both:

• Technical ability (Python, data modeling, Power BI)
• Commercial thinking (sales performance, profitability, channel mix)

---

🗂️ Data Sources

The project was built from a multi-sheet Excel workbook containing:

• Sales Orders – order details (date, product, customer, quantity, price, channel)
• Customers – customer master data (location, demographics)
• Products – product & category master
• Regions / State Regions – geographic hierarchy
• 2017 Budget – product-level sales budget for variance analysis

---

🧠 Key Analytics Steps

1. Data Modeling & ER Design

• Identified PK/FK relationships between sales, customers, products, and regions
• Designed a star-style reporting model

2. Data Ingestion & Cleaning (Python / pandas)

• Fixed malformed headers
• Normalized column names
• Enforced correct data types
• Removed duplicates and irrelevant fields

3. Data Joins & Model Build

• Sales joined to customers, products, regions, and budgets
• Removed redundant columns after joins

4. Feature Engineering (Retail KPIs)

Created the following metrics:

• Revenue
• Total Cost
• Gross Profit
• Profit Margin %
• Average Order Value (AOV)
• Monthly & yearly time buckets
• Channel classification

5. Exploratory Data Analysis (EDA)

• Monthly revenue & profit trends
• Channel mix distribution
• Product-level profitability analysis
• AOV distribution and outlier detection

6. Dashboarding (Power BI)

3-page report:

1. Executive Overview

   • Revenue, Profit, Margin, AOV trends

2. Product & Channel Performance

   • Best & worst products
   • Channel contribution analysis

3. Customer & Region Analysis

   • Sales by state and region
   • Top & bottom customers

Includes slicers for:

• Year
• Channel
• Region
• Product

---

📊 Example Insights Generated

• Clear seasonality pattern in revenue
• Wholesale dominates revenue, but some smaller channels deliver higher margins
• A small number of SKUs contribute to a large portion of revenue
• AOV is right-skewed → opportunity for basket-size optimization via promotions

---

✅ What This Project Demonstrates

Real-world retail data modeling

Python-based data preparation

Profit & margin analytics

Channel and customer performance analysis

Executive-level Power BI dashboard design

Commercial decision support reporting

---

🚀 Suggested Production Enhancements

Automate ingestion with ADF or Python scheduling

Add forecasting & anomaly detection

Implement data validation tests

Centralize KPIs in a semantic layer

Parameterize Power BI data sources

---

⚠️ Assumptions & Limitations

Budget table applies only to 2017 data

File-based data source used (not live database)

Dataset is for demonstration purposes