Data Studio, Data visualisation

Custom dynamic comparison table time-over-time in Data Studio

July 8, 2021

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

  • Text Hover

The setup


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:



  1. Create new date fields in the data source

  2. Blend the data source on itself

  3. Create calculated fields in the graph


Data Source


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)

  • Text Hover
 

Data Studio automatically sets the dates to Date & Time, so we need to change the Type to Date for all three fields

  • Text Hover

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.   1 Year before (Date -1 year)  

  2.   Date  

  3.   1 Month before (Date -11 month)  

  4.   2 Months before (Date -10 month)  

  • Text Hover

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:



  1.   This month  

  2.   1 Y before  

  3.   1 M before  

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

  • Text Hover

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:

  • Text Hover

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.

  • Text Hover

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”

  • Text Hover

Create the same calculated fields for   % var 2M   and   % var 1Y  .

  • Text Hover

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.

Author

Avatar

Daniel Barranger

YOU MIGHT ALSO LIKE THESE

How to show the latest value of a metric in Data Studio
How to show the latest value of a metric in D...
Data Studio, Data visualisation