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 for DoD, WoW, MoM etc and for crrating comparisons 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 = Date (adjust granularity to ‘Year Month’)
Metric = Sessions
Sort = Date (Month 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 GDS will strip them when creating the blended data source.

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 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, we must correctly configure the data configuration within the blended data source.

Start by updating the join keys to only include ‘Year Month’, when deleting keys ensure any subsequent ‘Missing’ references are also deleted.

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.

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

Leave a Reply

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