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.
Step-by-step guide on how to join data sources in Supermetrics
Prerequisite: joining data sources is done with the Data Blending feature, available in our Marketing Intelligence Cloud license
Join two data sources
- Go to the Data Blending page
- Create a new Blend
- Select the "Join" blend type
- Select data sources – for this example we will pick Facebook Ads and Google Analytics 4
- Select your accounts
- Select the fields you want to include in your new data source and you will want to query to build your reports
- In this case, 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
- Next, let’s select our 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 GA4, as the value in GA4 is populated by the campaign parameter of they utm tags
Other typical join keys are Date, or Source (where the value from GA4 is the traffic source, and the value from Facebook Ads is “facebook”. - You can then decide how the data will be joined (Left, Inner, Right or Outer) – see the “Differences in join operators” paragraph for more details
- Name your blend and save it
- You can now go to any destination, select “Data blending” as data source, and select the blend you just created
- To query it, select the metrics from each Data sources
- And select the dimensions you need. There will be two instances of the dimension you used as a join key. But if you selected Left, Inner or Right join, the returned value will be the same and you can select only one. If you select a full outer join however, 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
Today, it is not possible to join more than two data sources. However, it is possible to join a union blend with another data source or even another union blend.
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 will have to chose the fields that are present in you union blend.
In the example below, “Cost” is the blended cost from 5 different paid channels, as defined in this union blend.