Funnel charts
Use funnel charts to show progression through steps.
Funnel charts visualize how a measure breaks out across a series of steps. Typically they’re used to show how many people make it through a particular sequence, such as a checkout flow on a website. The first step (or gate) would be how many people visited your site. Then how many people viewed a product page (step 2), how many added that item to their cart (step 3), and so on.
We’ll walk through how to build a funnel chart in Metabase using the Sample Database included with your installation so you can follow along. We’ll show examples both in the query builder and the sql editor:
The Sample Database doesn’t contain events; it’s just four tables with order, product, and customer information. So we’ll have to get a little creative here to come up with examples for funnel charts.
Funnel chart example using the query builder
Here’s a contrived example. We’re going to pretend that the steps in our funnel are product categories (because we don’t have anything like statuses or pages or other progressions in our Sample Database). Here’s the notebook view of our query:
What we’ve done is joined the Orders and Products tables (see Joins in Metabase), summarized the count of orders, and grouped those counts by product category. Then we’ve sorted the results by count, descending. To get a funnel chart, we clicked on Visualization in the bottom left, and selected Funnel. In the settings of a funnel chart, under the Data tab, you can set the Step (in this case we’re using the product category) and the Measure (the count of orders).
Notice that in the Settings -> Display tab, you can change the Funnel type to “Bar chart”, which is another valid way of representing the data. The advantage of a funnel chart (beyond the visual metaphor) is that Metabase will also show the percentage of the measure that made it through each step.
Keeping the steps sorted with a custom column
If the count in each step doesn’t naturally decrease, you may need to sort the steps manually to preserve the actual progression of steps. For example, if you have the same count in successive steps, the steps could get swapped in the funnel chart, like if Metabase defaults to sorting the steps alphabetically to break the tie. Likewise if you have funnels that can expand in count at certain steps (new people entering the funnel halfway through, for example) the funnel will default to descending counts, which will mess up your step order.
In these cases, you can create an additional column to number the steps, and sort by step to enforce the correct funnel sequence. Using the above query as an example, we could modify it to preserve the sequence by adding another column, step, and then sorting by step.
Here’s the custom expression:
case([Products - Product Name → Category] = "Widget", 1, [Products - Product Name → Category] = "Gadget", 2, [Products - Product Name → Category] = "Gizmo", 3, [Products - Product Name → Category] = "Doohickey", 4)
Basically, we’re saying Widgets are step 1 of the funnel, Gadgets are step 2, and so on.
Funnel chart example using SQL
Another contrived example using the Sample Database: let’s say we learn that customers with the highest lifetime value are those that place orders from all four of our product categories: Doohickeys, Gadgets, Gizmos, and Widgets. So for this example, we want to see how our customers break out based on how many different categories of products they’ve ordered.
A crucial distinction to make here is that we’re not trying to see the distribution of customers, i.e., we’re not trying to see if how many customers ordered from just one product category, how many order from two categories, and so on. We’re going to take all the customers who placed an order for any category as step one. For the next step, we’re going to winnow that population down to only those who placed orders in at least two product categories, then three categories, then four.
Let’s say we have a customer pool of one hundred customers who placed orders. The table would look something like this:
| Step: number of categories | Count of customers |
|:------------------------------|:-------------------|
| Ordered from one category | 100 |
| Ordered from two categories | 70 |
| Ordered from three categories | 40 |
| Ordered from four categories | 20 |
Our plan of attack: we’ll break this query up into four subqueries using common table expressions, with each query further refining our results. Then we’ll UNION all of the results into a single results table.
We’ll start by getting all the customers that ordered from us, and put that query into a CTE to build another subquery. In the codeblock below, we call the first subquery starting_data. To get the first “step” of customers, we’ll create a new subquery, cat_one, that grabs as its starting data the results of starting_data.
WITH starting_data
AS (SELECT people.id,
products.category
FROM people
JOIN orders
ON people.id = orders.user_id
JOIN products
ON orders.product_id = products.id
GROUP BY people.id,
products.category
ORDER BY people.id),
cat_one
AS (SELECT id,
Count(id) AS cats
FROM starting_data
GROUP BY id
HAVING cats > 0
ORDER BY id)
We’ll do the same (i.e., progressively build on the previous results) for the next two steps:
WITH starting_data
AS (SELECT people.id,
products.category
FROM people
JOIN orders
ON people.id = orders.user_id
JOIN products
ON orders.product_id = products.id
GROUP BY people.id,
products.category
ORDER BY people.id),
cat_one
AS (SELECT id,
Count(id) AS cats
FROM starting_data
GROUP BY id
HAVING cats >= 0
ORDER BY id),
-- People who ordered from at least two categories
cat_two
AS (SELECT id,
Count(id) AS cats
FROM cat_one
GROUP BY id
HAVING cats > 1
ORDER BY id),
-- People who ordered from at least three categories
cat_three
AS (SELECT id,
Count(id) AS cats
FROM cat_one
GROUP BY id
HAVING cats > 2
ORDER BY id),
-- People who ordered from at least four categories
cat_four
AS (SELECT id,
Count(id) AS cats
FROM cat_one
GROUP BY id
HAVING cats > 3
ORDER BY id)
So, now that we have our four results: cat_one, cat_two, cat_three, cat_four, we’ll need to combine those results into a single result table. We’ll use UNION to combine the results.
-- Now we union these four results to produce a single table
-- that we'll use to build our funnel chart. The table will have two columns:
-- the Step: number of categories (our step),
-- and the count of customers (our measure).
SELECT 'Ordered from one category' AS "Step: number of categories",
Count(*) AS Customers
FROM cat_one
UNION
SELECT 'Ordered from two categories' AS "Step: number of categories",
Count(*) AS Customers
FROM cat_two
UNION
SELECT 'Ordered from three categories' AS "Step: number of categories",
Count(*) AS Customers
FROM cat_three
UNION
SELECT 'Ordered from four categories' AS "Step: number of categories",
Count(*) AS Customers
FROM cat_four
ORDER BY customers DESC
Funnel chart query
Here’s the full query:
WITH starting_data
AS (SELECT people.id,
products.category
FROM people
JOIN orders
ON people.id = orders.user_id
JOIN products
ON orders.product_id = products.id
GROUP BY people.id,
products.category
ORDER BY people.id),
cat_one
AS (SELECT id,
Count(id) AS cats
FROM starting_data
GROUP BY id
HAVING cats >= 0
ORDER BY id),
-- People who ordered from at least two categories
cat_two
AS (SELECT id,
Count(id) AS cats
FROM cat_one
GROUP BY id
HAVING cats > 1
ORDER BY id),
-- People who ordered from at least three categories
cat_three
AS (SELECT id,
Count(id) AS cats
FROM cat_one
GROUP BY id
HAVING cats > 2
ORDER BY id),
-- People who ordered from at least four categories
cat_four
AS (SELECT id,
Count(id) AS cats
FROM cat_one
GROUP BY id
HAVING cats > 3
ORDER BY id)
-- Now we union these four results to produce a single table
-- that we'll use to build our funnel chart. The table will have two columns:
-- the Step: number of categories (our step),
-- and the count of customers (our measure).
SELECT 'Ordered from one category' AS "Step: number of categories",
Count(*) AS Customers
FROM cat_one
UNION
SELECT 'Ordered from two categories' AS "Step: number of categories",
Count(*) AS Customers
FROM cat_two
UNION
SELECT 'Ordered from three categories' AS "Step: number of categories",
Count(*) AS Customers
FROM cat_three
UNION
SELECT 'Ordered from four categories' AS "Step: number of categories",
Count(*) AS Customers
FROM cat_four
ORDER BY customers DESC
Which should yield:
| Step: number of categories | CUSTOMERS |
|-------------------------------|-----------|
| Ordered from one category | 1,746 |
| Ordered from two categories | 1,632 |
| Ordered from three categories | 1,428 |
| Ordered from four categories | 1,031 |
Now all we have to do is click on Visualization in the bottom left and select Funnel.
If you open up the Settings tab, you can change up the Step or the Measure. In the Display tab, you can change the chart from a funnel to a bar chart (though as mentioned above, you’ll lose both the visual metaphor and the measure’s percentage with respect to the first step).
Keeping the steps sorted in SQL
Like above with the query builder, to enforce the step order, you can add an additional column (which we’ll call “step”:
SELECT 'Ordered from one category' AS "Step: number of categories",
Count(*) AS Customers,
1 as step
FROM cat_one
UNION
SELECT 'Ordered from two categories' AS "Step: number of categories",
Count(*) AS Customers,
2 as step
FROM cat_two
UNION
SELECT 'Ordered from three categories' AS "Step: number of categories",
Count(*) AS Customers,
3 as step
FROM cat_three
UNION
SELECT 'Ordered from four categories' AS "Step: number of categories",
Count(*) AS Customers,
4 as step
FROM cat_four
ORDER BY step