Menu Close Log in Get started

Lesson

Funnel charts

Use funnel charts to show progression through steps.

<em>Fig. 1</em>. One of the funnel charts we'll build with the Sample Dataset.
Fig. 1. One of the funnel charts we'll build with the Sample Dataset.

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 Dataset included with your installation so you can follow along. We’ll show examples both in the query builder and the sql editor:

The Sample Dataset 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 Dataset). Here’s the notebook view of our query:

<em>Fig. 2</em>. The notebook view of the query for our funnel chart.
Fig. 2. The notebook view of the query for our funnel chart.

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).

<em>Fig. 3</em>. A (rather chubby) funnel chart that uses categories as steps in the funnel.
Fig. 3. A (rather chubby) funnel chart that uses categories as steps in the funnel.

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.

<em>Fig. 4</em>. Creating a custom column using a custom expression to specify the order of steps in the funnel.
Fig. 4. Creating a custom column using a custom expression to specify the order of steps in the funnel.

Here’s the custom expression used in figure 4:

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 Dataset: 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).

<em>Fig. 5</em>. The Funnel options <strong>Data</strong> tab lets you set the <strong>Step</strong> and <strong>Measure</strong>.
Fig. 5. The Funnel options Data tab lets you set the Step and Measure.

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

Thanks for your feedback!

Get articles like this one in your inbox every month