In the aftermath of May 2020 Google algorithm update, we’ve seen a lot of impact across the board. Apparently in some industries this update was the true carnage, not the COVID19 pandemic.
I’ve made a comment in the Moz community about what I’m seeing in the sites that I admin, and there have been questions as to how to get to this interesting kind of information.
Google Search Console limits your ranked queries at 1000.
Now, I really like this data. So, I have developed the tooling to streamline my workflow. Right now all it takes for me to see rank size development and new or lost queries, is a one-liner:
$ stats r example.com 7 -m
And we get a cute report with a fuchsia pink chart. Yay.
I’ll give you the POC, though, which is infinitely more hassle but does the job.
Before taking the legwork of developing the tool, I’ve been using a Google Sheets add-on in a proof-of-concept to see if I should really bother.
The Google Sheets add-on is freely available as Search Analytics for Sheets. The data you get is perfectly fine, you just need to do a lot more manual digging to get it.
Workflow sketched right below.
1. Make a new Google spreadsheet with the add-on
Do that from a Google account that has full access to the domain you want to audit.
The homepage of Search Analytics for Sheets describes how to install and enable it. Your add-on will be available from a side bar, like so:
2. Create a second sheet for your 30-day data
Make a new sheet in your spreadsheet that you will use to dump your 30-day data for a specific domain.
Let’s say we have a domain that internally goes by the ID ‘alp’. We’ll make a sheet called ‘alp last 30’, and this is where we’ll point our 30-day data for ‘alp’ whenever we’ll pulling them from the API.
Which is done like this: Select the domain, pick last 30 days’ data range (I picked last month which is not always 30 days, I know I know), have it grouped into rows by
query and point it into your 30-day sheet.
3. Count your ranked queries
The only thing you need to do now to get the number of ranked queries over your 30-day period is to count all non-empty rows in ‘alp last 30’.
The best way to do that is from your first sheet, where you’ll have a date in column A and the count in column B:
4. Repeat to get rolling data!
To get the idea of how the number of your ranked queries changed, you’ll need to go back in time and pull rolling data for over a few weeks back.
In my POC, I dragged the right column formula over all the way down. Each time I was pulling new data set, I’d overwrite the current keyword count cell with the plain value so that it wouldn’t get overwritten with future data pulls.
This way you can just plug these two columns into a chart which will refresh every time you fill in a new date in your A-column.
! Remember though that you are overwriting your query data every time.
Anyway, have fun.