Cohorts analysis for Subscription Businesses in Spreadsheets & Excel [A detailed guide]

There are different kinds of businesses and the nature of their product depends on the business model they will adopt.

I will be showing you how to do User Cohorts analysis and Revenue Cohorts analysis in Google spreadsheets and Excel on these 2 kinds of businesses below. All the formula you need is on the sample spreadsheet here.

1. Transaction businesses: These businesses are often low frequency for the regular customer like hotel booking, online house rentals, or international money transfer. This type of business model implies that you only see the customer when they need your product and you keep a cut of the transaction as your fee.
2. Subscription businesses: They are often high-frequency businesses like online music streaming or LTV-dependent businesses like health insurance or memberships. This type of business model implies that the consumer can use your product as many times as their paid plan allows as long as they make their monthly or annual payments.

To do Cohort analysis for transactional businesses, read it here.

To do Cohort analysis for subscription businesses, continue reading.

You are going to request that your engineering or data team provide you with a CSV export of all users in the period you wish to do your analysis. I typically suggest lifetime even if you had very few users in your first year as you can do Cohorts analysis with only a few users.

These are the columns/information you need in the query they export:

• User id: a unique id that lets you identify each user. These can be emails if emails are unique to each user in your database.
• Start date: This is the date the user registered or became a paying member or any kind of activity that indicates the user is active. I suggest the first payment date since this is a subscription business.
• Expiry date: This is the date the user is set to expire. So if a user makes an annual payment, you can assume the user will churn after that period. Measuring your cohorts will let you know if on aggregate users will churn after their annual payments
• Revenue: If you would like to calculate revenue cohorts too, include it. This should be the total amount of money the user has ever paid you.
• Additional details: If you would like to segment your cohorts, you can add additional details like plan names, gender, et al.

How To Do User Cohorts Analysis

Some important things to note about the business we’re creating cohorts for.

• We’ll be using some dummy data for this example that stretches back 2 years.
• This business is a meditation app that allows people pay monthly, quarterly, or yearly. So even if a user signed up in the most recent month it’s possible for them to show up as being retained ten months in because they paid for annual.
• This business has 2 plans so we can measure the Cohorts on each plan
• We’re measuring retention based on the user’s payment. If they stop paying, they will be considered as churned.
• Again, this is dummy data that I made up so our user retention curves may not make much sense compared to other meditation apps.

>>> Click this link to see our spreadsheet.

STEP 1: Group the data by the number of months they’ve spent on the service and their join month.

This means we’ll be adding 2 extra columns to our original sheet.

• We use this formula, =TEXT(B2,"mmm-yyy"), to get the start month from the Join date.
• We use this formula, =ROUNDUP((C2-B2)/30,0), to know the number of months each user has spent using the service.

>>> Our spreadsheet looks like this now.

STEP 2: Create your spreadsheet template for your Cohorts and import your data into the Cohort view.

• Since we’re measuring our Cohorts in months, we’ll add the start month on the Y-axis (Column A) and the number of months on the X-axis (row 1).
• We use a countif formula to count all users that joined in a specific month and are active in the period being measured. Example: all users that joined in Jan 2019 and are still active by the 3rd month.
• Fill out the formula in all cells. You can check the spreadsheet for the formula

>>> Our spreadsheet looks like this now.

STEP 3: Create your User retention spreadsheet from your User Cohorts spreadsheet.

• The template for the spreadsheet is similar to the template for the Cohorts sheet as the Cohorts sheet shows the absolute numbers while the retention sheet shows the relative number of users month to month expressed as a percentage.
• We use division to divide the number of users in subsequent months by the total number of users originally acquired in that cohort.
• Apply conditional color formatting so that you can see where your retention numbers fall below your benchmark.

You would notice that we retain ~60% of our users by the 5th month. Since the data was fetched in the middle of January it means we don’t know if everyone that subscribed in December will come back yet since there are still many days left till the end of January which implies that some of the December monthly sign ups have not made their renewals.

>>> View Updated Spreadsheet here.

STEP 4: Create your Cohort graph.

• Visit the chart option and insert a smooth line graph using column A as your Y-axis and Row 1 as your X-axis.

• I have data for the past 24 months on my spreadsheet, but for the sake of this tutorial I am only pulling retention data for the past 12 months and I am excluding the most recent months from the Cohort graph because their retention numbers have not come in yet (retention is a lagging number).

Here’s a screenshot of the final result.

>>> View the updated spreadsheet here.

You can also watch this video to see all the steps above.

How To Do Revenue Cohorts Analysis

This is similar to the User Cohorts explained above and a good reason to do it is so that you can measure revenue expansion. Losing users is bad and you should work on fixing churn, however, revenue expansion within your existing user base is super-important too.

STEP 1: We’ll repeat step 1 and step 2 from above which is prepping your data and creating your Cohorts template.

We’ll be making a change here by introducing an extra column on our spreadsheet to calculate how much each user contributes to revenue per month over their lifetime.

That is if a user has paid us $100 over the 10 month period they are active, their monthly revenue contribution is $100 /10 months = $10 per month.

>>> View the updated spreadsheet here

STEP 2: Import your data into the Cohorts sheet by using a SUMIFS that sums all user revenue that joined in a specific month and have spent a specific number of months on your service.

>>> View revenue cohorts spreadsheet.

STEP 3: Calculate your revenue retention.

If you’re rapidly expanding revenue you might have user churn and have revenue retention numbers greater than user churn.

From our spreadsheet you will notice the revenue retention numbers look different from our user retention this implies that revenue per user might be declining or expanding depending on what the trend line looks like.

>>>> View revenue retention spreadsheet.

Comparing user retention to revenue retention

You will notice that the retention numbers for the user and revenue cohotrs are different for the same cohort. Keep on reading to see the implication.

STEP 4: Calculate revenue expansion.

To see if revenue per user is expanding for each Cohort, we’ve applied a formula that calculates revenue per user with reference to the first month of the base cohort. In our example, we saw revenue per user increase in some cohorts which is a good sign.

>>> View Revenue Expansion Spreadsheet.

How To Draw Insights From Your User and Revenue Cohort Analysis

Retention is really important, but it’s also really hard. The 2 most important actions you can take from your analysis are:

1. Is retention declining? If it is, you need to pull out the cohorts where retention declined and figure out what is different about that cohort. You can segment your user cohort by additional data that you collected so that you can have a clearer understanding of what is happening.
2. Is revenue expanding? You’ve gained a customer and you need that user to see more value in your product and pay you to help them do more. You can sell them add-ons, or bill them on team usage.

This Twitter thread by @lennysan is a good place to start when thinking of improving retention in your product.

For pricing and revenue expansion, I recommended the Profitwell blog.

If you’ve any questions, I will be happy to help. Reach me on ayemijohnson[at]gmail[dot]com.