by Format-3

Share article

Cohort Analysis for Product Teams: 3 Cohort Types, SQL & APC Caveats

Cohort analysis groups users by a shared starting point, such as the week they signed up or the channel that brought them in, and tracks them over time to surface retention and activation patterns that aggregate metrics quietly obscure. The payoff is a testable hypothesis: which onboarding change, channel shift or re-engagement nudge will actually move the number you care about? Done well, it turns a vague sense that “retention is bad” into a precise, actionable claim.

TL;DR:

Format-3

Turn Product Data Into Better Experiences

Format-3 helps teams turn complex product challenges into user-centric digital experiences through strategy, design, engineering, and growth.

Explore Format-3

Table of Contents

What cohort analysis is and the main cohort types
Why cohort analysis matters for product and growth teams
Types of cohort analysis and how to choose the right cohort definition
How to run a cohort analysis: step-by-step workflow
Practical SQL and Excel recipes
How to read and interpret cohort charts: common shapes and the actions they imply
Statistical caveats and advanced modelling: heterogeneity, sBG and the APC problem
A worked example: from cohort finding to experiment
Perspective: treat cohort analysis as an ongoing diagnostic, not a one-off report
Turning cohort insight into a working analytics pipeline
FAQ
Sources

What cohort analysis is and the main cohort types
Most dashboards report a single retention percentage for the whole user base, and that number is a quiet liar. It blends people who joined last week with people who joined eight months ago, people from a cheap ad channel with people who arrived through a referral. Cohort analysis fixes this by grouping users who share a starting condition, most often the date they first engaged, and then watching each group’s behaviour unfold across identical intervals since that start. According to Wikipedia’s framing, this is precisely the conceptual trick: group by shared characteristics, typically acquisition time, then track behaviour to reveal patterns an aggregate obscures.

Say a product onboards 1,000 new users in January and another 1,000 in February. Blended retention might look flat month over month, yet the January group could be churning twice as fast as February’s, a difference invisible until you split them apart and watch each one separately.

There are three cohort types worth knowing by name, because the type you choose determines what question you can actually answer:

• Acquisition (vintage) cohorts group users by when they first joined, letting you compare how different “generations” of users behave over equivalent tenure.
• Behavioural cohorts group users by an action they took, such as completing onboarding or making a first purchase, isolating the effect of that action on everything downstream.
• Time-window cohorts group users by a recurring period, such as a calendar month, useful for spotting seasonal or campaign-driven shifts rather than individual-level causes.

The window and granularity you pick change the story the data tells. A daily cohort index exposes early-week drop-off that a monthly view would smooth into invisibility, while a monthly view is better suited to tracking slower habits like weekly active usage over a year. Neither granularity is wrong, but mismatching it to the question you’re asking is one of the most common ways teams draw a confident conclusion from a chart that was never built to answer it.

Why cohort analysis matters for product and growth teams

Average retention curves hide the signal inside the noise. A single blended line can mask the fact that one acquisition channel converts beautifully for two weeks and then haemorrhages users, while another channel starts weak but builds a durable base. Cohort analysis separates these stories so a growth team can act on the right one instead of optimising for a weighted average that describes nobody in particular.

The practical value shows up in three recurring decisions:

• Onboarding changes: a cohort that falls off sharply in its first few sessions points directly at activation friction, not a vague “engagement problem”.
• Channel budget shifts: comparing acquisition cohorts by source reveals which spend is buying durable users and which is buying a short-lived spike.
• Re-engagement experiments: behavioural cohorts (users who did versus didn’t complete a key action) show whether that action is a cause of retention or merely correlated with it.

Cohort tables also tie behaviour to revenue more honestly than a single average ever could, because you can overlay ARPU onto the same cohort index and see whether the users who stay are also the users who pay.

Pro Tip: Before building a single chart, write down the decision the cohort view is meant to inform; a cohort analysis with no attached decision tends to become a report nobody reads twice.

Types of cohort analysis and how to choose the right cohort definition

Choosing a cohort definition is really choosing which question you’re prepared to answer well. Acquisition cohorts suit questions about durability over time, such as whether a product change improved long-term stickiness for everyone who joined after it shipped. Behavioural cohorts suit causal-flavoured questions, such as whether finishing a tutorial predicts staying longer, though correlation here still needs the caution covered later in this guide.

Once you know the type, three practical choices shape the result:

1. Granularity: daily cohorts for fast-moving consumer apps with short usage cycles, weekly for most SaaS products, monthly for low-frequency or high-consideration purchases.
2. Horizon: how many periods forward you track matters more than it seems, since a curve that looks stable at week four can still fall apart by week twelve.
3. Metric and cohort index: decide whether you’re measuring retention rate, active user counts or ARPU, and define the cohort index (period 0, period 1, period 2…) consistently so every cohort is compared on the same clock since its own start, not on the calendar.

Mismatches here are quietly expensive. A team measuring weekly retention with a monthly cohort index will see gentle-looking curves that disguise a genuinely alarming first-week cliff, and act too slowly on a problem that needed a faster response.

How to run a cohort analysis: step-by-step workflow

A cohort analysis is only as trustworthy as the data pipeline underneath it, so the workflow below treats the build as seriously as the interpretation.

1. Check your data requirements first. You need a reliable user identifier, a timestamped event log, and a consistent timezone across every event source; a cohort built on inconsistent identifiers will silently double-count or lose users.
2. Define the cohort identifier. This is typically the earliest date a user performed a qualifying action, such as MIN(signup_date) or MIN(first_purchase_date) per user, and it becomes the anchor every later comparison is measured against.
3. Compute the cohort index. For each event tied to a user, calculate the elapsed period since their cohort identifier (day 0, day 1, week 0, week 1, and so on), giving every cohort a shared internal clock regardless of the calendar date it started on.
4. Aggregate active counts per cohort and index. Count distinct active users for each combination of cohort and cohort index, which produces the raw table a retention chart is built from.
5. Calculate retention percentages. Divide each period’s active count by the cohort’s original size to get a retention rate, since raw counts alone will always decline as a cohort naturally shrinks and are harder to compare across cohorts of different sizes.
6. Choose a visualisation that matches the question. A heatmap table is excellent for scanning many cohorts at once and spotting an anomalous row; a line chart of retention curves is better for comparing the shape of decline across a handful of cohorts.
7. Run validation checks before trusting the result. Confirm cohort sizes only shrink or stay flat over the index (never grow, which signals a data bug), check that period 0 is sensible (often 100% by construction), and sanity-check total users against your source system.
8. Translate the pattern into an action. A cohort table is a diagnostic, not a conclusion; the point is to generate a specific hypothesis (a channel, a feature, an onboarding step) worth testing directly.

The step that teams most often skip is validation, and it is the one that saves the most embarrassment.

Pro Tip: Build the cohort table once as a reusable query or notebook, not a one-off script; the moment it needs updating for a new date range is the moment hand-built logic quietly breaks.

Practical SQL and Excel recipes

The underlying logic is identical in SQL and Excel: anchor every user to a cohort date, compute an index, then pivot active counts into a retention table. GeeksforGeeks’ walkthrough confirms this is the standard pattern across practitioner tutorials, pairing cohort construction with pivoting into heatmaps.

In SQL, the pattern typically looks like three layered steps: first, a window function such as MIN(event_date) OVER (PARTITION BY user_id) to derive each user’s cohort date; second, a date-difference calculation (in days, weeks or months, matching your chosen granularity) to produce the cohort index; third, a GROUP BY on cohort date and index with a COUNT(DISTINCT user_id), which you then pivot, either in SQL with conditional aggregation or by exporting to a BI tool.

In Excel, the same logic maps onto worksheet columns rather than window functions: add a cohort month column (the user’s first transaction month, pulled with a lookup formula), a cohort index column calculated as the difference between the transaction month and the cohort month, then build a pivot table with cohort month as rows, cohort index as columns, and a count of distinct users as the values, finally converting each cell to a percentage of that row’s period-zero count.

A handful of checks guard against the recipes above producing a confident, wrong answer:

• Deduplicate identifiers before anything else, since a single user tracked under two IDs (a logged-out session and a logged-in one, for instance) will inflate both cohort size and apparent churn.
• Normalise timezones at the event level, not after aggregation, or users near a day boundary get shuffled between cohorts depending on which server clock wrote the timestamp.
• Watch for sampling in your event source, since some analytics platforms sample high-volume events by default, which will understate active counts without any error message to warn you.
• Confirm cohort index starts at a consistent zero across every cohort, otherwise cohorts started mid-month or mid-week will be compared on slightly different clocks.

How to read and interpret cohort charts: common shapes and the actions they imply

A retention curve’s shape is a diagnosis waiting to be read correctly, and each common shape points towards a different fix.

• Rising retention (a curve that climbs after an initial dip) usually signals that the users who survive the first drop-off are a genuinely more engaged subgroup, not that the product is retroactively improving them; this is often the heterogeneity effect covered in the next section, and it is rarely cause for celebration on its own.
• Flat, steady retention after an initial settling point suggests the product has found a loyal core; the useful question becomes how to grow that core’s size, through acquisition targeting or onboarding, rather than how to change its behaviour.
• A steep early fall (most of the loss happening in period one or two) almost always points at onboarding friction or a mismatch between what marketing promised and what the product delivers, and is the single most fixable pattern on this list.
• A U-shaped curve (an early fall, a trough, then recovery) often reflects a product with a habit-forming core feature that takes users time to discover; the fix is usually to surface that feature earlier, not to change acquisition.

Pro Tip: When a curve looks unusually good, check the cohort size before celebrating; a small, self-selected cohort will often produce a flattering curve that a larger, more representative one will not repeat.

Each shape should prompt a specific experiment rather than a general sense of urgency: a steep early fall justifies an onboarding redesign test, a U-shape justifies moving a key feature earlier in the user journey, and persistently flat retention justifies a channel or targeting experiment rather than a product change at all.

Statistical caveats and advanced modelling: heterogeneity, sBG and the APC problem

Two traps catch analysts who treat a cohort chart as self-explanatory. The first is the age-period-cohort identification problem: age (tenure), period (calendar time) and cohort (acquisition group) effects cannot be mathematically separated without explicit assumptions, as Columbia University’s methods resource sets out plainly. Any claim that “this cohort behaves differently because of when it joined” is quietly resting on an assumption about what’s held fixed, and that assumption belongs in the report, not buried in a query.

The second trap is the ruse of heterogeneity: a rising retention curve often reflects weaker users leaving first, not survivors becoming more loyal. Fader and Hardie’s work on projecting retention shows that a simple beta-geometric (sBG) model, which assumes constant individual churn probability layered with cross-sectional heterogeneity, often forecasts cohort survival well and should be the first model tried when projecting retention beyond the observed window.

You cannot distinguish mathematically between age, period and cohort effects without stating your assumptions explicitly.

Before trusting any projection, hold out a cohort the model hasn’t seen, bootstrap a confidence range around the forecast, and test how sensitive the result is to the assumption you chose to fix.

A worked example: from cohort finding to experiment

Say your acquisition cohorts show Channel A losing a large portion of users within the first week, far steeper than Channel B’s curve.

1. Validate before acting: split the cohort by signup device and campaign to rule out a tracking artefact, and check ARPU, since a cheap channel with fast churn can still be profitable.
2. Check for confounders: confirm the cohorts launched in comparable periods, not one during a seasonal spike.
3. Run the test: an A/B test on Channel A’s first-session onboarding flow, not a blanket budget cut, isolates whether the drop is a product problem or a channel-quality one.

Perspective: treat cohort analysis as an ongoing diagnostic, not a one-off report

The industry’s comfortable habit is to run a cohort analysis once, screenshot the chart for a quarterly review, and move on, which is a bit like taking a single X-ray and assuming the patient is cured. Retention is a moving target, and a cohort built last quarter says nothing reliable about a product that has shipped six releases since.

The sturdier practice is to embed cohort views in a recurring dashboard, document the assumptions behind every cohort definition and index, and treat each chart as a hypothesis generator feeding into an experiment queue, not a verdict. Analytics that only describe the past are a comfortable habit; analytics that feed a testing pipeline are the ones that change a roadmap.

— Martin

Turning cohort insight into a working analytics pipeline

Spotting a retention problem is the easy part; building the pipeline, dashboard and experiment discipline to act on it consistently is where most internal teams stall. We work with product and growth teams on exactly that gap, through digital product discovery to define the right metrics and cohort structure, analytics instrumentation to get clean event data flowing, and product growth engagements that turn a cohort finding into a prioritised, tested roadmap rather than a one-off slide.

Typical engagements deliver:

• A data pipeline and cohort dashboard built on existing event data.
• A shortlist of prioritised experiments derived from cohort patterns.
• Support running initial tests to ensure insights reach production.

Our services page outlines the discovery and growth engagements in full; if you’d rather see the kind of hands-on instrumentation work we mean, our credentials detail recent product growth and experience design work. Reach out when you’re ready to turn your retention curves into a roadmap.

FAQ

What is cohort analysis?

Cohort analysis groups users who share a starting point, most often their signup or first purchase date, and tracks their behaviour across identical time intervals since that start. This separates genuine retention patterns from the noise of blending old and new users into one aggregate number, as Wikipedia’s definition sets out.

Can you give me an example of a cohort analysis?

A simple example: group every user by the month they signed up, then for each monthly cohort calculate what percentage are still active in month one, month two and month three after joining. Plotting these percentages side by side shows whether newer signup cohorts are retaining better or worse than older ones.

How do I perform cohort analysis in Excel?

Add a column for each user’s cohort month (their first transaction month) and a second column for the cohort index (the gap between a given transaction and that cohort month), then build a pivot table with cohort month as rows and cohort index as columns, counting distinct users in each cell. Convert each cell to a percentage of that row’s starting count to get a retention table, a pattern described in GeeksforGeeks’ cohort analysis guide.

How can I perform cohort analysis in SQL?

Use a window function such as MIN(event_date) OVER (PARTITION BY user_id) to assign each user a cohort date, calculate a cohort index as the date difference between each event and that cohort date, then group by cohort date and index with a distinct user count. Pivot the result, either with conditional aggregation in SQL or by exporting it into a BI tool, to produce the retention table.

Why does retention sometimes appear to rise over time?

A rising curve usually reflects weaker users leaving first, leaving a more loyal subgroup behind, rather than survivors becoming more engaged individually. This is the heterogeneity effect that Fader and Hardie’s research addresses directly with the beta-geometric model, and it is worth checking before treating a rising curve as good news.

Sources

• How to project customer retention — Fader & Hardie
• Age‑period‑cohort analysis — Columbia University

Recommended

• The Role of Collaboration in Product Development
• Products Don’t Scale, Systems Do
• The Role of AI in Product Development: A 2026 Guide
• Workflow for Product Innovation: Your 2026 Guide

By Format-3

Press & Media

More thoughts

Thought leadership creates value, builds knowledge and takes a stand, bridging the gap between traditional and digital platforms