Google Blogoscoped

Friday, March 21, 2008

Creating a Googleshare Map With Google Spreadsheets

The new gadget feature of Google Spreadsheets makes it easy to add heat maps. This can be used to create a world map illustrating the Googleshare for a given keyword across different countries; now-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):


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


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
etc. ...etc. ...   

I've picked a couple of country names, and their accompanying two-letter country codes (you will later see that Google's heat map gadgets expects to find these country codes in your spreadsheet).

Now pick another empty cell somewhere to be your keyword placeholder. Enter a keyword, like "samba" (without the quotes), and on top of Google Spreadsheets switch to the Formulas tab. Select the keyword cell, click the "Range names" button menu, and pick "Define new". Enter "keyword" as range name and hit hit Save.

Before we add the map, let's first add the formulas that will calculate the Googleshare. In the first "PageCount for country name" cell, write the following to grab the page count – this is just like the formulas above except it's now dynamically referring to another cell for the search query:

=importXml("" &
        A2, "//td/font/b[3]")

"A2" refers to the country name cell, and the "$" will make sure the variable is changed to the other respective cells when you copy it later. The cell to the right of above formula will now hold the page count for your country name plus the keyword. You can put your "keyword" range name to good use by writing the following into that cell:

=importXml("" &
        A2 & "+" & keyword, "//td/font/b[3]")

The third formula needs to be entered in the first cell titled with "Googleshare", and combines the results of the other two calculations to a percentage value:

=E2 / D2 * 100

Afterwards, select all the three cells containing the formulas, hold down the Ctrl key, and drag & drop the bottom right corner of the cell downwards over the other cells to paste it into them.

Now you're ready to add the map gadget. Select all of your country code and Googleshare values so they become highlighted in your spreadsheet. Then choose Insert -> Gadget on top (or click the gadget menu button). In the lefthand navigation switch to the Maps section, and press the Add button for the Heatmap gadget. The gadget is now embedded in your spreadsheet, and you can hit the "Save & close" button as its default settings work fine for you.

If all went right, you will now see the Googleshare heat map. The more colorful a country, the more it's related to the term you picked. Among the limited country list, Brazil is one of the top contenders for "samba," for instance, though by far not the only one. Have a look at the resulting spreadsheet for reference; you can copy that file if you want to edit it. While the heat map results may not always be quite what you expect, you can expand this sample to make it better suit your needs** (note unfortunately you can only use the importXml in up to 50 cells per spreadsheet, though).

*Lucky for us Google ignores their own robots.txt when polling this page.

**You can also embed the chart on any webpage (in your spreadsheets editor pick Publish -> more publishing options -> HTML to embed), or embed just the image (on the gadget, pick Publish Gadget from the top right menu, and copy the snippet).


Blog  |  Forum     more >> Archive | Feed | Google's blogs | About


This site unofficially covers Google™ and more with some rights reserved. Join our forum!