Okay, so this post won't set your pants on fire, but if you're interested in visualising search data to gain a little audience insight, read on.
This activity can be performed in under an hour and the end result is a neat little word cloud from Wordle. It's a useful way of communicating very boring data in a user-friendly and accessible way. If you're not familiar with Wordle, it's a toy for generating “word clouds” from any text you provide. The more times a given word appears in the text the greater prominence it is given.
Step 1) Get hold of some keyword search data
This could be a monthly search log from whatever source you can get your hands on. (e.g. your analytics system, Google Webmaster Tools report etc). Depending on what you're tying to achieve, I'd say a month's worth of data is sufficient. If you find the number of searches tails off rapidly then you could just pick, say, the 200 most popular keywords in the list.
'Keyword' and 'Number of searches'.
Step 2) - Identify themes
This step is optional but makes for a more interesting result. Whatever your website is about, it's very likely you'll want to bucket the keywords into categories. (ie Red widgets and Blue widgets). In this example I was interested in TV programmes vs. Web originals.
Assign a value to each of the categories in the third column of your spreadsheet. See screenshot below. This could be the category name or just a number. I added a key for my own reference but you don't have to.
Step 3) - Insert a new column for adding hex values
Hex values are six digit representations of an RGB color. Pick whatever colour you want to represent category in your eventual word cloud.
http://www.pagetutor.com/common/bgcolors1536.png
Into that column you'll need to add a formula that looks as your category column and assigns it your chosen hex value for each category. So, if you wanted one of your categories to be red your formula would be: =IF(C2=1,"FF0000")
Here's what my formula field looks like using 5 categories:
=IF(C2=1,"66BB44",IF(C2=2,"ff0044",IF(C2=3,"000000",IF(C2=4,"3344BB",IF(C2=5,"66335")))))
The formula above says if the previous cell (C2) is equal to "1" assign it the hex value of "66BB44", and so on. The more categories you have, the longer your formula gets. If you need to add more just make sure the number of closing brackets on the end of the formula matches the number of opening brackets for each category, or it will break the formula.
Your spreadsheet should now look something like this:
Step 4 - Add new columns to make it wordle friendly
Now insert two new columns after the 'keyword' and 'number of searches' columns so that Wordle can make sense of your data structure. (You no longer need the category ID column as the formula column has done all the work Wordle needs. You might want to move your category ID columns to the right of the hex column)
Step 5 - Make your wordle
You're almost done. Your spreadsheet should now look like this:
Paste in your content into the second text field on the 'Advanced' page. Make sure you don't past in the header row as we don't need that.
Step 6 - Experiment with different types of Wordle
Use the menu option on the top of the Wordle to change case, colour and horizontal/vertical display features.
Step 7 - Save as pdf or Print Screen
Now if you have a PDF writer installed you can save your Worlde as a high-res pdf and play about with it In Illustrator. This is neat as you can zoom right in on your terms and see those really small ones.
On a practical level I'll be using this as a way of finding out how well our attempts to raise awareness of our web exclusive videos is working over time. So by the end of 2010, we'd hope to see more yellow searches (yellow being web exclusive comedy videos).
Credit for this trick goes to my colleague Duncan Bloor, our resident SEO specialist at BBC Vision Multiplatform.
