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;

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;

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;

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)

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

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.

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

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

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

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

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

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

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!

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

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

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.

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.

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.

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.

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.

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)

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.

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.

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

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.

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

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.

=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.

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!