For a better reading experience, the following is the reference to different objects on Acho:
๐Ÿ”ค: Variable | ๐Ÿ”˜: Button | ๐Ÿ“‹: Table

Acho Studio - Cohort Analysis Charts

Good customer retention helps businesses keep valuable customers and create recurring revenue streams. Itโ€™s arguably one of the most important metrics in any business across industries. The problem with customer retention is that most strategies may only have temporary success, fewer strategies actually have long-lasting benefits.

This is because customer retention is usually caused by multiple factors, rarely it is determined by one single aspect of the business. For example, higher retention rates may result from a new product launch or a successful marketing campaign. Though it could regress to a previous state soon after.

Moreover, customer behaviors can be sporadic. A certain use case or need may resurface after several months if certain events occur. Businesses have no full control over what a customer may see or experience outside of their product offering.

To determine which strategies actually work, it is important to observe how different strategies influence the change of retention rate for a longer period of time. Cohort analysis is one of the best ways to reflect how well a product retains customers.

What is Cohort Analysis?

Cohort analysis helps segment customers based on certain traits or behaviors, it then maps out a trend on how these cohorts retain over time. Generally, acquisition time is the most common method to segment customers by groups. For example, you can group customers by their respective first purchase times in e-commerce. In SaaS, you can group customers by their registration time or payment date.

Take the following graph as an example. This cohort is extracted from the heat map provided above. It represents the change of retention among users who registered in August 2020. The first month had a 100% retention rate since all users registered at this time. In the second month, the retention rate dropped to 36.7%. That indicates 36.7% of users who registered in August 2020 kept using the product after a month. After 8 months, you can find the retention rate decreasing to 16.0%. It means that only 16% of all users in this cohort came back to the product.

Acho Studio - Cohort and Retention Rate

The reason to divide customers into different groups based on their acquisition time is to isolate impacts of marketing campaigns, sales strategies, new products, or features. If a customer started to purchase or use a product in the same time period, they would probably have received the same message, experience, or marketing effect, and enjoyed the same set of features or services. Therefore, we can evaluate the combined effects of several marketing tools or sales strategies on customer retention.

Generally, cohort analysis can answer the following questions:

  • Who are your customers?
  • When did your customers churn?
  • Why do they stop purchasing or using your product?

How to run a cohort analysis?

Here we use a SaaS companyโ€™s user behavior data as an example to conduct a cohort analysis. Although your data may not have the same schema as the example dataset, you only need to ensure that your dataset includes information that can be used for identifying individual users (such as user ids) and the timestamp of their purchase time(such as login time or purchase date).

1. Import data from your database

There are two tables in this case. To do the analysis, we only keep necessary columns in advance.

The first table is ๐Ÿ“‹ users. Each row includes a user's id and registration time. It can help you identify their acquisition time.

  • ๐Ÿ”ค user_id: a unique id for each user.
  • ๐Ÿ”ค registration_time: 10-digit UNIX timestamp for each registration
Acho Studio - User Table

The other one is called ๐Ÿ“‹ user_actions. It records each action that a user applies in the software. The ๐Ÿ”ค user_id suggests who is the user that has applied the action. The ๐Ÿ”ค action_time suggests when did he/she apply it.

  • ๐Ÿ”ค action_id: a unique id for each action
  • ๐Ÿ”ค user_id: a unique id for each user.
  • ๐Ÿ”ค action_time: 13-digit UNIX timestamp for each action.
Acho Studio - User Actions Table

*If you do not have a userโ€™s registration time recorded in your database, alternatively you can use their first purchase time or login time. Both can be used to calculate retention. For more information, check out this article.

2. Combine two tables

Click the ๐Ÿ”˜ Join button to combine these two tables.

Acho Studio - Join Tables

The result table should have columns as such:

Acho Studio - User Behavior Data

3. Transform the UNIT timestamp to a standard date format

As you can see, ๐Ÿ”ค registration_time and ๐Ÿ”ค action_time are presented in a series of digits (called UNIT timestamp) and it's difficult for human interpretation. You may use the ๐Ÿ”˜ Formula feature to transform them into a readable format.

Acho Studio - Date Transformations

First, using TIMESTAMP_SECONDS() ย or TIMESTAMP_MILLIS() can convert the UNIX timestamp into a standard timestamp. Basically, TIMESTAMP_SECONDS is used for a 10-digit UNIX timestamp and TIMESTAMP_MILLIS is used for a 13-digit UNIX timestamp. ย Next, ย applying DATE() on the timestamp can change it into a โ€œyyyy-mm-ddโ€ format.

The following chart illustrates how these functions work:

Acho Studio - Date Transformation Process

4. Calculate how long a customer has retained

In cohort analysis, we need to know whether a user keeps using a product, so we can evaluate the retention rate over time. In other words, we have to know when each action happens in what month since the user first started.

To calculate it, let's click ๐Ÿ”˜ Formula again and type in the following formula.

Acho Studio - Retained Months Calculation

First, DATE_DIFF() function can be used for calculating the interval between two dates. By specifying DAY in the function, it will return the difference in days.

Second, to recognize which month did each action accur, you can divide the number of days by 30. Finally, using the FLOOR() function it can get only the integer part of numbers.

Here is the result:

Acho Studio - Retained Months

* Important note: the ๐Ÿ”˜ Formula action cannot be applied in succession. Use a filler action(Sort, Move Column, etc) in between 2 ๐Ÿ”˜ Formula actions.

Finally, use the ๐Ÿ”˜ Cleanse action to convert ๐Ÿ”ค registration_date into months.

Acho Studio - Cleanse

5. Calculate the number of retained users in each cohort by month

Click ๐Ÿ”˜ Pivot Table, drag ๐Ÿ”ค registration_date and ๐Ÿ”ค retained_months into Vertical Dimensions, and ย put ๐Ÿ”ค user_id in Values to count the number of different users.

Acho Studio - Pivot Table

Here is the result.

Acho Studio - Pivot Table Result

6. Calculate retention rates

First, create another table called ๐Ÿ“‹ user_1 and apply the previous 5 steps. Next, click ๐Ÿ”˜ Filter to keep only the rows with ๐Ÿ”ค retained_months =1.

Acho Studio - Filter

Then, go back to the first table and click ๐Ÿ”˜ Join to combine tables.

Acho Studio - Join Tables

Finally, click ๐Ÿ”˜ Formula to calculate the retention rate.

Acho Studio - Retention Rate Calculation

The cohort analysis table is now all set.

Acho Studio - Final Results

7. Export to Visual Panel and visualize the results

The last step is to produce a chart with your table. Acho offers a built-in tool called Visual Panel for this. You can also connect your Acho table to your favorite BI tools such as Tableau and Metabase.

Acho Studio - Cohort Analysis Charts

Generally, cohort analysis can be presented in a line chart or a heat map. If you want to examine the trend of customer churn, the line chart can give you a clear picture of the pattern. For example, from the top-left graph, you can see all retention rate lines drop significantly in the second month and the declining trends become flat after that.

If you want to see if there is any group with an exceptionally higher retention rate in a specific month, a heat map should help you discover it much easier. For instance, from the above heat map, you can see that users who registered in September have a higher retention rate after 7 months.Finally, if you want to create a real-time dashboard to update numbers automatically, you can check out this article to set up Data Sync.