When you're blending your data, the outcome depends on the type of blend you choose to use as well as the join key. Selecting a suitable blending method is important to produce the results you want to see.
Types of blends
When blending data, there are 2 types of blends: union and join. You can also mix union and join if it serves your use case.
Union blends
Union blends aggregate or compare data from data sources with similar structures. Examples of union blends include:
- A paid channels mix that aggregates the cost, impressions, or clicks on all your paid platforms.
- An organic social mix that aggregates the number of followers or engagements across all your organic platforms.
Join blends
Join blends join insights from 2 data sources with different structures, where the data can be linked with a join key.
A good example is joining Facebook Ads data with Google Analytics data, where the cost of campaigns is linked to the conversions of these campaigns. Google Analytics does not know the ad metrics per campaign, such as cost, impressions, or clicks, and the ads platform doesn't know the engagement per campaign on the conversion platform such as engaged sessions, bounce, or conversions.
Mix of both
A good combo is to create a union blend to aggregate paid or organic channels and then to link the aggregated insights to an analytics platform like Google Analytics using a join blend.
Types of joins
A join type defines how the data is joined – what data is matched with what, what is excluded, and if empty values are used.
| Join type | Use case |
|---|---|
| Right join | Take all the data from the right table and the matching data from the left table where the join keys are the same. |
| Left join | Take all the data from the left table and the matching data from the right table where the join keys are the same. |
| Inner join | Combine data from both sources, match it where the join keys are the same, and drop the data that doesn't match. |
| Full outer join | Take all the data from both sources and match it where the join keys are the same. In the joined table, pad the non-matching columns with empty values. |
Join keys
Join keys are values to be matched between 2 data sources to join the insights from both sides. Select your join key based on what results you're looking for.
- To know what ad campaigns brought the most conversions, join on campaign dimensions.
- To know what data sources brought the most sales, join on traffic source.
- To report on a daily basis, add date as a join key.
Practical examples of different join operators
Let's say your campaigns A, B, and C have generated impressions and cost data on your paid channels:
| Campaign | Impressions | Cost |
|---|---|---|
| Campaign A | 1,200 | 500 |
| Campaign B | 3,400 | 780 |
| Campaign C | 1,450 | 460 |
| TOTAL | 6,050 | 1,740 |
On your analytics tool, campaigns B, C , and D have generated conversions data:
| Campaign | Conversions |
|---|---|
| Campaign B | 10 |
| Campaign C | 8 |
| Campaign D | 2 |
| TOTAL | 20 |
There can be several reasons why some campaigns are present in one data source or the other. For example, campaign A might not have generated conversions (maybe it was optimized for brand building and not conversions). Maybe campaign C is an organic or email campaign that doesn't have associated costs from paid channels.
Let's take a look at different join options to join the campaign data.
Left join
Notice how campaign D is not present in the query result, as it does not exist in the paid channel data source, which is the table on the left.
| Campaign | Impressions | Cost | Conversions |
|---|---|---|---|
| Campaign A | 1,200 | 500 | |
| Campaign B | 3,400 | 780 | 10 |
| Campaign C | 1,450 | 460 | 8 |
| TOTAL | 6,050 | 1,740 | 18 |
Right join
This time, campaign A is not present in the query result, as it does not exist in the analytics data source, which is the table on the right. The total number of impressions, cost, and conversions are different.
| Campaign | Impressions | Cost | Conversions |
|---|---|---|---|
| Campaign B | 3,400 | 780 | 10 |
| Campaign C | 1,450 | 460 | 8 |
| Campaign D | 2 | ||
| TOTAL | 4,850 | 1,240 | 20 |
Inner join
In this case, only campaigns B and C are in the results, as they are the only campaigns that are found in both the paid channel and the analytics source.
| Campaign | Impressions | Cost | Conversions |
|---|---|---|---|
| Campaign B | 3,400 | 780 | 10 |
| Campaign C | 1,450 | 460 | 8 |
| TOTAL | 4,850 | 1,240 | 18 |
Full outer join
A full outer join will return all possible rows.
| Campaign | Impressions | Cost | Conversions |
|---|---|---|---|
| Campaign A | 1,200 | 500 | |
| Campaign B | 3,400 | 780 | 10 |
| Campaign C | 1,450 | 460 | 8 |
| Campaign D | 2 | ||
| TOTAL | 6,050 | 1,740 | 20 |
Conclusion
As you can see, all joins tell different stories — there is no right or wrong option. When selecting a join operator, you need to know what insight you're looking for.
- Are you only interested in reporting on the traffic that came from your paid channels?
- Or all traffic to have an overall vision of your marketing activities – both paid and organic?
- Or are you only interested in traffic coming from your paid channels and generated conversions to understand how your conversion-focused campaigns have performed?
Troubleshooting
When you're creating your blends, we recommend querying the data from all data sources separately and comparing it to the final result.
For example, if you're joining paid channels and Google Analytics by campaign and date, query your paid channel union blend per campaign and per date, and your Google Analytics per campaign and per date. Verify that the join keys have equivalents in both queries.
Another explanation for the data mismatch is that maybe the value returned in Session Campaign from Google Analytics 4 does not exactly match the values returned from the Campaign name dimensions in your Paid channels union blend. In this case, you need to transform the values with a custom field to match. It's also possible that there simply is no data for these days or campaigns.
Instructions
Follow these instructions to create your joins for data blending.
Note that the joining of the data sources is done with the Data Blending feature, which is available in our Marketing Intelligence Cloud license.
Join two data sources
- On the Supermetrics Hub, go to Transform → Data blending.
- Click Create new blend.
- Select the Join blend type, and click Next step.
- Select data sources – in this example we'll use Facebook Ads and Google Analytics 4, and click Continue to configuration.
- Select the accounts to use with each data source, and click Continue to overview.
- Review your data sources and accounts, and if all looks good, click Add to blend.
- Select the fields you want to include in your blend. These fields will be available when you build your queries using the blend.
- In this example, let's pick Amount spent, Impressions, and Campaign name for Facebook Ads.
- And let’s pick Conversions and Session campaign name for Google Analytics 4.
- When you have all necessary fields selected, click Next step.
- Next, let’s select the join key. This is how the data will be joined. The value returned should match in both data source. Here, let’s select Campaign name from Facebook Ads and Session campaign name from Google Analytics 4, as the value in Google Analytics 4 is populated by the campaign parameter in their utm tags.
Other typical join keys are Date and Source (where the value from Google Analytics 4 is the traffic source, and the value from Facebook Ads is “facebook”.) - Next, decide how the data will be joined (Left, Inner, Right, or Outer) — see the types of joins section for more details.
- When your join key and join operator are selected, click Next step.
- Give a name and an optional description for your blend, and click Create blend.
- You can now go to any destination, select the Data blending data source, and select the blend you just created.
Note that if you're using data blending in Looker Studio for the first time, you need to go to the connector to make it available for your reports. Follow these instructions to get started with blends in Looker Studio. - To query data from your blend, select the metrics from each data source.
- Next, select the dimensions to include in your query.
There will be two instances of the dimension you used as a join key. If you selected a left, inner, or right join, the returned value will be the same and you can select only one. However, if you selected a full outer join, some values in one data source might not exist in the other, so you might want to select both.
Join more than two data sources
Currently, it's not possible to join more than two data sources. However, it's possible to join a union blend with another data source.
The steps are the same as when joining two unique data sources, but instead of having a fixed list of fields coming from the data source, you'll have to choose the fields that are present in your union blend.
In the example below, Cost is the blended cost from 5 different paid channels, as defined in this union blend.