---

🛒 E-commerce Product Data Aggregator & Analyzer

---

📚 Table of Contents

• Overview
• Key Capabilities
• System Architecture
• Technologies Used
• Project Structure
• Prerequisites
• Installation & Setup
• Database Setup
• Database Configuration
• Data Integrity Requirements
• Running the Pipeline
• Dashboard Preview
• Exploring the Dashboard
• Stopping Services
• Learning Outcomes

---

Overview

A full-stack data engineering & analytics pipeline that ingests raw e-commerce product and order data, stores it in a MySQL database, exposes data through a Flask REST API, and visualizes insights using an interactive Streamlit dashboard.

This project demonstrates end-to-end data flow — from ingestion → storage → API → analytics — using production-style Python tools.

---

📌 Key Capabilities

• Multi-Source Product Ingestion

  • Aggregates product data from multiple JSON sources
  • Harmonizes schemas and removes inconsistencies

• Order Data Processing

  • Ingests order data from CSV
  • Maintains referential integrity between products, orders, and order items

• REST API Layer

  • Flask-based API serving product data
  • Simulates an external upstream data provider

• Analytics Dashboard

  • Streamlit web app for sales analysis and product performance
  • Interactive charts and KPIs

• Relational Database Backend

  • MySQL (MariaDB) as a centralized data store
  • Normalized schema with foreign-key constraints

---

🧱 System Architecture

┌────────────────────┐
│ JSON Product Feeds │
└─────────┬──────────┘
│
┌─────────▼──────────┐
│ Flask REST API     │
└─────────┬──────────┘
│
┌─────────▼──────────┐
│ Data Ingestion     │
│ (Pandas + Python)  │
└─────────┬──────────┘
│
┌─────────▼──────────┐
│ MySQL Database     │
│ (Products, Orders) │
└─────────┬──────────┘
│
┌─────────▼──────────┐
│ Streamlit Dashboard│
└────────────────────┘

---

🛠️ Technologies Used

Layer	Tools
Language	Python 3.8+
API	Flask
Analytics	Streamlit
Data Processing	Pandas
Database	MySQL / MariaDB
DB Connector	mysql-connector-python / PyMySQL
Server	XAMPP
Visualization	Streamlit Charts

---

📂 Project Structure

/Ecommerce_Data_Analyzer/
│
├── api_server.py          # Flask REST API
├── data_ingestion.py      # Product & order ingestion
├── app.py                 # Streamlit dashboard
│
├── data/
│   ├── products_source_1.json
│   ├── products_source_2.json
│   └── orders.csv
│
├── mysql_setup.sql        # DB schema
├── requirements.txt
└── README.md

---

📦 Prerequisites

• Python 3.8+
• XAMPP (Apache + MySQL + phpMyAdmin)
• Basic knowledge of SQL & Python virtual environments

---

⚙️ Installation & Setup

1️⃣ Clone Repository

git clone <your-repository-url>
cd Ecommerce_Data_Analyzer

---

2️⃣ Create & Activate Virtual Environment

python -m venv .venv

Windows

.\.venv\Scripts\activate

macOS / Linux

source .venv/bin/activate

---

3️⃣ Install Dependencies

pip install -r requirements.txt

---

🗄️ MySQL Database Setup

1. Launch XAMPP

2. Start Apache and MySQL

3. Open phpMyAdmin → http://localhost/phpmyadmin

4. Open SQL tab

5. Paste contents of mysql_setup.sql

6. Execute to create:

   • products
   • orders
   • order_items

⚠️ If MySQL runs on a non-default port (e.g. 3307), update it in Python configs.

---

🔐 Database Configuration

Update DB_CONFIG in:

• api_server.py
• data_ingestion.py
• app.py

DB_CONFIG = {
    'host': 'localhost',
    'database': 'ecommerce_data',
    'user': 'root',
    'password': '',
    'port': 3306
}

---

📊 Data Integrity Requirement

Ensure all product IDs referenced in orders.csv exist in the product JSON files.

Missing product definitions will cause foreign-key constraint errors.

---

▶ Running the Pipeline

1️⃣ Start Flask API

python api_server.py

📍 Runs at: http://127.0.0.1:5000

---

2️⃣ Run Data Ingestion

python data_ingestion.py

✔ Inserts products & orders into MySQL

---

3️⃣ Launch Streamlit Dashboard

streamlit run app.py

📍 Opens at: http://localhost:8501

---

📈 Dashboard Preview

Performance Overview

Dashboard

---

🔍 Dashboard Insights

• Sales trends over time
• Product-level revenue analysis
• Category & brand performance
• Order volume statistics

---

🛑 Stopping Services

• Press Ctrl + C in terminals running:

  • api_server.py
  • app.py

• Stop MySQL & Apache via XAMPP

---

🎯 Learning Outcomes

• Designing end-to-end data pipelines
• Working with relational databases
• Building REST APIs
• Creating interactive analytics dashboards
• Enforcing data integrity constraints

---

⭐ If this project helped you learn or build, consider starring the repository!