{"id":4194,"date":"2016-05-04T09:51:31","date_gmt":"2016-05-04T13:51:31","guid":{"rendered":"http:\/\/www.jacobking.com\/?p=4194"},"modified":"2016-05-04T10:09:59","modified_gmt":"2016-05-04T14:09:59","slug":"competitor-keyword-research","status":"publish","type":"post","link":"https:\/\/www.jacobking.com\/competitor-keyword-research","title":{"rendered":"Competitor Keyword Research on Steroids – The “Nom Nom Nom” Method"},"content":{"rendered":"

\"nom-nom-nom-keywords\"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”. <\/p>\n

Oh yeah folks, I’m going to be balling so hard when “nom nom nom method” is getting 50 searches per month!!<\/p>\n

Alright let’s get started, first thing you’re going to need an SEMrush account<\/a> (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!<\/p>\n

Start off by putting your money site domain into SEMrush. <\/p>\n

\"put-in-your-domain\"<\/p>\n

Now under “Organic Research”, go to “Competitors” and export that beast. Now we need to sort the common keywords from large to small. <\/p>\n

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.<\/p>\n

Hopefully you’re still with me, here is a link<\/a> to the exact spreadsheet I’m working with. <\/p>\n

google.com
\nyoutube.com
\nwikipedia.org
\nfacebook.com
\nlinkedin.com
\nquora.com
\ntoptenreviews.com
\nwordpress.org
\nsearchengineland.com
\nshoutmeloud.com
\nmoz.com
\nsearchenginewatch.com
\nwpbeginner.com
\nsearchenginejournal.com
\nblackhatworld.com
\nwarriorforum.com
\nyoast.com
\nbestvpn.com
\ntoprankblog.com
\nseobook.com
\nupcity.com
\nmatthewwoodward.co.uk
\nscrapebox.com
\ngsa-online.de
\nbloggingcage.com
\nnewipnow.com
\nbuzzstream.com
\nlinksmanagement.com
\nlinksearching.com
\nclambr.com<\/p>\n

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. <\/p>\n

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). <\/p>\n

https:\/\/www.semrush.com\/info\/<\/em> and jacobking.com<\/em> and +%28by+organic%29<\/em><\/p>\n

Make a nice list like:<\/p>\n

https:\/\/www.semrush.com\/info\/google.com+%28by+organic%29
\nhttps:\/\/www.semrush.com\/info\/youtube.com+%28by+organic%29
\nhttps:\/\/www.semrush.com\/info\/wikipedia.org+%28by+organic%29
\nhttps:\/\/www.semrush.com\/info\/facebook.com+%28by+organic%29<\/p>\n

I’m using this Firefox addon<\/a> 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. <\/p>\n

Something like this for example:<\/p>\n

\"semrush-filters-rule\"<\/p>\n

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.<\/p>\n

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? <\/p>\n

Well thanks to this dude for the guide on using terminal to merge CSV files<\/a>. The process is insanely simple, if you’re on a PC then let me Google that for you<\/a>. Sorry but I already spent enough time finding the solution for Mac, you PC people are on your own!<\/p>\n

Let me simplify it for you. <\/p>\n

Start by going to System Preferences > Keyboard > Shortcuts > Services<\/strong><\/p>\n

Select: “New terminal at Folder”<\/p>\n

\"new_terminal_at_folder\"<\/p>\n

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<\/strong>”<\/p>\n

\"new_terminal_at_folder_service\"<\/p>\n

Then you run this command:<\/p>\n

cat *.csv >merged.csv<\/code><\/p>\n

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. <\/p>\n

From here you can go a few different directions. You can use a filter to start categorizing the keywords.<\/p>\n

\"excel-filter\"<\/p>\n

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. <\/p>\n

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. <\/p>\n

Now I’m not an excel guru but here is what I whipped up:<\/p>\n

=VLOOKUP(“*”&B2&”*”,$A$2:$A$100, 1, FALSE)<\/p>\n

It’s not a perfect solution but it gets the job done.<\/p>\n

\"excel-filter-existing-example\"<\/p>\n

Just use the Custom Sort for Column C and sort it from A-Z. <\/p>\n

\"custom-sort\"<\/p>\n

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<\/a> for the idea on that, you’re a beast bro, hat tip. <\/p>\n

I’m tired now, so until next time, go rank some shit!<\/p>\n","protected":false},"excerpt":{"rendered":"

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… Read More →<\/a><\/span><\/p>\n","protected":false},"author":2,"featured_media":4237,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"_links":{"self":[{"href":"https:\/\/www.jacobking.com\/wp-json\/wp\/v2\/posts\/4194"}],"collection":[{"href":"https:\/\/www.jacobking.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.jacobking.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.jacobking.com\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.jacobking.com\/wp-json\/wp\/v2\/comments?post=4194"}],"version-history":[{"count":0,"href":"https:\/\/www.jacobking.com\/wp-json\/wp\/v2\/posts\/4194\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.jacobking.com\/wp-json\/wp\/v2\/media\/4237"}],"wp:attachment":[{"href":"https:\/\/www.jacobking.com\/wp-json\/wp\/v2\/media?parent=4194"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.jacobking.com\/wp-json\/wp\/v2\/categories?post=4194"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.jacobking.com\/wp-json\/wp\/v2\/tags?post=4194"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}