Hi there, we’d like to show you how to blend data on Google Data Studio.
Before we take you through this exciting journey, we’d like to encourage you to grab a bottle of your favourite liquor and a snack or two.
Then get comfortable as we journey through one of the most accessible aspects of data analytics and reporting on Google Data Studio.
Here’s an interesting fact about Data blending on Google Data Studio that you may not have known.
Data blending allows business analysts to cope with the expansion of data needed to make critical business decisions.
There are four steps to data blending that most gurus ignore. These include data preparation, data blending, results validation, and final output presentation.
Professionals skip these steps several times, which usually doesn’t end well. We thought it wise to show you how to properly blend data on Google Data Studio, even from multiple sources.
What Is Data Blending on Google Data Studio?
We like to describe data blending as a move to merge two or more tables or big data from multiple sources, usually via a key. It sometimes goes beyond merging different file formats into blending different data types.
Top-level marketing executives or professionals like you frequently find themselves in a position where they need to merge information from different units to make sense of certain pieces of information essential for scaling their business.
In this context, Google Data Studio offers an excellent interface where tabulated data from two digital data sources can be merged into one table and visualized on one dashboard.
In order words, you can upload your sales table from that excel sheet onto Google Data Studio and use a time key to blend that data with your Google Analytics report.
That way, you’ll be able to see side by side the number of sales that came in on a particular day and time, the name of the product that was most viewed and purchased as well, the quantity as well as the total monetary value you got from each item that was purchased from your store.
With Google Data Studio, non-technical users can get rapid results in critical areas such as finance, sales, and marketing.
Marketers can blend data from a spreadsheet with the profitability of a product and CRM software. They can view the products that attract the most customer purchasing interest.
Conversely, senior management can make data-driven decisions faster using data blending tools. This will increase sales and profits.
1. Connect the data sources
The very first step towards blending data is to connect your data sources. You can do that via Google Data Studio using the steps below.
Remember, the first step is to connect your data sources. There are thousands of ways to connect even to your spreadsheet and SQL databases on Google Data Studio.
We also offer third-party connections when you seek to connect to a data source not provided by Google.
In that case, you can easily link your data sources through any Porter Metrics connector like LinkedIn, Google My Business, Facebook insights & Instagram insights, and any other connectors we provide on our website.
For this article, we are going to use our Facebook Insights connector. We will connect our account on Google Data Studio to our Facebook Insights.
Before we connect Facebook Insights to Google Data Studio, we need to set up an account with Google Data Studio.
To open a Google Data Studio account, simply visit Datastudio.google.com.
If you haven’t signed into Google Data Studio with your Google account, you will be required to do so before using the tool.
If you click “Blank Report” or “Create,” as shown in the image above, you will be presented with this pop-up.
Enter your basic information and click “Continue” at the bottom left corner of the pop-up form. Doing so leads to setting up your Google account’s next step- setting your email preferences.
Google gives you options on the types of emails you would like to receive from them. You can choose to accept all of their recommendations by clicking “Yes to all” their preferences or clicking “No.”
Click “Continue” on the bottom right corner after setting your email preferences. Doing this links your Google account to the Data Studio.
The next step is to link Google Data studio to our Facebook Insights.
Connecting Facebook Insights to Google data studio is a multi-step process. At the end of the connection, you will have a blank dashboard that looks like this below:
Connection, you will have a blank dashboard that looks like this below:
If you log into Google Data Studio from Porter Metrics, you won’t start with a blank sheet. You’d have the liberty to connect with our beautiful templates, so you don’t start with a blank screen.
This saves valuable time and reduces the stress of starting from scratch.
You can access any of our free Facebook Insights report templates here. You can see that if you desire templates for another platform or use case.
Please read this post to learn how to connect your Facebook insights to Google Data Studio using our Facebook Insights connector.
That is easy if you want to add multiple data sources to a single Google Data Studio report.
From this blank dashboard where you made your first connection, you can add multiple other data sources by either clicking on “Add Data.”
Then follow up by choosing any new data sources you’d like to connect to. On this occasion, we’d like to connect to Facebook Insights Connector.
And begin the authorization process and the prompts until you’ve successfully connected the data sources.
Alternatively, you can add new data sources by clicking on “Resources” and “manage data sources.”
Once you click on Manage added data sources, you should get an interface like the one below.
Now click on “Add a Data Source” to be able to add a new data source to your list of data sources displayed.
Once you click on Add a Data Source, you’ll see an interface like the one below.
Select or search for a connector of your choice and follow the instructions and prompts to connect that data source successfully.
Please note that the interface below shows you a list of active data sources connected to your dashboard. So if ever in doubt, click on resources and manage added data sources.
2. Challenges of Data Blending in Google Data Studio
After connecting to your data sources, you can blend data from both sources to answer questions for your reports.
In this article, we will blend our data to know the total ad cost of our advertising campaign. But before doing this,
3. Data blending settings
Before blending your data sources, you have lots of options in terms of customizations. You can blend up to 5 tables and combine multiple accounts from a single source using Portermetrics connectors.
To make it easier to identify data sources, you can rename tables to suit the data source. You can also choose different dimensions and metrics to customize your data blend.
You can decide to also add a filter to your blend by clicking “Add a filter” on the data blend settings interface. This is particularly helpful if you run different ad campaigns but only want to blend data with a particular campaign instead of all your campaigns.
You can input your custom date range under the “Date range” section if you intend to display blended data on specific dates.
Before blending your data, you must ensure you set a dimension common to your tables. This is called the Join Key.
This join key is a dimension that ties your data sources together in a blend. In most instances, this dimension will be the date as it relates and is common to all data sets.
Depending on what you need to achieve, this could be different. You can decide to use email addresses as a “join key” when blending CRM data with sales transaction data.
Ultimately, you must use a “join key” or a dimension common to all your tables to blend your data successfully.
How to Edit Join Configurations For Data Studio Blended Data
Having connected our data sources to Google data studio and learned about common challenges in blending your data, let’s learn how to edit join configurations in Data Studio.
There are three common ways of doing this; outer, inner, and cross-joins. We’d examine each type of join configuration with use case examples.
Outer, inner, and cross-joins on Data Studio
● Outer Join
The outer join involves three kinds of join: left outer, right outer, and full outer. The left outer join returns all matching rows from the right table to the left table(Master table).
The right outer join is opposite of the left outer join. In this case, the right table is the master table. The full outer join returns all rows from the left and right tables.
● Inner Join
When used to blend data, the inner join returns only matching rows from left and right tables.
For example, we have a table with sales data that contains customer names, IDs, products purchased, and quantity, and another table (stripe transactions) that contains customer names and IDs.
The inner join, when used, creates a blend displaying only customer names and IDs because those are the only matching rows on both tables.
● Cross-Join
This is the only join that doesn’t require a “join key.” The cross-join returns every possible combination of rows from the left and right tables.
4. Creating charts and custom formulas with blended data
One cool feature of Google Data Studio is its ability to create charts and custom formulas with blended data. In this section, you will learn how to do so.
How To Set Up Data Studio Blended Data Charts
Firstly, let’s learn how to set up blended data charts on data studio. Let’s assume you want to know the total number of clicks your website receives within a given period.
You can blend your Google Analytics data with your Google ads data together.
To blend both tables, click on the canvas and drag over both tables. Right-click and select “Blend data” on the pop-out menu.
Clicking “Blend data” blends both tables to give the result below:
This kind of blend is called “cross-join” because it returns every possible combination of rows from both tables from left to right.
To confirm or edit how you want to blend or join your data, on the right-hand side of your dashboard, beneath “Chart,” click the pencil icon next to “blended data,” as shown below:
This displays the blend data settings as shown below:
By clicking on “Cross-join,” you can configure how each table interacts with the other. You could decide to join them using the inner or outer join settings.
To set up a blended data chart, on the top right of your dashboard, click “Chart.”
This opens up the various kind of charts available to you on Google Data studio. Simply choose any chart type, and you’d be good to go.
How to Edit Blended Visualizations in Google Data Studio?
After choosing your chart type, you can further edit it to suit your taste. Depending on the chart type you choose, styling differs.
Assuming we choose to display our blended data using a column chart, we have styling options that can be found on the right-hand menu of your dashboard.
Beneath “Chart” on the right-hand menu of your dashboard, click “Style.” This opens up all the styling options available to edit our column chart.
We can choose to add a background color, a header, reference lines, etc.
How to Create Calculated Fields For Google Data Studio Blend Data Tables
To further make your blended data informative, you can use the calculated fields feature on data studio.
Still using our example, we can use the calculated field feature to get the total number of clicks from our Google Analytics and ads data.
To do this, under the Chart tab on the right-hand menu, click “add metric,” as shown below:
Then click the “Create field” on the drop-down menu.
This opens up the calculated field table. Here, we’re going to name our new field total clicks and enter the formula “clicks (table 1) + clicks (table 2), as shown below:
Click “Apply” at the bottom to add the new field to your blended data table.
Our new table above contains the new field we created (total clicks).
5. Null values troubleshooting on Google Data Studio
Null values on Google Data Studio can be a frustrating experience if you intend to report your total ad costs on Facebook and Google within a given period but haven’t spent money advertising on Facebook, for example.
Google Data Studio displays Facebook advertising costs as “null” instead of zero. To troubleshoot this, you can create a new calculated field in Google Data Studio using a case statement.
The case statement above can be used to replace null with zero. Simply replace the metric with a metric that suits your case.
6. Examples Of Data Blending With Google Data Studio
So far, we’ve used Google Analytics and Google ads data to explain the concept of blended data. Let’s get a little bit specific.
● Blending Facebook Ads Data
If you followed from the beginning, we discussed connecting your Facebook data to the data studio using our Facebook ads connector.
Doing this opens up many opportunities to create visually appealing Facebook ads reports that aren’t filled with metrics that don’t matter to you or your clients (If you’re an agency owner).
If you’ve connected your Facebook data to data studio, you can equally blend your data to create a custom report. Let’s assume you intend to know the average cost per click for each type of audience; this is easy to know.
Add Facebook data to your report dashboard to make this happen, then click blend data.
Set metrics and dimensions as shown below:
Google data studio automatically blends the data to give a result as shown below:
However, you can always configure the join, dimensions, and metrics to suit your taste.
● Blending Google Ads Data
We can also blend data for Google ads to create a custom report. The blend feature can allow you to know which country generates the most clicks for your ad campaigns, for example.
To do this, add your Google ads data to your dashboard, click “blend data,” and enter the dimensions and metrics as shown below:
T
his gives us the blended table as shown below:
From the table, we can easily see the number of clicks our Google ads received from each country.
7. Limitation Of Data Blending In Google Data Studio
Blending data in Google Data Studio has many advantages compared to other reporting tools. Nevertheless, it comes with its unique set of limitations.
1. Blended data can be inaccurate.
Blending data on data studio often returns an inaccurate result, especially when working on complex projects. These errors are sometimes difficult to spot, except if you pay detailed attention to your reports.
To mitigate this error, you should blend data in bits at different levels of your reports.
2. Limited use of data sources
Google Data Studio, unfortunately, doesn’t permit users to blend more than 5 data sources. This can be very frustrating for agency owners who want to create advanced and in-depth client reports.
3. Missing values
When there’s an error in a data blend, Google Data Studio does not fix the errors automatically. The missing fields are displayed as “null.” You have to troubleshoot the problem and get it fixed.
Conclusion
Google Data Studio is a powerful data visualization tool that allows users to blend data from multiple sources. This makes it easy to create custom reports and dashboards that can be shared with others.
Build your first report and visualize all that data on Google Data Studio via our free connectors or Schedule a call with Porter Metrics Support here so we can guide you to generate that free report right now.
In creating custom reports, we understand how terrifying it is to start from scratch; hence, we’ve created beautiful report templates you can use for your reports.
This article also outlines some of our easy-to-use connectors, which easily integrate with our free report templates. Aside from using our beautiful templates, we can also help you create your very first report for free.
Yes, you read that right. Simply book a 15-minute call with our support team, and you’ll be on your way to creating an epic report.
This brings us to the end of this article; we hope you found it interesting and informative. If you have any questions, kindly drop them in the comments section, and we’ll be there to answer them.
Frequently Asked Questions
Can you blend more than one data source on data studio?
Yes, you can blend more than one data source on data studio. You can add up to five data sources for your reports.
How do I fix system errors when blending data?
A system error could be due to the connector not being set up correctly. Ensure you properly set up our connector. You can also reach out to our support team to help you.
How to solve “unknown data source” error.
To solve this error, ensure you add a data source when making your report. If you’ve confirmed that you’ve added a data source, disconnect the connector and connect it again.
If the problem persists, wait a few hours and refresh.
