How to do cohort analysis in Excel

Here is an ultimate guide on what information do you need to collect and how to use this in data later on to do cohort analysis in Excel.

What to collect

I will separate this information to two sets – minimal requirements and additional information that can be handy

At minimum, you have to collect information about every payment made by your customers for your service. Here is a list of attributes you will be looking for:

  1. Customer ID – some unique string that can be used as an identifier for your customer. If you are in a business-to-business, tax id could be a good candidate.
  2. Service Start At – a first day customer has access to your service because of this payment.
  3. Service End Date – when access to the service customer paid for ends.
  4. Total Amount Paid – how much money in total customer paid you in this transaction
  5. Monthly amount – this is easy to calculate as Total Amount Paid / Number of Month Paid for
  6. Revenue Type – if you are in a recurring revenue business, you have to separate revenue that you expect to receive regularly (recurring) from revenue that is paid only once (one-time). Usually one-time revenue is revenue the customer pays you for Professional Services engagements or onboarding.

Additional attributes that can help you to enhance your analytics:

  1. Invoice ID – basically something that will help you track back to the original invoice, which has more details about this payment.
  2. Product Name – this comes very handy if you are selling more than 1 product or plan. It will allow you to segment your revenue by this name
  3. Any information about your customer – such as country / industry or business size – this attributes will allow you to understand your business deeper by segmenting results.
  4. Original Currency and paid values in original currency – if you are receiving payments in multiple currencies, you will need to convert them into one currency. You can either select one conversion rate for all data, or use Google Spreadsheets GOOGLEFINANCE formula

Where do I store this data

If you have around 50-70 customer – Excel or Google Spreadsheet may be a good place to start with.

Probe is also a perfect place for such information that can automate not only collecting this data, but also take care of all the updates and analytics on your behalf.

When it comes to keeping this information up-to-date, if the number of customers is tiny – consider doing it manually once a month. When it becomes tricky due to your scale Probe can help you.

How to calculate my metrics

Once you have all of your data in Sheets. You can use our Probe Google Spreadsheet plugin. It will take care of all the calculations for you.

All you need to do is to push this data inside Google Spreadsheet and give column headers correct names as below. It is important that headers match exactly the name specified in the list. Probe plugin uses this strings as keys to identify what data stored whee.

  1. customerid – your customer id goes here
  2. startedat – date when service starts for this payment
  3. endedat – date when service ends for this payment
  4. mrr – monthly amount contributing to your revenue

Here is the template you can view and copy to play with it.

Now all you have to do is two things: Select 4 columns with data Go to the top bar menu and click Extensions –> Probe –> Calculate Metrics Using Probe

This will automatically create a new sheet for you with basic metrics for your business.

What metrics Probe plugin supports

Here is a quick list of metric Probe will calculate with your basic data:

  1. New MRR – amount of recurring revenue that came from new business – customers that you have not done business with in the past.
  2. Expansion – this tells you how much money you gained due to existing customers starting to pay you more
  3. Contraction – this tells you ho much money you lose due to some of your existing customers starting to pay you less
  4. Churn – how much money you lost because customers left your service and stopped paying your
  5. MRR – total amount of recurring revenue for a given month, it already includes new MRR as well as all the movements that may happen to your existing customers
  6. LTV – this is an estimation of what is the potential amount of money you could expect to receive from a customer when he joins your service.
  7. ARPU – this tells you how much recurring revenue on average you are receiving per customer. It is a useful number to know to do a quick estimation of what will it take to reach your revenue goal. Ex. if your ARPU is $100, and you want to get to $100k in MRR you know that you need to find $100,000/$100 = 1000 new customers to your service

In addition to this, you are getting a couple of charts that visualize the same data.

How to do cohort analysis

We already wrote extensively about what is cohort, how to use cohort analysis and how to read it. So go there for this additional information.

It may get a little tricky to build one in spreadsheet, but now Probe Plugin helps you with this. All you need to do is again select these 4 columns of data and click Extensions –> Probe –> Calculate Churn Cohort using probe

This will show you your churn cohort. Cohort groups customers that start using your service within the same month, then looks into initial revenue you received from this group of customers and shows you what percent of this initial number got lost as time passes.

Again, here is a more details blog post on how this works and what you should be paying attention to in this analysis.

What next

A couple of useful resources for you:

  1. Analytics with Excel – a quick page to get access to this and other tips & tricks on how to do data in Excel
  2. Google Sheets Probe Plugin – install it from the Google Marketplace
  3. Spreadsheet Template you can copy to play with Google Probe Plugin
  4. If you grow beyond the spreadsheet more – talk to us – and we will help you to automate not only data collection but also your whole analytics.

alex Co-Founder at Probe