Bulk Keyword Categorisation & Classification

Need help with your enterprise or programmatic SEO strategy? Let’s chat.

Categorising a large keyword list in bulk takes hours to do, unless you do it a special way. Learn how to categorise thousands of keywords in seconds through the use of a magical Excel formula.

You can categorise anything you want with this formula, it doesn’t only work on keywords. Another popular use of mine is to categorise landing pages, which can significantly help with showing how ranking performance has improved certain landing page’s traffic.

This is an older video, of doing categorisation in Excel. The overall principles are exactly the same, however this will not work in Google Sheets.

But, I have a solution for that. A different formula that works in Google Sheets.

Categorising & Classifying Keywords in Excel

To categorise and classify your keywords in Excel, just like in the video, you can download my Excel template below.

Download the Keyword Categorisation Template
  You will receive a download link via email. We hate spam and never share your details.

Categorising & Classifying Keywords in Google Sheets

If you would rather use Google sheets, my preferred method these days, then you can copy my categorisation sheet below.

You will see a very similar template to what is shown in Excel in the video above, except with a slight tweak to the formula.

The formula I use for this is;

=IFERROR(ARRAYFORMULA(INDEX(*categoryRange*, MATCH( TRUE,ISNUMBER(SEARCH(*findSeedWordRange*,” “&*keyword*&” “)),0))), “Unclassified”)

(might need to change the quotations due to formatting issues)

You’ll see one major difference with the Google sheet though, and that comes down to the cell ranges used for the find/category keywords.

If a cell range in the formula is larger than what’s available, when no category is found it will leave the cell blank.

To use the ‘unclassified’ text, the range needs to be exactly the same as what’s available. This means every time you add keywords into the find column, you’ll need to expand that range.

I tend to use just the blanks as the other/generic/unclassified as it tends to make life a bit easier.

 

New catorisation formula & setup

I was reached out to on Linkedin by Kyle Faber and he believed he could help tweak the system a little bit.

Using a combination of regex & a textjoin, he was able to help tweak the system.

It does appear to be faster that the setup posted above, and is also a lot simpler & cleaner which will help troubleshooting.

I’ll break it out a bit more later, but for now, the new formula for joining all the seed phrases together is;

=textjoin(“[s]?|”,true,sort(I$3:I,1,false))

Then the formula used in the category cell, is;

=IFERROR(VLOOKUP(REGEXEXTRACT(” “&A2&” “,I$1),I$3:J,2,FALSE),”Unclassified”)

Which will categorise the exact same setup above, with a different formula, by looking for the matching find, and then return the category with a vlookup.

It will default to partial matching, but if you’d like exact matching of a word, then enter a space before & after the find column.

That will then match full mentions of the word, rather than just partial matching.

Was great to get some feedback on this, and be able to tweak the setup that I’ve used for years!

Get the updated sheet at the link below;

Definitely give Kyle a follow on his socials, at https://linkedin.com/in/kylefaber & https://twitter.com/regal_kyle.

 

Categorising URLs into Site Sections

You can also use this directly on URLs, to categorise them into site sections, locations, or anything you’d like.

 

Let me know how you use this sort of sheet, or whether you do this all a different way.

Posts you might also be interested in

Like this article?

Share on Facebook
Share on Twitter
Share on Linkedin
Share on Pinterest

Leave a Reply

Your email address will not be published.