Order status distribution
Most orders complete, but the tail of friction still matters.
Marketplace Drop-Off, Revenue Leakage, and Operational Friction
The marketplace looks healthy at the surface, but post-purchase friction is quietly eroding value and trust.
Completion is strong, yet delivery delays and concentrated seller/category pain create a much larger business problem than cancellations alone. This project turns those signals into a prioritized action plan.
What’s Happening
Healthy at the top line, but fragile underneath
On paper, the marketplace looks fine. Delivered orders dominate. The problem is what happens after purchase: delays, negative reviews, and a smaller but real layer of leakage through cancellations.
Why it matters
The business doesn’t have a demand problem first.
A 97.02% completion rate means the real story is downstream. Value is being put at risk after purchase, especially when customers wait too long for delivery.
- Completion rate: 97.02%
- Cancellation rate: 0.63%
- Low review score rate: 14.69%
Funnel Story
This is a post-purchase funnel, not a clickstream funnel
Olist doesn’t have event-level browsing data. Instead, the funnel tracks how orders move from purchase to approval, shipment, delivery, and finally review coverage.
Order journey stages
Purchased to reviewed, using available order records only.
Interpretation
Orders largely survive the purchase-to-delivery path, but the reviewed stage drops because not every delivered order turns into customer feedback.
Caveat
This is intentionally not framed as a product event funnel. The story stays grounded in the tables the dataset actually contains.
Revenue Leakage
Delayed deliveries put more value at risk than cancellations
Cancellations are visible and easy to blame, but delayed deliveries are the bigger value-at-risk issue in this marketplace. That makes operational reliability a revenue story, not just a customer support story.
Value at risk by issue
Late deliveries are the larger revenue problem, not a secondary operations note.
Top canceled categories
Cancellation leakage is concentrated in a smaller set of product categories.
Customer Pain
Satisfaction collapses when delivery slips
Review behavior gives the clearest read on customer pain. The strongest pattern in the project is simple: longer delays sharply increase the odds of low scores.
Review distribution
Most reviews are strong, but the negative tail is meaningful.
Review score by delay bucket
Delays are strongly linked to falling satisfaction.
Sharpest signal
On-time orders average 4.29. Orders delayed 8+ days average 1.73.
That gap is the center of the story. Delivery friction is not a soft experience metric here. It is an observable driver of customer pain.
Operational Concentration
The pain is concentrated enough to act on
This is not a marketplace-wide failure. The worst performance is clustered in a smaller group of sellers and categories, which makes intervention more practical.
Concentration explorer
The worst performance clusters in a manageable group of sellers, categories, and states.
What to Fix First
The project ends with a ranked action plan, not a chart dump
The priority system combines impact and frequency to turn findings into action order. It keeps the project focused on business decisions, not just diagnosis.
Priority table
Owner and monitoring metric included for each issue.
| Issue | Score | Owner | Metric | Action |
|---|
Method + Data Notes
Simple model, grounded assumptions
The analysis stays deliberately practical: SQLite-backed, order-status-based, and focused on the parts of Olist that can support a defensible business case.
Simple schema
customers
→
orders
→
order_items
→
products
orders
→
payments
orders
→
reviews
order_items
→
sellers
Important caveats
- The funnel is order-status based, not clickstream based.
- Repeat purchase is a proxy using
customer_unique_id. - Refunds are approximated through cancellations and dissatisfaction, not direct refund tables.
- The project uses static data and a plain HTML/CSS/JS frontend for GitHub Pages portability.
Workflow
- CSV files are loaded into SQLite.
- SQL workstreams produce validated summary outputs.
- Those outputs are converted into static frontend data.
- The presentation layer is a storytelling microsite, not a BI dashboard.