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 del.icio.us 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, “[“, ” “),”]”, ” “).
Then remove any spaces around the keywords by entering the formula =trim(A2) [presuming that your first keword is in cell A2].
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:
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.
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.
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:
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.
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…