Google Search Console for keyword research: Go beyond data limits & filter for actionable insights

Author: Andrew Cock-Starkey

When planning new content or reviving older assets, proper keyword research can mean the difference between success and failure.

While many keyword research tools can help with this process, Google Search Console (GSC) stands out for its accessibility, price point (free), and because the data comes directly from Google itself, making it an industry standard.

 In this guide, I’ll show you how to go further with this tool (well beyond the limitations of its standard web interface) to power-up your keyword research efforts.

Table of contents:

• Pros and cons of keyword research in GSC’s web interface

• The Google Search Console API for more data

  • Search Analytics for Sheets

• Filtering for actionable insights

• Regex for advanced data filtering

• 3 ways to use your GSC data for keyword research

Pros and cons of keyword research in GSC’s web interface

Google Search Console’s interface is often a good place to start your keyword exploration. If you’re thinking, ‘Wait, there are other ways to use this data aside from tracking rankings and clicks?’ you’re going to love what’s coming up.

GSC’s web interface is clean, clear, well-designed and once you know your way around, it’s pretty useful. In addition, Google often adds new features, elements, and improvements (hats off to the team that finally added the ability to use regex in filters—more on them later).

However, there are some caveats on data in Google Search Console’s web interface that I would be remiss not to mention at this early stage:

• Google redacts data in Search Console

• GSC only shows data for queries you already rank for

• GSC only shows the first 1,000 rows of data

Google redacts data in Search Console

The explanation around this is that Google’s systems remove any data that could identify individuals, so [Why does Andrew the SEO have such terrible dress sense?] may never show up in your queries (thank goodness!).

In Search Console when you apply filters (any filter), the great data redaction starts. Here’s an example by Mark Williams-Cook showing a site with 148,000 total clicks—39,700 clicks from branded terms with 30,800 clicks on non-branded terms.

So, what about the other 77,500 clicks? The answer: data redaction.

Source: Mark Williams-Cook.

This can be frustrating, but some of the tools I’ll show you in this article can help you address these issues.

GSC only shows data for queries you already rank for

If you’re doing keyword research around a brand new topic area or for a product in a niche you’ve never even operated in, Search Console may not be the best place to start.

GSC does not give you details that other third-party keyword research tools typically provide (e.g., search volume, keyword difficulty, search intent). It may be that you rank 85th and no one ever sees your site, but if you don’t rank at all there won’t be any data there.

But even when there is data, it’s limited—literally, as you’ll see below.

GSC only shows the first 1,000 rows of data

Investigate any issue in Google Search Console and you soon run into this:

You get 1,000 rows of data—that’s it, no more.

• You want to see which queries you’re ranking for? Cool, you can see the first 1,000 rows.

• Which pages on your site are ranking? Okey-dokey… but only the first 1,000.

• Investigating an indexing error or issue? Google says you have over 2,000 URLs that are not indexed because they’re a “Soft 404” (for example), but dig in and you can only see the first 1,000.

If you’re working on a small site or you’re DIY’ing your SEO for a small business, maybe you’ll never even get near the 1,000 row limitation in Google Search Console.

Only have 20 pages on your site? The likelihood of you needing 1,000 rows of data is pretty low.

But if the site you’re working on has many thousands of pages, ranks for tens of thousands of queries, and is potentially throwing up many more thousands of errors and indexing issues, then that 1,000 row cap will get frustrating pretty quickly.

You may be thinking, “Ah, but I can just add filters in GSC. Once I get below 1,000 rows the data is all good.”

If, for example, you filter to just show queries that a specific page on your site ranks for, you can get fewer rows… and if you limit the date window to just the last seven days… and maybe filter to show only mobile device searches…

And you can use the export button in Search Console to put all this in a spreadsheet.

Sure. That kind of works.

You can see where this is going: You’re just hamstringing your own efforts to get usable, actionable data—data that can actually help you make your site perform better.

What if I told you there was a way to get 50x more data? Or maybe even more?

The Google Search Console API for more data

Introducing the Google Search Console API (Application Programming Interface, but don’t worry, you don’t need to be a programmer to get the most out of them).

If you want to dive into the API, great. Here’s the documentation. The key part for me, though, is this:

“All use of Google Search Console API is free of charge. However, it is subject to usage limits.”

That’s right. It’s free (my favorite price) and so long as you don’t go wild, you’re unlikely to run into those usage limits (and even if you do, waiting a minute or a day usually sorts things out).

There’s nuance and exceptions here, but broadly speaking, you can make 40,000 QPM (queries per minute) and 30,000,000 QPD (queries per day)—that last number is thirty million. Per day. 

If you’re approaching those limits, you probably do need to dig into the documentation a bit more and start coding your own systems (or paying someone to do it for you).

The Search Console API is “limited to 50K rows of data per day per type (web, news, image, and so on) per property”. So that’s 50,000 queries you could get. Or 50,000 pages. Or 50,000 URLs affected by an indexing error.

Instantly, we’re in a different ballpark from that hard cap of 1,000 rows of data in GSC’s web platform. With the API, you can even get beyond that 50,000 limit too with some clever ‘stitching’ of data (but that’s a topic for another time and another blog post).

Hopefully, you’re now thinking: “Show me how!”

There are lots of tools out there, built by very clever people that will do all sorts of cool stuff with the Search Console API (Shaun Anderson at Hobo’s work is worth exploring—he’s built dashboards you can connect to your own data, saving you a ton of build time ). Some are free, some have a price tag, others work on a ‘freemium’ model (SEO Stack is an interesting new player in the paid-for tools area).

My favorite tool for leveraging the GSC API for keyword research falls into the freemium category. It’s called Search Analytics for Sheets, by the very smart Mihai Aperghis.

As the name suggests, this is an add-on that works in Google Sheets and you don’t even have to generate API keys to use it. It’s also free for up to 10,000 rows.

Let’s assume that, like all discerning shoppers, you’d like to try before you buy. Let’s take the free version for a spin.

Search Analytics for Sheets

I’m not going to give you a ‘How to’ guide on Search Analytics for Sheets (mostly because Aperghis and his team have already done a far better job of it than I can).

Instead, I’ll show you some of the ways I use it and how it can help you get loads of useful information to help you with your keyword research.

Once you install Search Analytics for Sheets from the Google Workplace Marketplace (like the Chrome Store for stuff that isn’t in Chrome!), open up a new Google Sheet.

Pro tip: Type “sheet.new” in your browser to do that from your browser bar.

Then, in your new Sheet, look along the toolbar until you see “Extensions”. In there, you’ll now find Search Analytics for Sheets with the option to “Open Sidebar”:

That will open up a sidebar that looks like this:

Most of this is self-explanatory so, again, I’m not going to explain every single step. If a particular field isn’t obvious, there are handy question mark icons (shown above) that you can hover over them with your pointer for more details.

Key things to note here are:

• Verified site — if you don’t see the site you want to work on in the dropdown list, chances are you don’t have the right permissions. Go check. If it’s a client site, you may need them to change your Google Search Console permissions. If you do see the site you want in there, make sure you choose the right one: With or without the “www”? HTTP or HTTPS? And domain-level properties work here too, so choose carefully.
  

• Date range — Too many times to mention I’ve tried to pull data on a site with a good idea of what I’m looking for, only to realize I set the wrong dates.
  

• Search type — You can use the tool to bring back data on Images, Video, Google News, or the web (default)—most times, most people want that one.

• Group By — This is basically asking you, “What columns do you want on your data?” You’re going to get clicks, impressions, CTR (click-through rate), and average ranking position by default. Let’s look at the dropdown choices.

What else do you want? Probably at least one of Page or Query. Without those, you get something like this:

Total clicks, impressions, CTR, and position for the entire site during the chosen date window. Lovely, but not all that useful.

Add in Query and you get something like this:

Now, you can see which search queries drove those clicks, impressions, etc.

Go one step further and add Query and Page to get this:

Now, you can see which of your pages is getting all those clicks and impressions.

Note: In these examples, I’m just showing the first five rows of data so the screenshots make sense and are legible, but we’re up to 25,000 rows in most of these examples. You can add in Device and Country too (and get something like this):

But be careful—there are limits (even if they’re big numbers) and each data point or column you add is another query against those limits. Only draw down the data if you actually plan to use it.

Back to our sidebar; remember, there are still a few more bits to review:

• Filter by — I’ll explain this in more detail in its own section.
  

• Aggregation Type — There are just two choices here: By Property or By Page. If you’re experienced with Google Search Console data, you may be familiar with these already. Basically, it’s asking: If you have two or more pages ranking for the same query, do you want to see that data rolled-up into one (‘By Property’)? Or do you want it split by page (‘By Page’)?
  

• Rows returned — How many rows do you want? 1,000, 10,000 or 25,000? (Or more if you’re willing to pay for a subscription.)
  

• Results sheet — Don’t skip this last one. Options here will look something like this:

The default is to create a new sheet. In this field, you’re choosing where you want Search Analytics for Sheets to put all the data it fetches you. Eventually, you may end up with a row of tabs along the bottom of your Sheets that look a bit like this:

These aren’t as confusing as they may look. They’re all called “SAS” (Search Analytics for Sheets) and then the date and time: 2024_10_18_13_42_16 (the 18th of October 2024, at 13:42:16, which was when I pulled this data).

Personally, I maintain this naming structure as knowing when you did these data runs can be super handy, but you can change your tabs to whatever you prefer for your own workflows.

When you run a new query, be sure to designate where you want the data to go. If you make a mistake and want to fetch the data again, you can get the tool to write over the top of your old Sheet (choose it by name from the drop-down or pick Active Sheet).

Otherwise, stick to Create New Sheet—it’s easy to move or rename it later.

There is likely to be a lot of data in these spreadsheets so eventually things are going to run very slowly if you have multiple tabs open in Google Sheets. This is worth bearing in mind (it’s also worth looking at tools like SEO Stack that’ll do the data storage side for you).

Lastly, hit the big blue “Request Data” button at the bottom of the sidebar and leave the tool to do its thing.

It usually only takes a minute or two, but this may depend on the complexity of the search you’ve set it to do.

You should now have around 25,000 rows of data. Lovely, lovely, data.

Just by ordering, the columns you can uncover some interesting stuff:

• Which pages get the most clicks?

• Which queries have the most impressions but the fewest clicks?

• Which pages have zero search impressions?

• Etc.

And now, you’re doing it with up to 25x more data than you can see directly in Search Console.

Filtering for actionable insights

Twenty-five thousand rows is too much data for anyone to take in and analyze, so like all good SEO folks, we’re going to pan this data for the gold. We’re looking for patterns and trends.

Filtering is going to do a lot of heavy lifting for you here.

You can easily create filters to provide you with actionable intel, such as showing all the pages with low impressions or queries with lots of impressions but low/no clicks.

How to filter out… 

Brand filters are useful for many types of businesses.

If you’re not already ranking at the top of results for your own brand name, you probably have bigger issues to address than keyword research.

The thing with branding is, when it’s working well, it can create a lot of noise in your data as a byproduct. That issue can be compounded by typos and spelling errors: is it [Starbucks] or [Starbux]? And what about [starbuck] or [strabucks] (common typos).

If a user goes through the trouble to punch into Google [Starbucks near me] or [Starbucks central Cambridge], they’ve pretty much already decided where they want to go. All data is useful, but if we were working for the famous mermaid coffee shop brand, maybe we don’t need to do a lot of work around our rankings for branded search terms.

With a filter, you can screen all those searches out of your data.

To give Search Console it’s due, you can do this right in the online interface:

The “+ Add Filter” button does just that.

And, thanks to the addition of regex filters, you can set up filters like this:

That will screen out all the examples above (note the “Doesn’t match regex” option).

Search Console uses the RE2 syntax for regex and has its own guide on how to use it GSC.

But remember, you’re only getting back a maximum of 1,000 rows of data here.

You can also do this in the Google Sheet, but with all the data you’ve pulled through. There are some basic filters available out of the box:

You can also use the “Custom formula is” option to get your regex jam going again using the REGEXMATCH formula (layering it up with “If” commands, you can do some crazy useful stuff in Sheets but that gets a little complicated a little too quickly for this post).

You can do this with Search Analytics for Sheets too—even before you fetch the data. You can set-up a filter that does/doesn’t match regex and get up to 25,000 rows of data narrowed down to your area of focus:

You can even add multiple filters to really hone in on the data you want.

Conversely, in Search Console you can add filters, but only one per type (so you can have one Query filter and one Page filter; but not two different Query filters). With REGEXMATCH, layering up filters can get very complicated and a misplaced pipe (|) or parenthesis can break everything.

In Search Analytics for Sheets, you can add multiple filters like this:

In the example above, I’m looking to pull all the queries that don’t mention my brand name (or common misspellings) but also queries that don’t mention SEO or Search Engine Optimisation (spelled with an s or a z for my American friends!) and the page URL has to contain the word “google”. So I’ve got two Query filters and one Page filter—something you cannot do directly in Search Console.

You don’t have to go far to find top SEO experts reinforcing the importance of brand in SEO. So filtering out your brand searches from non-branded searches is only going to get more important for most businesses (and the SEOs that work for them).

You can expand this beyond your own brand too. Use these filters to screen in/out competitor brand names. Are you ranking for a competitors’ name? Perhaps in searches around comparisons (e.g., [Is brand x better than brand y?] or [Alternatives to brand z])?

How to filter in…

Filters can work to include terms, too.

Something that’s great to do for all sorts of reasons (including SEO), is to answer questions your customers have.

Using a smart bit of regex, you can grab all the question-based search queries your site ranks for a question:

/\b(how|what|why|when|where|can|do)\b

The /\b ensures the regex matches “how” but not “show” or “however”.

You could add an /i at the end to make the whole lot case insensitive, so it would match queries with [How do I…] and also [how do i]—very handy when lots of folks searching aren’t very particular about their grammar (especially when using mobile devices!).

However, it’s worth noting that Google Search Console makes all query data lowercase. I include the tip around making regex case insensitive because capital letters can (and do) show up in URLs.

If you want to filter to find your ‘About’ page and you set up a regex filter like this:

(about)

It is not going to find it if your about page lives here:

optimisey.com/About-us

This is because of that capital letter after the slash. So that /i can come in handy for those sorts of regex filters.

Regex for advanced data filtering

There are a ton of regex guides out there (JC Chouinard’s is a great starting point) so this isn’t the place for another one.

The best way to work with regex is often trial and error (and the various GPTs and AI bots are often pretty great at helping you write or correct regex!) but below are some good examples to get you started and hopefully get your brain working to come up with even more.

Regex	What does it do?	How does it do it?
/\b(how|what|why|when|where|can|do)\b	Finds all the queries with questions that include the words who, what, where, why, when, etc.	/ specifies the start of the string. \b is used to mark a word boundary. This example ensures that the words we look for are just those words, so “how” and not “show” or “howitzer”. | means ‘or’. In this example we’re telling it to look for how OR what OR why, etc.
.*\/$	Looks for anything that ends with a forward slash. Handy for finding your URLs that end with a slash. If you use the filter “does not match regex”, you can also use it to find all the URLs that don’t end with a slash.	. means any character. * the previous thing any number of times. \ ‘escapes’ the next character (i.e., use the character as a character instead of as a regex command). \/ in this example would make the filter literally look for the forward slash. $ means ‘this is the end’, so in this example the / would have to be at the end of the URL.
.*(best|alternat|vs|versus|review|compar).*	Looks for commercial comparison terms. Useful for finding queries from potential customers that signal commercial intent.	. means any character. * the previous command (in this case, ‘.’) any number of times. In this example, we want queries with “alternate” OR “alternative” and “review” OR “reviews” and “compare” OR “comparison”, so we want to leave them open-ended.

3 ways to use your GSC data for keyword research

Congratulations! You’re now awash with data and you have the knowledge, tools, and the regex skills to do amazing analyses with it—analyses like:

• Find content to merge/consolidate or update

• Refine and improve existing content

• Find new content ideas

Find content to merge/consolidate or update

Sort your sheet of URLs by impressions and clicks to find all your pages that have low (or no) search impressions and clicks.

You’ve now got a list of pages that you should review: 

• Why aren’t they getting any search traffic? 

• Have they dropped from the index because of a technical issue? 

• Are they outdated now and have been overtaken by something new—maybe even something new still on your own site?

Ashwin Balakrishnan’s guide on content consolidation can help you put some of these issues to rest.

Refine and improve existing content

By focusing on a subfolder or page, you could extract a huge amount of data on that particular section of your site. Perhaps your /blog/ or all your /services/ pages, or even just a single specific page (such as your highest- or lowest-converting page).

With the huge amount of data about those specific pages, you can now zoom in on all your customer questions (our how|why|when… filter above). Then, find the questions that you don’t answer very well and update those pages so they do satisfy your visitors.

Find new content ideas

Taking this idea further, there may be enough questions left unanswered by your existing content that it warrants creating a new piece of content.

• Perhaps there are some queries that an existing piece of content gets lots of search impressions for but does not rank very well.
  

• Alternatively, it may perform well on lots of commercial queries, but does much poorer on informational queries.

This could demonstrate a gap in your content funnel. You’re getting searchers nearer to the point of purchasing (the commercial queries), but not when they’re still exploring or looking for answers (the informational queries) or vice versa. Build out new content around those informational queries to help put your brand top of mind when those searchers later move on down the funnel. And internally linking from one to the other could help the rankings for both pages. Matching searcher intent can make a world of difference to your rankings and conversion rates.

The more keyword data you have, the better your optimizations will be

These are just a handful of the things you could do with the huge range of data you can now unlock from your Google Search Console setup. Data-driven decisions are often the best decisions, so make sure you’re getting all the data you can to inform the choices you make based on your keyword research.

---

Andrew Cock-Starkey - SEO Consultant at Optimisey Andrew is an SEO consultant and trainer at his company, Optimisey. He's one of the specialist instructors on the Wix Studio SEO learning course and loves dreadful puns and rambling analogies.

Linkedin