Data blending in Google Sheets: 2 ways to combine data from multiple sources for deeper marketing reports

If you want to blend data from different sources for a holistic overview of marketing performance, Google Sheets is a great tool. It has different calculations and formulas that you can play around with. Especially it allows you to troubleshoot when there are issues with your blended data or having problems with blending data in Looker Studio.

In this article, we’ll discuss three useful functions you can use to join data in Google Sheets and some best practices to make data blending smooth sailing.

Here’s a sneak peak:

• Bringing data into Google Sheets
• Managing data in Google Sheets
• Three useful functions for joining data in Google Sheets
• An easier way to blend data in Google Sheets using Supermetrics Marketing Intelligence Cloud

Bringing data into Google Sheets

If you want to import data from Google platforms like Google Analytics 4, Google Ads, Google Search Console, etc. You can use the native connector. They’re free.

However, in case you want to bring data from other marketing sources besides Google, such as Facebook Ads, LinkedIn Ads, etc., it’s better to use a third-party tool like Supermetrics. This way, you can:

• Move data from +150 marketing sources
• Set up automatic refreshes and emailing so you get notifications whenever a certain metric changes
• Access to free, premade marketing reporting templates.

Managing your data in Google Sheets

It can get messy quickly when you bring data from different sources to Google Sheets for blending. Dividing them into separate tabs is a good way to stay organized with your data.

The ‘raw data’ tab is where you store all your unformatted raw data from your data sources. In this example report, we use Supermetrics to pull data from Facebook Ads, Microsoft Ads, and Google Ads into three separate tabs.

The ‘blended data’ tab is where the magic happens. You can match your data together and perform some calculations to get more insights from your data.

The ‘reporting data’ tab is where you put the last piece of the puzzle. When you’re done enriching and transforming the data, you can present them in a separate tab where it’s easier to monitor.

Additionally, you can connect the ‘reporting data’ tab to Looker Studio to bring the final results to your dashboard. You can find the Google Sheets connector in the connector gallery.

Three useful functions for joining data in Google Sheets

VLOOKUP

VLOOKUP is one of the most used functions for data joining. It lets you search for a value in one table and use it in another table.

The syntax for VLOOKUP is:

VLOOKUP (search_key, range, index, [is_sort])

• search_key: the value you want to look up.
• range: the range that contains the value you want to look up. Note that VLOOKUP will search from the first column in your range.
• index: the column number (within your chosen range) that contains the returning value.
• is_sort: this parameter is optional. Here, you can specify if you want to receive an exact match (FALSE) or the nearest match value (TRUE). In the case of data joining, you’ll want to set it to an exact match.

You’re telling Google Sheets what value you want to search for, where you want to search for it, the column number in the range that has the value to return, and finally, if you want to receive an exact match (FALSE) or the nearest match (TRUE).

Let’s say you have two tables:

• A marketing table with data about date, source, medium, campaign, impressions, cost, and clicks
• A conversion table with data about date, source, medium, transactions, and revenue.

There are two steps to connecting the puzzles.

First, you need to create composite keys for two tables using the TEXTJOIN function. Each composite key can be used to uniquely identify each row of the table. Without the composite keys, you’re likely to run into one-to-many relationships. Additionally, you can use them as join keys for VLOOKUP.

Your composite keys will include the campaigns’ date, source, medium, and campaign (which means campaign name in this case). It’ll look something like this.

Next, use VLOOKUP to join two tables. For example, the formula for combining transaction data with the marketing table is:

VLOOKUP($A4,$A$22:$J$33,6,0)

IF + REGEXPMATCH

The first step is to remap the campaign name to new values with an IF function (columns F and N).

That new cleaned-up name is then used as a join-key to generate the metrics table on the right side of the sheet, where metrics from two sources are aggregated together where the previously remapped campaign name matches.

The function we’re looking at next is a nested function — IF + REGEXPMATCH, where

• IF starts a conditional evaluation.
• REGEXPMATCH checks the target for a text match

Let’s take a look at the table below. As you can see, it has different naming conventions, for example, ‘Google Data Studio’ and ‘googledatastudio’, or ‘Enterprise’ and ‘enterprise’. 

You can put all your Google Data Studio campaigns in one basket and Enterprise campaigns in one basket using this formula

=IF(

REGEXMATCH(A7,"Data Studio|datastudio"),"Data Studio Campaigns",

IF(

REGEXMATCH(A7,"Enterprise|enterprise"),"Enterprise campaigns"

)

In simpler terms, your function searches in column A7 for ‘Data Studio’ or ‘datastudio’ and returns ‘Data Studio Campaigns’. If there is no such value, it will search for ‘Enterprise’ or ‘enterprise’ and return ‘Enterprise campaigns’. 

You can remap campaign names from different sources and use them as your join key.

Conditional aggregation

In Google Sheets, you can use different aggregation functions to summarize your data — calculating the sum, average, or counting the number of data points. But, in reality, you may not want to aggregate all your data. In that case, you can use conditional aggregation to specify which data you want to aggregate.

Conditional aggregation is a function that tells Google to perform data aggregation over a set of data when it meets certain criteria. We’ll take a look at some common conditional aggregation functions.

The SUMIF function tells Google to calculate the sum of the data that meets a predefined condition in a range. The syntax for the SUMIF function is:

SUMIF (range, criterion, [sum_range])

• range: you want to specify the data range you want to apply the condition to.
• criterion: you should specify the condition that defines which cells will be summed.
• sum_range: you should specify the range to be summed if different from ‘range’. This is optional.

Take the table below as an example. Let’s say you want to calculate the impressions from the US. You can do so by using SUMIF (B3:J12, “US”, D3:D12).

The AVERAGEIF function returns the average value of data that meets certain criteria in a range. The syntax for the AVERAGEIF function is:

The AVERAGEIF function returns the average value of data that meets certain criteria in a range. The syntax for the AVERAGEIF function is:

AVERAGEIF (criteria_range, criterion, [average_range])

• criteria_range: you should choose the data range you want to apply the condition to.
• criterion: specify the condition that defines which cells will be averaged.
• average_range: you should specify the range to be averaged if different from ‘criteria_range’. This is optional.

For example, if you want to calculate the average cost from the US, you can use AVERAGEIF(B3:J12, “US”, E3:E12).

Similarly, the COUNTIF function performs a conditional count over your data. The syntax for COUNTIF is:

COUNTIF (range, criterion)

• range: the range you want to count
• criterion: the condition you want to apply

For example, you want to count how many countries have CPC greater than 1. You can do so by using COUNTIF(H3:H12, “>1”)

An easier way to blend data in Google Sheets or any destination: Using the Supermetrics Marketing Intelligence Cloud

The Supermetrics Marketing Intelligence Cloud is a centralized platform that allows you to consolidate, transform, and move data to any reporting and analytics destination. It’s also easier and quicker way to blend your data.

Data blending in the Supermetrics Marketing Intelligence Cloud

Whether you’re an analyst looking for a more effective way to blend your data or a non-technical marketer who isn’t familiar with all join logic, you can easily blend your data and bring it to Looker Studio. With the Supermetrics Marketing Intelligence Cloud, you can:

• Blend more than 150 data sources—including paid, social media, web analytics, etc.
• Review your blended fields so you can verify how they look before using them in your report
• Modify the blended sources and configure the field mapping to fit your needs
• Use the created blends in all your reporting tools

Additional resources:  Learn 7 ways to transform your data using Supermetrics.

Getting started with data blending in the Supermetrics Marketing Intelligence Cloud

Here’s how you can use the data blending feature in the Supermetrics.

1. Log in to Supermetrics.
2. Choose ‘Transform’ → ‘Data blending’ on the left side of the screen.
3. Choose ‘Create new blend’.
4. Select the data sources you want to combine, then click ‘Continue to configuration’ and follow the instructions to configure the data sources.
5. After that, name your blended source, then click ‘Create blend’.

You’ll see your blended fields.

After that, your blended source will be available in your analytics and reporting destination. Let’s say you’re using Google Sheets for reporting.

1. Click ‘Back to blend list’.
2. Select ‘Use your blends in destinations’.
3. Once the window opens, choose ‘Google Sheets’ → ‘Go to connector’.
4. Follow the instructions to authenticate your data sources.

After that, you can easily build reports with your blended sources. To learn more about how data blending works, check out our support center.

Watch how to do data blending using Supermetrics

Over to you

To wrap it up, Google Sheets is a powerful tool for blending data from multiple marketing sources into one cohesive view. With the right functions and tools like Supermetrics, you can effortlessly merge data and create insightful reports. This streamlined approach helps you stay organized and make smarter marketing decisions faster.

If you’d like to move cross-channel data automatically to Google Sheets, start a 14-day free trial of Supermetrics for Google Sheets. If you want to go beyond moving data and transform your data, check out Supermetrics Marketing Intelligence Cloud.