Submit post
Go to Blog

Weekly Wisdom with Ross Tavendale: Super Fast Keyword Research

updated:
January 29, 2019

This video is part of our Weekly Wisdom series which features experts on a variety of topics. 

Modified Transcript

Need to find all the keywords that your customers are looking for, as well as everything your competitors are ranking for, and I need it fast?

Thankfully, with a couple of tools, it is easy to get this information very quickly. Okay, let me orientate you with this sheet. Essentially the green tabs here are all of the output tabs you are gonna use to actually get the information all cleaned and ready to use.

For example, we can see here, we have got all the keywords, how difficult they are from a score of zero to one, how many searches every month you're getting, and then some real basic analysis on how much money we think we're gonna make from them every month.

screen-shot-2019-01-28-at-50026-pm.png

Next up, we've got seasonality so I can see when I need to post my content based on the month. Then, of course, the keyword list itself plus working out if we actually rank for any of these terms or not. For example, this term has got this associated landing page, so we're fine, but you can see all of these terms do not have landing pages, so we probably need to make them.

How It All Works: An Example

The first tab is the setup tab. Here, what we want to do is we want to take all of the major keyword categories that we want to rank for. This is a homewares brand so what I'm gonna do is take some of their top keyword categories and put them in here. The way that I find keyword categories is really easy. First and foremost, I go to Search Console, the Performance Report, go to queries, and then I simply just download their queries.

Once you've got your queries, all you're literally doing is taking the big list of them, copying them, going over to an aggregate tool called Keyword Grouper Pro. All you're doing is putting them in there, giving a minimum group length at the amount of keywords, add any keywords you want to exclude (maybe it's the brand name, for example - it should be put in here), set excluded words, and process.

What we can see here is the top-level groups of all the products that this particular person sells. We have got bedside, floor, lights, pendant lights, etc. I am going to export this, and I'm going to add it into my main sheet.

Now we are going to take those top-level categories from Keyword Grouper Pro and put them in here, and then we are gonna do something slightly magical — we are gonna use Supermetrics, and we are going to click the big "Refresh All" button, and that is going to pull all of the information we need from SEMrush.

Phrase Match

Now that all that is done, let me walk you through the raw data sheets by going first to ph_SEMrush (ph stands for phrase match). This has essentially pooled all the phrase match queries for each of these keywords inside of the keyword categories tab, and it added them into columns here. So, this is just a big, massive dump of the first 5,000 keywords of our phrase match related to the term lamp, sofas, tables, etc.

Related Searches

Inside of the related searches, we can see that we have got all the terms that SEMrush think are related to all of these keywords. Again, the exact same format, top 5,000 just dumped straight into columns, but what we need to do is get it into one single column, so we let's start editing it and cleaning out the data.

Cleaning Up the Data

screen-shot-2019-01-28-at-45839-pm.png

You will notice here that when you click on this "Error. No data found." it is using a formula called UNIQUE. All UNIQUE essentially does is it goes to each of the phrase and related match, and it pulls it into these columns. If we were to redo this formula, you will notice it has got squiggly brackets, that is because it is an array formula. So, it is UNIQUE({, then we're literally gonna go to our first one and we're gonna select from column A to E, semicolon, and then select column G to K, and we are essentially just gonna rinse and repeat this process for not only the phrase match but also all of the related match data as well.

You can see it has taken all those columns and columns of data and put them into one single column, which makes it much easier to work on. It also attempts to tag. All this is doing is a nested, IF ISNUMBER SEARCH. So, it's kind of like a little auto-tagging thing just to kind of help us along.

What we are gonna do from here is we are literally just going to copy all of that out, and there is an option called "Edit these keywords", which is literally just a clean copy/paste. We are going to delete the old data in there first, and we are going to do Ctrl+Shift+V to get everything pasted in as it should be.

Next step. We have got all of the seasonality data in here, but it is in one column. You need to go Tools, and then to go Data, and split those text into columns. This option will allow you to name them as the different months.

More on this Weekly Wisdom with Ross Tavendale: Time-Saving SEO Hacks Post Ross Tavendale Weekly Wisdom with Ross Tavendale: Reclaiming Traffic Post Ross Tavendale Pagination; You’re Doing it Wrong! (Part 1) Post Arsen Rabinovich

Reducing the Keyword Data Collection

Once we are happy with the general raw data collection, what we now want to do is edit these keywords. Let's say that you see a bunch of stuff that either your client doesn't sell or is really just not related to them. What you want is to take out big chunks and delete the keywords that don't matter, and also tag keywords for organization purposes; this is the only real manual bit of the process that you need to go through. So, go through, get rid of the stuff that doesn't apply to you, and tag the rest of your terms. It is literally as simple as that.

The Final Keyword List and Landing Pages

When we now look at our keyword list, we can see all of the different terms that matter. For the example of houseware, we can see that for the term bedside tables, we already have a landing page, but it is /table-lamps not /bedside-tables. So we could probably make another one that that says bedside tables and perhaps enhance the rankings for this particular phrase. We can see for a bunch of their other keyword phrases that don't actually have any landing pages for them.

Now, you may be thinking, "Well, where am I actually getting all of this landing page information?" Well, it is super simple. Review your SC keywords (SC stands for Search Console). So when we go up to the top of the table in this example, what I have done is pulled it in the data via Supermetrics again.

You can do this by going to Supermetrics, then into the sitebar, then select Search Console as the source. I put my client's website in there, looked at the last 90 days of data, and here is the most important part, "Split to rows by Search query", and the full URL. You will not get this when you just download it from the main interface. I want to see all the queries and all the landing pages side by side.

The reason why I want that is because inside of the keyword list all this is doing is an index match. All it is saying is, look for the keyword football tables, inside of this Search Console dump, and if it exists, put the URL in there. If it does not exist, say "No Landing Page". What you can see here is we are very quickly able to crosswalk all of the keywords that we can see in the marketplace with all of the keywords we are currently ranking for and the related landing page.

And that is it. That is all the information you need to tell you the pages you need to create to rank better inside of search. All you need to do is the hard bit and actually go make the content. Super fast keyword research, done.

Go to Blog
Share this post
or

Comments

2000 symbols remain
Pavel Naydenov
Helper

An experienced member who is always happy to help.

Hi there.
It is really nice that you created this piece and explained all these complicated sheets, but really don't think that "Super Fast Keyword Research" is the right title for this article.

Well, may be it will be useful for agencies. For in-house agencies I kind of doubt.

Plus, SEMRush has this nice tool (I am sure you know it) Gap analysis.

Still, very well explained.
Cheers.
Scott Rumsey
Helper

An experienced member who is always happy to help.

I love these weekly posts, they are informative and cater for every level. Keyword research can be ardours at times but is necessary if you are to produce content that is relevant, trustworthy and assists with rankings. I really enjoyed this post as anything that simplifies the procedure is worth listening too.
Expert

Provides valuable insights and adds depth to the conversation.

This Weekly Wisdom was great Ross and to-the-point. A different angle towards conducting keyword research really fast.
Kate Toon
Helper

An experienced member who is always happy to help.

A smashing bit of content and I am IN LOVE with Keyword Grouper pro. P.s. Your accent is just the best.
Ross Tavendale
Guru

A bearer of digital marketing wisdom.

Kate Toon
Thanks Kate! High praise coming from the podcast queen. Appreciate the comments :-)
Newcomer

Either just recently joined or is too shy to say something.

Any chance we could get a copy of that template? :)
Ross Tavendale
Guru

A bearer of digital marketing wisdom.

Will Bradley
Sure thing Will - just making a sanitised version for public consumption. Will be ready for the end of the week :-)
Newcomer

Either just recently joined or is too shy to say something.

Hi everyone, I am an absolute newbie to this whole area of in depth KW research. I'm a small business owner trying to boost awareness and drive traffic to my store following several unsuccessful attempts by so called pros to market for me. I stumbled across Semrush during the Xmas holidays and have been glued to the computer just exploring the different tools and exploring really. I am keen to start learning how to improve the back end of my site.
However, to be honest, I am feeling a little overwhelmed with how much stuff there is to understand and implement. Do you guys have a recommendations as to which basic tools i should concentrate on initially just to get some traction?

Thank you all.
Cynthia
Ross Tavendale
Guru

A bearer of digital marketing wisdom.

Cynthia Holt
Hi Cynthia, i can recommend starting off with the SEMrush academy which will take you through how to use the tools and apply the data to your site. https://www.semrushchina.cn/academy/
Newcomer

Either just recently joined or is too shy to say something.

Ross Tavendale
Thank you Ross...will get myself enrolled
Tom Casano
Pro

Asks great questions and provides brilliant answers.

Nice work Ross! I had no idea you also use keyword groups and Keyword Grouper Pro. Very cool. I never heard of Supermetrics but I'll have to check that out as well. Brilliant work, thanks for sharing!
Ross Tavendale
Guru

A bearer of digital marketing wisdom.

Tom Casano
Thanks Tom! If you go to supermetrics.com/ross they will give you a special deal. But to really power it up you need to be working with https://www.semrushchina.cn/api-documentation/ - it is a beast!
Jason Barnard
Legend

Getting here is not easy at all!

columnist
Really neat process, Ross.
Didn't know about Keyword Grouper Pro.. I'll give that a go !
Thanks !
Ross Tavendale
Guru

A bearer of digital marketing wisdom.

Jason Barnard
Thanks Jason - definitely one of my favourite tools. I know Jono Alderson also created something called term tagger which also has killer functionality.

https://www.jonoalderson.com/tagger/
Andy Drinkwater
Expert

Provides valuable insights and adds depth to the conversation.

Top info as always Ross :)

I am just in the process of looking at Supermetrics myself at the moment - nice to get some information before I delve into it. And nice to see you you do this as well. Always like to have a nosey at how others perform tasks.
Ross Tavendale
Guru

A bearer of digital marketing wisdom.

Andy Drinkwater
Thanks Andy. It's my favourite tool by far. I love using tool interfaces but nothing actually beats rolling your own.
Newcomer

Either just recently joined or is too shy to say something.

This is brilliant! I've been doing something similar for years, but my way is highly manual. :(

Will this method still work with Google's new way of reporting keyword/landing page data?
David Attard
Newcomer

Either just recently joined or is too shy to say something.

Hey Ross,

that seems like a nice clean and fast way of doing keyword research. Can I ask you a couple of questions.

1. Is your template available for reuse? :-) I'm sure you've out a bunch of work into it, so it would be great if we could reuse your effort, even at a price, if you believe you might be giving away your competitive advantage.

2. 2nd question - and a more critical one. One of the biggest nags these days is keyword cannibalization. I think I saw a lot of phrases which seem to be fairly close, do you have a process to handle that in your research, trying to make sure your own content is not competing with itself?

Cheers
David
Ross Tavendale
Guru

A bearer of digital marketing wisdom.

David Attard
Hey David

Yes the template is available. I'm making it public friendly (removing our API keys and history) and will then publish it for download. I don't think publishing our templates gives away our competitive advantage - a lot of the example sheets are the basic version of what we use for client work.

On cannibalisation, yes we have a process to find and remove that. Again, with super metrics you can pull search console data and have both the queries and pages pull to a column. This means you get a big list of duplicated URLs with the queries next to it. I then use the "unique" formula to get the top URLs de-duplicated and do a simple countif to see the number of keywords ranking for each URL with the impressions and clicks summed. That is then the basis for digging in to see they keywords that are wildly different and need their own content.

For more basic cannibalisation, I actually use the onpage SEO checker with SEMrush. We tag ever page we have with keyword topics (literally every page) so we can see where there is overlap. It also comes in handy when we are doing internal link building as we've tagged every page up as cornerstone, commercial, supporting, etc along with a keyword tag.

When, I'm in sales meetings I often say SEO is "advanced admin" and to be honest it kind of is - once you have your ducks in a row and a good process - you're all set.
David Attard
Newcomer

Either just recently joined or is too shy to say something.

Ross Tavendale
Fantastic, thanks for the reply - what about trying to anticipate cannibalization by grouping the content into "buckets" beforehand?

Is that something which can be incorporated into the template?

David
Newcomer

Either just recently joined or is too shy to say something.

Ross Tavendale
Hey Ross,

Great video :)

where can I download the actual template ?

Best,
Max
Igal Stolpner
Newcomer

Either just recently joined or is too shy to say something.

Great post and tips Ross, thanks!

I wanted to try Supermetrics for a long time actually.

Spotting the popular keywords your direct competitors rank for and you don't have landing pages for is one of the quickest wins most websites can apply. Worth a quick revision on a regular basis.

We actually added 2 sections last year after seeing how well they work for competitors.
Ross Tavendale
Guru

A bearer of digital marketing wisdom.

Igal Stolpner
We like pulling competitors and comparators for gap analysis. It's amazing how much content with informational or conversational intent you can get ideas from when you look at a parallel industry to yours.
Newcomer

Either just recently joined or is too shy to say something.

Really amazing information very helpful
Jesse McDonald
Expert

Provides valuable insights and adds depth to the conversation.

Excellent video, Ross! I really enjoy seeing other SEO's keyword research process. I've never worked with Supermetrics but I'm definitely interested in checking the tool out further. It seems like it can really help with data aggregation.
Ross Tavendale
Guru

A bearer of digital marketing wisdom.

Jesse McDonald
Hey Jesse, I love seeing other SEO processes too. It's amazing how different everyone is. My favourite voyeur is seeing the bookmarks and plugins people use on their chrome.
Jim Munro
Newcomer

Either just recently joined or is too shy to say something.

Wow. Good job, Ross Tavendale. I think that's the most useful clip I've seen in a long time. It's a credit to you.
Ross Tavendale
Guru

A bearer of digital marketing wisdom.

Jim Munro
Thanks Jim - glad you found it useful. We love obsessing over cutting out a second here and there at Type A Media. This is a little extreme with regards to time saving but really fun to be able to magic this stuff up in a heart beat.
Tim Capper
Master

A veteran community member.

Brilliant vid, thanks Ross.
On a slightly different tangent if you don't mind. How are you calculating potential visits based upon monthly searches vs potential SERP position.

Monthly Search = 1000
Serp Pos 1
Serp Pos 2
Serp Pos 3

Est % of the monthly searches.
Editors' Pick
Ross Tavendale
Guru

A bearer of digital marketing wisdom.

Tim Capper
Editors' Pick
There is a SEMrush calculation that works it out for you. So they give you potential visits for a keyword at a position.

If I was to make my own formula, I'd pull all search console keywords and topic tag them together with their positions and average ranking. So if I was looking at a lighting ecommerce site I may have data like "black lampshades" are position 2.2 with a 8% CTR and "table lamps" are position 8 with a 1.2% CTR.

Doing this across all the keywords and aggregating the position down gives a very rough CTR for different keyword types. So you could legitimately work out a position 1-10 CTR at a keyword topic level and use that.

But it's a bit too fiddly for me so I just take the SEMrush number as gospel and apply an average. Not as accurate but forecasting never is :-)
Tim Capper
Master

A veteran community member.

Ross Tavendale
Brilliant, Thanks Ross
Newcomer

Either just recently joined or is too shy to say something.

Great video
Ross Tavendale
Guru

A bearer of digital marketing wisdom.

Jiut ray
Thanks Jiut
Chris Romero
Newcomer

Either just recently joined or is too shy to say something.

This was the best Keyword and Excel course I have ever taken. To be honest, I haven't learned as much in hours as I just did in 7 minutes. Love Supermetrics and Keyword Grouper Pro. Letting Google tell you what it wants to rank you for with the SC_keywords tab is super legit. Sending this to my team now. Thanks, Ross!
Ross Tavendale
Guru

A bearer of digital marketing wisdom.

Chris Romero
Thanks Chris - high praise coming from an industry vet like yourself.

Glad you found it useful.
Rachel Howe
Enthusiast

Occasionally takes part in conversations.

I've done keyword research similar to this with Excel and a little bit of Vlookups and Supermetrics, but its been a while. It was more of a manual than utilizing Google Sheets for GSC integration and SEMrush integration. Only thing I don't like is you have to pay monthly for the tool on top of other tools.

Overall, good stuff!
Ross Tavendale
Guru

A bearer of digital marketing wisdom.

Rachel Howe
The price can be a touch prohibitive. But you can always download CSVs and upload them to sheets. If you are really fancy I recommend using a service like zapier, scheduling reports and auto uploading them to drive so you can pull them in straight away. A bit more fiddling around at the start but you save the money on the additional tool.
Sheldon Campbell
Enthusiast

Occasionally takes part in conversations.

Brilliant, Ross! I haven't tried Supermetrics before - I may be tempted to, the next time I need to to do some in-depth KW research.
I love how you used array to pull the uniques... that's a huge time-saver.I can see I need up up my Excel game.
Ross Tavendale
Guru

A bearer of digital marketing wisdom.

Sheldon Campbell
Hey Doc! Yeah the sheets formulas are pretty unreal. If you haven't tried "query" yet (a pseudo SQL type formula_ give it a go - total game changer.
Andrea D‘Ottavio
Enthusiast

Occasionally takes part in conversations.

I am working with kw research for a website (I'm not an SEO!) on a friend website and I am trying several tools at the same time. Very time-consuming! Love your article Ross, probably it will help me save some time (and also learn new things, which is always a plus!). See you soon somewhere mate
Ross Tavendale
Guru

A bearer of digital marketing wisdom.

Andrea D‘Ottavio
Best of luck with it Andrea! Hopefully see you at an event soon :-)
Craig Campbell
Expert

Provides valuable insights and adds depth to the conversation.

Keyword research is one of those tedious tasks that takes a lot of time, but this is a great insight into someone doing it quick who is doing SEO on a day to day basis. Nice to see someone mixing up different sources and pulling it together quick and easy. Good job man.
Ross Tavendale
Guru

A bearer of digital marketing wisdom.

Craig Campbell
Thanks Craig. Bill Gates said if you want a job done fast ask a lazy person - i guess my mind just works permanently on "can't be bothered" with all the data heavy lifting.
Arnout Hellemans
Helper

An experienced member who is always happy to help.

Ross Tavendale
Hey...I have seen that phrase somewhere ;-). But indeed a great process. Quick Q, have you seen issues after the GSC update for filters on the 19th of August 2018? Or are you using an api call without any filters?
Hilary St Jonn
Enthusiast

Occasionally takes part in conversations.

This is great! You are right, the hard bit is actually going out and creating the content. Spending days and days on keyword research isn't always needed. This is way fast, thank you!
Ross Tavendale
Guru

A bearer of digital marketing wisdom.

Hilary St Jonn
Thanks Hilary - speed is where its at ;-)
Tristam Jarman
Helper

An experienced member who is always happy to help.

Another great video Ross! Whether you’re new to keyword research or consider yourself more of an expert I think this is a great video and well worth watching. Quick keyword research backed by real data, which doesn’t take a lifetime to execute :). Nice combination of data sources being brought together to drive content creation.

Not sure how many know of KeywordGrouper Pro™ but it’s definitely worth checking out. Think I actually saw Ross and Craig Campbell discuss it once on a webinar. Could be wrong.

The spreadsheet you use Ross would be great to share as I think a lot of people would use this and give them a solid direction of doing better keyword research. Granted you need Supermetrics etc.
Ross Tavendale
Guru

A bearer of digital marketing wisdom.

Tristam Jarman
Hey Tristam, I'm preparing a public friendly version at the moment. Will share on this thread when it's ready. :-)

I love keyword grouper pro - nice simple utility that very quickly tags things up for you.

Thanks for commenting.
Tristam Jarman
Helper

An experienced member who is always happy to help.

Ross Tavendale
Awesome Ross, looking forward to it. I think this will be helpful for so many people. I'll be sure to share...!
Peter Mead
Master

A veteran community member.

Nice update @rtavs! This is actually a fast way to do some really good keyword research. Lots of tutorals give quick keyword research but not to this level of results.
Supermetrics and SEMrush combo is a match made in heaven too!
Also the way you pulled in landing page info from Search Console.
Super fast for what it is, that's for sure.
Ross Tavendale
Guru

A bearer of digital marketing wisdom.

Peter Mead
Thanks Peter, I appreciate that. When you're agency side and need to get a complete marketplace overview for a pitch in a heartbeat - this is a really nice way of cutting things up and getting a birds eye view. I don't know where I would be with out the SEMrush API and Supermetircs.
Newcomer

Either just recently joined or is too shy to say something.

Worth Reading… Good Stuff Ross!

I especially like the idea if cross-referencing your final keyword list with the landing pages you have on the website and if landing pages for certain keywords are not available, creating new landing pages for those will only help you increase your chances of getting visible for a range of different keywords. And if your SEO game is strong you can always outrank competitors and win more leads and sale for your business.
Ross Tavendale
Guru

A bearer of digital marketing wisdom.

Moosa Hemani
Thanks Moosa - glad you found it useful. Love cross walking data from multiple sources :-)
Liraz Postan
Master

A veteran community member.

Oh wow! What a great guide! Also great for those who prefer video versus content, so thank you!
Ross Tavendale
Guru

A bearer of digital marketing wisdom.

Liraz Postan
Thanks Liraz - i learn better with video too :-)
Paul Lovell
Helper

An experienced member who is always happy to help.

Great Video a must for anyone to watch
Ross Tavendale
Guru

A bearer of digital marketing wisdom.

Paul Lovell
Cheers Paul - appreciate the feedback :-)

Send feedback

Your feedback must contain at least 3 words (10 characters).

We will only use this email to respond to you on your feedback. Privacy Policy

Thank you for your feedback!

Community Ranking System

Our SEMrush community rank reflects the level of your professional recognition in our community. We value quality contributions, so highly ranked members will get valuable incentives. Take part in discussions, write posts and speak on webinars, be friendly and helpful, and you will eventually get to the top of the ladder.

  • Newcomer
    Either just recently joined or is too shy to say something.
  • Enthusiast
    Occasionally takes part in conversations.
  • Helper
    An experienced member who is always happy to help.
  • Master
    A veteran community member.
  • Pro
    Asks great questions and provides brilliant answers.
  • Expert
    Provides valuable insights and adds depth to the conversation.
  • Guru
    A bearer of digital marketing wisdom.
  • Superstar
    Knows everything… well, almost.
  • Legend
    Getting here is not easy at all!