There are so many guides out there explaining about cohort analytics in a very intuitive way. They clearly explained the concept of analysis and examples with generic stories. I owe some of my colleague explaining with the real story of how I used in different use cases in different companies.

How to Build Cohort Analysis in Excel and Google Sheet

Why cohort analysis is important?

Thanks to Rjmetrics, an old BI tool acquired by Majento as they were the one who taught me the use cases on Ensogo time. Every morning I look at that dashboard before opening up other ads management or any other analytics use cases. 

Instead of just looking at the spending and revenue in a short span, cohort charts used to provide a better picture in the long term how everything is performing. I was like others when I started my career looking directly at how much I spent and how much is the revenue. it’s sad when my campaign is not working. And so I try to optimize campaigns to win the game very often. Am become sad/clueless

However after I start to use cohort analytics, I came to realize that even though some campaigns are not performing well In short time but they tend to perform well in the long term. It’s good learning that this can really avoid short term quick decision of changing campaigns and related things. This perspective of looking at a cohort lens is particularly crucial when someone working on products/services where the there time is taken/purchase cycle is long(like more than 60days).

70% this is how my dashboard look alike in Ensogo Rjmetrics

Why? Because imagine you are a car dealer. Am as a person who wants to buyer a car would 

  1. Compare different cars and it’s model
  2. Ask my friends where they buy (Showroom A vs Showroom B)
  3. Then compare different bank loans or any sort of financial arrangement 
  4. Decide when to buy

If you have been on the journey of buying, you will spend a lot of time thinking and asking feedbacks. The % of people who see. a car on the first day and buying it on the next day is way lower than 10% of total car customers.

So in this cases when we are about to decide to switch on / off off the campaigns if we take the decision on short term basis. Why because am not going to take a decision to buy the car in 5 days. 

This is one example how this concept of cohort analysis could be used but there are multiple use cases. Before we going into it, let us go through what is cohort analysis. 

What is cohort Analysis?

One of the early use case which allow us to understand this cohort analysis easily. Am a 90’s Kid and there would be millions of 90’s kid as well. In medical field they will group the people based on which cohort they born and then analyse their strength and weakness in their health. Specifically for example in Japan after Hiroshima & N nuclear blast, they could use this cohort group to understand what’s the health situations of the kids which born on that decade. 

Born Year vs Disease Affected Year

This way they would know whether the disease occured is only to specific cohort or it’s basically irrelevant to the born age but to other factors. 

OR 

It could be also like the disease is not because of the 90’s born cohort but to the cohort on 2010 who all affected by a diseased called Blahblahvirus irrelevant to the age group. And this Blahblahvirus affected people could have lost their immune power and keep getting infected by disease often. 

So when a medical institution wanted to provide special focus and cure something from the root, they could announce or silently provide special care to the people of blalblahvirus cohort. 

In technical terms, you would be 2 data points(dimension) in x/y axis to run a cohort analysis (The image am putting below would neatly explain this concept).

How I have used cohort analysis in different scenarios. 

My initial job at Ensogo was running Performance marketing campaigns especially focused on Paid Social from website objective to mobile app based campaigns. The business is a group deal buying site like Groupon. We advertise different deals(we call it as a deal and it’s like a product), different campaigns(like Christmas, travel seasons) at different weeks and months. 

The data points we collect are 

The Usual dimensions were 

The Metrics that will be derived out based on some formula would be

So the 12 Month accumulated view would already give us the understanding of lifetime value perspective. This dimension and Metric would provide multiple charts and food for insight thirst , let’s look in details on each of it.  

Anyway, Am going to put dummy numbers here for easy understanding on my excel cohort template and further screenshots below. We will use Deliveroo as an example

What’s the channel buyer conversion rate?

Every cohort will have 2 dimensions which involve date dimensions. This date dimensions would be used to group cohort pool(a) and the next date would their events(b). So here we have user signup date(A) and User who deposit their money in their wallet. By this method, this insights could provide the early part of my campaign whether to continue the campaign or stop.

Signup to Wallet Account opening Conversion rate

Example Action Item based on this insight: The Christmas Season week you launched on Facebook is having higher conversion rate or Is the Always on campaign having quality conversion rate?

Action Item2: If the conversion rate is declining on 2nd week, do we need to setup email automation to trigger a reminder email?

Identifying customer Lifetime value

First, we need to define our CLV is going to be 12 Months or 24Months or more than that. Depends on your business,  you could define this. If you look at the below table, I have selected only 12Months Lifetime. In cohort as standard, you will be taking the revenue based on their cohort pool, similar to how you are seeing in the below highlighted red box. Basically i sum up the total revenue of each cohort and divided by the total users on that cohort

Identifying customer Lifetime value
Identifying 12 Months lifetime value

What’s the “campaign” buyer conversion Rate?

It’s going to be tedious if we want to see this granular data if we are doing this analysis simply in Excel/Sheets. Strongly not into this idea as we need daily data. 

Hence if you are using any online tools,  you basically apply a filter or change the dimension by campaigns to see the performance of the campaign. 

Usually we experiment lot of campaigns, this is a similar graph I look at first thing. Those colourful accumulated chart will give me happiness if it’s keep going up. 

What’s the platform buyer conversion rate? 

Not all the platforms has similar conversion rate. By Data wise, iOS has always high conversion rate comparing with android. Web is mix of everything. Before 5years, this data is not a well known fact, we learned this lesson through hard way. However things could change or different for your business. So when you want to know this sort of signals to decide. This chart chart could help that way. 

Filtering CLV and Cohort based on Platform

Are all my cohorts are profitable and when they are breakeven?

An Example View how My dashboard used to look.

What’s the Ad Network performance? 

Here just replace organic search and direct with Ad Networks name

At one of the Fashion E-Commerce,  when we scale up the campaigns from traditional channels to Mobile app networks, this cohort analyse helped a lot. Because we spend huge amount of money and our average time taken to purchase is more than 5 weeks(not real number). Due to that, we need to take decision as quick as possible before we spend the whole budget and become clueless. So we used the other channel cohorts of buyer conversion rate to benchmark this ad networks. Thus provided us answers like

If we didn’t do that, we would have spent 100% of money and waiting for more time to see whether this investment would be worth. It’s a good exercise we did that to save ourself from App Networks Fake App Installs. 

Finding the average time taken to registered and purchased

Let me not go in too much detail as I have explained the concept above. So we cannot take quick decisions if we see the campaign is not performing on first few days. This is especially true for long purchase cycle business like car, real estate, investment, etc. They are high transaction value business and involves so much of reserach before taking decisions

So when you have an initial set of Buyers or any conversion data, analyze this and find your internal benchmarks to monitor numbers in future campaigns.  so if you look at the red box, most of the conversions happen on the first month itself. So that can be used as a benchmark. Or let’s say if the first month CR is going to be 60% in most of the cohort, then for your future campaigns, 60% could be used as a target.

Finding the repeat purchase contribution (from existing users)

This chart gives us an answer to what’s the repeat purchase behavior of our customers after their first wallet deposit. If we look at the table now, you will find the March 2018 cohort, they keep depositing money in their wallet.

This is reverse provides the answer of how much is the churn rate(the people not continuing to depositing with us)

When to setup Marketing Automation Triggers?

There are already a few analytical tools available online to automatically do your cohort analysis. The latecomer was Google analytics, Localytics has best one for mobile apps business. If you really don’t have a budget, then you can try via an excel sheet/google sheet(copy the file and try from your end). If you are not sure, click the chat, I will be happy to help

Leave your comments