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

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:

  • Drill down to the page URL in Search Console’s performance analytics and grab the keyword list
  • Do the same in SEMrush’s Organic Research report at a URL level
  • Maybe expand a little further by doing a drive-by data review in various keyword tools (try this one from my friend Nick Eubanks)
  • Rewrite or update your page based on the new insight

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.