Wow it’s been too long, time to brush the dust off this blog. Today we’re going to be diving into a method for keyword research with a bit of secret sauce. In the spirit of other marketing gurus coining phrases such as the “skyscraper method”, I’m going to call this technique the “nom nom nom method”.
Oh yeah folks, I’m going to be balling so hard when “nom nom nom method” is getting 50 searches per month!!
Alright let’s get started, first thing you’re going to need an SEMrush account (link is for 30 day trial if you’re interested). Before some of you start spazzing out, OMG Jacob you affiliate link pushing trickster! How could you try and make some money with your blog content?! Just calm yourself. This is not something you can accomplish without SEMrush. And most of you geeks probably already have a subscription so let’s get down to it!
Start off by putting your money site domain into SEMrush.
Now under “Organic Research”, go to “Competitors” and export that beast. Now we need to sort the common keywords from large to small.
I scrolled down to about row 50 and inserted a new row. This way I can now sort this group easily by “Organic Keywords” from high to low.
Hopefully you’re still with me, here is a link to the exact spreadsheet I’m working with.
google.com
youtube.com
wikipedia.org
facebook.com
linkedin.com
quora.com
toptenreviews.com
wordpress.org
searchengineland.com
shoutmeloud.com
moz.com
searchenginewatch.com
wpbeginner.com
searchenginejournal.com
blackhatworld.com
warriorforum.com
yoast.com
bestvpn.com
toprankblog.com
seobook.com
upcity.com
matthewwoodward.co.uk
scrapebox.com
gsa-online.de
bloggingcage.com
newipnow.com
buzzstream.com
linksmanagement.com
linksearching.com
clambr.com
Ok good stuff now I’ve got a nice chunky list of my top competitors. Now we need to download organic ranking reports for each of these which is a bit tedious but unfortunately there is no easy work around I could find. What you can do to speed up the process is use excel to create the SEMrush URL string and open all the tabs at once. Then tab through and download all your reports.
See you can just break it apart and use the & function to combine your list (Check the second sheet in the workbook I shared above if you need clarification).
https://www.semrush.com/info/ and jacobking.com and +%28by+organic%29
Make a nice list like:
https://www.semrush.com/info/google.com+%28by+organic%29
https://www.semrush.com/info/youtube.com+%28by+organic%29
https://www.semrush.com/info/wikipedia.org+%28by+organic%29
https://www.semrush.com/info/facebook.com+%28by+organic%29
I’m using this Firefox addon but depending on the browser you’re using you’ll need something to open multiple tabs at once. Before we go forward you might have noticed something about the domains I used in the sample above. They’re monstrous and I definitely don’t want to download their entire organic ranking report even if I could pull the entire report. This is where you’ll need to use some filters for the big boys.
Something like this for example:
Get creative with it, see in the example I had to use “Word Matching” instead of “Containing” since using the phrase “SEO” returned all kinds of crazy shit.
For some competitors with we won’t need to filter at alL, just download the whole report. Ok you have a shitload of reports, now what?
Well thanks to this dude for the guide on using terminal to merge CSV files. The process is insanely simple, if you’re on a PC then let me Google that for you. Sorry but I already spent enough time finding the solution for Mac, you PC people are on your own!
Let me simplify it for you.
Start by going to System Preferences > Keyboard > Shortcuts > Services
Select: “New terminal at Folder”
Now you can make a folder on your desktop and drag all the semrush reports in there. Right click that folder and under services you’ll see the option for “New Terminal at Folder”
Then you run this command:
cat *.csv >merged.csv
Alright now we’re getting somewhere, we’ve got all the competitor reports merged in to one huge report. The next step is to remove duplicates and filter the list some more. In excel use Data > Remove Duplicates and select Column A the keyword column. Ahh stare at the dupe removed goodness.
From here you can go a few different directions. You can use a filter to start categorizing the keywords.
One issue you might have is removing duplicates from this new list based on your existing list of keywords that you’ve already targeted. For example let’s say I already have a post on “link building tips” and this keyword is in my new list. I obviously don’t want to hit that phrase again so we’re going to need to get rid of the ones already used.
We need to create two new columns in excel. The first one containing our existing keywords we’ve already targeted with our site. The second using a Vlookup formula to identify if the keyword has been used or not.
Now I’m not an excel guru but here is what I whipped up:
=VLOOKUP(“*”&B2&”*”,$A$2:$A$100, 1, FALSE)
It’s not a perfect solution but it gets the job done.
Just use the Custom Sort for Column C and sort it from A-Z.
There you go, you’ve just isolated the unique new keywords. Well you should be able to have all kinds of fun from here. A quick note though, it can get a bit messy with trying to identify partial match keywords that you’ve already hit in the past. The only solution I could find was using an excel addon called “Fuzzy lookup”. Thanks to Chris Dyson builder of links and pillager of SERPs for the idea on that, you’re a beast bro, hat tip.
I’m tired now, so until next time, go rank some shit!
Its about damn time, glad you are back and looking forward to the new posts.
Thanks Brian, the fire is being fueled!!
I want to be the fist to welcome you back to Blogging we all have missed you Jacob.
Nice, and good to see you back. I thought that you had abandoned the blog, man!
Great post but tbh more looking forward to the PBN posts…currently building one out myself and always on the look out for the pro’s opinios.
Keep up the good work.
We were missing your posts!!!
It was a long time waiting, man. Reading your last email made me smile.
Waiting for all of the stuff you are about to release.
Regards,
Militsa
Awesome, looking forward to more blog post from you!
Pure Gold! That is all. Also welcome back! I thought you were dead seriously.
Nah not dead, hibernating maybe, but shit I’m about to turn 30 years old this year, I gotta get rolling!!
Great Post. I have been doing this exact method a lot recently. I have been using webharvy to automate the process of downloading all the organic results. For your sheet, you can wrap your vlookup in a if(iserror to make it look a little nicer [=if(iserror(VLOOKUP(“*”&B2&”*”,$A$2:$A$1030, 1, FALSE)),”No”,”Yes”)]
Hey thanks for the tip Tom!
Jacob. Tanks for sharing
Jacob, You must slow down, you’re overdoing it.
Loved this report and could see the amount of work you put into it.
Great info, shall use but at a slower pace…..
Gordon.
Thanks Jacob,
This was an nice multifaceted bit of SEO workflow education. You’re helping me to get my head around SEMrush tools at the same time. Thanks.
Glad you enjoyed it, quite a few little tricks sprinkled in there.
Glad you are back this was an enjoyable . Look forward to more – how to get free content!
Great to have you back, Jacob! The humour in the intro reminded me of why I love reading your posts. Looking forward to checking out more of your tips and tricks mate!
Great post, Jacob!
I miss you so much man!
Glad you are still alive, lol.
Anyway, do you think that link building using GSA SER for tier 1, “of course with proper settings”, is still a good thing nowadays, just like in your old post, “Why Your Tiered Links Won’t Rank Shit”, is that still work well.
i hope you create an update about that, and also what kind of backlinks that work best nowadays. people always talk about pbn, but some people like me might be do not really love this, since it is too costly.
Thanks Cholzie, yeah this is a sensitive subject. I’ll get there eventually.
Why do you prefer SEMRush for this and don’t use ahrefs instead? I found SEMRush data to be quite off/outdated in some niches at least. Does it actually catch more keywords than ahrefs?
Well ahrefs keyword stuff is so new I haven’t even tried it tbh. I can’t imagine it would be have more depth than semrush but need to play with it for sure.
Glad to see you back Jacob. Thanks for sharing very unique method.
I hope you will share more cool methods.
Cheers!
Upsell a video of the process at the end of the post for $10? :)
What’s going on man?
Nice to see you back, Feedly was empty without you!
Hey thanks dude, now let’s see if I can actually keep this train rolling!!
Hey Jacob, good to see you back. I’m putting together some resources for VA’s for keyword research. They should be able to follow this. :p
Hell yeah, crush it man.
Couldn’t believe I finally got an email from you again. It’s been a long time. I’m Glad you are back. About the article I normally use Ahrefs. I love that they actually email you back when you put some task, and it triggers itself with an auto report. But may be Semrush is worth the try. We hope for a new post soon! =)
Nice, nice. SEMRush link dead as of August or is it me?
Nah I’m looking into it.
Hi Jacob,
Amazing method in finding new keywords.
There are many keywords we can discover through SemRush, just sometimes the search volume matters. High search volume keywords tend to have higher competition. Anyway, long tail keyword might help in getting traffic if the website is not ranked by search engines yet.