Data Blending in Google Data Studio: Practical Guide

Google Data Studio is an outstanding data visualization tool — its dashboards are easy to share, its intuitive design makes it simple to use, and the best news of all — its completely free to use!

By setting up Google Data Studio integrations with native Google applications like Google Sheets, Google Ads, BigQuery, Google Search Console and others combined with third-party tools like Mailchimp, Facebook Ads, Shopify etc. you are able to have all of your company’s data in one place.

But simply connecting all of your data sources is not always enough, and in the section below we will explain why. Therefore, in this article, we will show you how to blend data sources in Data Studio.

Ready? Let’s begin!

Image for post
Image for post
Photo by davisco on Unsplash

Why blend data in Google Data Studio?

The world of accounting is an interesting place. It can be complicated and confusing, but it is precisely the numbers we receive from our ad cabinets and CRM systems that give us an accurate picture of how our business is performing.

So why blend data in data visualization software? Well, here are two main reasons.

Say you have launched a new advertisement campaign. It was running in Google AdWords and Facebook Ads platforms simultaneously. The said advertisement campaign just ran to an end and you would like to assess the total advertising expenses for a certain time period.

To receive these numbers, you would have to blend data from all of your data sources — Google AdWords and Facebook Ads — and display them in one place, or Google Data Studio in this case.

Let’s be honest — Google Analytics data can be quite incomplete. So to get a full picture of a certain event or occurrence you would need to blend data with another data source.

For example, our website has a number of support tickets and a number of “contact me” requests, filled by our website visitors. In order to see the correlation between leads and support tickets, we would have to blend data from our Google Analytics account and our website.

Too much info?
Ditch the reading, contact InsightWhale and we’ll take care of all the data blending for you 😉

Blending data in Google Data Studio, or so-called data integration, will result in a single chart displaying data from different sources. Here’s a great article on how to create the best Google Data Studio dashboards, by the way — Google Data Studio: Best Practices for Marketing Dashboards.

So how do you blend data sources in Data Studio? Let’s find out in the next section!

How to blend data in Google Data Studio?

There is a number of explanations on how to blend data in Google Data Studio on the web. But in order to be completely honest with you, dear readers, we’d have to say that they are mostly complicated and a bit confusing, especially given that the whole Google Data Studio data integration process is a piece of cake.

To make your life easier, we created this quick visual step-by-step “Google Data Studio: How to Blend Data” Guide so that you won’t get lost in explanations. Here it goes:

To begin with, place both of the charts you’d like to merge side by side (or close by) in the same report page of your Google Data Studio file.

For the example below, we choose the first chart (the yellow one) with the following parameters:

Dimensions: Country
Metrics: Impressions, URL Clicks, URL CTR (Click-through Rate)

And the parameters for the second chart (the pinkish one):

Dimensions: Query
Metrics: Clicks, Impressions, Site CTR, Average Position

Image for post
Image for post

To proceed with your quest on how to blend data in Google Data Studio, select both of the charts you’d like to blend at the same time by clicking the left mouse key and holding Shift ⇧.

Now that you have both of your charts selected, they should be both highlighted with the blue-coloured frame. And all you have to do perform data integration of both charts in your Google Data Studio dashboard is right-click and choose “Blend data” at the very end of the newly-created option window.

Image for post
Image for post

After you’ve pressed the “Blend data” option from the previous step, Google Data Studio has used it’s automated data integration tool to blend data from both of your charts together.

This new chart (the purple one) now has the data from both of the previous Chart 1 and Chart 2 without erasing them and keeping beside your newly-generated blended data creation.

Image for post
Image for post

You can still adjust your freshly-created blended data chart by adding metrics, changing dimensions and even add another data source to the existing ones. To do so all you have to do is press on your new chart, go to the window to your right-hand side, press Data > Data Source > Blended data > ✎ (Edit data source).

Image for post
Image for post

This will open a new bottom window where you can adjust and regulate data metrics for each of your data sources separately, and remove fields you deem unnecessary for the final Blended data chart.

To help you see the totals for each field on your Blended data chart, click on your chart, go to the window to your right-hand side, press Data > Summary row > ☑ Show summary row, which will create an additional row of grand totals.

Image for post
Image for post

Congrats! Now you know how to blend data sources in Data Studio, but that is not all this data visualization tool has to offer. Say you have two data sources — your Facebook and Google Ads — which show the numbers of clicks made to your website from ads over a single year period.

Speaking of which, here’s a great cheat sheet on which metrics and KPIs to visualize in Google Data Studio — Top 20 Metrics to Visualize in Google Data Studio. Now back to our data blending.

Say you want Clicks from both of your advertisement platforms present in a single line graph, so you blend data as we did in the steps above, and your Google Data Studio dashboard now looks something like this:

Image for post
Image for post

But that’s not enough and you want data from two or more sources not only blended but unified, so you can check the sum of all Clicks across platforms, their average etc. In that case here is what you have to do. Copy your newly-created Blended data chart, place it to the right and rename it into Blended & Unified data chart to avoid confusion.

Now click on your Blended & Unified data chart > Data > Metric > Add metric > Create Field.

Image for post
Image for post

That action will open a new window, where you can input the name of your new metric, which in our case is “Total clicks”, and type in a formula which is the name of both of your previous metrics and a plus (+) between them.

Here you can also choose which aggregation type to display — the total sum of your metrics, their average, median, etc. The very second you type in the formula the Google Data Studio report will automatically generate the unified data metric, which in this example is displayed as pink rectangles.

Image for post
Image for post

Since the default format of two lines and a bar in a single line graph chart is not very convenient in this particular case (and quite weird in general), in the style options we changed our newly-created Total clicks metric into another purple line.

Image for post
Image for post

By using these two techniques from steps 3 and 4 of our “Google Data Studio: How to Blend Data” tutorial you can now easily blend data in Google Data Studio from any sources you wish to. To prepare you for any possible pitfalls on the way forward, we created an additional section called Troubleshooting, you’re welcome 🙂

Troubleshooting

When working with Google Data Studio data blending, or any other data visualization tool’s special functions, it’s important to keep in mind all the pitfalls and limitations you can encounter.

First things first, there is a limit of up to 5 data sources you can blend in Google Data Studio. As a matter of fact, adding a large number of sources, graphs and data will make Google Data Studio load very slowly, especially if you add some calculated metrics to your graphs and tables.

If you know you will need to constantly utilize a large number of data sources, blended or not, consider exploring other data visualization solutions like PowerBI or Tableau that are best suited for the task. Here are great comparison articles to help you choose from:

The second point of our Google Data Studio data blending troubleshooting list is data accuracy. Sometimes, the information from the external data sources can be imported or displayed incorrectly. To avoid this, always start with a simple table before turning your blended data in any other data visualization type.

The third point — quality assurance. If you do see missing values or errors in your newly-created blended data tables, here’s what you should watch out for — naming and formatting of your join key. Join key is the metric that occurs in both of the charts you are about to combine and thereby binds them together.

If the namings (say “computer” and “desktop” under Device category) or formatting (DDMM and MMDD under Date) for you join keys are not matching you will face errors and blank fields until you fix them.

Conclusion

Data blending in Google Data Studio is quite easy — if you keep your data sources below five, watch out for how you name your metrics and check your blended data as tables before turning them into more sophisticated data visualization elements.

If in the near future you are looking to switch to some paid data visualization platforms and options, don’t forget to check out our awesome articles above and call us when you run into problems 😉

Originally published at https://insightwhale.com on January 22, 2020.

Written by

Fully remote team of professionals providing Digital Analytics, Conversion Rate Optimization and Business Intelligence services for clients around the world.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store