Identify missing phrases in your content using the SEMrush or Search Console API

Are you ranking for keywords that are missing from your page?

This article shares a simple Excel methodology to answer that question for you.

It uses SEO Tools for Excel, the SEMrush API and the Search Console API. Read on for the why or skip a few paragraphs for the how.

Why expand your copy for new keywords?

Analysing and improving your copy is (in my opinion) one of the most effective ways to improve your keyword rankings.

Links will get you part way there, but it’s the messaging in your copy that will carry you past the finish line, and beyond. I don’t feel I need to justify good copy writing; better copy equals more sales, just ask my friend Joel Klettke!

Richer copy also means a longer long-tail, more keyword rankings and – arguably – a more relevant document that performs well in search.

As I wrote last year in this article about improving category pages for retailers, focusing on links alone is a mistake. Links are powerful compensators for a subpar page, but without the content you’re working extremely inefficiently.

In the end, if you’re tolerating bad copy or no copy, you’re welcoming competitors to your door with open arms.

What keywords are you targeting and what does your page already rank for according to SEMrush and Search Console?

I’m a big fan of this very simple keyword expansion process:

I always find myself taking notes along the way, because when you’re reviewing a keyword list you should be working through ideas on how you’ll use those phrases in a legitimate way (versus just mindlessly stuffing them in wherever they’ll fit!).

Provided you do a nice job of the body copy (and by nice I mean written-for-your-customers-and-rich-with-benefits nice!), this is a good process for identifying keyword expansion opportunities. The thing is, moving from one report to another is slow.

Here’s a faster way to do it in SEO Tools for Excel.

How do I do this?

This is the end result:

To make the process work, first you need the keyword data.

Here’s how to fetch Search Console data.

Install SEO Tools for Excel, open Excel and head to SEO > Search Console > Search Analytics. You’ll get an options pane on the left hand side like this:

Go through the Google Account authentication and you’ll end up with a set of options including report, site, date range and so on.

I select the “formula” option in the helper, which inserts a formula for me. This lets me make small changes if I’d like.

In my very simple example, I’ve added a filter: country == usa. Filtering is obviously very important when we’re querying the search console UI and fortunately the options to create filters (including page URL!) are listed here.

Importantly, you can filter by URL with page == [your url].

You might want to use SEMrush data, so here’s how to do that:

Firstly you’ll need an SEMrush API key:

Like before, open Excel and head to SEO > SEMrush > URL Organic Search Keywords. This brings up an options pane:

Fill out the options (URL, database name and so on) and select Formula as output:

I’ve made a small tool from the formula by changing the URL to a cell reference: $B$1.

Finally, you need to check if the phrase is present on the page. You can do that with this formula:

=IF(RegexpFindOnUrl($B$1,A4,,,)<>"","FOUND","NOT FOUND")

The RegExpFindOnURL function is a handy feature from SEO Tools for Excel. It enables the use of regex matching filters on the source of a web page. So if we’re looking for that exact string on the page and, if the result is blank (“NOT FOUND”), then the keyword isn’t on the page.

Hopefully this is useful if you’re thinking of analysing your own pages – I can’t stress enough the power of a process that continually reviews and improves your page copy.

Comments are closed.

  • Thanks for putting this together! Would you be able to expand on the regexp formula? I’m trying to follow the steps you provide but having issues. I copy the formula and paste into a cell on row 4. I also have the URL listed in cell B2. I’m sure it’s something simple I’m not doing but i can’t figure it out.

  • The fact that SEO Tools for Excel only works with PC is infuriating. The manual approach without that tool requires too much time to make it worth the effort. There’s an opportunity here for someone to design a tool that utilizes the same functions using Excel for Mac or Google Sheets.

    • It’s the mac causing the problem. Excel for Mac and Excel for Windows are two completely different bits of software – unfortunately the development framework required to run SEO Tools for Excel isn’t a thing for Mac. But this is a problem that could be easily solved by, getting a windows laptop!

  • What an interesting way to deal with such a problem. I have to admit that I didn’t know it, but I’ll try it in the nearest future with pleasure. Thanks :)


Join the Inner Circle

Industry leading insights direct to your inbox every month.