Competitor Keyword Research with Google Sheets and SEMrush

Posted By on May 10, 2019



good morning hello from coatings for losers HQ at least the current headquarters today I want to share with you a template that will be going live on rank tank pro in July rank tank Pro is the Members Only site for all the SEO Google sheets templates that we build it at coding is for losers they don't require add-ons they pull data directly from services like SEM rush Google Analytics Google search console stuff like that to automate your work in Google sheets so this template is a really fun one and I had a lot of fun building this and using it from my own blog at coding is for losers what it does is it takes keywords that you already rank for and goes out and finds basically keywords that your competitors for those main keywords are also ranking for and lets you build a kind of keyword map of longtail keywords and some you know some more main keywords that you could either rank those pages for they are already ranking for full rank new pages for just generally gives you an idea of what other keywords in this universe of keywords that your site already ranks for can you go after and it uses data from SEM rush the SEM rush API to do all of this all of this searching for you it's a massive massive bumblebee up there having a good time so how does this template work the first thing it does is it pings the SEM rush API and pulls in top keywords for your site so the second step on this getting started tab is it pulls in your top key with top five keywords so for me that's Google sheets formulas vlookup multiple criteria if you look up Google sheets Google sheets functions and block spring that's the top five keywords that I'm ranking for and then the third step is you press in this ranked tank Pro menu you're you're following a ranked tank pro menu for all of these you'll say find your competition and what that does is it finds the actual sites that are competing with me on those terms so it searches for each of these keywords so Google sheets formula as we look at multiple criteria etc etc and pulls in the top five ranking pages for each of those keywords so it says who's my competitors here and then the last step step is it finds other keywords that those competitors competitor pages are ranking for so if you look at the spinal tab and all this aggregation is done for you it's pulling in other keywords that this page specifically this is a Google sheets tutorial page is ranking for and what this ends up as is you have you have kind of a full map database here of pages that that are ranking for you keywords that they're ranking for and then all the other possible keywords that you could attack based on what your competitors are ranking for for those same kind of focused keywords so it gives you kind of this database but the kind of you know the main action of how you'll use this and you zoom out a little bit you can see this whole page is it basically builds like a search menu for you so you can filter by any specific page that you're ranking for and look for for other focused keywords here you can focus on one specific keyword so Google sheets formulas in this case so we'll show you all the other keywords that you could go after their position for your competitor their search volume cost per click and kind of SEM Russia's competition score you can dig through this this huge list based on what your competitors are ranking for so you can look at a specific competitor specific page a specific keyword and see what it suggests that you also shoot to rank for so I really recommend using this template if maybe you're starting to work with a client and you you don't know the landscape of their their market that well and you want to get a feel for what their competitors are doing it's a really yeah it's a really good exercise to go through before you start building a content strategy or or site map if you're if you're adding on to to a site so hope you enjoy it like I said you can get this template through through the ranked tank pro membership site that I'll link to in the description take care

Posted by Lewis Heart

This article has 2 comments

Leave a Reply

Your email address will not be published. Required fields are marked *