E-commerce growth and customer retention
Olist grew GMV almost threefold in a year. I used SQL to check where that growth came from, and found a marketplace that is excellent at acquiring customers and almost never sees them again.
- Data
- Real: Olist public dataset, 97,905 orders, 94,703 customers, Jan 2017 – Aug 2018
- Tools
- SQL (DuckDB), Python, statsmodels
- Methods
- Window functions, cohort analysis, RFM, two-proportion z-test
- Currency
- Brazilian reais (R$)
Growth is real, and seasonal
Monthly GMV went from R$137k in January 2017 to around R$1M through 2018. Black Friday (November 2017) lifted GMV 53% in a single month, the biggest spike in the data.
Questions I answered in SQL:
- How fast is GMV growing, and what drives the peaks?
- Do customers come back after their first order?
- Which segments hold the revenue?
- Does the first-order experience change repeat behaviour?
Monthly GMV
R$, product value + freight; canceled and unavailable orders excluded. Hover for orders and AOV.
Almost nobody comes back
A key detail: Olist gives a new customer_id to every order. Joining on customer_unique_id instead reveals the real person. Without that fix, retention can't be measured at all.
Every monthly cohort retains under 1% of customers in any later month, and only 3.0% of all customers ever order twice. Growth is coming almost entirely from new customers.
WITH first_purchase AS (
SELECT customer_id, order_month,
MIN(order_month) OVER (PARTITION BY customer_id) AS cohort_month
FROM orders
),
activity AS (
SELECT DISTINCT customer_id, cohort_month,
DATE_DIFF('month', cohort_month, order_month) AS month_number
FROM first_purchase
),
cohort_size AS (
SELECT cohort_month, COUNT(DISTINCT customer_id) AS customers
FROM activity WHERE month_number = 0 GROUP BY cohort_month
)
SELECT STRFTIME(a.cohort_month, '%Y-%m') AS cohort,
s.customers,
a.month_number,
COUNT(DISTINCT a.customer_id) AS active,
ROUND(100.0 * COUNT(DISTINCT a.customer_id) / s.customers, 2) AS retention_pct
FROM activity a
JOIN cohort_size s USING (cohort_month)
WHERE a.month_number <= 12
GROUP BY a.cohort_month, s.customers, a.month_number
ORDER BY a.cohort_month, a.month_number;Cohort retention, % of customers active N months after first purchase
2017 acquisition cohorts. Darker cells mean higher retention (max 0.7%).
Who holds the revenue
RFM segments: share of customers vs share of revenue
Frequency is scored with thresholds (1, 2, 3+ orders) because 97% of customers buy once, so quintiles would be meaningless.
New high-value and Lost high-value customers are 30% of the base but bring 56% of revenue. Repeat customers are only 3%.
Top 15 categories by revenue
Share of product revenue. Hover for the cumulative share.
A long tail: the top 10 of 74 categories make up 62.4% of revenue, so no single category dominates.
The first delivery shapes loyalty
Taking only each customer's first order (with at least 6 months to return), customers whose first delivery arrived late came back 22% less often: 3.19% vs 4.09%.
A two-proportion z-test puts the chance of seeing this gap by luck at p = 0.002. A 5-star first review also goes with a higher repeat rate than a 1-star one.
That's why I followed up with a separate root cause analysis of late deliveries.
Repeat-purchase rate by first-order experience
% of customers who ordered again
Recommendations
Build a second-purchase programme
Target "New high-value" customers 30–45 days after delivery. Moving the repeat rate from 3% to 5% means about 1,900 more returning customers per 95k acquired.
Win back lost high-value customers
13.9k people averaging R$309 each. Use category-specific campaigns based on what they bought first.
Treat on-time delivery as a retention lever
Late first orders measurably reduce repeat purchases, so logistics KPIs belong in the growth review, not only the ops review.
Plan for Black Friday early
The biggest demand spike of the year. Inventory, carrier capacity and customer service need to scale ahead of it.