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 [6] 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:

http://hosting.gmodules.com/ig/gadgets/file/105832571962544454761/ddr_bullet_graph_1.xml

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.


=CONCATENATE(0,"-",(CEILING(MAX(B2:B8,C2:C8,G2:G8,H2:H8)+1,20)))
=CONCATENATE("[",((CEILING(MAX(B2:B8,C2:C8,G2:G8,H2:H8)+1,20)))/20,"]")


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

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.

Bookmark It

Posted by kalin on 2008-09-08 | 12 Comments »

Jay Leno’s Serious Advice to the U.S. Auto Industry

We had a chance to see Jay Leno in San Francisco during NADA 2008; not surprisingly, as a man who is passionate about cars, he had no problem injecting his opinions into his comedy routine. Today, Wired published an article by Leno suggesting what the American auto industry should be doing.  Beyond his thoughts on making world class vehicles that no one wants (i.e. trucks and SUVs), he suggests focusing on what American manufacturers have traditionally done best, building great cars that last without and not trying to please everyone. Of course, in classic Leno style, he provides advice you can bank on,

“No matter what happens, do not expect all American cars to go Eurosize. American buttocks are not getting any smaller.”

Bookmark It

Posted by DDR Admin on 2008-08-25 | No Comments »

Few in Review

Dealer Diagnostics traveled to Boston, June 24 – 26, to attend Stephen Few’s three day data visualization training, The 2008 East Coast Visual Business Intelligence Workshop.

Has the business intelligence field been more successful at building expectations or falling short of them?  This question will cause any professional in the industry to pause for thought.  When your correspondent had the opportunity to travel to Boston to take part in a three day data visualization workshop lead by dashboard guru Stephen Few, it was easy to imagine that the only happy ending might be a half-dozen oysters at the Union Oyster Bar and maybe a trip to Fenway. As a long-time reader of Few, the skeptic in me couldn’t help but imagine sitting through three days of material that was well covered in his existing books.  Fortunately, that’s exactly what it was.

“To see what is in front of one’s nose needs a constant struggle.”
– George Orwell

His first words were “What I’m going to show you today is not complex.” And it’s not, everyone in attendance knew it. Most in the class had read his books or at least his blog and newsletter and knew that this stuff isn’t rocket science–just bars, lines and tables. Would we be spending the workshop learning things that we already knew?

The workshops structured each day as a separate course, each priced at $350.

Day 1: Table and Graph Design for Effective Communication was based on his first book, Show Me the Numbers: Designing Tables and Graphs to Enlighten.
Day 2: Dashboard Design for at a Glance Monitoring was based on his second book, Information Dashboard Design: The Effective Visual Communication of Data.
Day 3: Visual Data Analysis for Discovery and Understanding was based on third, soon to be released, book, Now you See It.

While the material separation may have been useful to those with scheduling, budget issues or a narrow interest in presenting data in a table, it seemed unnecessary as almost everyone attended all three days. Each day was structured with a morning of lecture based theory, including a small group exercise, followed by additional afternoon theory and an interactive activity with the entire class. Few used many of the same examples that he presents in his books in addition to a few charts and dashboard examples that have been developed since each book’s publishing. However, if the message had any chance of being lost in familiarity, he brought it back again and again.

Stephen Few is an outstanding presenter. If he doesn’t already have a reputation as one of the best educators in the field, he’s certainly on his way to building it. Clearly, concisely and ever-so-smoothly Few walked a class of 65 through three days of his material without notes. His pacing and delivery were superb as was his ability to interact with the audience.

Each morning he covered basic theory and then offered the chance to critique poorly executed graphs and dashboards from software vendors. Just when the class was beginning to feel arrogant, Few would split the class into small groups and ask us to redesign a deceptive graph or misleading dashboard. There was more than one group thinking, “wait a minute, I was an expert in this a minute ago. Now what do I do?” Once Few had us back in the lecture hall and reviewed the exercise, you could sense the “aha” moments around the room when what we thought were familiar concepts became deeper insights.

At the end of each day, Few asked the class, “was anything I covered today complex?”, as a not so subtle reminder of where we had started with all of the data and limited insight.

Days earlier we walked into the workshop knowing everything Few was going to present yet after looking at samples of our work we realized that we had not learned many of the lessons: dashboards were complex enough to sell the idea to the boss, but all too often failed on the basics–choosing an appropriate color to best aid perception for instance or reducing graphical clutter, however brilliantly conceived by an analyst.

Few, with his mantra of simplicity, helped focus even the most experienced mind on improving the basics and letting the data tell its story.

Bookmark It

Posted by DDR Admin on 2008-07-01 | 8 Comments »

Follow the Action at Few’s Visual Intelligence Boston

For anyone interested in data visualization, between June 24th and 26th you’ll be able to follow the action of Stephen Few’s sold out Visual Intelligence Workshop via our Twitter feed.  Follow us and share in the cheers, jeers and tears plus watch for a few pictures that will be posted in real time.

Bookmark It

Posted by DDR Admin on 2008-06-24 | No Comments »

Summer Reading List

BusinessWeek has published its suggested summer reading list for MBA students. While it promotes the list as guide to stay on top of the current business zeitgeist, they also include a lot of classics (Benjamin Franklin’s Poor Richard’s Almanack) and recent hits such as The World is Flat and Freakonomics. What is particularly interesting is that Dan Roam’s Back of the Napkin made the list. I had the pleasure of listening to an excellent session from Dan at Vizthink 2008 in San Francisco. He’s an exceptionally sharp visual thinker and it’s nice to see BusinessWeek recognizing that visual thinking has earned some mind share in the business world. Not surprisingly, Dan has the best summary of the reading list, in a visual format of course.

Bookmark It

Posted by trevor on 2008-06-10 | No Comments »

The Odds Are Stacked Against Us

The world is awash in numbers yet most people find it harder than ever to understand them. Cory Doctorow, writing the Guardian, focuses on rare events and people’s inability to estimate their probability. Whether it’s playing the lottery, investing in the market, or running a business, most people rely on their intuition and end up making terrible decisions. Doctorow argues, “We’re just not wired to have good intuition about things that happen with extreme infrequency.” To remedy the situation he goes on to suggest

You don’t get to understand the statistics of rare events by intuition. It’s something that has to be learned, through formal and informal instruction. If there’s one thing the government and our educational institutions could do to keep us safer, it’s this: teach us how statistics works. They should drill it into us with the same vigor with which they approached convincing us that property values would rise forever, make it the subject of reality TV shows and infuse every corner of our news and politics with it.

Infrequent events or not, a better grasp of the numbers would certainly make for a lot more restful nights and more profitable days for any managers.

Bookmark It

Posted by trevor on 2008-05-21 | 2 Comments »

Going the Extra Inch: Airlines with Legroom

A post in the Economist’s travel blog, Gulliver, and a story in the New York Times had me thinking about legroom on airlines. According to UK-Air.net, one of the few ways to forecast your leg cramping comfort is by looking at seat pitch, which is “the distance between the rows of seats and is measured from the back of one seat to the back of the seat behind.” I began charging through a few sites which graciously list the data in tables. Tables are fine for listing data but for making quick comparisons or any type of analysis, visualizing the data is much more effective. A picture says a thousand words after all. People absorb a picture in an instant while taking several minutes to digest evan a few paragraphs or tables. Wanting to share what I’d learned about legroom with others I put together a quick graphic.

Below, I’ve charted the minimum seat pitch you can expect on a particular airline. (A lot of airlines vary seat pitch by aircraft or level of service, such as economy or economy premium/plus.) A seat pitch of 28 inches is the minimum required by UK regulations. For simplicity and for a little further insight into the airlines that I tend to fly, I organized the list of world airlines to those in the big three alliances. Enjoy.

Bookmark It

Posted by trevor on 2008-05-15 | 2 Comments »

Create Bullet Graphs with Google Charts in 7 Easy Steps

UPDATE: We have taken this a step further with a gadget which will allow you to build these using data from a spreadsheet. Check it out!

Stephen Few’s bullet graph, was a welcome addition to the data visualization scene in 2005. Up until that point, displaying a single measure graphically usually involved the dial, gauge or traffic light metaphor. Since then, more visualization tools and business intelligence vendors have started to support bullet graphs. We’ll take take a look at how to create your own bullet graphs using the Google Charts API, using nothing but your browser, in 7 easy steps.

(For those of you who don’t care how they’re made and want instant gratification, you can build your own bullet graph in the form below)

Step 1: Meet the Google Chart API

The Google Chart API allows anyone to create a chart image by calling a specifically formated URL. For the purposes of this demo, all you need to know is that when you give the Google Chart API a URL, it gives you back a png image.

http://chart.apis.google.com/chart?chs=100x40&cht=bhs&chco=000000&chd=t:65&chbh=15&chm=r,ff0000,0,0.49,0.51,1|r,666666,0,0,0.5|r,CCCCCC,0,0.5,1&chxt=x&chxl=0:|0|50|100&chxs=0,000000,9

returns this image:

Step 2: Deciphering the URL

That’s a long and confusing URL. Let’s break it down to see the pieces.

The URL has parameters. These specify size, color, labels, etc. Here’s the important part: each parameter is separated by an ampersand character (&). The URL looks a little more manageable when we isolate the parameters. (Here’s a picture of how the URL breaks down.)

chs=100x40
cht=bhs
chco=000000
chd=t:65
chbh=15
chm=r,ff0000,0,0.49,0.51,1|r,666666,0,0,0.5|r,CCCCCC,0,0.5,1
chxt=x
chxl=0:|0|50|100
chxs=0,000000,9

Don’t worry about what each means at this point (at the end of the page, I have described each of them). For now, simply recognize that a long URL can be easily broken down into a bunch of parameters.

Step 3: Build a Simple Bar Chart

Let’s start with the base of our URL: http://chart.apis.google.com/chart? and add on our parameters

http://chart.apis.google.com/chart?chs=100x40&cht=bhs&chco=000000&chd=t:65&chbh=15

Now we have a simple horizontal bar chart that will be the performance measure in our bullet graph.

Step 4: Set the Symbol Marker

The thin vertical bar displays the comparative measure. Start with the base URL from step three and add the comparative measure.

http://chart.apis.google.com/chart?chs=100x40&cht=bhs&chco=000000&chd=t:65&chbh=15&chm=r,ff0000,0,0.49,0.51,1

Chart markers operate by separating a chart into a range from 0, on the left, to 1, on the right. I wanted a thin red line, but not too thin, so I made it stretch from 0.49 to 0.51.

Finally, did you notice the “1” on the end? That “1” tells Google that I want the red marker to show up on top of everything else I have in the graph; that is, I want the red marker to be placed on top of the black bar, not below it where I won’t see it.

Step 5: Set the Bands

The colored bands are the qualitative ranges to show if your measure is good, bad, above target, below target or whatever you prefer.

http://chart.apis.google.com/chart?chs=100x40&cht=bhs&chco=000000&chd=t:65&chbh=15&chm=r,ff0000,0,0.49,0.51,1|r,666666,0,0,0.5|r,CCCCCC,0,0.5,1

Note the pipe symbol “|” that separates the two bands.

Step 6: Set the Scale

Google Charts makes scales easy by treating them like labels, allowing you to set them however you please. I’m going to keep it simple and use an easy scale.

http://chart.apis.google.com/chart?chs=100x40&cht=bhs&chco=000000&chd=t:65&chbh=15&chm=r,ff0000,0,0.49,0.51,1|r,666666,0,0,0.5|r,CCCCCC,0,0.5,1&chxt=x&chxl=0:|0|50|100&chxs=0,000000,9

The chxt parameter specifies that you want to label the x axis. The chxl parameter supplies the values for each label, and chxs specifies the first label (0), the text color (in hex: 000000), and size in points.

Step 7: View

A bullet graph is born. Thanks to the Google Charts API, you can embed the png image through the URL into your web site, document or application (check out the terms of service). Don’t forget to add a text label to your bullet graph so that people know what you’re presenting.

Conclusion

In closing, you should have a pretty clear idea for how a bullet graph can be created using the Google Charts API. For this demo, we wrote the URL by hand, but library support is widely available, although no libraries support bullet graphs out of the box.

Try it Yourself

We whipped up a quick javascript form to generate bullet graphs (hat tip to Bas Wenneker whose library we modified). If people find it useful enough, we could clean the code up a bit so it fully supports bullet graph creation. Let us know.

Your bullet graph:

Additional Examples

Hopefully this demo gave you enough to get started and get the wheels turning in your head. There’s a lot more that can be done with this type of information graphic; we’re looking forward to seeing what people come up with: drop us a line (or URL) and show us your chops.

Bookmark It

Posted by trevor on 2008-05-13 | 18 Comments »

NADA 2008

Three weeks after Vizthink, we were back in San Francisco for NADA 2008, the retail auto industry’s largest annual convention. Based on the positive feedback from the dealerships involved in our pilot and what we heard from some of the best minds in the visualization community, we thought it was the perfect time to announce the reports to a larger audience.

And what an audience it was: 24,000 people, 700 exhibitors, 5 halls. We spent a lot of time meeting up with people in the business so we didn’t get to see some of the more interesting sessions. By all accounts the conversation was well-developed with a lot of dealers working through the issues of making the most of their technology investments and looking for opportunities to work better and smarter (read excellent summaries of 3 of these sessions at The Light is Green).

Where ever we went in San Francisco there were dealers swapping ideas and making the most of their time on the coast. We had a chance to meet up with several important people in the industry, including DMS vendors, CRM providers, OEMs plus a mass of dealers, and went quite a bit further than we thought we were going to go.

I’d like to tell you more about how those went, but they’re in progress/on-going, so you’ll have to wait for the full story.

The convention ended with an oustanding speech from Tom Brokaw. He challenged the dealers and industry people in attendence to focus on our duties, to never rest and to seek out innovation and new ideas, building upon what was left by those who preceded us, and leaving something greater for those who follow. The room fell silent for a few moments before erupting in a standing ovation.

Bookmark It

Posted by kalin on 2008-05-08 | No Comments »

VizThink 2008

The idea to start to a blog was always on our mind. There was a lot going on for us and a lot of news to share but when faced with a limited amount of time and the decision of writing news or developing reports, the choice was pretty easy.

First, there was VizThink 2008, held in San Francisco in February. The inaugural conference brought together people to discover, create and share ideas on how visualization could help them in their endevours. They came from every where you could imagine, public, private and not-for-profit; from Fortune 50 to one person start-ups. Everyone was there to learn how to convey information more effectively through an image and within an hour of attending a few of the talks and workshops, you could almost see the light bulbs turning on over people’s heads. In fact, within the first 10 minutes when Dave Gray, of Xplane, led entire conference through a five minute exercise on how to draw what you do, it was like putting two hundred fireflies into a jar and shaking it up. If you have any interest in the emerging community of visual thinkers, VizThink is definitely worth a visit. Anyway, on with the show….

Over the next month, we’ll mention some specific nuggets of wisdom that popped up during the two days; for now, here’s a personal/professional highlight: meeting renowned visual communications guru Karl Gude. Karl managed information graphics for Newsweek magazine for over a decade, and at the Associated Press prior to that, he’s currently putting together an Information Graphics program at Michigan State University’s School of Journalism. Karl was generous enough to give our reports a twice over and had some good suggestions right off the bat, as you’d expect from someone with his level of experience. In email exchanges after the conference we were quite humbled by some of his comments (quoted with permission):

Your well designed and useful report provides a clear visual snapshot
of a dealerships complete financial picture on a single page, from a
broad-brush performance review of overall earnings and vehicle
category sales to a detailed horse race of each individual sales
associate’s fiscal contribution. The amount of data that has been
pulled from the record books and packaged here so nicely is really
quite amazing. Your graphics rocks.

Now there’s a Dealer Diagnostics’ milestone that will become etched into office lore.

Beyond the feedback on the reports, which included thoughts from people in as diverse industry’s as finance (CIBC Global Markets), print (Wired) and government (the CIA), a lot of ideas fell out of the event. Here’s what came of Dave Gray’s 10 minute drawing exercise.

It also proved to be a hit with everyone we showed it to; so much so, it became the back of our business card. Any idea what we do?

Bookmark It

Posted by trevor on 2008-05-07 | No Comments »