Live Dashboard → Click Here
An end-to-end customer analytics system built on the Olist Brazilian e-commerce dataset (~100K orders). Answers real business questions around retention, churn, revenue concentration, and customer segmentation.
- What percentage of customers return after their first purchase?
- How quickly do customers churn, and which cohorts are worst affected?
- Where does the business lose users in the purchase funnel?
- Which customers generate the most revenue (Pareto analysis)?
- Which customer segments should the business prioritize for retention?
| Metric | Finding |
|---|---|
| One-time buyer rate | ~97% of customers never placed a second order |
| Top 20% customer revenue share | Top 20% of customers drive ~62% of total revenue |
| Funnel drop-off | Largest leakage occurs post-payment: ~3% of orders drop off |
| Churn rate | Consistently above 90% across all monthly cohorts |
| Delivery impact | Customers with delivery time >20 days show significantly lower repeat rates |
Data Cleaning & Preparation
Merged 5 Olist CSVs on order_id and customer_id. Filtered to delivered orders only.
Engineered features: order_month, first_purchase_date, repeat_customer_flag, delivery_time_days.
SQL-Style Analysis
Replicated GROUP BY, JOIN, CTE, and window function logic in pandas.
See queries.sql for equivalent SQL for all major metrics.
Cohort Retention Analysis
Grouped customers by first purchase month. Built retention matrix showing % of users
returning in months 1–12.
Churn Analysis
Defined churn as customers with exactly 1 order. Computed churn rate per cohort.
Identified delivery delay as a correlated factor.
Revenue Analysis
Monthly revenue trend, AOV over time, Pareto curve showing revenue concentration.
Top 20% of customers contribute ~62% of total revenue.
Funnel Analysis
Tracked orders through 4 stages: Created → Payment Confirmed → Delivered → Review Submitted.
Conversion rates annotated at each stage.
RFM Segmentation
Scored customers on Recency, Frequency, Monetary value. Segmented into:
Champions, Loyal, At Risk, Lost.
- Python — pandas, numpy, matplotlib, seaborn
- SQL — cohort logic, window functions, CTEs (
queries.sql) - Streamlit — interactive dashboard, deployed on Streamlit Cloud
1. Retention drops to <1% by month 2
→ Introduce a post-purchase email sequence (discount or product recommendation) within 7 days of delivery.
Target the ~3% who do return — understand what made them come back.
2. Top 20% customers drive ~62% of revenue
→ Build a loyalty tier for high-frequency, high-spend customers.
Losing even 10% of this segment has outsized revenue impact.
3. Delivery delays correlate with churn
→ Flag orders with estimated delivery >15 days at placement and proactively communicate with customers.
Consider regional logistics partnerships to reduce tail-end delivery times.