How to Display YoY Data in Google Data Studio

In order to effectively analyse current performance it is key make direct comparisons with historical trends, ideally over matching time periods for example year over year (YoY). In this post I’m going to explain how such comparison can be displayed within Google Data Studio (GDS) using blended data sources.

Prior to the introduction of blended data sources within GDS the display of such data in table format was simply not possible when data was pulled directly from source, i.e. GA, GSC, Google Ads, Search Ads 360 etc. In order to present time comparison data, you first had to compile and process it within a third-party storage medium such as Google Sheets. For a whole host of reasons this was far from an ideal solution.

Early Methods

As an early adopter of GDS you were forced to utilise a third party tool such as Supermetrics to compile data within Google Sheets then via a selection of formulas calculate the desired time comparison metrics, ready to pull into GDS.

Google Sheets YoY Calculations

While this method worked well at the time it was not without its issues. The most common issues being the requirement for overly complex formulas to take account of elements such as custom channel groupings and frequent time out issues with the automated refreshing of data within Google Sheets.

What is Blended Data?

Fortunately, the introduction of blended data views within GDS in mid-2018 eliminated the above complexities effectively overnight. So, before we get started lets first cover off exactly what is a blended data view. A blended data view allows for data from two or more separate sources to be joined via a common dimension (join key). The data sources can be almost entirely unique (with the exception of the join key) or in the case of this guide identical albeit for the time period captured.

There are essentially two methods which you can utilise within GDS to create a blended data view. These include adding supplementary data sources to an existing chart or creating a blended data view from two exisitng charts. I typically choose this latter method as it provides a more visual and incremental method of creating a blended data view.

Step 1 – Create Blended Data View

For the purposes of this guide I am going to focus on the creation of a YoY comparison of organic sessions sourced directly from Google Analytics, however the principles are the same from other data sources such as GSC, Google Ads, Search Ads 360 etc.

First create a data source which will pull data in from your desired Google Analytics profile.

Next create a chart (basic table) with the below configuration within the data panel:

Dimension = Month of the year
Metric = Sessions
Sort = Month of the year
Default date range = Auto

Once this first initial chart has been created, simply copy and paste it so you have two identical charts side by side.

Note: Do not configure the respective dates in each table at this stage as we will be changing the utilised date dimension in the next step.

With both charts selected, right click and from the displayed menu select ‘Blend data’. This will result in the creation of a new chart populated from your newly created blended data source.

Initial Data Blend

At this stage to tidy your dashboard you can simply delete the initial source tables which you utilised to create your blended data source.

Step 2 – Edit Blended Data Source

Do not worry if at this stage the contents of your newly created chart are not what you were expecting, a degree of editing will be required. With the newly created chart selected, navigate to the right-hand data panel and click the pencil next to the name of your newly created blended data source. This will open the Blend Data configuration options.

The first change we need to make is to provide a more logical name to your newly created data source:

Rename Data Blend

Next add a supplementary date dimension (‘Month of year’) to the left data source. While we want to blend data on just month we will also want to output the year within our output table.

Join Keys

Moving on to the ‘Metrics’ section of each data source, click the pencil on ‘Sessions’ and provide a unique reference to each. Examples could include ‘Sessions (Current Year)’ vs. ‘Sessions (Previous Year)’. The aim is to allow you to easily distinguish each from one another as currently within your blended data source both are simply titled ‘Sessions’.

If you wish to include supplementary metrics, simply add these to each respective data source. For date comparisons the same metrics must be added to both data sources.

Once the above is complete it is now time to define the time periods for each data source. For a YoY comparison configure the following custom date ranges within each data source:

Data source 1 (Current Year):

Date selection current year

Data source 1 (Previous Year):

Date selection previous year

Note: the configuration utilised in the above screenshots will ensure dates automatically increment, requiring no manual adjustments each month unlike if fixed dates were set.

If any filters are required, i.e. ‘organic traffic’ only then these should be configured now and applied to both data sources.

Once all the above steps have been completed click ‘Save’ and close the ‘Blend Data’ configuration panel.

Within your output table ensure ‘Month of year’ is selected as your date dimension and not ‘Month of the year’.

Lastly delete any ‘Invalid dimensions’ from your blended output chart

Step 3 – Calculate % Difference

To help illustrate the relative change between the YoY statistics you may wish to calculate and add a percentage difference figure. To achieve this once again open the data panel and navigate to the Metric section. To include a percentage difference figure, you will need to add a custom metric. First click the ‘Add metric’ button then select ‘CREATE FIELD’. Enter the below new field parameters:

Name: % Difference
Formula: (Sessions This Year-Sessions Previous Year)/Sessions Previous Year)
Type: Number > Percentage
Comparison calculation: None
Running calculation: None

Percentage difference

Step 4 – Format New Blended Chart

To complete your time comparison, you may wish to optionally complete the following in order to achieve the optimal presentation:

  • Hide row numbers
  • Hide pagination
  • Apply conditional formatting

All of the above configuration options can be found within the ‘STYLE’ section of the right-hand chart configuration panel.

Formatted table

12 Replies to “How to Display YoY Data in Google Data Studio”

  1. Hey, Chris. Thanks for putting this guide together.
    My Sessions (Current Year) is working fine, but my Sessions (Previous Year) shows “null”. I checked on Google Analytics and I do have traffic for 2018/2019/2020/2021.
    Also, some steps from the tutorial are a little different from what we have on Google Data Studio these days.
    Could you please check if it is still possible to achieve this?
    Thank you in advance,

    1. Thanks for the prompt, it’s been on my to do list to update this guide following Google’s introduction of the improved date and time functions within GDS. I’ve just updated the guide to reflect this.

      With regard to your specific issue I suspect that nulls are being returned due to the join key not matching exactly. If the connector was created after September last year then within Google Analytics you should set the join key as ‘Month of the year’ but also include ‘Month of Year’ as a dimension within your left most (primary) data source. ‘Month of the year’ allows you to blend the two data sources as it only includes the month as opposed to the month plus year. You should use ‘Month of Year’ as the date dimension within your output table to indicate the current year.

      Hope the above helps, if you’re still struggling do let me know!

  2. Thanks for the article, very helpful. I was initially caught out like Paulo… but then reread the section and looked at the pretty pictures and got it working.

    Now my challenge is, how do I make it just show the month.. and not Month Year?

    1. The easiest method to delete the year so only the month is shown would be to add an additional dimension to the data blend of ‘Month of Year’. Stripping the year however is likey to result in data sorting difficulties if you are including months from both the current and previous calendar years.

  3. Hi Chris,
    I’m interested in creating a table with a time series comparison whereby I have

    i) different products in rows
    ii) average purchase price each product in one column
    iii) % price difference vs the previous month in another column beside the column in ii)

    I’m looking at MoM comparison and ideally would ideally like to:

    i) be able to apply a slicer to the report to select a subrange from the entire date range.
    ii) not have to update date ranges on tables as we move through time and future months get added.

    Just as FYI, I want my products in rows, because there will be 50+ products depending on the client and at least initially the historic data will be a max of 12 months (so 24 columns), but probably when viewing people will maybe only zoom in on a 3 month period via a slicer.

    Is this possible? Any guidance you can offer would be much appreciated.

  4. Hi Cian,

    The different elements of what you are trying to create are certainly possible, the difficulty is I don’t see a way you could achieve everything in one single table via GDS.

    Option 1 – To display all products in rows + the avg purchase price + % price difference MoM could be achieved via the method described in this post. Avg purchase price is a standard GA field and post blend a custom formula could calculate the MoM difference. The limitation of this method is the data would be limited to a set time period. The number of months could be expanded beyond two by simply adding additional data sources to the data blend for each required month. Additionally each date range could be set to automatically increment but a manual date selector could not be added to the report front end.

    Option 2 – If custom date selection by the user was a critical requirement then a basic table could be created (with no blend) utilising the built in comparison date range option. Unfortunately while the % difference vs. previous period is shown, actual figures for the previous period cannot be output.

    Option 3 – Lastly to display multiple months in the same table plus a custom date selector then a pivot table could be utilised with the column dimension set to month. Unfortunately dynamic % change metrics cannot be shown using a pivot table.

    Hope the above helps sorry its not the complete answer you were looking for. Option 1 I believe would be the closest to your optimal solution, just minus the custom date selection functionality!

  5. Please tell me how to make a setting for comparing month to month, week to week, day to day?
    Thank you so much

    1. Hi, the easiest method to compare DoD, MoM, YoY would be to create a seperate chart with a custom data blend for each. The data blend for each should have the join key modified to the respective time period which you would like to show.

  6. Hi Chris
    I am not successful in have GDS accurately calculate the % difference. I’m using the correct formula (Pageviews(lastquarter)-Pageviews(YoY))/Pageviews(YoY) however with these values of Pagesviews(lastquarter of 323,960 and Pageviews(YoY) of 239,850 the result should be 35.06% Comparison and Running Calculation are set to none. I’ve run thru all the aggregation radio button options and nothing comes close. What am I missing?

    1. Hi, you need to SUM each element of the formula as a result of the data blend. I believe the term for this is reaggregation. The formula below should return the correct % difference figure:

      (SUM(Pageviews(lastquarter)) – SUM(Pageviews(YoY))) / SUM(Pageviews(YoY))

  7. Hello Chris,
    This is very helpful. However when I use Default Date Range to “Auto”, I get only two months (today is July 8th) and by default it does says “Last 28 days”. How do I get an year worth of data with this setting? Please advise?
    Thanks
    ~ Nita

    1. Hi Nita

      In order to pull through 12 months of YoY data, the date range within the data blend for each data source needs to be set. For example

      Current year: 1 Jul 2020 – 30 Jun 2021

      Previous year: 1 Jul 2019 – 31 Jun 2020

      The date range for the actual table where the data source is your new dat blend should be set to ‘Auto’.

Leave a Reply

Your email address will not be published. Required fields are marked *