Did Jeff just chart?

Chandoo: Did somebody just chart?
Jeff: Yes. Yes I did. More on that later. But first, let’s take a sniff of Mike Alexander’s outliers, shall we?

Over at the bacon bits blog, Mike has an interesting post on using something called the Tukey Method to identify outliers in a data set. That article is worth reading for John Walkenbach’s comment alone.

Here’s Mike’s sample dataset, with the data points identified as outliers highlighted in orange:

The Tukey method that Mike blogs about constructs a fence around “reasonable” readings, and that fence is described mathematically by an arbitrary numerical factor:
(Quartile 1) – (Arbitrary_Factor × IQR)
(Quartile 3) + (Arbitrary_Factor × IQR)

Typically a factor of 1.5 is used. Check out Mike’s blog for a detailed explanation of this stuff.

That’s all good, but it also produces a fairly arbitrary cut-off, depending on what factor you use. So rather than using an algorithm to determine outliers, my preference is to sort the data from lowest to highest value, then plot it and look at the resulting shape:

—Edit— Jon says in the comments:
Your line chart would be easier to read if you’d used markers. I use markers to indicate where the data actually IS, and help show that the line only ties the data together and doesn’t indicate more data, until the points are nearly touching.

Trust Jon to chart in my face. But he’s right. So here it is:

[Aside: That chart’s done in Excel 2013. What’s weird is that those markers aren’t centered on the line, but seem to sit just above it by a point or two. Whoops, Microsoft.]

And here it is with data labels, so it’s easier to see the actual values:

Some may say that the data labels are redundant, because you can gauge the values from the axis. My mature response to that is “Ffffffrrrrrt”. I like the data labels…once I’ve used the line to quickly judge what may be outliers, the labels let me confirm the jump in values without having to move my head back and forth like I’m watching Roger Federer play Andy Murry at Wimbledon.

In fact, maybe I can combine the marker with the labels, and get rid of that axis altogether:

Hey, that looks cool. Anyone going to get Tufte on me?
—Edit over—

This is akin to making a bunch of actors line up in order of shortest to tallest, and saying:
Okay…Elijah, Dominic, Billy, and Sean…you’re shortest. And by golly, you four look a lot shorter than the others. You guys can be the Hobbits.

[Aside: I recreated the below graph from one a site called SFScope. Check out the outliers at both ends, and click on the picture to visit the original]

I like this graphical approach. I think it takes less effort to visually identify outliers than to programatically identify them. For instance, let’s look at Mike’s sample data again for a moment:

Looking at this data, I visually identify pretty much the same outliers as Tukey would – points 1,2,3, 19, and 20. In addition, it looks like that 4th data point – with a value of 13 – looks like it has outlier stamped all over it too, when you see it in context of the other data.

Another benefit of plotting ranked data is that it also allows you to ask questions about interesting trends within the datapoints that clearly are not outliers. For instance, what’s the deal with the sudden ‘acceleration’ in the trend between datapoints 16 and 17 caused by? Understanding drastic changes within non-outlier points might be worth as much money to a business as understanding the outliers themselves.

Lose the horizontal axis?

Sometimes with larger datasets, that horizontal axis can be distracting, because Excel only has enough space along that axis to display the labels for every nth rank.

For instance, take the below graph, which looks at just how much money an organization receives from each of its customers by way of annual membership subscription each year:

See what I mean? You find yourself trying to decipher the trend in the data labels, and this really draws your eye away from the incredible trend shown in the graph above.

So let’s just delete them:

That’s much less distracting. Wow: many of our customers hardly subscribe to anything, and a few practically keep this place afloat!

What else can we show on a graph like this?

Sorting your data like this also lends itself to visually segmenting your customers by how much they contribute to your total revenue.

For instance, the below graph shows just how many customers it takes to account for each subsequent 25% of revenue, and what the average annual subscription within each group is. This gives you a real appreciation into just how valuable your larger customers are in comparison to smaller customers:

Wow, half our subscription revenue comes from our Key Accounts and Large Customers groups, who make up just 10% of our subscription base. Let’s be especially nice to those customers. And lots of our effort is spent in servicing small clients that don’t buy much. Can we grow their business? Should we sack some of them as customers, so we can spend that effort finding bigger ones?

Using revenue ‘buckets’ of 25% was a fairly arbitrary choice. What if we designed a chart template that let you dynamically choose different sized revenue buckets, as well as let you use more buckets if you wanted to?
For instance, looking at the above graph, it looks to me that we have a whole bunch of ‘Tiny Customers’. And we also might want to segment that group of Median customers that all have exactly the same sized subscription into a group of their own.

Well, the chart template I’ve put together for this post lets you do just that:

Wow. Jeff charted again. Man, look at all those time-wasting small accounts…they’re about as welcome as a chart in an elevator!

Note that the above graph was produced using Excel 2013. Excel 2013 automatically puts in those grey lines connecting the data lables with the series. Those are called Leader Lines. They rock.

Unfortunately, earlier versions of Excel only use leader lines for pie charts. But fear not, intrepid reader, for my chart template uses a bit of VBA to automatically puts lines in for you using shapes, if you’re using Excel 2010:

What’s cool about this template is that all the data labels are dynamic: change the ‘breakpoints’ between groups or the number of groups in the ‘Controls’ table [see screenshot below], and the details within the data labels are updated automatically. Bing!

I modified a version of Jon Peltier’s great Label Last Point routine to refresh the placement of the data labels. (Thanks, Jon). Here’s the template, so you can play around in the privacy of your own screen:
Segmenting customers by revenue contribution_V1 [Not tested in Excel 2007 or earlier]

Oh yes. I most definitely charted, boss.

Updates

—Update 1—
Prompted by some great action in the comments below, I whipped up this redesign in both gray and white:

While I like the grey, I do think it’s harder on the eyes than black text on white background. And I don’t think a grey chart would work well on say a dashboard. But that said, there’s no doubt in my mind that this chart is sexier than my original. Might look nice in the Economist. Here’s a link to the revised sample file: Segmenting-customers-by-revenue-contribution_with_Leader_Lines V1

—Update 2—
Kaiser Fung has some great ideas on how to redesign this in his post Visualizing Uneven Distributions. Go check it out, and be sure to subscribe to both his Junk Charts blog as well as his Big Data, Plainly Spoken blog. Both are gold. Both will make you a better analyst.

Added by Chandoo

If you like this chart, chances are you are going to love the below too:

• Finding non-performing customers using Pivot Tables
• Pareto analysis and charts using Excel
• Market segmentation charts using Excel