Case studies / E-commerce delivery risk and churn

Late deliveries hurt reviews, and 96.9% of customers never ordered again

Olist churn root-cause investigation. Public dataset from Kaggle: 113,425 Brazilian marketplace orders. Python, MySQL, and Power BI.

By Zeeshan Akram, founder of MarginLens.

The question

Revenue at this marketplace stopped growing in mid-2018. This investigation looks at why, through two questions: how much delivery performance affects customer satisfaction, and how many customers come back after a first order.

The numbers

• Customers who never placed a second orderObserved in the data96.9%
• Average review score: on-time vs late ordersObserved in the data4.2 vs 2.5
• November 2017 customers who bought again in DecemberObserved in the data40 of 7,060

Observed figures are measured directly in the dataset. Modeled figures are what-ifs built from it, not savings or forecasts.

Findings

• On-time or early orders average 4.2 stars, late orders 2.5, and failed deliveries 1.7.
• Shipping cost also drives bad reviews; in categories like Electronics, freight is sometimes ~70% of the item price, dragging down scores even on on-time shipments.
• Delays concentrate in remote northern states (e.g., Amazonas, Roraima), bulky product lines (e.g., office furniture), and select high-earning sellers.
• 96.9% of unique customers (93,082 out of 96,076) never placed a second order.
• Every customer segment averages 4.0 stars or better (including Lost customers), showing that non-return is spread across satisfied buyers and is not directly explained by delivery satisfaction alone.
• Monthly marketplace revenue peaked near R$1M in November 2017 before stalling between R$838K and R$977K from March to August 2018.

What the data does and does not show

The data supports a clear link between late delivery and low review scores. However, it does not show that late delivery is why customers fail to return, since satisfied customers with on-time deliveries also mostly fail to reorder. Delivery and retention are treated as two separate operational problems.

What the audit concluded

RecommendationTreat late delivery and repeat purchase as two separate problems. Fix fulfillment to protect reviews, and test a second-purchase offer to win customers back.

Methodology

• Cleaning and exploration: Python, merging five relational tables, then exploring review scores against delivery time and freight against price.
• Business queries: five SQL queries in MySQL using CTEs and window functions: month-over-month revenue growth, delivery against reviews, late-delivery rates for top sellers, RFM segmentation, and cohort retention.
• Segmentation: because ~97% of customers are one-time buyers, custom absolute thresholds were used across recency, frequency, and spend.
• Dashboard: a four-page Power BI report on a star schema covering performance, logistics, retention, and executive summary.

Limitations

• The dataset is public Kaggle data (Olist marketplace), so seller and shipping dynamics will differ from an individual direct-to-consumer Shopify store.
• Observational data only; associations do not prove direct causation without controlled experiments.
• A customer with one order counts as not returning within the limited observation window.
• No cost, margin, or marketing-spend data is available.

Report and source files

• Read the executive report (PDF)
• Dashboard layout (PDF)
• Code, queries and notebooks (GitHub)

Other case studies

• Product profit concentration and SKU risk
• Discount profitability and margin erosion
• Customer lifetime value and retention