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.

If your struggling to find good ideas for articles and blogs, or want to attract some more traffic then the keyword wuestions from Wordtracker Labs looks like a cracking tool.

Searching with keywords the tool returns that keywords in popular questions. For example SEO returns.

Question – Times asked
what does seo stand for – 15
what is seo – 10
how do i become a certified seo – 6
how to set up seo – 4
how important is domain to seo – 4
how does links to bookmark pages affect seo – 3
how to do seo myself – 2
how to write seo articles – 2
how to remove seo for firefox – 2
how to seo – 2
external seo work what is – 2
seo how – 2

No doubt that several SEO’s will be writing blogs based around these questions – e.g. Dave N. The data seems to all come from America so there’s nothing to do with football clubs, towns and city’s in the UK. The time asked number comes from the last 140 days from partner search engines based in the US.

So if you need to write some good content for clients have a search and I’m sure you’ll come up with some great articles.

There was a lot of press recently about the release of SEOmoz’s Linkscape tool in the last few months. That’s seem to have died down now an I’ve had the chance to use the tool at work and found it very useful.

It’s great for finding links that your competitor have that you should have as well. Of course you can use something like Yahoo Site Explorer but Linkscape enables you to grab the cream of the links quickly. The ones that are do follow and maybe based in the UK.

It’s help me to find a reason why a site has been penalised. It identified a site that had 10,000 links from only 52 domains, with around 8,000 using the same anchor text. It stood out as unusual and proved the theory about how the site had been penalised.

I’ve also used it to find duplicate sites from clients as all the site were from the same IP.

I do think that it will become essential piece for my day to day work. It is a tad expensive to sign up for if you are an individual so if you work for a company try to get your company to sign up for pro membership!