The DDR Bullet Graph Gadget
We received a lot of great feedback when we posted our step-by-step guide on building bullet graphs using the google charts api. It’s no surprise that people want to take advantage of this compact and expressive visualization, especially since ready-to-go implementations are supported by only a few high-end visualization software packages.
While I was getting ready to leave for Boston to attend a workshop with the inventor of bullet graphs himself, Stephen Few, it struck me the time I was about to spend offline in the air there and back could be best used taking the Google charts bullet graph to the next level–as a Google gadget designed to pull data directly from a Google spreadsheet. We’ve played around with the concept a bit since then, and had some people test it out. Here’s how you can use it for your own data.
Step 1 – Create a Google spreadsheet
If you have a Gmail or Google account you are already able to create and share Google spreadsheets. Log into your account here. If you don’t have an account yet, you’ll have to sign up. Adding data can be done through a variety of ways: type it directly to the spreadsheet, import it from a range of file formats such as Excel, add it through automatically generated forms embedded in a web page or email, remote database calls, etc.
Step 2 – Format the data for the bullet graph
Let’s declare right away that the Google spreadsheet is not Excel. On the plus side, you’re going to be able to create a bullet graph by simply highlighting your data and selecting the bullet graph gadget. You’ll also have easy ways of sharing your visualizations over the web. On the negative side, the data will need to be setup in a way that the gadget can use. Fortunately, the convention is pretty simple and once learned, you’ll be able to create twenty bullet graphs as easily as you create one.
The gadget will display a label, units, and a scale if this information is provided in the selected/highlighted range. All of these are optional.
Units — Any text within parenthesis will be used to display units in the final output, in this case we have (U.S. thousands $). Optional.
Tick marks — A number enclosed in square brackets  will be interpreted as the number of tick marks. It’s best if this divides evenly into the full range of your graph, but not necessary.
Label — Any other text will be used for the label: 2005 Revenue YTD in this case.
In the screenshot, all of these values are in a single cell, but you can split them up into different cells in any order if you find it more convenient. The gadget reads the selected/highlighted range from left to right and once the gadget has a label, unit description, or number of ticks it will ignore any others that follow. For example, the row below will give you the exact same result.
The first numerical value in the row is always used as the featured value: 275. All other numerical values in the selected range will be interpreted as comparative values. In this case we have one comparative value: 260. Now, if you’re looking at the example and thinking, “what about column A, that has a number?” That’s actually a string, look for the first cell that has only numbers (no hypens and square brackets).
Ranges are specified as start-finish, in the example we have three ranges: 0-200, 200-250, 250-300.
You don’t have to cover all the possible values with your ranges, the gadget will insert ranges where they’re required as long as you specify the minimum and maximum values somewhere (in this case 0 and 300). You could produce the same results with these ranges 0-200, 250-300 or you can specify the total graph range and ranges you want to call out within this 0-300, 200-250. All of the below rows will give the same result.
This is designed to make it less tedious for you to calculate the specific ranges most of interest to you.
If you want to calculate these ranges from values in your spreadsheet, format the cell using the CONCATANATE formula, like this: =CONCATANATE(H5, “-”, H6)
Step 3 – Insert the gadget, and set your options
To insert the gadget highlight the cells we’ve assembled and click on the graph button in the toolbar, choosing gadget. Since our gadget is not yet in Google’s featured gallery (spread the word, and we’ll see what happens) you need to click on the link that says ‘Custom…’. Paste in the following url:
You’ll be presented with a number of preferences, all of these are optional or have reasonable default values except for ‘Range’ which should already be filled in with the cell range you selected/highlighted. If you want to specify additional comparative values or ranges that aren’t represented in the cells you selected you can enter them here.
If you want to change the colors of your comparative values you can enter a color using html hex values. A bright red would be specified with ff0000, a more subdued red that doesn’t steal as much attention from the viewers eye would be 990000. Try hitting apply and you should see your first gadget.
Most of the time you should stick to black for most aspects of the graph, if you need help getting colors in 6 character hex format there are plenty of color scheme selectors available on the web, one of my favorite sites for choosing colors that look good in data visualizations is ColorBrewer.
The gadget will automatically select a lower saturation for each additional range and comparative value you have according to Few’s bullet graph specification. The left range is always the darkest in the current implementation and it will be between 35% and 50% of the saturation of the color you specify.
Another important preference is CSS (cascading style sheets). All the elements produced in the graph are classed as ddr_bullet_graph_gadget, and the table cells are also classed as ddr_bullet_graph_column1 and ddr_bullet_graph_column2. If you plan to post the bullet graphs on your website and want to modify the padding, margins, or font-family to better match your site design you can turn off the default css and use your own styles by unchecking the box.
Finally, you also have the option of setting the size of the bullet graph in pixels, and putting the comparative measure bars above or behind the feature bar.
Step 4 – Create a stack of bullet graphs
One bullet graph on its own is fine, but graphical representations of data are made more useful when placed in the same visual field as other relevant information. You already know everything you need to make this happen, just select more than one row of data to place it in a bullet graph. While I was developing this code the Olypmpics were on so I kept a simple spreadsheet with Olympic medal counts.
I started with a few sources of data. The 2008 medal counts, are my feature measure, the 2004 medal counts represent a comparative value. I also wanted to see how each country was doing against projections of 2008 performance. PriceWaterhouseCoopers publishes a research paper projecting medal counts, and Andrew Bernard of Dartmouth does the same. I used this data to compute a projected range column which will put a range in the bullet graph indicating if the country managed to achieve a result consistent with what the experts expected of them.
The total range and ticks columns are computed using formula that find a range that will include all the values in the spreadsheet and then makes that range divisible by 20. I did it like this so the graph range would dynamically update if the 2008 medal data required it. Here are the formulas I used to create these calculated values.
Step 5 – Spread the insights
You’re free to use the Dealer Diagnostics bullet graph gadget anywhere and anyhow you want, it’s under the MIT open source license. If you think of a way to make it better, feel more than free to tell us about it or submit a patch. We have a few more enhancements planned based on some of the feedback from our beta testers, including an option to have a dot instead of a bar, vertical bullet graphs, and some extra display options.
Google spreadsheets can be easily shared with others (do we need to tell you to check with your company’s security policy before posting your data on the web?) and it’s possible to take this one step further. Click on the small arrow in the top right of your gadget and you’ll see options to publish on the web, or add to iGoogle.
Sharing it on the web gives you a code snippet which you can use to embed your gadget directly into a website. It’s a fast way to visualize data quickly for a blog post. When the page is reloaded it pulls updated (i.e. most recent) data down from your spreadsheet.
2008 Olympics medal count by country
Vertical line is 2004 result, medium grey range is projected result.
sources: 1 2 3 4
The tight range on the Great Britain graph showed that both of the projections we used were in close agreement on what to expect, a performance very close to 2004. While China was the talk of the games, the graphs suggest that the UK was the real story when it came to beating expectations.
If you’re wishing that the Google logo wasn’t there, subscribe to our rss feed. In a future post I’ll show advanced users how to use the Google visualization API without the gadget container and even without having to use data from Google spreadsheets.
Bonus Step – A simple personal dashboard
Adding the gadget to iGoogle places the it on your custom iGoogle home page where you can organize it in columns and tabs. You also get access to a new preference which allows you to set a refresh rate for the graphs without requiring a page reload, minute-by-minute if your data is being updated that rapidly. Click here to try it out.
Like us, some of you have probably never played with iGoogle before, but you might think of it as a personal dashboard where you monitor a set of indicators: usually things like recent mail, weather, important headlines, and stock performance. There are hundreds of gadgets built by Google or various third parties and you can access a lot of decent data visualization gadgets for making common types of graphs from within Google spreadsheets. You have to make the data you share with gadgets on iGoogle publicly accessible so this wouldn’t work for businesses viewing sensitive data. But you might use this for prototyping web dashboards, or for displaying non-sensitive data (your fantasy baseball stats from a shared spreadsheet), or publicly available data that you can freely republish (economic statistics, global financial indicators, interest rates, inflation, real estate data).
You’re not going to make the most advanced dashboard with these tools, this is pretty quick and dirty stuff. However, by loading your data into Google spreadsheets, using Google gadgets for visualization (especially now that you can use bullet graphs instead of gauges), and organizing them on iGoogle, you can rapidly create a simple but effective web-based dashboard. I bet a lot of you could use these tools to out-design many of the examples posted on the websites of big-name business intelligence software… in an afternoon.
Let’s see your chops, show and tell us about your results in the comments.