How to build your own, free SERP tracker in Google Docs

A little while ago, I was carrying out some linkbuilding to a whole heap of target URLs, and with a variety of keyword anchors. As my links were built, I realized I didn’t actually have a tool available to me to track the ranking positions of those URLs in the SERPs. I tried using some of the big software systems we use here at Red or Blue, but none of them could really do the job I wanted. I turned to other standalone online tools that are supposedly designed for SERP tracking, but even they couldn’t help me. At most, they could track a few pages for a couple of keywords, and I had more than 50 pages I needed to track for half a dozen keywords…I was at a loss as to what I should do, until I discovered this great blog post by Tom Critchlow , which explains how you can use Google Docs spreadsheets to carry out a variety of functions that are incredibly useful to any SEO. Tom does a great job of explaining how various functions and how they can be implemented, so I suggest you read his post first to get a feel for the concept first.

But on to what we’re here for. The SERP tracker. Monitoring SERPs is hugely important for any SEO, but most sophisticated software suites are surprisingly light on this front. Sure, if you have a project set up for a client site, you can track its rankings very accurately, but what if you, like me, have built a variety of links to specific pages and would like to see how they fare in the SERPs? Well, the tool I’ve made will help you do that.

You can take a look at a version of it here , just make a copy of it and you’re good to go! All you need to do is fill in the keyword and URL columns, copy the formula from the ranking column, and hey presto! Ranking value! (A quick note, when inputting your URL, make sure you include the http://, or else it sometimes doesn’t seem to work properly. It’s probably best to copy and paste the address from your browser). You are most welcome, I hope that fills in some of your SERP checking needs. At the moment, I’m still working on the incorporation of a script to record past ranking data on a daily basis, since at the moment this tool is more a “SERP checker” than a full “SERP tracker”. I’m very close to finding a neat solution though, and I will update this as soon as I have.

So how does this shiny little toy work then? Essentially, it all boils down to one formula:

=ArrayFormula(MATCH(1, FIND(B2,importxml(concatenate(“http://www.google.co.uk/search?num=100&q=”&A2&”&pws=0&gl=UK”),”//h3[@class='r']/a/@href”)),0))

Broadly speaking, what this formula does is run a search on Google UK for the keyword in cell A2, returning the top 100 results, and scrapes the page URLs of those results. It then checks the target URL in cell B2 against those returned pages to try and find a match. If it finds one, it records at what position it occurs, and displays that value as your result. Simple, really….or not. Any more detailed explanation is very complicated and frankly, somewhat beyond my skills. If you want to tweak the search function, either for different localisations, or different search engines entirely, you have to pay great care to ensure the search string is a valid one. You may need to go into Google’s settings to disable instant search and sign out of any google accounts to ensure that you get a clean search string.

Anyway, that should provide you with more than enough information to use my SERP tracker, or build your own, better one (If you do, I’d love to see it!).  And as I said, I’m working on building a script to properly log the results too, so if anyone has any advice on that particular problem I’d love to hear from you as well!

 

Written by Shaun Myandee

Information Analyst at Red or Blue, has a weakness for shiny toys and tech, is an avid football fan, and loves all that London has to offer (especially food and drink...) Is also the world's best looking SEO guy.

Comments

6 Comments on "How to build your own, free SERP tracker in Google Docs"

  1. Matt says:

    this is a very interesting and useful concept that could simplify things for a lot of people who dont use the various forms of software….

    I seem to have it working, but cant get the results to be too accurate yet, but will keep testing, great idea!

  2. Shaun says:

    Hi Matt,

    Glad you liked it!
    I agree, it doesn’t always seem to be super accurate, but it is still a handy part of the SEO toolkit, I think. I’ve actually solved the script problem I mentioned, and now have a version that can display the previous day’s result, as well as the result at the end of each week, which is handy for keeping an eye on progress. I’ll be writing a post about that very soon!
    Let me know if you manage to come up with any improvements yourself!

  3. Matt says:

    Hi Shaun

    thanks for your response. Ive found the current inaccuracy prevents it from being used, but its an excellent concept. The program rank tracker, has a report feature and an auto check/export to HTML file, which can be used to create a page that is always updated with the latest rankings. Personally, I feel that the standard reports have way too much clutter and noise in them, but Im working on getting those templates changed around, and then being sent to my password protected site which the client has for himself, a “marketing dashboard” so to speak. The idea is that an SEO company can have more of a hands free approach to SEO reporting, but this simplifies things and makes it a lot easier than using software, that is once it gets working and is accurate enough for use. Please email me etc if you want a guinea pig.

  4. Shaun says:

    Hey Matt,

    Yeah, I know what you mean. Sadly it seems to be all but unusable. I can’t quite get my head around why at the moment though. It used to work a while back, quite well in fact. Recently however it just seems….broken. Ah well, I think I may have to abandon the Google Docs experiment and go back to more “conventional” solutions. Or maybe I’ll just try to build my own one from scratch…but I’m not sure my skills are up to the task :P

  5. Rheace Jones says:

    Hello,

    Great post, it seems to be working for me. I was just wondering if it updates automatically?

  6. Shaun says:

    Hi Rheace,

    The tracker will retrieve results each time you open it. If you want to store past data, you’ll need to write a little script that runs the function and logs it into a separate cell.


Here's your chance to leave a comment!

HTML: You can use these tags: <a href="" title="" rel=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>