Cohort Analysis: How to Do It and What to Look For

Cohort analysis groups customers by when they started, or by how they were acquired, and then follows each group over time so you can see whether behavior is improving or deteriorating. It is the most reliable way to measure retention, repeat purchase, lifetime value and payback, because it avoids the trap of blended averages in which new customers mask the decline of old ones. This guide shows how to define a cohort, how to build a retention triangle with a worked example, how to read the table, what to look for, and how to convert it to lifetime value and payback. It is part of unit economics and budgeting and forecasting, supports customer lifetime value and CAC payback period, and leads to customer retention rate and churn rate.

Why averages fail and cohorts work

Suppose a company reports that 40 percent of its customers bought in the last quarter. That number blends brand-new customers, who have just bought, with customers from three years ago. If the company is growing fast, the share of new customers is large and the blended rate looks healthy even if older customers are leaving faster than ever. A cohort view separates the two. It compares customers of the same age with each other, which is the only fair comparison.

Step 1: define the cohort

The most common definition is the month in which a customer first bought or subscribed. You can also define cohorts by:

• Acquisition channel: paid search, paid social, referral, organic.
• First product purchased: which entry product the customer chose.
• Offer: full price versus discount code.
• Geography or store.
• Plan or customer size in a subscription business.

Start with acquisition month, because it is the simplest, then cut by channel and first product to find the drivers.

Step 2: choose the metric

Choose one primary metric per table and keep the others for follow-up. Contribution per customer is the one that connects to payback and lifetime value.

Step 3: build the triangle

A hypothetical brand tracks the percentage of each monthly cohort that placed an order in each month after acquisition. Month 0 is the month of the first order.

Each row is a cohort, each column is a month of age, and the table is triangular because younger cohorts have fewer months of history. In absolute terms, the January cohort had 0.12 × 1,000 = 120 customers active in month 3, the February cohort had 0.10 × 1,200 = 120, and the March cohort had 0.08 × 1,100 = 88.

Step 4: read it three ways

Read across a row to see the shape of one cohort. A healthy retention curve falls steeply in the first few months and then flattens, because the customers who remain are loyal. In the January cohort, the drop from 22 to 15 percent between months 1 and 2 is seven points, while the drop from 10 to 9 percent between months 4 and 5 is one point. The curve is flattening, which is the pattern you want.

Read down a column to compare cohorts at the same age. At month 1, retention fell from 22 percent (January) to 20, 18, 17 and 16 percent. At month 3 it fell from 12 to 10 to 8. Each new cohort is weaker than the one before at the same age. That is a quality problem in acquisition, in product experience or in both, and it would be invisible in a blended average that was being propped up by growth in customer numbers.

Read along a diagonal to find calendar effects. A diagonal runs through cells that share a calendar month. If every cohort shows a drop in the same month, something happened to the business in that month: a price rise, a stockout, a site change or a competitor promotion. Cohort effects (poor customers) show up down columns, and calendar effects show up along diagonals.

Step 5: look for specific patterns

Step 6: turn the cohort into LTV and payback

Convert percentage active into cumulative contribution. Suppose each active customer places on average 1.1 orders in an active month and contribution per order is $22. Then contribution per original customer in month n is the active percentage × 1.1 × $22 = active % × $24.20. For the January cohort, the first six months give:

Check: 0.22 × 24.20 = 5.324; 0.15 × 24.20 = 3.63; 0.12 × 24.20 = 2.904; 0.10 × 24.20 = 2.42; 0.09 × 24.20 = 2.178. Cumulative: 22 + 5.32 = 27.32; plus 3.63 = 30.95; plus 2.90 = 33.85; plus 2.42 = 36.27; plus 2.18 = 38.45.

If CAC is $40, the January cohort has not yet paid back by month 5 ($38.45), but it will in month 6 if it adds even $1.55 (9 percent × 24.20 = 2.18 would be enough). The younger cohorts, with lower retention, will take longer. This is what a cohort table gives you that a blended average cannot: a payback date for each cohort. See CAC payback period.

Building it in a spreadsheet or with SQL

In a spreadsheet, put the cohort month in the first column, months since acquisition across the top, and use COUNTIFS or SUMIFS on a customer-order table that has a cohort month and a months-since column. In SQL, the pattern is to find each customer's first order month and then count activity by months since that month. This example uses a common syntax; adjust the date functions to your database.

WITH first_orders AS (
  SELECT customer_id,
         DATE_TRUNC('month', MIN(order_date)) AS cohort_month
  FROM orders
  GROUP BY customer_id
)
SELECT f.cohort_month,
       DATE_DIFF('month', f.cohort_month, DATE_TRUNC('month', o.order_date)) AS months_since,
       COUNT(DISTINCT o.customer_id) AS active_customers,
       SUM(o.contribution) AS contribution
FROM orders o
JOIN first_orders f ON f.customer_id = o.customer_id
GROUP BY 1, 2
ORDER BY 1, 2;

Divide active customers by the cohort's month 0 count to get the retention percentage, and divide the running sum of contribution by the cohort's size to get cumulative contribution per customer.

Common mistakes

• Cohorts that are too small. A cohort of 40 customers produces noisy percentages. Combine months or use quarters.
• Comparing cohorts at different ages. Always compare the same month of age.
• Ignoring seasonality. Cohorts acquired in a peak month behave differently. Compare with the same month last year as well as the previous month.
• Using revenue instead of contribution in the cumulative view.
• Reading a triangle without segmenting. Overall tables hide channel differences.
• Forgetting the youngest cohorts are incomplete and treating blank cells as zeros.
• Not acting on the findings. A cohort table that is reviewed monthly and changes nothing is a report, not a tool.

A monthly routine

1. Update the table with the latest month of data.
2. Check the latest cohort against earlier ones at the same age.
3. Check each diagonal for a calendar event.
4. Review the three biggest channels or entry products separately.
5. Update payback estimates by cohort.
6. Write down one action, such as changing an onboarding email, an offer or a channel budget.

Operations show up in the cohorts

Cohorts reveal operational problems. If customers acquired during a stockout month show lower repeat, availability was part of their experience. If cohorts that received late deliveries churn faster, fulfillment is a retention lever. For consumer product companies, the cohort table is a useful bridge between marketing and supply chain conversations. See demand planning and inventory optimization.

Beware small samples

Percentages from small cohorts mislead. Imagine a cohort of 50 customers in which 8 are active in month 3, compared with 6 in another cohort of 50. The percentages are 16 and 12, a four-point gap that looks like a trend. The difference is two customers. Before drawing conclusions, combine months into quarters, use confidence ranges where your team has the skills, or wait for another month of data. Segment-level tables are especially prone to this problem, so set a minimum cohort size, such as 200 customers, below which the table is shown but not used for decisions.

FAQs

What is cohort analysis?

A method that groups customers by a shared starting point, such as the month of first purchase, and tracks their behavior over time to compare groups of the same age.

How do I do a cohort analysis in Excel?

Create a table with cohort month in rows and months since acquisition in columns, then use COUNTIFS or SUMIFS on your order data to fill each cell, and divide by the cohort size for percentages.

What is a good cohort retention curve?

One that falls early and then flattens at a level that supports your payback and lifetime value targets. The best test is whether newer cohorts match or beat older ones at the same age.

How big should a cohort be?

Large enough that a few customers do not swing the percentage. If monthly cohorts are small, use quarterly cohorts.

How is cohort analysis different from a retention rate?

A retention rate is a single number for a period. A cohort analysis shows how retention changes with customer age and across acquisition periods.