---

📌 Project Overview

Real-world Brazilian e-commerce data, analyzed end-to-end — from loading 7 raw CSV files into a relational MySQL database, all the way through 15 progressively complex business intelligence queries.

This project demonstrates a production-style Python → SQL analytics pipeline: automated schema inference, bulk data ingestion, and layered business questions answered with clean SQL — from basic aggregations to advanced window functions.

---

🗄️ Database Schema

Seven interconnected tables power all queries:

┌─────────────┐     ┌──────────────┐     ┌──────────────┐
│  customers  │────▶│    orders    │────▶│  order_items │
│─────────────│     │──────────────│     │──────────────│
│ customer_id │     │ order_id     │     │ order_id     │
│ customer_   │     │ customer_id  │     │ product_id   │
│   city      │     │ order_       │     │ seller_id    │
│ customer_   │     │  purchase_   │     │ price        │
│   state     │     │  timestamp   │     └──────────────┘
└─────────────┘     └──────────────┘            │
                           │                    ▼
                    ┌──────────────┐     ┌──────────────┐
                    │   payments   │     │   products   │
                    │──────────────│     │──────────────│
                    │ order_id     │     │ product_id   │
                    │ payment_     │     │ product_     │
                    │   value      │     │  category    │
                    │ payment_     │     └──────────────┘
                    │ installments │
                    └──────────────┘
     ┌──────────────┐        ┌──────────────────┐
     │   sellers    │        │   geolocation    │
     │──────────────│        │──────────────────│
     │ seller_id    │        │ zip_code_prefix  │
     │ seller_city  │        │ lat / lng        │
     └──────────────┘        └──────────────────┘

---

📁 Repository Structure

PythonSQL_Ecommerce_Project/
│
├── 📓 PythonSql_ecommerce.ipynb     # Main analysis notebook (EDA + all 15 SQL queries)
├── 🐍 csv_to_sql.py                 # Automated CSV → MySQL ingestion script
├── customers.csv
├── orders.csv
├── order_items.csv
├── payments.csv
├── products.csv
├── sellers.csv
├── geolocation.csv
│
└── 📖 README.md

---

🚀 Getting Started

Prerequisites

Python 3.8+  |  MySQL Server  |  Jupyter Notebook

Install required Python libraries:

pip install pandas mysql-connector-python matplotlib seaborn numpy

---

Step 1 — Clone the Repository

git clone https://github.com/sorol25/PythonSQL_Ecommerce_Project.git
cd PythonSQL_Ecommerce_Project

---

Step 2 — Set Up MySQL Database

CREATE DATABASE ecommerce;

---

Step 3 — Load CSV Data into MySQL

csv_to_sql.py handles everything automatically:

• Infers SQL column types from pandas dtypes (INT, FLOAT, DATETIME, TEXT)
• Creates all 7 tables dynamically with CREATE TABLE IF NOT EXISTS
• Handles NaN → NULL conversion cleanly before insert
• Commits each table as an independent transaction

Update the data folder path, then run:

# In csv_to_sql.py — update this line to your local path:
folder_path = 'your/path/to/data/folder'

python csv_to_sql.py

---

Step 4 — Open the Analysis Notebook

jupyter notebook PythonSql_ecommerce.ipynb

Update the DB connection at the top:

db = mysql.connector.connect(
    host="localhost",
    username="root",
    password="your_password",
    database="ecommerce"
)

---

🔍 Business Questions Solved

🟢 Basic — Foundations

#	Question	Highlight
1	Unique cities where customers are located	4,119 distinct cities
2	Total orders placed in 2017	45,101 orders
3	Total sales per product category	74 categories — top: Bed Table Bath at $1.71M
4	% of orders paid in installments	~99.99% of all transactions
5	Customer count by state	SP dominates — visualized as bar chart

---

🟡 Intermediate — Business Aggregations

#	Question	SQL Technique
1	Orders per month in 2018	MONTHNAME() · GROUP BY · sns.barplot
2	Avg products per order, grouped by city	CTE + AVG() + multi-table JOIN
3	Revenue % share per product category	Inline subquery ratio calculation
4	Price vs. purchase frequency correlation	np.corrcoef → r = –0.106 (weak negative)
5	Revenue per seller, ranked	DENSE_RANK() OVER(ORDER BY revenue DESC)

---

🔴 Advanced — Window Functions & Analytics

#	Question	SQL Technique
1	Moving average of order values per customer	AVG() OVER(ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
2	Cumulative monthly sales per year	SUM() OVER(ORDER BY years, months)
3	Year-over-year revenue growth rate	LAG() → 2017: +12,112% · 2018: +20%
4	6-month customer retention rate	Dual CTE + DATE_ADD() + LEFT JOIN
5	Top 3 spenders per year	DENSE_RANK() OVER(PARTITION BY year ...)

---

📊 Key Findings at a Glance

💰  Total Platform Revenue (all time)   ~$16,008,872
📦  Orders in 2017                       45,101
📈  YoY Growth 2017 → vs 2016           +12,112%  (platform launch ramp)
📈  YoY Growth 2018 → vs 2017           +20%
🛍️   Top Revenue Category               Bed Table Bath — 10.70% of total
🔁  Orders Paid via Installments        ~99.99%
📉  Price vs. Purchase Correlation      –0.106  (weak negative — cheaper items sell more)

---

💡 Featured Queries

Year-over-Year Revenue Growth

WITH a AS (
  SELECT YEAR(orders.order_purchase_timestamp) AS years,
         ROUND(SUM(payments.payment_value), 2)  AS payment
  FROM orders JOIN payments ON orders.order_id = payments.order_id
  GROUP BY years ORDER BY years
)
SELECT years,
       ROUND(
         ((payment - LAG(payment,1) OVER(ORDER BY years)) /
           LAG(payment,1) OVER(ORDER BY years)) * 100
       , 2) AS yoy_growth_pct
FROM a;

Moving Average of Customer Order Values

SELECT customer_id, order_purchase_timestamp, payment,
  AVG(payment) OVER(
    PARTITION BY customer_id
    ORDER BY order_purchase_timestamp
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS moving_avg
FROM (
  SELECT orders.customer_id, orders.order_purchase_timestamp,
         payments.payment_value AS payment
  FROM payments JOIN orders ON payments.order_id = orders.order_id
) AS a;

Top 3 Customers by Spend Per Year

SELECT years, customer_id, payment, d_rank FROM (
  SELECT YEAR(orders.order_purchase_timestamp) years,
         orders.customer_id,
         SUM(payments.payment_value) payment,
         DENSE_RANK() OVER(
           PARTITION BY YEAR(orders.order_purchase_timestamp)
           ORDER BY SUM(payments.payment_value) DESC
         ) d_rank
  FROM orders JOIN payments ON payments.order_id = orders.order_id
  GROUP BY YEAR(orders.order_purchase_timestamp), orders.customer_id
) AS a
WHERE d_rank <= 3;

---

📜 License

MIT License — Copyright (c) 2026 Yeamine Alam Sorol
Free to fork, star, and build upon.

---

👨‍💻 About the Author

Yeamine Alam Sorol Data Analyst

 

---

_{If this project helped you, consider starring ⭐ the repo or sharing it with someone learning Data Analytics.}