Scaling word-based keyword grouping in Excel

Sam Underwood
Sam Underwood
December 21, 2020
7 mins read

Excellent keyword research is one of the essential foundations of your SEO strategy.

Some see it as one of the more 'basic' tasks for SEO, often delegated to a more junior member of a team.

However, there is a vast difference between basic keyword research and detailed, effectively grouped research prepared for prioritisation and usage for other SEO tasks.

There is a plethora of brilliant content out there on the keyword research process, so I won't be going over that.

I'm adding to the conversation a way to group and tag keywords based on the words contained within them, all done within Excel.

The method I'm going to run through works great because:

  • It's quick
  • You don't need to know how to code
  • It's scalable — I've categorised keyword sets in the 10,000s using this

That said, it does have its pitfalls. The main one being that word-based keyword tagging can't accurately infer intent. If you're looking for a solution for that, Kevin Indig has a brilliant process.

How should you group keywords?

You should cater the way you group keywords based upon trends you spot within the dataset.

However, some generic groupings are:

  • Category
  • Sub-category
  • Common words
  • Intent
  • Position range
  • Commercial importance

The spreadsheet

As I've said, I've built this using Excel to keep it as accessible as possible. This isn't my 'full' keyword research sheet, just the part I use for categorisation. Here is the link to download:

The sheet consists of two parts, the actual list of keywords (in the 'Keyword Research' tab) and then the 'Config - Categorisation' tab.

In this tab, you'll find various long tables with headings at the top matching the header specified within the 'Keyword Research' tab. By default, it's set to be 'Category', 'Sub-category', 'Custom 1' and 'Custom 2'.

categorisation tables

I usually edit these to be specific to a client based upon how I want to categorise. Editing the headings in the 'Keyword Research' sheet will automatically be reflected in the categorisation sheet.

For example, I recently tagged keywords based upon the following for a client who creates Casino/Bingo content.

example custom categorisation headers

You'll see within each table there is a 'Search For' and 'Return' column. The left column is what you are checking for (using 'contains' logic), and then the right column will be returned in the 'Keyword Research' sheet.

You've probably already got the gist of it by now.

Once you've done your research, copy and paste your keyword list into the first column of the 'Keyword Research' sheet.

keyword column highlighted

Next, head over to the categorisation tab and starting using each table to categorise your keywords however you want.

It’s really as simple as that.

Some key things to note:

  • The tables prioritises bottom up. If your keyword matches multiple searches, whatever is lowest in the table deems how it will be categorised.
  • Use can use wildcards! If you check the example screenshot I shared above, you can see I've got one category searching for 'bingo*slot'. When both terms show in that order within a keyword, it'll be categorised as 'Bingo & Slots', regardless of the words in-between.

One other thing you'll notice once you start is it's fast and the formula is simple.

Nearly two years ago, I tweeted about a methodology doing something similar using SERP features, which I'm planning to evolve further and release in the future.

How it works

Another way I've seen word-based categorisation done is by using the SEARCH function. The formula ends up looking like this when you want to check for multiple words:

=IF(OR(NOT(ISERR(SEARCH("are",A3))),NOT(ISERR(SEARCH("article",A3))),NOT(ISERR(SEARCH("better",A3))),NOT(ISERR(SEARCH("boost",A3))),NOT(ISERR(SEARCH("can",A3)))=TRUE,"Awareness",IF(OR(NOT(ISERR(SEARCH("alternative",A3))),NOT(ISERR(SEARCH("application",A3))),NOT(ISERR(SEARCH("comparison",A3))),NOT(ISERR(SEARCH("benefit",A3))),NOT(ISERR(SEARCH("best",A3))),NOT(ISERR(SEARCH("companies",A3))),NOT(ISERR(SEARCH("directions to",A3))),NOT(ISERR(SEARCH("estimation",A3))),NOT(ISERR(SEARCH("feature",A3))),NOT(ISERR(SEARCH("hardware",A3))),NOT(ISERR(SEARCH("installation",A3))),NOT(ISERR(SEARCH("like",A3))),NOT(ISERR(SEARCH("leading",A3))),NOT(ISERR(SEARCH("near me",A3))),NOT(ISERR(SEARCH("or",A3))),NOT(ISERR(SEARCH("program",A3))),NOT(ISERR(SEARCH("review",A3))),NOT(ISERR(SEARCH("service",A3)))=TRUE,"Consideration",IF(OR(NOT(ISERR(SEARCH("acquire",A3))),NOT(ISERR(SEARCH("agency",A3))),NOT(ISERR(SEARCH("agent",A3))),NOT(ISERR(SEARCH("analysis",A3))),NOT(ISERR(SEARCH("apply",A3)))=TRUE,"Decision","Intent Not Found"))))))

That method will slow your machine to a halt with larger datasets. And it's also time-consuming and hard to manage, you have to copy and paste the formula repeatedly every time you want to search for another word.

Mine is more straightforward. It looks like this:

=IFERROR(LOOKUP(2,1/COUNTIF([@Keyword],"*"&category[Search For]&"*"),category[Return]),"")

Yup, that's right, it's just two functions used together, a LOOKUP and a COUNTIF.

The LOOKUP is for returning the correct result from your table. The COUNTIF returns a '1' if the query in your keyword column is found in the 'Search For' column of the table.

Inferring intent

Alongside grouping similar keyword into logical categories based upon similarity, you'll also want to group based upon the keyword's intent.

You'll see within the categorisation tab I've created a table for doing this.

intent tables

It categorises keywords into:

  • Navigational: Users looking to navigate a particular site
  • Informational: Users looking for information

Informational terms are categorised based upon standard terms people use when looking for information, e.g. what, where, how, best, tips etc.

Navigational terms categorise based upon them including common URL patterns, or from a list of brand names you can enter (overwriting 'Brand 1' etc. in the 'search for' column).

You'll notice it doesn't categorise commercial terms. That's because without SERP analysis or a smarter algorithm for categorisation, we can't be sure the keyword is commercial.

We could say if the keyword doesn't include any of the ones we've mentioned, tag it as commercial. However, take the term 'Eiffel Tower'.

This is an informational term, but you can't infer that just from looking at those two words without having a strong understanding of the entity (Google does this thanks to its Knowledge Graph).

If you want to tag 'Commercial' or add additional informational categorisations, manually add more phrases to this table.

Or, combine this methodology with intent mapping based upon SERP Features.

What’s next?

Once you've categorised your keywords, you'll want to start moving onto:

  • Opportunity sizing — Measuring potential growth within different market niches
  • Market analysis — Analysing where competitors are positioned within different market niches
  • Site structure — Organising the site based upon different how people search

All three of the above require a post of there own on how to do them (I've already got one for site structure).

For now, I'll explain how the above tagging process benefits them.

Market analysis

One great thing about accurate tagging of keywords is you can pinpoint whether specific competitor strategies are working.

Here's an example.

After tagging our keywords, we begin our competitor analysis and notice one competitor includes the word 'best' in H1s and title tags, and our site is not.

We tagged our keywords and created a group for all terms containing 'best'. So we know from summing up the search volume there is a reasonable amount of searches for 'best [product name]'.

Is this competitor performing well for these searches?

You can figure this out manually (which I'll write about in the future), but if you're using a rank tracker supporting both tagging and importing via CSV, you could filter for the 'best' tag and see whether their visibility is higher than competitors for that term.

ahrefs visibility comparison

This can give you some really useful insights for suggestions you’ll make as part of your SEO strategy.

Site structure

When it comes to site structure, effective tagging makes it easier to form topic clusters and efficient taxonomies.

topic cluster

This is done by making good use of the category and sub-category columns. It would be best if you categorised so the 'Category' column could be pillar pages.

Sub-categories should be the content pages coming off the pillar page.

An example, we could have a category for 'New York', but then sub-categories for:

  • Population
  • Things to do
  • Neighbourhoods
  • Time
  • Mayor
  • Minimum Wage
  • Universities

SERP analysis and research are required to understand whether individual pages are needed for each of the above.

However, you can see from just these two examples, putting the work in at this stage makes future SEO tasks more efficient.

Other keyword grouping methods

Other than word-based grouping, another great way to categorise keywords is by looking at how similar search results are for different keywords.

If the search results are similar, or a URL ranks for multiple keywords, we can assume that the dataset's keywords must be related.

This is an effective way of grouping keywords as your reverse-engineering Google's understanding of the intent of different keywords.

Sounds great, but what's the downsides?

  1. Scraping Google can be costly
  2. You're still probably going to want to make some manual adjustments afterwards
  3. The grouping is non-hierarchal, e.g. no categories and sub-categories

A blended approach between this method and word-based categorisation counteracts some of the downsides, and it tends to be what I do.

If you're interested, here are some tools to help you get started.

Final words

Hopefully you'll find this useful. To me, one of the best ways to up your SEO-game is by improving your process when it comes to research and prioritisation, so I’m hoping to find some time to cover more on the topic in the future.

coffee-cup linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram