When you track your favorite KPI, you can easily visualize the absolute values of that KPI over time. What if we could visualize the percentage growth rate over time instead?
Expected result
The result of this tutorial is the following: a time series that describes the daily evolution rates:
We will use the Google Analytics data source (but this method is valid for any data source) and the following concepts:
- JOIN
- IF() function
- DATETIME_SUB() function
The objective is to be able to calculate for each day, the percentage of growth (growth rate) compared to the previous day.
Let’s go!
All starts with a join
We will start by making a data mix (join of 2 data sources in Data Studio) but using the same datasource without join key and the same dimensions and metric on each side:
- Dimensions: Date & Date Past
- Metric: Sessions & Sessions past
*I add the word “past” to the dimensions and metrics on the right to be able to differentiate them later.
So here is the table we get (always make a table to check the data mix 😉 )
We can see that the list of “Date past” is repeated for each “Date” (same for the “Sessions past” metrics for “Sessions”)
We will use this feature to target only the rows for which the couple “Date – Past date” have only 1 day difference (the difference between them is 1 day)
By using the DATETIME_SUB function
Date past = DATETIME_SUB(Date, interval 1 day)f and only if this condition is TRUE, then the growth rate is calculated according to the mathematical formula:
(Sessions-Sessions past)/Sessions past
Otherwise we return “null”
Let’s use the IF function to calculate the growth rate
The objective with the IF function is to check if the difference between the 2 dates is 1
If true, we return the calculation of the growth rate
If false, we return a null value
IF(Date past=DATETIME_SUB(Date, interval 1 day),(Sessions-Sessions past)/Sessions past,null)
Adding this calculated field to the table we get this result:
We can therefore observe that the value of the growth rate is only calculated if “Date past” is the day before “Date”.
All that’s left is to display it on a bar chart and…Tadaaa!
The solution is also available on this data studio: https://datastudio.google.com/s/jZiLjTUUxqQ
Documentation DATETIME_SUB : https://support.google.com/?visit_id=637515687668660679-2720491554&hl=en
