Bubble, bubble toil and trouble
By Chris Gemignani
January 15, 2009
Find more about:
visualization
nodebox
bubblechart
concentrate
excel
Recently we wanted to show how Concentrate, our new long-tail search analytics tool, could give you a view of search patterns across travel websites. As political junkies, we were inspired by this chart from our friends at the NY Times.
The first tool we tried, simply on principle, was Excel 2003. As expected, making a NY Times quality bubble chart in Excel 2003 is a hard problem. Here's a draft of how far I got before giving in to label fatigue.

The bubbles themselves aren't tough, but getting the labels right is hard. I'd love to see a solution, so if any reader wants to tackle it eternal fame can be yours. Here is a CSV if you want to try.
Another of the tools we use at Juice is NodeBox, which we used to make this:

Here's the code that made the graph.
The power of a programmatic approach like this is that by changing a line or two, you can get the following. Click for a larger version. Click the text for the code..
With great power comes a great need to exercise restraint. Otherwise you end up like these poor chaps. Must... flex... restraint... muscles...
Introducing Concentrate for Long Tail Search Analytics
By Zach Gemignani
January 9, 2009
Find more about:
concentrate
seo
search
analytics
We are pleased thrilled to introduce Concentrate™, an innovative long-tail search analytics tool. Concentrate is for SEO and paid search professionals who want to make sense of search keyword data and make the most of search investments.
Check out the demo here. Or try out the free version here (you’ll need admin access to a Google Analytics account).
We built Concentrate because we saw a fundamental conflict in the world of search analysis: On the one hand, search keyword data is terrifically interesting and valuable. It can tell you what your visitors and customers want and how they think about you and your products.

Unfortunately, search query data is also big, messy, and hard to get your hands around. In a typical month, the Juice site gets over 10,000 visits from over 7,000 unique keywords.
Even if I could somehow wrap my head around our top 100 keywords, I’d only understand 25% of the visits. For people spending money on search engine optimization or paid search campaigns, that’s a big blind-spot to accept.
We want you to understand and act on all your search data. Concentrate ingests data from sources that most sites already have available (e.g Google Analytics, Omniture, Coremetrics, Hitwise, Compete, etc.), enhances this data by finding common patterns and query types, and visualizes search phrases for exploration and analysis.
Over the next couple of weeks, we will share examples of some of the interesting things you can do with Concentrate, including:
Pattern identification to condense the long tail into keyword phrases with similar structures. For example, here are some common search patterns from a cooking web site (the “[x]” represents a wildcard).

Keyword visualization to show the connections between keywords and the relative performance of phrases. This wordtree shows the frequency of words within phrases (size) and average time spent on site (color).

Congratulations to Chris, Pete, and Sal for all their hard work, diligence, and creative problem solving to launch this solution.
8 comments | Show all comments only the last 5 are shown
Jim Novo said:
That wordtree report visualization is absolutely brilliant, you guys rock!
Chris Gemignani said:
Much appreciated, Jim!
Jared Huber said:
This is very exciting! I agree that keyword categorization is definitely the right way to think. I'm testing out the demo version as we speak. Keep up the great work. -Jared
Daniel Waisberg said:
Looks amazing, I will implement it and start working for my own website. I think that for search marketing / SEO companies this will be a killer tool. It can add a huge value!
Bjoern Sjut said:
Hi,
has there already been testing with foreign languages? I could volunteer to integrate it with a German content heavy site to test the behaviour on umlauts, etc.
Bjoern Sjut said:
Oh, I can shed a light on this already: My most important keywords for our German sites are "error#" and "unicode error#" :-(
Pete Skomoroch said:
Bjoern,
Thanks for the feedback. I just fixed that unicode error for you and reloaded your list. Concentrate should run without errors on foreign languages, but some of the text processing components (stopwords, stemming, etc) are only fully supported in English at the moment. Let me know how the new results look and we will work on incorporating more international features.
Pauli Price said:
On the final validation stage, where I entered the bounce rate for my first keyword, the application met with an un handled exception because it couldn't find the google analytics keyword file. Perhaps because there were spaces in my site name? Unfortunately it also spit out all kinds of diagnostic information you probably don't want the casual observer to see. You really want to trap that unless the login is a privileged account.
Anyway, help doesn't go to a help screen or anything - it appears that clicking on 'help' brings one to the account page, so I figured I'd post my tale of woe here.







17 comments | Show all comments only the last 5 are shown
Scott Sanders said:
It's far from a full solution, but the XY Chart Labeler might help you get part of the way there: http://www.appspro.com/Utilities/ChartLabeler.htm - I find it indispensable for my bubble and scatter charts.
Clint said:
Chris, it's easy enough to mimic the NYT chart with your data in Excel. A couple of custom series to represent labels (e.g. site name and terms), some custom formatting and you've got a decent representation. The problem of labels in this case I think is the relative smoothness of the data. the NYT data ranges from 1 to 21 which compared to your sample data which runs 0-100% is a relatively tight grouping so the bubble scales are much more similar. In other words, it was a lot easier for the NYT team to fit labels on their bubbles than it is to do on your data. I'll post a graphic later on my blog but, I think the acceptable solution is to create a size legend series...
Clint said:
Scott, in my attempt, I did use x/y chart labeler (a must-have for excel) but that was for custom row and column labels, I don't think it will really solve for getting the labels onto the buttons.
Chris Gemignani said:
I'm looking forward to seeing your solution, Clint at blog.instantcognition.com. The challenge I found, was labeling the X and Y axes at all, not to mention tastefully. I'll be curious to see what you come up with.
Clint said:
Hey Chris,
I was about half way through writing the blog post and encountered a lovely BSOD. Too tired to start the post over tonight so here is the URL to the screenshot of my solution:
<img src="http://blog.instantcognition.com/wp-content/uploads/2009/01/juice_concentrate_bubble.png" width="350px" height="287px" border="0" alt="Clint's Excel Bubble Chart Solution" title="Clint's Excel Bubble Chart Solution"/>
Clint said:
Wow, didn't think that would actually work.
In any case, to follow up on my original comment about the smoothness of the data...
The area of the bubbles in the NYT graphic scale from 1 to 21 which means the diameter has to scale 21x from smallest to largest. For the concentrate data the diameter scales from .5% to 100% - a 200x increase so it's much harder to make data labels fit into/onto the bubbles and be readable and preserve the integrity of the graphic itself
Clint said:
one last note ... don't do math when you're tired. As the above shows (still true, math is just wrong)
Robert said:
Just wanting to throw SAS' name in the hat, as another software that can do this kind of "bubble table" graph (with a bit of programming).
Here's an example of the output. I'm using gif here, which everyone should be able to easily view. SAS can also output other formats such as svg and 1200-dpi tiff, which make the bubbles look smoother (more "publication quality").
http://robslink.com/SAS/democd33/death_odds.gif
Chris Gemignani said:
Very nice, Robert: It's a good tip in general about exporting. If your graphing solution doesn't output antialiased (i.e. lines look smooth rather than chunky) charts, create a higher resolution version then scale it down. A good, free tool on the Windows side for image manipulation is: http://www.irfanview.com/
Fabrice said:
I just posted an Excel version of the chart based on the UDF I created.
Here : http://sparklines-excel.blogspot.com/
Andy Cotgreave said:
I put this together in Tableau:
http://tinyurl.com/9skr3h
Wasn't too sure what your colour was measuring, so I based it on size.
I tried it with text labels, but the text was too big relative to the bubbles. And I couldn't force the size variance to be any bigger. I would have liked to be able to make the biggest bubble bigger.
However, I think it's okay for 5 minute's work!
Clint said:
Andy,
yes, it's much simpler in Tableau and while I like your color as value indicator, you could just as easily have the values displayed next to the bubbles instead of 'on' them and Tableau keeps it clean.
chip said:
Rob Bovey has an xy chart labeler that may have helped on the original Excel version. I use it a lot and it provides a good degree of flexibility on placement.
http://www.appspro.com/Utilities/Utilities.htm
The labels are not dynamic which is a drawback. It works on other types of charts too.
Andy Cotgreave said:
Hi Clint,
Yes, I did initially add the text. However, in Tableau it somewhat overwhelmend the circles. I did try to format the text to grey and shrink it, but the text only served to confuse things.
Chandoo said:
Hi Chris,
Good stuff...
I have tried the same in excel while keeping the labels right (I guess so). You can take a look at the chart and downloadable excel here: http://chandoo.org/wp/2009/01/19/excel-bubble-chart/
Let me know your comments
David Franta said:
Didn't really find another place to post this, but interesting article posted by Cringely (ZDnet fame) about how JP Morgan mangled a bubble chart recently -
http://blog.cringelysmortgage.com/2009/01/29/whats-wrong-with-wall-street/
Mike Chelen said:
How about using the Google Charts API scatter plot? http://code.google.com/apis/chart/types.html#scatter_plot
It allows variable bubble sizes, and has been used in some similar charts such as http://www.xefer.com/twitter
said:
Add a comment