Your business data contains a lot of valuable information about your customers, operations, costs and finances. And by using cohort analysis, a type of behavioural data analytics, you can dig deeper into data about your customers’ behaviour and calculate your business’s retention and churn rate.
This article will guide you through how to conduct a cohort analysis to calculate retention and churn rate in Excel. Together, we’ll follow a step by step process, using a simple user log spreadsheet to demonstrate.
User logs are often used by businesses that rely on paid member subscriptions or monthly payments from customers. The user log we’ll refer to throughout this tutorial has three simple columns:
- Event — This will be either a sign-up, renewal or unsubscribe
- User ID — A number will be assigned to each customer/subscriber so you can see which users the events apply to.
- Date — A date will be listed next to each event to tell you when each user subscribed, renewed or unsubscribed.
You can download the user log file to follow along with this tutorial. The final analysis spreadsheets show the calculations, tables and insights you’ll have after following the steps in this article:
Final Analysis (Google Sheets)
Note: If you scroll down to the bottom of this article, you’ll find a video version of this tutorial.
What is Cohort Analysis?
Cohort analysis is a type of behavioural analytics. It involves taking your customer data, but rather than looking at all users at once, it splits them into groups (i.e. cohorts). The grouping is based on their certain characteristics such as demographics, interests, lifespan, etc.
Even if your business is only recently established, and you only have limited amount of data, cohort analysis will give you valuable insights into how customers are responding to your products or services. You can gain a clear understanding of user engagement and identify any lack of activity by certain user groups.
Cohort analysis will also shed light on your churn rate and retention, and by measuring these factors, you can then take action to reduce churn and improve retention.
Step 1: Prepare Data for Cohort Analysis
Once you’ve downloaded the user log for this tutorial, you’ll see the three columns with data already inputted. ‘Event’ describes a type action related to a given row (i.e. start of subscription, renewal, or user termination). ‘User_id’ is a unique identifier of a user. The ‘Date’ is the day, month and year when the event occurred.
While the spreadsheet already has enough data points to analyse the user journey, adding a few extra columns makes the job much easier. To make it possible to analyse user behaviour by the month and year when they joined the service, you need to add two columns for year and month. You can do this using the MONTH and YEAR function in Excel or Google Sheets.
Next, create a ‘month_no’ column — this will show the month in which an event occurred, counting Month 1 as the month that your first user signed up. So for example, if your business’s first user signed up in January, this will be Month 1, and if another user signs up in April, this will be Month 4.
Step 2: Create a Monthly Summary of Data
With these columns set up, you’ll be able to create a simple pivot table which shows the number of user signups, renewals and unsubscribes by month and year. To do this, select all of the data (use the Control+A shortcut), then click on the drop-down ‘Data’ menu in Google Sheets or Excel and select ‘Pivot Table’.
Then in the pivot table editor, add ‘Event’ to your rows, add ‘Year’ and ‘Month’ to your columns. For values, select ‘User ID’ and make sure this is summarised by COUNTA. And, to make the presentation a bit clearer, just un-click ‘show total’ on the Month and Year columns.
The resulting table will show you how many events occurred each month. Use this information to create a secondary table beneath this first pivot table to show the closing number of users at the end of each month.
Step 3: Assign Users to Cohorts
While showing user numbers by month can be very helpful, you ideally want to get a better insight into how users behave. To do this, you can assign users to a specific cohort (a group of users that share similar characteristics).
Cohort analysis often involves grouping users by age range, profession, gender, etc. As we don’t have that information in our example dataset, we’re going to group users based on when they signed up.
To do this, you first need to create another column labelled ‘cohort_month’. This will allow you to easily see which cohort users belong to. You’ll then need to apply a filter to your table so that it only displays sign-ups. Then copy this table and paste it into a new sheet. Name the sheet ‘cohort_lookup’ and rename the column ‘month_no’ to ‘cohort_month’ for clarity.
Step 4: Add a Cohort Age Column
With the user cohort table completed, you now need to head back over to our log sheet and finish your data preparation. Remove the filter applied in step 2, and then you’ll need to create a ‘cohort_month’ column for this sheet. This involves using a v-lookup between the user_id value and the table in the ‘cohort_lookup’ sheet. Once you’ve done this, every event of each user will be assigned to a specific cohort.
For the next step, you need to add a ‘cohort_age’ column. This will let you know how long a user has been signed up for. To calculate cohort age, subtract the ‘cohort_month’ from the ‘month_number’.
Step 5: Assign Event Value
Next, we want to assign a value to each event. For the purpose of this analysis, any ‘signup’ or ‘renewal’ will carry a value of 1 and ‘unsubscribe’ will have a value of 0. This is so that you can count the number of active users, and discount any users that have unsubscribed.
Step 6: Create a User Profile Pivot Table to Display User Behaviour
For the next step, you need to create another pivot table. This time we want to see how users behave during their subscriptions. The table should show you the date that users signed up, how long they remained a customer, and when they cancelled or unsubscribed.
When creating your pivot table, populate the rows with ‘cohort_month’, the columns with ‘cohort_age’ and for the value, add ‘event_value’ and summarise by SUM.
You should now be able to see how many users signed up in each month, how many renewed and how many unsubscribed.
Step 7: Cohort Retention Analysis
Using the user profile pivot table that you created in Step 5, you can create a user retention table. The table will show the number of users that stayed subscribed to the service as a percentage of initial signups.
For instance, looking at the screenshot below, you can see that 26.3% of users in the month 4 cohort renewed their subscription in month 6.
While the user profile table gives a good overview of absolute numbers, a retention table gives a better insight into the relative behaviour of users. For example, you can see that users from certain cohorts remain paying customers longer than users in other cohorts.
Step 8: Cohort Churn Analysis
In addition to your retention table, a user churn table can help you see how many users you are losing each month. The table will also show how ‘old’ users are when they unsubscribed or cancelled.
Looking at the month 8 cohort we can see that 93.8% (15 out of 16) of users unsubscribed from the service before their first renewal! Another interesting pattern that emerges is that on average, 64.5% of users cancelled before Month 1. One explanation for this is if the sign up came with the offer of a free trial. When the trial came to an end, users may have cancelled their subscription before the first payment.
Step 9: Analysis and Interpretation
The user log we’ve used in this tutorial is a simple dataset. However, even with this snippet of user data, we can draw some interesting conclusions.
We can see that in month 8, there must have been an event or factor that influenced user behaviour. That event caused more users to end their subscription than in other months. There might also have been factors that convinced users who signed up in months 4 or 11 to remain paying customers longer than other users.
One thing to note is that this data only has 14-20 users per cohort. Random occurrences can cause fluctuations in patterns and trends so we need to gather more data. With a larger dataset, cohort analysis will help you identify clear patterns in retention and churn rate.
When analysing your own business data, you can look at churn rate and retention alongside your sales and marketing campaigns. You can also use the historical data as a benchmark to measure the effectiveness of the future campaigns.
Look for when users are signing up, how long they remain customers for, when they unsubscribe. What do you think may cause them to leave your business? Once you’ve made sense of your data and gained a better understanding, you can use these insights to implement different strategies to improve retention and reduce churn.
Bonus Insight: Lifetime User Value
As an extra, I’ve also added a lifetime user value table to the final analysis of the user log. You can see how I created this in the video below, or take a look at the final analysis spreadsheet to see how you can use your user retention figures to calculate user lifetime value and how each user contributes to your total revenue.
Watch Video Tutorial
Get in Touch
Hi, my name is Jacek and I love data. Hope you’ve enjoyed reading this tutorial as much as I enjoyed writing it! If you have any questions about data analysis in general or any topic in particular, don’t hesitate to get in touch.
If you would like to book one of my One-to-One Excel and Data Courses, click here. Or for more info about my Data Analysis and Financial Modelling Consulting services, visit this page.
How to Create a Subscription Model with Churn Calculation – Learn how to apply churn and retention rate analysis to build a subscription model with a customer’s life cycle and lifetime value calculator.
How to Analyse Data in Excel with Power Query and a Pivot Table – Here, I will take you through an example of how to analyse and transform data in Excel using Power Query and Pivot Tables
How to Use Python and Pandas for Data Consolidation and Transformation – This step-by-step tutorial will introduce you to Python and teach you how to write scripts to speed up your work with data.
Creating a Marketing Investment Plan in Excel – Learn more about combining churn and customer lifetime calculations with marketing assumptions in a financial and cash flow model.
How to Visualise Data in Tableau – Learn how to use Tableau to quickly visualise and analyse big data files.
Learn How to Become a Self-Taught Data Analyst – In this blog, I am sharing a few tips on how to teach yourself data analysis along with some useful links.
Your First Steps in Excel – Beginner’s Crash Tutorial – If you are new to Excel and spreadsheets, this post will give you a quick overview of all the basics you need to know to start working with spreadsheets.
Hi Jacek, thank you for the article, it’s super complete and helpful!
You’re welcome, Francisco. Glad I could help!!
Thank you, Jacek, for a precise step-by-step explanation!
You’re welcome, Andre!