Revenue Retention Analysis: Creating the Cohorts

This blog post is about how to create cohorts for a billings/revenue retention analysis, and part of a series of posts on how to prepare and conduct such an analysis and interpret the results. Beyond the insights you will gain, conducting the analysis will be helpful for most expansion stage companies hoping to raise expansion capital. Many venture capital firms will do this analysis at some point during the due diligence process, and presenting it to them up front will save them time and likely impress them with your “metrics-driven approach” to management.

Once you have billings by customer by month in a spreadsheet (with the customers separated into different billing cycles, products, and product versions), you will have to distributed the customers into cohorts, or groups, based on when the customer was first billed or signed up (often, the sign-up and first billing of a customer fall in the same month, but if they differ, create cohorts based on the first billing). The cohorts could be monthly, quarterly, half-year, or annual. The reason we distribute the customers into periodic cohorts is because we want to understand how different cohorts behave over time (a good company is constantly trying to improve revenue retention by improving its product and customer service, increasing upsells, etc.). We are also trying to find out whether a lot of customers cancel their subscription after the first month? Do customer and the revenue they contribute attrite at a steady rate? Does revenue/billings from each cohort increase over time (buying more seats/usage)?

Typically, the cohort period you choose will depend on how much historical data you have (12 months, 3 years, etc.) and how many customers the company has. If you have 3 years worth of billings data, half-year or annual cohorts might be preferable to quarterly cohorts, because a chart showing the behavior of 12 quarterly cohorts will be crowded and cluttered. If you have two years of billings data and the company only has 15 customers, you will likely want to group the customer into half-year cohorts, because grouping them into quarterly cohorts may result in some cohorts having 0, 1, or 2 customers. The table below shows suggested cohort period for different data quantities (time periods):

Let’s assume that we have one year worth of revenue/billings data (2009) from a company that has signed up 5 customers per quarter, and that we have decided to group the customers into quarterly cohorts. This means that we will create 4 quarterly cohorts. For example, the Q1 2009 cohorts will contain only customers that were first billed in Q1, the Q2 cohort will contain only customers that were first billed in Q2, etc.

The company whose billings are shown above signed up 5 customers in Q1 and another 5 customers in Q2. The tables show the first 6 months worth of billings for the Q1 and Q2 cohorts. If this company offered monthly and annual billing, we would need to create separate quarterly cohorts for the monthly customers and annual customers. Similarly, if the company had different products or product versions, it would be necessary to create separate quarterly cohorts for each product or product version.