📊 Ecommerce Analytics Platform

An end-to-end data analytics project demonstrating ETL pipeline design, data warehousing, and business intelligence using PostgreSQL and Power BI.

---

🚀 Project Overview

This project processes raw ecommerce datasets, transforms them into a structured analytical model, and delivers actionable business insights through an interactive Power BI dashboard.

It simulates a real-world data workflow — from raw data ingestion to business decision-making.

---

🧱 Tech Stack

• Python (Pandas, SQLAlchemy)
• PostgreSQL (Data Warehouse)
• SQL (Transformations)
• Power BI (Visualization & Insights)

---

🔄 ETL Pipeline

1. Extract

• Raw datasets loaded from CSV files:
  • Customers
  • Orders
  • Payments
  • Products
  • Order Items

---

2. Transform

• Data cleaning & preprocessing:
  • Handling missing values
  • Data type corrections
  • Removing inconsistencies
• Business logic applied:
  • Revenue calculation
  • Order aggregation
  • Customer mapping

---

3. Load

• Data loaded into PostgreSQL:
  • Staging Tables (stg_*)
  • Warehouse Tables

---

🏗️ Data Model (Star Schema)

Fact Table:

• fact_sales

Dimension Tables:

• dim_customers
• dim_products
• dim_date

This structure enables efficient analytical queries and scalable reporting.

---

📊 Key Metrics

• Total Revenue
• Total Orders
• Average Order Value (AOV)
• Shipping Revenue

---

📸 Dashboard Visualizations & Insights

---

📈 Total Revenue

• Revenue shows a strong upward trend, indicating consistent business growth.
• A peak suggests a possible seasonal or promotional impact.
• A post-peak drop may indicate demand normalization or data irregularities.

---

📦 Total Orders

• Orders increase significantly over time, reflecting strong customer acquisition.
• Growth aligns with revenue, confirming volume-driven expansion.
• Indicates increasing platform usage and transaction frequency.

---

📊 Orders Trend Over Time

• Rapid growth observed during early business phase (2016–2017).
• Sharp spike likely due to high-demand events or campaigns.
• Stabilization indicates transition to a mature business stage.

---

💰 Average Order Value (AOV)

• AOV remains stable (~150–180), showing consistent pricing strategy.
• A sharp drop in early 2017 indicates a potential data anomaly.
• Stability confirms revenue growth is not driven by increased spending per order.

---

🛍️ Revenue by Product Category

• A few product categories dominate overall revenue.
• Indicates dependency on high-performing categories.
• Lower-performing categories present optimization opportunities.

---

📈 Overall Insights

• 📈 Revenue growth is primarily driven by increase in order volume
• ➖ AOV remains stable → consistent pricing strategy
• 📦 Seasonal spikes suggest event-driven demand
• 🛍️ Revenue concentration highlights key product categories
• ⚠️ Data anomaly detected in AOV → potential data quality issue

---

🧠 Business Conclusion

The business is scaling through customer acquisition and transaction growth, rather than increasing customer spend.

🎯 Strategic Opportunities:

• Increase AOV via bundling and upselling
• Expand high-performing product categories
• Improve data validation processes

---

▶️ How to Run

pip install -r requirements.txt
python scripts/load.py