Back to Dashboard
RetailIQ · Built by Yuvraj · 2026

One question
drove everything.

Why do 93% of e-commerce customers never return after their first order? I spent weeks inside 100,000 real transactions to find out.

0
Real orders
0.777
Model AUC
9
DB tables
62
ML features
Scroll to explore
93%
never placed
a second order
Single-purchase (93%)
Returned (7%)
The Problem

The real cost of a bad first impression.

Every e-commerce company talks about customer acquisition. The real money is in the second purchase — and 93% of customers on Olist's platform never made one.

That's not a marketing problem. It's a data problem. And data problems need data solutions. I built this platform to find the root cause — and predict who's going to leave before they do.

Dataset: Olist Brazilian E-Commerce · 99,441 real orders · 2016–2018

How it was built

Phase by phase. Decision by decision.

01
Data Engineer
Schema Design & Ingestion Pipeline
Designed 9 PostgreSQL tables with proper FK constraints and indexes — not a CSV dump. Two schemas: raw and analytics. Batch-insert pipeline loads all 99K rows with full logging and error handling.
PostgreSQL SQLAlchemy Medallion Architecture Python
02
Data Analyst
5 SQL Queries — Real Business Questions
Revenue trend with LAG window functions. RFM segmentation with NTILE scoring across 96,478 customers. Delivery vs satisfaction with PERCENTILE_CONT. Product health scoring. Seller scorecards with DENSE_RANK. Each query answers something a real business would pay to know.
CTEs Window Functions NTILE RFM Framework PERCENTILE_CONT
03
Data Scientist · The Mistake
Churn Model v1 — 99.9% Precision (Wrong)
First model hit 99.9% precision. I immediately knew something was wrong. After investigation: classic target leakage — recency_days was both defining the churn label AND used as a feature. CV AUC was 0.56. The model wasn't learning — it was memorising a rule. Scrapped entirely.
Target Leakage Detected CV AUC: 0.56
04
Data Scientist · Fixed
Churn Model v2 — Leakage-Free Frame
New definition: given first-order signals only, predict whether the customer will ever place a second order. Recency and frequency completely excluded. RandomizedSearchCV — 30 iterations. 5-fold CV. SHAP explainability reveals freight_value as #1 predictor — a real, actionable business insight.
AUC: 0.777 CV: 0.760±0.009 SHAP XGBoost
05
Full Stack
API & Dashboard
FastAPI with 9 endpoints serving live PostgreSQL data and real-time ML predictions. Dashboard as pure HTML/JS — no Dash, no framework, total CSS control. Deploys anywhere.
FastAPI Pydantic Pure HTML/JS Live DB
The Hardest Part

When 99.9% precision is a red flag.

Any data scientist should immediately be suspicious of near-perfect metrics on a real-world dataset. I was.

The diagnosis: recency_days was both defining the churn label (recency > 180 = churned) AND used as a feature. The model was memorising a threshold, not learning customer behaviour.

The fix wasn't a hyperparameter tweak. It required rethinking the entire ML frame — new label definition, new feature set, rebuilt from scratch. That's what real data science looks like.

❌ Version 1 — Leaked model
99.9%
Train Precision
0.56
CV AUC
~Random
Real Performance
recency_days used as feature → directly encodes the label → model learns nothing meaningful
rebuilt from scratch
✅ Version 2 — Leakage-free
0.777
Test AUC
0.760
CV AUC
±0.009
CV Stability
First-order signals only · recency & frequency excluded · 62 behavioural features · 5-fold CV
SHAP Explainability

The model told us something the business didn't know.

freight_value is the #1 churn predictor — not delivery speed, not review score. Customers who pay disproportionately high freight relative to order value never come back.

This is "surprise cost shock" — the customer feels deceived. The product was R$50, the delivery was R$25. They don't complain. They just don't return.

Business implication: A free-shipping threshold above R$150 would directly attack the #1 churn driver — no loyalty programme needed.

Top churn predictors — Mean |SHAP| value from your model.pkl
freight_value
0.196
installments
0.171
avg_item_price
0.150
promised_days
0.134
actual_days
0.108
review_score
0.094
freight_pct
0.074
End-to-end architecture

From raw CSV to live prediction in 8 steps.

📦
Raw CSV
9 files · Kaggle Olist · 99K rows
🐍
Ingestion
Python · SQLAlchemy · batch inserts
🗄️
PostgreSQL
raw + analytics · 9 tables · 7 indexes
📐
SQL Layer
5 queries · CTEs · window functions
→
→
→
🧮
Feature Eng.
Pandas · 62 leakage-free features
🤖
XGBoost
AUC 0.777 · SHAP · 5-fold CV
⚡
FastAPI
9 endpoints · Pydantic · live DB
🖥️
Dashboard
Pure HTML · SVG charts · real-time
What the data said

Four findings that matter.

01
4.29 → 1.73
Delivery SLA is the #1 retention lever
Late delivery (7+ days) destroys avg customer rating. Even 1 day late drops it from 4.29 to 2.6. Tighten SLA on bulky categories — no discount needed.
02
SHAP #1
Freight cost predicts churn better than satisfaction
Surprise shipping costs create "cost shock" — the strongest signal that a customer won't return. Free-shipping above R$150 would directly address this.
03
10× LTV
Champions are worth protecting at any cost
RFM segmentation: Champions avg R$318 LTV vs R$32 for Lost. 15,526 Champions identified. A referral programme targeting this group would compound growth.
04
R$1.24M
health_beauty is the benchmark
Highest revenue, high satisfaction, manageable logistics. Every other category should benchmark against it. The data makes the case without ambiguity.
Yuvraj
Yuvraj
Data Scientist | Data Analyst | Data Engineer | AI-ML Engineer
🏆Global Hackathon Finalist · Outlier.ai / Scale AI
☁️Oracle Cloud AI · 96% & 98%
🤖RLHF · Deccan AI Inc. (USA)
📊Data Science Intern · Gaurav Industries
"I didn't build RetailIQ to learn tools.
I built it to prove I can think with data."