Looker Studio Blending Super Guide

Looker Studio Blending Super Guide

The following article provides useful tips for how to blend data in Looker Studio.  

What You Can Blend

A list of what you can blend in Looker Studio:
  1. Two data sources that share the exact same dimension.  The keywords here are "dimension" and "exact".  We rarely ever see a case for using metrics as join keys.  Examples are:
  2. Same date:  Both data sources have a date value of 2020-12-20.
  3. Same campaign name:  Both data sources have a field (the field name doesn't need to be identical - just the values) with the value "holiday shopping 2021".  This is a common join key when blending Google Analytics data with Facebook Ads, Microsoft Ads, Mailchimp and other data sources.
  4. Same region or country:  Both data sources have a dimension that reports region (I.E. "Florida") or country (I.E. "Germany") identically. 

Punctuation and capitalization must be identical:
- If one data source reports "florida" and the other "Florida", they will not join.
- If one data source reports "united-states" and the other "united states", they will not join.

What You Cannot Blend

A list of what you cannot blend in Looker Studio:
  1. Two or more different date ranges of data, by date:  If you have data in one data source going up to Dec 31, 2020 and another data source starting Jan 1, 2021, there is no way to combine these data sources to report 2020 and 2021 data by date.  You can report totals for the combined data sources across both timeframes, but cannot use the date dimensions as join keys.
  2. Two or more data sources using metrics as the join keys:  We welcome feedback regarding this assertion!  We have yet to see anyone joining 2 or more data sources when using metrics as the join keys, but there is always a first.
  3. Two or more data sources that have no identical values for at least 1 dimension of each:  In order for blending to be successful, the join keys look for identical values (spelling and punctuation included - everything must be exactly the same).

Blending For Beginners

Blending is an advanced Looker Studio feature.  There is a learning curve with Blending, but once you learn the concepts you should be equipped to join data sources to enhance your reports.

Before You Begin Blending:

Establish an objective:  Why are you blending?  To combine two data sources' total Impressions, such as Google Ads with Facebook Ads, in 1 scorecard?  Or do you just need to report Google Ads and Facebook Ads on the same page?  If you can get away with reporting without blending, we recommend you do so because blending increases the complexity and risk for reporting the error.  If you do want to sum up multiple data sources' metrics or do the math on fields across multiple data sources, then proceed.  
 

Identify the join condition (join key). At least one dimension must be used between the data sources that have identical values between data sources. The date is a very common join condition when one needs to sum up PPC metrics like Impressions, Clicks and Spend. The campaign is very common when trying to report total engagement with a Campaign that is identical across multiple data sources.  

 

Under Blend Data, select a Table 1 and Table 2 and click Configure join.




In Join configuration, select a Join operator to use.



Under Join conditions, click Add field for the left and right tables to add a field from each table. Note: A cross join does not accept any conditions.


  • Understanding joins: Looker Studio allows the use of five different types of joins for blending. 

    • Left outer join: The data source chosen on the far left of your blend will combine only with the data source(s) to the right that shares the same key values. If the far left data source does not have data for a particular day, the blended data will also not contain that data. We see this happen quite frequently when clients try to blend data and then ask us why there are dates missing data.

    • Because of this limitation, you must choose the far left data source wisely!  Make sure the data source has data every day in the date range you want to report. And as a hack, if the particular metric(s) you want to report do not have data every day, add additional metrics that you may not use just to populate 0's for the rest of the data you do want to use. For example, there may be no Transactions on certain days in Google Analytics for your data source. Add Sessions to the list of metrics so that Transactions must populate with 0's in the output to Looker Studio.

    • The left outer join operation means only data in the left circle will report in Looker Studio.

    • Right outer join: The data source chosen on the right of your blend will combine only with the data source to the left that shares the same key values. If the right data source does not have data for a particular value, the blended data will not contain that data. Only data in the right circle will report in Looker Studio.

    • Inner join: Only rows that match in both the left and right data sources will report in Looker Studio. Rows in the left or right data source only without a corresponding row in the other data source will not be reported.

    • Full outer join: All matching rows from both the left data source and the right data source will be reported inclusively in Looker Studio.

    • Cross join: A cross join is a cross-product of the left and right data sources without any conditions, and can produce a very large result set. The number of rows returned will be the number of rows in the left data source multiplied by the number of rows in the right data source. Using a cross join can cause a “too much data requested” error.

 

Calculated Fields and Blending

Quite often the reason for blending is to create calculated fields that sum up metrics from 2 or more data sources or to do other math functions across metrics.  For example, in the blend below, the metric "Total Qty Sold" is the summation of Miva Qty, eBay Qty and Amazon Qty:



The formula is as follows, to sum up these 3 quantities into 1 total quantity:




Advanced Blending

Ready to take blending to the next level?  Here are some examples:
  1. Filtering one of the data sources in the blend to include or exclude certain data:  This must be done in the blend:



Any filtering for blended data sources must be set up in the blend itself
Do not filter in the report outside the blend:



Blending Troubleshooting

See the following suggestions if you are not getting data or correct data in your blend.

No data:

  1. If you create separate reports with the 2 or more blended data sources, do you see identical values for the dimensions used as join keys?  If not, that is the root cause.
  2. If you use "date" as the join key, does the leftmost data source have data for all dates?


Incorrect data:

  1. Are the calculated fields all wrapped in 'sum()'?


  1. If wrapping in 'sum()' still doesn't work, try wrapping in 'nary_max()':


Still Need Help?

Blending is difficult!  Let the PMA team help you with our professional services.  We can complete most blends in 1 hour and at reasonable rates.  Contact us with your questions and we will get your blending questions answered and offer solutions if your data cannot be blended.



    Still having issues?
    Send us a ticket and we will get back to you.
    Submit a ticket

      • Related Articles

      • Looker Studio Demo Template Guide

        When connecting to Looker Studio, there are a few best practices to keep in mind. From demo templates to customizations to troubleshooting, this guide will teach you some pro tips for using Power My Analytics data connectors. Use a Demo Power My ...

      • Instagram Ads Data Connector User Guide

        The following article is a guide for automating reports with your Instagram Ads connector. It includes metrics available and instructions for how to connect, use, and backfill your Instagram Ads data. To create an Instagram Ads report, you will need ...

      • Facebook Ads Data Connector User Guide

        The following article is a guide for automating reports with your Facebook Ads connector. It includes metrics available and instructions for how to connect, use, and backfill your Facebook Ads data. To create a Facebook Ads report, you will need at ...

      • Looker Studio Error Troubleshooting Guide

        Getting an error in Looker Studio? Click on the wrench icon or "See details" link to find the error details. Common Errors Error: You must select a valid config when connecting to the data-source Error: This action is not supported while logged into ...

      • Mailchimp Data Connector User Guide

        The following article is a guide to automating reports with your Mailchimp connector. It includes information on available data as well as currently unavailable data and tips on how to get the most out of your Mailchimp reports. To begin creating ...