Ok, so about ten days ago I challenged my readers to solve the following charting problems with the Google API - Bullet Chart, Sparkline and Square Pie. I provided a sample bullet chart but asked if anyone could improve upon it. Unfortunately, only one brave soul, Jeff Donnici, took up the challenge and provided two solutions for Sparklines.

I am inviting Jeff to write his own discourse on how to create sparklines with the Google API, so Jeff, please email me (my email is at the bottom of the right-hand nav) with your description of how to create the sparklines.

First, let’s cover some basics of the API.

The API is pretty simple, it’s just a URL with a bunch of query parameters attached to define the chart and its elements. It can also be a bit annoying when you’re encoding the data sets and their associated information. My guess would be that to really get the power out of the API you would want some sort of programmatic interface to it as building the chart request ‘by hand’ can be laborious.

So, components that are used in these examples:

• Base API URL: http://chart.apis.google.com/chart?
• Required Elements
  • Chart Type Parameter: cht - Allowable Chart Types
  • Chart Size: chs - Chart Size in pixels [w]x[h] (chs=250×250)
  • Chart Data: chd - Chart data encoding methods (simple, text, extended)
• Optional Elements Used
• Chart Title: chtt - Text Title for the Chart
  • Chart Title Text Size: chts - Title font size and color
  • Bar Chart Height: chbh - Bar width
  • Chart Colors: chco - data series colors
  • Chart Background Fill: chf - Chart fill options

For me, let’s start with the Bullet Chart. The stumbling blocks here are that

1. The Google Chart API does not support combination charts, or at least not a bar chart / scatter plot combo, which is how I create Bullet Charts in Excel.
2. The API does not seem to support single data point properties. In other words, I can set the color and width properties for the stack (e.g. series) but not for data points in the stack.

If you remember the Excel example of the Bullet chart, you will recall that we have 3 data series (top, middle, bottom) each with 4 data points to create the basic visualization of the Bullet chart. We then combine into that chart a scatter plot to create the target line. Well this API doesn’t support coloring for a single data point (which is required in the Excel solution) so what does Google offer as a replacement? Well, with the background fill, we can go a linear stripe that will take up some percentage of the chart space - ok, I think I can work with that:

But first things first. Let’s define the chart type:

http://chart.apis.google.com/chart?cht=bhs

• cht=bhs tells Google that we are working with a horizontal bar chart

Next, let’s set the chart size

http://chart.apis.google.com/chart?cht=bhs&chs=250×40

• Remember, chs wants [width]x[height] in pixels and it’s required

While we’re at it, lets go ahead and set the bar width (or height if you prefer)

http://chart.apis.google.com/chart?cht=bhs&chs=250×40&chbh=10,25

• chbh lets us define that bar width and optionally the spacing between bars. In this example the gap is set to 25 to make sure that the chart fill area shows up around the bar itself.

Ok, for the last of the easy bits, let’s go ahead and set the Chart title

http://chart.apis.google.com/chart?cht=bhs&chs=250×40&chbh=10,25&chtt=Homepage+Retention+Rate:+90%

• Note the plus (+) symbols in the Chart Title text, this tells the API to use a blank space when creating the title

On to the data. As noted above, there are 3 options for encoding data - simple, text, and extended. I haven’t really figured out why you would use simple because it’s basically a substitution scheme that feels more annoying than anything. However, in this example we are using text encoding because it allows us to use regular numbers with up to 1/10th precision.

http://chart.apis.google.com/chart?cht=bhs&chs=250×40&chbh=10,25&chtt=Homepage+Retention+Rate:+90%&chd=t:90.0

• when adding the chart data, you must first tell chd how you are encoding the data (chd=t) and then supply the data itself (:90.0)

The above gives us a basic bar chart:

Now we need to add the elements that will make this look like a Bullet chart and we’re going to get it all done with chart fill!

http://chart.apis.google.com/chart?cht=bhs&chs=250×40&chbh=10,25&chtt=Homepage+Retention+Rate:+90%&chd=t:90.0&chf=c,ls,0,000000,0.50,cccccc,0.25,000000,0.005,efefef,0.245

chf (chart fill) takes the following arguments:

• fill area: background (bg) or chart area (c), in the Bullet chart, we use the chart fill method
• fill type: solid (s), linear gradient (lg), and linear stripes (ls)
• angle: angle of the stripe between 0 (horizontal) and 90 (vertical)
• stripe color and width pairs: color hex value and length expressed as a percentage

If you look at the URL snippet above in bold, you will see that we have defined

• the background element as "chart fill" (c)
• the fill type as "linear stripe" (ls)
• the "angle" as zero
• The first section is black and extends to 25% of the length
• The second fill section is the same as the first
• the third section is also 25% but is a dark gray
• the fourth section, only 1/2 of 1 percent in width is black - It’s a trick, this is the target line!
• The last section is 24 & 1/2 percent of the length and is a very light gray

That’s it, now we have a functioning bullet chart

I sure wish that I could remove the axes lines, and add data labels to the bar and target line, but oh well.

Ok, I need to take a break as this is quite a long post. In part 2, we will walk through the pie chart solution (which is not that great),

I’m not quite sure how long I’ve been ignoring the Google Chart API but I saw it in a post the other day which reminded me, or at least gave me the inkling, that I had seen some discussion of it before.

Well, that was several days ago and I finally had some time to play with it and in a couple of hours created the following bullet chart:

So here’s the challange

1. Create a Better Bullet Chart than the Above
2. Create a Sparkline
3. Create a Square Pie Chart

Using the Google Chart API of course.

Once you’ve created your solution, post a link to it here in the comments. For the solutions that we like best, I’ll ask the author to provide a description and instructions to be published here in a follow up post.

Update: 12/15/2007

Jeff Donnici sends us two sparkline examples.

Simple:

With Banded Background:

Update 12/19/2007

Well with only Jeff taking up the challenge after 10 days, here’s the final solution: a so-called square-pie chart. Of the 3 types this is the most poorly supported by the Google API mostly because on bar charts you cannot control the scale of the axes.

Update Feb 29, 2008

Geoff offers us this austere Bullet Chart solution:

And this slightly "dressed up" version:

Stephen Few has long proposed his bullet chart as a replacement for gauge-type reports. Steve argues that bullet charts are more efficient (take up less space) and encode more and more accurate information than a gauge or speedometer type chart. I’m still unconvinced, especially since I’m not a fan of a visualization that requires retraining of the end consumer. I’d much rather use something that the end consumer ‘gets’ right away.

That being said, I haven’t written a visualization post in a while and just because I’m not convinced on bullet charts doesn’t mean that you aren’t and that you might not be looking for a way to create them in Excel. My version is a combo-chart using both a scatter plot and stacked horizontal bars so the order in which we add data and chart elements is important.

BTW, I am going to assume that you have a passing familiarity with Excel and its charting utilities so don’t be surprised when I outline the steps to take (insert a scatter plot) rather than give detailed instructions (select data in cells A1:B2, go to ‘Insert’, click on the Scatter Plot drop down and select the scatter line with no point markers).

Step 1: In this first step, we will add the data for the scatter plot and create the default scatter plot graph. In this example, we are using the scatter plot to set a goal line for the overall bullet chart.

So, when we create the initial scatter plot, you’ll see that the x-coordinates are ‘80′ - this is my target. In this case, I happen to be charting the performance of the Homepage Retention Rate (1-Abandonment Rate) and I am shooting for 80%. I happen to be using whole numbers for simplicity’s sake but it could just as easily be percentages. Don’t worry about the vertical alignment of the line just yet, we can adjust it - if needed - later when we are closer to the completed graph. Notice that I haven’t removed any of the chart junk yet either, that will happen in a later step.

Step 2: Now we need to add the data for the horizontal stacks and the stacks themselves. We will have 3 bars (Top, Middle, Bottom) with 4 sections each.

When working with combination charts, adding the new data points and chart types can be tricky. In the screen shot to the left, we’re looking at the completed process, I’ve excluded the intermediary step of what the data looks like when we first add it.

In order to add the stack data to chart we first select the data from all four of the stack columns and copy it. Then we select the chart and use ‘Paste Special’ to paste the data as ‘New Series’ with ‘Values ‘Y’ in columns’. This will paste our stack data in as new scatters. We then select each of the stack series and change it’s chart type to ’stacked bar’.

Ok, now that we’ve talked about how to add the stacked data to the chart, let’s talk about the data itself. In the top row, the first data point is ‘50′ - this is our floor (minimum acceptable value). The second data point for the top row is always zero. We need this second stack element for visual handling on the center stack (see the little green block in the center bar) when it is less than our minimum. Oh, I’m also not going into excruciating detail on the formulae used in the data set, they are fairly straight forward and you can take a look at them when you download the spreadsheet at the end of this post.

Step 3: This is easy, set the gap width on the bars to ‘0′ so that they are slammed up against each other.

Step 4: Time to take out the chart junk.

Remove the vertical axes, and the secondary horizontal axis.

Remove the horizontal lines.

Remove the legend.

Remove the plot edge lines

Set the scaling on the primary horizontal axis, set the axis-line to none as well as the major tick marks to none.

Step 5: Apply consistent and valuable color-coding to chart elements.

The blue stack becomes our representation of ‘Actual’ (e.g. the bullet). The background elements are encoded in shades of gray.

Up to 50% is nearly black - remember, this is our floor

From 50% to 75% is about 50% gray - this is our marginal performance section

And finally, from 75% to 100% is light gray (20%) for acceptable performance. I’ve also changed the target line to full black, it stands out nicely against both the light gray and the blue.

Step 6: Resize the chart.

So far, our chart hasn’t look much like a bullet chart has it? Well, that’s pretty simple to take care of. In this example, I’ve held the horizontal length the same but compressed the vertical height. Now we have something that very definitely is looking like a bullet chart.

Step 7: Add a text label and center align the chart to the text label.

Ok, so what I’m really doing is adding a text description, and the actual value of my metric in the two cells to the left of my bullet chart. Then, I’m massaging the bullet chart into alignment (hopefully) with the text. Notice that the value I’ve put in the label (86) doesn’t match the value in the chart, we’ll fix that in the next step. You might have also noticed that in this step, I removed the final piece of chart junk from my chart - the chart area border. Up until now, it was helpful to have the border for moving the chart around, but now that I have it correctly situated, I no longer need it.

Step 8: Synchronize label value to chart value.

Yep, you guessed, that’s a fancy way of saying set the first value of the center stack equal to the text label value (=D89). Now, I can enter a new value on the label, and the chart will update automatically.

Step 9: Obscure the chart data.

We had to jump through a lot of hoops to get to this graphic, but we probably don’t want the report consumer to be thinking about those hoops, so set the text color of your chart configuration data to ‘white’, effectively hiding it from the consumer.

Step 10: Rinse & Repeat

 Ok, so there’s my step-by-step on how to create Bullet charts.

What? You want the spreadsheet? Well, since you asked so nicely…

Download the Excel 2007 version or Download the Excel 2003 version

That’s Barney’s (Neil Patrick Harris) infamous one-liner from CBS’s "How I Met Your Mother". It’s a favorite show of ours (the wife and I).

Now, Neil was recently nominated for an Emmy for his work as Barney on the show - he is the gooey, sticky center that holds it all together. So, after seeing the nom nod, my wife points me to "Barney’s Blog" thinking that I should have some fun with Barney’s statisticovisual (I love making up words) approach to dating.

WELL…

Not to be a downer or anything but my amusement was ruined by several factors.

Take for instance, the first bar chart which is labeled "BARNEY STINSON’S HOOK UP PERCENTAGES"

So, the chart purports that Barney’s hookup percentage ranges from 200 to 500 percent, depending on the condition of not having or having Ted with him. Hmm, so when the big B is flying solo he hooks up 200% of the time? And when he has Ted with him the percentage goes up to 500%? Um, part of the Barney character is a near-pathological need to be right and this ain’t right. Also on this chart are Ted’s hook up percentages which range from 0% (without Barney) to what looks to be somewhere between 10 and 20 percent (with Barney). So, half of the chart is wrong and half is correct?

This chart is SO not awesome (as Barney might say).

Also, what’s with the lazy reliance on Excel 2003’s default chart settings? Barney is an over-the-top kind of guy and if he’s going to go the trouble of creating these charts and putting them up on foam cores then he is certainly going to send them to a graphic designer (at the least, hopefully he’d send them to a data visualist to really make them sing) to dress them up - I imagine Barney sitting there, screaming "Suit UP!" at his laptop.

Finally, in the last line chart, the y-axis is labeled "Level of Hotness", I’m pretty sure that Barney, if he existed, would have labeled that axis "Level of Awesomeness"

Sheesh, CBS marketers, make sure to get into character before writing these posts.

Update: Google Analytics Team, please don’t do this anymore. I just turned on the ‘compare to past’ feature and noticed that you are doubling up series on the sparklines. This is a big no-no as far as I am concerned because the sparkline with mutliple series on it just ends up looking like a squiggle. The data from the second series actually becomes chart junk!

If your a visual analysis wonk like me you probably couldn’t help but be excited with the new version of Google Analytics and the introduction of Sparklines. To me, it comes across as a major endorsement of sparklines as a business tool.

But, for this implementation to be a useful endorsement of sparklines, they have to show value as a tool and I don’t think the GA implementation is quite there - yet. Remember the new version of GA is in Beta so the team has lots of opportunity to improve.

From the Visitor Summary Report…

In this case, the shaded area is not useful, it’s distracting. The reason for this is relatively simple - it’s shading the area under the curve (plotted line) in the case of a sparkline, the shaded area should code more or different information. I’d really like so see this shaded area represent some UCL and LCL data, for instance the standard deviation (plus or minus) from the mean of all values.

The value currently associated with the sparklines are the total or total average for the period represented when they should be the current (or most recent) value.

I’d like to see some data markers on the chart - current value, high water mark and low water mark. Of course, the markers should be color coded to their value on the right. So, for instance the most current data point marker could be red and the current value would also be in red. The high and low water marks would be different colors and their associated text would be those colors to tie them together.

If that’s as clear as mud, here’s a quick example:

Notice that my example is a bit longer, this is because Tufte recommends shooting for a 45 degree angle on the line slopes (it eases understanding)

1. The gray area represents the ‘normal’ distribution of visitors - the "biorhythm" as Avinash would say and it is the mean +/- 1 Standard Deviation
2. Both the oldest point (starting point) and newest point (end point) are marked in red
3. The watermarks (high and low) are marked in green
4. For each of the data points that are marked (except for the starting point) an associated value is presented to the right and is color coded so that associations between the chart and the values are easily made

Finally, I’d like to see the sparklines be more contextual to the report or dashboard that they are in. For instance, I’m not quite sure why total page views is reported in the Visitor report. Also, is that average page views per visit or page views per visitor? I’d rather see average visits per visitor and average usage days per visitor (daily visitors divided by absolute unique visitors) than some of these other metrics.

What do you think of the Google Analytics sparklines?