Level 2 · The cohort retention table

Retention needs a window

“What share of customers came back?” has no answer until you say within how long. Give a customer three years and nearly everyone at Harbor & Pine orders again; give them a week and almost nobody does. A cohort retention table fixes the window and measures every cohort against it: “repeat within 90 days of the first order”, “repeat within 365 days”.

The window is counted from each customer’s own first order, not from the start of the quarter. A customer who first bought on March 30 and one who first bought on January 2 both get exactly 90 days.

One flag per customer

Join each customer’s later booked orders onto their first order, and turn “is there one inside the window?” into a 0/1 flag:

MAX(CASE WHEN b.order_date <= f.first_at + INTERVAL 90 DAY THEN 1 ELSE 0 END) AS r90

MAX collapses many later orders into one flag per customer: a customer with five repeat orders still counts once. The join must be a LEFT JOIN with b.order_date > f.first_at in the ON clause, so one-time buyers stay in the table with a flag of 0. Put that condition in WHERE and they vanish from the denominator, and retention jumps.

Then the cohort’s rate is the average of the flags: SUM(r90) * 100.0 / COUNT(*).

Fair windows only

A 365-day window only makes sense for customers who have had 365 days. Anyone whose first order was in 2025 has not, so their missing repeats are not a verdict, just a lack of time. That is why this table stops at first orders before 2025-01-01. This is called right-censoring: the outcome is cut off by the end of the data, not by the customer.

Reading the table

Read down a column to see whether newer cohorts behave like older ones. Read across a row to see how retention builds with a longer window. Compare holiday cohorts with holiday cohorts: gift buyers from November behave differently from customers who found the store in spring.

Watch the first cohort of any dataset. If the data begins in January 2023, the “2023Q1 cohort” is partly customers who were already loyal before the data starts. Here that is 116 of 177 people, which is why that row looks so good.

Common mistakes

• No window. “Ever came back” rewards old cohorts for being old.
• WHERE instead of ON. Filtering the later orders in WHERE drops one-time buyers.
• Counting orders instead of customers. COUNT(b.order_id) measures how much repeaters buy, not how many came back.