java - 漏斗分析计算，你会如何计算一个漏斗？

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% 的人执行了 event_n
3. 现在我将不得不查询同时执行了步骤#1 的步骤#2 的数据，并显示 %
4. 与#3 相同，但对于步骤#3 和步骤#2 的条件

我很好奇这些在线服务如何在托管的 Saas 环境中显示这些类型的报告。 map-reduce 是否以某种方式使这更容易？

Answer 1

首先，根据您的假设，使用标准 SQL 给出答案:有一个布局简单的表 EVENTS:

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

在某个时间获取执行步骤#1 的 session :

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

这里我假设 event1 在每个 session 中可以发生多次。结果是在某个时间演示 event1 的唯一 session 列表。

为了得到step2和step3，我也可以这样做:

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

现在，您要选择按顺序执行第 1 步、第 2 步和第 3 步的 session 。更准确地说，您需要计算执行步骤 1 的 session 数，然后计算执行步骤 2 的 session 数，然后计算执行步骤 3 的 session 数。基本上，我们只需要将上述 3 个查询与左连接结合起来，即可列出进入漏斗的 session 及其执行的步骤:

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

结果是在第 1 步进入漏斗的唯一 session 的列表，并且可能继续到第 2 步和第 3 步...例如:

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

瞧瞧!

现在开始讨论。首先，如果您采用“集合”(或功能)思维方式而不是“程序”方法，结果非常简单。不要将数据库想象成具有列和行的固定表的集合……这是它的实现方式，但这不是您与之交互的方式。都是套装，你可以按照你的需要来安排套装!

第二点，如果您使用的是 MPP 数据库，查询将自动优化以并行运行。您甚至不需要对查询进行不同的编程、使用 map-reduce 或其他任何方式...我在包含超过 1 亿个事件的测试数据集上运行相同的查询并在几秒钟内获得结果。

最后但同样重要的是，查询开启了无限的可能性。只需按引荐来源网址、关键字、着陆页、用户信息对结果进行分组，然后分析哪个提供最佳转化率!