Three more ways to look at cohort data

I've just added three new charts to my Excel template for cohort analysis.

The first one shows the MRR development of several customer cohorts over the cohorts' lifetime:



Each of the green lines represents a customer cohort. The x-axis shows the "lifetime month", so the dot at the end of the line at the bottom right, for example, represents the MRR of the January 2013 customer cohort (all customers who converted in January 2013) in their 9th month after converting.

Here are some of the things that you can see in this chart:

The second chart is based on exactly the same data but shows MRR for calendar months as opposed to cohort lifetime months, and it uses a slightly different visualization:

One of the things you can see here is the contribution of older cohorts to your current MRR (something to keep in mind if you're considering a price increase and are thinking about the impact of grandfathering):

The third chart shows cumulated revenues minus CACs for different customer cohorts, i.e. it shows how much revenues a customer cohort has generated less the costs that it took to acquire the cohort:

The purpose of this one is to show if you're getting better or worse with respect to one of the most important SaaS metrics: The CAC payback time, i.e. the time it takes until a customer becomes profitable. Note that for simplicity reasons the chart is based on revenues. If you use it in real life, it should be based on gross profits, i.e. revenues minus CoGS.

What you can see here is that the first cohorts cross the x-axis (a.k.a. become profitable) around the 6th lifetime month, whereas newer cohorts are crossing or can be expected to cross the x-axis further to the left, i.e. become profitable faster.

If you want to take a closer look, here's the latest version of the Excel template, which includes the new charts. Or even better, download it and pay with a tweet! :)

Cohort Analysis: A (practical) Q&A [Guest Post]

My colleague Nicolas wrote a great guide with tips and tricks on how to do cohort analyses which I'd like to share with the readers of this blog. Thanks, Nicolas, for allowing me to guest publish it here. Without further ado, here it is!

- - - - - - - - - -

At Point Nine we believe that the only way to get a real sense of user retention and customer lifetime is doing a proper cohort analysis. Much has been said and written about them and Christoph has a published a great template and guide on the topic if the concept is new to you.

With this Q&A I want to focus on some of the more practical questions that might arise when you are actually implementing a cohort analysis for your startup. After close to two years of working with SaaS companies and doing numerous of these analysis I have learned that in most cases there is no perfect step-by-step procedure. But although you will always have to do some customisation for a cohort analysis to perfectly fit your business, there are a handful of questions and pitfalls that I have seen over again and again and want to share so that you can avoid them.

Now let's get into it!

Q: Which users should I include in the base number of the cohort?

There are two parts to the answer as it depends on what you want to measure. If you want to find out your overall user retention and have a free plan, then you should include all signups of a specific month.

However if you are trying to calculate your customer lifetime value, you should only look at the number of paid conversions. I only count an account as a paid one when the user has or will be charged for a period. So if you offer a 30-day free trial for example, wait to see if the user converts into a paying plan before you include him in the cohort. This way the numbers won't be biased with users that actually never paid for your service.

If possible without too much effort, you should also try to eliminate all 'buddy plans' that you have given to friends, your team or investors. If they are not paying, they are not representative for the real cohorts.

Q: How do I treat churn within the first / base month?

There are different approaches here, but in my view taking churn within the first month into account is the most accurate representation of reality. That means that in your first month the retention could be less than 100%, if people cancel their paid subscription within that month. It would look something like this:

I do this because I don't want the analysis to exaggerate churn in the second month and understate it in the first / base month. After all the reasons for churning in the first 1-4 weeks could be very different than after 5-8 weeks.

Q: Should I treat team and individual accounts differently?

If you are at a very early stage or sell mostly (90%+) individual plans it is probably sufficient to mix them all in the same analysis. But when team plans make up a significant part of your paid accounts, or your product has a very different user experience when a whole team uses it, you should probably look at both type of accounts separately.

Findings could include that team accounts are a lot more active, churn less and see a lower drop-off in the first month than individual plans. Or not. :)

Q: What about annual vs. monthly plans?

Again, if you are focusing on how active your users are over their lifetime it is OK to mix both plans. If you just want to see how many of the people that signed up still come back after X months, no need to split hairs.

If you are however focused on churn, you should only look at paid accounts that could have churned in that month. This is one of the 9 Worst Practices in SaaS Metrics and means that you should exclude all annual plans that are not expiring in the respective month. Including these in the denominator would otherwise skew churn numbers.

Q: Now that I have it, what can I take away from it?

The two most obvious take-aways are depicted in this (KISSmetrics) retention grid. Note that this is a most likely an analysis for a mobile app and the numbers for your SaaS solution should be significantly higher:

(click for larger version)

Moving horizontally you can see how the retention of a cohort decreases over the users lifetime. Interesting here is where the highest drop-offs occur and whether the numbers stabilise after a few months.

Vertically, you can (ideally) see how the retention of your cohorts change over the product lifetime. Assuming you are not twiddling your thumbs while catching up with House of Cards or sipping Mai Tai’s at the beach once your product launches, you should see an improvement in user retention with younger cohorts as the product improves. If this is not the case, you should consider whether the hypotheses or features you are working on are the right focus.

Most importantly though, this data will be the basis to give you a sense for your customer lifetime value (CLTV). If you take the weighed retention data for the 6th or ideally 12th month and extrapolate it, you will get an approximation for the average lifetime of your customers. Multiplying this with the average revenue per account (ARPA) or respective plan that you are looking at (e.g individual / team) it will give you your CLTV. This number is really the quint essence of the cohort analysis, as it gives you an idea about how profitable your business model is (=how much more money are you making with than what you are paying to acquire him). Subsequently it will also tell you the highest price you can spend on customer acquisition to grow profitably. It is important to note here that although super valuable, especially in the early stages of a startup this number will always be an estimation and most likely not 100% accurate. So keep in mind to continually track and fine-tune your CLTV calculations.

And one last thing: If you have accounted only for paid subscriptions as defined at the first question above, then the base rates of each month will also give you the most accurate number for paid customer growth and subsequently MRR growth. Two charts you will want to have at hand when talking to investors.

Q: Is that it?

For this post, yup! If you want to learn more about cohort analysis or SaaS Metrics, I would strongly suggest to check out Christoph’s and David Skok’s blog. And in case you have any questions on the above or something is unclear, feel free to ask away in the comments or send me a mail and I will do my best to answer you (or forward the hard questions to Christoph). ;)

- - - - - - - - - -

Like this post? Make sure you add Nicolas' blog to your reading list.

Excel template for cohort analyses in SaaS

[Note: This post first appeared as a guest post on Andrew Chen's blog. Andrew is a writer and entrepreneur and has written a large number of must-read essays on topics such as viral marketing, growth hacking and monetization. He was kind enough to publish my post on his blog, and I am republishing it here.]

If you’re a long-time reader of my blog (or if you know me personally) you’ll know that cohort analyses are one of my favorite tools for getting a deeper understanding of a product’s usage. Cohort analyses are also essential if you operate a SaaS business and want to know how you’re doing in terms of churn, customer lifetime and customer lifetime value. I’ve blogged about it before and have included “Ignore your cohorts” in my “9 Worst Practices in SaaS Metrics” slides.

My feeling is that over the last 12 months the awareness for the importance of cohort analyses has grown among startup founders. One reason may be that thought leaders like David Skok have been writing about the topic, another reason are web analytic tools like MixPanel and KissMetrics that make it simple to create cohort analyses.

And yet, many founders are still having difficulties with cohort analyses, be it with the collection of the data or the interpretation of the results. With that in mind I wanted to create a simple cohort analysis template for early-stage SaaS startups.

You can download the Excel file here.

The idea is that you have to enter only a small amount of data and everything else is calculated automatically. Specifically, what you’ll have to type in (or import from a data source) is the basic cohort data: How many customers did you acquire in each month and how many of them were retained in each subsequent month. If you also want to see your churn on an MRR basis and get a sense for your CLTV, you’ll also have to enter the corresponding revenue numbers.

If you’re not sure how to read a cohort analysis, here’s a quick explanation:

Here are some brief notes on each of the arrays in the sheet:

A1: This is where you enter the raw data. Start with January 2013 and enter the number of new customers that you’ve acquired in that month. Then move to the right and enter how many of those January 2013 customers were still customers in February, March, April and so on. Then move on to the next row. If your data goes further back than January 2013, extend the table accordingly.

A2 and A3: A2 takes the data from A1 and shows it in “left-aligned mode”, making it easier to compare different cohorts. As you can see the columns have changed from specific months to “lifetime months”. A3 shows the number of churned customers as opposed to the number of retained customers. Both A2 and A3 aren’t particularly insightful to look at per se, but the data is necessary for the calculations in B1, B2 and B3.

B1: Shows the percentage of retained customers, making it easy to see how retention develops over time as well as to compare different cohorts with each other. What you’ll want to see is that younger cohorts are getting better than older cohorts.

B2. This is kind of like the “inverse” of B1, showing the percentage of churned customers as opposed to the percentage of retained customers. In any given row, the sum of the percentages of churned customers plus the percentage of retained customers equals 100%.

B3: B3 is similar to B2, but the difference is that churn isn’t calculated relative to the original number of customers of the cohort but relative to the number of the cohort’s customers in the previous month. Let’s say you have a cohort with 100 customers and after 6 months the cohort has been reduced to 50 customers. If you lose 5 customers in month 7, this represents 5/100=5% churn in B2 but 5/50=10% churn in B3.

So what’s the correct number? There’s no right or wrong here, it depends on the question that you want to ask. If you want to know e.g. “How many customers do I lose within the first six months?”, B2 (in conjunction with B1) gives you the right answer. But if you want to know what percentage of customers you’re losing per month (important when you look at data across multiple cohorts and for lifetime estimates), take a look at B3.

What you’ll want to see in this table is that after a usually relatively high churn rate in the first lifetime months churn starts to stabilize (because the people who never really adopted the product in the first place are now gone).



C1-C3: Same as A1-A3, just for MRR instead of customer numbers.

D1-D3: Same as B1-B3, just for MRR instead of customer numbers. What you’ll want to see is that your MRR churn is lower than your customer churn due to account expansions.

E1 and E2: If you enter the CACs for each cohort, these tables show you when each cohort breaks even.

Also take a look at the second tab in the Excel sheet, which calculates/estimates customer lifetime and customer lifetime value on a cohort basis. Note that the data is highly speculative for younger cohorts for which there isn’t much data yet.

Further notes are included in the Excel sheets.

If you have any questions or comments, please feel free to reach out!

Know your user cohorts

One of the most important tools to better understand the usage of a web application – or a service, a game or a mobile app, it doesn't matter – is a cohort analysis. In fact, it's almost impossible to get a really good understanding of a service's usage without looking at activity and retention numbers on a cohort-by-cohort basis.

And yet, most startups that we're talking to haven't looked into cohort analyses yet. Often the reason is lack of resources. If you're a young, bootstrapped startup and you have to decide if you want to use your developers' scarce time to improve your product or to get better statistics most founders will decide for the product. That's understandable. Nonetheless I would like to argue for a high quality standard of metrics early on, since the insights that you'll get by understanding your metrics will often be highly actionable. And of course it will make your conversations with investors who want to understand your numbers much easier. At the minimum, I think you should try to make sure from the beginning that you collect the data that will allow you to do more sophisticated analyses later.

Back to the original point, why is a cohort analysis so crucial? Let's take a look at the following chart of an imaginary startup:



Looks like the company is growing nicely, hm? No exponential growth, but constant, linear growth. Now take a look at this chart:



It looks like the number of active users is growing even steeper. Great! 

But now let's take a look at the underlying cohort numbers in this Google Sheet.

The number of new signups are contained in cells D5 to D14, and the cumulated number of signups are in cells E5 to E14 (I used that one to make the chart look better :-) ). The number of active users, which the second chart shows, is contained in cells H15 to Q15.

In case you're not familiar with cohort analyses, here's a quick introduction:

• Each row represents a signup cohort.
• In the "right-aligned" cohort analysis at the top you can see the number of active users of each signup cohort for every calendar month. So, for example, I5 is the number of users who signed up in January 2011 and were active in February 2011, and I6 is the number of users who signed up in February 2011 and were active in February 2011. Accordingly, if you go down to the "Total" numbers in row 15 you'll see the total number of active users for each calendar month. These are the numbers which form the activity chart above.
• In the "left-aligned" cohort analysis at the bottom you can see the number of active users of each signup cohort for every user lifetime month. Example: I20 shows the number of users who signed up in February 2011 and were active in March 2011 (=user lifetime month #2 of the February 2011 cohort).

Row 29 and 30 calculate the monthly drop-off rate and the percentage of users who is still active n months after signing up. Here's where it gets really interesting. Our imaginary startup has a monthly drop-off rate of 50%, which means that after 6 months only 4% of the users are still active! That's not easy to see if you're just looking at the charts above, is it?

Note: In the example that I'm using, a user who registers in month x qualifies as an active user in that month. The assumption is that he logs in at least once after registration and that that log-in makes him count as an active user. That effect completely distorts the real activity numbers. If you're signing up a growing number of users it means that your activity numbers can basically only go up regardless of any real usage activity. So - if you're talking about "active users" it's best to leave out the users who have signed up in the timeframe that you're talking about. That is, if you're talking about the number of active users from last week, include only the users who signed up until the week before.

By the way, while I've used "activity" in this example you can of course use cohort analyses to track other aspects, too. As a SaaS company, for example, you should have a cohort analysis for retention/churn. As an online shop, you should have a cohort analysis for repeat purchases.