Google API Keyword Spreadsheet
<Update> Google and Yahoo Results in one spreadsheet. </Update>
I was just checking out this -new to me- excel blog ExcelGeek that has some code on parsing the HTML from a Google result page and returning the URL’s in Excel. This led me to revisit my post Google Code for Ross (page no longer exists) and create a downloadable sreadsheet for using the Google Api.
The purpose of the spreadsheet is to use the Google API for basic keyword research, or to see how competive it is to rank for a search term.
Some Screenshots: A keyword has been entered and the spreadsheet displays the total results for the keyword and the average backlinks to each of the top 10 results:
And the actual top 10 URL’s for a keyword with their backlink count is displayed:
You can also view the fullscreen shot of a different query by clicking here.
What does this mean?
1. It’s an example of using the Google API in Excel of course 🙂
2. The search for Automate Excel is not very competitive to rank for becuase there is a small number of total results for the search and some of the top ten results show 0 backlinks (or votes).
I don’t know much about Keyword ranking so if anyone has further ideas for development leave a comment or hit the contact button up top.
The spreadsheet:
I created the Google API Keyword Spreadsheet with Excel 2003, a Google Developer Key, and the Microsoft Office 2003 Web Services Toolkit 2.01. You may get by without having to download the Web Services Toolkit since the classes are included, however this scenario is untested (hopefully SOAP support will be native in Excel 12).
Google API Keyword Spreadsheet
Note 1: The Google Api is a bit slow!
Note 2: You are limited to 1000 queries/day by google. Each request with this spreadsheet is the equivalent of 11 queries (1 for the total results, 1 each for the backlinks of each result URL). So you’re limited to 90 uses of this tool with the google api/day.
Note 3: I’m determining backlinks by using the “link:domain.com”, not sure if this is the best way.
Note 4: Maybe this will help with the next version?: KEI = (P^2/C)