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.
You’ll need to dig into the Google API data for the search console
Google Search Console limits your ranked queries at 1000.
- If your website ranks for more than that, the API is the only way for you to get a dataset that isn’t cut off at the ceiling, preventing you from seeing the exact changes you are looking to see.
- Even if your site is much smaller than that, go for the API. You still need the data in a format that is easy to work with.
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.
“K gimme the code?”
Eh, nope.
I’ll give you the POC, though, which is infinitely more hassle but does the job.
How to get your Google rank size development over time
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.
Like so:
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.
- If you want to see the actual new and lost queries for your domain between two timespans, you’ll need to export the sheet that contains your queries for each starting date. With just the plain queries for a timespan, all you need to get is the difference between the two.
- This way you’ll see that not every drastic change in ranked queries is really that drastic. Sometimes you will be losing queries that are really completely irrelevant to the website anyway. Often it’s queries that are somewhat related but while they may be bringing in impressions, you are not getting any clicks from them anyway.
- Sometimes you will be losing queries for no reason, because whatever, and that’s called Google Update. It will get back in a few months as the SERP adjusts away from Google’s agenda back to what people actually engage with, and then Google will roll out another update, and so on and so on.
Anyway, have fun.
[gallery]
