How to build a cohort analysis chart

Your aggregate churn number is lying to you

You're reporting 5% monthly churn to your board. That number is accurate. It's also useless.

Aggregate churn blends together wildly different customer behaviors into a single percentage. A company could have improving product-market fit, with newer cohorts retaining at 90%, while the aggregate stays stubbornly at 5% because old cohorts from a year ago are still bleeding out. Or the reverse: aggregate churn looks stable while each new cohort actually retains worse than the last. The growing base just masks the decline.

This is the problem with averages. They smooth away the signal. You can't tell if things are getting better, getting worse, or staying the same. You definitely can't tell why.

Cohort analysis is how you see what's actually going on. It breaks your customers into groups based on when they signed up and tracks each group separately over time. Instead of one number, you get a map of your business that shows where retention is strong, where it breaks down, and whether your product changes are making a difference.

What a cohort analysis chart actually shows

Imagine a grid. Each row is a group of customers who signed up in the same month. Each column is how many months have passed since they signed up. Each cell tells you what percentage of that original group is still around.

That's it. That's the whole concept.

Notice the triangle shape. Newer cohorts haven't lived long enough to fill later columns. That's normal, and it's actually useful: it means you can compare the same time periods across cohorts. In this example, Month-2 retention improves from 61% (Jan) to 73% (Apr). Something is working.

People use two main formats for cohort analysis. The triangle table (shown above) is great for precise numbers and spotting trends across cohorts. Retention curves overlay each cohort as a line on the same chart, which makes it easier to visually compare the shape of retention over time.

One important distinction: logo retention counts customers (how many people are still subscribed), while revenue retention counts dollars (how much MRR that cohort still generates). Use logo retention for product decisions. Use revenue retention for financial planning. They can tell very different stories, because a few expanding enterprise accounts can mask broad logo churn.

Build one from your data

If you've never built a cohort chart by hand, you don't actually understand your retention data. Analytics tools are great for ongoing monitoring. But doing it manually at least once forces you to confront exactly what your data does and doesn't tell you.

With SQL

If you have a database with subscription or activity data, here's the core query pattern. The details will vary depending on your schema, but the structure stays the same.

-- Step 1: Get each user's cohort (signup month)
WITH cohorts AS (
  SELECT
    user_id,
    DATE_TRUNC('month', created_at) AS cohort_month
  FROM users
),

-- Step 2: Get each user's active months
activity AS (
  SELECT
    user_id,
    DATE_TRUNC('month', activity_date) AS active_month
  FROM user_activity
  GROUP BY 1, 2
)

-- Step 3: Calculate retention per cohort per period
SELECT
  c.cohort_month,
  DATEDIFF('month', c.cohort_month, a.active_month) AS months_since_signup,
  COUNT(DISTINCT a.user_id) AS active_users,
  COUNT(DISTINCT a.user_id)::FLOAT /
    MAX(cohort_sizes.cohort_size) AS retention_rate
FROM cohorts c
JOIN activity a ON c.user_id = a.user_id
JOIN (
  SELECT cohort_month, COUNT(*) AS cohort_size
  FROM cohorts
  GROUP BY 1
) cohort_sizes ON c.cohort_month = cohort_sizes.cohort_month
GROUP BY 1, 2
ORDER BY 1, 2;

The denominator matters. Always divide by the original cohort size, not the previous month's active count. If your January cohort had 200 signups and 100 are active in month 3, that's 50% retention, regardless of how many were active in month 2. Using the previous month as denominator gives you period-over-period survival rate, which is useful for different questions but is not the same thing.

With a spreadsheet

This is the simplest approach and it works surprisingly well.

1. Export your data. You need two columns at minimum: customer signup date and either a cancellation date or a "still active" flag.
2. Group by signup month. Add a column that extracts the month from the signup date. This is your cohort label.
3. Count active users per period. For each cohort, count how many customers were still active at month 1, month 2, month 3, and so on. Divide each count by the original cohort size.
4. Build the grid. Rows are cohort months, columns are period numbers. Fill in the percentages.
5. Add conditional formatting. Color the cells from green (high retention) to red (low retention). This turns the spreadsheet into a heatmap where patterns jump out visually.

Reading the chart: what the patterns mean

Building the chart is the easy part. Knowing what to look for is where it gets useful.

The questions your cohort chart answers

Segment your retention curves by ICP

A single retention curve for all customers is better than an aggregate churn number. But it still averages away the most actionable insight: which types of customers stick around and which don't.

The real power of cohort analysis comes when you stop grouping by signup month and start grouping by customer type. Plot a separate retention curve for each segment, overlay them on the same chart, and the differences will tell you more about your business in five minutes than a month of staring at dashboards.

Here's what that looks like. Same product, five different customer segments, wildly different retention stories.

Same product. Five completely different businesses hiding inside the same aggregate number.

The enterprise annual customers (blue line) barely churn at all. They integrated via API, they're on annual contracts, and switching costs are high. 83% retention at month 12 is excellent. You want more of these.

Mid-market teams (teal) lose about 12% in the first month, then the curve flattens out nicely. The activation problem is real but contained. If you could get more of that first-month cohort to stick, you'd move this line significantly.

Now look at the two SMB lines. Integrations are the dividing line. SMBs who connect at least one integration (amber) retain at 46% after a year. SMBs who don't (red) crater to 19%. That's a 2.4x difference in retention, and the only variable is whether they connected an integration. This is the kind of insight that changes your entire onboarding strategy overnight.

The self-serve trial converts (dashed gray) are the worst performers. 90% gone after 12 months. If you blended all five segments into one line, you'd see maybe 40-45% overall retention. Decent, you'd think. But the reality is you have two healthy segments, one fixable segment, and two that are dragging the whole average down. Different problems, different solutions.

How to segment

The best segmentation dimensions depend on your business, but these are reliable starting points:

• Company size or plan tier. Enterprise, mid-market, and SMB customers almost always have different retention profiles. If you're not segmenting by this, you're flying blind.
• Acquisition channel. Organic search signups often retain differently than paid ad converts or Product Hunt launch spikes. Knowing this tells you which channels produce customers worth acquiring.
• Activation behavior. Did they complete onboarding? Connect an integration? Invite a teammate? Segment by these behaviors and you'll see exactly which actions predict long-term retention.
• Contract type. Monthly vs. annual billing creates fundamentally different retention dynamics. Annual customers have fewer decision points, which means fewer chances to leave.
• Use case or job-to-be-done. If your product serves multiple use cases, each one may have a different natural retention rate. A project management tool used for client work retains differently than the same tool used for internal to-do lists.

Start with two or three segments. You don't need to go granular on day one. The first time you overlay segmented curves on the same chart, you'll probably see a gap wide enough to rethink how you allocate your retention efforts entirely.

Common mistakes that make your cohort chart useless

From chart to action: what to do with what you find

The point of cohort analysis isn't the chart. It's what you do next. Here's how to map common patterns to specific fixes.

If your biggest drop is in month 1 (activation problem)

Most of your users never reach the moment where your product clicks for them. Fix this before anything else, because nothing downstream matters if people leave before getting value.

If retention erodes steadily after month 2 (engagement problem)

Users activate but don't build a habit. They find initial value, then drift away. You need to deepen engagement and make the product stickier.

If there's a cliff at month 12 (renewal problem)

The annual renewal is a decision point where customers actively re-evaluate whether to stay. You need a save strategy for this moment.

If newer cohorts are worse (quality problem)

Your acquisition is pulling in worse-fit customers, or a product change hurt the new-user experience. This is urgent because it means the problem is getting worse with every signup.

If you're losing people to failed payments

This shows up in cohort data as random-looking drops that don't correlate with usage patterns. It's involuntary churn, and it's the easiest type to fix.

Tools that build cohort charts for you

Once you understand the mechanics, you probably want a tool to automate the ongoing work. Here's what to use depending on your setup.

Frequently asked questions