Olist: why 97% of customers never buy again

Why I picked this question

Olist is a Brazilian marketplace that connects small sellers with customers. In sales, I learned that winning a second order from a customer is usually cheaper than finding a new one. So before looking at anything else, I wanted to know how many Olist customers actually come back, and whether the ones who do are worth chasing.

How I approached it

1. Loaded the 8 raw tables into MySQL using Python (pandas), and kept only delivered orders.
2. Measured how often customers buy, then compared spend between one-time and repeat buyers.
3. Tested the obvious explanations first: do repeat buyers buy different products, or rate their experience higher?
4. Measured the gap between purchases to find when customers come back.
5. Compared repeat rates between sellers, then built a Power BI dashboard to summarise it all.

What I found

1. Almost nobody comes back

Only 2,801 customers ordered more than once during the period in the data.

WITH customer_orders AS (
  SELECT c.customer_unique_id,
         COUNT(DISTINCT o.order_id) AS order_count
  FROM customers c
  JOIN orders o ON c.customer_id = o.customer_id
  WHERE o.order_status = 'delivered'
  GROUP BY c.customer_unique_id
)
SELECT order_count,
       COUNT(*) AS number_of_customers,
       ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 2) AS pct_of_customers
FROM customer_orders
GROUP BY order_count
ORDER BY order_count;

2. The few who return are worth a lot more

Repeat customers spent about 92% more per person. Even a small increase in second purchases would matter.

3. It isn't the products or the service

Four of the top five categories were the same for both groups (health and beauty, home and bath, sports and leisure, computer accessories). Average review scores were almost identical too: 4.15 for one-time buyers and 4.21 for repeat buyers. So fixing products or service quality is unlikely to bring people back on its own.

4. Timing matters

The second purchase takes the longest, about 81 days. After that, gaps settle at around 61 to 63 days. Getting the second order is the hard part.

WITH customer_orders AS (
  SELECT c.customer_unique_id, o.order_purchase_timestamp,
    ROW_NUMBER() OVER (PARTITION BY c.customer_unique_id
      ORDER BY o.order_purchase_timestamp) AS order_sequence,
    LAG(o.order_purchase_timestamp) OVER (PARTITION BY c.customer_unique_id
      ORDER BY o.order_purchase_timestamp) AS previous_order_date
  FROM customers c
  JOIN orders o ON c.customer_id = o.customer_id
  WHERE o.order_status = 'delivered'
)
SELECT order_sequence,
       COUNT(*) AS customers_at_this_order,
       ROUND(AVG(DATEDIFF(order_purchase_timestamp, previous_order_date)), 1) AS avg_days
FROM customer_orders
WHERE order_sequence > 1
GROUP BY order_sequence
ORDER BY order_sequence
LIMIT 5;

5. Some sellers keep customers much better than others

Among sellers with 50+ customers, the best kept up to 8 times more customers than the platform average. Location, category and reviews didn't explain it, so these sellers are worth studying.

What I would recommend

1. Test a win-back message around day 55 to 65 after the first order, before the average second purchase at day 81. Measure it against a control group.
2. Learn from the best sellers. Compare their delivery, pricing and follow-up with similar sellers who don't retain customers.
3. Track retention by monthly cohort so the business can see whether it improves over time.

What I'd look at next

Customers who bought near the end of the dataset had less time to come back, so tracking cohorts over the same time window would give a fairer picture. I'd also add delivery times, late deliveries and freight costs to see whether they explain some of the churn.

A problem I had to solve first

My first import stored IDs and dates as TEXT, so MySQL couldn't index them and my five-table joins kept timing out. I re-imported all 8 tables with the right data types (VARCHAR for IDs, DATETIME for dates), added 14 indexes, and the same query then ran in 8.13 seconds.