Since I’ve been away there’s been a few tools built using Google Docs using the importXML function, I’ve linked to some useful blog posts at the bottom of this post if your interested.

Anyway here’s a tool that I put together quickly that takes 25 key phrases, checks for serps that include review / ratings displayed. It’s useful if you needed to decide if implemented rating and review rich snippets would be a useful exercise, e.g. none of the serps have them = a good opportunity to be the only one.   Or if certain key phrases had a low CTR you could see if the serps are saturated by rich snippets.

The rich snippets it looks for are the ones with star ratings such as the ones below.

It’s pretty simple to use.

Add up to 25 key phrases on the first sheet, rows B2 to B26. 

That’s all you need to do, it grabs the top 10 results for each serp, and then looks for the div in the search results that contains the star ratings. The function in Google spreadsheet is,

=importxml(C2,”//div[@class=’f’]”)

You can change the search engine version and  language etc by changing the query URL’s in column C in the first sheet.

Another sheet tots up the numbers with percentages. It looks for all reviews, reviews that contain ratings and reviews that contain price information (used for restaurants). I’ve stuck a couple of graphs in the last sheet to show totals.

 

Something I learnt when using importxml –  when importxml runs it pulls back the code from the URL you supplied, in this case it’s a Google search result. When it pulls back this page it does so without running JavaScript. For example, author pictures in search results are added by JavaScript as the page renders, so you can’t see them when using importxml, also some names of divs are changed by JavaScript. When using importxml you need to look at the source code of the page you are importing to decide what element you need to grab, be sure to open that page in your browser with JavaScript turned off thus to simulate the way importxml works.

This is in no way a completed tool, it most likely will break. Please feel free to use, copy, and improve. If you make any improvements leave a comment with a link to the file URL and I’ll repost with updated versions.

Just open the link below and make a copy to use.

–> Bulk Review / Rating SERP Checker Tool in Google Docs

 

Note : It’s limited to 25 key phrases as you have a maximum of 50 importxml calls, I used two for each key phrase.

 

Have a read of the posts below to find out more information about importxml, the links open in new tabs.

http://www.distilled.net/blog/distilled/guide-to-google-docs-importxml

https://seogadget.co.uk/playing-around-with-importxml-in-google-spreadsheets/

http://www.seerinteractive.com/blog/importxml-cookbook

http://zoomspring.com/learn-importxml-tutorial/

When conducting key phrase research little tips, tricks and tools can save time. Being a wizz at Excel helps, being able to create scraping tools is also great but sometimes you just need to copy and paste from sites to generate key phrase lists.

Google docs

I’ve come across a neat method of using the ImportXML function on Google Docs to extract lists from a web page into spreadsheet (from there you can paste into the Google Keyword Tool to get volumes). Credit goes to this post for the how to us http://ouseful.wordpress.com/2008/10/14/data-scraping-wikipedia-with-google-spreadsheets/.

First you need a page with data on, could be a list of products, table of locations, for example list of dog breads, list of football teams, or a list of settlements in the uk ordered by population.

In Google docs you then use the ImportHtml function specifying the URL, that you want to grab a table and which table e.g. the 1st, 2nd etc.

=ImportHtml(URL, “list” | “table”, index).

For example

=ImportHtml(“http://en.wikipedia.org/wik…..m_settlements_by_population”,”table”,1)

A full explanation can be found on the help files http://docs.google.com/support/bin/answer.py?hl=en&answer=75507 which also show other uses and variations.

You hit enter and hey presto you have a nice table with all the figures separated out.

google docs import html

If you look into the “ouseful” blog, linked above, you can then publish from google docs into RSS, then to Yahoo pipes into RSS then use the RSS on your site as content.

You can also import from RSS and product feeds.