Wordle, Google Analytics, Keywords and Pretty Tag Clouds

The little sidebar widget on my home page that shows posts written “on this day” in previous years is a source of amusement for me. For example, I recently discovered this old post from 2006, showing a tag cloud of terms which people used to find this site.

The tool I used to generated that cloud is defunct, but I figured I could create a much prettier tag cloud using Google Analytics data and Wordle.net.

Here’s what I ended up with. Click to make it really, really big. You may prefer this medium-sized version here, and a non-lightboxed full size version.

Truly random, eh? It’s not reflective of what I write about, but rather what I write about that accidentally wins the search engine battle so that people find pages on this site.

As I note below, this is only the first 100 most popular keywords or phrases for 2010. Those 100 phrases represent about 20% of the volume of searchers coming to the site. The other 80% is comprised of 171,218 different keywords and phrases (the last term in Latin characters with a single visitor is “zyloken”, which doesn’t seem to be a word at all).

I thought I’d generate an analogous cloud for the same period in 2006, just to see how much the terms had changed. Same deal here, click to embiggen or look at alternate sizes.

Wow, things look a lot more shallow and prurient in 2006, don’t they? Remember that these data sets are just the arcane convergence of what one writes about, which blog posts out-compete other search results and the dark art that is Google’s search algorithm. I never really wrote about sex very much. I think this is the banal little post that attracted all that “online sex game” traffic back in 2006.

Generating Wordle Tag Clouds From Your Site’s Keywords

The process was pretty straightforward:

1. In Google Analytics, under Traffic Sources, navigate to the Keywords section. Be sure to set the data range to what you want (I just choose January 1 through July 24, 2010).
2. In the footer, next to Show Rows, choose the number of keywords or phrases you want to display. I went with 100, which seemed like the maximum you might want in a Wordle tag cloud without it becoming unreadable.
3. Click the Export link near the top of the page, and choose CSV.
4. Open Microsoft Excel (or Numbers or Google Docs or whatever) and delete the top section of data that just shows visitors per day.
5. Delete all of the columns after the first two. They’re labeled Keyword and Visits.
6. Delete all of the other random rows that don’t contain keyword phrases and numbers, including the header row.
7. Export the spreadsheet to a CSV file.
8. Open the exported CSV file in a plain-text editor like NotePad or TextEdit.
9. Using Find and Replace, replace all the instances of commas with colons. Your list should look something like this (only longer):
   fruitbats:133
   llamas on parade:85
   zombies:42
   donuts:5
10. Copy that data into the first field on Wordle’s Advanced page and click Go. Voila, instant tag cloud.

If you want to output a high-resolution version of your Wordle, use your browser’s print functionality to print to a PDF file (Mac users, note this workaround).