The P9 Guide to Cohort Analysis in SaaS (v0.9)

Everything you always wanted to know about cohort analysis (but were afraid to ask)

Published in

Point Nine Land

23 min readJun 18, 2023

Back in 2012, I wrote a blog post titled “Know your user cohorts”, which began like this:

“One of the most important tools to better understand the usage of a web application — or a service, a game or a mobile app, it doesn’t matter — is a cohort analysis. In fact, it’s almost impossible to get a really good understanding of a service’s usage without looking at activity and retention numbers on a cohort-by-cohort basis. And yet, most startups that we’re talking to haven’t looked into cohort analyses yet.”

More than ten years later, cohort analyses have become an essential part of most SaaS companies’ toolkits. However, even though everyone is now aware of cohort analyses, and every major web, product, or revenue analytics product offers features for cohort analysis, it still requires time to fully comprehend everything you need to know about cohort analyses and, perhaps more importantly, to utilize them to obtain real, actionable insights.

The purpose of this guide is to equip you with everything you need to begin your journey with cohort analyses. It’s structured as an FAQ, making it easy for you to skim over the sections you’re already familiar with and get to the areas that are of interest to you. I have written this guide specifically for first-time SaaS founders with limited experience. Therefore, most of the questions and answers are relatively basic, but some of the more advanced sections may also interest more seasoned founders.

If you have any suggestions for questions to add, let me know and I’ll try to incorporate them in the v1.0 of the guide.

Basics

What is a cohort analysis?

Cohort analysis is a powerful method used to analyze groups of customers and their behavior over time. Grouping is usually done based on when a customer has signed up or converted into a paying customer. By conducting a cohort analysis, you can track customer behavior, retention, churn, and revenue over time.

What makes a cohort analysis so useful?

Simply looking at revenue growth or a usage metric over time doesn’t tell you much about your ability to retain users and turn them into happy long-term customers. If you’re acquiring a lot of new customers quickly, metrics like “usage from repeat users” or “payments from repeat customers” may go up nicely, even if your retention rate sucks. Time series data, like an ECG, gives you the pulse but not the underlying conditions.

This is why it’s so essential to use cohort analysis, especially in SaaS, where it’s all about retaining customers and revenue. By understanding how various customer groups stick around (or don’t), you can get a much better idea of what’s really happening with retention. Cohort analysis can also help you determine which types of customers are driving your growth, which marketing channels are bringing in the most valuable users, and which product features are most popular with different customer groups.

Should I look at churn or retention cohorts?

Both metrics are two sides of the same coin, so it doesn’t really matter. Both can be used to report on customers, subscriptions, and revenue. And both can be cohorted. That said, I tend to look at churn when talking about customers/logos and retention when looking at revenue/dollars, but that might be just a personal preference.

What’s the difference between customer churn and revenue churn?

Customer churn refers to the number of customers who leave your service over a certain period, while revenue churn refers to the revenue lost from those departing customers. Your customer churn rate can be very different from your revenue churn rate. For example, if you lose several low-ACV customers but retain your high-ACV accounts, your customer churn might be high, but your revenue churn remains low.

Customer churn (AKA logo churn) cannot be negative. Revenue churn is negative if the expansion ARR from your retained customers more than offsets the churn ARR from lost customers and contractions. Ultimately, revenue retention matters more than customer retention, but you’ll still want to keep an eye on customer churn/retention, too.

What’s the difference between a “right-aligned” and a “left-aligned” cohort analysis?

If a cohort analysis is “right-aligned”, like the one below, each column represents a calendar month (e.g. March 2022, April 2022, etc.):

In a “left-aligned” cohort analysis, each column represents a “lifetime month” (1, 2, …, n):

In most cases, the left-aligned version is more useful because when you examine cohort data, you usually want to see how your users behave throughout their lifetime.

What if some of my customers are on monthly plans and others are on annual plans?

Doing a cohort analysis across customers with different contract durations can lead to highly inaccurate results, particularly in the early stages or when there’s a significant influx of new customers on annual plans. These customers are unable to leave during their first year, so including them in the denominator of your churn rate calculation will artificially reduce your churn rate. Therefore you should segment your customers into those on monthly plans and those on annual plans, conducting separate cohort analyses for each group.

How to create a cohort analysis

How can I transform raw payment data to do a cohort analysis?

Depending on which payment processor you’re working with, your transaction data will look different. However, no matter how you’re processing payments, you should be able to generate a report that looks similar to this one:

As you can see, every row represents one transaction, and for each transaction, you have the following data points:

The date when the customer became a customer, i.e. when he/she was charged for the first time.
How much you’ve charged the customer.
The date of the transaction.
A customer ID

If all of your customers are on a monthly plan, this is all you need to create a very simple cohort analysis. If some of your customers are on monthly plans and others are on annual plans, it gets slightly more complicated. And if you want to do cohort analyses for different segments of your customer base, monitor your cohorts in near real-time, or have tens of thousands of rows in your payment file, I’d highly recommend that you use ChartMogul, which makes all of this very easy. :-)

If you want to find out how to quickly turn raw transaction data into a cohort analysis, below is a simple step-by-step guide. If you’re an experienced Google Sheets or Excel user, you won’t need this guide, but if you’re a spreadsheet newbie, this might be a helpful starting point.

Step 1: Import a CSV file containing your transaction data into Google Sheets (or Excel).

Step 2: Select the columns containing the customer start date, transaction amount, and transaction date, and select “Pivot table” from the “Insert” menu.

Step 3: Add transaction amount in values, customer start date in rows, and transaction date in columns.

Step 4: Right-click on any of the dates in the header row of the pivot table and select “year-month” from “Create pivot date group”.

You should now see something that looks like this:

Congratulations, you’ve created your first cohort analysis! 🎊🎉😎

In case something didn’t work as expected on your end, here’s a Google spreadsheet with some sample data on the first tab and a pivot table on the second tab. To add a simple visualization of your cohort analysis, select the data (excluding the “Grand Total” column, insert a chart, and select stacked area chart. Check “switch rows/columns”, and you should see something like this:

The chart above is a nice and easy way to visualize cohort data, but in many cases, there are better ways. We’ll look at several other options in this post. Also, note that the cohort analysis that we’ve created here is “right-aligned”.

You can also use ChatGPT’s Code Interpreter plugin to transform transaction data into a cohort analysis. I’ve played around with it a bit, and it works surprisingly well. It may not always get it right, so for now, you still need to verify it yourself.

Understanding cohort analyses

How to read a cohort analysis

Here’s a simple example, using a left-aligned view:

Each row represents a cohort of customers grouped by the month in which they converted. The first row represents the January 2020 cohort, the second row (marked in green) the February 2020 cohort, etc.
The numbers in the “New customers” column (marked in yellow) indicate the total number of customers you converted in the given month.
The columns for lifetime months 0–9 show how many customers you have retained in that lifetime month. For example, the numbers in the red box show how many customers are still around after three months.
Month 0 is the month they converted. In the example above, customers can’t churn within their first billing cycle, i.e. month 0.
One final example to make it super clear: If you look at the number in the blue cell, you can see that out of the 115 customers you acquired in May 2020, 105 are still around two months later.
If you’re wondering why there are no numbers in the bottom right (which gives cohort analyses their characteristic triangle look): The sample data above is from October 2020, so the October 2020 cohort is still in its initial month, the September 2020 cohort is in its first month, and so on.

Note that the example above shows the absolute number of customers. It’s usually more interesting to look at percentages since that makes it easier to compare retention rates across different cohorts:

Here are two other ways of looking at the exact same data:

The first table shows how many customers you’ve lost, per cohort, throughout the customer lifetime, as a percentage of the initial cohort size. For instance, in the fifth lifetime month of the April 2020 cohort, it says 4.5% because five out of the original 110 customers churned in that month (5/110 ≈ 4.5%).

The second table shows the same thing but relative to the previous month. At the end of the fourth lifetime month, 97 out of the initial 110 customers of the April 2020 cohort were still around. Therefore, according to this logic, the churn rate in the fifth lifetime month is 5/97 ≈ 5.2%.

If this sounds like a subtle difference, consider this example: If you start with 100 customers and lose three customer per month (i.e., 3% of the initial cohort size), everybody is gone by month 34. In contrast, if your monthly churn rate is 3% of the remaining customers of the previous month, you still have 25% of your customer base after four years.

What about all those colors?

Assigning different background colors to the cells in a cohort analysis makes it easier to spot trends and identify issues. For example, in the screenshot above, you can immediately see that the average churn rate is trending down throughout the customer lifetime because the red is transitioning into orange/yellow. You can also see right away that, for instance, month 5 was an unusually bad month for the April 2020 cohort.

It’s really important that you’re mindful about your choice of colors, though. Different colors are associated with different meanings, and if you don’t bear that in mind or don’t align the color gradients with the proper range of values, you can end up with colors that confuse rather than aid understanding.

It’s commonly accepted to use green for “good” values (e.g. a churn or retention rate you’d be happy with), red for “bad” values, yellow for “OK”, and gradients for values in between. In the example above I went for a simpler alternative with only two colors (yellow for 0% churn, red for 5% churn per month). In the revenue retention example further above I’ve used a strong green for 100% revenue retention and a light green for 80%.

Working with cohort analyses

What should I look for in a cohort analysis?

The most important things you’ll typically look for are:

1) Is retention (customer or revenue retention) improving over the customer lifetime? To determine this, you start on the left side and move horizontally to the right to see if your churn rate decreases:

In the example above, the monthly customer churn rate drops from an average of 4–5% per month in the first three lifetime months to around 2–3% per month in the subsequent lifetime months. There’s not enough data to indicate if it will stabilize at about 1.5–2% p.m. after month 9 or so, but this is one of the things you’ll want to see.

2) Is retention (customer or revenue retention) getting better over time, i.e. are you retaining more recent cohorts at a higher rate than older cohorts? By starting at the top and moving down, you can see if there is a positive trend towards lower churn, perhaps as a result of increasing product maturity, better onboarding, or other improvements.

In the example above, there’s no clear trend in either direction.

Here is another example, from a different (fictional) company, and looking at revenue retention:

In this example, more recent cohorts clearly perform better than older ones. It’s easy to see here because, for any given column, the cells are becoming more yellow-y as you move down.

3) How much can I spend on customer acquisition? How much you can spend to acquire a customer depends on your LTV (Lifetime Value) and what CAC/LTV ratio is acceptable for you. But estimating LTV is difficult, especially early on. A pragmatic approach is to define a limit for CAC payback. For example, you might find it acceptable to pay up to 15 months of (Gross Margin adjusted) MRR for a new customer. To keep track of your CAC payback times, you’ll have to monitor customer retention over time, and the best tool for that is (surprise!) a cohort analysis..

What are some actionable outcomes of doing a cohort analysis?

Here are a few examples:

Cohort analysis helps you identify when and where you’re losing customers. If you’re experiencing a massive exodus in the initial months, followed by a relatively stable retention rate, you’ll want to look into your onboarding and qualification process. Conversely, if churn is consistently high and doesn’t stabilize later in the customer lifetime, it indicates that you don’t have strong PMF yet.
Cohort analysis is the best way to forecast customer lifetime and get a solid estimate of LTV and CAC payback times — obviously extremely important so you know how much you can spend on customer acquisition.
If you have distinct customer segments or personas, you might discover, by analyzing each group separately, that one of them has a significantly higher LTV than the other. Knowing this you can adjust your marketing strategy to try to attract more customers from the high-LTV segment.
Monitoring usage activity across cohorts can give you early warning signs of potential churn, which is particularly critical for businesses offering annual plans. By catching these red flags early, you can take proactive steps that will hopefully help you rescue at-risk customers.
Cohorted retention graphs are very effective in showing which new product features, software releases, or promotional campaigns brought better or worse customers.

What are some good ways to segment cohorts?

The most frequent ways to segment cohorts are based on acquisition channel (e.g. inbound and outbound), customer segment (e.g. mid-market and enterprise), contract duration (e.g. monthly and annual), and geography. The goal is always to better understand the behavior of certain segments of your customer base and generate actionable insights (e.g. “this is a great customer group, let’s try to get more of those”). In the beginning, when you don’t have a lot of data, you shouldn’t overdo it to avoid trying to analyze too many tiny cohorts. Over time, you can do more and more slicing and dicing to answer questions like:

How does our retention rate vary by AE?
What is the correlation between usage of certain product features and churn?
Do customers with certain characteristics have a higher LTV?
How does our retention rate differ for customers that received a discount?

What are some other good ways to visualize cohort data?

This chart shows MRR in absolute terms, for different cohorts, over the customer lifetime:

The main takeaway from this example is that the company has strong growth in terms of new customers: The October cohort is larger than the September cohort, which is larger than the August cohort, etc. You can also see that, for example, the August 2020 cohort is doing better in terms of retention than older cohorts, but this is not the best chart to dig into retention rates.

If you’d like to compare retention rates of various cohorts, this is a much better way of doing it:

What you can see here is that for any given month, with few exceptions, more recent cohorts perform better than older ones: The February 2020 line is above the January line, the March 2020 line is above the February 2020 line, etc.

Here’s another way to look at the same data:

Here, the lines don’t represent cohorts. Instead, each line represents a point in time in the customer lifetime (1M, 3M and 6M), while each section on the x-axis represents a cohort. Since the chart is based on the same data as the previous one, the lines do what you expect them to do: they go up.

Last but not least, here’s a chart that shows you when you break-even on customer acquisition costs:

What you can see here is cumulated Gross Profit minus CACs for different customer cohorts, i.e. it shows how much (gross margin adjusted) revenue a customer cohort has generated, minus the costs that it took to acquire the cohort. The purpose of this chart is to show if you’re getting better or worse with respect to one of the most important SaaS metrics: The CAC payback time, i.e. the time it takes until a customer becomes profitable. In the example above, you can see that more recent cohorts break-even faster than older cohorts (although the older ones already have a very good CAC payback time).

How can different teams use cohort analysis to make better decisions?

Whether you are a founder, customer success, sales or marketing leader, people across the SaaS organization can take advantage of cohort analysis. A few examples:

For founders and revenue leaders, it can be useful to segment cohorts by pricing plan. Each plan might have its own characteristics in terms of churn and retention.
The marketing team might look into how retention might vary for different acquisition channels to see which channels are bringing in customers that stick around.
Sales leaders can segment by AE to see which sales rep has the highest retention rate.
Teams responsible for customer onboarding can determine the health of customers in their first few months of paying for a subscription and identify when customers are dropping off.
A customer success team might segment churn data to determine, for example, if customers with discounted plans are more prone to churn. Or, to see how NPS score correlates with churn rate.

How can I use a cohort analysis to estimate customer lifetime value?

The simplest formula to calculate customer lifetime value for a SaaS company is to take your ARPA (adjusted for CoGS, i.e. your average Gross Profit per account) and divide it by your customer churn rate. There are various problems with this approach, for example, if churn isn’t spread linearly over the customer lifetime.

To get to a better customer lifetime value estimate, you can take the revenue retention data of your existing cohorts, extrapolate each cohort’s future revenue development, and calculate LTV based on the NPV of the projected revenue streams. If you’d like to dig into this, here’s an article I wrote a while ago.

How can I make my cohorts look better when I’m fundraising?

When preparing your cohorts for fundraising, there are a number of ways how you can showcase your data in the most favorable light. Let’s take a look at a few approaches, starting with the most legitimate tactics and proceeding along a somewhat slippery slope:

1) Segment cohorts by customer type: Customer segmentation is super important irrespective of fundraising (a topic I’ve written about many times, e.g. here), so it makes a lot of sense to reflect this in your fundraising materials. As startups evolve, they often discover new and more profitable customer segments. The classic case is a SaaS startup that starts by targeting SMBs before eventually moving upmarket. Analyzing all your numbers together often doesn’t provide a comprehensive picture, especially if a specific segment has better economics and is growing faster.

2) Cut out the noise: Sometimes, certain outliers within your data can obscure genuine insights. For example, significant churn from a non-ICP customer could drastically distort your overall retention rate. In such cases, removing these outliers can provide a clearer perspective on the data. Make sure to disclose exactly what you’ve removed and why.

3) Include only customers who survive a specific period (e.g. three months) in your cohort analyses: If you have a huge drop-off within the initial months, e.g. because you make it very easy to become a paying customer, consider treating this period as an extended free trial. This approach can be particularly useful in consumer subscription businesses, which often experience very high churn rates in the first months. Keep in mind hat this strategy essentially redefines who your “real” customers are, so you’ll obviously have fewer (but better) customers.

So … some cherry-picking is fine, as long as you’re super transparent about it, and as long as it makes sense. What doesn’t make sense, for example, is to define your active users as those who engage with the product at least once per week and then report your WAU/MAU ratio. 🙂

Last (and least), you might be tempted to adjust the conditional formatting in your spreadsheet to create a pleasant “green” first impression. Not recommended, unless you’re sure you want to bring investors on board who can be tricked like this.

Advanced questions

What is “hidden churn”, how can I detect it, and what can I do about it?

Hidden churn is when customers are inactive but still paying. It’s like a gym membership you never use but keep paying for. Eventually, inactive customers will churn, so you should monitor the health of your customers and try to re-engage with customers whose activity goes down. If your customers are on annual plans it’s particularly important to be on top of this to avoid bad surprises when it comes to renewals.

How can I measure customer health with a cohort analysis?

Measuring customer health indicators like usage frequency and user engagement through cohort analysis can be a powerful tool for detecting hidden churn. Essentially, you want to quantify the percentage of inactive users as a churn predictor.

One way is establishing a health indicator that resonates with your product and expected customer usage. Your health indicator could be as straightforward as “at least one user logged in” or it could be more specific such as “customer has utilized our product to generate more than ten invoices.” Sometimes, it might be a more complex customer health score that integrates multiple elements. Once you’ve determined your customer health indicator, you can conduct a cohort analysis based on this indicator to show the number and percentage of at-risk customers for various cohorts over time.

You can also look at a key usage indicator that closely correlates with the intended usage of your product and the value customers get from it (like, say, the number of images created and exported for a creative tool) and look at how this number changes over time for different cohorts (similar to how we previously looked at revenue retention over time).

How do I calculate averages for churn or retention across several cohorts?

The values in the bottom rows of cohort data tables represent the average churn rate in the respective lifetime month, for all old enough cohorts. The average is calculated by dividing the number of customers lost in the given lifetime month by the size of the original cohort group (or the number of customers of the cohort group in the previous month, respectively). In other words, it’s a weighted average (weighted by the cohort size) instead of a simple average of the monthly churn rates. This probably sounds more complicated than it is. I think it will become clearer if you look at the formula in the screenshot above.

What’s a “smiling cohort graph”?

Consumer subscription companies (and to some but a lesser extent, SMB-focused B2B SaaS companies) typically lose a large percentage of their subscribers in the first one or two years. What can make these companies great businesses nonetheless is if most of the remaining subscribers who “survive” the first two years remain customers for a very long time. If some of these loyal users upgrade to a more expensive plan over time (or you’re good at re-activating/winning-back customers that churned), a cohort’s revenue can go up later in a cohort’s life, producing a chart like this:

I wrote about this in more detail in this post.

What can I see by reading a cohort table diagonally?

Let’s have a look at this left-aligned cohort table:

If you start in month 0 of the October 2020 cohort and move up and to the right, add all values, the result is the total MRR as of October 2020 (assuming the table contains rows for all months with active customers). You can also see the composition of your October 2020 MRR by cohort. I think there are better ways to delve into this, but since I’m trying to cover the anatomy of a cohort analysis here, I wanted to mention it for completeness' sake.

Let’s turn to a right-aligned cohort table:

If you look at a right-aligned cohort table by going from the top left to the bottom right, you can see how the retention rate in a given lifetime month has developed from older to more recent cohorts. We’ve looked at better ways to visualize this earlier, but in case you stumble on a cohort analysis that is formatted like this and you want to quickly check how retention has been doing, this is how you can do it.

What is NDR, and how can I use cohort analysis to calculate it?

The way mature SaaS companies usually track and report churn and retention is through Net Dollar Retention (NDR). If you’re seeking a single number that encapsulates a SaaS company’s retention performance, NDR is your go-to metric.

NDR compares the recurring revenue from a set of customers across comparable periods. Sounds cohort-y! The reason why I haven’t mentioned it earlier is that so far, we’ve always looked at cohorts grouped based on when a customer became a paying customer. Then, we looked at their retention over time. To look at a larger dataset (e.g., if a company has several years of cohort data), we could group cohorts by quarters or even years, but to condense the analysis even further, to just one metric, we’ll have to turn to NDR.

To calculate NDR, you effectively create a cohort based on active customer contracts at a certain date. For example, if it’s January 2023 and you’re looking at annual NDR, you’ll include all customers as of December 31, 2021, and look at what happened to the ARR from that customer group as of December 31, 2022. So for the December 31, 2022, value, you’ll include expansion ARR, contraction ARR, churn ARR, and reactivation ARR but disregard ARR from new customers that joined in 2022 or later.

You can, of course, also track annual NDR on a quarterly basis. Quick example: In April 2023, you’ll look at your entire customer base as of March 31, 2022, and calculate that cohort’s ARR as of March 31, 2023, divided by the value from a year earlier. You can, of course, also do it on a monthly basis.

If you’re curious about where you might find your NDR in the cohort tables we’ve looked at, I’m happy to report that we’ve found some use for the “diagonal” read. 🙂

Recall that if you add up the values diagonally, going from the bottom left to the top right, you’ll get the total ARR (or MRR, as used in the example above) as of the month where you’ve started. So in the example above, your total MRR in May 2020 is $11,788 + $9,898 + $8,678 + $7,210 + $6,876 = $56,490 (shown in violet). As mentioned before, this assumes that January 2020 is your first revenue cohort. To look at the MRR from that customer group at a later point in time, you simply move the diagonal to the right, as indicated by the pink cells. The table above doesn’t have enough sample data to show the 12-month shift necessary for an annual NDR, so I’m only showing a quarterly NDR here.

Acronyms

For this section, I’ve turned to ChatGPT, which was kind enough to write provide the following definitions:

1M retention: It’s the percentage of customers still doing business with you one month after they converted. If you convert 100 customers this month, and 60 of them are still around next month, your 1-month retention is 60%. You’re keeping more than half — not bad!

ACV (Annual Contract Value): The average annual revenue per customer contract. It’s like your yearly allowance from each customer, if customers were your rich relatives.

ARPA (Average Revenue Per Account): The average revenue you’re making from each customer, which can be calculated monthly or annually. It’s like your allowance from each customer, except they’re not your parents, and you actually need to provide a service.

ARR (Annual Recurring Revenue): Your company’s yearly subscription income. This is the reason you have annual company parties (or why you should start having them).

CACs (Customer Acquisition Costs): The average amount you need to spend to get a customer on board. They can be blended (including all customers from all channels and all sales and marketing expenses) or for specific channels (excluding organic). Figuring out how to calculate this is a hot topic in its own right, but remember: if you’re spending more than the GDP of a small country, that’s probably too much.

CoGS (Cost of Goods Sold): The total cost of producing the services sold by your company. Yes, digital goods do have costs too, like server space, support, and all the caffeine for your dev team.

Gross Profit: Revenue minus CoGS. This is your earnings after covering the raw costs, but before accounting for little things like salaries, rent, and the CEO’s yacht.

LTV (Lifetime Value): Total revenue you expect from a customer during their relationship with your company. It’s like foreseeing your romantic relationship in dollar terms, without the breakups.

MAU (Monthly Active Users): The number of users who actively engage with your product in a given month. In the party of your product, these are the folks who show up every month.

MRR (Monthly Recurring Revenue): It’s like ARR, but in bite-sized, monthly pieces. The monthly subscription income your SaaS company gets to roll in.

NDR (Net Dollar Retention): This tells you how much you’re earning from existing customers over time, taking into account churn, contraction, and expansion revenue. If you’re over 100%, your existing customers are worth more over time — they’re like wine, getting better with age.

WAU (Weekly Active Users): Similar to MAU, but on a tighter schedule. These are the users who show up to your product party every week. They’re the life and soul of your SaaS fiesta.

Huge thanks to Olga Berezovsky, Bianca Wilk, and Sid Jain for their valuable feedback on a draft of this post!