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 team member.
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 on the keyword research process, so that 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 is 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.
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'.
I usually edit these to be specific to a client based upon how I want to categorise them. 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.
Within each table, you'll see 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.
Next, head over to the categorisation tab and start using each table to categorise your keywords however you want.
It’s 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 similar methodology 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"))))))
Code language: Excel (excel)
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 search for another word.
Mine is more straightforward. It looks like this:
=IFERROR(LOOKUP(2,1/COUNTIF([@Keyword],"*"&category[Search For]&"*"),category[Return]),"")
Code language: PHP (php)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 keywords 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.
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 are categorised based upon 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 more intelligent 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 understanding 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 their 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.
This can give you some beneficial insights for suggestions you’ll make as part of your SEO strategy.
Site structure
Effective tagging also makes it easier to form topic clusters and efficient taxonomies for site structure.
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're the downsides?
- Scraping Google can be costly
- You're still probably going to want to make some manual adjustments afterwards
- 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.
Here are some tools to help you get started if you're interested:
- SE Ranking Keyword Grouper - This tool does exactly what I explained previously, and allows you to enter your own dataset for grouping
- Serpstat Keyword Clustering - Same as above
- Ahrefs Parent Keyword - Within the Ahrefs Keyword Explorer tool, there is a column for the parent keyword; this uses the SERP and search volumes to determine how keywords should be grouped.
Final words
Hopefully, you'll find this helpful. 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.