Google Search Console API: pull your own data

Full transcript

1,156 words · the narration as spoken in the episode.

Intro

Search Console's export button gives you a thousand rows. If your site has more queries than that, the rest are simply not in the file.

And Search Console only keeps about sixteen months of history. Everything older is gone, unless you saved it.

Both problems have the same fix: pull the data yourself, through the API, every day, into your own database. In this episode we'll do exactly that for one of my own sites. We set up access with a service account, write the query, page through all the rows, and store it so the history never disappears again.

Access: a service account

To call the API from a script, you need an identity that isn't you. That's a service account: a robot user in Google Cloud.

Three steps. Create a Google Cloud project and enable the Search Console API in it. Create a service account and download its key, which is a JSON file. Then go to Search Console, open your property's user settings, and add the service account's email address as a user.

Which permission? Restricted is enough for reading data. Give it the least access that works. And use the read-only scope in your script, so even a bug can't change anything.

Where does the key go? Same rule as last episode. Outside the project folder, readable only by you. I keep one key in my home folder, and the script points to it with an environment variable.

One account can be added to many properties. I use one service account for all my sites, so one script can pull everything.

The query: dimensions and filters

The method you'll use for almost everything is called search analytics query. You give it a date range, a list of dimensions, and optionally filters.

Dimensions are how the data is split. Date, page, query, country, device, and search appearance. You choose the combination, and you get one row per combination with clicks, impressions, CTR and position.

Here's the first surprise. The dimensions you choose change the totals. If you ask for the date only, Google counts per site, and you get the complete total. Add the page dimension, and it counts per page. Add the query too, and rows get dropped for privacy.

Here's one day on one of my own sites. A quick note first: the data is real, straight from the Search Console API, but anonymized. The domain, the page URLs and the search terms are replaced, so we stay on the principles and not on the keywords. The numbers are untouched.

The date-only pull says three hundred and fifty clicks that day. Add up the query and page rows for the same day, and you get about sixty. Same site, same day. That isn't a bug in your script. Episode nine explains the whole thing.

My practical rule: store two separate pulls. One by date and page, which gives you complete page totals. And one by date, page and query, for the detail.

Row limits and pagination

Each call returns at most twenty-five thousand rows. If there are more, you ask again with a start row offset, twenty-five thousand, then fifty thousand, and so on, until a call comes back empty.

Here's what that looks like on my own site, six days in a row.

Each bar is one day of query and page rows. The line is the thousand-row export limit. Every day lands between about forty-seven hundred and sixty-two hundred rows. That's five to six times the limit. Everything above the line is data you'd never have seen from the export button.

Pull one day at a time. That keeps each call small, and it makes a daily job simple: one date in, all rows out.

Even with pagination, the API won't give you every query. Very rare queries, and queries Google considers private, are left out. In episode nine you'll see how much that can be on this same site.

Data delay and fresh data

Search Console data is not real-time. The last two or three days are usually missing or incomplete. The API can return fresher, preliminary data if you ask for it, but those numbers can still change.

So for daily storage, I do two things. I pull a date that's a few days back, when it's final. And I re-pull the last few days again on the next run and overwrite them. Never trust yesterday's numbers in Search Console as the final answer.

When you compare periods, compare complete days only. A week that ends yesterday will always look like a drop.

Storing daily pulls

Now store it. I use SQLite: one file, no server, and it's fast enough for millions of rows. We set up the full workspace in episode six.

Two tables, matching the two pulls. One row per date and page. One row per date, page and query. The date plus the other dimensions form the key, so if you pull the same day twice, the new row replaces the old one instead of doubling it.

Also save the raw response as a JSON file, like we did with DFS. It's free data this time, but the cache still protects you from bugs in your parsing.

Then schedule it. A daily job that pulls the last five days and upserts them. Once it has run for a year, you have history that Search Console itself no longer shows you.

Pitfalls

Three things that trip people up.

Property type. A domain property covers every subdomain and protocol. A URL-prefix property covers only that prefix. Make sure the script queries the same property you look at in the interface.

Position is an average, and averages hide a lot. Episode eight is about reading it properly.

And don't use the sitemaps report in the API to judge indexing. Its "indexed" number has proven unreliable in my own measurements. For index status, use URL Inspection, which we'll come back to in episode seventeen.

Sanity checks on the first pull

Before you build anything on the data, check it once against the interface. Pick one complete day. Here's that check on my own site.

The date-only pull should match the interface total for that day, or come very close. So should the sum of your page pull. If it doesn't, you're probably querying a different property, a different search type, or a day that isn't final yet.

The query pull will be lower. Here it's about sixty clicks out of three hundred and fifty. Don't panic about that gap. It's the hidden queries we just talked about.

Then check the row counts per day, like in the chart we saw earlier. A sudden day with far fewer rows, while clicks look normal, usually means a pagination bug, not an SEO event. Ten minutes of checking now saves you from a chart that's wrong for a year.

Wrap-up

A service account, two pulls a day, pagination, and a database that keeps your history. The pull script and schema are linked in the description. Next episode: joining Analytics, Search Console and DFS into one data model. See you there.