How to blend data in Looker Studio with practical examples
Data merging 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 and bring together insights from multiple data sources.
This technique can be especially useful for SEO professionals and digital marketers when they need to compare data from different sources.
How SEO experts can use data blending
Because Google Search Console (GSC) and Google Analytics (GA) are different data platforms, you can’t see social media traffic to articles and average position at the same time.
It can be particularly important to analyze whether there is a correlation between social popularity and search ranking position. With Looker Studio authoring, you can now create such correlation reports.
Combining data on 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 Leverage Data Blending
PPC marketers can compare data across multiple ad platforms like Google Ads, Facebook Ads, and LinkedIn Ads.
By bringing together the data from these platforms, marketers can compare metrics like cost per click (CPC), conversion rates, and ROI to see which platform is performing best.
They can also combine data from multiple campaigns to get a more holistic view of their campaign’s overall performance and spot patterns or trends that may not be visible in individual campaigns.
Now let’s dive in and learn from use cases.
How to combine Google Analytics 4 and Google Search Console data
We will build a blended data source to find out if social media traffic and Google Discover visibility correlate.
This will be an interesting experiment, especially given that Gary Illyes recently said that social media could play an important role in improving indexability.
In order to combine data from two sources in Looker Studio, we need to add both sources to our project.
If you haven’t added the sources yet, here’s a step-by-step guide on how to do it. If you already know how to do this, you can skip it and continue below.
How to add Google Analytics 4 to Looker Studio
Go to resources > Administer Added data sources.
In the pop-up dialog box, click Add data source Click on the hyperlink and select your Google Analytics 4 (GA4) property.
How to add Search Console data to Looker Studio
Go to resources > Manage added data sources As in the previous step, search for “Search Console” in the pop-up dialog box to find a connector.
Choose URL Impressions > Discover to add Google Discover data in Looker Studio.
Now let’s add data tables from each source to our dashboard.
After all these steps you will see data from each source, but you will notice that GSC has a full URL while the GA4 spreadsheet only has the path.
We should put both data in the same format and set the same dimension field ID to be able to merge them.
It’s important to ensure that the IDs (or names) and types of the fields you want to merge are consistent across data sources.
(Looker Studio also matches by field name, but I strongly recommend using the same field IDs as well.)
Overall the fastest One way to merge data is to select both tables, right click on them and select “merge data” from the table Menu.
However, if you try different sources, you’ll find that the mixed data doesn’t make sense, as shown below.
In some cases this might work, for example when you are merging data from the same source. However, if you are merging data from different sources, we need to create a new field-adapted “Page Path” that has the same format and ID.
Let’s create a “Page Path” in the GSC source as a new field that uses the REGEXP_REPLACE formula to remove the host from the URL.
The key point here is to set the field ID “path” which will be the same when created in GA4.
Thus, Looker Studio is able to adjust and merge dimensions.
Do the same with the GA4 source and create a Page Path field with an ID of Path.
Now in the tables we have added the replacement dimension with the newly created field “Page Path” (you can give it any name to distinguish them more easily, the key is that the “Field ID” is the same).
Now your data is ready for merging by clicking “Merge Data” in the right-click dialog shown above. But before that, we want to make sure we only have social traffic from GA4. To do this we need to apply a filter to the GA4 table by following the steps below:
Add an filter > create a filter, In the pop-up dialog box, set the filter name and the condition that the media contains “social”.
Now you can combine data and view social traffic and Google Discover impressions side-by-side.
-
If you see “Null” values in the “Impressions” column for older URLs, it’s because GSC only has data available for newly published URLs.
As a result, data for older URLs may not show up in GSC, which can result in “null” values.
- This is common and does not necessarily indicate problems with your data or tracking.
Below is a simplified diagram showing what shuffling means.
But what if we also want to see how impressions and social traffic evolve over time?
To do this we need to edit the mixed data source and drag it into a dimension list as well as one Date Field.
(In other cases, you may need to repeat the step of converting dimensions to the same format and ID with the date field as well. However, in this case, since they are already in the same format and have the same name, it works because Looker Studio also uses the matches field names.)
Now you can choose that time sequences Chart type to see how Google Discover impressions and social traffic match up.
Since impressions are much higher than social media traffic, it’s helpful 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 ebb and flow of impressions correlates very closely with social media traffic patterns, suggesting that increased social media traffic translates to better 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 might be wondering if there is a connection between ranking and social traffic. Let’s explore that too.
All the steps are the same except that you should add the web data table from GSC and provide the average position as a metric.
After creating and blending a new field “Page Path” with the same Field ID “Path”, we see that there is no correlation between social traffic and ranking position on the search engine results page (SERP).
You can see that the ranking position doesn’t change as social traffic rises and falls. From this we can conclude that ranking position is not correlated with social media traffic.
Diploma
From these examples, you can see how many useful data insights you can get by combining data from different data sources.
Many of you have access to Ahrefs, SEMrush, or any other SEO tool, and you can try combining backlink data with Google Analytics referral traffic data to understand the impact of your backlinks on your website traffic.
The basic principle here is to create dimensions in two datasets with the same field IDs that will be used when merging.
More resources:
Featured image: Andrey_Popov/Shutterstock