As one of the product analysts for GOV.UK, search is my main focus. Recently I built a handy tool to get an overview of Google search results, to help us review and optimise our content. I thought it was worth sharing as other people might find it useful, and my version was inspired by the work of others.
How to use the rank checker
If you just want to get on and use it, you don’t need to know much about how it all works. First, get your own copy of the spreadsheet:
- You’ll need to be logged in to a Google/Gmail account with access to Google Drive (formerly Google Docs)
- Open the view-only Google SEO rank checker spreadsheet
- Select File > Make a copy...
- Give the document a name and click OK
Now you’ll have an editable version, ready to customise with the keywords you want to check:
- Enter your website URL in the top left corner (eg https://www.gov.uk)
- Enter your keywords or phrases in the top row - one in each column
- (Optional) Enter the URLs you're interested in into the first column, from cell A5 downwards
- (Optional) Highlight the search results section from row 17 to the bottom, select Format > Conditional formatting..., and enter your website URL next to 'Text contains'
You should now see the top 50 Google UK results for each of your keywords, with any results for your website highlighted in yellow. At the top, you’ll see your current highest position for each keyword (or ‘Not in top 50’), followed by the position of any specific URLs you’ve entered.
You can insert as many extra rows as you want for specific URLs (copy the formulas from the row above), or delete rows if you don’t need them. And you can add more keyword columns, up to a limit of 50 per spreadsheet. To keep things simpler and faster, use several spreadsheets for different topics/sections rather than cramming all your keywords into one big file.
Bear in mind that the results will be reloaded every time you open the spreadsheet, so if you want to save a snapshot of a particular day, copy and paste the data to a new sheet using ‘Paste values only’ (or take screenshots).
Note: If you see #N/A or #VALUE! because the results haven’t loaded properly, try re-entering or changing the keyword, or just wait a while for the data to refresh.
How the spreadsheet works
The core function is a Google Spreadsheets feature called ImportXML, which ‘scrapes’ information from a web page or a file. In this case, it’s extracting the URLs from search results.
Credit must go to Nathan Grimm, a marketing manager from Seattle who shared a formula that works, which got me started. Lots of people have written useful guides to building SEO tools (for search engine optimisation) using ImportXML, but the older methods no longer work properly with Google’s current search result format.
After breaking down the formula to understand how it worked, with a bit of trial and error I adapted it to find the highest ranking for any URL beginning with ‘https://www.gov.uk/’. At first it was a simple vertical list of keywords, but I realised that to check several URLs it was using an ImportXML call each time and would soon hit the limit of 50 per spreadsheet.
It also seemed more useful to see the actual results at a glance, rather than just the ranking numbers. So I rejigged it to a horizontal layout with the keywords along the top, and split the formula into three parts:
- First, the ImportXML formula in row 17 fetches the top Google search results for the keyword in row 1, and shows them from row 17 downwards.
- The formula in row 2 looks at this list of results to see if it can find the website from cell A1, and if so, in what position. If there’s no match, it shows ‘Not in top 50’.
- The formula in rows 5-14 looks at the same list of results to see if it can find the specific URL in column A, and if so, in what position. If there’s no match, it shows a blank space.
This way, you can compare the results side by side for a set of related keywords, and each keyword only needs one ImportXML call, no matter how many URLs you check against its results.
The Google search URL in the ImportXML function includes several variables:
- &pws=0 gets the ‘standard’ results as seen by users who aren’t signed in to Google, rather than your personalised results
- &gl=UK gets results from Google UK - change it to another country code if you need to
- &num=50 gets the top 50 results - change the number if you want more or fewer
Annoyingly, the conditional formatting in Google Spreadsheets can’t automatically use the URL from the top left cell. I looked into doing this with a script but it seemed complicated, so for now the URL has to be entered separately in the conditional formatting settings. You could also highlight your main competitors or partners in different colours for an easy overview.
This is simply a spreadsheet so everything can be customised to suit you. If you make something better, why not share it?
Why GOV.UK cares about ranking
There’s debate among search analysts and marketers about how much rankings really matter. Studies have claimed that up to 53% of clicks go to the top result (or 36%, or 18%). Most people choose one of the first few results, and rarely look past the first page. But tracking your exact position is less meaningful now that search results are increasingly personalised for different users.
What matters most for GOV.UK is that people can quickly find accurate information about government services. That’s an essential part of our aim to be ‘simpler, clearer, faster’. If people can’t find what they need online, they may end up using more expensive telephone services or paper forms.
Some other sources might not be accurate, either because they’re out of date, or they’ve misunderstood something, or they’re slightly misleading - for example, charging for a service that’s available free, or charging a premium for checking an application. So we want to make sure that people can easily find the official, current government information near the top of their search results.
For more details about how we’re using search analytics, see Lana Gibson’s posts: The SEO war – fighting the good fight in search and SEO for GOV.UK.
9 comments
Comment by Tara Stockford posted on
Hi Anthony
This seems to be a widespread problem affecting the ImportXML function in the old version of Google Sheets.
I was expecting Google to automatically convert my old spreadsheet to the new version of Google Sheets, but since that hasn't happened yet, I've made a new spreadsheet manually:
https://docs.google.com/spreadsheets/d/1g40HRAa6ep-j-QXySfq28-3vqJUXaVWynrAHndu4Ljk/edit?usp=sharing
This also has the advantage of no longer being limited to 50 keywords per spreadsheet.
Comment by Onur Ozen posted on
Thanks for the post and tool. As you have told on Help sheet on the doc, tool sometimes produce #N/A results. I have realized that image results on SERPs causes this. So, I have updated the formula and filtered image results. Here is my revised version: https://docs.google.com/spreadsheet/ccc?key=0AnyzdAWnpBUHdEpsNXkzOWxzM05WbmtTYjhIaUVoaFE&usp=sharing
Please check out and let me know about your comments.
Comment by Anthony posted on
Onur, this tool is great and I've been using it for sometime now in the U.S. and noticed that it has not been working lately. Both the original version and your updated version give only #VALUE results. I've tried everything to refresh the results that I've done in the past but nothing seems to work. Any suggestions? Thanks.
Comment by Martin Broadhurst posted on
Great post. This spreadsheet is incredibly useful. I dare say this is one of th best uses of tax payers money I have come across 🙂
Comment by Ryan posted on
Google Docs is a great XML parser however it does have restrictions on how many rows you can have. To have a full understanding of an industry scope, a full keyword analysis should be created which covers every possible keyword within the particular industry which is segmented by groups. For example my startup is mainly location based around the UK and looking at London boroughs, therefore I group keywords by location name. Doing this creates many thousands of results. On top of that, Google will start to block automated requests so you may find that results are showing as not ranking when infact they really are. I can't seem to find any ability for Google Docs to have a random sleep timer? Google Docs will be good for maybe 50 keywords at most, but its not scaleable. Above 50 keywords, ranking software should be used or if you're able to, create your own.
Comment by grapii posted on
Thanks, this is just what I needed to help review
Comment by Susan posted on
really helpful - thanks for sharing!
Comment by Graham Lee posted on
This is brilliant!
Comment by Luke posted on
Thanks for sharing!
If you want to search more than one word wrap with single quotes: 'blue badge'