Cohort analysis has recently become more popular, an increasing number of analysts master it and begin to use it. Google Analytics was not afraid to include this tool in its system, originally designed for a wide range of users.

Indeed, the cohort analysis is as easy as pie, although unusual, therefore it is not immediately perceived. Its singularity is that in most methods of analyzing the customer base, the slice is taken as if “across” - for a certain date, and in a cohort analysis – “along” the entire life cycle of customers.

Let's look at an example

But let's not get stuck on theory but right away get down to practice. We have already used the specially prepared file with the sales database of the fictitious online store “Tip-Tops” several times in analytical articles.

You can download this file from Google Drive.

Description of the contents of this file can be found in the first article of the analytical series - Data Analysis in E-Marketing. Article 1 - Channels for attracting customers.

Among other things, there is an example of cohort analysis in the file, which we will discuss in detail in this article.

From the initial data, we only need a sales database, which has information about the date, order price and customer id. That’s all. Calculations are done automatically, and the results are displayed in tables and diagrams on the “Cohort_Analysis” sheet.

How is segmentation built by cohorts

Cohort analysis is not some sort of “in-itself’ analysis, it is a segmentation of the customer base, the results of which can already be analyzed as you like. Thus, first of all, we must segment our database, i.e., to select cohorts. And cohorts in our application are nothing but groups of users of the same type by time, for example, of first purchase.

In our example, we build cohorts precisely by the time of first purchase. First, we split our entire time interval into equal periods (here and below, the original data is entered into orange cells, and the results appear in blue). Most often as such period a month is selected, but it is important that there are enough people in cohorts for the possibility of averaging. We selected a quarter and received 8 cohorts.

These are customers who made their first order in the relevant periods.

The formulas automatically put cohort numbers on the “Customer_Base” sheet, and from there the number of customers in cohorts and the amount of all their purchases in the table shown above were calculated.

Beginning to analyze

By dates, a table with cohorts will look like specific “layers”.

The same data is more convenient to “read” in the diagram.

The first conclusions can already be seen in this representation:

1. Each new cohort spends more than the previous one. It's good. But on the last cohort, growth slowed. It is necessary to think why.
2. In the first quarter of each cohort, the maximum amount of purchases is made, in the second, customers are already spending significantly less. Obviously, if you increase repeat sales, the business simply “flies”..

In order to compare behavior of different cohorts, it is better to bring them to the same basis - the beginning of the life cycle. On the example of number of buyers who made purchases, the table will look like this:

This is already a typical representation in a cohort analysis. Remember like Google’s at the beginning of the article?

If we bring data from the previous table to the maximum values in the cohort (index), we can compare dynamics of active buyers in cohorts.

What can we see in our store:

1. Behavior of cohorts is very similar. There are no major differences, for example seasonal ones.
2. In the second and subsequent quarters, the purchases are made only by about 20% of the cohort.
3. Strongly falling in the second quarter, then the number of buyers in the cohort remains generally stable, i.e., further churn is not noticeable (where is churn rate, where is end life cycle?).
4. The number of buyers in a cohort can fall, and then – grow.

Obviously, the decrease in revenue in cohorts, which we examined earlier, is due to the fact that customers do not return for a second purchase. But it's interesting to look at the dynamics of the amounts spent by customers.

From this diagram it is clear that in all cohorts without exception the amount spent by buyer in the second quarter is growing! This interesting moment we saw in many businesses, and it can be explained by growing confidence of new customer to the store, recession of fears after the first successful purchase. Do you have it same way?

Customer churn rate и Customer lifetime value

Yet in the first article of our analytical series, we promised to understand the customer churn rate and customer value during the life cycle (CLV or LTV). Now it's time for this - we have a test sales base and we made a cohort segmentation, which is the best way to study these values.

Churn rate

Churn rate is the proportion of customers who have left for a certain period of time. Having determined this indicator on the basis of historical data, we can build forecasts of the number of customers, future revenue and customer value for the whole cycle of “life”.

This indicator works well in subscription business, where there is a clear event of customer's departure – withdrawal, unsubscription. In ecommerce, however, there is no such event, the customer simply stops buying for some unknown reasons. He actually left, it is difficult for us to count on him, but how to determine the fact of leaving? What if he comes back?

The criterion of leaving should be determined independently and it will, as a rule, be a time criterion. Sometimes it's easier to set a life-cycle limit, like in an infant goods store, sometimes it's more difficult, for example, in a hypermarket for home appliances.

We propose to be attached to the period of inactivity of customer. It is especially logical if the business has already calculated the care criteria for other types of analysis. So, in our test online store we studied outgoing customers and made segmentation by recency of purchases. More details about this are written in the article devoted to RFM-analysis. And there we defined the category of “departed”, as those customers who did not make a purchase for 1 year. We got 100 of them out of 1,711. We know their id and we can calculate them in our cohort segmentation.

And then we see another problem Churn rate in ecommerce - it is not linear in time. Since the largest number of customers make only one purchase, after a specified period of inactivity, there is an avalanche-like churn. In the table above it is clearly visible - the first churn figures far exceed the subsequent ones.

It turns out that if we calculate the average quarterly churn of customers, this figure will not tell us anything.

And yet churn rate can be used in ecommerce. We will not be able to make a prediction for an arbitrary period of time in the future, but we can, looking at the first cohorts, perform churn analysis and predict the churn in the second quarter, the third, etc. As far as our history is enough. For example, according to our rules of recognizing clients as departed, it turned out that the churn rate appears for the first time in the fifth quarter of the life cycle and is on the average 60.5%, in the sixth - 9.7%.

Customer Lifetime Value (CLV, LTV)

When calculating the Customer Lifetime Value in ecommerce, we are faced with the same issues of ambiguous duration of life cycle and its non-linearity over time. But since we have already answered them, we will calculate the CLV with the same reservations.

In general, CLV is average profit received for average life cycle of average customer. You can calculate it in different ways, and you need to be prepared for the fact that the results may be different. This is all due to averaging, which is very disliked by adherents of exact sciences. The apotheosis is the recommendation to average the results of averaging as well:) This is said, for example, in the blog of KISSmetrics.

So, we need average profit per customer for average life cycle. To get this value, let's take average profit per customer per quarter and multiply by average length of life cycle. Formula:

CLV = AMPU * average lifetime, where AMPU — average margin per user

Average duration of life cycle, in this case, is nothing like a value inverse to our churn rate, that is, we can write:

CLV = AMPU * (1 / churn rate) or CLV = AMPU / churn rate

Both AMPU and churn rate in ecommerce are nonlinear, i.e., depend on the time period for which we are sampling. To minimize the problem of non-linearity, let us take them from the first cohort, which has the maximum coverage over time.

As a result of simple calculations, we get CLV = $1,448, while the average Lifetime was 6.6 quarters.

Customer Lifetime Value vs Customer Acquisition Cost

The meaning of calculating the CLV indicator is that by investigating the behavior of some key business metrics in the past, we get the opportunity to build profit forecasts in the future. At the same time, one of the most important applications of CLV is calculation of limit of eligible costs for attracting a customer (customer acquisition cost - CAC, more about CAC in ecommerce - in the article on our blog).

This is done by comparing CAC and CLV. It is logical that client should bring more profits than was spent to attract him. Here, however, we must not forget that these quantities should be comparable. Ideally, you need to balance net profit (but excluding the cost of attraction) with these very costs of attracting. Then CAC

But making such calculations is difficult enough and marketers in ecommerce operate, as a rule, by CLV calculated from gross profit. That's how we counted the CLV in our example. It is clear that in this case CLV should already be not just larger than CAC, but much larger, because it must pay all other business expenses, including interest to banks and taxes. The ratio of 1:3 is a certain accepted benchmark, but only you can determine the optimal ratio for your business.

About accuracy of balancing a penny in a penny here, of course, it will not go, but there are a lot of questions to the accuracy and reliability of the CAC and CLV values themselves in ecommerce, as we have already said. Nevertheless, the “sighted” use of these indicators with an understanding of their essence and nuances will allow marketer to make quick decisions about his costs and react to negative trends in profits before the financial director or bookkeeper starts screaming about it. And in the most favorable case, the last two will not even see anything but a growing profit. We wish that in your business it was so! :)

Analysis of data in e-marketing. Article 1 - Channels for attracting customers

Analysis of data in e-marketing. Article 2 - RFM segmentation