This Data Studio template helps to merge Google Search Console and Google Analytics data to be able to quickly prioritize pages and keywords for SEO based on their profitability, and not just the number of clicks. It is available in two versions: for HTTPS websites and for HTTP websites.

 

Table of contents

 

 

How could this be done before

 

As previously it was possible to solve this problem

  • It was necessary to upload data from the Google Search Console to Google Sheets directly or using this Chrome extension.
  • In the other tab of the table, upload the necessary data from Google Analytics directly or using Google Reporting API.
  • Merge data in a table using the VLOOKUP formula.
  • Add a couple of formulas and run them across all pages and keywords.
  • Do it every time again for a new period, since the data is static.

An experienced user can do this in 5-10 minutes for one site. But if you need to do this every week, month, and for more than one site, then the time spent on such routine operations becomes noticeable.

How can this problem be solved now

Now you don’t have to do anything. In 2 clicks you are available all the necessary data that is updated in real-time. But the report has certain limitations.

 

Who will benefit from the template

 

The report will be useful if several conditions are met.

1. Google organic search is the main source of the traffic to the site.

In the report, I display data on sessions, conversions, and revenue based on the total traffic on the site. If in addition to organic traffic from Google and direct traffic (direct / none), you have a lot of traffic from other sources and all sources of conversion metrics and income are very different, then the forecast metrics will be seriously distorted.

I tried to set up a filter in the report to display data on organic traffic only, but in this case, it breaks.

On the other hand, if the conversion, income, average order value you have for all sources are similar, then the report will be useful.

2. You have correctly configured e-commerce and / or tracking goals in Google Analytics.

As practice shows, few people have set up e-commerce. But if you have it configured, then check that:

  • It was included with you exactly in the view with which you always work.
  • You have non-zero metric data: Transactions, Revenue, Average Order Value, Ecommerce Conversion Rate.

If you haven’t configured e-commerce tracking, you can use tracking goals. In this case, it is important that:

  • In one view, you had only goals of one type that directly relate to the monetization of the site: filling out the purchase form, registering, clicking on the affiliate link, clicking on the Buy button, visiting the payment confirmation page. Very often in one view, they combine different types of goals. For example buying, spending on the site for more than 3 minutes, viewing more than 5 pages, subscribing to email newsletters.
  • For each goal, you conveyed the value of the goal. You can set it for each goal with your hands in Google Analytics or automatically transfer it as an Event Value variable if you configure all goals based on events.

 

google analytics goal value

 

3. You already have accumulated data on traffic, sales, revenue, which allow you to build forecasts for potential revenue from the page, keyword.

In the report, we calculate the potential revenue from the page, keyword, based on the data on conversion, and the average order value for the past period.

If you just set up goal tracking or e-commerce, you will need to wait sometime for the data to collect. What exactly is this period, depends heavily on your weekly traffic and conversions.

 

How to merge Google Search Console and Google Analytics data

 

The problem that I encountered when I took up this report is as follows. When I created the GSC + GA dataset myself, I got invalid data. Data from Google Analytics and from this dataset were 2-3 times different.

I tried to find a pattern – do the differences across all pages have some sort of common index of differences. But the scatter turned out to be chaotic.

That is, the option when you form a table based on one data source and then click on Blend data to combine it with another did not work for me the way it should work.

 

search console and google analytics blend data

 

But another option worked. I describe step by step what to do if you want to generate a similar report and encounter such a problem.

1. Connect Google Analytics as a data source and add a new field there.

connect google analytics data

2. Write down the field formula and save it.

write formula for data studio custom field

The formula is in text form.

concat("https://",Hostname,Landing Page)

This formula is needed because the Landing Page parameter is presented differently in the Google Search Console and Google Analytics.

If your site is hosted on the HTTP protocol, then change the formula as well. It’s also important that the view in Google Analytics show data for only one domain. If you set up subdomain tracking within the same view, you won’t be able to combine data with the Google Search Console within that view.

3. Create two separate tables per page from two different sources.

The first table should include all the metrics with Google Analytics that you will display in it or use in calculating your calculated metrics. And surely such a table should include the new parameter that you created in the last step.

The second table should include all metrics from the Google Search Console (URL Impression dataset) and the Landing Page parameter is required.

Select these two tables, click the right button, and click Blend data.

 

blend data from search console and google analytics

 

Google Data Studio itself will choose the parameter by which the data is combined and form a new table, where metrics from both datasets will be opposite the page. And most importantly – they will be valid.

Next, connect the automatically created dataset to all filters, create calculated metrics, and play with the data as you like. But here there is one more nuance.

 

How the report works

 

Important nuance

The caveat is that you need 2 separate combined datasets to use the report to the maximum. One dataset includes Query parameter, and the second does not.

All report elements, except the first table, are connected to the dataset with a keyword so that you can filter data by keywords. If you filter data by keyword, the filter will apply to all data except the first table.

Therefore, those numbers that you see on the cards will coincide with the data in the second table and will differ in a smaller direction from the data in the first.

 

data studio scorecards

 

The reason why the data is different if we include Query in the dataset is described in the Google Help screenshot below.

query filter in data studio

 

Now a little more about how the report is arranged.

 

Two pages

The first page is designed to work with data based on tracking goals, the second – based on working e-commerce. When creating our own metrics, we use different metrics with Google Analytics. Some for the Goals, others for the Transactions. Therefore, such a division into pages is necessary.

 

goal and transaction reports

 

Three groups of metrics

The report has 3 groups of metrics: Google Analytics metrics, Google Search Console metrics, and calculated metrics, which we form based on the first two.

I specifically did not change the labels of Google Analytics and Google Search Console metrics in tables and cards, so you can easily compare their values with the original source.

Click on the picture to see the inscriptions.

 

google data studio custom metrics

 

Calculated metric formulas

Formulas for page view.

Lost clicks

(Impressions * 0.2) – Url Clicks

Lost conversions

((Impressions * 0.2) – Url Clicks) * Goal Conversion Rate

Lost revenue

(((Impressions * 0.2) – Url Clicks) * Goal Conversion Rate) * (Goal Value / Goal Completions)

Formulas for transaction view.

Lost clicks

(Impressions * 0.2) – Url Clicks

Lost transactions

((Impressions * 0.2) – Url Clicks) * Ecommerce Conversion Rate

Lost revenue

((Impressions * 0.2) – Url Clicks) * Ecommerce Conversion Rate * Avg. Order Value

 

How filtering works

By default, for all tables and data cards, I added a filter that excludes data from quick links within the same URL. That is, on pages that contain a hash – #.

 

filters in google data studio

 

Filters for the pages and keywords that are placed at the top can be used. The only thing, as I said, when filtering by keywords, the first table will not change in any way.

 

How to work with the template

 

Basic

You just save yourself a link to the report and use it based on your data without changing anything in the structure. I keep all reports on my site, under the heading of Google Data Studio templates. If you need to quickly find the report you want, you can return to this page, everything is well structured there.

 

Advanced

You copy the report to yourself and then you can experiment with it as you like.

Click Сopy report in the upper right. Connect the Google Search Console and Google Analytics on the same site.

 

copy data studio report

 

Also, do not forget that you can easily download the generated tables in Excel or Google Sheets and prescribe for yourself opposite each page, a keyword, what actions should be done on it to increase positions.

 

export data from table in data studio

 

Frequently asked questions

 

1. Why do I have slightly different data in Google Analytics and in the report?

You really may have a small margin of error in the data. But this is not related to any problems of data aggregation when combining GA and GSC.

For the test, I specifically made 2 tables with one Google Analytics source. In the first table, the default Landing Page parameter was displayed, and in the second, the parameter is already based on the formula where the domain is visible.

Although the data should be identical, there really is a minimal error. The reason is unknown to me. But with such an error, the report does not lose value. If you have a much larger error, write to me about this issue.

 

data differ in google analytics and data studio

 

2. What if I don’t have conversion and revenue data?

The first is to configure them as quickly as possible. Secondly, while this data is being collected, you can add your conversion and average order value ​​to the formulas. They can be found somewhere from competitors, in market research, or predicted based on your experience.

 

3. What should I do if there is data on conversion and revenue, but only in a view with different types of goals?

Yes, the problem here is that if you create a new view, where you reconfigure goals of only one type, then all past data on goals in it will not be displayed.

The solution exists. You can replace metrics common to all goals in the formula with metrics for one specific goal. Then the formulas may look like this. For example, calculating lost conversions and revenue only based on goal number 10.

Lost conversions

((Impressions * 0.2) – Url Clicks) * Goal 10 Conversion Rate

Lost revenue

(((Impressions * 0.2) – Url Clicks) * Goal 10 Conversion Rate) * (Goal 10 Value / Goal 10 Completions)

 

3. Why are sessions displayed in the report and not users?

Because Goal Conversion Rate and Ecommerce Conversion Rate conversion metrics are calculated based on sessions, not users. Therefore, it is appropriate to derive precisely this metric.

 

4. Is it possible to extrapolate page conversion to keyword conversion?

Yes, keyword conversions on a single page can vary greatly. Especially problematic if you have a strong page and it is ranked by high-frequency irrelevant keywords that are unlikely to bring sales. But there’s nowhere to get keyword conversion data from organic search.

Therefore, we can either extrapolate in this way or conduct companies in Google Ads in parallel and take keywords conversions from there. But this will also not be the best option since traffic in organic and commerce can be very different.

 

5. Why is there no data on the position of the keyword?

Because in Data Studio you cannot combine 3 data sources if they all do not have one common parameter. You cannot combine GA and GSC (URL Impression), and then combine this common dataset with GSC (Site Impression), where there is data on the positions.