What is cohort analysis: how to build and read a cohort report

What a cohort is

A cohort is a group of people who did the same thing in the same period: everyone who first bought in September, everyone who signed up in the first week of November, everyone who installed the app from one campaign.

There are two kinds:

• Acquisition cohorts group people by when they started: first visit, sign-up or first order. This is what most cohort reports mean, and what this guide uses.
• Behavioural cohorts group people by something they did: used a feature, bought a category, saw a new checkout. They are how you test whether that action changes what people do afterwards.

How to read a cohort table

A cohort table has one row per cohort and one column per period since the cohort started. Each cell answers: of the people who started in this period, how many did the thing again N periods later?

Read it three ways:

1. Along a row: how one cohort decays. A steep drop after the first period is normal; what matters is where the curve flattens, because those are your loyal customers.
2. Down a column: whether newer cohorts do better or worse at the same age. If month-3 retention falls from cohort to cohort, something about how you acquire customers changed.
3. Along a diagonal: cells on the same diagonal happened in the same calendar period. A dark or pale diagonal is a seasonal event or a promotion, not a property of the cohorts.

The table also tells you when to act. If most cohorts stop buying after the second month, a reminder or offer in month two reaches people before they are gone.

Choices that change the number

The same orders produce different cohort tables depending on four decisions, and whoever asked for the report rarely knows they were made:

Cohort retention analysis in SQL runs each of these choices on real data, on the Google Merchandise Store sample and on the demo shop behind [Demo] E-commerce, and prints the results: month-one retention of 3.8% on calendar months against 2.8% on rolling 30-day windows, 6.9% when “that month or later” counts, and 9.0% with every placed order against 7.6% with completed orders only. Write the four choices under any cohort table you share.

A fifth choice is how long you wait before you judge. On the demo shop behind the public [Demo] E-commerce project (synthetic data), we grouped customers whose first completed order came in January–June 2025 by the channel of that order, and measured the share who ordered again:

At 90 days, customers from paid ads look a third less loyal than customers from organic search. At a year, the gap is three points, and email, the weakest at 30 days, comes out on top, though with 67 customers that lead is within noise. A cohort table read too early does not just give a smaller number; it can rank the channels in the wrong order.

Cohort analysis in Google Analytics 4

Google Analytics 4 has a built-in cohort report in Explorations. Use it for behaviour on your site or app: how many users who first arrived in a given week came back.

1. Open Explore, then the Template gallery, and choose Cohort exploration.
2. Set the four core settings (Google Analytics Help, Cohort exploration, checked October 2026):
   • Cohort inclusion: the condition that puts a user in a cohort, for example first touch.
   • Return criteria: what counts as coming back, for example any event or a purchase.
   • Cohort granularity: daily, weekly or monthly.
   • Calculation: standard counts users who returned in that period only; rolling counts users who returned in that period and every period before it; cumulative counts users who returned in any period up to it.
3. Optionally add a breakdown dimension, such as first user source, and choose the value (metric) to show.

Google lists three limits: a cohort exploration shows at most 60 cohorts, a breakdown shows the top 15 values of its dimension, and demographic dimensions are subject to thresholding.

The larger limit is what GA4 knows. It sees sessions and events, not your order statuses, refunds or margin, so its cohort table measures coming back, not buying again. The two are far apart. On the demo shop behind the public [Demo] E-commerce project (synthetic data), we took the customers whose first completed order fell in January–June 2025 and counted, for each 30-day month after it, who returned to the site and who ordered again:

WITH firsts AS (
  SELECT customer_id, MIN(order_date) AS first_order
  FROM `owox-demo.ecommerce.orders`
  WHERE status = 'Completed'
  GROUP BY customer_id
  HAVING MIN(order_date)
    BETWEEN '2025-01-01' AND '2025-06-30'
),
activity AS (
  -- a return visit, and a repeat order,
  -- each tagged with the 30-day month it fell in
  SELECT f.customer_id, 'visit' AS kind,
    DIV(DATE_DIFF(s.date, f.first_order, DAY)
        - 1, 30) + 1 AS m
  FROM firsts AS f
  JOIN `owox-demo.ecommerce.sessions` AS s
    ON s.customer_id = f.customer_id
   AND s.date > f.first_order
  UNION ALL
  SELECT f.customer_id, 'order',
    DIV(DATE_DIFF(o.order_date, f.first_order,
        DAY) - 1, 30) + 1
  FROM firsts AS f
  JOIN `owox-demo.ecommerce.orders` AS o
    ON o.customer_id = f.customer_id
   AND o.status = 'Completed'
   AND o.order_date > f.first_order
)
SELECT m AS month,
  ROUND(100 * COUNT(DISTINCT IF(kind = 'visit',
    customer_id, NULL))
    / ANY_VALUE(n), 1) AS returned_to_site_pct,
  ROUND(100 * COUNT(DISTINCT IF(kind = 'order',
    customer_id, NULL))
    / ANY_VALUE(n), 1) AS ordered_again_pct
FROM activity
CROSS JOIN (SELECT COUNT(*) AS n FROM firsts)
WHERE m BETWEEN 1 AND 6
GROUP BY m
ORDER BY m

A visit cohort says roughly half your customers are retained every month; an order cohort says one in eleven. The exact levels, and the slight rise in visits over time, come from a synthetic dataset; the gap between the two kinds of cohort is the point. Both are true, of the same people. If the question is revenue, retention or lifetime value, build the cohorts from orders; use GA4’s cohort exploration for engagement, and label which one a table shows.

Cohort analysis in Google Sheets

A spreadsheet is enough for a few thousand rows. Start from a table with three columns: the cohort period (week or month of first purchase), the activity period (week or month of the order) and the metric (orders, customers or revenue).

Then build the table one of two ways.

With a pivot table. Select the data, choose Insert → Pivot table, put the cohort period in Rows, the activity period in Columns and the metric in Values (summarised by SUM). Add conditional formatting to colour the cells.

With formulas, if you want the layout under your control. On a new sheet, with the source data on a sheet named Data:

• Cohort periods down column A, in cell A3: =UNIQUE(Data!A2:A)
• Activity periods across row 2, in cell B2: =TRANSPOSE(UNIQUE(Data!B2:B))
• The metric for each cell, in B3 and filled across and down:

=SUMIFS(Data!$C:$C,
  Data!$A:$A, $A3,
  Data!$B:$B, B$2)

To show retention as a percentage, divide each cell by the cohort’s first-period value. To filter by source or campaign, add those columns to Data and add a matching criteria pair to SUMIFS.

Where the source data comes from matters more than the formula. A file exported by hand is out of date the day after. If your orders already sit in your warehouse, the spreadsheet can be fed from a scheduled report instead of an export.

Cohort analysis in your warehouse

Once orders live in a warehouse, the cohort table is one SQL query: find each customer’s first-order month, count which later months they ordered in, and divide by the cohort size. It has no row limit, it reads order status and revenue directly, and it is the same query every time it runs. The full query, with each of the four choices above as a visible line, is in cohort retention analysis in SQL.

The question that query answers can also be asked in a sentence, once orders are published as a Data Mart in a data model such as the E-commerce model:

“Group customers by the month of their first completed order and show the share who placed another completed order in each of the following six months.”

The answer is only as right as the Data Marts behind it. But the four choices are made once, by the analyst, and the next person who asks gets the same table.

What to do with cohort analysis

• Time your retention offers. Send the reminder in the period just before most cohorts drop off.
• Compare acquisition channels. Break cohorts down by first source. A channel with cheap first orders and no second order is more expensive than it looks; customer lifetime value by channel puts a number on it.
• Test changes over the long run. A standard A/B test measures the first conversion. A behavioural cohort of people who saw the new version, compared with those who did not, shows whether the effect lasts.

• Find your best customers. Combine cohorts with RFM analysis to see which cohorts produced the customers who still buy often and spend the most.

Data AnalyticsWhat is Data Analytics? How Data Becomes an Insight You Can TrustOctober 14, 2026→GA4Google Analytics 4: What Changed and What Comes NextJuly 25, 2025→Marketing AnalyticsWhen to Adopt OWOX as a Marketing LeaderMay 27, 2025→

See all articles →