Skip to content
Faisal Mahmud TusherDigital marketing, SEO & analytics
with Faisal Tusher

Growth Marketing › Growth Economics

Cohort Analysis

A step-by-step tutorial for cohort analysis: choosing the cohort definition, the retention or revenue measure, and building the table.
Tutorialintermediate13 min readUpdated

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.
  1. 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.

  2. 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 (LTV) 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.

  3. 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.

  4. 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 result

    A monthly retention cohort query

    The query assumes an orders table with customer_id, order_date and net_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.

  5. 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.

  6. 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.

  7. 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.

  8. 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 (CAC) 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 (CVR) 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.

  9. 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 KPI 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 sizeAge zeroAge oneAge twoAge threeAge fourAge fiveAge six
Oldest complete periodCustomers acquiredComplete by definitionShare activeShare activeShare activeShare activeShare activeShare active
Next periodCustomers acquiredComplete by definitionShare activeShare activeShare activeShare activeShare activeMasked if incomplete
Next periodCustomers acquiredComplete by definitionShare activeShare activeShare activeShare activeMasked if incompleteMasked if incomplete
Most recent complete periodCustomers acquiredComplete by definitionShare activeMasked if incompleteMasked if incompleteMasked if incompleteMasked if incompleteMasked 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.

Illustrative example — not a real result

A filled retention table for a subscription box

CohortSizeAge 0Age 1Age 2Age 3Age 4Age 5Age 6
January1,480100%71%58%52%49%47%46%
February1,620100%69%55%50%47%45%—
March2,910100%54%38%31%28%——
April1,730100%72%60%54%———
May1,690100%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

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.

Frequently asked questions

Can I do cohort analysis in Google Analytics instead of SQL?

For a first look, yes: the cohort exploration groups users by an acquisition event and follows them by a chosen metric, and it is quick to set up. It works on analytics users rather than customers, counts activity as the platform defines it, and is limited by the property's data retention and sampling, so it is best treated as a preview. Decisions about lifetime value and payback should rest on a table built from order or subscription data.

How many periods of history do I need before cohorts are useful?

Enough for the oldest cohorts to show whether the curve flattens, which for monthly cohorts in most businesses means several months and ideally a year. Young cohorts still tell you something about early retention, and comparing them with older cohorts at the same age is valid from the second month. What you cannot do with short history is project lifetime value, because the tail of the curve has not been observed.

Why does my blended retention rate improve while every cohort looks the same?

Because the blended rate is a weighted average across cohorts of different ages, and acquisition growth adds young customers who are, by definition, mostly still active. The rate rises without any customer behaving differently. The cohort table removes that effect by comparing customers of the same age, which is why the retention KPI should be a specific cell of the table, such as month-three retention for the latest complete cohort.

Terms used on this page

Where this page is used

Part of these learning paths

Also related

Sources

  1. Google Analytics Help — Google Analytics Help

Related