Granular SEO Lab: Making sense of Google API data for Search Console

The tide is turning ... AKA Google just killed all one-man-shows that didn't go tits up from the pandemic

Granular SEO Lab: Making sense of Google API data for Search Console

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.

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:

Search Analytics for Sheets

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:

30d data google api

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:

Count your ranked queries

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.

 

Labeled as · search-data · intelligence · tooling
Last update at 2020-05-26 08:09:13 +0000
Written by TheoryForce