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.
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.
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.
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:
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.
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):
Data source 1 (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
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.