How to get search query performance data for each URL with Google data studio

How to get search query performance data for each URL with Google data studio

Years ago, Google no longer provided metrics for your landing pages at the search query level. Before, you could easily see how well your landing pages were performing for search queries but now the keyword report in Google Analytics has become quite useless.

Written by

Darren Friel

Published

29 May 2019

Categories

Acquisition

Data

SEO

Then, the more prominent online software suites like SEMrush jumped in the gap Google left behind and started offering ‘non-provided’ solutions for a premium price.

Luckily, with the Search Console connector for Google Data Studio, a workaround provides you with traditional page-level query data like impressions, clicks & CTR and the average ranking position.

Create a Landing Page by Query table in Google Data Studio.

First, create a Landing Page table report in Google Data Studio based on Google Search Console. Make sure to select the URL impressions when you set up the Data Source (Image 1).

Once you have set up the connector and inserted a table onto your report page, select Landing Page & Query as Dimensions, Impressions, URL Clicks & URL Ctr as Metrics and sort by URL Clicks.

Using my brother-in-law’s website as an example – put together, it will look something as shown below (Image 2).


1. Search Console URL Impressions
2. Landing page table sorted by URL clicks

Next, create another table. This time, use the Search Console Site Impression as a data source to put together a table report that shows at least Query & Average Position.

Export to Google Sheets

Once you have put both tables together, the next step is to download the data from these tables. For this, make sure you switch back to the View mode.

You will see three dots next to the table on its top right. When you click on these, a menu lets you download the table data as an Excel file or export it to Google Sheets. For the report we are creating, it doesn’t matter which you choose. More important, though, is to make sure to select the data range you want to get the data for.

Match the data from both tables with a VLOOKUP

The next thing I am going to do is to match the data from the table with the average ranking for each query with the data from the landing page metrics table.

So go into the landing page data sheet and add a column called Average Ranking. Then, in the row with the first query (this would most of the time be row 2), add a vertical lookup that looks for the Average Position of the corresponding question in the sheet with the data we have downloaded from the average position per query Data Studio Report.

So, in the example below, I have looked up the Average Position of the query ‘Neutral Bay Plumbing’ in the sheet Average Position:

=VLOOKUP(B2,’Average Ranking’!$A$2:$B$113,2,FALSE)

Just drag down the formula, and you will have the average position of your landing pages for each query they had at least 1 impression for!

Congratulations, you have just recreated a report with search query data at the landing page level!

The dog and bone.
Subscribe and be the first to hear about news and events.

Written by

Darren Friel
The dog and bone.
Subscribe and be the first to hear about news and events.
View our last posts
AI robot tutor helping a student with homework, they are sitting on the couch at home and reading a book

Unlocking the Power of Generative AI: Google’s 10 Free Courses

Tahlia Reynolds - 6 min read
Close up of mobile displaying Chrome's incognito mode

Navigating Chrome's Block on Third-Party Cookies

Preet Singh - 9 min read
Purse with overflowing receipt

How to Grow Your Business Despite Consumer Uncertainty 

Tahlia Reynolds - 5 min read
Team members thinking together

Get in touch

Ready to get the ball rolling? Drop us a line.

First name*
Last name*
Email address*
Phone number*
Company*
Your message