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:
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!