Part 2 of 2 — following the EDA with a focused funnel investigation.
The first Olist analysis established that 97% of customers are one-time buyers and that the top 10% of customers drive 38% of total revenue. A natural follow-up question: are operational failures in fulfillment driving customers away? This report was built to answer that directly — does the order pipeline lose customers, and does delivery performance explain the retention problem?
The central finding is a paradox: the fulfillment funnel is operationally excellent at every stage — but the customer experience inside that efficient pipeline reveals a meaningful satisfaction problem that demands attention.
SQL in BigQuery. Four tables. Five findings.
All analysis was performed in Google BigQuery using SQL. Scope was limited to orders with a recorded delivery date (96,476 orders) to ensure delivery time calculations were accurate. Two additional tables — order_reviews and timestamp fields within orders — were joined to the existing dataset from Part 1.
- CASE WHEN — classifying orders as on-time vs. late by comparing actual vs. estimated delivery dates
- DATE_DIFF — calculating elapsed days between each funnel stage
- CTEs — staging delivery status classifications before joining to review and customer tables
- Multi-table JOINs — connecting orders → reviews → customers across all findings
- COUNT DISTINCT on customer_unique_id — correctly identifying repeat buyers at the person level
Five findings. One paradox.
Finding 1 — The Fulfillment Funnel Is Operationally Excellent
Of 99,441 total orders, all three stage-to-stage conversion rates exceed 98%. Approved: 99.84%. Shipped: 98.37%. Delivered: 98.79%. The largest drop occurs between approval and shipment (1.63%), pointing to seller fulfillment delays as the primary source of pipeline loss — not payment processing or final-mile delivery.
Finding 2 — Average Delivery Time Is 12.1 Days
The average time between order placement and customer delivery is 12.09 days. This is a structural disadvantage in modern e-commerce where customers increasingly expect delivery within a few days. When the baseline is already 12 days, any additional delay is felt acutely — which makes Finding 3 more significant, not less.
Finding 3 — 8.1% of Orders Are Delivered Late
7,827 orders (8.11%) were delivered after their promised date. While 91.89% arrive on time, nearly 1 in 12 orders experiences a delay — large enough to represent a recurring customer experience problem at scale. Late deliveries are not edge cases; they are a predictable operational pattern.
Finding 4 — Late Deliveries Drop Review Scores by 40%
On-time orders average a review score of 4.29 out of 5. Late orders average 2.57 — a drop of 1.72 points, or 40%. This is not a marginal finding. Even though only 8.1% of orders are late, those orders generate disproportionately negative experiences that likely suppress ratings, discourage referrals, and damage platform reputation.
Finding 5 — Delivery Delays Do Not Directly Explain Retention
The most analytically interesting result: customers who received late deliveries show a slightly higher repeat purchase rate (4.81%) vs. on-time customers (3.40%). This appears to contradict the satisfaction finding — but it reflects selection bias. Customers who place multiple orders are more likely to have experienced at least one late delivery simply because they have more orders in the system. The result should not be interpreted as evidence that late deliveries are neutral to retention.
The correct interpretation: delivery performance alone cannot account for the 97% one-time buyer rate. Low retention is driven by structural marketplace factors — customers using Olist for one-off purchases, limited loyalty mechanisms, and the nature of gift and specialty product categories.
The code behind each finding.
How Parts 1 and 2 connect.
Taken together, the two Olist analyses tell a coherent story about the business's core challenge and where to focus improvement efforts.
- 97% one-time buyers (Part 1) → Delivery delays hurt satisfaction but don't fully explain retention. Structural marketplace factors are likely dominant — customers arriving for one-off purchases with no built-in reason to return.
- Top 10% drive 38% of revenue (Part 1) → Protecting high-value customers from late delivery experiences is critical. A 4.29 → 2.57 review score drop risks losing the segment that matters most.
- $159.83 avg order value (Part 1) → A 12-day average delivery window is misaligned with mid-range pricing expectations. Faster delivery would support, not just improve, the customer experience.
- Health, Gifts, Lifestyle lead revenue (Part 1) → Gift and lifestyle purchases are inherently time-sensitive. Late delivery is especially damaging in these categories where timing is part of the value.
Four actions, prioritized by impact.
- Reduce the late rate from 8.1% to ~4%. With review scores dropping 40% for late orders, a 50% reduction in late deliveries would meaningfully lift overall platform satisfaction. Tighter seller SLA enforcement, more accurate delivery date estimation, and proactive customer communication when delays occur are the highest-leverage operational levers.
- Address the 12-day delivery baseline. Logistics partnerships, regional fulfillment centers, or seller incentives for faster processing could compress the average delivery window — improving customer experience even without reducing the late rate.
- Build loyalty mechanisms — retention is the real problem. Since delivery performance does not fully explain the 97% one-time buyer rate, retention strategies must address underlying marketplace behavior. Loyalty programs, personalized re-engagement emails, and subscription or bundle offers are higher-leverage than operational fixes alone.
- Implement priority fulfillment for the top 10%. The top decile drives 38% of revenue. Proactive service recovery — automatic credit or discount for any late delivery — for this segment would protect the revenue concentration identified in Part 1 while improving satisfaction where it matters most.