Updated 10/19/22 with new information.
Are you creating charts on Looker Studio (formerly known as Google Data Studio) using multiple data sources and running into problems with data blending?
In this blog post, we’ll introduce you to exactly how data blending works in Looker Studio, note the considerations that limit our blending abilities, and share some tactics we’ve successfully implemented to resolve data blending issues. If you’re new to Looker Studio and would like to learn more, please check out our blog on getting started with Google Data Studio.
What Is Data Blending in Looker Studio?
Looker Studio’s data blending feature allows users to combine data and calculate metrics across data sources without writing code. It is a powerful function and yet requires some understanding of fundamental SQL join types.
You can use data blending to create any visualization offered in Looker Studio for a complete view of performance across different sources. You can also blend data sources to combine metrics across multiple properties like cost, revenue, and transactions, by joining matching dimensions like campaigns, keywords, and device types.
Looker Studio allows users to easily create a Data Blend in a report by simply clicking multiple scorecards or tables, right-clicking, and selecting ‘Blend Data.’ This method creates the Data Blend for you with the selected metrics and sets the first data source you selected as your left-most source.
In the screenshot below, click the Cost scorecard first and press the command key on mac or the control key on pc. Then, click the Clicks scorecard and right-click to select Blend data. That’s how you blend the Cost/Clicks scorecard, but you need to adjust the metric type from percentage to currency.
Why Use Data Blending in Looker Studio?
Combine Metrics Across Platforms
I use and build automated reports extensively for client work, and data blending is undeniably the most useful feature of Looker Studio. More often than not, I run into clients who have multiple Google Analytics properties or Google Ads accounts, are running Paid Social ads on multiple social platforms, or offer their services or products within a website and across applications.
Clients and analysts should look at combined performance metrics for efforts with the same goals or budgets, regardless of how the data collection is disjointed. Check out my blog post on the marketing metrics you must track to determine if you’re tracking the right ones.
Better Performance Reporting
Blending data in Looker Studio allows you to combine metrics reported on different platforms that should be viewed as a total (ex., app conversions in Firebase and website conversions in Google Analytics). You can provide performance for an overall budget spread across more than one source or medium (ex., Facebook Ads and LinkedIn Ads). Ultimately, data blending provides holistic reporting on performance.
When to Use Data Blending in Looker Studio
Data blending should be used when you need to view combined data for analysis or reporting purposes. Below are some example use cases for data blending in Looker Studio, just in case this sounds overly general or doesn’t seem like it will ever apply to you—which it will.
- You’re running Google Ads across multiple accounts with the same overall budget.
- You’re running Bing Ads and Google Ads and need to report on combined Paid Search performance.
- You need to report on Bing Ads Ads cost metrics by campaign but report on goal completions out of Google Analytics.
- You want to report on Paid Social campaign metrics and goal completions from Google Analytics.
- You’re running Paid Social ads across multiple platforms from the same Paid Social budget.
- You manage multiple subdomains or sister sites for the same account.
What Can You Blend in Looker Studio?
Conceptually, you can blend just about anything in Looker Studio as long as the data source is in an available Looker Studio connector and there’s at least one dimension in common (i.e., a Join Key) among your data sources. Your data can live in a Google Sheet, SQL database, or even be a simple file upload to connect to Looker Studio, and custom fields can be added to create matching Join Keys.
Unfortunately, it’s not as easy as we’d like to blend any data with data blending in Looker Studio with these liberties. There are limitations outside the two main requirements (data source available in Looker Studio and Join Key) to return accurate metrics in your reports.
How to Blend Data in Looker Studio
Step 1: When in Edit mode, select the chart you need to work on and click the BLEND DATA button under Data source to add more data sources to the blend.
Step 2: Click Join another table, search and select the data source.
Step 3: Select the dimensions and metrics or create calculated metrics as needed from each data source with or without filters.
Step 4: Configure join by selecting the join operator and matching dimension(s) for the join conditions, then save.
Step 5: Rename the Data Blend and click save.
Different Types of Join Operators
Left Outer Join was only initially available on Looker Studio for data blending. In early 2022, Looker Studio released other advanced join types. There are now five join configurations available for data blending in Looker Studio. If you have experience with SQL, the following join operators should be fairly straightforward.
Inner Join (New)
It will return the matching rows from the left and right tables. In the screenshot below, only Campaign 1 and Campaign 2 match in Data Source 1 and Data Source 2.
It will return everything from the left table plus the matching rows from the right table. The screenshot below shows all campaigns from Data Source 1 plus the matching Campaign 1 and Campaign 2 from Data Source 2. This is a default join type when you start data blending.
Right Outer Join (New)
It will return everything from the right table plus the matching rows from the left table. The screenshot below shows all campaigns from Data Source 2 plus the matching Campaign 1 and Campaign 2 from Data Source 1.
If you shift the right table to the left, you’ll get Left Outer Join.
Full Outer Join (New)
It will return all rows from the left or right table, resulting in potentially very large datasets. The screenshot below shows all rows from Data Source 1 and Data Source 2.
Cross Join (New)
It will return all possible paired combinations of each row in Data Source 1 with each row in Data Source 2.
Challenges of Data Blending in Looker Studio
Limit of Five Data Sources
Five data sources sound like plenty, but there are many situations when you need more than that to blend together. I’ve run into it quite a few times, and the only solutions have been to either forgo some metrics or combine data sources into one through Google Sheets or by importing data from one platform to the other.
Some examples where I’ve run into problems because of this limitation:
- Combining Bing Ads Cost data (Data Source 1) with Google Analytics conversions (Data Source 2) across more than two accounts or properties.
- Combining Google Analytics metrics across more than five different properties.
- Combining Assisted Conversions data with Google Analytics last-click conversions across more than two accounts or properties.
Common Data Blending Issues and How to Resolve Them
The issues I often run into in Looker Studio are easily resolved with a simple workaround. It seems Looker Studio is still ironing out logistical kinks for functionalities that should work seamlessly on their own. The following solutions and hacks have solved my blending issues so far.
Data Returning “Null” Values
Looker Studio will return “null” (-) values when any row of a dimension has no value associated with it. In the example below, the ‘Leads’ column returns null values and misses conversions from the second data source in the data blend due to the Left Outer Join operation.
The ‘Leads’ column is missing a conversion because the First Interaction Assisted Conversion (which is from the second data source) occurred for a keyword that did not return any values under the first data source. This results in inaccurate reporting! You can see that the total number of Leads under the ‘Leads’ column does not add up to what Looker Studio is reporting as the ‘Grand total.’
This issue can be resolved by creating custom metrics in the table that tell Looker Studio to either return the max metric for that field or return a 0 (instead of null). Use the formula below to create this metric:
NARY_MAX([FIELD],0)
Replace [FIELD] with the metric and use the SUM([FIELD]) calculation if you need an aggregated metric. This is the formula used in the above example:
NARY_MAX(SUM(Goal Completions),0)+NARY_MAX(SUM(First Interaction Assisted Conversions),0))
Returning 0s instead of nulls allows Looker Studio to combine the metrics and return a value, even if there are no values for one of your combined metrics. It’s a silly workaround, but it’s what makes sense with Looker Studio’s current configuration.
Another solution I’ve seen for this is creating a custom metric using a CASE WHEN formula, telling Looker Studio to either return 0 when the value returns null or to return the field:
CASE WHEN [FIELD] IS NULL THEN 0 ELSE [FIELD] END
Data Returning Incorrectly Calculated Metrics
I highly recommend always viewing your combined metrics by the Join Key in a table format before putting it into a scorecard. Viewing it in a table allows you to check that the correct metrics are being pulled in from each source before combining them.
When you’ve noticed that your report is calculating metrics incorrectly, comb through the following checkpoints:
- Make sure the naming is the same for your Join Keys. I’ve tried using Device Category as a Join Key between Bing Ads and Google Analytics before realizing that the naming conventions are different. Bing Ads uses Computer, Tablet, and Smartphone instead of Desktop, Tablet, and Mobile, which are used in Google Analytics. Use a CASE WHEN formula to return the same dimension names in one data source to match the other. For example, you can create this custom field in your Google Ads data source:
- Make sure the formatting is the same for your Join Keys. This is specific to dimensions like Date. Although the Date dimension may be called the same and look the same in their platforms, they may be formatted differently in Looker Studio. Pull your data into a table with the date set as the dimension and check the formatting of your dates. You may find that one is YYYYMMDD, while the other is MMDDYYYY. If reporting on a monthly basis, you should attempt changing the ‘Show As’ under each date in every data source to ‘Year Month.’
Wrap Up
I hope these tips and tricks are helpful when you need to do data blending on Looker Studio for reporting. Since Looker Studio keeps rolling out new features, there will likely be new issues, and hopefully, some existing ones will get resolved.
Hi Oeuyown,
That’s a great tips from you and thanks for that. I am also getting the “null” values sometimes and now I know how to solve that issue. Hope to get more tips from you about getting the Google Ads Headlines into Google Data Studio Reports. Currently, that feature is not available I guess.
Thanks, I’m glad you found my post helpful! GDS really is missing quite a few features but, as mentioned, it’s ever-evolving and hopefully, Google Ads Headlines will be offered soon! In the meantime, you could use the Google Ads Supermetrics add-on to pull headlines and Google Ads data into a Google Sheet and connect to that as your data source. You can also schedule this report to refresh automatically so it’ll keep updated as needed. It’s not the most seamless solution but gets the job done if your report can’t go without it.
Hi Oeuyown,
Thanks for sharing this content. As far as I can see you can’t combine Google Ads and Google Analytics using campaign as the key as they are different cases. Unless you name all of your campaigns in Google Ads in lowercase which I’m sure nobody does. Campaign id also doesn’t work, I assume because in Analytics it’s a text field but in Google Ads it’s a numerical field so GDS can’t make the connection. So how do people “blend” Analytics and Ads data? Thanks!
The campaigns don’t typically come in as lowercase in GA—I wonder if there’s a filter set in GA to force lowercase? If you’re seeing lowercase campaign names, you could create a new field in your Google Analytics data source to force lowercase using the LOWER(‘Field’) function then use this new field as the join key.
You can also change field types in GDS. Navigate to the field in your data source and click the drop-down for Campaign ID and change it to text in GA or a number in Google Ads. Here’s a screenshot: https://imgur.com/a/hpmpl2t
I hope that helps!
Hi Oeuyown Kim – Thank you for your well-written blog!
One thing that I have run into that no one seems to talk about is that when I am adding in fields to a blended data source I get maxed out at 10 dimensions and 20 metrics. It seems like you can add a max of 30 fields to a blended data source (from each source) as the dimensions and metrics just turn into fields later on. I know that tables have a max number of dimensions and metrics, but I don’t see any documentation about a max number of dimensions you can add to a data source within blended data. Have you run into this? Thanks for any support or helping me with my thinking on this.
Thanks, Wesley! I’m glad you found it helpful.
I haven’t run into that issue myself but you could add multiple of the same data source in one data blend and pull additional fields into the second instance of that data source. However, you’ll hit a limit of 5 data sources in one blend.
Hi Oeuyown,
Are you familiar with using Landing Page as a join key between GA and GSC data? unfortunately, GSC stores landing page URLs prepended with “https://website.com” and GA doesn’t, which means they don’t match. I tried making them match using this method: https://medium.com/@WillmannTobias/google-data-studio-how-to-join-google-search-console-and-google-analytics-data-with-the-new-data-3480ac0cd4b0 but it says they’re incompatible, even though you can see in the separate tables that they match. Any ideas how to solve this?
Odd, that should do the trick! I would double-check that the URLs are exactly the same again in separate tables with a metric for each data source—(1) GA URLs w/ hostname appended and a metric (like sessions), and (2) GSC URLs and a metrics (like impressions).
Do you see that the URLs are exactly the same, the same URLs show across both, and each of the tables are returning metrics in individual tables?
Thanks a lot. NARY_MAX did the trick for me when cumulated data with “null” fields wasn’t showing. Good article 🙂
Hey there. If I use the NARY_MAX formula or the case formula, it does turn null into 0 but it still shows all of the 0 / null values in the table. If I try to apply a filter to exclude null or include only > 0, then the table breaks. And since the two formula solutions are within the table and not a new metric / dimension in the blended dateset itself, I can’t use them in filters. Thoughts?
WOW. Very extensive. Thank you.
I am working on a blend (multiple Google ads) and my cost values are not matching Google ads interface. I am not using cost to blend (should I?). My numbers are around 10% off
Thanks for reading, Koki!
Without seeing what you’re currently using as the Join Key for the blend, I would recommend joining the sources on the Date dimension only, unless you anticipate needing to filter by common dimensions on the blended data source.
Let me know how that works.
This is a very informative post. It is really helpful in solving the problems related to data blending.