I've been playing around with the GSC API recently investigating ways to make better use of the wealth of free data it provides.
As a result of my testing, I wanted to see if I could create a simple, low-cost rank tracker by using the average position data within Search Console and sending that information to Google Sheets.
There are multiple features of a rank tracker that I wanted to try and replicate, including:
- Ranking distribution trend
- Being able to enter the keywords you specifically want to track
- Keyword ranking trend charts
- 7/30/90/365 average position overview
Usually, I store my GSC data in BigQuery and then pull from there, but to make this something others could use I've recreated it using Supermetrics and put it in a free to use template.
If you want to see some pros and cons jump ahead here, to summarise though this definitely doesn't have all the features of a rank tracker, nor could it have when based upon GSC data.
It is certainly a useful addition though!
What does the report look like?
There are a few different things I've created in the Google Sheet. Here is a bit of an overview:
Grossing keyword overview
For the first report, I was inspired by the grossing overview found within Ahrefs.
This report gives you a great top-level view of trends over the last 7/30/90/365 days as well as a trends chart that shows you an overview of that keywords ranking history.
I also included some slicers just so you can filter for specific keywords or position groups.
Daily ranking trend tables
This report plots daily ranking data into a table with conditional formatting that allows you to quickly spot any particular ranking trends.
You can customise the to/from date. This report is surprisingly fast, I tested it with 4 months worth of data across 400 keywords and it loaded straight away.
I also included a 'Metric' dropdown.
By default, it plots ranking data in the table but you can also get it to plot either click or impression data.
Pretty useful!
Daily ranking trend charts
Alongside that, I made some trend charts that allow you to select up to 5 keywords and see the position/click trend.
Similar to the tabular view, there are date and metric filters as well as a dropdown to configure which keywords you want plotted on the chart.
Ranking distribution charts
You'll see this useful visualisation in most rank trackers, it gives you a great overview of your position spread across the keywords you upload.
I've also included a 100% stacked version of that chart to make it a bit easier to spot changes between different position groups:
Pros/cons of rank tracking via this sheet
I've just started using the sheet and I'm finding it useful, but there are some definitely some limitations if you're comparing it to your usual rank tracker.
| Pros | Cons |
|---|---|
| You can track as many keywords as you like with no additional cost | Limited by the Google Sheets cell count limit |
| It is almost free, excluding Supermetrics subscription | There are no competitor insights |
| 16 months of historical data for a new keyword set | No SERP feature data |
| There is a 2-day time lag on data | |
| Only really useful for higher volume terms on page 1/2 |
The two largest limitations I've found is that you don't receive SERP feature data and competitor insights, which is where rank trackers become incredibly useful.
How to set it up
You can find a link to the template below.
Once you're on it, follow the steps below to get started:
1. Making your copy
First, go to 'File > Make a Copy'.
Once you have a copy, you'll need to get Supermetrics to remake the sheet in a new account by going to 'Add-ons > Supermetrics > Duplicate this file for another account'
2. Set up the config and pull data
In your new copy of the copy in the 'Config' tab set the keywords you want to track and the profiles to pull the data from. You can add multiple variations here e.g. http and https if needed.
Set up your initial data pull in the config. You can backdate by up to 16 months but only do what you really need. I usually do the past 90 days.
Go to 'Add-ons > Supermetrics > Refresh all'.
Make sure not to ever make edits in the tools sidebar and just refresh sheets or things will break (explained why later).
Wait a while, if you entered lots of keywords or a long time-span this can take some time. It can sometimes seem like Supermetrics isn't loading the data, be patient, it should start loading.
3. Set up daily data refreshes
Once you've got your 90 days worth of data, now you want to set it up to refresh daily with new data.
Don't worry, doing this won't replace your old data but will instead append any new data.
To do this, first head back to the 'Config' and set it to just pull the past 1 day of data (this is automatically offset by the GSC data delay of 2 days).
Head to 'Add-ons > Supermetrics > Schedule refresh & emailing'. Set up a daily refresh early in the morning.
You're all done! You've now got a daily GSC rank tracker in sheets.
How it all works
If you really want to find out how I've made it work, I'd definitely recommend checking out the formulas I've used for each tab.
I won't go into too much detail here, but I'll run through the most complex parts.
QUERY functions everywhere
You'll see for most of the analysis of the keyword data I've utilised the Google Sheets QUERY function which allows you to write in a language similar to SQL within a Google Sheet.
Here is one example of a QUERY used for the filterable daily trends chart tab:
=IFERROR(
QUERY('GSC - Keyword/URL Data'!A:G,
"select A, AVG("&IF(C10="Rank","D","E")&")
where B is not null and A >= date '"&TEXT(DATEVALUE(C3),"yyyy-mm-dd")&"' and A <= date '"&TEXT(DATEVALUE(C4),"yyyy-mm-dd")&"' and (C = '"&C5&"' or C = '"&C6&"' or C = '"&C7&"' or C = '"&C8&"' or C = '"&C9&"')
group by A
pivot C
format A 'dd/mm/yy'")
,"No Data")Code language: PHP (php)
You can see I've added some inline cell references to make it so that the WHERE statements are based upon the input from the dropdowns.
JOIN and CONCATENATE
To make it so you can add a list of keywords into the sheet and pull data from the API just for those, I've used the below formula within the hidden 'SupermetricsQueries' sheet.
=CONCATENATE("[",IF(ISBLANK(Config!$D$5),,CONCATENATE("{""field"":""query"",""operator"":""=~"",""value"":""^",JOIN("$|^",FILTER(Config!$D$5:$D, NOT(Config!$D$5:$D= "") )),"$"",""combineToPrev"":"",""},")),"{""field"":""searchtype"",""operator"":""=="",""value"":""Web"",""combineToPrev"":"";""}]")Code language: PHP (php)You can read more about the JOIN function here.
What I'm doing with this formula is combining the list of keywords you add into the 'Config' sheet and separating them with some regex to make the API query exactly match the keywords in your list.
You can see a bit of regex in there mixed with some syntax used by Supermetrics to store your query information.
At the same time, the formula ignores any blanks in the column.
If you check other formulas within that hidden sheet, you'll see I've got something similar that allows you to query multiple GSC profiles at once depending on the list in the 'Config' sheet.
Replicating within Data Studio
A little addition to this, I also replicated the reporting from the Google Sheet within Data Studio!
The only thing missing is the grossing report, tabular data within Data Studio isn't quite as flexible as what you can do in Sheets.
To get this working grab the template below (don't worry about the missing data in the template):
Make a copy of it and when prompted to create a new data source, create a Google Sheets connector pointing towards your copy of the sheet template and use the 'GSC - Keyword/URL Data' tab.
You may also need to re-create the helper calculated field I made to sort the ranking distribution chart by position range, to do that create a calculated field containing this:
CASE
WHEN Position Range = '1 - 3' THEN '1'
WHEN Position Range = '4 - 6' THEN '2'
WHEN Position Range = '7 - 10' THEN '3'
WHEN Position Range = '11 - 20' THEN '4'
WHEN Position Range = '21+' THEN '5'
ENDCode language: PHP (php)And use it as a secondary sort on the chart like below (note the 'Min' aggregation':
Wrapping up
Hopefully, you've managed to set this up and make use of it. I'm finding it quite a handy little tool.
I think to take the concept further I'd probably try and bring some of the data analysis into Data Studio which is definitely a possibility and may speed things up for larger datasets.
It was a fun little experiment though, any questions feel free to comment below or tweet me at @SamUnderwoodUK.