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.
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
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.
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 https://www.semrush.com/blog/5-best-keyword-research-tools-for-startups/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.
Comments
Either just recently joined or is too shy to say something.
Great thinking behind all this. I like it!
Did you ever get around to cleaning up that template?
TIA
Either just recently joined or is too shy to say something.
Great tutorial!
I wondered if there was any update on the template?
SEMrush columnists are authors who had proved their expertise in digital marketing and contribute regularly to our community.
An experienced member who is always happy to help.
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.
SEMrush columnists are authors who had proved their expertise in digital marketing and contribute regularly to our community.
An experienced member who is always happy to help.
SEMrush columnists are authors who had proved their expertise in digital marketing and contribute regularly to our community.
Either just recently joined or is too shy to say something.
As an old school SEO guy I love this type of creativity step by step out of the box method.
Thank you Ross for this actionable post.
You have won yourself a new fan.
Seo is indeed a kungfu .
It's formless like water (every customer problem is unique)
Yet I definitely agreed with your Bruce Lee quote .
A Bruce Lee and Ross fan here.
Jeff
Either just recently joined or is too shy to say something.
As an old school SEO guy I love this type of creativity step by step out of the box method.
Thank you Ross for this actionable post.
You have won yourself a new fan.
Seo is indeed a kungfu .
It's formless like water (every customer problem is unique)
Yet I definitely agreed with your Bruce Lee quote .
A Bruce Lee and Ross fan here.
Jeff
Asks great questions and provides brilliant answers.
SEMrush columnists are authors who had proved their expertise in digital marketing and contribute regularly to our community.
An experienced member who is always happy to help.
SEMrush columnists are authors who had proved their expertise in digital marketing and contribute regularly to our community.
Either just recently joined or is too shy to say something.
SEMrush columnists are authors who had proved their expertise in digital marketing and contribute regularly to our community.
Either just recently joined or is too shy to say something.
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
SEMrush columnists are authors who had proved their expertise in digital marketing and contribute regularly to our community.
Either just recently joined or is too shy to say something.
Asks great questions and provides brilliant answers.
SEMrush columnists are authors who had proved their expertise in digital marketing and contribute regularly to our community.
SEMrush columnists are authors who had proved their expertise in digital marketing and contribute regularly to our community.
Didn't know about Keyword Grouper Pro.. I'll give that a go !
Thanks !
SEMrush columnists are authors who had proved their expertise in digital marketing and contribute regularly to our community.
https://www.jonoalderson.com/tagger/
An experienced member who is always happy to help.
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.
SEMrush columnists are authors who had proved their expertise in digital marketing and contribute regularly to our community.
Either just recently joined or is too shy to say something.
Will this method still work with Google's new way of reporting keyword/landing page data?
Either just recently joined or is too shy to say something.
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
SEMrush columnists are authors who had proved their expertise in digital marketing and contribute regularly to our community.
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.
Either just recently joined or is too shy to say something.
Is that something which can be incorporated into the template?
David
Either just recently joined or is too shy to say something.
Great video :)
where can I download the actual template ?
Best,
Max
Provides valuable insights and adds depth to the conversation.
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.
SEMrush columnists are authors who had proved their expertise in digital marketing and contribute regularly to our community.
Either just recently joined or is too shy to say something.
Asks great questions and provides brilliant answers.
SEMrush columnists are authors who had proved their expertise in digital marketing and contribute regularly to our community.
Either just recently joined or is too shy to say something.
SEMrush columnists are authors who had proved their expertise in digital marketing and contribute regularly to our community.
A bearer of digital marketing wisdom.
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.
SEMrush columnists are authors who had proved their expertise in digital marketing and contribute regularly to our community.
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 :-)
A bearer of digital marketing wisdom.
Either just recently joined or is too shy to say something.
SEMrush columnists are authors who had proved their expertise in digital marketing and contribute regularly to our community.
Either just recently joined or is too shy to say something.
SEMrush columnists are authors who had proved their expertise in digital marketing and contribute regularly to our community.
Glad you found it useful.
Occasionally takes part in conversations.
Overall, good stuff!
SEMrush columnists are authors who had proved their expertise in digital marketing and contribute regularly to our community.
Occasionally takes part in conversations.
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.
SEMrush columnists are authors who had proved their expertise in digital marketing and contribute regularly to our community.
Occasionally takes part in conversations.
SEMrush columnists are authors who had proved their expertise in digital marketing and contribute regularly to our community.
An experienced member who is always happy to help.
SEMrush columnists are authors who had proved their expertise in digital marketing and contribute regularly to our community.
An experienced member who is always happy to help.
Occasionally takes part in conversations.
SEMrush columnists are authors who had proved their expertise in digital marketing and contribute regularly to our community.
Provides valuable insights and adds depth to the conversation.
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.
SEMrush columnists are authors who had proved their expertise in digital marketing and contribute regularly to our community.
I love keyword grouper pro - nice simple utility that very quickly tags things up for you.
Thanks for commenting.
Provides valuable insights and adds depth to the conversation.
A veteran community member.
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.
SEMrush columnists are authors who had proved their expertise in digital marketing and contribute regularly to our community.
Either just recently joined or is too shy to say something.
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.
SEMrush columnists are authors who had proved their expertise in digital marketing and contribute regularly to our community.
A veteran community member.
SEMrush columnists are authors who had proved their expertise in digital marketing and contribute regularly to our community.
A veteran community member.
SEMrush columnists are authors who had proved their expertise in digital marketing and contribute regularly to our community.