One-liner: SQL + Python analysis of ~99K real e-commerce orders uncovering revenue trends, customer segmentation patterns, delivery performance gaps, and product portfolio insights — with statistical validation and business recommendations.
Tech Stack: MySQL 8.0 | Python (pandas, matplotlib, seaborn, scipy) | Power BI (optional dashboard)
Dataset: Olist Brazilian E-Commerce — 99,441 real anonymized orders, 2016-2018, 9 relational tables
| # | Finding | Metric | Implication |
|---|---|---|---|
| 1 | Revenue grew ~7x from R$120K to R$849K monthly | R$13.55M total | Strong growth trajectory, Q4 seasonality |
| 2 | 97% of customers are one-time buyers | 3.0% repeat rate | R$1.42M opportunity if 5% convert to repeat |
| 3 | Late deliveries score 1.7x lower on reviews | Cohen's d = 1.33 (large) | Delivery SLA directly drives satisfaction |
| 4 | Top 5 categories = ~40% of revenue | R$5.7M combined | Diversified portfolio, low concentration risk |
| 5 | 85% of payments via credit card | Avg 3.5 installments | Installment plans are key purchase enabler |
ecommerce-sales-analytics/
├── notebooks/
│ └── ecommerce_eda.ipynb # Python EDA + statistical tests
├── database/
│ ├── 01_create_tables.sql # Schema (3NF, 9 tables)
│ ├── 02_constraints.sql # CHECK, FK constraints
│ ├── 03_insert_data.sql # Bulk CSV load
│ ├── 04_indexes.sql # Indexing + EXPLAIN before/after
│ ├── 05_views.sql # 4 reusable views
│ ├── 06_queries.sql # 53 analytical queries
│ ├── 07_stored_procedures.sql # 5 stored procedures
│ └── 08_triggers.sql # 3 triggers
├── datasets/raw/ # 9 Olist CSV files
├── docs/
│ ├── PRD.md
│ ├── Data_Dictionary.md
│ ├── ERD.md
│ ├── Query_Optimization.md
│ ├── GIT_WORKFLOW.md
│ └── steps/
├── reports/
│ ├── business_recommendations.md # Business narrative
│ └── exports/ # CSV exports for BI tools
├── screenshots/
│ ├── query_results/ # MySQL + Python plots
│ └── execution_plans/ # EXPLAIN before/after
└── README.md
Total product revenue of R$13.55M across 98,199 orders, with steady monthly growth from R$120K (Jan 2017) to R$849K (Aug 2018). Peak month was November 2017 at R$1.00M, indicating strong Black Friday / holiday seasonality. Average order value stabilized around R$138.
97.0% of 94,983 unique customers are one-time buyers, with only 3.0% placing 2+ orders. Repeat buyers contributed R$890K compared to R$14.85M from one-time buyers. This extreme imbalance represents the single largest growth opportunity — converting just 5% of one-time buyers would generate an estimated R$1.42M in additional revenue.
On-time delivery rate is 93.5% (median 10 days). The Welch's t-test confirms late deliveries have significantly lower review scores (on-time mean 4.29 vs late mean 2.57, p<0.001, Cohen's d = 1.33 — large effect). Correlation between delivery delay and review score is r = -0.23 (p<0.001).
Top 5 categories (health_beauty, watches_gifts, bed_bath_table, sports_leisure, computers_accessories) account for ~40% of revenue. The remaining 69 categories form a long tail with diverse contribution — suggesting inventory rationalization opportunities but no dangerous concentration risk.
| Test | Variables | Result | P-value | Business Takeaway |
|---|---|---|---|---|
| Welch's t-test | Late vs on-time delivery → review score | Reject H0 | <0.001 | Late deliveries have 1.7x lower review scores (large effect) |
| Mann-Whitney U | Weekday vs weekend → AOV | Fail to reject H0 | 0.31 | No significant AOV difference; uniform pricing OK |
| Pearson correlation | Delivery delay → review score | r = -0.23 | <0.001 | Longer delays weakly erode satisfaction |
| Chi-square | Payment type vs review score | Reject H0 | <0.001 | Payment method influences satisfaction |
- MySQL 8.0+
- Python 3.8+ with:
pip install pymysql pandas matplotlib seaborn scipy sqlalchemy python-dotenv - (Optional) Power BI Desktop for interactive dashboards
# 1. Clone the repo
git clone https://github.com/YOUR_USERNAME/ecommerce-sales-analytics.git
cd ecommerce-sales-analytics
# 2. Create schema
mysql -u root -p < database/01_create_tables.sql
# 3. Add constraints
mysql -u root -p < database/02_constraints.sql
# 4. Load data (enable LOCAL INFILE first if needed)
mysql --local-infile=1 -u root -p ecommerce_analytics < database/03_insert_data.sql
# 5. Add indexes
mysql -u root -p ecommerce_analytics < database/04_indexes.sql
# 6. Create views
mysql -u root -p ecommerce_analytics < database/05_views.sql
# 7. Run queries
mysql -u root -p ecommerce_analytics < database/06_queries.sql
# 8. Install stored procedures
mysql -u root -p ecommerce_analytics < database/07_stored_procedures.sql
# 9. Install triggers
mysql -u root -p ecommerce_analytics < database/08_triggers.sql
# 10. Run Python notebook
set DB_PASSWORD=your_password
cd notebooks
jupyter notebook ecommerce_eda.ipynb| Skill Category | Details |
|---|---|
| Schema Design | 3NF normalization, PK/FK, CHECK constraints, composite keys |
| Query Writing | 53 analytical queries: joins, CTEs, subqueries, CASE |
| Window Functions | ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, NTILE, FIRST_VALUE, SUM()/AVG() OVER |
| Data Modeling | Views (4), Stored Procedures (5), Triggers (3), Indexes (11) |
| Query Optimization | EXPLAIN before/after, indexing strategy, ANALYZE TABLE |
| Python Analysis | pandas EDA, matplotlib/seaborn visualization |
| Statistics | t-test, Mann-Whitney U, Pearson/Spearman correlation, Chi-square |
| Business Thinking | Data-driven recommendations with quantified impact |
| Documentation | PRD, Data Dictionary, ERD, Query Optimization notes |
See reports/business_recommendations.md for detailed findings and actionable recommendations across revenue, customer segmentation, product portfolio, delivery, payment, and geography.
- Power BI / Tableau interactive dashboard on exported CSVs
- Python ETL pipeline for incremental data loads
- Customer churn prediction model (logistic regression / random forest)
- A/B test framework for delivery SLA experiments
- Role-based DB access per persona (analyst / manager / marketing)
Haridyanshu Jindal
- Email: haridyanshujindal@gmail.com