Product funnel chart using bubble chart

[Hellomoto]

I want to create a product funnel chart (a funnel oriented horizontally) using a bubble chart and a picture of the funnel as the background. the picture is aligned such that each phase review is aligned with a whole number in the x-axis. Then, the idea is to plot bubbles in the space between the x-axis intervals. Each bubble represents a concept and as each mature into projects they will move to the next phase. So, my issue is: how to assign values to each concept such that they are plotted in the funnel properly? This link below is to a jpg of a product development funnel that I want to mimick:


http://www.productstrategy.net/product-development-process/


All ideas welcomed. Thanks in advance.

 

[Luke M]

First, let me ask if you really want a funnel. In normal day use, a funnel works by having EVERYTHING that goes into it eventually comes out. Most companies have "forgotten" this idea and tend to think of funnels as filters, where more and more stuff gets left behind at each process. This leads to abominations like this:

http://peltiertech.com/WordPress/bad-graphics-funnel-chart/


The other argument against funnel chart, specific to you, is that it makes it extremely hard to track where the different projects are. Instead of knowing the bubbles are stacked in a known order, you'd have to readjust everytime they moved, as the vertical alignment would be changing.


With that said, I would think a simple X-Y scatter might work better, or maybe a bar chart? You could use decimal values to indicate how far along a project is within a phase, and each vertical mark (using integers if using X-Y, naturally using bars) to indicate each different project.


Personally, I'd work with an XY scatter, as it would be easier to add lines represxenting phase changes, and adding a dummy series with labels for the project names is not that hard.


Please don't take this as an attack against your idea personally, I've just seen way too many "bad" charts where someone went with making a "pretty picture" rather than an informative chart.

 

[Hellomoto]

Thanks for your reply Luke. Although the physics of a funnel is clear, I think most business execs failed elementary school physics Some genius saw that the curves produced by the reduction in stuff as it goes through the phases looks like a funnel. Hence, the use of the term funnel. That's my hypothesis. Like most folks, I'm just using what is typical in most companies. I'm not much of arebel


I can eliminate the curved lines that imply a necking down. This will eliminate the funnel look. Anyways, to further clarify, it's not important where they are within the phase... just what phase they are in. So, showing progress is not needed.


Original idea was to have all project leads input what phase they are in. Then have excel randomly add decimal values. The result would be plotted on a bubble chart. Purpose is to create a visually appealing graphic (eye candy). the real data that matters is not shown, which is the sum of all data points in each phase. Example, 30 concepts in phase 0, 15 in phase 1, 0 in phase 2, 6 in phase 3, etc. Based on the sum, the health of the "funnel" will be displayed via conditional formatting of a cell some where in the worksheet. (later I'll figure out how to display the sums somewhere n the worksheet, or maybe on top of the chart. For now, the sums are not shown)


A scatter plot can be used. That's what I tried originally, but preferred the segmented aspects of the bubble chart (similar to bar charts). Please excuse my bad choice of words (segmented). I really don't know how to describe the functions of each chart.


Maybe using a predfined dummy series is the trick. The dummy series will consist of a set of values that are already located on the chart to create the artwork. Then, based on the sums of each phase, those values will either be on or off (using if command). Sort of like controlling each pixel of a monitor. thanks for bringing up the use of a dummy series. That might work for both bubble and scattered plots.

 

[Luke M]

Sounds like you've got a handle on things. Jon Peltier has plenty of tutorials on how to build different chart types, as well as a few "never would have thought of that" tricks.