Skip to content (Press Enter)

Data Studio Tuto

Blog de Louis Dubruel

  • English
    • Français

How to create a Cohort analysis in Google Data Studio

by Louis Dubruelupdated on December 14, 2021December 14, 2021

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

L’attribut alt de cette image est vide, son nom de fichier est cohort_analysis_on_datastudio.png.

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%.

L’attribut alt de cette image est vide, son nom de fichier est Apo-Resume-›-Rapport-hebdomadaire-SEO-1-1024x515.png.

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)
Apprendre Google Data Studio

Louis Dubruel

Post Navigation

Previous Article
Next Article
  • SEO analytics: analyse your keyword trends like in SEMRush on Data Studio
  • How to create a Cohort analysis in Google Data Studio
  • JOIN in Data Studio
  • CASE WHEN function in Data Studio
  • When are pie chart most effective ?

©️ Louis Dubruel 2020 – 2021

Recommended for you...

Growth Rate: Calculate and visualize it in Data Studio

by Louis Dubruel
Recommended for you...

How to create time plot in Data Studio with native visualization

by Louis Dubruel
Recommended for you...

JOIN in Data Studio

by Louis Dubruel
© Copyright 2023 Data Studio Tuto. All Rights Reserved. The Ultralight | Developed By Rara Theme. Powered by WordPress.