Ranking Tool In Excel
  • Hi all!

    I thought I'd share something I've made using the awesome seotools excel plugin! It is a ranking tool that crawls google properties using xpath and returns the associated a ranking position for a search term (this could be a page url, domain or even a broad search term).

    Paste your keywords in row 3, change the locality in cell B4, and your own search/filter term in cell B2... the results are summarized in the summary tab.

    You can also add an interval wait time (in milliseconds)...

    The only problem is that it is quite resource heavy I think as it crawls google 3 times for each term. So... just start slow

    Mike

    Keyword ranking tool (quick) v4 - 10 results only.xlsx
    16K
  • 20 Comments sorted by
  • Really cool mike! Thanks for sharing!

  • Thanks for sharing! I'm new to using functions, but this was helpful. :)>-

  • Hi Mike, Sounds great but I can't get it to work - perhaps I'm being thick:) I'm trying to track keywords for the domain www.maillife.co.uk - so in B2 do I enter "www.maillife.co.uk" or something else? I'm using "co.uk" for the locality. When I use "com" I get nothing as well. Keywords are "mailshop" and "mail life". All I'm seeing is "NAME?" in all cells. cheers james

  • Actually, perhaps a key question is does this work on Mac or only for Excel on Windows?

  • Hey, I don't think it's working for me anymore.

    It keeps saying #ADDIN?

  • hey digital people,

    Have you installed the SEOtools plugin?

  • Being an Excel programmer, I just tidied it up a little...

    SEOTools Keyword Research.zip
    35K
  • ...oh, I also added PageRank & Backlink retrieval, and a primitive scoring system for keyword difficulty, and I'm in the middle of integrating Search Volume from https://seogadget.co.uk/google-adwords-plugin-excel/

  • @harvest316 - Did receive access to the Google Adwords API? I requested access telling them that my intention was to use that tool. They declined my access. I'm wondering if you received it?

  • @harvest316 - awesome, totally made it a ton better so thanks for that! @jeffbell I think you need MCC access to get approval for the API.

  • Hi guys,

    I've been rearranging this file to work with 20 keywords and reach ranking up to position 120 and it's all fine, except the fact that when we search for some specific keywords and we have more than 10 results (I've seen it reaching 17 results only in the 1st page) where some of these results are only local "Google+" suggestions, but are counted anyway within the ranking result. This happens for keyword "boat charter" for "sunsail.co.uk" domain. I had to extend the =XPathOnUrl(B11,XPathExpr,Attr,BuildHttpDownloaderConfig(,Wait)) a few more rows to grab it otherwise it would say that ranking was >30 when it really is on 6th position.

    I was trying to exclude these results by changing the XPath Expression (Detail worksheet cell B2) with: //li[@class='g' and not(contains(.,'@style=margin-bottom:14px'))]//h3[@class='r']//a[not(contains(@href,'tbm=plcs') or contains(@href,'/search?q='))] and with //li[@class='g' and not(contains(@style,'margin-bottom:14px'))]//h3[@class='r']//a[not(contains(@href,'tbm=plcs') or contains(@href,'/search?q='))] and some other variations...

    This because, these results vary from the normal ones just in it's style inside the li class="g" style="margin-bottom:14px" instead li class="g" .

    I tried as well to exclude this suggested links with &pws=0&gl=UK on cell B11 like this: ="http://www.google.co.uk/search?q="&UrlEncode(C8)&"&pws=0&gl=UK"

    But no luck...

    Does anyone had this issue before or can help me excluding these results? Because like this instead having this domain on the 6th position, I have it on the 13th position what isn't correct...

    Thanks in advance, Fred

  • I'm having the same issue. It's due to local search. The key difference between the regular results and local search is that the local results have a style of "margin-bottom:14px" while the rest of the organic results don't. They are all in "class=g" so is there a way to change the XPatch Expression to count for all results in "class=g" but exclude them if the style of "class=g" is equal to "margin-bottom:14px"???

    I'm not a wiz when it comes to XPath so any help would be greatly appreciated.

    Thank you.

  • yaright @fc12 - have you tried

    //li[@class='g' and not(contains(@style,'margin-bottom:14px'))]//h3[@class='r']//a[not(contains(@href,'tbm=plcs') or contains(@href,'/search?q='))]

    used scrape similar to test and seems to work as required..

  • whoops saw that you did!

  • here you go... it didnt like style as an attribute so here is a starting point alternative

    //li[@class='g' and not(.//table)]

    it avoids anything with a table element in it

    this works pretty well:

    //li[@class='g' and not(.//table)]//h3[@class='r']//a[not(contains(@href,'tbm=plcs') or contains(@href,'/search?q='))]

  • thanks mikeyd it works as supposed now... it collects the exact ranking position for the keyword.

    mikeyd rules! and now I have an awesome ranking tool checker that collects rankings for 20 keywords at once with positions up to 120.

    cheers, Fred

  • awesome! glad to help... it should have worked as you suggested but the style attribute wasnt interpreted as expected - might be worth asking Niels? also if you wanted to share the tool I wouldn't say no ;) cheers

  • Hi Guys, thanks for the file, its proven to be really useful, I'm not great with Excel and wanted to know if anyone can share the version which allows me to add more keywords and do you believe it would be possible to make one that would analyse 500 keywords? would excel handle that amount of data?

  • Good job guys with the Rank Tool. I love it

  • Nice, thanks mikeyd!

    I recommend this article on rank tracking and how to get arguably more accurate data using Google Analytics & Excel: http://www.blindfiveyearold.com/new-ways-to-track-keyword-rank

    Also, I suggest using SEO Gadget's mozscape api tool to get more info on the results (DA/PA/links/root domains).

    Oh and I also use this to remove "sitelinks" results (in case you're doing branded search which will skew the results, search for "toyota" for example): =XPathOnUrl("http://www.google.com/search?q=dogs&pws=0";"//h3[not (ancestor::div[@class='sld'])][@class='r']/a[contains (@href, 'http')]";"href")

    (the [not (ancestor::div[@class='sld'])] worked to remove the sitelinks)

Like SeoTools for Excel?

Help me keep SeoTools free!
Please Donate.

Having trouble?

Check Troubleshooting before posting.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Login with Facebook Sign In with OpenID

In this Discussion