Olist 02: The Number That Almost Fooled Me: When "Repeat Purchase" Runs Backward in Time
38% of "repeat customers" placed their second order before the first one even arrived. Here's how that broke a model, and how we fixed it.
Series, Part 2 | A plain-language read on the Olist Brazilian e-commerce root-cause report (full notebook on Kaggle, link at the end)
Time for an honest confession. There’s a model in this report whose first run produced a conclusion I nearly wrote down as fact.
That model set out to answer a simple question. Does how well a customer’s first order goes, on-time delivery, a good review, affect whether they ever come back? The coefficients that came back were almost pure noise. Statistically, it looked like delivery experience didn’t matter at all.
But the real problem wasn’t that the effect wasn’t there. It was that the timeline we fed the model was running backward.
1. Warm-up: one field name, two different customer counts
Before getting into the timeline problem, here’s a smaller, easier trap worth mentioning first, since it’s the entry-level version of the same mistake.
Olist’s order table has a field called customer_id. The name suggests it identifies a customer. It doesn’t. It’s an alias for each order. Buy twice, and the system generates two different customer_id values for the same person. The field that actually tracks “one real person” is called customer_unique_id.
Count it either way and you get two very different totals.
- Counting by
customer_id: 99,092 “customers” - Counting by
customer_unique_id: 95,774 actual people
The extra 3,318 aren’t new customers. They’re the same repeat buyers showing up twice, once for each order, each time logged as if they were someone new.
Left: counting by the wrong field overstates "customers" by 3,318. Right: the real repeat purchase rate is only 3.1%, with 96.9% one-time buyers.
Here’s why this trap does real damage. Use the wrong field by mistake and the repeat purchase rate gets pulled straight toward zero, because each returning customer’s two orders look, in the data, like two different people. The system literally can’t see the repeat purchase happening.
This one’s cheap to find and cheap to fix, just a matter of picking the right column. But there’s a second version of this same mistake buried deeper in the report, and this one costs a lot more to catch.
2. The real trap: a model that came back with nothing
With the field fixed, the next step was the more interesting question. Does the experience of the first order affect whether a customer comes back?
Run a logit model with delivery delay and review score as predictors, and “placed a second order” as the outcome. The first run came back almost entirely as noise.
- Delivery delay’s effect was small enough to ignore
- The “was it late” variable had a p-value of 0.57
- Review score had a p-value of 0.86
Statistically, none of these variables explained anything. And if that were true, most of what the industry believes about logistics driving retention would need a rewrite.
Something about that result felt off enough not to accept it at face value. Going back to look at the raw distribution of the data turned up something more troubling.
3. The timeline is running backward
Line up each repeat customer’s first and second order by timestamp, and a chunk of them placed their second order before the first one was even delivered. Some placed it the same day.
This doesn’t hold up logically. You haven’t received the package yet, so how could this delivery experience be the reason you decided to buy again?
Here’s how much of the data this actually contaminates. 38% of “repeat purchase” cases had the second order placed before the first one was delivered. These orders got counted as successful repeat purchases, but there’s no way delivery experience can explain them. They’re pure noise, and they systematically drag every coefficient toward zero. That’s why the first model came back empty. It’s not that delivery genuinely doesn’t matter. It’s that over a third of the evidence was unusable from the start.
4. Switching to a stricter ruler: only orders placed after delivery count
The fix wasn’t to throw out data. It was switching to a tighter definition. A repeat purchase only counts as “experience-driven” if the second order came after the first one was actually delivered. Customers who ordered early stay in the denominator, they’re just counted as a zero, so the sample size doesn’t shrink because of this.
Switch to this stricter ruler, and the coefficients that had looked dead come back to life.
| Before (mixed timing) | After (delivery-anchored) | |
|---|---|---|
| Was late | p = 0.57 (no effect) | −0.57pp, p = 0.03 |
| Review score | p = 0.86 (no effect) | +0.14pp per star, p < 0.001 |
Delivery experience wasn’t useless. The old timeline had just buried a third of the evidence in noise.
5. One more round of self-doubt: could the new ruler be creating a false effect?
At this point the analysis could reasonably stop. But one risk hadn’t been ruled out yet. Could the delivery-anchored definition itself be systematically biased toward a certain kind of customer?
Customers whose order arrived late naturally have less time between delivery and the end of the observation window, less time to place a second order. That alone could lower their repeat rate, purely because of a shorter clock, not because their experience was worse, creating an effect that looks real but isn’t.
The test: look only at 2017 customers, since this group has 8 to 20 months of observation, long enough that a few extra days of delivery delay barely register against that window. If the effect were just an artifact of shorter windows, it should shrink or disappear in this group.
The result. The effect held, both in direction and size. The review score effect actually grew to +0.31pp per star, and the late delivery effect grew to −0.97pp. The concern about a false effect was ruled out.
One more check that doesn’t depend on the model at all. Look directly at two groups of customers defined by outcome, not prediction. Customers with an objectively good experience (received three or more weeks early, gave five stars) had a repeat rate of 3.54%. Customers with an objectively bad one (late delivery, two stars or fewer) had a repeat rate of 2.32%. That raw gap matches the model’s predicted direction and size, which is a good sign this small effect isn’t something the modeling process invented on its own.
Predicted repeat rate for the worst experience (10 days late, 1 star) is 1.2%, the base rate is 1.9%, and the best possible experience (22 days early, 5 stars) is 2.2%. All three sit far below the 15% threshold, and the gap between worst and best case is under one percentage point.
6. The final word: not “no effect,” and not “the key lever” either
The wording here needed to be precise, because the mistake teams make most often isn’t a bad calculation. It’s a conclusion written with more confidence than the data can actually support.
Here’s the accurate answer. Delivery experience does have a real, measurable effect on repeat purchase, but its ceiling is somewhere between 2.2% (strictest measurement) and 3.3% (loosest). A healthy repeat rate benchmark in e-commerce sits around 15%, and that’s already a conservative bar. Even a customer’s first experience being flawless in every way still wouldn’t get the predicted repeat rate anywhere close to a quarter of that benchmark.
The accurate statement isn’t “delivery doesn’t matter,” and it isn’t “delivery is the key lever for retention” either. It’s this: delivery experience has a measurable effect, but it’s an order of magnitude too small to carry retention on its own.
The full report, including all code, charts, and cited sources, is published on Kaggle:[Kaggle]
Next up: the one part of this report that’s genuinely a story. An 11-day trucker strike in 2018, and how the platform quietly moved its own delivery promise while it was happening.
Photo by Jakub Żerdzicki on Unsplash