How to merge data in Looker Studio with practical examples

Consolidate data in Looker Studio (formerly Google Data Studio) is a powerful technique that allows you to combine data from multiple sources into a single report or visualization.

You can create custom charts and reports that provide a comprehensive view of your data, gathering insights from multiple data sources.

This technique can be especially useful for SEO professionals and digital marketers when you need to compare data from different sources.

How SEO experts can use data mashup

Because Google Search Console (GSC) and Google Analytics (GA) are two different data platforms, you cannot see social media traffic to articles and average ad position at the same time.

It may be particularly important to analyze whether there is a correlation between social popularity and ranking standing in search. With Looker Studio Building, you can now generate these correlation reports.

Blending data about website traffic, bounce rate, and time on site with keyword ranking data can help SEO professionals better understand how website performance affects search engine rankings.

How PPC Marketers Can Use Data Mashup

PPC marketers can compare data across multiple advertising platforms such as Google Ads, Facebook Ads, and LinkedIn Ads.

By blending data from these platforms, marketers can compare metrics such as cost-per-click (CPC), conversion rates, and ROI to determine which platform is the best performer.

Also, they can combine data from multiple campaigns, allowing them to get a more comprehensive view of their overall campaign performance and identify patterns or trends that may not be visible in individual campaigns.

Now let’s dive in and learn with use case examples.

How to mix Google Analytics 4 and Google Search Console data

We will create a combined data source to see if social media traffic and Google Discover visibility are correlated.

This would be an interesting experiment, especially considering that Gary Ellis recently said that social media may play an important role in improving indexability.

To combine data from two sources in Looker Studio, we’ll need to add both sources to our project.

If you haven’t already added the sources, let’s learn how to do it step by step. If you already know how to do this, you can skip and continue below.

How to add Google Analytics 4 to Looker Studio

go to resources > Manages Added data sources.

In the pop-up dialog box, click Add the data source Hyperlink and select your site in Google Analytics 4 (GA4).

How to add Search Console data to Looker Studio

go to resources > Manage added data sources Same as in the previous step and search for “Search Console” in the popup dialog to find a connector.

He chooses URL impressions > explore To add Google Discover data to Looker Studio.

Now, let’s add spreadsheets to our dashboard from each source.

After all these steps, you will see the data from each source, but you will notice that the GSC has the full URL, while the GA4 table has only the path.

We have to fetch both data in the same format and assign the same ID to the dimension field so that we can mix them.

It is important to ensure that the identifiers (or names) and field types you want to mix are consistent across all data sources.

(Looker Studio also matches the field name, but I highly recommend using the same field IDs as well.)

In general, the fastest The way to merge data is to select both tables, right-click on them, and choose Merge Data from File menu.

But if you try it with different sources, you’ll notice that the scrambled data doesn’t make sense, as shown below.

In some cases this may work, for example, when mixing data from the same source – but when mixing data from different sources, we need to create a new modified field “Page path”, which will have the same format and ID.

Let’s create a Page Path in the GSC source as a new field that uses the REGEXP_REPLACE syntax to remove the host from the URL.

The key point here is to set the field ID ‘path’, which will be the same as when it was created in GA4.

Thus, Looker Studio is able to match and merge dimensions.

Do the same with the GA4 source and create a “Page Path” field with the “Path” ID.

Now in the tables, we’ve added a surrogate dimension with the newly created field “Page-Path” (you can name it anything to distinguish them easily, the key is for the “Field-ID” to be the same).

Now your data is ready to be merged using Merge Data from the right-click dialog shown above. But before that, we want to make sure that we only have social traffic from GA4. Therefore, we need to apply a filter to the GA4 table by following these steps:

Add filter > create filter, And in the pop-up dialog, set the name of the filter and the condition Medium contains “Social”.

Now you can mix data and get social traffic and Google Discover impressions side by side.

Below is a simplified diagram showing what blending means.

But what if we also wanted to see how impressions and social traffic evolve over time?

For that, we need to edit the blended data source and drag it into the list of dimensions and also a file date area.

(In other cases, you may need to repeat the step to convert the dimensions to the same format and ID with the date field as well. But in this case, since they are already in the same format and have the same name, it will work, since Looker Studio also matches using the field name.)

Now you can choose a file time series Graph type to see how Google Social impressions and traffic stack up against each other.

Since impressions are much higher than social media traffic, it is useful to choose a logarithmic scale for better visualization.

After analyzing the data, it is clear that there is a strong correlation between Google Discover impressions and social traffic. The ups and downs of impressions align very closely with social traffic patterns, suggesting that more social media traffic results in higher visibility in Google Discover.

(Please always remember that correlation is not causation.)

Google Discover’s ranking algorithm is very different from Google Search.

Now, you may be asking if there is a correlation between ranking and social traffic. Let’s explore that too.

All the steps are the same with the difference being that you have to add the GSC web spreadsheet and include the average position as a metric.

After creating a new field, Page Path, with the same field id and blend path, we see that there is no correlation between social traffic and ranking position in the search engine results page (SERP).

You can see that the ranking position does not change while the social traffic goes up and down. Thus, we can conclude that ranking position does not correlate with social media traffic.

Conclusion

With these examples, you can see how many useful data insights you can gain by combining data from different data sources.

Many of you have access to Ahrefs and Semrush over any other SEO tool, and you can try combining backlink data with Google Analytics referral traffic data to understand the impact of backlinks on your website traffic.

The basic principle here is to create dimensions in two sets of data using the same field IDs, which will be used in the blend.

More resources:

Featured image: Andrey_Popov/Shutterstock