Cohorts and retention

01The problem

Harbourline's commercial director has a worry: "We win new customers, but do they stay? Or do we keep replacing the ones we lose?"

Counting active customers per quarter doesn't answer it. The count was 80 in the first quarter of 2025 and 73 a year later, but that total mixes long-standing customers with new ones. A fall could mean old customers leaving, new ones not sticking, or both. To find out, you need to follow groups of customers who started at the same time and see what each group does next. That's cohort analysis, and it's one of the most requested analyses in subscription, retail and B2B companies.

02The concept

A cohort is a group of customers who share a starting point: here, the quarter of their first booking. A retention table counts how many from each cohort are active 0, 1, 2… periods later.

You build one in three steps:

1. Activity: one row per customer per period they were active (SELECT DISTINCT customer_id, period).
2. Cohort: each customer's first period (MIN(period)).
3. Join and count: for each cohort and each "periods since start", count the customers.

To work out "periods since start", give every quarter a number that counts up: year × 4 + quarter. Then 2026-Q1 minus 2025-Q3 is 2 quarters, even across a year boundary.

Two traps to say out loud

• Left-censoring. Harbourline's data starts in January 2025, but customers signed up as early as 2021. The "2025-Q1 cohort" is really everyone already active when the data begins, not new customers. Treat it as the existing base and compare the genuinely new cohorts separately.
• Part periods. The data ends on 31 August 2026, so 2026-Q3 has two months, not three. Activity in that quarter will look lower simply because it's shorter. Label it or leave it out.

03Example

The retention table, by quarter of first booking:

WITH activity AS (
  SELECT DISTINCT
    customer_id,
    CAST(strftime('%Y', booking_date) AS INTEGER) * 4
      + (CAST(strftime('%m', booking_date) AS INTEGER) + 2) / 3 AS q_index
  FROM shipments
),
cohorts AS (
  SELECT customer_id, MIN(q_index) AS cohort_q
  FROM activity
  GROUP BY customer_id
)
SELECT
  (c.cohort_q - 1) / 4 || '-Q' || ((c.cohort_q - 1) % 4 + 1) AS cohort,
  a.q_index - c.cohort_q AS quarters_since_start,
  COUNT(*) AS active_customers
FROM activity AS a
JOIN cohorts AS c ON c.customer_id = a.customer_id
GROUP BY c.cohort_q, quarters_since_start
ORDER BY c.cohort_q, quarters_since_start;

Reading it:

• The existing base (2025-Q1, 80 customers) is steady: 63 were active the next quarter, and 56 were still booking in the short 2026-Q3.
• The 2025-Q2 cohort, the largest group of genuinely new customers, had 13 customers, but only 6 booked again the next quarter.

Does that mean new customers leave? Check before you say so. Of the 19 customers who first booked between April and December 2025, 16 booked again in 2026: 84%, about the same as the existing base (68 of 80, 85%). New customers aren't leaving more. They're booking less often, which is a different problem with a different fix (account management, not win-back campaigns).

04Walkthrough

1. Run the activity CTE on its own and check that a customer appears at most once per quarter.
2. Check the quarter number: SELECT (2026 * 4 + 1) - (2025 * 4 + 3); gives 2, which is 2025-Q3 to 2026-Q1.
3. Run the full example and find the 2025-Q2 row for quarters_since_start = 4. 11 of 13 were active a year later, more than in the quarters in between. A seasonal pattern? That's worth a question to the sales team.
4. Write the director's answer in two sentences, mentioning both traps.

05Practice

06Challenge

07More practice

08Check your understanding