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 using a spreadsheet. Together, we’ll follow a step by step process, using a simple user log spreadsheet to demonstrate.
User logs are often used by both offline and online subscription businesses such as gyms, content subscription businesses, and subscription boxes. This type of spreadsheet should be familiar to any business that relies on members 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 both the user log file to follow along with this tutorial, and the final analysis spreadsheet showing the calculations, tables and insights you’ll have after following the steps in this article.
Note: If you’re more of a visual learner, scroll down to the bottom of this article and you’ll find a video version of this tutorial.
What is Cohort Analysis?
Before we get started, you might be wondering what cohort analysis is. You’re no doubt familiar with churn rate and retention, but if you haven’t carried out cohort analysis before, this type of behavioural analytics involves taking data about user behaviour, and rather than looking at all users at once, splitting the users into groups based on their behaviour or lifespan.
Even if your business is only recently established, and you only have a few months of customer data, cohort analysis will give you valuable insights into how customers are responding to your business, products or services.
You can gain a clear understanding of user engagement, identify any lack of activity by certain user groups, and look at engagement separately to growth. 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.
How to Use Cohort Analysis to Calculate Retention and Churn
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. 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 on a monthly basis, 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.
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, and for values select ‘User ID’ and make sure this is summarised by COUNTA. And, as one final step to make the presentation a bit clearer, just unclick ‘show total’ on the Month and Year columns.
The resulting table will show you how many events occurred each month. You can also 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.
You can see an example of what your pivot table should look like in the screenshot below.
Caption: A Google Sheet summary of user signups, renewals and unsubscribes by month and year.
Step 2: 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, but 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’ which 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, labelled ‘cohort_lookup’ and rename the column ‘month_no’ to ‘cohort_month’ for clarity.
Step 3: 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’.
Caption: A spreadsheet showing calculations for month number, cohort month and cohort age.
Step 4: 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 5: Create a User Profile Pivot Table to Display User Behaviour
For the next step, you need to create another pivot table which will show 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 left or unsubscribed.
So 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.
Caption: A table listing users by their cohort_id and showing the total number of users per month.
Step 6: Cohort Retention Analysis
Using the user profile pivot table that you created in Step 5, you can create a user retention table which will show you 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.
Caption: A table showing the monthly user retention rates of each cohort_id.
Step 7: 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, and how ‘old’ users are when they unsubscribe.
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 unsubscribed before Month 1. One explanation for this is if the sign up came with the offer of a free trial, lots of users may have cancelled their subscription before the first payment.
Caption: A monthly user churn analysis table showing incremental churn percentage.
Step Eight: Analysis and Interpretation
The user log we’ve used in this tutorial might be a simple dataset, but 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 and 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, though, is that because this data only has 14-20 users per cohort, random occurrences can cause fluctuations in patterns and trends. However, 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, or while taking local or global events into account. You can also use data as a benchmark to compare against future user behaviour.
And once you’ve made sense of your data and gained a better understanding of when users are signing up, how long they remain customers for, when they unsubscribe and what causes them to leave your business, 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.
Hopefully, you’ve found this tutorial helpful and now feel confident enough to carry out cohort analysis on your own business data. If you have any questions about data analytics, or you need help maximising the value of your business data, don’t hesitate to get in touch and schedule a free consultation.
ChallengeJP data consultancy can help your business make the most of data and achieve data-driven success.