Google Spreadsheets: Creating a Googleshare Map

Google Spreadsheets new gadgets feature makes it easy to add a heat map, which can be used to create a world map illustrating the Googleshare for a given keyword across different countries.  Google employee Douwe Osinga called this "Land Geist" a couple of years ago. What's "googleshare"? Also called mindshare, the Googleshare expresses how much any two given […]

Google Spreadsheets new gadgets feature makes it easy to add a heat map, which can be used to create a world map illustrating the Googleshare for a given keyword across different countries. 

Google employee Douwe Osinga called this "Land Geist" a couple of years ago.

What's "googleshare"? Also called mindshare, the Googleshare expresses how much any two given concepts are related to each other. The calculation first grabs the result page count for a search for a given term, say "Beatles". It then grabs the search page count for that word plus another word, like "Beatles" + "John Lennon". Now you take the percentage of the second value in relation to the first and you got the "Googleshare." As a high amount of pages mentioning the Beatles also mention John Lennon, John Lennon has a large "mindshare" with the Beatles (higher than, say, "Mick Jagger").

To do this in Google Spreadsheets, you first need to find out how to grab the page count for something. The importXml function, which accepts the parameters URL and XPath (an XPath is a query language for XML documents), comes in handy. Because Google outputs the page count on top of results within <b> tags within a <font> tag, we can grab it like this* (where "sushi" is our keyword):

=importXml("http://www.google.com/search?hl=en&q=sushi", 
        "//td/font/b[3]")

The second page count we need for the calculation adds the country name as well, like this:

=importXml("http://www.google.com/search?hl=en&q=sushi+brazil",
        "//td/font/b[3]")

Now that is set up, let's add real values for the countries and for the keyword. In your spreadsheet include the following structure:

Country nameCountry codeGooglesharePageCount for country namePageCount for country name + keyword
ChinaCN   
IndiaIN   
USAUS   
etc. ...etc. ...   

Full Article

Google, Google Docs, Google Spreadsheets, Googleshare Map, Maps, Heat Map