Business Problem — E-commerce Analytics¶
Company Context¶
Olist is a Brazilian e-commerce marketplace connecting small businesses to customers across the country. It handles ~100,000 orders annually across thousands of sellers and product categories. The data spans the full customer journey: order placement, payment, delivery, and post-purchase reviews.
The problem: The leadership team operates on gut feel. They don't know their true customer lifetime value, which product categories actually drive profit, why some customers leave bad reviews, or whether their delivery times are hurting satisfaction. They have rich data trapped in 8 separate tables that nobody has connected.
You've been asked to build the analytics layer that turns this relational data into business intelligence.
Business Questions to Answer¶
Q1 — Revenue and Growth¶
- What is total revenue and order volume, and how does it trend over time?
- Which product categories generate the most revenue?
- What is the average order value, and how does it vary by category?
Q2 — Customer Analysis¶
- What is the customer lifetime value distribution?
- What proportion of customers are repeat buyers vs one-time?
- Can we segment customers using RFM (Recency, Frequency, Monetary)?
- Which states/regions have the most valuable customers?
Q3 — Delivery and Satisfaction¶
- What is the average delivery time?
- Is there a relationship between delivery time and review score?
- Which product categories have the worst review scores?
- Do late deliveries cause bad reviews?
Q4 — Payment Behaviour¶
- What payment methods do customers use?
- Do customers who pay in instalments spend more?
Q5 — Operational¶
- Which sellers perform best (volume, ratings, delivery speed)?
- Where are delivery bottlenecks geographically?
Success Metrics¶
| Metric | Goal |
|---|---|
| Customer LTV visibility | Calculated and segmented |
| Repeat purchase rate | Measured and improved |
| Review score | Identify and fix drivers of bad reviews |
| On-time delivery rate | Measured, bottlenecks identified |
Stakeholders¶
| Stakeholder | Cares about |
|---|---|
| CEO | Growth, LTV, overall health |
| Head of Marketing | Customer segments, repeat purchase, acquisition value |
| Head of Operations | Delivery times, seller performance |
| Head of Product | Category performance, review drivers |