Creating a Google Search Console rank tracker in sheets

Sam Underwood
Sam Underwood
April 29, 2020
9 mins read

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.

rank tracker grossing report

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.

rank tracker daily trend tables

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.

rank tracker daily trend chart

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.

ranking distrubtion chart

I've also included a 100% stacked version of that chart to make it a bit easier to spot changes between different position groups:

ranking distrubtion chart stacked

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.

ProsCons
You can track as many keywords as you like with no additional costLimited by the Google Sheets cell count limit
It is almost free, excluding Supermetrics subscriptionThere are no competitor insights
16 months of historical data for a new keyword setNo 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'.

sheets 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'

sheets supermetrics duplicate 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.

gsc rank tracker config

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.

supermetrics daily refresh

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")

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.

If this is complete gibberish to you and you've never written SQL, I'd definitely recommend starting by getting used to the QUERY function. Here is a great guide to get you going.

Once you've learnt it, it will completely change how you handle data analysis within spreadsheets.

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"":"";""}]")

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.

Why you can't use the Supermetrics sidebar...

This hidden 'SupermetricsQueries' sheet is used by Supermetrics to store how it will query the API.

You'll see when you first use Supermetrics it automatically creates the hidden sheet and fills it in based upon the query you setup within the sidebar.

Because I've filled this in with a formula, if you use the sidebar and edit the query, it'll overwrite my formula, so avoid doing that.

Replicating within Data Studio

A little addition to this, I also replicated the reporting from the Google Sheet within Data Studio!

google search console rank tracker 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'
END

And use it as a secondary sort on the chart like below (note the 'Min' aggregation':

ranking distribution sorting

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.

linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram