How To: Make Data Presentable and Sexy using the Word Cloud tool Wordle…

PushON | August 3rd 2010

Ever had the problem of presenting data to a client and struggling to explain it’s application or meaning? A more creative approach that we have recently adopted here at PushON is using a nifty new word cloud creation tool Wordle. Wordle is a great tool that lets you create randomly generated word clouds from the most prominent words either in a data set, any given URL or a username. This has become especially useful for presenting keyword research to clients, by creating a word cloud where the most popular terms (in terms of traffic volume) are the most prominent. In order to demonstrate this process I have done a step-by-step guide below:

Let’s imagine that we’re doing some rough keyword research around beer, suppose we plug in 5 terms into the Google Keyword Tool ‘beer, ale, pint, bitter, stout’ and export this data to Excel. The only data that we will need for this analysis is the ‘keyword’ and ‘local monthly searches’ so delete the rest. Now clean up the keyword term by removing the squared brackets by entering the formula =SUBSTITUTE(SUBSTITUTE(A2, “[“, ” “),”]”, ” “).

An =Substitute formula in Excel makes cleaning up keyword results a simple job

Then remove any spaces around the keywords by entering the formula =trim(A2) [presuming that your first keword is in cell A2].

An =Trim formula in Excel makes cleaning up keyword results a simple job

After this we need to duplicate the keyword based upon the number of local monthly searches. To do this, number the coloumns above your keywords from 1-1000, as shown below:

By creating coloumns of 1-1000 we can limit the size of the very large search volume terms...

Because some keywords are going to produce a large amount of terms (upwards of 10,000) lets reduce the results by entering a colomn ‘Volume in 100s’ with the formula =B2/100.

By dividing the Local Mongthly Searches by 100, the keyword terms will be smaller but still in proportion

Then to populate the remaining cells insert this formula =IF(D$1<=$C2, $A2,””), this formula will then paste the ‘keyword’ value into all cells relevant until the number from 1-1000 (running along the columns at the top) is less than or equal to the number in the ‘volume in 100s’ colomn. Essentially this will populate a number of cells relevant the the amount of local monthly searches.

The =IF formula checks the Local Monthly Searches/100 against the columns 1-1000 filling in as many that are relevant

Now we’re in a position to make the word clouds, we’ll do this using Wordle. Select all of the keywords (and the repeated versions) from your Excel document and past these into the ‘paste a bunch of text’ section on Wordle:

Copy all of the terms that you have repeated based on their volume, then paste them into Wordle...

Now once you’ve clicked ‘go’, the tool will automatically generate a word cloud based on the number of repetitions of each keyword. From here you can make changes to the font, layout, or colours using the dropdown menus from the top.

Wordle will then generate your word cloud, which you can modify using the tools to change font, layout and colour

Then once you are ready to use this image click ‘open in window, make it fullscreen and then printscreen and save as the desired picture format (.jpg, .gif etc), so you can import into any file you want…