Flagship · SQL · Power BI · Machine Learning
E-Commerce Customer Satisfaction & Predictive Analytics
An end-to-end build on 99K Brazilian e-commerce orders: a PostgreSQL pipeline, a Power BI dashboard, and a classifier that flags orders likely to receive a bad review while they are still in transit.
- 99K
- orders analyzed
- 340K
- rows · 8 tables
- 126
- engineered features
- 50%
- recall on bad reviews
01
Business problem
Olist is a Brazilian e-commerce marketplace. The question: what drives revenue and customer satisfaction across Brazil — and can we spot an unhappy customer before they leave a 1–2 star review?
By the time a bad review is written, the customer is usually already lost. A prediction made while the order is still in transit leaves room to act.
02
Dataset & scope
Public Olist Brazilian E-Commerce dataset: ~99K orders and ~340K rows across 8 tables, September 2016 – September 2018. September 2018 is a partial month and is annotated on the trend chart rather than hidden.
Personal learning project on public data — not affiliated with Olist.
03
Data cleaning & validation
- Loaded raw files into staging tables and fixed encoding, duplicate and orphan-record issues in SQL, with row-count checks and zero rows deleted.
- Locked a single revenue definition — R$13,591,643.70, item price with freight excluded as pass-through — and verified every query and DAX measure against it.
- For modeling: removed 446 duplicate order IDs, filled 1,273 missing categories, and dropped ambiguous 3-star reviews — leaving 87,946 orders.
04
SQL & database architecture
An 8-table PostgreSQL schema with composite keys and foreign-key constraints. Analysis used joins, CTEs, subqueries, aggregation and date functions.
05
Exploratory analysis
| Question | Finding |
|---|---|
| Revenue trend | Steady month-over-month growth, peaking November 2017 (holiday shopping) |
| Categories | Health & beauty leads, but the top 5 categories are only ~40% of revenue |
| States | São Paulo alone is ~38% of revenue — 3x the #2 state |
| Concentration | Top 5 states = 74% of revenue vs. top 5 categories = 40% |
| Delivery vs. reviews | 5-star orders arrive in ~10 days; 1-star orders take about twice as long |
The strongest pattern is a clean staircase — as delivery time rises, review scores fall:
| Review score | Avg delivery days | Orders |
|---|---|---|
| 5 stars | 10.2 | 57,066 |
| 4 stars | 11.8 | 18,987 |
| 3 stars | 13.8 | 7,961 |
| 2 stars | 16.2 | 2,941 |
| 1 star | 20.8 | 9,406 |
06
Dashboard & visualization
The report was planned on a blueprint before building in Power BI, then implemented with DAX measures (TOPN, DATEDIFF, DIVIDE), Power Query cleaning and a custom theme. A PowerPoint executive briefing accompanies it.
07
Machine learning methodology
- SQL feature pull: one row per order with delivery timing, cost, category and location, plus a binary bad-review label (1–2 stars).
- Engineered a cross-state shipping proxy and one-hot encoded categoricals into 126 features.
- 80/20 stratified split (70,356 train / 17,590 test) preserving the 14% bad-review rate.
- Trained a scaled, class-weighted Logistic Regression and a Random Forest on the identical split.
- Leakage guard: every feature is knowable at or before delivery; the review score is only the label.
08
Model evaluation & limitations
| Model | Recall | Precision | ROC-AUC |
|---|---|---|---|
| Logistic Regression | 50% | 35% | 0.728 |
| Random Forest | 35% | 51% | 0.731 |
Accuracy was deliberately not the target: with only 14% bad reviews, predicting "good" for every order scores 86% and catches nothing. Logistic Regression was chosen because, for an early-warning system, a missed at-risk order costs more than a false alarm.
Limitations: ROC-AUC near 0.73 is moderate, and the selected model wrongly flags 2,317 good orders in the test set. Results are on historical public data and have not been tested in a live operation.
09
Findings & recommendations
Customers react to broken promises, not clock time. Lateness against the estimated delivery date outranked raw delivery speed; three of the top five features are delivery timing, together about 40% of the model's decisions. Category and location barely registered.
How an e-commerce business could use this (proposed, not measured outcomes):
- Set conservative delivery estimates that are reliably met, rather than aggressive ones that get missed.
- Score in-transit orders and flag high-risk ones for a proactive message, discount or shipping upgrade.
- Treat dissatisfaction as a fulfillment execution problem — it is not concentrated in particular categories or regions.
- Concentrate marketing geographically while keeping the product catalog broad.
10
Repository
SQL scripts, Python notebooks, the Power BI file, exports and the briefing deck are all on GitHub.
View repository on GitHub