How to create a Cohort analysis in Google Data Studio

Cohort analysis is a fairly simple way to visualize your user retention. And we’re going to see how simply we can do it in Data Studio.

Expected result

This tutorial is based on a Spreadsheet data source but the principle remains the same for any data source with a Date field that indicates the 1st user interaction and a second Date field that indicates the interactions in the user’s life.

The following concepts are used in this tutorial:

• Blend data
• CASE WHEN
• DATETIME_DIFF() function
• Pivot table
• Filters

Create Cohort dimension

The idea here is to create buckets from 0 to 6 depending on the number of months between the date of the event to be analyzed and the date of the user’s first purchase: 0 being the month of the first purchase and 6 being the 6th month after the first purchase.

For example, if we have the date of purchase of a product (purchase_date) and the date of account creation (user_since) of the user, we can analyze the purchase retention.

CASE
WHEN DATETIME_DIFF(date_purchase,first_purchase,month)=0 THEN "0" 
WHEN DATETIME_DIFF(date_purchase,first_purchase,month)=1 THEN "1"
WHEN DATETIME_DIFF(date_purchase,first_purchase,month)=2 THEN "2"
WHEN DATETIME_DIFF(date_purchase,first_purchase,month)=3 THEN "3"
WHEN DATETIME_DIFF(date_purchase,first_purchase,month)=4 THEN "4"
WHEN DATETIME_DIFF(date_purchase,first_purchase,month)=5 THEN "5"
WHEN DATETIME_DIFF(date_purchase,first_purchase,month)=6 THEN "6"
END



Mix the data to display the data in percent

Thanks to these buckets, we can get a cohort analysis without blending data.

Just set up a pivot table with “first_purchase” in row, “cohort_bucket” in column and “user_id” in metric

The only problem is that we will only have the total in absolute and not in percentage compared to the first bucket 0… which is the whole point of visualizing a cohort.

To do this we need to go through the data mix.

• Our join key is the first_purchase date (your initial date)
• On the left table :
  • dimension: cohort_bucket
  • metric: date_purchase
  • we exclude cohort_bucket nul with a filter
• On the right table:
  • dimension: cohort_bucket
  • metric: date_purchase
  • we select only cohort_bucket equal to 0
  • we exclude cohort_bucket null

We will therefore have at month 0, only ratios of 100%.

Create the graph

For the graph we use a pivot table, configured as follows:

• Row: first_purchase
• Column: cohort_bucket_left (the dimension in your left table of your blend data)
• Metric: COUNT(date_purchase_left)/COUNT(date_purchase_right)