06 August 2019
Keyword research can be, by its nature, quite a time consuming and laborious process. It is, however, absolutely necessary in informing the definition of key focus areas on the site, and those that will have the best likelihood of increasing traffic and in turn driving revenue.
In order to understand the mass of keyword data that one would usually begin with, the keywords must first be categorised into manageable groups that can be properly separated and mapped to sections of the site. Done manually this is quite time consuming and – dare we say it – boring!
To speed up this process I have made a quick keyword classifier in Google Sheets.
I’ll quickly break down how to use it and then go into a little more detail on how the formulas work for those of you that are interested.
You can access a copy of the sheet here.
The keyword categorisation steps are:
First download your keyword data as usual from your favourite tool – I like AhRefs or SEMrush for this.
Paste the keyword, search volume and keyword difficulty under the labelled headings in tab 1.
The rows should turn red to show that no category has been specified for any of the keywords. Now move to tab 2.
This is where you will add the phrases to group your keywords by. In the “Keyword Search Phrase” column, enter the phrase you would like to search for within your keyword data.
Then in the “Keyword Category” column, enter how you would like to categorise these phrases. It’s up to you if this is the same as or different to the search term.
You can manually choose your keyword groups if you like, but there’s a quicker way to kick this off…
Use the work that your competitors have already done to your advantage!
This works especially well for e-commerce sites.
First, find a competitor with a cleanly structured URL format. Just search for a big industry product and there’s a very strong chance you’ll find one on the first page.
We want to pull out the manufacturer terms and models, so we’ll search for a manufacturer and a model, as a high ranking site for this phrase is likely to have structured their site cleanly to display this.
Quick Keyword Classifier Google Audi A3 Lease Deals Quick Keyword Classifier Google Audi A3 Lease Deals Results
Either of the above examples would work fine, as the manufacturer and model sit at the same point in the URL across the site architecture and are thus easily extractable. To get a clean list of URLs to work with: Crawl the site with Screaming Frog SEO Spider. Export the crawl into a Google Sheet. Filter to show only URLs with a 200 OK response code. And includes “text/html” in the Content column. Quick Keyword Classifier Screaming Frog Screen Now you can use the SPLIT & INDEX functions to extract the parts of the URL you need. SPLIT allows you to split a string into separate cells using a delimiter – in this case we will use the forward slashes in the URL. INDEX allows you to choose which of these cells you would like to return. The syntax: =SPLIT(text,”delimiter”) =INDEX(reference,[row],[column]) We can combine the two functions together as below: Quick Keyword Classifier Index Split Google Sheets Formula You can download this sheet below to make this easier: Quick Keyword Classifier Brand Model Extractor Google Sheet Just paste in your URL list from Screaming Frog. And specify which level of the URL you’d like to extract in the Brand and Model columns. Then jump into the tabs for a cleaned up version of the output. Quick Keyword Classifier Brand Model Extractor Google Sheet Tabs The “Brands” tab shows an alphabetised and de-duplicated list of the “Brand” column. The “Brands / Models” tab shows an alphabetised and de-duplicated list of the specified models and the brands they are associated with. What’s nice about this is, using the “Quick Keyword Classifier,” you can classify all of the “model” related keywords by brand, even where the brand isn’t specified! It’s not perfect, and you might need to clean the list up a little still, but it should certainly help speed things up. It already removes any of the following terms: Blog|Used|Guide|News|About|Contact|Us|- You can add additional terms you’d like to exclude in the “remove” row, separated by a pipe “|”. Quick Keyword Classifier Brand Model Extractor Google Sheet Exclude Filter When you have the list as you like it, you can copy and paste into “2. Auto Categorisation List” in the “Blueclaw: Quick Keyword Classifier” workbook. Quick Keyword Classifier Brand Model Extractor Google Sheet Keyword Grouping Once the keyword categorisation list is pasted in, the sheet will go ahead and categorise all of your keyword data using these terms. This may take 30-60 seconds as the formulas run through. 3. View Output Once the formulas have run through, you can jump into tab “4. Classified Keywords” to view an alphabetised list of the classified keywords. Quick Keyword Classifier Classified Keywords These are ordered by keyword category, then search volume, then keyword difficulty. Jump into “5. Pivot Table” for a view of the keyword data as a pivot table. Quick Keyword Classifier Google Sheet Classified Keywords Pivot Table This allows you to understand which keyword groups have the greatest monthly search volume associated with them. You can use the “+” to expand the group and see the keywords within it: Quick Keyword Classifier Google Sheet Classified Keywords Pivot Table Expanded 4. Adjustment (Optional) This stage is optional. If you’re happy with the output from the automatic keyword classification, then excellent. However, if there are a few keywords that have been erroneously categorised, you can jump into “3. Manual Categorisation” tab to individually re-assign these. Quick Keyword Classifier Keyword Categorisation Manual Override Anything in the “Manual Categorisation Column” will override anything in the “Auto Keyword Classification.” How does it work: The Google Sheets Formulas The bulk of the tool is built around one formula. This is as below: =ArrayFormula(IFERROR(VLOOKUP(REGEXEXTRACT(PROPER(A4:A),PROPER(JOIN(“|”,FILTER(‘2. Auto Categorisation List (Needed)’!B4:B105,’2. Auto Categorisation List (Needed)’!B4:B105″”)))),’2. Auto Categorisation List (Needed)’!B4:C105,2,FALSE),””) Let’s break down the functions used and how they work: VLOOKUP REGEXEXTRACT JOIN FILTER IFERROR 1. VLOOKUP The VLOOKUP formula allows us to vertically scan for a string within a list and return a value horizontally related to it (this is specified by the index column). The syntax: =VLOOKUP(search_key,range,index,[is_sorted]) The VLOOKUP is the primary basis for the formula. =ArrayFormula(IFERROR(VLOOKUP(REGEXEXTRACT(PROPER(A4:A),PROPER(JOIN(“|”,FILTER(‘2. Auto Categorisation List (Needed)’!B4:B105,’2. Auto Categorisation List (Needed)’!B4:B105″”)))),’2. Auto Categorisation List (Needed)’!B4:C105,2,FALSE),””)) We are searching within the categorisation list and returning an adjacent value. The nested REGEXEXTRACT formula is the search key – underlined above for clarity. The output of this formula is used to search within the “Keyword Search Phrase” column in the “2. Auto Categorisation List” tab and return the adjacent value in the “Keyword Category” column. Quick Keyword Classifier Keyword Grouping 2. REGEXEXTRACT The REGEXEXTRACT formula allows us to extract matching strings dependent on the specified regular expression. The syntax: =REGEXEXTRACT(text,regular_expression) The nested REGEXEXTRACT formula is as below: =REGEXEXTRACT(PROPER(A4:A),PROPER(JOIN(“|”,FILTER(‘2. Auto Categorisation List (Needed)’!B4:B105,’2. Auto Categorisation List (Needed)’!B4:B105″”)))) Both the “text” range and the regular expression are wrapped in PROPER; this removes any capitalisation from affecting the formula by ensuring all text is capitalised in the same way. The regular expression itself, as underlined above, is built using the FILTER and JOIN formulas. These provide the information and are separated by a “|” pipe. In a regular expression, the pipe symbol essentially means match “or”. For example: =REGEXEXTRACT(PROPER(A4),PROPER(“Bugatti|Vauxhall”)) The above formula would extract the first of any of the listed strings from the presented text. For example, running this formula against “vauxhall bugatti cars” would return “Vauxhall.” 3. JOIN The JOIN function is, as one might expect, a joining function. It allows us to concatenate an array of values, separated by a specified delimiter. The syntax: =JOIN(delimiter,value_or_array1,[value_or_array2]) The specified delimiter is the pipe “|” to act as the “or” match symbol in the regular expression. The array to be joined are the words specified in the “Keyword Search Phrase” column in the “2. Auto Categorisation List” tab. The output of this alone would look as below: amazingcarsite|amazing car site|abarth|alfa romeo|aston martin|audi|bentley|bmw|bugatti|citroen|dacia|ds|ferrari|fiat|ford|honda|hyundai|jaguar|jeep|kia|kuga|lamborghini|land rover|lexus|lotus|maserati|mazda|mercedes|mini|mitsubishi|nissan|peugeot|porsche|renault|rolls royce|skoda|ssangyong|subaru|suzuki|tesla|toyota|vauxhall|volkswagen|volvo 4. FILTER The FILTER function allows one column to be filtered by a condition of itself or another column. The syntax: =FILTER(range,condition1,condition2) The nested FILTER formula is as below: =FILTER(‘2. Auto Categorisation List (Needed)’!B4:B105,’2. Auto Categorisation List (Needed)’!B4:B105″”) It is used here to remove blanks from the list before concatenating the strings. The formula uses as the condition to specify “is not equal to” and empty inverted commas “” to specify blank. Thus, it returns values from column B where column B is not blank. 5. IFERROR The IFERROR function lets us specify another action if the initial formula results in an error. The formula is wrapped in IFERROR to show a blank cell if no matches are available. This makes the final data easier to read by avoiding an array of “#N/A” entries appearing between the successfully returned values. The final formula: Now we’ve broken it all down, the below formula should make much more sense: =ArrayFormula(IFERROR(VLOOKUP(REGEXEXTRACT(PROPER(A4:A),PROPER(JOIN(“|”,FILTER(‘2. Auto Categorisation List (Needed)’!B4:B105,’2. Auto Categorisation List (Needed)’!B4:B105″”)))),’2. Auto Categorisation List (Needed)’!B4:C105,2,FALSE),””)) You can download a copy of the sheet here to see it in action. If you’re interested in learning a few more formulas to help speed up your SEO work with Google Sheets, you can check out my previous blog post: 21 Google Sheets Formulas For SEO To Speed Up Your Workflow At Blueclaw we’re always looking for new ways to approach challenges; if you’re interested in our help for your on-site SEO strategy, then please get in touch.
We’d love to chat with you about your next project and goals, or simply share some additional insight into the industry and how we could potentially work together to drive growth.