Using Wordle to visualise search behaviour

Ever wondered what people are searching for on your website but don't enjoy reading excel spreadsheets? No?

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.

Now, copy that data to excel. To begin, you need two columns:
'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:

Go to http://www.wordle.net/advanced

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.

How much do you trust Google?

I just discovered this cute video from Google Japan showing how Street View works. There was an interesting debate in the press earlier this year regarding Street View in respect of people's privacy. The Wikipedia entry cites details of cases around the world where Street View has been called into question over invasion of privacy.

It even prompted human rights body Privacy International to complain to the UK's Information Commission Office (ICO), who rightly concluded that the risks of invasion of privacy for the majority of people was very small and that it was not in breach of the Data Protection Act. After all, it's not illegal to take photos or film anyone in public, so long as you're not harassing them.

All that aside, as someone who frequently uses many of Google's wonderful free products, it got me thinking about how much information they hold about us, and at what cost to our privacy?

After all, there's no such thing as a free lunch. Actually there is, but moving on...

When you look at the number of products (see graphic below) that have quickly and seemlessly integrated themselves into many aspects of our lives, you realise just how powerful all that information could be if they were to start mining information across their range of products.

How many of these Google products do you use?

Here's the full list.

Clearly Google is all too aware of users' concerns in the area of data privacy, which is why you can now monitor all of the products you use with the new Google Dashboard. This allows you to tweak all of your settings and delete any data you don't want stored.

And what a lot of data they store.

Still, they wouldn't be smart enough to do anything with it, would they?

Seven easy steps to search engine success

Natural search engine optimisation (SEO) is about common-sense web production, resulting in a bunch of free marketing. Yet some people still dismiss it for a number of reasons. They're either too sceptical, too busy, don't see it as their responsibility, or feel that it interferes with their copy. The truth is SEO is everyone's responsibility, from design, code, editorial and marketing. Ultimately, what's the point in being 'too busy' producing websites if your users can't even get to them?

Before launching any web product (or even a single page) identify your keywords, define the best possible site structure and seek links from authoritative sources from around the web. There are other aspects of SEO, but if you can knock off the following basic principles you'll be in great shape for performance in search engines.

1. Research your keywords. (This is not about meta keywords. These are used only by Yahoo currently. It's the words you will use throughout the page elements in #4). Don’t just think about the brand name.
Make it your business to know what people are looking for in relation to your planned content. Exploit other popular words and phrases people are using when searching. Always start with Google Adwords Tool.

2. Create a holding page early ensuring there are links to it.
This increases chances of being indexed by search engines and allows people to link back to your page.

3. Build your proposition’s overall structure around your keywords, using those keywords in your local navigation.
This means using keywords in all the right places, making it easier for search engines to take meaning from your page. Overall structure and the contents of individual pages should be informed by things people are looking for, and the phrases/labels they actually use when they search.

4. Use keywords in all the right places.
The more of these you can hit the better (* essential)

• Title text - the most important element*
• In the H1, H2 tags etc* - also very important that it complements the title text using the same targeted words
• In the page content (first 200 words)*
• In anchor text (link text) of any internal and external links*
• Top level navigation (site structure)*
• Meta description (under 140 characters including spaces) - this won't help rankings but increases chances of being clicked on in search results pages (SERPs)
• In image filenames and alt text - (to improve chances of being indexed by Google images)
• Video titles and associated descriptions
• Meta keywords

5. Be a smart copywriter
Write copy that will be loved by both humans and search engines
Reinforce keywords in your body with synonyms and clarification words. E.g. green, eco, environment; green, vegetables, spring, recipe; green, car, fuel, efficient.

6. If you already have a site and you’re restructuring never just delete pages.
Do your research to see where inbound links are concentrated, and carefully create a 301 redirect plan.

7. Obsess about good linking behaviour.
Spend 30% of your editorial effort obsessing about the following types of linking:
i. Seek out external links from trusted sites that are most relevant to the central theme of your page
ii. Encourage related internal and external sites to use link text that matches keywords on your page
iii. Exploit every internal linking opportunity and always use matching keywords in the anchor text, never Click here or Read more.

If you're interested in learning more about SEO the following sites and blogs are leading authorities on the web:

• Matt Cutts' Blog (Google's SEO)
• SEO Book
• SEO Moz Blog
• Search Engine Land

Countryside Massive from BBC Comedy

 
More nonsense from BBC Comedy in the form of the Joes, Countryside Massive.

Jerry Jackson is RLY RLY Board!

I'm very much enjoying these little web originals by David Firth, commissioned for BBC Comedy Extra. You can watch more David Firth here.