Here you can download this template. Download
A cohort of people is a group who share a common characteristic over a time-span. For example, let’s say we have a group of students who graduated in 2019. This group of students is a cohort. So all these students graduated in same year, so this is their common characteristic or commonality. In this article we learn Cohort Analysis in Excel.
Cohort Analysis is a study or analysis that helps us analyze or find insights on a cohort’s behavior or activities. It is a subset of behavioral analytics (analytics with which we can have insights into behavior of customers on ecommerce platforms, online games, web and mobile applications) that takes data from a particular dataset and instead of looking at all the users as one unit, it breaks them into related groups or cohorts for analysis. It allows to identify relationship between a population’s characteristics and the population’s behavior.
Some of the examples of cohort analysis can be seen in industries of ecommerce, gaming etc. For instance, in the ecommerce industry, a business may be interested in analyzing the behavior of only those customers who have purchased in the previous 15 days say, so as to analyze the patterns during a promotion or sale. In the gaming industry, games could identify cohorts of new users and professional players to study their behavior.
Let us study in this article how we can use cohorts to track customer retention, how we can create customer retention curve, and how we can compute customer life time value.
COHORT ANALYSIS model in Excel
Let us say we are running a business of web apps, and we have executed some campaign and acquired customers in different months. We have a cohort analysis model as below:
We can see in the model that we have acquired 164 customers in January, 119 customers in February, 254 customers in March and so on. So the group of customers we have acquired in each month are cohorts. Let us now observe the behavior of these cohorts over a period of time. For instance, in January, the number of customers acquired are 164, but we have retained only 66 customers in the next month out of 164.
Similarly, in month 2 we have retained 51 customers, in month 3 we have retained 48 customers and so on in month 11, we have only 23 customers. So this is how we are tracking the customer retention for each cohort where we have a total of 12 cohorts (Jan-Dec).
Also we have a dropdown at the top right of this model where we have options for different marketing campaigns. If we select ‘Email’ in this dropdown, we will have number of customers acquired via email marketing:
Customer RETENTION in PERCENTAGE and ESTIMATES
Next we have converted the entire picture to percentage. We saw above that in January, the month 1 value was 66 and total customers were 164, so (66/164)*100 is approximately 40%. Let us say we have a population dataset ha
Also we have estimated or calculated the future values using the growth function in Excel:
At the end of the above table, we have average retention of customers for each month. It is 44% in month 1, 35% in month 2 and so on.
Customer RETENTION CURVE
In the process of cohort analysis, we have created a customer retention curve as below:
In this curve, the blue values represent the actual values, and orange line represent the estimates.
Now the dropdown that we saw at the top right in Section1 is also linked with this retention curve. So if we select, let’s say ‘Direct’ from the dropdown, then we can see the retention curve by direct marketing campaign:
Similarly we can select anything from the dropdown and the changes will be reflected in the retention curve as well.
So one example of cohort analysis with the above steps can be let’s say Netflix has executed one marketing campaign and they have acquired some customers and they wish to observe the behavior of customers over a time period on the basis of retention. So this can be done using cohort analysis model.
Customer LIFETIME VALUE
Customer Lifetime Value is the estimation or prediction of net profit attributed to the entire future relationship with a customer, i.e. how much profit will be generated by a group of customers. On the basis of current actual profit values computed using Excel formula and average retention calculated above, we can estimate the future profit values or profit values for future months.
Let us see some other examples in which we can employ cohorts in business analytics and use cohort analysis:
- When we wish to test a hypothesis that users who are acquired via display ads have higher customer lifetime value than ones who are acquired via facebook.
- Let’s say we have daily cohort of users who launched a mobile app for the first time and revisited the app in next 10 days. For this, we have a retention table as below:
So from this retention table, we can infer some points as below:
- On Jan 26, 1358 users launched a mobile app
- On Day1, retention was 31.1%, and so on day 7, retention was 12.9%
- Day7 retention implies that on the seventh day after the usage of app, 12.9% i.e. approximately 1 in 8 users who launched an app on Jan 26 were still active users on the app
- Out of all the users, 27% users were retained on Day1, 19.2% on Day2 and so on.
So this is a cohort analysis model that we saw above.
If you want to learn more click here…
If you want to learn Linear Regression in Excel click here…