Funnel Visualizations That Make Sense

It happened last week: reviewing a client’s weekly report that I inherited, and I just couldn’t take it any more:

That prompted some Twitter back-and-forth with Todd Belcher and Alyson Murphy, which led to me popping off that I’d write a post showing some of my preferred alternatives. And…this is that post.

First, a Word on Funnel Atrociousness

Funnels, as a concept, make some sense (although someone once made a good argument that they make no sense, since, when the concept is applied by marketers, the funnel is really more a “very, very leaky funnel,” which would be a worthless funnel — real-world funnels get all of a liquid from a wide opening through a smaller spout; but, let’s not quibble). Major web analytics platforms, though, simply use a static image to represent the concept of a funnel, which, as it turns out, is a pretty horrible thing to do:

1. Consumers of funnel visualizations are humans
2. Humans process graphical elements much more readily than they process raw numbers
3. Ergo, a statically proportioned funnel with real numbers on it obfuscates the actual information, Q.E.D.
   

So as not to pick on specific web analytics vendors (but ones that start with “A” and “G” are both guilty of this), below is a generic version of a “typical” funnel visualization:

For some reason, analysts like to build this sort of thing in Excel. They also tend to do a fairly lousy job of availing themselves of the various alignment and spacing capabilities of Excel, so the funnel segments don’t quite line up. And, sometimes, they even introduce a 3D effect by adding some additional ovals and curved lines. The multi-coloring is just silly…but it happens.

With the image above, what is your eye drawn to? The labels of each step? Nope! The numbers inside each step? Again…nope! They’re drawn to the big (and static) trapezoids that make up the funnel. Now, imagine if you removed the labels and the numbers and only had the funnel (since we just determined that was the strongest visual element in the image). Would it tell you anything of use? No!!!

A Better Way: A Horizontal Bar Chart

I’ve got a host of alternate ways to represent this information. The most elaborate — but still a “build once and then just update the data” option — is to use a horizontal bar chart with two series in it:

This is a “stairstep funnel,” but it’s still clearly a funnel, and the lengths of each bar are accurate representations of the values in each step. I’ve created this sort of visualization using data from “static” funnels in the past and immediately seen crazy/weird data that required a lot of scrutiny when just looking at the raw numbers on a static image. In the example above, I took a page from Adobe Analytics fallout reports (many users treat Adobe’s conversion funnels as fallout reports…although they’re quite different) and added “conversion from start” and “conversion from previous step” values. I also added in-cell conditional formatting for the “conversion from previous step” — it jumps out that the biggest falloff points are from viewing cart to starting checkout, and from entering payment info to completing the order. These values could also use color-coded conditional formatting, and those same steps would show as “red.”

To build the bar chart itself, I added two columns of data to split the total into a “negative half” and a “positive half.” These additional calculations can be somewhere off the printable area and hidden, but are shown right next to the base data below for illustrative purposes:

It’s simply a matter of plotting two series on a horizontal bar chart (“-Half” and “+Half”), changing the series properties (for either series) to an “Overlap” of 100%, and setting both series to use the same fill color.

Compact Options: Conditional Formatting

The examples above are for situations when the funnel is a Big Deal in the overall set of data being presented. That is sometimes the case, but it’s not always the case. One way to provide a more compact view — a mini-funnel — is to use in-cell conditional formatting. I sometimes only show a “half funnel” this way:

To get the bars at the right, I added a simple formula to those cells to set the value equal to the value for that step. Then, I added conditional formatting with bars and selected the option to “Show data bar only.”

That, of course, doesn’t have to be a one-sided funnel. It’s not a perfect funnel, but using the same “split values” option described in the previous section, this can also be a symmetrical funnel:

If the white gap down the middle offends your OCD, you can always manually add boxes to fill that in.

Kick it Old School with REPT

If you’re using an old version of Excel — a version that doesn’t have the conditional formatting options introduced in Excel 2010 — then the REPT function is a handy way to get a similar effect. I used to do this all the time:

All this does is repeat the “|” symbol an appropriate number of times. The one wrinkle is that you have to divide your base number by some constant. Otherwise, you’d be trying to repeat the “|” 151,131 times for the first step, which wouldn’t work! In the example above, “151,131” is in cell C5. The formula in D5 is:  =REPT(“|”,C5/10000). Make sense?

To do a symmetrical funnel, you can actually use the identical format from above, but put a right-justified column next to a left-justified one:

I like to think of this approach as the hipster option — the intentionally less crisp and clean way to achieve something that modern Excel already provides.

Are You Convinced?

These are just a few options. They’re all based on the same premise: humans process relative lengths much more easily than they process raw numbers. And, effective data visualization is all about removing friction from the cognitive funnel!