Revenue Retention Analysis: What to Look For (Part IV)

This blog post is about how to interpret the data and results of a billings/revenue retention analysis, and focuses on trends and patterns that you should look for. It is part of a series of posts that serve as a step-by-step guide on conducting the analysis from start to finish. 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 perform this analysis at some point during the due diligence process. Presenting this data upfront will save them time and likely impress their management teams with your “metrics-driven approach” to management.

In the last two posts, I discussed some trends, patterns and anomalies you should look for in a billings growth by cohort chart. In this post, I will cover how to calculate the expected 1-year billings and expected lifetime billings a cohort will generate, and how this information can be used to benefit a business.

To calculate the expected one-year billings that the most recent quarterly cohort of customers will generate, you will have to analyze the billings growth data. Aggregate a few quarters worth of customers for which you have at least one year’s worth of billing data, and calculate billings for months 1 through 12 as a percentage of the first month’s bill. Then, sum those percentages and multiply them by the total first month bill for the cohort whose expected one-year billings you are calculating. For example, if historical data shows that billings as a percentage of first month’s bill decrease by 2% a month (month 1 is 100%, 2 is 98%, 3 is 96%, and 12 is 78%), and the first month’s total bill of the quarterly cohort whose expected one-year billings you are calculating is $100,000, then the expected one-year billings for that cohort will be (1+0.98+0.96+0.94+…+0.78) * $100,000 = $1,068,000.

Calculating the expected lifetime billings that a cohort of customers will generate is a bit more complicated. To do that, aggregate a few quarters worth of customers and calculate total billings by month. You will then plug it into the following formula (except that in our case, we will be using months instead of years):

Let’s assume that the total billings in month 12 are $78,000 (ending value) and the total billings in month 1 are $100,000 (beginning value). The number of time periods that have passed (in our case, number of months) is 11. In this scenario, the monthly billings attrition is (0.78)^(1/11) – 1, or 2.23%.

Then, total the first month’s billings of the cohort who lifetime billings you are calculating, and divide it by the monthly billings attrition rate. If the total first month bill is $150,000, and the monthly billings attrition is 2.23%, you can expected that cohort to generate $150,000/0.023, or $6.7 million over its lifetime.

This information will allow you to predict how much billings your most recent customer cohort will contribute over time, and will help your company put together more accurate revenue forecasts, and build more realistic budgets. It will also let you calibrate how much your company should be spending in sales and marketing to acquire customer cohorts. If the time to recoup sales and marketing spend is abnormally long (such as over two years), this indicates that the company should think about ways to improve the effectiveness of its sales and marketing spend, or perhaps raise capital to fuel growth. Finally, this information allows a company to figure out which products, product versions, or customer segments to focus its sales and marketing efforts on, and how much in sales and marketing to spend on each.