Cohort Analysis in Excel: A Comprehensive Guide

Analysing customer data, establishing trends, and gaining actionable insights has become a fundamental strategy to spearhead growth in organisations worldwide. Cohort Analysis Excel is an advanced analysis tool that allows businesses to better cater to their target audiences by drawing powerful insights from them.  

Despite the abundance of advanced data analysis tools on the market, Excel stands out due to its ease of use and the variety of tools on the platform. This is further supported by the fact that according to a survey by Statista, more than 86% of respondents reported using Excel tools for work-related everyday tasks. 

In this blog, we will explore Cohort Analysis Excel in detail, including the reasons to use it and the steps to construct it in Excel.  

Table of Contents 

1) What is Cohort Analysis Excel? 

2) Steps involved in Cohort Analysis Excel 

3) Benefits of using Cohort Analysis Excel 

4) Cohort Analysis Excel example 

5) Conclusion 

What is Cohort Analysis Excel?   

Cohort Analysis Excel is a tool for analysing customer behaviour over a period of time. It involves dividing customers into groups based on when they became a customer, i.e., their cohort, and then tracking their behaviour over time. Excel can be used to conduct Cohort Analysis by using formulae and pivot tables. To perform this analysis in Excel, you will need to organise your data by groups or strata, and track metrics such as Customer Lifetime Value (CLV), retention rate, and average purchase value over time.  

The main purposes of Cohort Analysis are: 

a) To help businesses understand how customer behaviour changes over time and identify areas for improvement in customer acquisition and retention strategies.  

b) To help businesses calculate the lifetime value of a customer or user, which is the total revenue that a customer generates for a business over a period of time. 

c) To help businesses understand the impact of changes they make to their products, services, or marketing strategies.  

d) To formulate marketing strategies by identifying the most effective channels or campaigns for acquiring and retaining customers.  

e) To optimise the product development lifecycle by identifying which features or product enhancements are most appealing to different strata of users.

Steps involved in Cohort Analysis Excel 
 

Here are the steps involved in performing a Cohort Analysis Excel:

Step 1: Collect the data 

The first step in conducting a Cohort Analysis is to collect the necessary data. This data should include information about your customers or users, such as the date they joined, their purchase history, and any other relevant information you want to analyse.  

In this step, you should also define the metrics you want to track and the main goal that you want to analyse during the analysis.  

Step 2: Define the cohorts 

Once you have collected the data, you need to categorise the data based on the cohorts you want to analyse. Cohorts are groups of customers or users who share similar characteristics. To define the groups, you will need to create a table that lists the unique values for the characteristic you want to analyse.  

Cohorts are generally categorised based on three divisions: 

a) Time: The Time category allows you to group your customers based on time frame. For example, organisations may choose to categorise their customer data based on the time of subscription to an email list.  

b) Segment: The Segment category allows you to group your customers based on a specified segment. For example, organisations may choose to categorise their customer data based on the tier of subscription they have availed.  

c) Size: The Size category allows you to group your customers based on the budget that needs to be spent on them. For example, organisations may choose to categorise their customer data based on the budget for ads targeted to specific groups of individuals.  

Step 3: Calculate the metrics 

Next, you need to calculate the metrics you want to analyse for each cohort. The common metrics that are measured are: 

a) Customer Retention Rate (CRR): Customer Retention Rate refers to the percentage of customers who continue to do business with a company over a specified period of time.

Formula - CRR = ((E-N)/S) X 100, where,

E is the number of consumers at the end of the period,

N is the total number of consumers acquired during the period, and, S is the number of consumers at the start of the period.

The higher the CRR, the more successful a company is at keeping its existing customers loyal and happy. It is generally considered to be a positive indicator of customer satisfaction and brand loyalty.  

b) Repeat Rate: Repeat Rate helps track customers who have interacted, purchased, or inquired with the business repeatedly in comparison to consumers who have only transacted with the business once.  

Formula – Repeat Rate = N/T X 100, where, 

N is the number of customers who have repeatedly transacted with the business, and,  

T is the total number of customers.   

c) Orders per Customer: Orders per Customer helps track the number of orders each customer places with the business. A high Orders per Customer metric means the retention rate for the service or product is high, implying the performance of the product is good. 

d) Average Order Value (AOV): Average Order Value is a metric that helps the business identify high-value cohorts that need to be further targeted to build brand loyalty in a profitable way. Using this metric, businesses can differentiate between cohorts based on the revenue they bring and can specifically target customer groups that bring them the most profitability. 

Step 4: Construct the cohort chart 

Once you have calculated the metrics, you can create a cohort chart to visualise the data. A cohort chart is a stacked bar chart that shows how each group performed over the time period. The x-axis represents the time period, and the y-axis represents the size of the cohorts. Each bar in the chart represents the metric you are analysing.  

Step 5: Interpret the results 

Finally, you need to interpret the results of the Cohort Analysis. Look for trends and patterns in the data, such as whether certain groups have higher or lower retention rates than others. Use this information to make data-driven decisions that optimise the customer experience and enhances customer retention.  

Integrate Excel workbook settings and learn how to troubleshoot formulas with our Microsoft Excel Expert MO201 course now! 

Benefits of using Cohort Analysis Excel   

Listed below are five benefits of using Cohort Analysis in Excel: 

a) Identify trends and patterns: It can help businesses identify trends and patterns in user behaviour, allowing them to see how certain groups of customers behave over time. This information can be used to create targeted marketing campaigns, optimise product offerings, and improve customer retention.  

b) Measure customer lifetime value: It can help businesses understand the lifetime value of their customers by tracking their spending patterns over time. This allows businesses to better understand which customers are most valuable and how to keep them engaged with their brand.  

c) Improve customer retention: It can help businesses develop proactive plans and identify factors that lead to customer churn. This will allow businesses to take proactive steps to improve customer retention. By analysing groups of users who have stopped using their product or service, businesses can identify common patterns and take steps to address the underlying issues.  

d) Optimise marketing campaigns: It can help organisations optimise their marketing campaigns by identifying which channels and campaigns are most effective in acquiring and retaining customers. By tracking user behaviour over time, businesses can also identify which campaigns are driving the most engagement and revenue.  

e) Better decision making: Like any analytics tool, Cohort Analysis facilitates better decision making for the organisation. It provides businesses with valuable insights into customer behaviour, allowing them to make informed decisions about aspects such as product development, marketing, and customer service. By analysing user behaviour over time, businesses can identify improvement areas and make data-driven decisions that drive growth and profitability.  

Cohort Analysis Excel example 

Here's an example of how to conduct a Cohort Analysis for an e-commerce website: 

Step 1: Define the cohort and the metric  

First, you need to define the cohort you want to analyse. In this example, let's say we want to look at the cohort of customers who made their first purchase in January 2022. The metric we'll be analysing is the total revenue generated by this stratum. 

Step 2: Create the cohort table 

Next, you'll create a table that shows the monthly revenue for this cohort over time. Here's an example of a cohort table:


 

This table shows the revenue generated by the Jan 2022 group each month for six months after their first purchase. The numbers in each cell represent the total revenue generated by that cohort in that month. 

Step 3: Calculate Retention Rate 

Now you'll calculate the Retention Rate for each month. Retention Rate is the percentage of customers from the initial group who made a purchase in each subsequent month. Here's what the retention rate table might look like:



 

This table shows the percentage of customers from the initial cohort who made a purchase in each subsequent month. For example, 50% of the Jan 2022 cohort made a purchase in Feb 2022. 

Step 4: Analyse the results 

Finally, you'll use the cohort and retention rate tables to draw some conclusions about customer behaviour. For example: 

a) The Jan 2022 cohort generated the most revenue in their first month, but their revenue declined steadily over time. 

b) The retention rate for each subsequent month is relatively low, indicating that many customers do not prefer the brand in the long term.  

Step 5: Taking action 

The final step is to take action based on the insights gained from the cohort analysis. For example, if according to the example, we find that customers who signed up in January have a higher retention rate, the marketing strategy will have to be adjusted to focus on attracting more customers in January. 

Conclusion 

In conclusion, Cohort Analysis Excel is a powerful tool for gaining insights into the behaviour of different customer segments over time. By using this technique, businesses can identify patterns and trends in customer behaviour, which can inform important decisions about product development, marketing, and customer retention strategies.  

With the step-by-step guide provided above, you should be able to get started with Cohort Analysis, even if you have little experience with data analysis tools and techniques. By mastering this flexible tool, decisions made on marketing and development can be data-driven, helping the business thrive in an increasingly competitive marketplace.  

Learn how to create the key analytics graphs and charts in Excel with our Advanced Microsoft Excel 2019 55270AC course now!