漏斗图

使用漏斗图来展示步骤的进展。

One of the funnel charts we

漏斗图可以可视化一个指标如何在一系列步骤中分布。通常它们被用来展示有多少人通过了特定的序列，例如网站上的结账流程。第一步（或入口）是访问你网站的人数。然后是查看产品页面的人数（步骤2），将商品添加到购物车的人数（步骤3），依此类推。

我们将通过使用与你的安装一起提供的样本数据库，向您展示如何在Metabase中构建漏斗图，以便您可以跟随操作。我们将同时在查询构建器和SQL编辑器中展示示例。

查询构建器
SQL编辑器

样本数据库不包含事件；它只包含四个订单、产品和客户信息的表。因此，我们在这里需要有点创意，以便为漏斗图提供示例。

使用查询构建器的漏斗图示例

这是一个假设的示例。我们将假装漏斗中的步骤是产品类别（因为我们的样本数据库中没有类似状态或页面或其他进度的内容）。以下是查询的笔记本视图。

The notebook view of the query for our funnel chart.

我们所做的是将Orders和Products表连接起来（见Metabase中的连接），汇总订单数量，并按产品类别对这些数量进行分组。然后我们按计数降序排列结果。要获取漏斗图，我们在左下角的可视化上点击，并选择漏斗。在漏斗图的设置中，在数据选项卡下，您可以设置步骤（在这个例子中我们使用产品类别）和指标（订单计数）。

A (rather chubby) funnel chart that uses categories as steps in the funnel.

注意，在设置 -> 显示选项卡中，您可以更改漏斗类型为“柱状图”，这是表示数据的另一种有效方式。漏斗图（除了视觉隐喻之外）的优势是，Metabase还会显示每个步骤中指标的百分比。

使用自定义列保持步骤排序

如果每个步骤的计数没有自然递减，您可能需要手动排序步骤以保持实际的步骤进展。例如，如果您在连续步骤中有相同的计数，步骤可能会在漏斗图中交换，就像Metabase默认按字母顺序排序以打破平局一样。同样，如果您在某个步骤的计数可以增加（例如，新人在漏斗中间进入）的情况下，漏斗将默认按降序计数，这将打乱您的步骤顺序。

在这些情况下，您可以创建一个额外的列来编号步骤，并按步骤排序以强制执行正确的漏斗顺序。以上述查询为例，我们可以通过添加另一个列，step，然后按step排序来修改它以保留顺序。

Creating a custom column using a custom expression to specify the order of steps in the funnel.

这是自定义表达式

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)

基本上，我们是在说Widgets是漏斗的第1步，Gadgets是第2步，依此类推。

使用SQL的漏斗图示例

使用样本数据库的另一个假设示例：假设我们了解到，终身价值最高的客户是那些在我们所有四个产品类别（Doohickeys、Gadgets、Gizmos和Widgets）下下订单的客户。所以对于这个例子，我们想看看我们的客户是如何根据他们订购的不同产品类别来分布的。

在这里需要明确的一个关键区别是，我们并不是试图查看客户的分布情况，也就是说，我们并不是试图查看有多少客户只从某一产品类别下单，有多少客户从两个类别下单，依此类推。我们将把所有对任何类别下单的客户作为第一步。对于下一步，我们将筛选出那些至少在两个产品类别下单的客户，然后是三个类别，接着是四个类别。

假设我们有一个由一百位下单客户组成的客户池。表格可能看起来像这样

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

我们的攻击计划：我们将使用公共表表达式将这个查询拆分为四个子查询，每个查询进一步细化我们的结果。然后我们将所有结果使用UNION合并到单个结果表中。

我们将首先获取所有向我们下单的客户，并将这个查询放入一个CTE中以构建另一个子查询。在下面的代码块中，我们将第一个子查询称为starting_data。为了获取第一个“步骤”的客户，我们将创建一个新的子查询，称为cat_one，它将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)

对于接下来的两个步骤，我们将执行同样的操作（即，基于前面的结果逐步构建）

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)

因此，现在我们已经有了四个结果：cat_one、cat_two、cat_three、cat_four，我们需要将这些结果合并到单个结果表中。我们将使用UNION来合并结果。

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

漏斗图查询

以下是完整的查询

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

应该会产生以下结果

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

现在我们只需要点击左下角的可视化并选择漏斗图。

如果您打开设置选项卡，您可以更改步骤或度量。在显示选项卡中，您可以更改图表从漏斗图变为柱状图（尽管如上所述，您将失去视觉隐喻以及与第一步的度量百分比）。

The Funnel options Data tab lets you set the Step and Measure.

在SQL中保持步骤排序

与上面的查询构建器一样，为了强制执行步骤顺序，您可以添加一个额外的列（我们将称之为“步骤”

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

自助分析

嵌入式分析

文档

学习

Metabase 基础

提升你的数据技能

漏斗图