How to Make a Rank Checker with Google Docs

This is a pretty quick and dirty SEO tool for quick rank checks and “hunch verification,” and it isn’t really a replacement for a full application (except for the Advanced checker, but we’ll get to that). However, if you’re not quite ready to shell out the cash for a full application, this handy strategy will help you stay on top of your site’s search engine rankings until you can afford the SEO big guns. Unfortunately, each spreadsheet is limited to 50 XML calls, and occasionally the formula will be finicky and not return any results at all, just because it feels the need to be huffy (read: Google throttles it). But anyway, onto the practical application.

Part 1: Meet ImportXML

First off, here’s the ImportXML syntax:

=importxml(“URL”;”query”)

Where the URL is the address of the XML or HTML file you wish to reference and the query is the XPath code that searches for and gathers the information from the specified URL. For example, the formula =importxml(“www.whitefireseo.com”;”//a/@href/”) will import all of the URLs and anchor texts (elements with the ‘href’ and ‘a’ attribute) from my homepage into a spreadsheet. Crack open a Google Spreadsheet and give it a try!

The ImportXML function will help you quickly gather data from webpages into a digestible and editable format. There’s almost no end to what you can do with this function; you can use it to grab all of the urls on a page or in a certain area, you can use it to return the first 10 Google search results into a spreadsheet, you can use it to pull all of the Javascript elements off of a page, check your website ranking, and so on.

In the next part of this tutorial, we’ll look into a basic implementation of the formula: making a quick Google rank checker.

Part 2: The Basic Rank Checker

Here, I’ll give you the formula for the basic rank checker and explain it in a little more detail. The exact syntax and formation of advanced formulas using this technique would take far more space than we have in this article, so we’ll focus on the rank checker tool itself.

You will need three cells to implement this formula:

This will be your final product. Cell A1 holds the search term you want to scrape in Google, B1 holds the search URL, and B2 holds the formula that we’ll discuss shortly.

The A1 cell doesn’t need any more explanation, so let’s move to B1. The formula you have in this cell should look like this:

=concatenate(“http://www.google.com/search?q=”&A1&”&pws=0&gl=US&num=50″)

The concatenate function basically sticks multiple cell’s information into one cell, so this will pull the phrase you put in the cell A1 and run a Google search based on those terms that follow it. The “&pws=0” modifier in the URL means that Google will remove personalized results so you have a raw ranking that isn’t influenced by your search history or geographic location. “&gl=US” allows you to specify a location so you don’t get foreign results (in this case, the US). And finally “&num=50” is the number of search results that it will return for the query, so 50 in this case.

The formula in B2 (where the magic happens) will look like this:

=importxml(B1,”//h3[@class='r']/a/@href”)

Let’s break this down! The =importxml will pull data from the URL specified in the cell B1 (which is the Google search URL that we just put together). The data it will pull is defined by the string “//h3[@class=’r’]/a/@href”. This gets hairy, so it’s time for a bulleted list.

• ‘//’ tells the formula to select all of the defined element that follows it

 

• h3 is what the double slash is targeting, meaning that the formula will select all of the h3 (header 3) elements on the page

 

• “[@class=’r’]” tells the formula to select all of the h3 elements that are assigned the “r” attribute. If we look at the source code of Google’s search result page, we can see that each search result is labeled <h3 class=”r”>. 

 

• The “/a/@href” portion of the formula tells the query to pull all of the anchor text and link information of every h3 element that is labeled with a class=”r” attribute.

Whew! If you’re not much of a coder, that can get confusing quickly. That should pull everything you need into your spreadsheet, now all that you have to do is add some conditional formatting to the results to make it a rank checker. Set up conditional formatting on the results column to change the background or text color when your URL appears (Format>Conditional Formatting…). Boom! You’ve got a basic rank checker! Pretty snazzy, yes? But what if you want to check the rankings of your site for multiple keyword phrases? The next part is where the magic happens.

By the time we’re through with this next portion, you will have a rank checker that will look for your site on up to five keywords, tell you where you rank in Google for them and which URL of yours ranks for that query. For a sneak peek, here’s what your finished product will look like:

It’s a beautiful thing, and it’s not as complicated as you’d think. Let’s get to building our free, fully featured rank checker!Start off by setting up your spreadsheet to look like the above image, then, copy over the formulas in cell B1 and B2 that we used to create the basic rank checker into the following column.

Cell E1 will contain the concatenated URL (same as the formula in B1 of the basic checker) and cell E2 will hold the =importxml formula. Be sure to change the cell reference in the formula to B2, where your first keyword will be located. This will do what you’d expect; exactly the same thing that our first checker did. Alright, next part!

Create two more columns to the right of the scraped results column. In the first column, you’re going to need to grab a script from Google’s script gallery called “parseDomain.” It’s free and easy to get a hold of, so I won’t go into details of how to create the script. Basically, this formula will strip each URL down to the root domain (example.com).

After you have the script downloaded and authorized, type =parseDomain(E3) into cell F3. Drag the bottom-right corner down until you hit the end of the search results. Now you’ve got a column of root domains. Onto the next step!

In the next column, enter the formula =IF(F3=$A$2,1). This formula will scan the list of root domains looking for the domain you entered in cell A2. The dollar signs are important; they indicate that A2 is the referenced cell wherever the formula is copied, whereas F3 will change with a change in position. Once the formula has done its thinking, you should have a column of FALSE and 1 values. If you rank for the term, the sheet will spit out a 1 for that line. Drag the bottom-right corner again to copy the formula down to the end of the results.

Make another column and enter the following formula: =IF(G3=1,E3). None of the cell references in this formula are absolute, so it will change when you copy it down to the remaining cells. Basically, this will look for the 1 values and put the matching URL in the cell. This is important for the ranking URL portion. Copy this down as with the others. Here’s what you should be looking at:

Back to the front of the spreadsheet! In the cell below “Google Rank,” enter the following formula:

=Arrayformula(MATCH(1, FIND($A$2,F3:F103),0))

This formula searches for your domain (defined in cell A2) in the indicated array (F3:F103) and returns the row number (out of the total number of rows in the array) that the domain is located on. There’s your Google rank!

Alright, now for the final portion. Sometimes, you’ve got pages ranking for terms that you didn’t anticipate. This formula will give you the URL that ranks for that term so you can identify where you may be optimizing for other terms (and where to retarget). This is an implementation of the VLOOKUP formula. It took me a while to wrap my brain around this, but here’s the formula you’ll be entering in the first cell under “Ranking URL.”

=VLOOKUP(1,G3:H103,2,FALSE)

This formula looks for the first indicated value (1) in the indicated array (G3:H103) and returns the first (FALSE) corresponding value in the second (2) column. The FALSE is a bit tricky; you’ll need it there in case multiple of your URLs rank for the same term, since, without FALSE in place, it will return the last URL that ranks for the term instead of the first. So this will run through your two columns and shoot the URL it found when it encounters your domain.

That’s it! To increase the number of terms you can check at the same time, just copy over the four columns (E-H), change their cell references to match the new position, and copy down the other formulas at the beginning of the spreadsheet. I haven’t gotten too deep into exactly how many keywords could be checked at the same time, but since the sheets are limited by Google to 50 importxml calls, I’d imagine it’s somewhere near there. I don’t recommend filling out many more than five, however, since Google will occasionally throttle the importxml function and that chance only increases with the number of calls you add.

So there you go: a completely free Google rank checker! If you’re not willing to go through the work to create the sheet for yourself, you can see it and grab a copy of it here: Advanced Rank Checker.

Let me know what you think in the comments! If you find any way to improve on it, let me know and I’ll add it here.