How to Perform a Cohort Analysis to Track Customer Retention Rate

Data Tutorial SaaS Metrics

What is a Cohort Analysis?

A cohort analysis is a powerful and insight method to analyze a specific metric by comparing its behavior between different cohorts, or groups, of users. This type of data analysis is most often segmented by user acquisition date, and can help businesses understand customer lifecycle and the health of your business and seasonality.

Use Cases for a Cohort Analysis

Since cohorts are most commonly segmented by a time-dependent grouping, such as user acquisition or user start date, it can be used to glean a number of insights on key metrics for your business:

  • Monthly revenue growth based on time of year - allows you to see if new cohorts are more or less valuable than previous cohorts

  • Determines if your business gets impacted by seasonality

  • How updates to your website impact user acquisition

  • Marketing campaign effectiveness over time

  • Customer Lifetime Value

  • Customer Churn

  • Customer Retention Rate

How to Perform a Cohort Analysis to Track Customer Retention Rate

Customer Retention Rate can be measured by using a cohort analysis in Chartio. The idea is that you’ll track the Customer Retention Rate by grouping users by their sign up date (daily, weekly, monthly, etc - varies based on your business objectives and pricing model).

To perform a Cohort Analysis in Chartio to track Customer Retention Rate, there are three pieces of data needed:

  1. Who you are tracking
  2. Sign up date
  3. All subsequent dates after sign up

All of this data can usually be found in an Event table with a user ID and event timestamp. We’ll bring this together in a SQL Query and add it to Chartio as a Custom Table in the data source. The assumption for this is your data source has the ability to do Common Table Expressions (CTEs). If not you’ll need to do this as subqueries.

To begin forming your query you’ll want to answer two questions: “who are we tracking?” and “when did they first sign up?”.

You may have a users table which shows you both of those item. Often it will be a user ID and signup date. If not, you can generally look at an Events table and get the user id with the minimum or MIN event date. We’re using the Chartio Demo data source which runs on PostgreSQL as our example:

SELECT “Activity”.“user_id” AS “User_Id”,
DATE_TRUNC(‘day’, MIN(“Activity”.“created_date”))::DATE AS "First_Event_Date"
FROM “public”.“activity” AS "Activity"
GROUP BY “Activity”.“user_id”

The next piece is simple. You’re just selecting the user and their event dates.

SELECT “Activity”.“user_id” AS “User_Id”,
DATE_TRUNC(‘day’, “Activity”.“created_date”)::DATE AS "User_Event_Date"
FROM “public”.“activity” AS “Activity”

Finally, you pull the two queries together and then calculate the time between the user’s sign up date and their event date. We’ll do this in the form of a Common Table Expression:

WITH “USER_FIRST_EVENT” as (
SELECT “Activity”.“user_id” AS “User_Id”,
DATE_TRUNC(‘day’, MIN(“Activity”.“created_date”))::DATE AS "First_Event_Date"
FROM “public”.“activity” AS "Activity"
GROUP BY “Activity”.“user_id”),

“EVENT” as (SELECT “Activity”.“user_id” AS “User_Id”,
DATE_TRUNC(‘day’, “Activity”.“created_date”)::DATE AS "User_Event_Date"
FROM “public”.“activity” AS “Activity”)

select “USER_FIRST_EVENT”.“User_Id”,
“USER_FIRST_EVENT”.“First_Event_Date”,
“EVENT”.“User_Event_Date”,
“EVENT”.“User_Event_Date”- “USER_FIRST_EVENT”.“First_Event_Date” as “Days_Since_Signup”,
(“EVENT”.“User_Event_Date”- “USER_FIRST_EVENT”.“First_Event_Date”)/7 as “Weeks_Since_Signup”

FROM "USER_FIRST_EVENT"
JOIN “EVENT” on “USER_FIRST_EVENT”.“User_Id” = “EVENT”.“User_Id”

We included a calculation to determine how long it has been for each event from the signup date in both a Day and Week calculation. We’ll use these in the Tables and Charts.

Now that we’ve written our SQL query, you can move on to performing the cohort analysis in Chartio by following these steps:

figure 1

  1. Add the above to Chartio as a Custom Table for that data source
  2. Once this custom table has been created in the schema go to the Chartio Data Explorer (Documentation: Using Your Custom Table)
  3. From the new Cohort table add User_ID to ‘Measures’ and set the aggregation to Count Distinct
  4. Add the First Event Date to ‘Dimensions’. At this point you can cohort at the Date level or you can change the data bucketing to Week or Month to set the Cohorts at higher levels of aggregation
  5. Add in the Weeks Since Sign up to the ‘Dimensions’
  6. Run the Query and then add a Pivot Step
  7. The Pivot Step will pivot the Weeks Since Signup which will line up your individual weeks

Conclusion

A cohort analysis gives you a deeper understanding of your users and can glean insights on the health of your business. In measuring the Customer Retention Rate metric as a cohort analysis, you can see the value of each customer cohort and understand more about how to retain your customers for long-term growth.


Recent SaaS Metrics Tutorials