How to Do Cohort Analysis From Scratch

1What Is Cohort Analysis?

Cohort analysis is a technique that groups users by a shared starting characteristic, usually the period they signed up, and then tracks how each group behaves over time. Instead of asking how many users were active this month, it asks how many of the users who joined in January are still active in their second, third, and fourth month. That reframing exposes trends that a single aggregate number completely hides.

The reason it matters is that top-line metrics can lie by averaging. Total active users can rise steadily even while every individual group of new users abandons the product faster than the last, simply because you are acquiring more people. Cohort analysis separates acquisition from retention so you can see whether the product is actually getting stickier.

The most common form is a retention cohort, which we will build from scratch in this article. The same method applies to revenue, engagement, or any behavior you can track per user over time.

2The Data You Need

Cohort analysis is refreshingly light on requirements. At minimum you need a table where each row is an event with three fields: a user identifier, the date the user first signed up, and the date of the activity you care about. If your events table lacks a signup date, you can derive it as each user's earliest event.

From those three columns you can compute everything else. Nothing here requires machine learning or specialized tooling. Analysts routinely build their first cohort table in a spreadsheet, then graduate to SQL once the dataset outgrows the rows a spreadsheet handles comfortably.

• user_id: a stable identifier for each user.
• signup_date: the date the user first joined, which defines their cohort.
• activity_date: any date the user did the thing you count as active, such as logging in or making a purchase.

3Step One: Assign Each User a Cohort

The first step is to label every user with a cohort key derived from their signup date. For monthly cohorts, truncate the signup date to the first of the month, so a user who joined on March 14 belongs to the March cohort. In SQL this is a date_trunc on signup_date; in a spreadsheet it is a formula that returns the year and month.

Choose the cohort granularity that matches your product's rhythm. Consumer apps with daily use often benefit from weekly cohorts, while B2B products with slower cycles usually read better as monthly. The wrong granularity either buries the signal in noise or smooths it away entirely.

4Step Two: Calculate Each User's Age

Next, for every activity event, calculate how old the user was, in periods, when it happened. The cohort age is the difference between the activity date and the cohort start date, measured in the same unit as your cohorts. A March-cohort user who is active in May has an age of two months; the same activity in March is age zero.

This age is the column axis of your cohort grid. Age zero is the signup period itself, where retention is by definition close to 100 percent, and each subsequent column shows how the group thinned out. Getting the age calculation right is the part beginners most often botch, so verify a few rows by hand before trusting the grid.

5Step Three: Build the Cohort Table

Now pivot the data into a grid. Rows are cohorts, columns are ages, and each cell counts the distinct users from that cohort who were active at that age. The first column shows cohort size; every column to its right shows how many of those original users came back.

Convert the raw counts to percentages by dividing each cell by its cohort's size in the age-zero column. A cell that reads 42 percent at age three means 42 percent of the users who joined in that cohort were still active three periods later. Percentages let you compare a cohort of 5,000 users against one of 500 fairly.

6A Worked Example

Imagine a small app with three monthly cohorts. The January cohort has 1,000 signups; 400 are active in month one, 250 in month two, and 200 in month three. As percentages that reads 100, 40, 25, 20. The February cohort of 1,200 signups retains 46 percent, 30 percent, and 24 percent over the same ages. March, with 1,500 signups, holds 52 percent and 34 percent so far.

Read down the age-one column and a story appears: 40 percent, then 46 percent, then 52 percent. Each new cohort retains better than the last at the same age, which strongly suggests a product change between January and March improved early retention. The total active-user count alone could never have told you that, because it also swelled from rising signups.

7Reading the Retention Curve

Plot a single cohort's percentages against age and you get a retention curve. Almost every real product produces the same shape: a steep drop over the first few periods as casual users leave, followed by a flattening as the committed core settles in. The two things to read are how fast it drops and, crucially, how high it flattens.

The height of the flat tail is a rough proxy for product-market fit. A curve that decays toward zero means users try the product and never find a lasting reason to return. A curve that flattens at a meaningful, non-zero level means you have a habitual core, and growth becomes a matter of feeding the top of the funnel. A rising curve, called a smile, where lapsed users return, is the strongest signal of all.

8Comparing Cohorts Over Time

The real power of cohort analysis is comparison. Line up several cohorts' curves on one chart and you can see whether the product is improving. If newer cohorts sit above older ones at the same age, your changes are working. If they sit below, something you shipped is quietly hurting retention even as vanity metrics climb.

Be careful to compare like with like. Seasonal effects, marketing campaigns that acquire lower-intent users, and pricing changes can all shift a cohort's baseline for reasons unrelated to product quality. Annotate your cohort chart with what changed and when, so a dip in one cohort can be attributed to its actual cause rather than blamed on the product.

9Beyond Retention: Other Cohort Metrics

Once you can build a retention grid, the same machinery answers richer questions. Swap the counted metric from active users to revenue and you get a revenue-retention cohort that shows whether each group spends more or less over time. Swap it for a specific action, like completing a key feature, and you can see whether a product habit forms or fades.

Cohorts also work for questions of quality, not just quantity. You might cohort by acquisition channel to learn that paid users churn twice as fast as organic ones, or by first-week engagement to prove that users who hit a certain milestone in week one retain far longer. Each of these is a variation on the same three-step method.

10Common Pitfalls to Avoid

A few mistakes trip up almost everyone building their first cohorts. Watch for them before you present results to anyone.

• Judging the youngest cohorts too early, when they have only one or two periods of data and cannot yet show their tail.
• Mixing time zones or date formats so activity appears to happen before signup, producing impossible negative ages.
• Using cohorts that are too small, where a handful of users swings the percentage wildly and looks like a trend.
• Forgetting that the latest period is often partial, which makes the final column understate retention.
• Comparing cohorts across a major definition change, such as redefining what active means midway through.

11Frequently Asked Questions

What is cohort analysis used for? It is used to measure retention and behavior over time by grouping users by when they started, so you can tell whether a product is genuinely getting stickier rather than just acquiring more users. It is a core tool for understanding product-market fit.

How is cohort analysis different from a normal retention metric? A single retention number averages everyone together, while cohort analysis breaks users into groups by start date and tracks each group separately. That separation reveals whether newer users retain better or worse than older ones, which an average hides.

Do I need SQL to do cohort analysis? No, you can build your first cohort table in a spreadsheet with a signup-month formula, an age calculation, and a pivot table. SQL becomes helpful once your dataset grows beyond what a spreadsheet handles comfortably.

How many users do I need for a meaningful cohort? There is no fixed number, but very small cohorts swing wildly on a few users, so aim for cohorts large enough that a single person leaving does not move the percentage much, often at least a few hundred users per cohort.

What does the retention curve flattening tell me? A curve that flattens at a meaningful non-zero level indicates a committed core of users who keep returning, which is a strong signal of product-market fit. A curve that decays toward zero means users are not finding a lasting reason to come back.

Can I learn cohort analysis for free? Yes. SkillVeris offers free data analysis courses and study notes that cover cohort analysis, retention, SQL, and metric design with worked examples you can follow along with.

12Putting Cohort Analysis to Work

Cohort analysis is one of the highest-leverage techniques a data analyst can learn, because it turns a fog of aggregate metrics into a clear picture of whether your product is actually improving. With nothing more than user IDs, signup dates, and activity dates, you can assign cohorts, compute ages, pivot into a grid, and read a retention curve that tells you the truth about stickiness.

The best way to internalize the method is to build one on your own data. You can learn the full workflow for free on SkillVeris, where the data analysis and SQL courses and study notes walk through cohort tables, retention curves, and metric design step by step. Pair this with the KPI and metrics topics on the platform, and you will be equipped to answer the question every product team eventually asks: are our users staying?