Skip to content

README.md

Latest commit

 

History

History
190 lines (150 loc) · 11.4 KB

README.md

File metadata and controls

190 lines (150 loc) · 11.4 KB
Raw

🛒 E-Commerce Database & Data Mining Intelligence Platform

Database Architecture

📌 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:

🍊 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:

📈 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.