Cohort Analysis in Marketing: Measuring Retention and LTV

Why cohorts matter

Aggregate metrics mix all customers together. If revenue grows, you cannot tell whether new customers spend more or existing ones return more often. A cohort is a group of customers who share an event in the same period, most often a first purchase or sign-up in the same month. Tracking each cohort separately shows you:

• how quickly customers drop off;
• whether retention improves for newer cohorts after product changes;
• which channels bring customers who stay and pay longer.

How to build acquisition cohorts

You need an orders (or activity) table with three fields: customer ID, date and amount. Plus each customer's acquisition channel, usually by first touch or first order.

1. For each customer, find the first purchase date: that is the cohort month.
2. For each order, compute the period number since the first purchase: month 0, 1, 2 and so on.
3. Group by cohort and period number: count active customers and sum revenue.

WITH first_order AS (
  SELECT customer_id, DATE_TRUNC(MIN(order_date), MONTH) AS cohort_month
  FROM orders
  GROUP BY customer_id
)
SELECT
  f.cohort_month,
  DATE_DIFF(DATE_TRUNC(o.order_date, MONTH), f.cohort_month, MONTH) AS month_n,
  COUNT(DISTINCT o.customer_id) AS active_customers,
  SUM(o.revenue) AS revenue
FROM orders o
JOIN first_order f USING (customer_id)
GROUP BY 1, 2
ORDER BY 1, 2;

The syntax is BigQuery; date functions are named differently in other databases. To compare channels, add the channel to first_order and to the grouping.

How to read a retention table

Rows are cohorts, columns are months since first purchase, cells are the share of the cohort that bought in that month. A hypothetical example:

The numbers are made up for illustration only. Read the table in three directions:

• Across a row: one cohort's lifecycle. Where is the steepest drop, and does the curve flatten? A plateau means the product has a core of loyal customers.
• Down a column: cohorts compared at the same age. If M1 rises from cohort to cohort, your changes are working.
• Along a diagonal: calendar events. A sale or an outage hits every cohort in the same calendar month.

Cohort LTV and comparing channels

Cohort LTV at horizon N is the cumulative revenue (better: gross margin) of a cohort by month N divided by the number of customers in it. Channels can only be compared fairly at the same horizon: 3-month LTV against 3-month LTV.

Then set it against CAC (customer acquisition cost) for the same channel:

A typical pattern: a cheap channel brings customers with little repeat demand, an expensive one brings customers who return. The first wins on first-order metrics, the second on cohort LTV.

Using cohorts for budget decisions

• Shift budget to channels with the best LTV/CAC ratio at a horizon your cash flow can sustain.
• Set target CAC per channel from expected LTV: how much you can pay for a customer and still pay back by the month you need.
• Watch new cohorts: if retention of fresh cohorts drops, a bigger budget only accelerates losses.
• For young cohorts, use an LTV forecast based on older cohorts' curves, and recheck it as data accumulates.

Common mistakes

• Incomplete cohorts: the March cohort has no M3 yet, so do not compare its "final" LTV with January's.
• Revenue instead of margin: a channel full of discount-driven buyers looks better than it is.
• Small cohorts: with a few dozen customers, percentages jump around by chance. Use longer periods or merge channels.
• Mixing in returning customers: a cohort must contain only new customers.