Before you start
- A table of customers with an acquisition date and a per-period activity record. Orders with customer identifiers and dates, subscription periods, or product events keyed to a user. The SQL for marketers guide is the prerequisite: the queries here assume you can join, group and use window functions.
- One agreed definition of "acquired" and of "active". First paid order, first subscription start, or first qualifying event; and an order, a renewal, or a core action in the period. Write both down before querying.
- A stable customer identifier that survives device changes and email updates. Where identity is weak, cohorts of logged-in or paying customers are more reliable than cohorts of visitors.
- A place to build. A warehouse and a SQL client, or a spreadsheet for a first version on exported data. Google Analytics offers a cohort exploration for event-based cohorts, which the explorations guide covers; it is a quick first look rather than a replacement for a table built on orders.
- Enough history. Cohorts younger than a few periods tell you little; the most useful cohorts are old enough to show whether the curve flattens.
- The economics guides. The LTV and CAC guide explains what the table feeds; the retention and payback guide explains how to read the shapes.
Define the cohort
A cohort is a group of customers who share an acquisition event in the same period. Decide the event first: first paid order is the usual choice for retail, subscription start for subscriptions, and the first core action for products with a free tier, because cohorts of sign-ups mix people who never became customers with people who did and the curve then says more about activation than retention. Then decide the period that defines membership, normally the calendar month of the event, so that "the March cohort" means everyone whose first order fell in March. Record the acquisition channel and any segment attributes (plan, country, product line) alongside each customer at this point, because you will want to split the table by them later and the attributes should be the ones that held at acquisition, not the current ones.
Choose the measure
Each cell of the table holds one measure for one cohort at one age. The simplest is retention: the share of the cohort active in that period. The alternatives are revenue per original customer (cumulative or per period), contribution margin per original customer, or orders per original customer. Retention describes behaviour; revenue and margin describe value, and the lifetime value figure (LTVTotal value a customer brings over time.) comes from the margin version. Build retention first, because it is easiest to sanity-check, then add a revenue or margin table on the same cohorts. Decide whether retention means "active in this period" (which can go up and down) or "still active, never lapsed" (which only falls); the first is usual for retail, where customers skip months, and the second for subscriptions. Write the definition into the table's title.
Set the grain
The grain is the length of one period along the table's columns. It should match the business's natural cycle: weeks for products used daily, months for most retail and subscription businesses, quarters for considered purchases with long gaps. Too fine a grain produces a noisy table full of near-empty cells; too coarse hides the early drop where most of the lesson is. The cohort period and the age period are usually the same, but they need not be: monthly cohorts followed at weekly age is a reasonable choice for a product where the first month decides everything. Decide also how to count the acquisition period itself, normally as age zero, where retention is by definition complete, and whether partial periods at the end of the data are shown or masked, since an incomplete current month will always look like a collapse.
Build the table
In SQL the build has three parts: a customer-level table with acquisition period and attributes; an activity table of customer and period for every period in which the customer was active; and a join that computes each customer's age at each activity, then groups by cohort and age to count distinct active customers and divide by cohort size. In a spreadsheet the same logic is a pivot of a customer-by-period activity grid, with cohort as rows and age as columns. Check three things before trusting the output: the age-zero column equals cohort size everywhere; the row totals of cohort size reconcile to the number of customers acquired per period in your source system; and no cell exceeds the cell to its left in a "never lapsed" table. The worked query in the example shows the shape.
Illustrative example — not a real resultA monthly retention cohort query
The query assumes an
orderstable withcustomer_id,order_dateandnet_revenue, and builds a monthly "active in period" retention table for a homewares retailer. The first expression finds each customer's cohort month from their earliest order; the second lists every month in which a customer ordered; the third joins the two and works out the customer's age in months at each order; the fourth counts cohort sizes; and the final select groups by cohort and age, counts distinct active customers and divides by cohort size to give the retention share.with first_orders as ( select customer_id, date_trunc('month', min(order_date)) as cohort_month from orders group by customer_id ), activity as ( select distinct customer_id, date_trunc('month', order_date) as activity_month from orders ), aged as ( select f.customer_id, f.cohort_month, (extract(year from a.activity_month) - extract(year from f.cohort_month)) * 12 + (extract(month from a.activity_month) - extract(month from f.cohort_month)) as age_months from first_orders f join activity a on a.customer_id = f.customer_id ), cohort_sizes as ( select cohort_month, count(*) as cohort_size from first_orders group by cohort_month ) select g.cohort_month, s.cohort_size, g.age_months, count(distinct g.customer_id) as active_customers, round(100.0 * count(distinct g.customer_id) / s.cohort_size, 1) as retention_pct from aged g join cohort_sizes s on s.cohort_month = g.cohort_month where g.age_months between 0 and 12 group by g.cohort_month, s.cohort_size, g.age_months order by g.cohort_month, g.age_months;For the January cohort of 1,480 customers the query returns 1,480 at age zero, 412 at age one (27.8%), 301 at age two (20.3%) and 236 at age three (15.9%), which reconciles to the order system's count of 1,480 first orders in January. The analyst also checks that the February cohort of 1,620 reconciles before loading the output into the reporting table, because a mismatch at this stage usually means guest checkouts have been counted as new customers twice.
Plot the curves
A table of numbers hides shapes that a chart shows at once. Plot one line per cohort with age on the horizontal axis and the measure on the vertical, so that every cohort starts at the same point and the lines fan out to the right. For retention, a line that falls and then flattens means customers who survive the early periods tend to stay; a line that keeps falling at a steady rate means the product has no retained core yet; a line that falls and then rises means seasonal or replenishment behaviour. Plot cumulative revenue or margin per original customer on a second chart: that curve is the lifetime value taking shape, and the point where it crosses acquisition cost is the payback period. Colour cohorts by age or by acquisition quarter so that drift over time is visible, and mask the incomplete current period so that it does not read as a cliff.
Read the triangle
Laid out with cohorts as rows and age as columns, the table forms a triangle: old cohorts have many filled columns, young cohorts few. Three readings come from it. Down a column, you see how a given age has changed across cohorts: if month-three retention is rising from the top row to the bottom, something improved for newer customers (or the acquisition mix changed; check before celebrating). Along a row, you see one cohort's curve and whether it flattens. Along a diagonal, you see what happened in one calendar period to cohorts of every age, which is how a pricing change, an outage or a competitor launch shows up: a whole diagonal shifts at once. The blended retention rate that most dashboards report is a weighted average down a column with the weights set by cohort sizes, which is why it rises whenever acquisition grows, regardless of whether retention changed.
Compare cohorts
The question behind every comparison is whether newer customers behave differently from older ones, and why. Compare like with like: cohorts at the same age, not a mature cohort's late retention against a young cohort's early retention. Look for three patterns. A steady improvement down the column suggests product or onboarding changes are working. A steady decline suggests the acquisition mix is drifting towards worse-fit customers, often because paid channels are saturating; check the channel split before blaming the product. A step change in one cohort points to something specific that happened in that acquisition period, such as a promotion that pulled in bargain hunters, and that cohort should be annotated and perhaps excluded from projections. Record each comparison and its explanation in the growth team's learning log, because the same questions will be asked again next quarter.
Segment
A single table averages across customers who may behave quite differently. Split it by the attributes recorded at acquisition: channel first, because channel acquisition cost (CACCost to acquire one new customer.) can only be judged against the retention of the customers that channel brought; then plan, product line, country, device, first-order value or any attribute the business believes matters. Keep each segment's cohorts large enough to read, since a cohort of a few dozen customers produces a curve that is mostly noise, and merge periods where volumes are thin. The most valuable segment splits are usually the ones that separate a loyal core from a one-off tail, because they show that the blended lifetime value is an average of two populations and that acquisition should be aimed at the first. Where a segment's curve differs sharply from the rest, trace it back to the conversion step (CVRShare of visits or clicks that convert.) or offer that produced those customers. The same within-cohort comparison tells you whether a survey score warns of leaving, which the tutorial on testing whether survey scores predict churn runs by splitting each cohort into score bands.
Act and revisit
A cohort table is a decision tool, not a report. The actions it drives are: moving budget towards channels and segments whose curves flatten highest; aiming activation work at the age where the curve drops most; setting the lifetime value and payback inputs in the unit economics table from the observed curves rather than assumptions; and choosing which cohorts' behaviour to use when projecting. Then schedule the revisit. Refresh the table monthly, adding a row and extending every existing row by a column; compare each new month's reading of the same cells with last month's to catch data problems; and review the shapes quarterly against the growth model. Keep the retention KPIA metric tied directly to an objective. on the growth dashboard defined as a specific cell (for example, month-three retention for the latest complete cohort) rather than as the blended rate, so that the number cannot improve simply because acquisition grew. The cohort table shows how each acquisition month ages; to see where this month's change in the whole active base came from, the growth accounting tutorial reads the same activity by calendar period as new, retained, resurrected and churned customers.
Cohorts also keep pricing experiments fair: the free trial length test follows each sign-up week to the same horizon, so that trial lengths are compared on one clock.
For an app, the same split of each install cohort by whether users took an early action is how the app activation event is found, before that event becomes the cohort's starting line.
A cohort table says whether customers stayed but not where inside a journey they were lost; the comparison of cohort and funnel analysis sets out which question each answers and how to check a funnel fix in the cohorts it produced.
A cohort row says what share of a group lapsed but not which customers need which message; the comparison of RFM segmentation and cohort analysis explains when to sort individual customers by recent buying instead, and how to read those segments by cohort.
Cohort table template
The template below is the layout to build, with cohorts as rows, age as columns and one measure per cell. Copy it into a sheet or model the warehouse table on it; the filled example that follows shows what it looks like with data in it.
| Cohort (acquisition period) | Cohort size | Age zero | Age one | Age two | Age three | Age four | Age five | Age six |
|---|---|---|---|---|---|---|---|---|
| Oldest complete period | Customers acquired | Complete by definition | Share active | Share active | Share active | Share active | Share active | Share active |
| Next period | Customers acquired | Complete by definition | Share active | Share active | Share active | Share active | Share active | Masked if incomplete |
| Next period | Customers acquired | Complete by definition | Share active | Share active | Share active | Share active | Masked if incomplete | Masked if incomplete |
| Most recent complete period | Customers acquired | Complete by definition | Share active | Masked if incomplete | Masked if incomplete | Masked if incomplete | Masked if incomplete | Masked if incomplete |
Build the same layout twice more with cumulative revenue per original customer and cumulative contribution margin per original customer in the cells; the second of those is the lifetime value table, and the column where cumulative margin passes the channel's acquisition cost is the payback period for that cohort. Keep the three tables on the same cohorts and the same grain so that a cell in one can be read against the same cell in the others.
A filled retention table for a subscription box
| Cohort | Size | Age 0 | Age 1 | Age 2 | Age 3 | Age 4 | Age 5 | Age 6 |
|---|---|---|---|---|---|---|---|---|
| January | 1,480 | 100% | 71% | 58% | 52% | 49% | 47% | 46% |
| February | 1,620 | 100% | 69% | 55% | 50% | 47% | 45% | — |
| March | 2,910 | 100% | 54% | 38% | 31% | 28% | — | — |
| April | 1,730 | 100% | 72% | 60% | 54% | — | — | — |
| May | 1,690 | 100% | 74% | 62% | — | — | — | — |
Reading down the age-one column, January, February, April and May sit between 69% and 74%, while March drops to 54%: the March cohort was nearly twice the usual size because of a half-price promotion, and the bargain hunters it attracted lapsed early. Reading along the January row, the curve flattens from age four, which supports projecting a long tail for that cohort. The blended retention reported on the company's dashboard for June was 61%, pulled down by March's size; excluding March, like-for-like month-one retention had in fact improved from 71% to 74%. The team annotates March, excludes it from the lifetime value projection, and sets the dashboard KPI to "age-three retention, latest complete cohort" so that promotions cannot move it without changing behaviour.
Verify it worked
- Check the age-zero column. It should equal the cohort size in every row.
- Reconcile each cohort size with the customers acquired in your source system. In the example, the January cohort of 1,480 matches the order system.
- In a "never lapsed" table, confirm that no cell exceeds the cell to its left.
- Plot the curves. Every cohort should start at the same point and fan out to the right, with the incomplete current period masked.
- Read down one column and along one row. You should be able to name the cohort that stands out and say whether an older curve flattens.
If it goes wrong
- Cohort sizes do not reconcile with the source system. Check the acquisition definition first. A mismatch usually means guest checkouts were counted as new customers twice.
- The newest cohort looks like a collapse. The current period is incomplete. Mask it and plot again.
- A "never lapsed" row rises from one age to the next. The activity definition is probably "active in this period". Align the definition and write it into the table title.
- Cells are near empty or noisy. The grain is too fine, or the segment is too small. Choose a coarser period or merge thin segments.
- One cohort sits far from the rest, like March in the example. Look for a promotion in that acquisition period, annotate it and consider excluding it from projections.
Next steps
- Read the LTV and CAC guide to see how the margin table feeds lifetime value and acquisition cost.
- Read the retention and payback guide to interpret the curve shapes and the payback period.
- Read the explorations guide for the cohort exploration, a quick preview that does not replace a table built on orders.
Common mistakes
- Cohorts of sign-ups when the business sells to customers. The curve then measures activation, not retention; cohort on the first paid event.
- Blended retention as the KPI. It rises whenever acquisition grows; define the KPI as a specific cell.
- Comparing cohorts at different ages. A young cohort's early retention against a mature cohort's late retention says nothing.
- Including the incomplete current period. It always looks like a collapse; mask it.
- Attributes taken from the current record. Segment by what was true at acquisition, or a customer who upgraded last week will be counted as a loyal upgrader from the start.
- Reading improvement down a column without checking the mix. A better age-three figure may just be a cheaper channel's share shrinking.
- Building it once. The table is refreshed monthly or it decays into a slide; the customer analytics hub covers keeping the underlying data sound, and the growth economics hub shows where the table feeds.
