After working with a couple of our portfolio companies on cohort analyses, I eventually put together a cohort analysis template. In the spirit of open sourcing and (hopefully) helping out some founders, I’m posting it here with an explanation and a how-to guide for better understanding the fundamentals of a cohort analysis.
Before we begin, I want to preface this by saying that it’s a basic template, but should cover most bases initially. At a minimum, it should provide a helpful template to build on top of. It’s delightfully simple, so hopefully that makes everyone’s lives easier. I’ve used some excel wizardry so that inputs are incredibly easy, and no more copy-and-pasting new formulas down a waterfall every time a new month of data is added.
What is a cohort analysis and why do you need one?
Let’s start off at the beginning. A cohort analysis is a way to measure your company’s customers through bucketing. Most people segment their cohorts by month, as in, the initial month of purchase or sign-up. This means that you can track the data of all customers who initially purchased in January of 2016, for instance, and see how frequently they are purchasing, or, if they’ve canceled their subscription.
If you were to ignore a cohort analysis, you’d only see the big picture, and wouldn’t be able to see the order velocity and how well customers interact with your product or service over time. If you were simply to be concerned with top line growth, you could go ahead and acquire expensive new customers. In order to build a sustainable business, it is imperative to have repeat customers, and keep those customers as engaged as possible, since they’re much cheaper to retain than constantly acquiring new customers. A cohort analysis helps you understand the life cycle and behaviours of your customers.
What metrics should a cohort analysis track?
A cohort analysis shows you many things, which are helpful to track the health of your business over time:
- Retention rate: For a subscription business, it is imperative to be tracking retention of customers, or when they decide to cancel service. This is usually done in 30-day, 60-day, 90-day, and 1-year retention, and potentially further. A healthy business will want to keep its customers as long as possible, as they’re much easier to make money from than attracting new customers.
- Repeat rate: For an eCommerce or appointment-based business, repeat rate measures how frequently someone is coming back to buy. Unlike a subscription business, there’s no obligation to return, but similarly, it is much easier to get customers to return than having to acquire new customers. Repeat rate benchmarks will vary by type of business. For example, for a mattress company, you might expect a customer to come back every 5 years, whereas for a beauty-appointment business, you might expect customers to book every 3 months.
- LTV: Using your retention rate and multiplying it by your monthly subscription rate, gives you your customer lifetime value. Alternatively, you can measure actual revenue data if there is variation. Ideally, this would be over the rough lifetime the customer stays with the business, but it can also be measured in a finite amount of time, such as a 1-year LTV. For eCommerce businesses, this can be measured by the amount of purchases over a year, multiplied by average order size.
- CAC: Customer acquisition cost is the amount of money a business spends to acquire a customer. In general, it will be monthly marketing spend divided by the number of new customers attracted to the platform (subtracting any marketing spend on retaining existing customers)
- LTV:CAC: This ratio will tell you how much value you get from a customer, relative to the amount of money you spend on them. Good ratios will vary by business; however, an LTV:CAC of minimum 3x is a good general threshold.
How to use this template
Everything in orange shading needs to be filled out — those are hardcoded numbers. The fields are as follows:
- First month of analysis
- Latest month (last date of data)
- Monthly subscription fee (for subscription businesses)
- Cohort data starting in row 61, which includes new customers within the cohort, marketing spend, and individual cohort data for that month (for subscription businesses, it’ll be total customers still on the platform, for eCommerce companies, it’ll be purchases made by customers in that particular cohort).
Outputs are fairly straight-forward, and can be found at the top, divided into info for subscription businesses and eCommerce businesses. From here, the template can always be customized and any specific data or outputs can be added that are desired.
Here’s the link to the excel file, which you can download for yourselves.
Please drop me a line with any questions, comments or ways I can make this better. Hope it’s helpful!