To make better product decisions, we need reliable data, but business analytics sometimes treat consumers like a single entity, using metrics that combine all of them into a single, large block. Using such generalizations is never a good idea because it probably won’t provide us with useful information. Cohort analysis is useful in this situation.

You can deconstruct the data with context and subtlety by using cohort analysis. This enables you to discover solutions that have the potential to produce outcomes. You must understand why consumers do what they do in addition to seeing when and what they do.

What is Cohort Analysis? Cohort Analysis is a behavioral analytics technique that separates the data into cohorts. i.e. groups of people who share certain traits.

We need to understand the terms ‘Churn’ and ‘Retention,’ as they are important metrics in Cohort Analysis. Have you ever wondered why users stay with or churn from a particular product? 🤔

In this article, we will look at a case study of different cohorts, their churn and retention rates over 6 weeks.

Before we proceed, let’s take a look at simple definitions of churn and retention.

Churn/retention: One of the most important analytics for any web, e-commerce, or subscription-based company is churn/retention analysis. These metrics are often based on cohort analysis and help determine the number of users, the duration of use, and the lifetime revenue generated by those users.

CASE STUDY

You work for a web subscription company as a data analyst. Your business is relatively new and evaluates user behavior using a third-party software. This was insufficient as the data expanded, so they brought you in as their first data analyst to help them advance their analytics.

Step1️⃣: Write an SQL query that pulls the cohort data from the database of your choice.

The following is an example of the data output. The dataset below shows the subscriptions of different user cohorts starting from 2020-11-01. Each row represents a particular cohort, starting from week_0 (i.e., the initial number of users who subscribed in the first week) and tracking until week_6.

• For the first cohort, 20,085 users started their subscription. The number gradually decreases until it reaches week_6, where only 17,014 users remain. This implies that 17,014 users stayed with the platform until week_6.
• You will apply the same metrics to each subsequent cohort, with one row per cohort.

Step 2️⃣: Now, we need to calculate the retention rate for each cohort every week. The formula for calculating the retention rate is by dividing the number of cohort users in each week by the initial cohort value at week_0, as shown below.

• Obviously, the retention rate is 100% at week_0 for all cohorts because that represents the initial subscriptions, and there was no churn at that point. The retention rate decreases each week until week_6. This shows that the highest and longest retention rate was achieved by the cohort that started subscribing on 2020-12-20.

Step3️⃣: Let’s calculate the churn rate. As we know, churn is the opposite of retention. So, the formula to calculate the churn rate is: 1 – Retention Rate. Using this formula for each corresponding cohort will give us the churn rate.

• It shows that there was no churn rate at week_0, as that was the initial subscription, and the churn rate decreases each week thereafter.

Step4️⃣: Create a Cohort Analysis Chart. The cohort analysis is carried out here. The data is organized in a “cohort chart,” as shown below, using conditional formatting for color grading. The intersections of the vertical and horizontal axes represent the combined metrics you are examining.

With this cohort analysis, we have gained insights into customer retention and churn rate.

All things considered, the three tools listed above are excellent choices for starting to use cohort analysis as part of your data analytics toolkit.

Remember, though, that you must take action with your product as soon as you obtain the cohort data. If you don’t act on these insights and continue iterating, they will be useless.

See you on the other side 👍