Customer Repurchase at Olist (Final Project, Part 1)

The question

Olist connects small merchants to Brazil's large marketplaces. In its public 2016–2018 data almost nobody buys twice, and that is expensive: a customer who already bought could come back without the cost of acquiring a new one. The TripleTen final project came in two stages: first a decomposition, the analysis plan, approved by a reviewer; only then the implementation, with a notebook, a dashboard and a presentation. My question was: what happens in the first purchase (delivery, rating, freight and region) that separates who comes back from who does not, and where can Olist and its merchants act?

The final sprint also had an A/B test and a SQL analysis, which are in part 2.

The decomposition: the plan before the analysis

Before any test, I looked at the data to settle the definitions every number depends on.

Who the customer is. Counted by customer_id, nobody ever bought twice: every order creates a new record. Counted by the person (customer_unique_id), someone reaches 17 orders and 2,997 people (3.12%) placed more than one. The customer became the person.

What a repurchase is. A new order from the same person, on another day, within 180 days of the first purchase. Same-day orders do not count: 789 reviews covered more than one order and 94.80% of them joined orders from the same day, one purchase split in several. The 180-day window covers 76.04% of returns, against 55.61% with 90 days.

Which months to compare. The edges of the data are nearly empty (September 2016 has 4 orders). From January 2017 to August 2018 there are 99,092 of the 99,441 orders (99.65%), in comparable months.

The join trap. Joining orders, items and reviews directly took the table from 99,441 to 114,092 rows (14.73% more): an order with several items would count several times. The plan was to aggregate each table per order before joining.

Five hypotheses, each with its test and KPI chosen in advance: late delivery (H1), a low rating (H2), the weight of freight (H3), the customer's state (H4), and whether a late delivery drives customers away by dragging the rating down (H5). Price and freight are skewed (mean item price R$ 120.65 against a median of R$ 74.99), so the plan already called for medians and tests that do not assume a normal distribution.

The reviewer approved the decomposition and left seven points for the implementation, such as moving the end of the usable period to 31 August 2018. The final notebook records what I did with each one.

The data

In the implementation, the 99,441 orders became 96,096 people. So that everyone had the same 180 days to come back, I built a cohort of people whose first purchase fell between 1 January 2017 and 4 March 2018: 58,139 people. Only 1,167 of them came back (2.01%), the reference rate for the whole project.

• I aggregated items and reviews per order before every join and checked at each step that no row multiplied: the final table has 58,139 rows and 58,139 people.
• 3,955 of the 56,154 first purchases delivered with a date arrived late (7.04%).
• States with under 5% of orders were grouped as "other", so their rates would not be unstable.

Method, and why this method

The outcome is binary (came back or not), so I used a two-proportion z-test, with chi-square first whenever there were more than two groups and a Holm correction for multiple comparisons. For freight, which is skewed, I used Mann-Whitney. Alpha was fixed at 5% before any test ran and, beyond the p-value, I reported the 95% interval of each difference and the smallest difference each test could detect at 80% power: with a 2% repurchase rate, "no difference" can simply mean too little data.

Finally I re-ran H1 and H2 with 90- and 365-day windows and without people who placed several orders on the first day, and translated the effects into people, because percentage points alone hide the real size.

Findings

A late delivery drags the rating down. With a late first delivery, 75.63% of ratings are 1 to 3, against 18.17% on time (z = 82.95). It is the strongest result of the project, and it is what the chart above shows.

Customers who rate 1 come back less. Repurchase changes with the rating (chi-square, p = 0.0291), and the difference sits in the 1-star group: 1.68% against 2.10% for high ratings, Holm-adjusted p = 0.0398. Ratings of 2 and 3 do not differ.

Late delivery matters, but only just. Customers with a late delivery came back at 1.57% against 2.05% on time (p = 0.0389, interval −0.88 to −0.07 points). The direction holds at 90 and 365 days but the significance does not, so I treat H1 as a signal rather than a certainty. With the rating held equal, no test passes: the data is too small to say whether delay drives customers away only through the rating (H5).

Freight does not separate who comes back, and state separates little. The median freight weight is 0.223 in both groups (Mann-Whitney, p = 0.3747). By state, only São Paulo sits above the rest of the cohort (2.25%) and "other" below it (1.74%).

In people, the effect is small. If customers with a late delivery came back at the on-time rate, that would be about 19 more people in 180 days (3 to 35 by the interval), 1.62% of the 1,167 repurchases.

The decision it enables

Deliver the first purchase on time, for the right reason: the direct gain in repurchase is small, and the strong argument is customer experience, with 1–3 ratings rising from 18% to 76% when the delivery is late. Act quickly on 1-star ratings, the group the test confirmed comes back less. Break down the "other" group before investing by region, since it mixes very different states. Do not count on freight coupons to bring customers back. And track two alert KPIs, the first-purchase delay rate (7.04%) and the share of 1-star ratings (12.22%), repeating the analysis as more months of data arrive.

The data is observational: it shows what moves with repurchase, not what causes it. The delivery included a Tableau Public dashboard that reads the same numbers as the notebook and a CAR-format presentation for decision-makers. The decomposition and the implementation were approved by the TripleTen reviewer, and with this sprint I completed the program.

Links

Interactive dashboard on Tableau Public · Repository · Part 2: A/B test and SQL