How to get Search Query performance data for each URL with Google Data Studio
Years ago, Google decided to no longer provide metrics for your landing pages at 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 been become quite useless.
Then, the larger online software suites like SEMrush jump 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, there is now a work around, that not only provides you traditional page level query data like impressions, clicks & CTR but also the average ranking position.
The following steps show you how this works, for free.
Create a Landing Page by Query table in Google Data Studio
First of all, 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 report sorted by URL Clicks.
Create a Search Query by Average Position table
Next, create another table. This time, use the Search Console Site Impression as 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, next steps are to download the data from these tables. For this, make sure you switch back to the View mode.
Next to the table on its top right, you will see 3 dots. When you click on these, a menu appears that gives you the option to download the table data as Excel file or export 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
Next thing I am going to do is to match the data from the table with 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 query 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 landing page level!
Brush up on your Google Analytics right here – on our Indago blog, and by following us on LinkedIn today!
