One of the earliest and arguably most important parts of the SEO process is keyword research. Keyword research helps you answer that all important question, "In what quantities do people use search engines to find the products and services on my website?". Your research process will ultimately govern the method you use to structure your website, inspire your content strategy and kick start your link building campaigns.
So, it's a bit of a shame that some SEO's don’t like doing keyword research. It’s data intensive, requires some heavy lifting with Excel, and, let’s be honest, at times feels a bit like guess work. We're reliant on data to make the right decision, and that decision could have consequences months if not years down the line for your SEO project.
This is a "give it up" post
For several years now, I have used a methodology for our client keyword research that I believe adds deeper, actionable insight in to the decision making process. You see, a list of keywords with search volumes is all well and good, but it’s not particularly actionable, is it? I’m going to show you part of a process that helps to change all of that.
Before we get started, I’ll make the disclaimer now – I believe in this process so much that I built a keyword tool that does all of this work for you. Obviously I’m going to give a nod to that point, but none of what you'll learn in this post depends on using our tools.
What you need to know first
To follow along, you need to understand a key principle in my methodology. That principle goes a little like this:
To make keyword research more actionable, you need to be able to categorise, group and filter keywords. Deep insight into category based search behaviour can make your research considerably more effective.
That’s it! Now, let’s think about what I just said. For my example, I’m going to use an automotive / cars based analogy, (I love cars, though this concept works for nearly any industry!)
Imagine you’ve got a used cars website, and you want to know if there’s any potential search traffic in location based terms for popular car brands, like “used Audi in Birmingham” or “second hand Mercedes London”. If you wanted to determine the top, most searched for locations for keywords that contain known car manufacturer brands – you might have a problem. If you generate a lot of keyword data, how are you going to be able to group the terms into their corresponding keyword categories?
Here’s a basic example of what I mean:
At first glance, this chart looks like it’s been produced from a single list of keywords. It has in fact been produced with only 2 simple filters in a much larger dataset. “Brand” and “Location”. The data in this chart might help you decide in what order to target your keywords (and their variations) with content, or even justify an entirely new content type for your large scale dynamic website.
What I'm going to show you will teach you a way to generate your data first and sort it all out by categorising it later, and not just by “location”, or “brand”, either. Let’s get started.
Gather your keywords
Firstly, we need to build a keyword list. Let’s start by listing the sources of keywords you can get inspiration from, like Google Analytics, Wordtracker’s keyword suggestion tool, Wordstream’s tool, and my all time personal uber-favourites – Mergewords.com. If you’ve got access to PPC data, use that too.
Want some ideas on how to build a rich and varied keyword list full of potential opportunity? Using my example, I want to build a list of car manufacturers (“brand”) by UK City (“location”), and I’d like to compare demand for used vs new (“condition”). To do that, I’ll choose some of my favourite manufacturers, Porsche, BMW, Mercedes and VW.
Grab your list of cities and head over to mergewords.com
Yay! 2,376 new keywords. Now for the search volumes.
Get search volumes
So you have a few thousand keywords to gather search volume for. You’ve got a few options, one of those being an epic copy and paste mission via Google’s keyword tool . Don’t panic, it’s actually not that bad. You’d be surprised just how quickly you can collect a lot of keyword data, even manually, if you get your process right.
By using Chrome , you can build up a sweet downloads folder full of CSV files, 100 keywords at a time. A reasonable copy, paste and download mission can yield a great data set.
Obviously, this stage requires effort. For you savvy SEOs with development skills, you might want to consider writing a script to access the Google Adwords API. Freelancer.com or oDesk are good places to go to get a script written, too. I found a worthy solution in a script I had made by a freelance developer on Freelancer.com, from which I automated the keyword data collection process via Mozenda . Being able to gather data for around 50,000 keywords at a time really enabled me to do some interesting things, like capture 10 related keywords from the suggest API and run all of those through the search volume API, too. Big data for the win.
Create your categories
Ok, time for some Excel heavy lifting. We’re going to start by creating our keyword categories, and then use an Excel array formula to categorise each of the keywords in our data set. For our example, we’re really interested in filtering for keywords that have location, brand and “condition” based keywords.
Do bear in mind this is advanced Excel, and will require some problem solving on your part. Stick with it, it’s very, very cool.
Create a category table with headers for each of your category names and add “markers” in to each category:
Next, in a separate Excel tab, you’re going to need to build up your keyword search volumes table. Create columns for your keywords, search volumes and category names:
Next, the awesome bit. We’re going to use an Excel array formula to identify keywords that belong in a category by matching strings of text between our category markers and the terms contained in the keyword list. Here’s what a categorised list will look like:
And here’s the formula we use to make it happen:
{=IF(SUM(NOT(ISERROR(FIND('Keyword Types'!$A$2:$A$7,$A2)))*1)>0,P$1,"Non-"&LOWER(P$1))}
Where “'Keyword Types'![CELLRANGE]” refers to the category column we’re matching in the category table, “$A2” is our keyword, in this case “audi” and “P$1” is the name of our category column, in this case, “Brand”.
You’ll also notice that there are some curious curly brackets included in the formula. That’s what makes our formula work across an array.
What’s an array formula?
“An array formula is a formula that works with an array, or series, of data values rather than a single data value.” – Chip Pearson
We’re using an array formula because we’re attempting to match values across a range of cells. Any one keyword marker string could appear in our keyword, so our formula needs to be capable of checking across a range of values. Array formulas can unlock an entirely new level in Excel, as my good friend Tom Szekeres taught me some time ago. They're just amazing! That. Is. All.
Putting the data together
Follow this step by step process to see if you can get your first categorised list of keywords to work in Excel:
1) Paste the formula into excel and highlight the part of the formula surrounded in red in this screenshot:
2) Click your category tab and highlight your category list. Press F4.
3) Press CTRL, Shift and Return and Boom! A categorised list of keywords – in our case any keyword that contains “new”, “used”, and “second hand”.
Why such an advanced approach to add to your keyword research methodology?
When you’re designing a brand new site architecture, or enhancing an existing one, justifying new content groups, changes to dynamic meta templates or defining keyword strategy decisions should always been made on the data. If you have the capability to work with large, expanded data sets, with a scalable categorisation approach, there’s no doubt you can make decisions a lot more confidently. I hope that with a little practice and perseverance, you will agree with me. Some example categories for your industry might include:
- Gender ("Men's", "Women's", "Girl's", "Boy's")
- Occasion ("Christmas", "Valentine's Day", "Easter")
- Location by Cities or States ("New York", "Washington", "New Hampshire")
- Colours ("Red", "Green", "Blue")
You've also got product groups and names, buyer intent (research, review, purchase) and perhaps even groups that directly reflect the position of a category page in your site architecture ("Tier 1", "Tier 2"). Cool huh?
What data should I use?
All of it. Everything you can find. As much as you can get your hands on. That’s the point. You might not want to rely solely on Google search volumes, and, if you can, you should build rankings data and analytics entries into your data set. All I can say is thank the gods for VLOOKUP. That’s another blog post though – if you want me to write it, shout out in the comments.
What should I do next?
Well, the next most sensible suggestion might be to analyse your data using a pivot table. Making pivot tables is a really easy way to quickly deep dive into your data across multiple data points. I wrote a pivot table tutorial (including how to make pretty charts for keyword research), the content of which is designed specifically for keyword researchers.
Since Richard didn't put a proper plug in for his tool, I will [disclaimer - I'm just a happy customer andin no way related to Richard or his company].
So let's talk about his KT. It can link with your Google Analytics account and pull in keyword data effortlessly that way, and/or it can allow you to add your own list of keywords manually.
It's fairly robust so although I'm far from a power user, I've never been able to make it hiccup yet with hundreds and hundreds of keywords thrown at it.
Here's the very best part. You can create categories and all you need do is name the category and tell it the base keyword you want included in the category. So for example, you have a shoe site. Make a category called "Spats". use the same word as your base keyword, and in seconds it will find and tag every permutation automatically (ie. vinyl spats, vintage spats, leather spats, boys spats, etc.)
Next is where the magic comes in. You can export to CSV, and it will automatically include a categories column with the the keywords that fit into the category tagged.
I'm sure I am butchering a proper description of the Keyword Tool but I'm sure other users will chime in and do it justice [Gianluca I'm talkin' to you pal!]
Agreed - their tool is pretty awesome for advanced KW research - https://tools.seogadget.co.uk/ - and it deserves a plug (as does this great post!)
That was a fine description - thank you!! It's really interesting to hear feedback on a user's interpretation of a product in development. I'm super chuffed with (all of those) awesome comments and I'd be delighted to hear more some time.
Glad you liked the post!
Thank you for sharing, Richard.
I am going to sound super lazy with this question: But can your KW tool the same as this - cumbersome - process?
Hi Richard, thanks for the post - I was wondering if you are still taking registrations for your keyword tool. I've followed the link Rand posted above and found the signup page to be blank. If there is still a way to sign up I'd love to give it a try. Thanks!
Some minor hints: Just log-in into Google Keyword Tool - then you get 800 instead of 100 results per query. And use the Google Traffic Estimator (in exact-match mode) instead if you're interested in the pure search volumes - you can throw in up to 2.500 keywords in one query.
I must admit, however, that I have some problems with your methodology:
Sorry for my critics.
Hi There
Mergewords is a great way to test out some of your ideas easily - it's not nessecarily your starting point. Obviously you need to start with a firm usnderstanding of the data sources available to you and what they can tell you - internal site search data, keywords from paid and organic, suggest tools for related queries etc
As for the Google KW tools, yep you might be right. When I do use that tool, I tend to just try to grab volumes for "only show KW's related to my queries - I thought this permits a paste job of around 100 keywords and you might get 250 or so back in total. If you're getting 800 at a time, awesome. I run all of my stuff through the adwords api almost all the time, so I might have missed something on the external tools I wasn't aware of.
And the most important thing to remember - this process isn't set in stone - use bits of it by all means, but only if you can see a use for it.
OMG Richard
This isn't a post, it's a book.
I really like this post, and I appreciate you sharing it with us, but let me play devil's advocate for a minute - how powerful is the end product of this process?
For example, say you're re-evaluating a site's IA - are you really going to get so much data from this approach, as opposed to looking at the top derivatives of your main keyword that you are going to justify the extra time and labor? Using your dealership as an example, wouldn't the IA be driven more by a combination of usability and searcher intent divined from head keyword data than this sheet?
Good post, some great information on how to categorise your keyword research.
I have been doing keyword research into groups for a long time hehe, just makes the whole process far easier for every one who is working on it and the bigger the site the more sub sections you will need to utalize =) but also I notice you mention wordtracker and some other keyword tools, I find been in the Australian market these tools provide data is that is not very good at all. Sure in the US market and Europe it is great =)
Yes a good very useful post
First thought - wow.
Second thought - Instapaper, as I feel that I m ay have to read this a few times to fully understand the full power of what I have read. Takewayas so far Merge Words looks awesome, and methodology seems very sound.
Great post Richard!
I've never looked at keyword research like this before.
Initially, getting into SEO, it all looked a bit pointless. Then, when I started ranking websites really well without it, it seemed even more pointless. Then, when I realized that I didn't know jack, and I should be using anything I can that gives me solid actionable data, I started learning it more. You're right, being able to make definitively confident decisions has a lot of power in the world of SEO.
Thanks for such a highly valuable post. Write the post on Vlookup!
No problem! Excel skills for the win ;-)
Or fail in my case :( I just realised I need some tutoring.
Rarely do I see something this well written about such a potentially time consuming and complex subject, fantastic post. So awesome I nearly fell off my chair, and please, do write that post about VLOOKUP.
Deal! Generally blog posts that make you fall off a chair are a good thing, right? :-)
I'm giving you a thumbs up because of the introduction to keywordmerge. Man, is this awesome.
Thank you genuinely.
This is an amazing post!
I think the only thing I would add is a link to your sample Excel file so us pragmatic/activist learners can see your formulas in action!
I would be most interested in a followup article - what do you do next with this great information (content, targeting, links...etc).
That would be a great follow up article. Using those tools we end up with say, 13,000, suggestions, what next?
I plug those in and then measure traffic, and competitiveness, trying to find some good ROI short tail terms, and target the Long tail on the big pages. But it would be nice to see how a pro does those steps in a 'give it up' way. :) Thanks.
If nobody writes it I will :)
That was a really good post. I think we need many more posts like that. Hardcore analytical posts. Not so much fluff about why SEO is good or bad, but hardcore things like this.
Really cool way to categorize the keywords. Instead of using mergewords, you can write a macro and post the following code as a module.
Sub MakeKeywordVariatons() Dim rngPrefixesList, rngKeywordList As Range Dim rngPrefix, rngKeyword As Range Dim strVariationList As String Set rngPrefixesList = Sheet1.Range(Sheet1.Range("A2"), Sheet1.Range("A2").End(xlDown)) Set rngKeywordList = Sheet1.Range(Sheet1.Range("B2"), Sheet1.Range("B2").End(xlDown)) Sheet1.Range("D2").Select For Each rngPrefix In rngPrefixesList For Each rngKeyword In rngKeywordList ActiveCell = rngPrefix.Value & " " & rngKeyword If strVariationList = "" Then strVariationList = ActiveCell Else strVariationList = strVariationList & ", " & ActiveCell End If ActiveCell.Offset(1, 0).Select Next Next Sheet1.Range("G2") = strVariationList End Sub
At the workbook level, you can post this code.
Sub MakeKeywordVariatons() Dim rngPrefixesList, rngKeywordList As Range Dim rngPrefix, rngKeyword As Range Dim strVariationList As String Set rngPrefixesList = Sheet1.Range(Sheet1.Range("A2"), Sheet1.Range("A2").End(xlDown)) Set rngKeywordList = Sheet1.Range(Sheet1.Range("B2"), Sheet1.Range("B2").End(xlDown)) Sheet1.Range("D2").Select For Each rngPrefix In rngPrefixesList For Each rngKeyword In rngKeywordList ActiveCell = rngPrefix.Value & " " & rngKeyword If strVariationList = "" Then strVariationList = ActiveCell Else strVariationList = strVariationList & ", " & ActiveCell End If ActiveCell.Offset(1, 0).Select Next Next Sheet1.Range("G2") = strVariationList End Sub
This should do the trick for you in excel itself.
Love this Richard. This actually helped us with an automotive client presentation. Just wanted to give you props!!
I'm late to the party, but this is a fantastic write up on keyword research. It's interesting to look at someone else's prefered methodology. Thanks!
Just checking back in on the comments to see if anyone has posted anything about SEOgadget. Surely someone must have signed up after this post! What's the scoop on the tool?
This is awesome stuff. Definately a 'give it up' post. I am going to check out the Keyword tools too. Great post. Wonderfully written. The same strategy can be applied in our product online aggregator script
[Link removed by editor.]
That is an outstanding tool. I am still during some comparison but I think I have a new toy.
I'm sharing this post with the colleagues in my department at work. Thank you very much for taking the time to write such an informative article.
Hi Richard,
Thanks so much for your post on this. I did not know about { } before and have made do with VLOOKUP and SUMIF ;-) Thanks to Tom C who pointed me to this post.
Damien
Hi,
I know this is pretty late in the game, but a colleague just asked me to amend one of the formulas so thought I'd share. Amesome blog by the way, I've moved from SEO to analytics and conversion but had I had a tool like this a year ago it would have saved a lot of time!
My colleague directed me to the post and wanted the {=IF(SUM(NOT(ISERROR(FIND('Keyword Types'!$A$2:$A$7,$A2)))*1)>0,P$1,"Non-"&LOWER(P$1))} to return which category the keyword belonged to instead of either section name or ''non'.
In other words, she wanted a partial match using an array WITHOUT the use of a macro or VBA.
Here is a neat little formula which will do this for you, its not amazingly intelligent as you can expect from a non VBA formula, but it does the trick.
{=IF(ISERROR(INDEX(Categories!B$2:B$21,MATCH(TRUE,ISNUMBER(FIND(Categories!B$2:B$21,$A2)),0))),CONCATENATE("Non-",F$2),INDEX(Categories!B$2:B$21,MATCH(TRUE,ISNUMBER(FIND(Categories!B$2:B$21,$A2)),0)))}
Basically, 'Categories!B$2:B$21' is the list you want to find the partial match in (or name of your category), and A$2 is the name of the keyword that you want to assigned to the category. F$2 is the header for each group.Note how all of these are only semi absoluted? This is so you can drag the formula across columns. You will, however need to amend it for the different columns to be the exact ranges (unless you have the same number of categories in each section). But if that's difficult to understand, don't worry - it'll just come up with 0 instead og "non-".
Ctrl-Shit-Enter to array it again and you're ready to go. Note, because its a partial match, sometimes very similar category names are superflous - e.g. ring and rings any keyword with ring or rings in will always return the first name it encouters. Following that logic, earring, for example, would be placed before ring. Just a bit of common sense and trial and error is needed.
The code is pretty simple - it just finds a partial match and if it can't does a concatenation of "non" and the subject heading.
This partial match will shortly be written about in more detail on my blog: https://blog.rachelsweeney.com/
Cheers
Hi Rachel
Nice! Glad to see you're developing the formula. Very cool :-)
I'm fairly new to SEO with a purely marketing background. I'm working for a telecom company, and we already have a website. Sadly, our SEO has left a lot to be desired. I don't think we ever bothered to undertake a keyword research before. Now, all content on our website is relevant to our offerings only. We have Packages, offers (that work on specific packages), value added services (that you have to activate) etc. Does that mean I don't have to create categories all over again?
I couldn't relate to example Richard quoted, though use of excel seems pretty exciting. Can anyone help me out here?
Interesting post helpful for new Seo professionals helping them grab the potential keywords and merge them well to gain the benefits from various sources say google analytic, PPC keywords etc. It will be good to recomend it for others.
Very Helpful article, thanks for sharing it.
This is excellent. I know I'm a few years late, but thank you for "Giving it Up". Really useful
Awesome article! Thanks for sharing! I also used some kinda data collection tools like Octoparse, Mozenda,import..... to help me bulk collect keywords and I was able to gather lots of keywords at a time. But I had no idea how to do with the results. There's hundreds of thousands of keywords..... Your article really can solve my problem! Thank you very much! Awesome！
Hi Richard,
I was watching your webinar: Actionable Keyword Research. What is the vlookup formula to read the different columns inside the category table? I can't make it work. Awesome video by the way
Found the fix, THanks phantom
Thank you for sharing an insightful post. I'm having a bit of trouble forming categories and would like to request assistance please. I work for a non profit employment service offering employment services, jobseeker services and training services.
It's clear how the categories are formed for a brand term like BMW because there's not much variety, but I'm a bit confused with something like 'employment agency' because there's so many different types of searches, ie recruitment agency, recruitment service, job agency etc. And then we also offer disability employment, migrant employment, etc.
Would anyone mind sharing how I might go about categorising something like this? Your expertise would really have an impact.
Thank you in advance.
Regards
Jason
Thanks for sharing Richard!
I've to tell you, never played too much with excell, well know it how I need it... but you gave excellent ideas and methods (especially for large sites).... I see that you use excel... have you tried doing that with open office? I know they are almost the same, they have small differences though... as I just read the post, gonna try it there.... hope it works same good...
This information is very helpful to me. I'm learning something new, Like you I think the words are so important, and we must do everything necessaryfor the most appropriate.
I appreciate this valuable information.
WOW. this is one of those webinars that I will be rewatching a few times. Thank you so much for providing so many helpful tips.
This is a great post and coupled with the webinar on actionable keyword research, you have helped me tremendously with identifying opportunities in a streamlined way. I do have one important problem involving the graphs (the third slide of the powerpoint on the webinar):
How do you create a simple graph with all three data points (search volume, analytics, and ranking) without the huge numbers in search volume towering the analytics data? For example if your keyword is "grass" with 5,000,000 queries a month, that is going to skew the analytics data. The rankings will be on a secondary axis, but unless you are getting close to or ALL of the traffic for any given querie, then the bar for analytics will be way lower and hardly visible compared to the bar for search volume.
after you have the report then what do you do?
Congratulation for this great article which helps a newbie as me a lot. Yes, please start working on follow-up of what doing next! Thanks again!
Two thumbs up for this excellent post!It's shows the power of Excel when doing keyword research once again.Richard, I'm very much interested in the earlier-mentioned KT. Isn't there a trial or demo version available to see whether it fits the bill?
Hi there - yep there's a trial code on sharkSEO's awesome (and very kind) review: https://sharkseo.com/whitehat/clever-keyword-research/
Holy crap, this is just insane! I'm very thrilled about this kind of keyword research, but it does look like it's only for the elite Excel people. Hopefully i can implement this into my own skillset. Better pray to the SEOgods :)
For those looking to move away from Excel or "desktop applications" in general, here's a web-based tool for data collection and filtering: https://databox.phgroup.com/
I do use Excel a lot but I'm always looking for ways to move away from it.
This method is going to save lots of time and help to provide a more comprehensive keyword report. I don't think we need to be experts at excel. We all have basic knowledge and should give it a go. If I become stuck with my research or reporting ... google is my friend. I learn so much more by just trying to find the answer for myself - Helpful posts like this for example.
Thanks for the comprehensive tutorial Richard.
I also just want to say that whilst I'm a fairly new user of your new keyword tool and took a while to 'get it' in terms of the value but it is really impressive. I look forward to seeing how it develops over the coming months.
Very helpful tips - I like that way of working! The free tools Mergewords.com and Ubersuggest are worth an applause, too!
Totally. I love Mergewords - everyone I show it to gets it straight away. What a timesaver!
Thank you thank you thank you. This is awesome stuff. Definately a 'give it up' post. I am going to check out the Keyword tools too.
update - just tried out Merge Words and I am so excited. I was using something of own making. Merge Words is SOOO much better.
WOW this was a great post.
update 2 I wish I could gave a double thumbs up. Ok I will. TWO THUMBS UP!
Learning how to use VLOOKUP changed my life. I wholly support spreading the good word.
Hi Richard,
when you presented this awesome categorization for keyword hunting methodology at last London SEOpro I felt like Mr. Carter discovering the Tutankamom tomb. Now, reading about it again and with the added experience of the SEOGadget.co.uk keyword tool, I feel again that sensation.
Great post... hard SEO, but explained so well that also a newbie can experiment with it and do the best thing an SEO can do: learn.
I guess I need some more excel training. I can't get the spreadsheet formulas to work.
I'm having the same problem.
The array formula seen in the section: Create Your Own Categories... even when changing the cells as instructed... doesn't seem to paste correctly. Also it lacks the squigly line brackets { or } when seen again under the section: Putting the Data Together.
If anyone else has this problem please let me know what I'm missing. I'm pretty adept @ Excel but can't seem to get this working correctly - and really wanting too!
Hey Gary - My bad if you have tried this already, but the trick is to, once you get the new range input in the formula (and fixed with F4) is to click Control+Shift+Enter; that should do the trick
Hi Gary,
When you paste in the formula, don't include the curly braces. Excel will include these automatically once it's told that it's an array fromula by way of the control+shift+enter (command+return on a mac).
Hope that helps.
Legendary post as ever Richard. Great job!
Amazing Post! We are essentially doing all this by brute force right now. Thank you!
Awesome post, I'll be re-reading this many times over.
Providing better data, and improving my skills in this department is one of my big plans for the year. Thanks again for all the useful information, and of course a great tool that's proving itself to be very useful!
Very good article! we are having to do alot of keyword research for a new project atm. Where our domain already has authority over many keywords, site is being completely overhauled and want to target more of the market we occupy.
Thanks!!
Fantastic post, great to know that despite having used Excel for the last 15 years I know absolutely nothing of its power...time to remedy that!!
As an aspiring Excel Jockey myself, I have to say this is pretty neat. You could probably use that array formula in any number of one-off scenarios. Thanks for the great post. I'm off to create some categories.
Great post. I've always shied away from the more fancy Excel stuff. This has inspired me to give it a shot to see if I can reveal any opportunities.
For us folks who aren't Excel wizards, this is a great way to discover SEO Gadget. Good job!
Going to take the Lynda dot com excel classes now. whoohoo!
sir! Thank you for such a deep researched article... i ll surly gonna give your tools a try!!
This is a fantastic post I can't believe it doesn't have 1000 comments.
It still has not 1000 comments just because many of us were sleeping and because, as it happens after a great movie, or a book or simply as it happens when you see the girl of your life, the normal reaction is silence :)
And on that note.. Good morning Gianluca!