Cohort analysis: how to read the table nobody reads

A cosmetics brand I work with closed the first half of the year at 22% repeat purchase within 30 days. On paper, a lukewarm number. The team spent three weeks testing subject lines, win-back coupons and send frequency chasing those missing points.

The problem was never the email. It was that 22% described no actual customer. It was the average of five groups: four of them ran between 24% and 25%, and one, March, ran at 16%. March had been the month of an aggressive coupon push that nearly tripled new customer intake.

An average retention rate is the kind of number that looks like an answer and works like a blanket. It covers everything and explains nothing. Cohort analysis pulls the blanket off.

What is cohort analysis?

Cohort analysis is the practice of splitting customers into groups that share a starting event within the same time window, usually the month of their first purchase, and then tracking each group across the periods that follow. Instead of one retention rate for the whole base, you see the curve of each intake separately.

The term comes from epidemiology, where a cohort is a closed group followed over time. The logic carried over to business untouched: rather than photographing different groups in different months and comparing the photos, you follow the same group and watch what happens to it. Each acquisition month becomes its own intake, with its own quality.

Why average retention misleads

Back to the cosmetics brand. Five monthly intakes of new customers, January through May, and how many of each came back in the following months.

Cohort	New customers	Repeat in M+1	In M+2	In M+3
January	400	96 (24%)	68 (17%)	56 (14%)
February	520	130 (25%)	88 (17%)	68 (13%)
March	900	144 (16%)	90 (10%)	72 (8%)
April	610	146 (24%)	104 (17%)	in progress
May	580	145 (25%)	in progress	in progress

The aggregate math: 3,010 new customers in the period and 661 repeat buyers in the month after signup, or 22%. No cohort is 22%. Four sit between 24% and 25%, and March alone drags the average down because it came in at more than double January's volume. An average is not typical behaviour. It is nobody's behaviour, weighted by the volume of whoever got it wrong.

It is worth finishing the calculation, because it changes the conversation about that campaign. March brought in 380 more new customers than February. Three months later, 72 of March's customers were still active against 68 of February's. Those 380 extra customers produced four active ones. The entire media budget of that month bought volume that evaporated, and a 22% average hid it for six months.

This is why average retention and aggregate churn rate work as dashboard indicators, not diagnostics. They tell you something changed. They do not tell you what, or when it walked in the door.

Building a cohort: four decisions

Building a cohort table means choosing four things. Get any of them wrong and you produce a handsome, useless chart.

1. The event that defines entry. First purchase is the retail default. In software it is usually activation rather than signup, because people who create an account and never use it poison the whole intake.
2. The granularity. Monthly works for most businesses. Weekly only makes sense with a short purchase cycle and high volume. Quarterly is the refuge of anyone with few accounts per month.
3. The event that counts as a return. Any purchase? A purchase above a minimum value? A login? The choice moves the number by tens of points, so write the definition down somewhere before someone quietly changes it mid-year.
4. The metric inside the cell. Percentage of active customers is the most common, but cumulative revenue per intake usually answers the question finance is actually asking.

The three directions you can read the table

Here is the part almost no guide covers. A cohort table does not have one reading. It has three, and each direction answers a different question.

Row: how does this cohort age?

Follow January left to right: 24%, 17%, 14%. The loss is brutal in the first month and slows afterwards. That is normal, and it is the most useful thing the row tells you: there is a floor, the group of people who genuinely became customers. If your curve never flattens, the product has no retention, only inertia. If it flattens early and high, you have a recurring business without selling a subscription, and your LTV calculation finally rests on something real instead of a guess.

Column: did acquisition quality change?

Read the M+1 column top to bottom: 24%, 25%, 16%, 24%, 25%. Every cell in a column holds different cohorts at the same age, so the comparison is fair. One cell out of line, like March, points straight at what happened in acquisition that month: a new channel, an aggressive coupon, a promotion that attracted discount hunters. The column is the best detector of media that buys bad volume.

Diagonal: did something happen that month?

The diagonal is the reading nobody does and the one that solves the hardest cases. Calendar May contains, at the same time, January's M+4, February's M+3, March's M+2 and April's M+1. If all those cells drop together, the problem belongs to no cohort: it belongs to the month. Broken checkout, stockout, a shipping price increase, a policy change, a long holiday. A dip that shows up across rows and columns at once is almost always a calendar event disguised as a retention problem.

Customer cohorts or revenue cohorts?

The two versions of the same table tell different stories, and picking only one is a common mistake.

	Customer cohort	Revenue cohort
What the cell shows	% of the intake still active	revenue generated by the intake
Best for	retail, apps, repeat purchase	SaaS, subscriptions, B2B
Alarm it raises	shrinking base	shrinking revenue on a stable base
Blind spot	ignores that survivors spend more	hides concentration in a few accounts

In subscription businesses the two routinely point in opposite directions: the base drops 3% a month while cohort revenue climbs, because the people who stay upgrade. That gap between losing customers and losing money is exactly what net revenue retention measures, and it is one of the SaaS metrics investors look at first. Track only the customer cohort and you will celebrate a problem or panic over good news.

Where cohort analysis breaks

Small intakes. With 40 customers in a cohort, two extra buyers move the number five percentage points. Below roughly 100 customers per period, group into quarters or accept that the table is showing noise dressed as a trend.

Long purchase cycles. If the product is repurchased every six months, a monthly cohort returns a wall of zeros and an unnecessary scare. Match granularity to the real repurchase cycle, which you find in the median gap between orders, not in what the team assumes it is.

Shifting mix. Changing channels changes who walks in. New cohorts can be worse without anything in the product getting worse. When that happens, split the cohort by source channel before concluding anything about retention.

Tool limits. The cohort exploration in Google Analytics 4 displays at most 60 cohorts and, when you apply a breakdown dimension, shows only its top 15 values, per the tool's own documentation. That is enough to start and to answer top-level questions. To cross intake with channel, plan and product category at once, you will need the raw data outside it.

What to do with what the table shows

A cohort table is only worth the effort if it turns into a decision. In practice, three decisions come out of it regularly.

The first is about media: cohorts with below-standard M+1 point to channels or creatives buying disposable volume. Cut budget there before scaling. The second is about onboarding: if the drop from M0 to M+1 is always the steepest, the money sits in the customer's first month of life, not in reactivating people who already left. The third is about sales channel. The E-Consumidor 2026 study, run by Nuvemshop with Opinion Box in Brazil, found that 46.5% of consumers come back to buy again on a brand's own store against 30% on marketplaces. If your marketplace cohorts nosedive while your own-store cohorts flatten out, the pattern is not an accident, and the conclusion about where to invest changes.

Notice that none of these decisions shows up in an average rate. All of them show up once you separate the intakes.

Frequently asked questions

Is cohort analysis the same as cohort study?

They share the method, not the field. The cohort study comes from epidemiology and follows a closed group to observe health outcomes. Business cohort analysis borrows the same follow-the-group logic and applies it to purchases, activations or logins.

How do you build a cohort analysis in Excel?

You need a table with customer, first purchase date and the date of each order. Add one column with the month of the first purchase and another with the difference in months between the order and that date. Drop both into a pivot table, intake in rows, difference in columns, counting distinct customers. Those two calculated columns do the work, not the pivot.

Can you do cohort analysis in Power BI?

Yes, and the logic is identical: a DAX measure counting distinct customers by intake and period index. The hard part remains modelling the first purchase date, which has to stay fixed per customer and must not be recalculated inside the period filter.

What is a good retention rate per cohort?

It varies so much by sector that external benchmarks usually hurt more than they help. The useful reference is internal: your own cohort from six months ago, at the same age. What matters is whether the curve is flattening higher than it used to.

Is cohort analysis useful without a subscription model?

It is, and sometimes it pays off more. In retail and services, cohorts reveal whether the second purchase actually exists or whether the business survives by bringing in new people every month, which is the most expensive and least visible situation on a revenue spreadsheet.

The table solves nothing on its own

The value of cohort analysis is not in building the table, it is in looking at it with a question in mind. Build one once, screenshot it and never return, and you have gained a slide. Revisit the same table every month, always asking what changed in the newest intake compared with the previous one, and you have gained an alarm that catches acquisition problems months before revenue complains. It is a cheap habit with a disproportionate payoff.

The obstacle is rarely conceptual. Order data lives in the ecommerce platform, source data in the ad accounts and signup data in the CRM, and joining the three is tedious enough that the analysis keeps getting postponed. Sherlok exists to shorten that path: connect the sources once, then ask in plain language, something like "compare 30-day repeat purchase across the last six monthly cohorts by acquisition channel", and get the analysis back instead of a queue of data pulls. The table is still yours. What changes is the time between the question and the answer.