🛒 E-Commerce Database & Data Mining Intelligence Platform

📌 Executive Overview

This repository contains an end-to-end E-Commerce Enterprise Database, Analytical Warehouse, Python EDA, and Data Mining Pipeline. The project integrates a fully normalized relational database (SQL Server RDBMS), analytical reporting views, advanced exploratory data analysis (EDA) using Python, machine learning workflows in Orange Data Mining (Supervised Classification & Unsupervised Clustering), and comprehensive executive documentation.

---

📁 Repository Structure

The project has been organized into 5 core modular directories:

E-Commerce-Database-Project/
├── 01_Database_Schema/               # RDBMS DDL, Foreign Key Constraints & Seed Datasets
│   ├── Creating Tables - Ecommerce.sql
│   ├── Insert Tables - Ecommerce.sql
│   ├── tabled created.pdf
│   └── SQL_Tables_Dataset/           # 13 CSV dataset files + Dataset Inventory Report
│       ├── Dataset_Report.txt
│       ├── Tables Dataset.rar
│       ├── address.csv
│       ├── cart.csv
│       ├── category.csv
│       ├── customer.csv
│       ├── order_table.csv
│       ├── orderitem.csv
│       ├── payment.csv
│       ├── product.csv
│       ├── product_return.csv
│       ├── review.csv
│       ├── seller.csv
│       ├── shipment.csv
│       └── support_tickets.csv
│
├── 02_Analytical_Views/              # SQL Business Intelligence Views, KPIs & Analytical Queries
│   └── Questions - Ecommerce.sql
│
├── 03_Python_EDA/                    # Python Exploratory Data Analysis & Interactive Notebooks
│   ├── EDA_Online_Retail.ipynb
│   ├── EDA_Report.html
│   └── data.xlsx                     # Online Retail Dataset (536K+ transactions)
│
├── 04_Orange_Data_Mining/            # Machine Learning & Clustering Workflows (Orange)
│   ├── Orange_Final.ows              # Orange Visual Workflow Scheme
│   └── Orange_Widget_Report.docx     # Data Mining Analysis & Widget Documentation
│
├── 05_Documentation_&_Presentation/ # Executive Reports, Presentation Slides & ERD Diagrams
│   ├── 01. Ecommerce_Project_Report.pdf
│   ├── 02. EDA Report — Online Retail Dataset.pdf
│   ├── Architecting_E-Commerce_Intelligence.pptx
│   └── ERD_Diagrams/
│       ├── ER_Diagram.png
│       ├── Relational Database Architecture.png
│       └── mindmap for tables.png
│
├── .gitignore                        # Git exclusion rules for temporary/junk files
└── README.md                         # Main Project Documentation

---

🗄️ Part 1: SQL RDBMS Schema (01_Database_Schema)

The foundational data layer is designed in Microsoft SQL Server (MS SQL) under the e_commerce schema. The relational architecture enforces 3rd Normal Form (3NF) across 13 core entity tables:

📊 Relational Database Architecture

• Customer Management: customer (Demographics, Age), address (Geographic & Shipping addresses).
• Vendor & Catalog Management: seller (Merchants), category (Product taxonomy), product (Inventory items, SKU, MRP, Stock).
• Shopping Cart & Checkout: cart (Session carts, conversion status), order_table (Order master), orderitem (Line items & pricing).
• Financials & Logistics: payment (Payment modes & status), shipment (Logistics provider, delivery status, SLA/Late flags).
• Post-Purchase & CX: product_return (Returns & refunds), review (Ratings & feedback), support_tickets (Support ticket tracking & SLA monitoring).

🔑 Referential Integrity & Constraints

• Primary & Foreign Keys: Standardized single and composite keys ensuring strict entity integrity.
• Cascading Rules: Implemented ON DELETE SET NULL and ON UPDATE NO ACTION to preserve historical order records even if customer entities change.
• Validation: Includes field data types, nullability restrictions, and numerical constraints.

---

📈 Part 2: Analytical Views & Business Intelligence Queries (02_Analytical_Views)

Located in 02_Analytical_Views/Questions - Ecommerce.sql, this module transforms normalized operational data into strategic decision-making insights via high-performance SQL Views and analytical queries:

👁️ Key Database Views

1. vw_CustomerMaster: Aggregates customer total spend, lifetime order count, primary city, and acquisition date.
2. vw_ProductCatalog: Dynamically labels inventory status (In Stock, Low Stock <= 80, Out of Stock <= 5) to optimize reordering.
3. vw_OrderDetails: Denormalized order report merging items, line totals, customer info, payment status, and shipping stage.
4. vw_ReturnRefundSummary: Consolidated returns tracker categorizing return reasons, status, and financial refund volume.
5. vw_SupportSLA: Dynamically calculates resolution time in hours and flags SLA breaches according to ticket priority thresholds (High: 24h, Medium: 48h, Low: 72h).

📊 Key Business Intelligence Queries

• Daily Revenue & AOV: Computes daily revenue, order volume, and Average Order Value (AOV).
• Cart Conversion & Abandonment: Analyzes monthly cart conversion rates (Current metric: 87.5% conversion, 12.5% abandoned carts).
• Category Sales Ranking: Uses window functions (DENSE_RANK() OVER (PARTITION BY category_id ORDER BY TotalRevenue DESC)) to rank top products per category.
• Courier Delivery SLAs: Measures on-time delivery percentages and average transit days across couriers (e.g., Aramex, DHL, FedEx, UPS).
• Outstanding Balances: Identifies unpaid orders and revenue distribution across payment modes (Card, Cash on Delivery, Online, Wallet).

---

🐍 Part 3: Python Exploratory Data Analysis (03_Python_EDA)

The EDA module analyzes over 536,000 transaction records using Python (pandas, numpy, matplotlib, seaborn, plotly).

🔍 Key Analysis Highlights

• Data Cleansing & Wrangling: Imputed missing values, filtered out invalid entries and canceled invoice anomalies.
• RFM Customer Segmentation: Evaluated Recency, Frequency, and Monetary values to identify high-value VIP segments versus at-risk customers.
• Temporal & Sales Trends: Mapped peak buying hours, day-of-week velocity, and seasonal spikes.
• Product & Geographic Reach: Identified top-selling SKUs and analyzed country-level revenue distribution.
• Deliverables:
  • EDA_Online_Retail.ipynb: Complete interactive Jupyter Notebook.
  • EDA_Report.html: Standalone interactive HTML report.

---

🍊 Part 4: Orange Data Mining & Machine Learning (04_Orange_Data_Mining)

The data mining workflow (Orange_Final.ows) implements both Supervised Machine Learning and Unsupervised Clustering using Orange 3.

🤖 Supervised Learning (Cancellation & Churn Prediction)

Five classification algorithms were trained and evaluated on an 80/20 Train/Test split:

1. k-Nearest Neighbors (k-NN) ($k=7$)
2. Logistic Regression
3. Decision Tree (Max depth = 8)
4. Support Vector Machine (SVM) (Linear Kernel)
5. Neural Network (Multi-layer Perceptron)

Model Evaluation & Metrics

• Performance Tools: Test & Score node, Confusion Matrix, ROC Analysis curves, and Lift/Performance curves.
• Objective: Accurately predict transaction cancellations and customer churn prior to order fulfillment.

🧩 Unsupervised Learning (Customer Segmentation)

• Algorithm: k-Means Clustering ($k=2$).
• Validation: Silhouette Plot analysis to evaluate cluster cohesion and separation quality.

---

📄 Part 5: Documentation, ERD & Presentation (05_Documentation_&_Presentation)

This directory houses all executive presentation collateral and comprehensive technical reports:

1. Executive Project Report: 01. Ecommerce_Project_Report.pdf — Complete project breakdown from database design to operational recommendations.
2. EDA Technical Report: 02. EDA Report — Online Retail Dataset.pdf — Statistical visual report detailing dataset characteristics.
3. PowerPoint Presentation: Architecting_E-Commerce_Intelligence.pptx — Executive slide deck outlining database schema, analytics, and ML model performance.
4. ERD Visual Diagrams:
   • ER_Diagram.png: High-resolution Entity-Relationship Diagram.
   • Relational Database Architecture.png: Relational schema diagram.
   • mindmap for tables.png: Conceptual schema mindmap.

---

📈 System Metrics & Insights Summary

Metric / Dimension	Value	Note
Total Database Tables	13 Tables	Fully normalized 3NF schema
Total Order Volume	$674,798.90	Over 700 customer orders
Delivered Revenue	$411,342.77	60.0% delivery completion rate
Cart Conversion Rate	87.5%	700 of 800 carts converted
Average Order Value (AOV)	$979.39	Delivered orders average
Top Revenue Category	Electronics ($288.9K)	Followed by Home & Kitchen ($100.2K)
Top Merchant Seller	TechNova Ltd ($180.6K)	Top revenue contributor
Customer Support SLA Breaches	69 Breached / 175 Tickets	SLA threshold: High (24h), Med (48h), Low (72h)

---

🛠️ How to Run & Setup

1. Database Setup (SQL Server)

1. Open SQL Server Management Studio (SSMS) or Azure Data Studio.
2. Execute 01_Database_Schema/Creating Tables - Ecommerce.sql to build the database, schema, and tables.
3. Populate seed data using 01_Database_Schema/Insert Tables - Ecommerce.sql or import CSV files from 01_Database_Schema/SQL_Tables_Dataset/.
4. Execute 02_Analytical_Views/Questions - Ecommerce.sql to generate all analytical views and KPIs.

2. Python EDA Setup

pip install pandas numpy matplotlib seaborn plotly jupyter
jupyter notebook 03_Python_EDA/EDA_Online_Retail.ipynb

Or open 03_Python_EDA/EDA_Report.html in any web browser.

3. Orange Data Mining Setup

1. Install Orange Data Mining 3.
2. Open 04_Orange_Data_Mining/Orange_Final.ows in Orange 3 to view model connections, confusion matrices, and ROC curves.

---

📝 License & Project Information

Developed as part of the Data Mining and Visualization curriculum. Designed for enterprise-grade e-commerce database architecture and data analytics.