Measuring Customer Retention: Cohorts and Data Requirements
Build a customer retention cohort table from order data, define a returning customer, avoid common calculation traps, and learn when GA4 is not enough.
8 min readBranislav Mateas
Copy post as Markdown
On this page
A single repeat rate cannot show whether customers acquired this spring return as often as customers acquired in winter. A cohort table can. It groups customers by the date of their first order, then tracks each group over the same amount of time.
The table is only as good as two choices made before anyone opens a report: what counts as a return, and which system can see one. This article works through both, using a hypothetical shop.
If the immediate question is why GA4 shows almost every buyer as new, start with this diagnostic. This article picks up where that check ends, with measuring return behaviour over time.
Define a returning customer before counting
"Returning customer" sounds obvious until two people calculate it differently. Write down four rules first.
Who is one customer? Decide how to merge guest checkouts, changed email addresses and duplicate accounts. Use one stable customer ID in the analysis.
Which order counts? Decide how to treat cancelled orders, full refunds, partial refunds and exchanges. A customer whose only second order was cancelled has not returned. A partial refund usually still counts as a purchase, but the rule should be explicit.
What is a separate decision to buy? Two orders on the same day may be a forgotten item. An automatic subscription renewal is not the same behaviour as a customer choosing to buy again. Report renewals separately.
What makes an order the first? Use the full order history, even when the analysis covers one year. An export that starts in January makes a loyal 2024 customer look new.
Then choose the window from the product's buying cycle. A coffee shop may read 30-day windows. A mattress shop may need yearly ones. A low rate over a period shorter than the normal buying cycle says little about retention.
The numbers below are hypothetical. The shop exports valid orders on 30 June 2026. Each cohort contains customers whose first valid order fell in that month. Each cell shows how many of them ordered again in that window after their first order, and what share of the cohort that represents.
First order
Customers
Days 1-30
Days 31-60
Days 61-90
Days 91-120
January
400
48 (12.0%)
36 (9.0%)
28 (7.0%)
24 (6.0%)
February
320
32 (10.0%)
24 (7.5%)
16 (5.0%)
12 (3.8%)
March
250
20 (8.0%)
15 (6.0%)
10 (4.0%)
April
200
14 (7.0%)
8 (4.0%)
May
150
9 (6.0%)
The blank cells are deliberate. A cell is complete only when the cohort's last possible customer has had the full window. A customer who first bought on 31 March reaches day 120 on 29 July, after the export date, so March has no value in the last column. A customer who first bought on 31 May reaches day 30 exactly on 30 June, so May gets one cell.
Read down a column, not across a row. Across a row, the rate falls in every cohort, which is normal because the same people are getting further from their first order. Down the first column, the rate falls from 12.0% to 6.0% across five cohorts. That is the finding worth investigating.
The table does not explain the fall. Possible causes include a change in acquisition channels, a discount that attracted one-time buyers, or seasonality. Split the cohorts by first-order channel, product or discount use to test them. Small differences also need caution: in the May cohort, one percentage point is only 1.5 customers.
Three calculation traps
Summing windows as if they were unique customers. A customer can appear in several windows. January's four cells sum to 136 customers, which would suggest 34.0% retention by day 120. Suppose only 104 different January customers ordered again in that time. The correct cumulative figure is 104 / 400 = 26.0%. Cumulative retention needs its own distinct count. For this hypothetical cohort, the sequence could be 48, 72, 90 and 104 customers, or 12.0%, 18.0%, 22.5% and 26.0%.
Using the wrong denominator. Across all five cohorts, 83 customers ordered in days 31-60. Dividing by all 1,320 customers gives 6.3%. That is wrong, because May customers have not finished this window. The eligible base is 400 + 320 + 250 + 200 = 1,170 customers, so the correct figure is 83 / 1,170 = 7.1%.
Letting an average hide the trend. The pooled rate for days 1-30 is 123 / 1,320 = 9.3%. It is correct, and it hides the drop from 12.0% to 6.0%. Keep the cohort rows visible next to any summary.
Build the table from an order export
An order export with a customer ID, order date and status is enough. The query below uses BigQuery syntax. It has not been run against a real shop, and the table name, columns and status values are placeholders. Check it against a few customers by hand first.
WITH valid AS (
SELECT customer_id, DATE(ordered_at) AS order_date
FROM orders
WHERE status NOT IN ('cancelled', 'fully_refunded')
AND DATE(ordered_at) <= DATE '2026-06-30'
),
first_order AS (
SELECT customer_id, MIN(order_date) AS first_date
FROM valid
GROUP BY customer_id
),
repeat_order AS (
SELECT v.customer_id, f.first_date,
CAST(CEIL(DATE_DIFF(v.order_date, f.first_date, DAY) / 30) AS INT64) AS window_no
FROM valid AS v
JOIN first_order AS f ON v.customer_id = f.customer_id
WHERE v.order_date > f.first_date
)
SELECT DATE_TRUNC(first_date, MONTH) AS cohort, window_no,
COUNT(DISTINCT customer_id) AS customers
FROM repeat_order
WHERE DATE_ADD(LAST_DAY(first_date), INTERVAL window_no * 30 DAY) <= DATE '2026-06-30'
GROUP BY cohort, window_no
ORDER BY cohort, window_no;
Window 1 means days 1-30, window 2 means days 31-60, and so on. Same-day repeat orders are excluded by the > condition. The outer filter keeps only complete cells. Cohort sizes come from a separate count of first_order by month, and a window with no returning customers returns no row, so show it as zero rather than blank. The COUNT(DISTINCT ...) matters because a join can multiply rows.
The same steps work in a spreadsheet: add a first-order date per customer, calculate days since that date for each later order, and count distinct customers per cohort and window.
When GA4 is enough, and when it is not
GA4's cohort exploration can include users by first touch, any event, any transaction or a chosen event, and return them on any event, any transaction or a chosen event. It is useful for one specific question: do these identified users come back to the site or app?
Three documented details change how its table should be read:
Time frames. Monthly granularity runs from the start to the end of each month, and weekly runs from Sunday to Saturday. These are calendar periods, not elapsed days as in the table above.
Calculation types. Standard counts users returning in that period. Cumulative counts users returning in any period. Rolling counts users returning in that period and all previous periods, which makes it stricter than cumulative.
History.The retention setting applies to explorations and allows up to 14 months of user-level data, so it limits long cohort analysis. Standard reports are not affected.
The larger limit is identity. A user in GA4 is an identifier, and the cohort treats a lost identifier as a lost customer. Suppose 18 of January's 48 returning customers from the table reorder in February on a new browser. GA4 would see 30 returners, or 7.5% rather than 12.0%. It would also count those 18 people as new, so February's cohort grows to 338 and its first-window rate falls from 10.0% to 32 / 338 = 9.5%. One lost identifier distorts two cohorts. This is an illustration of the mechanism, not a measured error rate.
Use this to choose a source:
Situation
Source
Question is about return visits or feature use, within the retention period
GA4 exploration, labelled as identified users
Signed-in product where most activity happens after login
GA4 with User-ID, from the date it was added
Guest checkout, or repeat orders on another device
Shop backend or CRM
Refunds and cancellations change the answer
Shop backend or CRM
Orders also come from phone, store or marketplaces
Backend or CRM that holds all channels
Cohort needs more than 14 months of history
Backend, or BigQuery if linked early enough
Cohort must show revenue or margin kept
Backend or finance data
User-ID helps only partly. Earlier data is not reprocessed, and events from visitors who are not signed in carry no user ID. Consent adds another gap: Google states that without consent, events are not associated with a persistent user identifier.
BigQuery export improves access, not identity. It provides raw events and removes the exploration limits, but the export page describes the data as observed with no modeling, and cookieless pings appear in it without becoming known customers. The inference here is that a person who appears under a new user_pseudo_id in a new browser stays two identifiers in BigQuery unless your own customer ID connects them. The documentation also does not describe loading events from before the link, so plan as if the history starts at that date.
A practical split follows. Count customers, orders and retention in the system that owns the orders. Use GA4 for what happened on the site before the purchase. To add that context, join on transaction_id, which Google requires on purchase events, and report what share of orders found a match. A cohort split by channel covers only that matched share, so a low match rate limits what the split can show.
