漏斗分析计算，如何计算漏斗？

Question

假设我要在网站上追踪用户的“事件”，这些事件可以是以下内容之一：

1. 浏览首页
2. 将商品加入购物车
3. 结算
4. 支付订单

现在，每个事件都存储在数据库中，如下所示：

session_id event_name created_date ..

现在我想构建一个报告，显示我定义的特定漏斗，例如：

Step#1   event_n
Step#2   event_n2
Step#3   event_n3

所以，这个特定的漏斗有3个步骤，每个步骤都与任何事件相关。

现在，我如何根据上述数据构建报告呢？

请注意：我只想清楚地表明，我想能够创建我定义的任何漏斗，并且能够为其创建报告。

我能想到的最基本的方法是：

1. 获取数据库中每个步骤的所有事件 2. 步骤#1将是x％的人执行了事件_n 3. 现在，我将必须查询执行了步骤#1的人也执行了步骤#2的数据，并显示％ 4. 对于步骤#3，同样是条件为步骤#2

我好奇这些在线服务如何在托管的Saas环境中显示这些类型的报告。是否使用Map-Reduce可以使此过程更加容易？

- Blankman

---

如果您认为这样更容易，您可以在reducer中获取每个session ID的所有事件。 - Thomas Jungblut

Answer 1

首先，根据您的假设，使用标准SQL查询语句得出答案：

假设有一个名为EVENTS的表格，其布局简单：

EVENTS
-----------------------------
SESION_ID , EVENT_NAME , TMST

获取在某个时间执行步骤#1的会话：

-- QUERY 1
SELECT SESSION_ID,MIN(TMST) FROM EVENTS WHERE EVENT_NAME='event1' GROUP BY SESSION_ID;

在这里，我假设事件1可以在一个会话中发生多次。结果是列出了一些在某个时间展示了事件1的唯一会话列表。

为了获得步骤2和步骤3，我只需要做同样的事情：

-- QUERY 2
SELECT SESSION_ID,MIN(TMST) FROM EVENTS WHERE EVENT_NAME='event2' GROUP BY SESSION_ID;
-- QUERY 3
SELECT SESSION_ID,MIN(TMST) FROM EVENTS WHERE EVENT_NAME='event3' GROUP BY SESSION_ID;

现在，你想选择按顺序执行了step1、step2和step3的会话。 更准确地说，您需要计算执行了step 1的会话数量，然后计算执行了step2的会话数量，最后计算执行step3的会话数量。 基本上，我们只需要将上述三个查询与左连接组合起来，以列出进入漏斗的会话以及它们执行的步骤：

-- FUNNEL FOR S1/S2/S3
SELECT 
  SESSION_ID, 
  Q1.TMST IS NOT NULL AS PERFORMED_STEP1,
  Q2.TMST IS NOT NULL AS PERFORMED_STEP2,
  Q3.TMST IS NOT NULL AS PERFORMED_STEP3
FROM
  -- QUERY 1
  (SELECT SESSION_ID,MIN(TMST) FROM EVENTS WHERE EVENT_NAME='event1' GROUP BY SESSION_ID) AS Q1,
LEFT JOIN
  -- QUERY 2
  (SELECT SESSION_ID,MIN(TMST) FROM EVENTS WHERE EVENT_NAME='event2' GROUP BY SESSION_ID) AS Q2,
LEFT JOIN
  -- QUERY 3
  (SELECT SESSION_ID,MIN(TMST) FROM EVENTS WHERE EVENT_NAME='event2' GROUP BY SESSION_ID) AS Q3
-- Q2 & Q3
ON Q2.SESSION_ID=Q3.SESSION_ID AND Q2.TMST<Q3.TMST
-- Q1 & Q2
ON Q1.SESSION_ID=Q2.SESSION_ID AND Q1.TMST<Q2.TMST

结果是在第一步进入漏斗的独特会话列表，可能已经继续到第二步和第三步... 例如：

SESSION_ID_1,TRUE,TRUE,TRUE
SESSION_ID_2,TRUE,TRUE,FALSE
SESSION_ID_3,TRUE,FALSE,FALSE
...

现在我们只需要计算一些统计数据，例如：

SELECT
  STEP1_COUNT,
  STEP1_COUNT-STEP2_COUNT AS EXIT_AFTER_STEP1,
  STEP2_COUNT*100.0/STEP1_COUNT AS PERCENTAGE_TO_STEP2,
  STEP2_COUNT-STEP3_COUNT AS EXIT_AFTER_STEP2,
  STEP3_COUNT*100.0/STEP2_COUNT AS PERCENTAGE_TO_STEP3,
  STEP3_COUNT*100.0/STEP1_COUNT AS COMPLETION_RATE
FROM
(-- QUERY TO COUNT session at each step
  SELECT
    SUM(CASE WHEN PERFORMED_STEP1 THEN 1 ELSE 0 END) AS STEP1_COUNT,
    SUM(CASE WHEN PERFORMED_STEP2 THEN 1 ELSE 0 END) AS STEP2_COUNT,
    SUM(CASE WHEN PERFORMED_STEP3 THEN 1 ELSE 0 END) AS STEP3_COUNT
  FROM
    [... insert the funnel query here ...]
) AS COMPUTE_STEPS

Et voilà！

现在开始讨论。首先，如果你采用“集合”（或者函数）思考方式而不是“过程式”方法，那么结果就非常简单明了。不要将数据库视为一组固定的有列和行的表格...虽然它是这样实现的，但这并不是你与之交互的方式。它都是集合，你可以按照需要排列集合。

其次，如果你使用MPP数据库，那么查询将自动优化以并行运行。你甚至不需要以不同方式编写查询，使用map-reduce或其他方式...我在测试数据集上运行了相同的查询，其中包含超过1亿个事件，并在几秒钟内获得了结果。

最后但并非最不重要的是，查询打开了无限的可能性。只需按引荐人、关键字、着陆页面、用户信息等对结果进行分组，并分析哪些提供了最佳转化率即可！

- SergeFantino

---

我认为这个查询可能存在问题，例如如果我想知道谁做了：E1、E2、E3，而我有一个包含 E2、E1、E2、E3 的会话，我相信上述查询将失败（因为它只考虑事件的第一次出现）。 - shaylevi2

实际上不是这样的。初始查询中唯一的限制是时间上的E1<E2<E3。因此X、E1、E2、E3将匹配，但也可以是*、E1、*、E2、*、E3... 可以强制执行事件E1=>E2=>E3的严格顺序，但这需要首先在会话中计算事件（例如可以使用窗口函数轻松完成）。 - SergeFantino

Answer 2

你思考这个问题的核心问题在于，你正在按照SQL/表格类型模型进行思考。每个事件都是一个记录。NoSQL技术（你可能对此有所了解）的好处之一是，你可以自然地将记录存储为每个会话一条记录。一旦按会话方式存储数据，你就可以编写一个例程来检查该会话是否符合模式。不需要执行连接或其他任何操作，只需在会话中的交易列表上循环即可。半结构化数据的强大之处就在于此。

如果你将会话存储在一起呢？那么，你只需要遍历每个会话并查看它是否匹配即可。

在我看来，这是HBase的绝佳用例。

使用HBase，你将会话ID作为行键存储，然后将每个事件作为值与时间戳作为列限定符。这样留下的数据是按会话ID分组，然后按时间排序的数据。

现在，你想要确定有多少会话执行了行为1，然后是2，再是3。你可以在这些数据上运行MapReduce作业。MapReduce作业将为你提供一个会话每个行键/值对。在数据上编写一个循环以检查它是否匹配该模式。如果是，则计数+1；否则，不计数。

没有必要使用HBase，你可以使用MapReduce对静态数据进行会话化。按会话ID进行分组，然后在Reducer中将与该会话相关的所有事件分组在一起。现在，你基本上和使用HBase时一样了，可以在Reducer中编写一个方法来检查模式。

如果你没有大量数据，那么使用HBase可能有些过度。任何可以按层次结构存储数据的数据库都适用于这种情况。MongoDB、Cassandra、Redis都是不错的选择，它们各自有优点和缺点。

- Donald Miner

---

不确定我是否表达清楚，但我的意思是希望用户能够定义漏斗（每个步骤和匹配事件），并能够查看历史数据的报告。我想这意味着我必须运行批处理作业将旧数据提取到给定的数据存储/结构中，对吗？这方面没有什么神奇的方法，对吗？ - Blankman

我刚刚在阅读有关HBase的内容，我喜欢它可以将相关数据以这样一种分组的方式存储等。但是，如果我想在MongoDB中做类似的事情，该怎么办呢？（对我来说，HBase可能有点太高级了） - Blankman

是的，您需要编写某种进程将它们组合在一起，或者如果您正在使用存储此类数据的数据存储，则可以修改记录。如果您正在处理用户而不是会话，则只需按用户分组而不是会话ID。 - Donald Miner

对于MongoDB，您可以将列表存储为json非常容易。我想每个用户都将是一条记录，并拥有一系列操作。不过，我对MongoDB并不是专家。 - Donald Miner

Answer 3

我最近发布了一个开源的Hive UDF来完成这个任务: hive-funnel-udf

对于这种漏斗分析任务来说，它非常容易使用，你只需要编写Hive查询语句，无需编写自定义的Java MapReduce代码。

但是请注意，这仅适用于使用Hive/Hadoop存储和查询数据的情况。

- Josh Walters

---

请不要发布重复答案。相反，考虑其他可以帮助未来用户找到他们所需答案的行动，如链接帖子中所述。 - Mogsdad