How to Perform Cohort Analysis & Calculate Customer LTV in Excel

What is cohort analysis?

First, a cohort analysis is how a lot of companies gain deeper insight into the behavior of their users/customers. They can then leverage this insight to build successful growth strategies. In a cohort analysis instead of looking at users/customers individually, they are placed into related groups known as cohorts. Each person in a cohort must share a related yet distinguishable trait that separates them from the other cohorts. The most common trait used to do this is date — specifically the date at which each user/customer starts using the product. These dates can be grouped by day, week, month or even year, and determining which one is best is dependent on what type of questions your analysis is attempting to answer.

What is customer LTV?

Customer lifetime value (LTV) is the gross profit a customer will generate or has generated over their lifetime.

1. Calculating customer LTV is one of the best ways of building an effective acquisition strategy because knowing the projected LTV of a customer allows a company to determine how much they can spend to acquire customers and still generate a profit. Companies typically use the 3:1 ratio meaning on average if customers spend $300 over their lifetimes a company can spend up to $100 to acquire them, which allows companies to generate a real ROI on customer acquisition.

2. It is almost always cheaper to retain current customers than to acquire new ones. Customer LTV is crucial here because it allows a company to measure the effectiveness of their retention strategies over time by measuring how each strategy impacted the lifetime value of their customers. You can further segment your customers to determine which ones have the greatest lifetime value then focus your acquisition/retention strategies, branding/messaging and customer outreach to target them.

3. There is a lot of confusion surrounding calculating customer LTV and one of the reasons is that there are multiple ways of calculating it depending on your type of business. For a small SaaS MRR company you can typically use the simple LTV formula (Avg Revenue x Time) due to the high margin/high churn aspects of the business model, but beyond that it is imperative to factor in other variables.

[Note: To determine which customer LTV formula is right for your business check out these resources:

1. Kissmetrics Customer LTV Infographic

2. How to Calculate Customer LTV in E-Commerce

3. The Ultimate Guide to SaaS Customer LTV]

Why does it matter?

Cohort analysis and customer LTV allows you to assess exactly how a group of your users/customers behaved over their lifetimes. Individually too many factors could be responsible for a single customer’s LTV for you to develop accurate assessments. If looking at the overall data, certain assessments may be true for some customers and horribly wrong for others, which could lead to decisions that can cause significant damage to your business.

In any organization there will be various opinions about how to move forward and what is best for the company. You will find that many of these opinions are often in conflict with each other and there is seemingly no easy answer. In cases like this and even cases when the answer seems obvious it is always a good idea to check the data to see the objective results of your acquisition and retention strategies. Cohort analysis and customer LTV will change the way you view the value of your user/customer base, and will provide measurable data to help determine what kind of acquisition/retention strategies, branding/messaging and customer outreach you need to move forward.

What are the stages of cohort analysis?

1. Determine what questions you want to answer. The point of an analysis is to produce actionable information you can use to improve your business, products, user experience, etc. To ensure that happens, it is important that you are answering the right questions. Answering the wrong questions can make your analysis essentially useless.
2. Define the metrics that will be able to help you answer the question. A cohort analysis requires you to identify measurable events such as a subscription start and cancel dates as well as specific properties such as the value of a customer’s monthly payment.
3. Define the specific cohorts that are relevant. Each person in a cohort must share a related yet distinguishable trait that separates them from the other cohorts. The most common trait used to do this is date, such as the date at which each customer starts using the product, but this can be segmented further to examine differences between other traits. (such as the differences between each subscription plan type)
4. Perform the cohort analysis. There are various methods you can use to perform a cohort analysis. In Excel it is generally done using pivot tables and data visualization. In this example we are going to use data visualization to examine churn rate, monthly revenue and customer lifetime value.

How do you build a cohort analysis and calculate LTV in Excel?

[Note: If you don’t have your own data set and just want to practice, you can use my sample data located here: Sample Data Set]

1. Examine and clean the data set. Before starting any kind of analysis, the data set must first be examined to understand what type of user/customer data is currently being tracked. Search for any errors or abnormalities, such as cancel dates beginning before the start date, whales skewing the results, etc.

2. Expand the data set to include new columns. The current data is the basis for a cohort analysis, but in order to do it you need to first use it to calculate new information, such as cohort, number of active months and customer LTV.

• Cohorts. There are various ways to group customers into cohorts, but for this analysis each cohort will represent the month the customer was acquired. This format needs to be uniform for all customers in the cohort and can be calculated using the “End of Month” function, which in this case finds the end of the previous month and adds one to get the beginning of the current cohort.

• Number of Active months. This is the customer’s lifetime. (from when the customer was acquired to when they stopped using the product/service) This can be calculated through the DATEDIF function, which calculates the number of days, months, or years between two dates. In this analysis the active months are inclusive because customers pay during their cancel month.

• Customer LTV. There are various methods to measure LTV. In its simplest SaaS MRR form take the customer’s monthly payment and multiply it by their active months to generate total revenue.

3. Data Visualization. Use your expanded data set to group your individual customer data into cohorts, and from there you can start building charts to visualize your data and aid in your analysis.

[Note: Before attempting any of the functions specified in this article it is important to first understand the proper syntax and logic. Press F1 (PC) to access the Excel Help menu and look up information on each function. There are also excellent online resources such as ExcelJet.]

Cohort Churn Analysis

4. A cohort churn analysis will examine how well you have retained customers over each cohorts’ lifetime. Cohorts that do well will provide insight into what to replicate in the future, and high churn will help you determine which strategies to change.

• To perform this analysis first you need to understand the COUNTIFS function, which will count the number of cells in a specified range that meet certain criteria.

• In a MRR cohort analysis we can use this function to count the number of active customers in each cohort. (Those who still have active subscriptions)
• This COUNTIFS function has two conditions that must be met:

1. If the cohorts in the specified range are Equal to the current cohort (e.g. Aug-14)
2. If the plan cancel date is After the end of the previous month

• The COUNTIFS function (Current active customers) is then Divided by the total number of customers in the cohort to get the percentage of the active customers per month.

[Note: The cells must be formatted correctly to get a percentage. Right click on the cell selection, go to Format Cells, go to the Number tab and select Percentage.]

[Note: Before attempting this function understand how to lock and unlock formulas in Excel, so you can control how they change when copied to different areas. F4 (PC) is a shortcut to change how a formula is locked.]

• Locked: $C$2
• Column Locked: $C2
• Row Locked: C$2
• Unlocked: C2

5. The Final Product is a visualized analysis of customer churn for each cohort, which will significantly aid in understanding which retention strategies were not effective and which should be replicated in the future.

[Note: To go deeper into a churn analysis, further segment your customers to understand how the churn rate of certain types of customers compare to other types (e.g. Segment by plan type to compare customers by plan).]

[Note: In order to apply the color scales in the final product you need to apply conditional formatting to the area. Excel even provides the ability to add additional rules to the formatting to allow greater customization.]

Monthly Revenue Cohort Analysis

6. A cohort churn analysis is a great way to visualize what percentage of customers are retained each month, but not all customers are created equal. Some customers will spend hundreds of dollars on your product or service, while others will spend next to nothing. With that in mind you will need to know more than the monthly customer churn rate. A monthly revenue cohort analysis will show how much revenue per month a cohort has generated over its lifetime.

• The SUMIFS function is almost the same as the COUNTIFS function only instead of counting it will sum all the data in a specified range if certain criteria are met.

• This SUMIFS function has two conditions that must be met:

1. If the cohorts in the specified range are Equal to the current cohort (e.g. June-14)

2. If the plan cancel date is After the end of the previous month

• The Number of People in Cohort is calculated through a basic COUNTIF function with only one condition that must be met: Does the customer’s cohort equal the current cohort?

• Total Cohort Revenue is calculated by simply adding all the monthly payments together to get a total for each cohort.
• The Average Revenue Per Customer is calculated by taking the total cohort revenue and dividing it by the number of customers.

7. The Final Product is a visualization of monthly revenue over the lifetime of each cohort. This allows you to see exactly which months cohorts were bringing in significant revenue and for how long.

[Note: Calculating a percentage based on monthly revenue rather than customer churn can also be done by modifying the SUMIFS function.]

8. Revenue Churn. Simply take the current monthly revenue and Divide by the original monthly revenue for each cohort. Reformat the cells to output percentages.

Cumulative Cohort Analysis

9. In a cumulative cohort analysis, you examine the total revenue each cohort brings in over its lifetime. Each month the new revenue is added to the total until there are no longer any active customers in the cohort. This is a great way to explain to potential investors how your business is improving over time.

• To understand how to perform a cumulative cohort analysis you must first understand the IF function, the SUMIFS function and how nested functions work.
• The SUMIFS function is almost the same as the COUNTIFS function only instead of counting it will sum all the data in a specified range if certain criteria are met.

• This SUMIFS function has two conditions that must be met:

1. If the cohorts in the specified range are Equal to the current cohort (e.g. May-14)

2. If the plan cancel date is After the end of the previous month

• The IF function is a simple conditional statement. You specify a logical test that returns a Boolean, a value if the test is true, and a value if the test is false.

• In this IF function the SUMIFS function is “Nested” inside, meaning the SUMIFS function will only be evaluated if the logical test is found to be true, else the cell will be left blank.
• In this case if the SUMIFS function does not equal 0 (logical test), add the result of the SUMIFS function to the results of the previous month to keep a running revenue total for each cohort.

[Note: The cells must be formatted correctly to display a currency amount. Right click on cell selection, go to Format Cells, go to Number tab and select Currency.]

10. The Final Product is a visualization of total revenue over the lifetime of each cohort. This provides valuable insight into how cohorts perform relative to one another, and provides concrete data for you to look back and determine exactly which strategies worked and which ones did not. The Jan-15 cohort brought in over double the revenue of the Dec-14 cohort and customers were retained for much longer.

What happened there? Did you try a new marketing strategy? Were you endorsed by Oprah? And can those strategies be replicated? Gaining valuable insight into your customers’ behavior is the first step toward building a machine to continuously grow your company. Cohort analysis can help you take that first step on your journey to success. Good luck!

📝 Read this story later in Journal.

🗞 Wake up every Sunday morning to the week’s most noteworthy Tech stories, opinions, and news waiting in your inbox: Get the noteworthy newsletter >