Structuring data for Funnel charts

[nmg]

I have a classic usage case for a Funnel chart: number of opportunities in a sales cycle:

..but when I visualise with the Funnel chart, I get this:

The first stage is not shown at all, and the rest of the steps are expressed as a percentage of the first stage and not the overall total.

I've read the documentation on Funnel charts, it shows a funnel like this:

...which makes me think that the data needs to be structured in a particular way; I'm guessing the overall total needs to be included in the results to act as the first 'step'? I knocked up a quick union query using the sql editor to do this:

...but it seems when I do, the Funnel chart is no longer selectable when using SQL questions?

Has anyone had any success using this chart, and if so, is there indeed a secret to structuring your data? Or is the funnel chart broken?

I've tried using 0.29.3 and 0.30.1, both return the same results.

Thanks for reading

[jornh]

Trying to loop in @kwilde … who I’ve seen is using funnel

[kwilde]

Your first example is exactly like my funnel charts which each percentage being a percentage of the first step which you show to be 359 (ex 100/359=.27855). The second example is the same (ex 727/3901=.1889)

You don’t need an overall total. If you want to see an overall total and percentages of that you would want a pie chart.

[nmg]

Well, here's something I got wrong: my data is showing number of opportunities currently in each stage; to make the funnel I need a reverse cumulative sum:

..as the opportunities in the later stages have already passed through the previous (the source data doesn't track stage progress).

I still don't think this will produce the same result as the example funnel; the structure of the data is not changing, just the values

Edit: I love how you can freehand SQL stuff in Metabase:

As suspected, still not quite the same format as that in the documentation, but at least the numbers are accurate

Double Edit:
I can make this:

Using this:

Which is about as close as I can get to this:
https://www.metabase.com/docs/latest/users-guide/images/visualizations/funnel.png

[jornh]

@nmg

Edit: I love how you can freehand SQL stuff in Metabase:

Which just made me google this markdown (but aparently also SQL - and other forms) of tables:

Table Editor

[nmg]

Nice find @jornh !

For the record, I’m convinced the Funnel Chart is broken. I simply cannot replicate the same format as the example in the documentation.

[jacobwb]

This is an oldie now, but I find it's easier to give each stage a number, like with:
CASE
WHEN status = 'Making Introductions' THEN 1
WHEN status = 'Sending Proposals' THEN 2
...

Then aggregate everything by current status, and count how many things are in that status:

Do your select and get your results in a CTE like this:
WITH step_1 AS (
SELECT status,
<use the case statement from above> AS status_order
COUNT(*) or COUNT(<some GUID>) AS instances
FROM whatever_table
GROUP BY status
)...

Then, count the things that are either in that status or went past that status by using a partition function when you select from step_1. Just do a window, and sum up the number of instances up to and including the one in each row. So, if you have 5 sequential steps, if you want the funnel number for step 3, you want a row that says "step 3" and has a number summing everything that's in step 3, 4, and 5. You can do it like this:

SELECT status,
SUM(instances)
OVER(ORDER BY status_order DESC ROWS BETWEEN UNBOUNDED
PRECEDING AND CURRENT ROW) AS things_that_went_at_least_as_far_as_this_step
FROM step_1
ORDER BY status_order;

This did the trick for me!