Custom dynamic comparison table time-over-time in Data Studio
I have been struggling with this issue for a while: trying to compare the change for my chosen metric time over time for custom set periods.
What I wanted to see was the variation in purchases compared to last month, two months ago and one year ago in one single table and, most importantly, to be able to change the date range for my data.
Data Studio allows you to compare your metric to only one period of time, and the comparison is rather percentage variation or absolute change. This means you can’t see the previous period’s real metric nor you can compare to more than one period.
So far, I had two workarounds, rather create a month over month fixed comparison that would automatically swift every new month (here you can find a good explanation How to Display YoY Data in Google Data Studio), or create hidden tables, one behind the other, to view the real value and the change but need to use three (sic!) different data control selectors.
Needless to say, none of these was practical.
In this post I’ll explain how you can create a table to compare variation over custom time periods and use at the same time the date controller to change the period of comparison.
Caveat: this solution will not work with the Google Analytics connector because you can’t choose the date range dimension in the data source configuration of the blended data. I used data coming from BigQuery but it will also work with Google Sheets and other sources.
What we are going to achieve
In this example I want create a table that compares the selected date to the previous month, two months before and one year before. In order to do that, we are going to:
- Create new date fields in the data source
- Blend the data source on itself
- Create calculated fields in the graph
In the first place, we will need to create three fields in the original data source:
- 1 Month before (Date -11 month) : DATETIME_SUB( Date , INTERVAL 11 MONTH)
- 2 Months before (Date -10 month) : DATETIME_SUB( Date , INTERVAL 10 MONTH)
- 1 Year before (Date -1 year) : DATETIME_SUB( Date , INTERVAL 1 YEAR)
Data Studio automatically sets the dates to Date & Time, so we need to change the Type to Date for all three fields
Once we have created the three fields and set them to date, we can move on to the blended data
Blended data configuration
Now we need to create our source for the graph. We are going to blend the same data source four times on its own, using the 4 dates field we have as a join key:
- 1 Year before (Date -1 year)
- 1 Month before (Date -11 month)
- 2 Months before (Date -10 month)
I am using the ecommerce metric Quantity as the metric of my table. So I will add it to each data source in the blended data and then rename it respectively:
- This month
- 1 Y before
- 1 M before
- 2 M before
In order for the table to work properly, we need to add the day dimension in the first data source, in the Date range section Date and leave it set to Auto
Since I will want to use Product as a dimension in my table, will add it as a Join key in the different data sources as well. In the end, it will look something like this:
Graph and calculated fields
Once the data source is set, we can move on to the graph. I am obviously interested in the variation of the Product Quantity for the period set compared to one month before, 2 months before and one year before.
I will then add Product as the Dimension and the all the 4 different quantity metrics we created as metrics.
Now, we just need to create the percentage variation calculated metrics in order to get the change from one period to another:
(SUM( This month ) – SUM( 1 M before )) / SUM( 1 M before )
I will call this calculated metric % var 1M and set it to Type “percent”
Create the same calculated fields for % var 2M and % var 1Y .
And, at last, add the Date range control to the dashboard
Wrapping it up
Once configured we’ll be able to compare seamlessly the purchases registred in the chosen time period to the ones made one month, two months and one year before.
The dashboard we have created is just an example, by changing metrics and periods, you can customise it to your measurement necessities.