📊 Retail Data Pipeline & Analytics

📝 About The Project

This project is an end-to-end data analytics solution that automates the flow of e-commerce retail order data from raw ingestion to database reporting.

Using Python, Pandas, SQL Server, and the Kaggle API, the project downloads raw retail data, cleans and standardizes missing/dirty records, engineers key business metrics (like net profit and sale price), and loads the structured data into MS SQL Server for analytical querying.

---

🛠️ Pipeline Overview

[1. Kaggle API] ──> [2. Python / Pandas] ──> [3. MS SQL Server] ──> [4. SQL Analytics]

1. Extraction (Kaggle API): Programmatically downloads the compressed retail-orders dataset directly from Kaggle.
2. Transformation (Pandas):
   • Unzips and parses CSV data.
   • Normalizes column names into snake_case.
   • Replaces inconsistent missing values ('Not Available', 'Unknown') with standard NULLs.
   • Engineers derived columns: discount, sale_price, and profit.
3. Loading (SQLAlchemy & SQL Server): Creates an optimized SQL table schema and bulk loads the transformed data into MS SQL Server.
4. Analytics (SQL): Runs analytical queries using CTEs, Window Functions, and Pivoting to extract actionable business insights (e.g., top-performing products, regional sales breakdown, and YoY growth).

Loading (SQLAlchemy & SQL Server): Creates an optimized SQL table schema and bulk loads the transformed data into MS SQL Server.

Analytics (SQL): Runs analytical queries using CTEs, Window Functions, and Pivoting to extract actionable business insights (e.g., top-performing products, regional sales breakdown, and YoY growth).