Posted by Bill.Sebald
If you’re old in SEO years, you remember the day
[not provided] was introduced. It was a dark, dark day. SEOs
lost a vast amount of trusty information. Click data. Conversion
data. This was incredibly valuable, allowing SEOs to prioritize
Google said the info was removed for security purposes, while
suspicious SEOs thought this was a push towards spending more on
AdWords (now Google Ads). I get it — since AdWords would give you
the keyword data SEOs cherished, the “controversy” was
warranted, in my opinion. The truth is out there.
But we’ve moved on, and learned to live with the situation.
Then a few years later, Google Webmaster Tools (now Search Console)
started providing some of the keyword data in the Search Analytics
report. Through the years, the report got better and
But there’s still a finite set of keywords in the interface.
You can’t get more than 999 in your report.
Guess what? Google has more data for you!
The Google Search Console API is your friend. This summer it
became even friendlier,
providing 16 months worth of data. What you may not know is
this API can give you more than 999 keywords. By way of example,
the API provides more than 45,000 for our Greenlane site. And
we’re not even a very large site. That’s right — the API can
give you keywords, clicks, average position, impressions, and CTR
How to easily leverage the API
If you’re not very technical and the thought of an API
frightens you, I promise there’s nothing to fear. I’m going to
show you a way to leverage the data using Google Sheets.
Here is what you will need:
If you haven’t heard of Google Sheets, it’s one of several
tools Google provides for free. This directly competes with
Microsoft Excel. It’s a cloud-based spreadsheet that works
If you aren’t familiar with Supermetrics, it’s an add-on for
Google Sheets that allows data to be pulled in from other sources.
In this case, one of the sources will be Google Search Console.
Now, while Supermetrics has a free trial, paid is the way to go.
It’s worth it!
Installation of Supermetrics:
- Open Google Sheets and click the Add-On option
- Click Get Add-Ons
- A window will open where you can search for Supermetrics. It
will look like this:
From there, just follow the steps. It will immediately ask to
connect to your Google account. I’m sure you’ve seen this kind
of dialog box before:
You’ll be greeted with a message for launching the newly
installed add-on. Just follow the prompts to launch. Next you’ll
see a new window to the right of your Google Sheet.
At this point, you should see the following note:
Great, you’re logged into Google Search Console! Now
let’s run your first query. Pick an account from the list
Next, all you have to do is work down the list in Supermetrics.
Data Source, Select Sites, and Select Dates are pretty
self-explanatory. When you reach the “Select metrics” toggle,
choose Impressions, Clicks, CTR (%), and Average Position.
When you reach “Split by,” choose Search Query as the Split
to rows option. And pick a large number for number of rows to
fetch. If you also want the page URLs (perhaps you’d like your
data divided by the page level), you just need to add Full URL as
You can play with the other Filter and Options if you’d like,
but you’re ready to click Apply Changes and receive the data. It
should compile like this:
Got the data. Now what?
Sometimes optimization is about taking something that’s working,
and making it work better. This data can show you which keywords
and topics are important to your audience. It’s also a clue
towards what Google thinks you’re important for (thus, rewarding
you with clicks).
SEMrush and Ahrefs can provide ranking keyword data with their
estimated clicks, but impressions is an interesting metric here.
High impression and low clicks? Maybe your title and description
tags aren’t compelling enough. It’s also fun to VLOOKUP their
data against this, to see just how accurate they are (or are not).
Or you can use a tool like PowerBI to append other customer or paid
search metrics to paint a bigger picture of your visitors’
Sometimes the littlest hacks are the most fun. Google commonly
holds some data back through their free products (the
Greenlane Indexation Tester is a good example with the old
interface). We know Search Planner and Google Analytics have more
than they share. But in those cases, where directional information
can sometimes be enough, digging out even more of your impactful
keyword data is pure gold.
Sign up for The Moz Top
10, a semimonthly mailer updating you on the top ten hottest
pieces of SEO news, tips, and rad links uncovered by the Moz team.
Think of it as your exclusive digest of stuff you don’t have time
to hunt down but want to read!