Cohort analysis allows to examine groups of customers and their behaviour over time, providing insights into your customer retention, churn, and identify revenue patterns.
By understanding these dynamics, marketers can make more informed decisions to improve customer loyalty and long-term success.
What is a Cohort Analysis?
Cohort analysis is a method of grouping customers based on their sign-up or conversion dates and studying their behaviour and performance metrics over time. This technique enables businesses to track customer retention, churn rates, and revenue trends. Instead of solely relying on overall revenue growth or usage metrics, cohort analysis allows for a more granular understanding of customer behaviour, providing valuable insights into the effectiveness of customer retention strategies.
The Value of Cohort Analysis
Examining revenue growth or usage metrics in isolation provides only a limited understanding of customer retention. Cohort analysis is particularly crucial in the Software-as-a-Service (SaaS) industry, where customer retention and revenue are paramount. This analytical approach helps businesses grasp how different customer groups retain or churn, identify the driving factors behind growth, evaluate the effectiveness of marketing channels, and determine the popularity of various product features among different customer segments.
Churn Cohorts or Retention Cohorts?
When conducting a cohort analysis, both churn and retention metrics are equally important and complement each other. While churn metrics focus on customers who discontinue their subscriptions within a specific period, retention metrics analyse the revenue lost from those departing customers. Depending on the context, you may choose to prioritise one over the other. For instance, when discussing customers or brand impact, churn cohorts are typically used, whereas retention cohorts are preferred when examining revenue or financial aspects. However, this preference may vary from person to person.
Understanding Customer Churn and Revenue Churn
Customer churn represents the number of customers who terminate their subscription during a given timeframe, whereas revenue churn reflects the lost revenue resulting from customer departures. It’s important to note that customer churn rate and revenue churn rate can differ significantly. For example, if you lose several customers with low Average Contract Value (ACV) but retain high-ACV accounts, your customer churn rate may be high while your revenue churn rate remains low. Negative revenue churn occurs when the expansion Annual Recurring Revenue (ARR) from retained customers exceeds the ARR lost from departing customers and contractions. Ultimately, prioritising revenue retention is crucial, but monitoring customer churn and retention is also essential.
"Right-Aligned" vs. "Left-Aligned" Cohort Analysis
In cohort analysis, the alignment of columns in the analysis table can impact its usefulness. In a “right-aligned” cohort analysis, each column represents a calendar month, such as May 2023, June 2023, and so on. On the other hand, in a “left-aligned” cohort analysis, each column represents a “lifetime month” (1, 2, …, n). Typically, the left-aligned version is more valuable as it allows you to observe how users behave throughout their entire customer lifecycle, providing insights into long-term trends and patterns.
Handling Monthly and Annual Plans
When conducting a cohort analysis, it’s important to consider the impact of different contract durations. Analysing cohorts that include customers on both monthly and annual plans can lead to inaccurate results, particularly in the early stages or when there’s a significant influx of new annual plan customers. Including these customers in the denominator of churn rate calculations artificially reduces the churn rate. To address this, it is recommended to segment customers based on their plan durations and perform separate cohort analyses for those on monthly plans and those on annual plans. This approach ensures more accurate and meaningful insights into customer retention and churn patterns for each group.
By segmenting customers with different contract durations and conducting separate cohort analyses, you can avoid distortions in your churn rate calculations and gain a clearer understanding of the retention dynamics within each plan type. This allows you to make more informed decisions regarding customer retention strategies, identify opportunities for improvement, and optimise your revenue growth.
How to Create a Cohort Analysis
To conduct a cohort analysis, you need to transform raw payment data into a format that allows for meaningful insights. In this article, I will guide you through the process, step by step.
Step 1: Import Your Transaction Data
Depending on your payment processor, the structure of your transaction data may vary. In your cohort analysis spreadsheet, each row represents a transaction, and you have key data points for each transaction:
- Customer onboarding date (the first charge date)
- Transaction amount
- Transaction date
- Customer ID
If all your customers are on monthly plans, you can start with this simple setup. However, if you have a mix of monthly and annual plan customers or want to analyse different customer segments, the process becomes slightly more complex. Additionally, if you have a large number of rows in your payment file or want to monitor your cohorts in near real-time, there are further considerations.
Step 2: Prepare Your Data in Google Sheets or Excel
To quickly transform your raw transaction data into a cohort analysis, follow these steps:
- Import the CSV file containing your transaction data into Google Sheets or Excel.
- Select the columns containing the customer start date, transaction amount, and transaction date.
- Go to the “Insert” menu and choose “Pivot table.”
- Set the transaction amount as values, customer start date as rows, and transaction date as columns.
- Right-click on any of the dates in the header row of the pivot table and select “year-month” from the “Create pivot date group” option.
Congratulations! You have successfully created your first cohort analysis. 🎊🎉😎
For a simple visualisation of your cohort analysis, you can select the data (excluding the “Grand Total” column), insert a chart, and choose a stacked area chart. This type of chart provides a clear and intuitive representation of cohort data. However, there are alternative visualisation options that may better suit your specific needs, which we will explore later in this article. It’s important to note that the cohort analysis we’ve created here is “right-aligned,” meaning each column represents a calendar month.
As an alternative method, you can also leverage ChatGPT’s Code Interpreter plugin to transform transaction data into a cohort analysis. While it generally performs well, it’s always recommended to verify the results to ensure accuracy.
By following these steps, you can harness the power of cohort analysis to gain deep insights into customer behaviour, retention rates, and the effectiveness of your business strategies.
Understanding Cohort Analyses
Cohort analysis is a valuable tool for gaining insights into customer behaviour and retention patterns over time. To effectively interpret a cohort analysis, it is important to understand how to read and interpret the data. In this article, we will guide you through the process, using a left-aligned view as a simple example.
Each row in your spreadsheet represents a cohort of customers grouped by the month in which they converted. The first row corresponds to the January 2020 cohort, the second row (highlighted in green) represents the February 2020 cohort, and so on.
The “New customers” column (highlighted in yellow) indicates the total number of customers you converted in a given month. The columns labeled as lifetime months 0–9 display the number of customers retained in each respective month. For example, the numbers enclosed in the red box illustrate the number of customers who remain after three months.
Month 0 represents the month in which customers converted. In the provided example, customers cannot churn within their first billing cycle, which is month 0.
To illustrate this further, let’s take another example. If we examine the number in the blue cell, we can see that out of the 115 customers acquired in May 2020, 105 are still retained two months later.
You may notice the absence of numbers in the bottom right corner, which gives cohort analyses their characteristic triangular shape. This is because the sample data provided is from October 2020, meaning the October 2020 cohort is still in its initial month, the September 2020 cohort is in its first month, and so on.
In the example above, the absolute number of customers is presented. However, it is often more insightful to view the data in percentages, as it facilitates comparison of retention rates across different cohorts.
Here are two alternative ways of presenting the same data:
The first table demonstrates the percentage of customers lost from each cohort throughout their lifetime, relative to the initial cohort size. For instance, in the fifth lifetime month of the April 2020 cohort, the value is 4.5% because five out of the original 110 customers churned during that month (5/110 ≈ 4.5%).
The second table displays the churn rate relative to the previous month. At the end of the fourth lifetime month, 97 out of the initial 110 customers from the April 2020 cohort were still retained. Accordingly, based on this calculation, the churn rate in the fifth lifetime month is approximately 5.2% (5/97 ≈ 5.2%).
The distinction between these two methods is subtle but significant. Consider the following example: If you start with 100 customers and lose three customers per month (i.e., 3% of the initial cohort size), all customers would be gone by the 34th month. However, if the monthly churn rate is 3% of the remaining customers from the previous month, you would still have 25% of your customer base after four years.
Colour Coding Your Cohort Analysis Chart
Assigning different background colours to cells in a cohort analysis can aid in identifying trends and highlighting issues. For instance, in the screenshot provided, you can immediately observe that the average churn rate decreases over the customer lifetime, as the red colour transitions into orange and yellow. Additionally, you can quickly identify that month 5 was an unusually challenging period for the April 2020 cohort.
When choosing colours for your cohort analysis, it is crucial to be mindful of their associations and ensure they align with the intended meaning. Different colours carry different connotations, and improper colour gradients can lead to confusion rather than enhancing understanding.
Commonly accepted colour conventions involve using green to represent “good” values, such as desired churn or retention rates. Red is often used to signify “bad” values, while yellow indicates a moderate or acceptable range. Gradients can be employed to represent values falling between these categories. However, it’s worth noting that in the provided example, a simpler approach was taken, using yellow to represent 0% churn and red to represent a 5% churn per month. In the revenue retention example mentioned earlier, a strong green shade was used to indicate 100% revenue retention, while a lighter green shade represented 80%.
By effectively applying colour coding techniques to your cohort analysis, you can enhance the visual clarity of the data and gain deeper insights into customer retention patterns and potential areas for improvement.
Working with Cohort Analyses
What to Look for in a Cohort Analysis
When conducting a cohort analysis, there are several key factors to consider. Let’s explore the most important ones:
- Improvement in Retention: One crucial aspect to examine is whether retention, whether customer or revenue retention, improves over the customer lifetime. To evaluate this, start from the left side of the analysis and move horizontally to the right, observing if the churn rate decreases.
In the provided example, the monthly customer churn rate shows a decline from an average of 4-5% per month in the initial three lifetime months to approximately 2-3% per month in the subsequent months. While the available data doesn’t indicate if it will stabilise around 1.5-2% per month after the ninth month, this is an encouraging trend to observe. - Time-based Retention: Another essential aspect is to assess whether retention improves over time, specifically whether more recent cohorts exhibit higher retention rates compared to older cohorts. By starting at the top and moving down the analysis, you can identify any positive trend towards reduced churn, potentially resulting from factors like increased product maturity, enhanced onboarding processes, or other improvements.
In the given example, no clear trend is evident in either direction.
Here’s an additional fictional example that examines revenue retention from a different company:
In this instance, it becomes apparent that more recent cohorts consistently outperform older ones. This distinction is easily discernible by observing how the cells become progressively more yellow as you move down each column. - Customer Acquisition Spending: Determining how much you can allocate to customer acquisition is reliant on understanding your Lifetime Value (LTV) and deciding on an acceptable CAC/LTV ratio. However, estimating LTV, especially in the early stages, can be challenging. A pragmatic approach involves setting a limit for CAC payback. For instance, you might consider it acceptable to invest up to 15 months of (Gross Margin adjusted) Monthly Recurring Revenue (MRR) for acquiring a new customer. Monitoring customer retention over time through cohort analysis enables you to keep track of CAC payback periods effectively.
Actionable Outcomes of Cohort Analysis
Conducting a cohort analysis yields actionable insights that can drive decision-making. Here are a few examples:
- Identifying Customer Churn Patterns: Cohort analysis helps identify when and why customers are leaving. If you notice a significant exodus in the initial months followed by relatively stable retention, it’s crucial to investigate your onboarding and qualification processes. Conversely, consistently high churn rates that don’t stabilise throughout the customer lifetime may indicate a lack of Product-Market Fit (PMF).
- Estimating Customer Lifetime Value (LTV): Cohort analysis provides an effective means of forecasting customer lifetime and obtaining a reliable estimate of LTV and CAC payback times. This information is crucial in determining the amount you can invest in customer acquisition.
- Segmenting Customers: Cohort analysis allows for segmenting customers based on various criteria such as acquisition channel (e.g., inbound and outbound), customer segment (e.g., mid-market and enterprise), contract duration (e.g., monthly and annual), or geography. By analysing each group separately, you may discover significant differences in LTV among segments, enabling you to tailor your marketing strategy accordingly.
- Early Churn Warning Signs: Monitoring usage activity across cohorts can provide early warning signs of potential churn, particularly for businesses offering annual plans. Detecting these red flags early on allows you to take proactive steps to retain at-risk customers.
- Evaluating Product Performance: Cohort retention graphs are highly effective in assessing the impact of new product features, software releases, or promotional campaigns on customer retention.
Segmenting Cohorts Effectively
To gain more granular insights from cohort analysis, it’s important to segment cohorts in meaningful ways. Here are some recommended approaches:
- Acquisition Channel: Segmenting cohorts based on the acquisition channel can provide valuable insights into the effectiveness of different marketing and sales strategies. By comparing the retention rates of customers acquired through inbound and outbound channels, you can determine which channels are bringing in higher-quality customers.
- Customer Segment: Analysing cohorts based on customer segments, such as mid-market and enterprise, allows you to understand how different types of customers behave and whether certain segments exhibit higher or lower retention rates. This information can guide your targeting and customer acquisition efforts.
- Contract Duration: Cohorts can be segmented based on the duration of customer contracts, such as monthly and annual plans. This segmentation helps evaluate the impact of contract length on retention and allows you to tailor retention strategies accordingly.
- Geography: If your business operates in multiple regions or countries, segmenting cohorts by geography can reveal regional variations in customer retention. This information can be useful for targeting specific markets or identifying areas where retention strategies may need adjustment.
When segmenting cohorts, it’s important to strike a balance. While segmenting cohorts provides deeper insights, avoid creating too many small cohorts, especially when you have limited data. Start with broader segments and refine them over time as you gather more information.
By leveraging cohort analysis and effective cohort segmentation, you can uncover valuable patterns, trends, and actionable insights to optimise your customer retention strategies and drive sustainable business growth.
Visualising Cohort Data Effectively
When it comes to visualising cohort data, there are several effective ways to present and analyse the information. Here are some examples:
- Absolute MRR Chart: One way to visualise cohort data is by representing Monthly Recurring Revenue (MRR) in absolute terms for different cohorts over the customer lifetime. This chart demonstrates the company’s growth in terms of acquiring new customers and allows for a comparison of cohort sizes. However, it doesn’t provide detailed insights into retention rates.
- Retention Rate Comparison Chart: A more insightful approach is to compare the retention rates of various cohorts. This type of chart shows how each cohort performs in terms of customer retention over time. By analysing the lines representing different cohorts, it becomes apparent that more recent cohorts tend to have higher retention rates than older cohorts.
- Cohort Time-Based Chart: Another way to visualise cohort data is by plotting the customer lifetime on the x-axis and different points in time (e.g., 1M, 3M, and 6M) on the y-axis. Each line in this chart represents a specific point in the customer lifetime, while the sections on the x-axis represent different cohorts. This chart allows for tracking the progression of cohorts over time.
- Break-Even Analysis Chart: A useful chart for assessing customer acquisition costs is the break-even analysis chart. It shows the cumulative Gross Profit minus Customer Acquisition Costs (CAC) for different customer cohorts. By examining this chart, it becomes clear how long it takes for each cohort to reach the break-even point, indicating when a customer becomes profitable. The chart highlights that more recent cohorts tend to break even faster than older cohorts.
Cohort analysis can be leveraged by different teams within a SaaS organisation to make informed decisions. Here are a few examples:
- Founders and revenue leaders can segment cohorts by pricing plans to understand the characteristics of each plan in terms of churn and retention.
- The marketing team can analyse how retention varies across different acquisition channels to identify the most effective channels for acquiring customers with higher retention rates.
- Sales leaders can segment cohorts by Account Executives (AE) to identify the sales representatives with the highest retention rates.
- Customer onboarding teams can monitor the health of customers during their initial months of subscription to proactively address any drop-offs.
- Customer success teams can analyse churn data to identify patterns, such as whether customers with discounted plans are more likely to churn or how Net Promoter Score (NPS) correlates with churn rate.
To estimate customer lifetime value (LTV) using cohort analysis, a simple formula involves dividing Average Revenue per Account (ARPA), adjusted for Cost of Goods Sold (CoGS, i.e., average Gross Profit per account), by the customer churn rate. However, this approach has limitations, such as assuming linear churn distribution over the customer lifetime.
A more accurate estimation of LTV through cohort analysis involves extrapolating revenue retention data from existing cohorts, projecting future revenue development, and calculating LTV based on the Net Present Value (NPV) of the projected revenue streams. This approach takes into account the varying revenue patterns of different cohorts and provides a more comprehensive understanding of customer lifetime value.
By leveraging cohort analysis and employing various visualisation techniques, SaaS organisations can gain valuable insights to make informed decisions. Whether you’re a founder, customer success, sales, or marketing leader, cohort analysis can be utilised to better understand customer behaviour, optimise marketing strategies, improve sales performance, enhance customer onboarding, and drive customer retention efforts.
Remember, effective cohort analysis involves segmenting cohorts based on relevant criteria such as pricing plans, acquisition channels, sales representatives, or customer characteristics. By analysing retention rates, revenue trends, and break-even points, organisations can identify areas for improvement, implement targeted strategies, and ultimately maximise the value of their customer base.
Understanding Cohort Retention Analysis
Key Insights and Metrics: Unlocking Customer Churn Patterns and Maximising Retention
Hidden Churn: Identifying and Addressing Inactive Customers
Hidden churn refers to the phenomenon where customers remain paying but are inactive, similar to a gym membership that goes unused. To mitigate hidden churn, it is crucial to monitor customer health and proactively engage with customers showing decreased activity. This becomes especially important for customers on annual plans to avoid unpleasant surprises during renewal.
Measuring Customer Health with Cohort Analysis
Cohort analysis offers a powerful approach to measure customer health indicators such as usage frequency and user engagement. By establishing relevant health indicators tailored to your product, like login frequency or specific product utilisation, you can identify at-risk customers within various cohorts over time. This enables proactive interventions to prevent churn.
Calculating Averages for Churn or Retention Across Cohorts
To calculate averages for churn or retention across multiple cohorts, cohort data tables provide valuable insights. The bottom rows of these tables present average churn rates for respective lifetime months, considering all old enough cohorts. This weighted average accounts for cohort size and provides a more comprehensive understanding of monthly churn rates.
Understanding the "Smiling Cohort Graph"
In consumer subscription companies, a significant portion of subscribers tends to churn in the first one or two years. However, what sets apart great businesses is the retention of the remaining subscribers over an extended period. As loyal users upgrade or return after churn, cohorts may exhibit revenue growth later in their lifespan. This generates a “smiling cohort graph,” indicating successful customer retention.
Decoding Cohort Tables Diagonally
Diagonal reading of cohort tables offers valuable insights. In a left-aligned cohort table, summing values while moving from month 0 of a cohort up and to the right provides the total Monthly Recurring Revenue (MRR) as of a specific month. This helps understand the composition of MRR by cohorts. Similarly, a right-aligned cohort table allows observation of retention rate changes across different cohorts, aiding quick assessment of retention performance.
Net Dollar Retention (NDR): A Cohort-Based Metric
Mature SaaS companies often employ Net Dollar Retention (NDR) as a comprehensive metric for tracking and reporting churn and retention. NDR compares recurring revenue from a specific set of customers over comparable periods. To calculate NDR, create cohorts based on active customer contracts at a particular date, examining changes in Annual Recurring Revenue (ARR) over time. This analysis enables a condensed overview of retention performance.
Finding NDR in Cohort Tables
For those curious about NDR in cohort tables, the diagonal read technique comes into play. Summing values diagonally, from bottom left to top right, reveals the total ARR or MRR as of the starting month. Shifting the diagonal to the right unveils the revenue from the customer group at later time points, allowing tracking of NDR over different periods.
By conducting cohort retention analysis and leveraging these insights, businesses can gain a comprehensive understanding of customer behaviour, improve retention strategies, and optimise long-term customer value.