Combine GA4 and GSC Data in Looker Studio

This post will be helpful if you’re looking to:

• Create a content performance report – Identify winning content and opportunities for improvement for Google search.
• Merge Google Analytics 4 and Google Search Console Data
• Create a looker studio report with combined metrics from GA4 and GSC.
• Separate your blog performance from the rest of your website landing pages.
• Identify engagement on your pages that are ranking well/poor on organic search.

I created this detailed guide to combine GA4 and GSC data because I had to report on a few metrics to stakeholders on new blog pages that I recently published. The idea was to see if our topic selection, content quality, and intent were right for our target audience.

With hours of research on how to do this, the closest I could find was Looker studio templates that were paid. But even that wasn’t promising in terms of what I was looking for.

So here it goes:

Introduction

The guide is structured to have four sections:

1. Basics on what we’re trying to do here along with the ‘why’ and benefits. This would help you sell the importance of this report to your stakeholders.
2. Instructions on how to blend Google Analytics [GA4] and Google Search Console [GSC] data in Looker Studio.
3. Some tips on how to use the template, analyze the data, derive insights, and build action items based on your findings.
4. Learnings, noted issues, etc.

For this activity, you need access to a Google Analytics property and Google search console access of the same domain.

Content Performance on Organic Search: Why does it matter?

When you’ve published new blog pages, you need to know how they are performing on search. You can use some metrics from the Google search console such as impressions and clicks for queries that are ranking for that page or do a competitor analysis.

But if you want to optimize a page, those metrics might not be enough. You would also need to know how people are engaging with the page. And how much are they liking it?

Metrics such as impressions and clicks are second-order metrics here which tells you that Google Search likes or does not like your page. But if a visitor engages with it enough is not possible with these.

You then turn to Google Analytics or a session recording app for help. From Google Analytics, you learn to see landing pages or pages and screen reports and get user engagement metrics from there.

But then:

Are you not analyzing your new content performance solely based on their search engine rankings and not based on visitor engagement?

You need to:

• Have a zoomed-out view of all of your pages and analyze how they are doing SEO-wise and engagement-wise.
• Track user engagement along with ranking data to derive insights for page optimizations.

Objective: Tracking user engagement along with URL impressions, clicks, avg position

An example of insight can be that you find some pages that have a high engagement rate for people reading the article, but you don’t have enough position on Google search for it to rank organically. You can then create a plan for promoting that high-engagement piece across Google search and other channels.

Out of 100s of website pages, you can only get these types of pages from the report like the one we’re going to create in Looker studio below.

Enters Looker studio

Looker Studio helps you visualize your marketing data and create dashboards. You can connect your Google Analytics property and Search console property as a data source in Looker Data Studio to create reports that matter.

With this, you can merge two data sets (GA4 and GSC) which is called blending in Data Studio.

With blended data GA4 and GSC data, you can create a very simple table that shows you Google search performance metrics along with user engagement metrics from GA4.

According to your use case, you can also add other metrics from GA4 such as e-commerce add to cart or purchases from a page, number of events (if you’re tracking different events for a similar set of pages), DAU/WAU/MAU if you’re tracking app pages, Publisher Ad clicks/impressions if you’ve connected Adsense/ Admob account to your GA4 account.

There are two alternatives to this approach:

• You can do manual reporting in Excel sheets. I have done that and honestly hated it for every week I had to put in the numbers manually. Manual data entry work for that report increased with each new blog page added or updated.
• You can create exploration reports in GA4 with a connected Google search console property. I personally like this option until I have to share this report with the stakeholders. You do not want to ask them to look at the 10000 and 1 nth reports with them figuring out if they have access and the steps to access that particular report inside GA4. Though you have better quality data, there are fewer customization options in the exploration section of GA4 as compared to Looker Studio.

How to Combine GA4 and GSC Data

Know how to combine Google Analytics and Google Search Console data in Looker Studio.

The secret here is understanding that you need to have a base (a key) to merge these datasets. Looker Studio uses data from database tables that have a field name (key) and a field ID (Key ID). You need to make both of these uniforms to be able to merge the two using a common Key.

Connect GA4 and GSC as data sources in Looker studio

If you haven’t already, connect your Looker studio to your Google Analytics property and Google Search Console property.

Connect Google search console to Looker studio

To connect GSC data to Looker Studio, go to the “Data sources” tab. Select “Connect new data source” and then, select Search Console from the list of Google connectors.

You’ll see your search console property in the right sidebar. Now select that property and you’ll see two options – “Site impressions” and “Url impressions”.

While adding the Search console property, select “Url impressions” as the dataset. This dataset is most useful since it contains all the URLs and Query dimensions along with metrics such as clicks, impressions, position, etc.

The “Site impressions” dataset doesn’t have queries attached to the URLs. You need queries with URLs set to drill down and identify queries ranking for that page.

Connect Google Analytics to Looker Studio

To connect GA4 data to Looker Studio, go to the “data sources” tab. Since you’ve already added GSC data there, you won’t see the “Connect new data source” option.

Instead, select “Create” and select “Data source” there and then, select Google Analytics from the list of Google connectors.

After you’ve selected Google Analytics, you’ll see the same GA4 property that you have access to. If you have a GA3 property attached, you’ll see that as well. Select the relevant GA4 property and click on “Connect”.

Understanding GA4 and GSC data in Looker studio

Now that we have connected our data sources, we need to blend these two data sources. To do it, you need to understand how data sources work.

Each data source has a field and type of data in it. These fields are further divided into dimensions and metrics. For example, dimensions can be landing page (text type data), and metrics can be landing page clicks – URL impressions (Number type data).

If you explore the GSC data table in Looker Studio, you’ll find that the landing page dimension has full URLs. Example: https://www.example.com/url-slug. See example below:

If you explore the GA4 data table in Looker Studio, you’ll find that the landing page dimension or page path dimension only has the page path ie. the /url-slug instead of the full URL with https, domains, or subdomains.

Now this is a problem since if you want to merge the two data, you need a common dimension (or field) for them to be matched.

Ie. It should either be only /url-slug in both GSC and GA4 data or it should only be https://www.example.com/url-slug in both.

There is a solution to this – Custom fields in data. Fortunately, Looker Studio allows us to use formulas to create custom fields. We’ll create our dimension in each of the tables (GA4 and GSC) to regularise it.

in GSC data, rather than having the full URL we’ll extract the URL slug from every URL in the GSC dataset and use it as the common dimension.

For GA4, since we already have page paths there as a dimension, we don’t alter a lot there. But we’ll still create a new field in our GA4 dataset. Because along with the field, there is a field ID. We need to create the same field IDs in both the GA4 and GSC datasets to make it work.

Create custom-calculated fields in Looker Studio

Create a custom field in your GSC dataset

To create a custom field in your GSC dataset, go to data sources. From data sources, double-click on your GSC data source. You will see the fields available in your dataset along with the field type.

From your dataset, to go “Add a field” and then select “Add calculated field”

After hitting add calculated field, you’ll see that you can give a field name, field ID, and the formula to define your field.

Name This field as “Landing page path” and set the field ID as “path”

We know that we want to use this as a dimension, where we want to use “Landing page” as the existing dimension from this dataset and replace it with the URL slug from it.

For this purpose, we’ll use the “Regex_replace()” formula to use the landing page as the input and replace it with the page path from that landing page.

REGEXP_REPLACE(Landing Page, "https?://[^/]+", "")

Your field details should look like this:

After you’re done, hit on save.

Create a custom field in your GA4 dataset

For creating a custom field in your GA4 dataset in Looker Studio, do similar steps.

Go to “add a field”, go to “Add calculated field” and name your field name as “Landing page path” and field ID “path”

For the formula here, since we already have a page path dimension, we can just use that here.

Hit save and now you’re ready to merge GA4 and GSC data!

Creating Looker Studio report with Merged GA4 & GSC data

Blending GA4 and GSC data in Looker Studio

Create a blank report in Looker Studio and use your existing data sources (GA4 and GSC).

After you’ve added both GA4 and GSC as data sources in your report, you’ll see the calculated field (Landing page path) that you created earlier in both sources.

To blend the data, insert a table in your report. In the right sidebar, you’ll be able to see the data sources. From there, select “Blend data”.

Now after you’ve hit “Blend data”, you’ll see an existing table on the left. You’ll see an option to join another table on the right. Select “Join another table” and from the list that appears, select the data sources which is not on the left table. (GSC data in my case here).

Now that you’ve both of your tables containing your GA4 and GSC datasets, it should look like this:

But before configuring join, we also need to select the metrics and dimensions that we’ll need for this dataset.

Looker Studio doesn’t take the whole dataset by default. It will only use the metrics and dimensions that we select in each of our tables.

Now for this, we know that we want to analyze content performance. For analyzing content performance, you’ll need these metrics and dimensions:

After selecting your dimensions and metrics, you can verify these fields on the right-hand side of the screen.

we need to configure the join conditions to match the custom calculated fields that we created earlier.

For it, select Configure Join.

After you select configure join, you’ll see a few options for the join operator. These are pretty self-explanatory. To examine all our pages be it in GSC or from GA4, I’ll suggest that you use the “Full outer” Join.

In the Join conditions, you need to tell Looker Studio how these two tables are connected. We know that in both of these tables (GA4 and GSC), we have a common field with a common field ID (Landing page path with field ID as ‘path’). We can let Looker Studio know that it can use those fields to join the two tables so all other data related to these tables are also joined.

After you have configured your table joins, click on save and you’ll be taken back to your report. Now you’ll see your blended data in the data source under the setup tab.

Congratulations on reaching here. You’ll successfully blend GA4 and GSC data in Looker Studio. Now you only need to set up the table and possibly create a dashboard to analyse content performance.

Select the dimension as the Landing page path in your table and select the metrics that you need to analyze content performance.

Analyse data, Derive insights and Improve your content performance

Stay tuned for this section.

I’ll show how to create a dashboard that will help you analyze this merged data to analyze content performance and derive insights.

Connect with me on Linkedin to get this template sooner rather than later!

Noted issues

• The landing page is not a reliable dimension. There are not set values or “Null” values.
• The list will depend on your join condition. Full join would mean you might have pages that only have GSC data and no GA4 data. You would also have pages that would only have GA4 data but not GSC data. This ensures you’re not missing out on pages that might be important for your business.
• The Regex Formula used to standardize page paths doesn’t make normalized URLs that have subdirectories in them. for example: https://www.domain.com/blog/url-slug will change to blog/url-slug in the search console dataset which will not match with the page path of url-slug in Google Analytics.

Learnings

• If you’ve already connected your Google search console to your Google Analytics 4 account, you’d think that your GA4 dataset should already have those Google search metrics. I’ve tried to use dimensions from GA4 (Landing pages) and metrics such as Organic Google Search impressions and organic Google Search clicks together. But sadly it doesn’t work in Looker Studio.

• You need to know the difference between a metric and dimensions. If you put, for example, Device (which is a dimension) as a metric, you’ll see numbers when you put device as a control and filter your results. This is true for other dimensions such as landing pages, page paths, etc.

Inquisitive?

Read this Medium article to learn how to blend GA4 and Universal anlytics data.