Category: Keyword Research

Extracting Keyword Templates/Patterns from a Keyword Set

Extracting Keyword Templates/Patterns from a Keyword Set

When doing keyword research for programmatic sites, you shouldn’t be analysing each page individually.

You especially can’t be optimising an entire programmatic build page by page.

You should really be looking at the overall group, that each page falls into, and looking it as a whole.

How do we do this though? How can we best isolate the keywords that a programmatic system should fall into?

 

What are keyword templates/patterns?

A keyword template, or keyword pattern, is the overall structure of a keyword with dynamic elements removed.

So think about your keywords, without dynamic elements like property types, locations or something else.

They’re the core keyword with all that removed.

Some real estate examples are;

<propertyType> for sale in <location>

buy <propertyType> in <location>

<location> <propertyType>

You can’t primarily optimise for every variant of a keyword.

You’ll probably get one good option for a page title & H1.

They give you the opportunity to compare keywords like for like so that you know which one to pick for primary optimisation.

 

How to find your keyword patterns

Personally, the only tool I am using is Google sheets and keyword research data.

There are a few tools out there, but sheets give me everything I need to do this.

Before jumping into the below, I recommend you go check out my post/video on keyword categorisation here.

We will be using that same formula to loop through and make substitutions to our keywords.

 

Preparing your keyword data

The prerequisite to being able to do this, is having a keyword set with categorisation set up.

You should have sets of ‘find’ words, that are then bucketed into their categories, as we will be using these ‘find’ words to find the categories.

If you’ve already done keyword categorisation following my process, then you’re in luck! You’ll have pretty much everything you need already.

If you haven’t, then please go check out the categorisation setup here first. You can still do the categorisation in the process below, but that post will give you more information about the setup.

 

Setting up your keyword template replacements

For each categorisation set, you’ll need to add a third column. The new column is where the templated element will go, that you will replace the find word with.

For my example, when it finds a vehicle type it will then replace it with <vehicle>.

Any mentions of ‘car’ in the keyword, will be replaced with <vehicle>.

Any mentions of ‘truck’ in the keyword, will be replaced with <vehicle>.

etc.

Each version of a ‘find’ must be included, as that is what is substituted out when we create the template.

You can see in the above sample list that “cairns” and “airport” aren’t being swapped out.

‘Cairns’ needs to be added to the location list for it to be swapped. If you want to replace ‘brisbane airport’ and not just ‘brisbane’, then ‘brisbane airport’ needs to be added to the categories. It will need to sit higher in the list than ‘brisbane’, to ensure the entire location name is replaced and not just the ‘brisbane’ portion.

Adding a length, =LEN(x) can help sort the list by length.

Whilst you could create a fixed cell reference for each category, I figure this way left it open for easier modifications if you’d like to put a different template for different finds within the same category.

 

Extracting your keyword templates

As mentioned, the keyword template formula works the same as the categorisation formula.

It looks for the find word, and then replaces it with the template.

This formula is run in a new column for each category that needs replacement. My demo has 4 built-in, and can be extended if you need to replace more than 4 different categories.

The formulas essentially work just replacing one category set at a time. It will then use the previous category as the substitution source, building upon each as it goes.

The ‘keyword template’ column in G, grabs the fourth category replacement and brings it forward, so that we can hide the 4 actual replacements.

This way, you’re only left with the final keyword template visible.

So provided you’ve got your categories setup well enough, you’ll get some shiny keyword templates out at the end.

Even if it’s not perfect, as long as you correctly capture all the top keywords, that should cover the majority of the keyword set for keywords with the most volume.

Basically, you’ll still get a great insight with a minimal amount of work.

 

Analysing your keyword patterns

Throw your new data into a pivot table, pivoting the keyword template & the total search volume.

The pivot table will group all the templates together, significantly shortening your overall keyword list, and revealing your top keyword templates.

So from this fake sample data, we can see that our fake, sample users would prefer searching in the format of ‘<vehicle> hire <location>’.

This gives us a little better understanding of how users are search, across the entire data set.

 

Some little caveats

Each replacement needs to be listed exactly as mentioned in the keyword

Plurals will leave ‘s’ if the plural isn’t in the find. The formula will find the first one in the list, so any plurals (longer versions) should go to the top.

Must be ordered as required, multiple mentions within the same category don’t get included

Only the first one found will be used. Any plurals or longer variations of a find should go to the top to ensure the full word is replaced.

It could be extended to look for a second or third reference, but that might take a bit of work. A simple way would be to have a second category of the same keywords, sorted from shorted to longest rather than longest to shortest. Essentially a reverse search.

 

Keyword patterns with seoClarity

I came across this tweet being shared;

Whilst I don’t use seoClarity, and due to the price point never will unless a client is using it, this looks like a quick way to be able to understand your keyword templates.

So if seoClarity is in your budget, it could be a good option.

 

Using your keyword templates/patterns

Rather than optimising for keywords, with a programmatic build, you should optimise for your keyword templates.

Depending on your site structure, and inbuilt customisation available will depend on what templates you should look for.

For primary elements, you should be looking at your templates and comparing search volumes for templates like;

properties for <channel> <location>

or

<channel> properties in <location>

Then depending on whether you’re wanting to optimise for the highest volume, or the quick wins like low comp, you can then pick the correct template to optimise for.

I won’t go into keyword density and optimisation stuff like that, but you can also think about where you can use these templates on your page.

Other elements of the design could have the primary, or alternate, keyword templates included.

 

Different templates by category

A good example is comparing the top ‘rental’ keywords to the top ‘for sale’ keywords.

You’ll get something like;

rental properties <location>

vs

properties for sale <location>

as possible top keywords for each category.

You couldn’t simply target

properties for <channel> <location>

as you would end up primarily targeting

properties for rent <location>

and not the actual primarily search keyword of;

rental properties <location>

Purely an example, but you can see how this can change based on some categories.

The easiest way to build this would be to pick the one that most suits, and that’s how I would recommend an MVP be built.

You could then build it out with different templates for each category, and sub-category, to allow the primary targeting to include these differences.

 

 

Access my sample template

You can access my sample template at the link below.

Just copy the template over to your own Google Drive and you will be able to edit it.

Let me know if you have any questions!

Analysing Brand vs Generic Keyword Performance

Analysing Brand vs Generic Keyword Performance

Breaking down your GSC into brand vs generic keyword performance can give you a better insight into how an SEO campaign is going.

Most SEO activity won’t be affecting the brand too much, as it will be more to drive performance increased within the generic keywords.

Whilst you’ll get a flow-on effect, in that as generic performance improves, more traffic will then drive brand marketing essentially, increasing return visits via branded search.

That takes time though.

You’ll also want to monitor rankings separately, and rankings for brand-related keywords will bring down(up?) the average, and hide some of the generic keyword performance.

My first analysis like this many years ago took quite a while to figure out.

Now, they’re like 30 minutes.

Definitely a worthwhile analysis, considering how short they are.

What are brand keywords?

Brand keywords are any keywords that include your brand name.

This could include both brand + generic phrases, as long as there is a mention of your direct brand in the keyword it’s a branded keyword.

It also includes anything that should include the brand, and by that I mean misspellings. Whilst you might think there are 3 ways to spell your brand, I can assure you the internet will come up with 50 more.

 

What are generic keywords?

A generic keyword is any keyword that does not include a mention of the brand.

It’s basically the “everything else” bucket for this analysis.

There could be keywords mentioning other competitors, or other brand names from what you sell, but these will still be classed as generic (although, you could further categories these later to get additional insight).

 

Why analyse brand vs generic keyword performance?

There are two main reasons I like to do these analyses.

Brand vs Generic keyword split

Initially, they’re great to set the default pre-SEO brand vs generic split percent.

As your generic SEO activity increases, the assumption here is that the generic keywords will improve performance more than the brand. You would then see an increase in the overall % of the generic keywords.

You can then trend this monthly, and just add something else to the list to help show any performance increases.

Exclude branded keywords from GSC data

The other reason is to essentially just exclude brand performance from GSC data.

Sometimes brand performance, particularly spikes from campaigns, can mess with the GSC data.

Excluding these branded keywords can help you get a more accurate overview of ranking performance for the keywords you’re targeting.

 

How to do a Brand vs Generic keyword analysis

1. Use your favourite GSC data exporter to get a dataset of Query, Clicks, and Impressions. I personally use Search Analytics for Sheets, and would only select the latest 3 months of data, or even just the latest month if the sites big enough, to ensure that the analysis is with recent data. You could rerun with older data to compare.

2. Copy my Google sheet from the below link

3. Import your data into the rows highlighted below. If you have included CTR & Av rank then just ignore them, but most exporters by default will include that data so we may aswell save it for later.

4. Enter variations of you’re brand terms into the K column, and then mark them as ‘Branded’. These should be the shortest variations you can come up with, that still ensure generic keywords don’t get included. Leave the cells below it, as the formula will just fall back to ‘Generic’ when a brand seed word isn’t found. You should only really be adding 5-10 to start, and can come back and refine it to more later on.

 

5. (OPTIONAL) If you’d like to get deeper insight, add some categories & sub-categories into their columns. This will help you break the performance down by category, if that’s something you think would be useful for your brand.

6. Visit the ‘analysis’ tab to have a brand vs generic performance overview pivot table ready for you

One caveat to this is that due to exporting query performance data, you have a limited click count. Large portions of longer tail keywords are excluded from query exports, so whilst this % is a good indication, it should be used as only that and not a source of “truth”.

You will be able to see this shift over time though, as both branded and generic keywords will get excluded.

You can read more about that here under the ‘queries’ expander.

Monthly Brand vs Generic Keyword Analysis

This can be extended to include monthly data, and I might extend the Google Sheet later if I get a few asking for it.

Just throw on a month row, ensure your data exports are monthly, and then just add them all together in the same sheet with each different set having its associated month added.

 

Just another insight you can provide

Overall, this is just another little insight you can provide your clients, and hopefully, another metric you can just keep an eye on.

 

Bulk Keyword Categorisation & Classification

Bulk Keyword Categorisation & Classification

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 data in Excel 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.

Let me show you how you can categorise text with keywords in Excel, removing the need for another keyword categorization tool you need to pay for.

This is an older video, of the keyword categorisation process in Excel. The overall principles are exactly the same, however this one 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 categorise keywords in 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 categorisation formula & setup

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

Using a combination of regex & a textjoin, he was able to help tweak how the categories are applied.

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. This would allow you to view SEO performance by site section, based on grouping the URLs by folders, or any structure within them.

Just use the exact same formula, but rather than putting keywords in the ‘find’, add portions of URLs that you would like to group together. The formula will then put each of the URL structures together in to their groups, allowing you o view their peformance.

 

Let me know how you use this sort of sheet to categorize in Excel or Google sheets, or whether you do this all a different way.

Estimating SEO Traffic for a Keyword Set

Estimating SEO Traffic for a Keyword Set

Estimating your organic traffic will allow you better assess your current performance for a set of keywords, but also allows you to directly compare that performance with your competitors.

In this video, I run through how to properly set up your data and run the formula across a set of categorised keywords. This will be the basis of future videos, on how we can better use this data.

The estimated traffic is being worked out by multiplying the search volume of the keywords, with the expected CTR at their current rankings.

The formula I use for this in Excel is;

=IFERROR([@[Search Volume]]*(IFERROR(LOOKUP(9.99999999999999E+307,SEARCH(CTRModel16[Position],[@Rank]),CTRModel16[CTR]),”N/A”)),0)

Which is templated as;

=IFERROR([*CELL FOR SEARCH VOLUME*]*(IFERROR(LOOKUP(9.99999999999999E+307,SEARCH(*CTR MODEL TABLE RANK COLUMN*,*CURRENT RANK*),*CTR MODEL TABLE CTR AT RANK COLUMN*),”N/A”)),0)

Removing the bulk of it, we essentially have the search volume, along with a lookup for the CTR at that rank. The lookup could be simplified with a vlookup if you want to, however, I choose to just use my categorisation formula for this.

This can be simplified to just a vlookup for the rank table, especially if you’re using whole rankings rather than GSC average rankings that have a decimal place.

Estimating SEO Traffic in Google Sheets

You can duplicate a Google sheet below which is 95% the same as the Excel above to get you started.

Let me know if you have any issues, but it should just be plug and play with your keywords, rankings data, and any associated categories.

Bulk Generating Keywords for SEO

Bulk Generating Keywords for SEO

Discover the easiest ways to generate keywords in bulk using Excel. These formulas are the basis of so much work, and keyword generation really will just give you a top-level understanding of the formula’s use.

 

Concatenate – Join as many cells together as needed

=CONCATENATE(*First CELL to join*,*Second CELL to Join*)

Concatenation is a method that many people are using, and its just adding one cell to another. This can be useful to start things off, but there ampersand (&) does this too, a lot cleaner with a lot more flexibility.

So use this if you’re used to it, but I’d recommend switching to using the below ampersand (&) method.

 

& – The better way of joining items together

=*CELL to Join*&*CELL to Join*&”Some text to join on the end”

My preferred method, you literally just put an ‘&’ between each cell you want to work with.

So =A2&” “&B2 will output A2, a space, and B2. Nothing really complex about it, giving you full freedom.

 

Substitute – Swap text in a cell, for other text

=SUBSTITUTE(*CELL for text swap / the template*,*Old text for replacing*,*New text to replace with*)

Giving a little more customizability through template elements, the substitution formula lets you bulk substitute out specific dynamic elements into a keyword template.

So you could have a list of categories, products, locations, or really anything else, and just swap them out in a keyword template.

You can create keywords following a specific rule, and this tends to generate less keywords overall, with a higher percent of them having volume as you know the formats to follow.

 

Merging keywords with MergeWords

Mergewords offers a simple version of the formulas above, allowing you to just paste in the different parts of the keywords and output the merged keywords.

You can use merge words by visiting here and then just pasting each portion of the keyword in their respective box.

The big downside here is that you don’t have control about what version gets merged with what, like you do in the templated formula.

However, this offers a good method of merging many different keywords together, fast, and in extreme bulk.

 

 

 

Product Comparison Keywords: A foot-in-the-door SEO strategy

Product Comparison Keywords: A foot-in-the-door SEO strategy

Comparison keywords are an awesome foot-in-the-door SEO targeting strategy if you’re kicking off any sort of product-based strategy.

Many decent volume, low-comp keywords just sitting there for the picking and can be targetted individually, or in bulk.

Many people won’t even consider many of them!

My latest little project has even just cracked 270 clicks a day on the comparison pages alone… within 2 months.

Not a bad little start!

Comparison keywords search performance

As with all keywords though, there are some that are easier than others.

How do we find the keywords?

How can we best target them to make the most of the opportunity?

Who’s doing it well?

Before we get stuck in and take a look at a few options, I just want to explain a little about these comparison terms.

 

What are comparison keywords?

As the name suggests, they’re simply keywords that a consumer will search when they’re looking to compare products.

Two of the main comparison keyword formats you’ll see are;

Product 1 vs Product 2

Product 2 or Product 3

And then now and then, you’ll find yourself an elusive three-way comparison that could be worth targeting;

Product 1 vs Product 2 vs Product 3

But, what can we deem as a “product” when it comes to these comparisons?

Anything.

These aren’t limited to products of the standard physical nature found in a store.

Which leads me into who can target these comparison keywords.

 

Who can leverage product comparison keywords?

Many will initially think Laptops, Tablets, Phones etc and all your other gizmos, gadgets and anything you walk into a store to buy.

But they go so much further than that.

Affiliate marketers, media companies, business owners.

There are plenty of options to jump into the comparison ring and knock out some rankings.

So let’s take a look at a few of the options.

 

Ecommerce / Traditional Product-based Websites

The traditional ecom / product-based websites are the default go-to for the usage of comparison terms.

Whether selling laptops or washing machines, phones or vacuums, cars or blinds, consumers are probably looking at comparing that product to something else before they buy.

Someone looking at grabbing an XPS laptop could be searching;

Product comparison keywords example 1

Albeit a smaller volume, you’ve also got the category/subcategory/type comparison keywords that fit in here, with keywords like;

Product comparison keywords example 2

These have a much-limited scope, so normally are reserved for these higher volume product-based comparison terms.

However, still worth looking into their value for you.

 

SAAS / Software Businesses

Many software companies get stuck with where to start their SEO strategy.

Most will tackle a content strategy, filling up their blogs with general content that fits their niche.

Which is great! But… they’re missing out on potentially closing an audience that’s a bit further along their funnel.

Users that are just doing some final comparisons before they pull the trigger on their chosen software.

Wouldn’t you want to ‘sway’ the conversation with some favourable data-points showing you as the #1 choice?

Let’s check out what people might compare one of the top mailing software with…

Product comparison keywords example 3

But guess what? Mailchimp is nowhere to be found for these keywords…

Except for one.

Mailchimp comparison example

Klaviyo is also running the comparison back to them.

This is the only comparison they appear to be running.

Mailchimp is leaving out so much opportunity where they could be controlling some of the comparison conversations for the other comparisons too.

 

Service Providers

Looking at a new electricity provider? Or a new phone company?

What about a new internet plan?

Well, even if you’re not, there are thousands of people that are, every single month.

Product comparison keywords example 4

What about a new web hosting plan?

Product comparison keywords example 5

 

Location

Looking to move house?

What about planning your next holiday?

Once you lock down a few options, you probably wanna weigh them up against each other to see where could be best for you.

Product comparison keywords example 6

Websites in travel could really be getting in on this to grab some potentially easy conversions.

Even the official tourism groups of the locations should be jumping on this, to try to swing it in their favour.

One big callout here though, be careful of sports teams, or other things that use the location name in these comparisons. Your volumes will be thrown out the window.

A quick Google search can confirm Google’s intent from the keyword, and avoid you wasting your time.

 

Sports

This is one of the categories where things get interesting.

A majority of the volume for sports-related comparison terms will come down to users trying to find out information about an upcoming/completed match between the teams.

However, there is one other big share of this market that is worth taking a stab at.

Consumers search these keywords to work out who could win, should they ever play against each other.

Some random American hockey team still sits at some pretty high volume;

Product comparison keywords example 7

One even includes ‘tickets’ which can show the intent here.

Correct targeting by a sports team could help them grab some of the SERP real estate for these keywords.

Correct targeting by a sports website, could even steal some keywords from the larger players.

 

General Businesses

You really start scraping the barrel here, due to the localised nature of general small businesses.

But, the keywords are still there. Hiding away, waiting for you to find them!

Product comparison keywords example 8

Never know, your business/businesses in your niche, may have some comparisons waiting for you to leverage.

 

Finding the keywords

How do we actually go out and find the keywords though?

There are two tools I use for this, but both involve a little pre-generation of your seed keywords.

It all starts with a list of the products you want to compare.

If you’re only looking to try find comparisons for a single product, or a single name, it’s easy!

Just do;

$name$ vs

So;

Product name vs

You can then try to look at any variations of the name.

For a basic product, I’d start with

$brand$ $productName$ vs

$productName$ vs

That get’s whacked into the tools, and hopefully, you’ll find yourself some nice comparison keywords to play with.

However, for the people that are looking to find comparisons they could do, from a larger list, you need to;

  • List out all your product names in Google sheets
  • Add any variations to the list
  • Append “vs”, “verse”, “or”, or any other extension you’d like to search for
  • Copy all into a single list

This can all be done pretty quickly in Google sheets;

Generating product comparison keywords with Google Sheets

From here, we just need to paste the ‘C’ column somewhere, to remove the formula and avoid mishaps.

 

Finding comparison keywords with ahrefs

Being one of the obvious larger keyword research tools around at the moment, ahrefs offers something that many tools can’t.

More keywords!

Which is exactly what we need for this.

ahrefs has a ‘phrase match’ keyword research option, which will return any keywords in their database that contain the inputted seed words.

Just access their keyword explorer, paste in 10 seed keywords (their max for phrase option) and then click “phrase match”.

ahrefs phrase match example

This will hopefully come back with plenty of the top comparisons for you to look at!

Using my XPS example, I can see that I should potentially be looking at creating comparisons for hundreds of different combinations!

If you’re working with some higher volume products, you’ll find plenty of comparison duplicates, with alternate names.

Read on for something I’ve shared below, to help analyse your list.

 

Finding comparison keywords with Google Suggest/Autocomplete

The other method of finding comparison keywords is to use Google’s autocomplete feature.

Google autocomplete suggest

This will give you hot comparisons to use. Hyper dynamic keywords, that will popup well before any keyword research tool’s database will grab them.

Yeah, my example doesn’t do this justice though, as the XPS is a popular laptop!

But how about a less popular model.

If we take a look at one of Asus’ newer laptops, one I am actually hoping to buy as soon as Australia has some more stock, we can see a different story.

ahrefs phrase match

Google suggesting comparison terms

Look at all that magical autocomplete data!

ahrefs had nothing.

I wouldn’t expect it too either. It sometimes takes months and months before keyword tools will collect see these keywords.

Simple typing the product names into the Google search bar is a quick and dirty way of grabbing these terms.

However, I prefer to use a tool as I can grab some search volume in the process, should it be available.

Keysearch is a tool I’ve used for a couple of years, primarily because they come out with some awesome discounts now and then, and have decent enough data for it.

Type in the keyword, one at a time for autocomplete usage, and then click ‘Google suggest’.

keysearch

Filter the keywords to contain your comparison phrase, in this case, it is ‘vs’.

It will then spit out the same autocomplete keywords as before, and a few more, for you to get stuck into.

Repeat the process for each of your seed keywords, and it will give you the comparison terms along with any volume or CPC data the tool has.

If you’re really not fussed about the extra data, jump into Keyword Shitter and throw in all your seed keywords at once.

keyword shitter comparison keywords

Add all your comparison phrases in as positive filters and then you can go ahead and keyword shit yourself silly!

 

Zero search volume does not mean zero search volume

Keyword search volume is derived as an average of the previous 12 months of searches for that keyword, should tools actually have that data.

When products are released, it could take months before a tool even shows the first month of volume for it.

Any comparison you’re doing for a new product is pretty much guaranteed to have no volume.

You will also find many comparisons that will show zero volume, even though they are older.

Due to the lower volume, lower comp, nature of these keywords it could still be worth creating the comparison.

Considering how easy some of these pages are to create, even if it just helped make 3-5 conversions over a 6-12 month period, what’s that worth to you?

For some, it’ll be worth plenty.

Need proof of their value? Fine.

Here are some stats of a single, shiny comparison page;

74 total keywords showing up in GSC for this page, with the top one being almost 50 clicks, over the 6 week period.

How do these keywords look in ahrefs though?

Like absolute garbage – as expected haha.

Ahrefs didn’t even have 2/3rds of the keywords in their database!

Two keywords show a whopping 30 combined volume, yet they’ve driven a combined ~200 clicks for the month.

Rarely, will someone target a set of “zero volume” keywords because of this.

Comparison keywords are where I’d recommend that be a bit different. They’re worth the “zero” volume.

That’s because that zero search volume does not mean zero search volume.

 

Matching & extracting the product names from the comparisons

If you’re working in bulk, the generated keywords can sometimes be a bit of a pain as you might struggle to match up the product names.

There could be many names for a single product, and individually matching these would be painful if they’re across lots of comparisons.

Well, it was a pain for me anyway – so maybe this will help you out too!

Duplicate My Google Sheet Below

It’s a bit of a behemoth… but I wouldn’t have bothered doing it if it wasn’t going to turn out that way :P

1. Paste in all your keywords & volumes

2. Scroll across to the right and paste in your product seed words, along with their associated product names & IDs

The ‘seed’ is a word/phrase within the keywords, that would align it to the specific product. So for me, ‘xps 15’ relates back to the Dell XPS 15.

3. Drag down the formulas, into any empty rows you have

The formulas will now run and match any products within your comparison keywords, to the seed list you provided.

This is a two-step process, where you’ll find that ‘model 1’ and ‘model 2’ will be broken up by either “vs”, “verse”, “or”, or “compared to” as the comparison text in the middle.

If the comparison keyword doesn’t contain one of them, the formula will break. You should be able to adjust the formula to suit your needs, and if not, just comment and I will try help you out :)

Once broken down, the formulas will categorise the two models, using the seed keyword list you provided. It will just look for the seed, inside the keyword, and then match it up with the given product.

If the keywords aren’t being matched to the products, you will need to build out your seed list to include the missing seed words. This means you’ll just need to add the alternate names for a product, until the formula can correct match them together.

Model 1 assumes that you know the exact model name before the ‘vs’ because that’s what you would have been researching.

However, model 2 tries to match two different ways.

There’s the same ‘exact’ match of the seed as Model 1, but then there is also a partial match.

If I delete the seeds for ‘xps 15’, and ‘xps 17’, you’ll see that the partial match still matches these to keywords to their correct model.

This is because the partial match formula is based on a wildcard match, and will match ‘xps 17’ inside the seed of ‘dell xps 17’.

The final column will then look for an exact matched product, and if one isn’t there it will look for a partial match.

Sometimes the partial match can go haywire though, so that’s why it’ll prioritise an exact match where possible. It’s also best if you manually check the partial matches, as you never know what it’ll match up to.

Duplicate My Google Sheet Below

Prioritising the comparisons

If you’re only working with a few comparisons, just stick to however you normally prioritise SEO stuff. Usually just by a mix of search volume & competition.

But, if you’re working in bulk you’d probably be better off running your keywords through this sheet.

Provided you’ve done the above instructions on setting up the sheet, you’ll just want to load up all your product names, with their combined search volume on the far right. Whether this is volume from one keyword, or many, it just needs to be one total volume per product.

The sheet will pull in this product volume data, and display it next to the match model names.

And this is where things get interesting.

The formula in the sheet, will look at the volumes for the two products being compared, and then optimise the ordering of the comparison to ensure the highest volume product is first in the order.

Might not be helpful to everyone, but it was something I wanted to use, so you get it too :)

In the above, even though the XPS 13 came first in the keyword, the dataset says that XPS 15 had a higher volume, thus putting it first in the calculated comparison.

You can just kill off the search volume for the products if you don’t want to use this feature.

Individual comparisons will then be prioritises by their search volume with the ‘comparison volume’ column aggregating the search volume of any comparison keywords with their associated matched comparisons.

All the duplicate comparisons will have their search volume aggregated from the unique comparison keywords that were matched to them.

Search volume for any time XPS 15 was compared to XPS 17 gets aggregated into the “Dell XPS 15 vs Dell XPS 17” comparison.

You’ll then be able to sort all your comparisons by volume, to work out what to target first.

Duplicate My Google Sheet Below

How to properly target comparison keywords

I’ll keep this bit brief, as you’ll see a few different ways to target them below. Also cant give you alllll my secrets ;)

There are two mains ways to target them though.

Dynamically and manually.

Dynamically will leverage a dataset and let you target the keywords in bulk, giving you the option to build out and optimise the content further as they grow.

Manually means you’d be writing out the content for the pages individually as you go.

Both have their own merits, but if you’re looking at targeting more than a handful of comparisons at a time, some sort of dynamic solution would be best.

No matter the strategy you’re taking, there are a couple of things that should really be included in these comparison pages.

A nice and simple table/chart showing a direct comparison of any features/specifications of the products, and then a summary of the strengths and weaknesses of each product off the back of these.

At its core, that’s what you’d need at a minimum. The rest is SEO.

 

Good comparison examples

Who does this well?

There are soooo many examples of comparison pages – particularly in tech.

The best examples, are looking at the keywords you’re trying to target though. Nothing better than leveraging what Google already loves, for what you’re targeting.

You don’t need to be as good as the overall best, you just need to be better than your direct competitors.

Apply some of what you can learn from the overall best to your market though, and it should help you out!

 

Versus.com

These guys are definitely one of the “best in class” comparison websites… you’ve probably never heard of them though!

Tech, Sports, Food, even dating apps!

423k out of their total 493k keywords are rankings for URLs with “-vs-” in them.

That’s their comparison pages.

They’re absolutely killing it.

versus comparison example

They are definitely one of the “prettier” comparison websites floating around and give some great breakdowns of the individual features of each device.

 

CameraDecision

Can you even guess what they compare? Yeah, cameras, and they’re killing it too.

Not as high as they used to sit, but they’ve managed to keep themselves pretty stable over the last couple of years.

CameraDecision example

They’re just keeping it simple though.

Overall summary, key specs of both, and then the side-by-side features comparison.

 

RTINGS.com

These guys do a lot more than comparisons, but they’ve got some great comparison pages here!

rtings example

Simple summaries, with some awesome side-by-side comparisons of specs and features.

Not much text, but they’re covering that on their reviews side. These are just their pure reviews.

They’re ranking well with them too!

 

HostAdvice

Webhosting is a tough niche to crack. There are some prime commissions available for those that do though!

Host advice example

Bit of a slap from the May update it seems though!

These guys have cracked one of the largest web hosting comparisons they can – Godaddy.

HostAdvice comparison example

They’ve covered so much here, but as you’ll see, it’s just a giant comparison table!

A lot of work goes into the data powering this page, but at its core, it’s just that – data.

 

Why wouldn’t you target comparison keywords?

There are a few reasons I know of, of why people might not target these keywords. All completely understandable, and up to you if you see them as blockers.

But hey, you never know until you give things a go sometimes!

 

You don’t like free traffic from brand jacking

Has anyone… ever… turned down highly targeted free traffic before?

Probably, but I wouldn’t be. I’d be milking that traffic as much as possible!

Comparison keywords are great for when people are looking for those comparisons, but, there’s something that I haven’t mentioned yet.

These pages will also rank for non-comparison terms.

Imagine being a brand new email platform, creating a comparison of you vs MailChimp, but then ranking for keywords like “MailChimp features”, “What is MailChimp used for” or even “MailChimp cost”.

This is known as brand jacking, and it’s a great way to get your brand in front of a highly targeted user-base!

You probably won’t be able to jump out the gate with such a known brand, but there are definitely some competitor terms you could get in there with, stealing yourself some nice SERP space.

Brand jacking is also a perfect segway into the next reason…

 

Legal stuff

Probably one of the biggest is the fear of being sued. If you’re outright comparing yourself to your competitors, and it’s favourable for you, they’re obviously not going to like it.

They’re especially not going to like it if you’re ranking at the top of Google for keywords including their brand name, or worse, their brand name by itself.

I have pretty much no clue about this side of things, so anything I say about this stuff is further from advice that it could possibly ever be, but what I know is you must avoid anything slanderous.

Stick to what you know. Talk yourself up, but don’t slander or negatively talk about the competitors.

Let your numbers & features do the talking.

 

Biases

Users will often prefer third-party, unbiased reviews.

If you’re not third-party and you’re just comparing yourself to competitors, consumers may not take to that favourably.

However, it’s still worth jumping in there with your say. Something is better than nothing!

 

Lower volume markets

The volume is so low, it might not be worth the effort for some.

Yeah, this is completely reasonable.

But, you never know until you try!

At least dig in, see what you can find, and give it a shot. Do a test run with a few, see how they perform, and go from there.

 

Unknown brand

Following on from lower volume markets, if you’re a brand new SAAS and no one knows your name, you won’t benefit from these comparison terms like others will.

Why? No ones searching your brand, so no ones searching for comparisons.

As you grow, they will though.

And if someone comes across you, they might want to see how you’re the better option anyway, so I’d say it’s still worth getting in early here.

You might even just rank for the competitors brand names without anyone searching the comparison anyway ;)

 

What’s stopping you?

Don’t see the value?

Can’t find any comparisons you’d want to target?

I’d love to hear if there are any reasons holding you back.

Hit up the comments with any comparison feedback, especially if you’re successfully targeting them already!

Building a Keyword Research Dashboard in Excel

Building a Keyword Research Dashboard in Excel

An extremely large table of all their keywords, isn’t going to give the insight some clients would want. Some would like more a bit more insight into the data they’ve paid thousands for.

Building a keyword research dashboard in Excel is a great way to allow a client to interact with their keyword data.

In this short video, I will show you how you can take a categorised keyword list and turn it into a client-friendly dashboard!

The video is a couple years old now, and I may eventually update it, however, for the most part, it still holds true today.

Now, this won’t be great for some clients. You could end up giving them way too much information, and have them asking way too many questions.

However, I personally don’t mind that. I love when clients have an interest in SEO as it gets them more involved in the project, and more excited when the results kick in.

How to build a keyword research dashboard in Excel

  1. Categorise up your keyword research data
  2. Create pivot tables for each category level
  3. Attach slicers to the categories so you can filter the data
  4. Design the tables & slicers to match your client’s branding

And there you have it, you will have an interactive Excel keyword research dashboard to send to your clients.

Creating an online keyword research dashboard

You can save this Excel dashboard to Onedrive and then share with your clients via a link instead of sending a file.

For dashboards under a certain size this means they will be able to view the dashboard online instead of having to download the file. You can then simply update the keyword research file, and they will be able to see the update when they refresh their browser.

This becomes a massive help when trying to send updated data to a client. No more v53 tacked onto the end of a file because you’ve updated it 53 times!

Download the Excel Keyword Research File

If you watched the video and can’t be bothered doing the work yourself, you can just have my file!

Or if you’re doing keyword research for car hire you can also just steal my keywords – chances of that have to be slim.

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

 

Don’t forget to add some proper styling to the file though! She ain’t exactly pretty at the moment.

If you have any issues with the Excel file, or just need a hand with anything from the video, just leave me a comment below and I can help you out.

10 Years of Keyword Research and What You Can Learn From It

10 Years of Keyword Research and What You Can Learn From It

I’ve been doing SEO a little while now on and off over the years (but mostly on) and finally getting back into it full time now.

Over time, I have learned quite a few little handy tricks doing keyword research that has helped me save countless hours… and many weeks!

I figured it was about time to share my little keyword journey and hopefully, you can leverage my wasted time and speed up your research.

Now, I am not going to spend time explaining some stuff, so if you haven’t done keyword research before you should definitely read the Backlinko guide. Some of the post contains basic information, but much of it will only slightly touch on the basics so it’s best you read those guides beforehand.

 

Niche/topic planning

This is where I started back then, and it is where I start now.

The initial topic planning has barely changed for me over the years, as I am sure it hasn’t for many other people.

It does not matter how smart an automated tool is, it will never be as good as you just jotting down some ideas on a spreadsheet.

Back then, I used Excel. Now I use Google Sheets where possible. That’s pretty much the main difference for topic planning!

 

Google Sheets for niche & topic planning

The first part here is just jotting down all the ideas you can think of off the top of your head.

Each column should be separate subtopics, all sitting under a parent topic.

Using the video game niche as an example, this is what my planning would look like;

Google sheets niche research

This would be duplicated out based on every topic and sub-topic I can think of at the time.

The idea here isn’t to cover all the topics within the niche, it’s just to come up with what you can think of so you can build it out later.

So get your research started cleanly.

 

Finding additional topics by analysing competitors

I then try and find new topics along with variations of the existing topics that you think are worth finding sub keywords for.

Just start Googling all your topics and see if anything stands out, then either add it to an existing topic or create a new one.

You should be able to get a few variations just by looking at the SERPs, but jump into the competitor websites and check them too.

Pick up any similar keywords/namings to the topics you have, along with any topics you completely missed.

It doesn’t matter how well you think you know a niche, unknown sub-niches are just a few searches away!

 

What you’re looking for

The aim is to find the gems that an automated tool might not be able to pick up for you. With a secondary goal of starting to build out some top keywords.

Continuing with the gaming niche research as an example, we can take “ps4” and immediately see it’s also known as “playstation 4”. Whilst Google, and many tools, will see this as the same it is still worth jotting it down as the acronym and full spelling might not always be picked up as being the same.

Breaking out the ‘PS4 Consoles’ topic, I quickly come up with;

PS4 Consoles
Playstation 4 Consoles
Playstation 4 Slim
Playstation 4 Pro
Playstation 4 Bundle
Playstation 4 Deals
Preowned Playstation 4

It used to be a lot harder to get keywords like ‘playstation 4 pro’ out of ‘ps4 consoles’, but with the tools available these days it’s so much simpler.

The improvements Google has done on related searches, you can just search for ‘ps4 consoles’ and it’ll show ps4 pro right away;

Google related searches to keyword

But I will go into that in a bit.

Just make sure you get as many different variations of your topic as possible so that it is easier to leverage tools to expand on your seed keywords.

Expanding on topics and finding longer tail keywords

Once you have the initial topics mapped out, you need to start on expanding the keyword set for each of them. This will help you better understand the highest priority keywords so you know what to target on your website.

On top of this, you’ll be able to better understand the entire niche, rather than just a tiny keyword set, which will help with performance analysis and understand where you might be lacking.

 

Google Keyword Tool (now planner)

Finding keywords with Google Keyword Tool

In simpler times, the Google keyword tool was a real goto for my research.

For one, it gave great search volume data.

But its strength was the little “Match Types” selection on the left-hand side. You could actually get Google to come back with suggestions based on Broad / Phrase match, or just get the data for keywords you entered into the list with the exact match.

I would go through topic by topic, and add related keywords to a list, which could then be bulk exported keeping each keyword assigned to the list.

None of this range data, but especially none of the volumes for similar keywords being merged as they do now.

The newer Keyword planner is certainly not at the top of my list as a go-to anymore.

 

Google Autocomplete

Keyword research with Google autocomplete

Originally launched in 2004, Google autocomplete has been an invaluable resource for many SEOs for years.

Google literally gives you the longtail keywords that expand upon the keyword you’re searching for. Use them!

At first, just manually grab them. Do a few manual searches so you can see how it all works, then move into the automated tools to scrape it.

 

Google related searches

Just like Google autocomplete, the related searches offers so much value to SEOs.

Related searches used to be very similar to the autocomplete, and didn’t provide too much value.

Old google related keywords search

However, in 2009, Google added some extra intelligence to them and updated their design.

 

Updated google related searches

This was the first version of intelligence beyond the autocomplete and would be the start of many updates they have since made to it.

Took me a little while to get onto it, but they’ve proved invaluable since.

 

Keyword research tools to assist keyword generation

Over the years I have used quite a few tools to grab me the keywords.

Initially, I was rather cheap, so just stuck to the Google keyword tool. Took a bit longer, but at least I had somewhat quality keywords in there.

After a year or so I finally threw some money at a few tools to help with my research.

 

Scrapebox

Keyword scraper with Scrapebox

Scrapebox was the first research tool I grabbed back in 2009 with a nice little discount off BHW (Blackhatworld, back when it was cool… I think?).

Not initially for the keyword tool, but it soon proved rather useful at building out a nice list.

You can basically just plug a few seed keywords in there, select how many levels deep (awesome for super long keywords), and then click start!

The tool will use a variety of sources and grab you all the long tail keywords you ever need.

I used to just plug all my topics in one by one, along with any keywords I had already found for them, and then let scrapebox do its thing and generate my keywords.

However, there is one catch. You get A LOT of data. What you will need to do is seriously filter this stuff down.

Still a viable tool, and you can grab it here.

 

Market Samurai

Using market samurai for keyword discovery

 

I think Market Samurai was launched in 2008, but I grabbed it in 2010. This tool started to change how I looked at SEO.

It was the first tool I used that started to make me look at SEO a little bit deeper than just a few keywords and some automated link building (2010 SEO was fun!).

Market Samurai had me starting to spend more time on the keyword research, and I was looking at competition in a niche, rather than just trying to rank for anything.

The site still works, and I apparently still have access to the tool, not sure if it still works though. Better tools around these days.

 

Uber Suggest

 

Researching keywords with Ubersuggest

 

Uber suggest launched around late 2009 / early 2010, and quickly became rather useful for discovering new keywords.

Just like the other tools, you throw in a seed keyword and the tool will quickly spit out hundreds of longtail keywords for you to use.

Neil Patel recently-ish acquired Uber Suggest and has been making some updates that have only made the tool better, and at no cost.

I haven’t used the newer version too much, but can safely say I got plenty of value out of the old one!

Give the new tool a try here.

 

Longtail Pro

Longtail Pro was released back in 2011, and slotted itself in as a “new shiny” for me for a while.

I didn’t get massive amounts of value out of it just because I had a handful of other tools that were giving me what I needed at the time.

But who doesn’t like shiny new tech?

It has since been improved upon, grown in features, and can still help many people out.

The original creator sold the software a couple years ago but you can still pick up the software here.

 

SEMrush

SEMrush keyword research

I used SEMrush for years. It became a staple for me… until they started messing with their pricing and removing my API access.

I had a little Google Sheets setup that would hook in with the API and scrape me heaps of keywords & rankings. This let me do heaps of research fast and was within the credit counts that came with my basic subscription. They then removed the API access for this level and jacked up the prices.

Completely understand the business move, and I am sure it worked well for them, but just meant I needed an alternative.

Still worth checking out, as you may enjoy it more than alternatives. They’re still releasing new features you can jump on and you can still get some great value out of downloading the keywords for a domain / URL.

 

keywordtool.io

longtail keywords with keywordtool.io

Once SEMrush didn’t prove valuable for me any more, I turned to KeywordTool.io.

It quickly became my go-to research tool for a couple years due to its speed, simplicity, and filtering rules.

I used the pro version from around 2016 to early 2018, and just loved the fact I could have separate users & share access with outsourcers/mates.

Jump in here and let me know what you think if you haven’t used it before.

 

Ahrefs

It’s safe to say ahrefs is pretty epic. Epic in what you can get out of it, and epic in pricing too. Not recommended for early SEOs, but certainly should be on the list once you start getting work or making some money!

How do I get the value out of it for keyword research?

By stealing competitors keywords!

Keyword download with ahrefs

Just throw in a competitors domain, or the URL path directly related to your niche, and you get a massive list of keywords that you can start to filter through! Do it for the top 10-20 sites, and it’s pretty safe to say you’ll cover a significant chunk of the niche.

Worth a buy for just this, but only if you can actually afford it! Don’t raid your rent money for it. You can get by without it.

 

Keywords Everywhere

Related searches & volume with Keywords Everywhere

I think every SEO under the sun uses this free chrome extension now.

It isn’t a tool that I used very early on in it’s release, and have only just started using it properly, but whenever you Google something the tool will show you related searches on the right.

This significantly helps when expanding out your topics, and you can quickly export all the keywords and their volumes pretty easily.

Give it a crack here.

It does get a bit annoying through so I tend to turn it off regularly.

 

Keysearch

 

 

I have recently switched over to Keysearch for my research (grabbed a Black Friday special… so I am set for a year!).

Friendly interface, and fast keyword selection. I can just tick on the related keywords I like, and then save them to a little list.

A drawback here is each list needs to be exported separately. Takes a couple extra minutes to do that, then a couple minutes to re-merge, but I am happy with that for now.

This is ideal for generating an audited list of related keywords pretty fast, saving you on the cleanup later on.

Definitely worth it for newbies and more advanced users alike.

Give it a test run and jump into the tool here.

 

KeywordKeg

Using keysearch for keyword research

I only played with Keyword Keg for a little bit, since I grabbed Keysearch instead. Definitely one I will be keeping an eye on, as I could switch over at some time in the future.

For now, I am pretty happy using my current toolset.

Still worth having a play with Keyword Keg though, and you can check it out here.

It’s made by the guys that did Keywords Everywhere.

 

Getting the search volume

I have always used Google for my search volume.

The fact they’d allow an upload of 3,000 keywords via csv at a time and then offered a nice CSV export just had me sold from day one.

However, as many of you would know Google did this stupid thing of combining all the volumes for similar keywords.

keywoord search volumes for melbourne plumbers

So even though each of those 3 keywords would get searched a completely different amount of times, Google will now aggregate their volumes into a single one.

Which is an absolute pain if you use search volume for anything. You either need to use an alternate source or leverage one of the third party tools and their estimations if you want to separate out the volume for these keywords.

I personally prefer to try and remove all these similar keywords and just focus on the primary one, as that allows me to continue to use Google for the volumes.

Why? I use the search volume for a heap of different analysis & performance monitoring. You could still get away using this, but you will get inflated “estimated traffic” values, and some other metrics that might be more off than normal.

Google keyword planner upload

Unfortunately, Google killed off the 3,000 keyword limit upload via CSV in the latest planner, and also made it slightly easier uploading in that you can just paste a bulk keyword set in (used to be an 800 limit via pasting).

But there is always a catch!

The new keyword limit appears to currently be 2,500.

Still workable, just a pain.

Oh, and you need an active Adwords account to even get a somewhat accurate volume these days. So if you don’t have one of them, or can’t access one through a client, it’s probably best you look elsewhere for volumes.

 

Playing with large location-based keyword sets

Things really changed for me process wise when I jumped into agency life in 2013. Moving interstate and re-settling was nothing compared to changing from small business clients to some rather large businesses!

I went from helping a plumber to working with large international hotel chains.

I went from a home-based cupcake business to ranking a national car rental agency.

But most importantly, I went from a few hundred keywords, to keyword sets of 100,000+.

This forced me to rethink how I did SEO… and this is where the real time savers kick in!

 

Bulk generating keywords

When I was starting agency side, I could use Excel but I certainly didn’t have any sort of advanced skills.

I quickly saw how much time I could save with some of the processes, and began learning more about it.

The biggest thing I was using it for at first, was bulk generating keywords.

I had a handful of keywords, that I need to replicate across multiple locations.

 

How I started generating

So, before I played with Excel too much, I did a pretty manual thing to swap out locations in keywords.

I used to find/replace a location out of the keyword.

Find and replace location keywords

Hey, it worked and got the job done! Once I started playing with 10+ locations and larger keyword sets this got really annoying.

I needed a different way of doing this.

 

Along came concatenation (my version of it anyway)

Concatenation is basically just joining things together, but I started doing concatenation before I even knew what that meant.

Within Excel, you can use the ampersand (&) as a magical way to join things together.

You can also use a space between two quotations to insert a blank space into your formula.

By leveraging these two things, I was able to merge together different cells to generate my keywords.

Concatenating keywords in Excel

The formula for my above example is;

=$A3&” “&C$2

It allows me to tack on the location to the end of the keyword template, and create me the keywords I need.

The $ symbol locks in a cell reference. So by locking in the first column (the keyword ‘template’) and the 2nd row (the location), I am able to drag the formula down and across.

Excel actually has a formula to do this;

=CONCATENATE(text1, [text2], …)

To this day, I don’t know why you would use this over the ampersand though. The ampersand version is just so much more flexible, and easier to maintain as you build out your formulas.

Would love to hear of a usecase if you have one though!

 

Substitute yourself to local keywords

You might notice in my concatenation example I had “car hire” and “car rental” templates.

This is because I would like both ‘car hire sydney’ and ‘sydney car hire’, but to do this with concatenation you would need to modify the formula.

This is where some substitution magic comes in!

=SUBSTITUTE(text, old_text, new_text)

This formula quickly became how I generated location keywords and is still used in one form or another today!

It looks inside a piece of text, for specific text, then replaces that specific text with whatever you tell it to.

Instead of adding a template and location together, I used;

Car hire LOCATION

LOCATION car hire

And just swapped out the location for the location I wanted to use.

Substitution formula for keyword generation

The formula to swap the text LOCATION for the location is;

=SUBSTITUTE($A3,”LOCATION”,C$2)

And to take it one step further, you can even replace ‘car hire’ with SERVICE, and then add that to the substitution formula.

This could also be modified to suit any sort of keyword, so you could easily tack on modifiers if you wanted to. No reason you can’t substitute out 3,4, or 5+ words.

I made a video about it a couple years ago, so this might be a bit more useful :)

The issue when you start to do that is that you end up with too many different variations, and are just copying and pasting your formula all over the place.

Which is where I started learning about macros and how to repetitively do something I hated doing.

 

My excel keyword builder (magic macro)

Keyword builder in Excel

Created in 2014, this magical thing would cycle through keyword types, keywords, locations & even secondary locations, and bulk generated me a tonne of keyword variations.

The macro died off a little while ago, well something broke and I can’t be bothered fixing it, but saved me many hours.

Back when Google gave a good volume value, this worked best, as it let me easily identify the top search terms to target.

It sometimes took a little while to generate, and it was hard to get setup initially due to the macro.

So I needed a new plan.

 

IMforSMB Keyword Tool

I’d always wanted to learn a bit more about coding, and so in 2015, I decided to take my Excel tool and build out my own web tool.

Years of playing with WordPress gave me a decent enough understanding of HTML & PHP, so that was my obvious choice for this tool.

After about a week of hacking, I finally pulled something together.

IMforSMB Bulk Keyword Generator

This completely replaced the Excel generator for me.

Using the DIY Generator of the tool, I can now just paste in services & locations and click generate and the tool will spit out all my keywords.

IMforSMB search term generator

I threw in some templates services for people to use the builder if they’d rather, but my main focus here was just the simple generator so that I could let my Excel sheet die off.

You can check out the old bulk keyword generator here, however, this is going to get replaced in the future.

This tool started me thinking about a few things and got me working on my latest project.

 

SEOpp Keyword Generator

SEOpp keyword tool

After the IMforSMB tool started getting used, I figured it was time to build the tool out properly.

The first piece has just finally been launched with the SEOpp keyword generator now live.

The tool currently forces a business type selection, however, it will soon allow you to also enter custom services.

There is a big difference between this and the IMforSMB tool. SEOpp integrates with millions of locations, so you can generate a list of locations for your research!

You pick an initial location, a distance you service, and then it will spit out the locations for you to generate your keywords with.

Plenty more coming soon, so stay tuned!

 

Managing your keywords

It’s all well and good having a bajillion keywords laying around, but you need to be able to move them around manage them effectively.

I try and do as much of my work in a single Google Sheets or Excel File, depending on how large the data is and what I need to do.

 

Splitting CSV files of keywords

If you generated a list of 50,000 keywords, you’ll probably need to break it up if you want to run it through the Google keyword planner, or any other bulk upload tools.

Not many tools will accept an upload of 50k at once!

I started by manually doing this, 3,000 at a time for the planner. It is a tonne of copy/pasting and drove me mental.

Until I found the CSV splitter here.

Select a CSV, then enter how many rows for each file. You can tick on whether to have the header remain on each individual file or not as well.

This will spit out all the files in the same folder as where you have the large CSV, so if you’re splitting a heap of files just organise it accordingly beforehand.

 

Merging CSV files of keywords

If you’ve ever downloaded a heap of CSVs at once, you know how much of a pain it can be merging these all back together.

I too started by manually copy/pasting all the data one by one into a master set.

Mostly search volume exports, ranking data or competitor analysis, I had enough pretty quick and discovered a neat little CMD trick.

Mergine CSV files with CMD

How to merge CSV files together

  1. Open up the command prompt by typing CMD into windows search.
  2. Type ‘CD’ (change directory) and paste in the folder path for where all your CSV files are and hit enter
  3. Type ‘copy *.csv filename.csv’ and hit enter

If successful your merged file will be in the same directory where all your smaller files were stored.

You will be able to merge hundreds of files in seconds, so you can save quite a bit of time!

Not sure if this trick works on Mac, so feel free to give it a crack and let me know.

 

Bulk categorisation & classification of keywords

Categorising (or bucketing/classifying) keywords is a pretty big pain once you begin managing hundreds to thousands of keywords, let alone hundreds of thousands.

This is where Excel has really shined for me over the years.

I discovered a little magical formula that has allowed me to essentially create category ‘seed’ lists, and then bucket the keywords off the back of this.

=IFERROR(LOOKUP(9.99999999999999E+307,SEARCH(Category1[Find],$Keyword Cell Reference$),Category1[Category]),”Generic”)

Ignoring the super big number at the start (something to do with that being the largest row count Excel can do), the formula is actually really easy to use.

How to bulk classify keywords for SEO

  1. Create a table with two columns, ‘Find’, and ‘Category’, and name the table “Category1”
  2. Enter your ‘search’ words that you want to look for in the left column
  3. Enter your categories next to the search words, that will be given when a search word is found
  4. Enter the categorisation formula in a cell next to your keyword and update cell reference to point at it
  5. The formula should now categorise your keywords, based on what you entered

Bulk keyword categoriser & classifier

So essentially, it just looks for words (or parts of words) within your keyword, then categorises or classifies them appropriately off the back of that. If it doesn’t find something, it will just say ‘Generic’ instead, which you can update to something else in the formula. You can even use a cell reference, and customise it per category if you want.

No more match, lookup or vlookup formulas just looking for a single category at a time!

I don’t remember what it was originally used for, certainly wasn’t for SEO though! It was hidden away on some accounting forum, where SEOs will rarely go. Goes to show what you can find when you Google hard enough!

Another one I made a video for a couple years ago, which might explain the formula a little better.

I also created a keyword categorisation template you can download here.

It’s pretty useful for a few things, and I would love to hear what you do with it!

Among other things, I use it for dashboarding and keyword mapping to get them all assigned to the right URLs.

 

Leveraging categories for keyword to URL mapping

Once you’ve got your categories, you can throw them into a URL structure with a formula. There are quite a few variables here, and it sometimes comes down to personal choice as to how you’d like your URLs, but see below for a starting example.

Now, this isn’t how I would be grouping keywords & structure the URLs for this particular keyword set, but it still provides a good example.

Keyword to URL mapping in Excel example

=SUBSTITUTE(SUBSTITUTE(LOWER(“/”&[@[Category Type 1]]&”-“&[@[Category Type 2]]&”/”&[@[Category Type 3]]&”/”),” “,”-“),”generic/”,””)

The formula is taking the categories, and merging them all together, whilst following the standard practice of making everything lower case, and replacing spaces with dashes.

You’ll also see that I merged the first two category types together so they sat at the same level. Just for something different!

Then you just need to create separate pivot tables for each URL you’ve created, and you will have your URL > Keyword mapping.

Excel pivot tables with keyword mapping

You’ll then end up with a neatly mapped set of keywords, and some pretty tables that show the mapping.

You can event take it a step further and cross check this mapping against the actual rankings for the website, see what’s not ranking for the right page, then try to determine why.

But that’s for another time!

 

Building a keyword dashboard

By just leveraging pivot tables & slicers, you can create an interactive keyword dashboard. This could be for a client, or just to make it easier for you to see top keywords when you’re dealing with keyword lists of thousands.

For this one, probably best you just watch the video!

 

And that’s a wrap… for now.

I’d love to be able to break down the different bits here into mini-tutorials, so if there is anything, in particular, you want to find out more about, let me know in the comments below!