SQL 漏斗分析
漏斗衡量特定人群如何在里程碑中取得进展。只有在产品定义准确之后，SQL 才会变得简单：谁进入、步骤是否必须按顺序发生、用户必须进行多长时间、哪个身份代表个人或帐户，以及重试和重复事件的行为方式。
将事件减少到每个参与者一行
WITH events AS (
SELECT account_id, event_name, timestamp_utc
FROM product_events
WHERE timestamp_utc >= now() - INTERVAL '30 days'
),
signups AS (
SELECT account_id, MIN(timestamp_utc) AS signed_up_at
FROM events WHERE event_name = 'signup_completed'
GROUP BY account_id
),
workspaces AS (
SELECT s.account_id, s.signed_up_at,
MIN(e.timestamp_utc) AS workspace_created_at
FROM signups AS s
LEFT JOIN events AS e ON e.account_id = s.account_id
AND e.event_name = 'workspace_created'
AND e.timestamp_utc >= s.signed_up_at
GROUP BY s.account_id, s.signed_up_at
),
account_steps AS (
SELECT w.account_id, w.signed_up_at, w.workspace_created_at,
MIN(e.timestamp_utc) AS first_event_sent_at
FROM workspaces AS w
LEFT JOIN events AS e ON e.account_id = w.account_id
AND e.event_name = 'first_event_sent'
AND e.timestamp_utc >= w.workspace_created_at
GROUP BY w.account_id, w.signed_up_at, w.workspace_created_at
)
SELECT
COUNT(*) AS accounts_seen,
COUNT(signed_up_at) AS signed_up,
COUNT(workspace_created_at) AS created_workspace,
COUNT(first_event_sent_at) AS sent_first_event
FROM account_steps;
每个阶段保留每个账户的一行数据，并选择在前一个有效阶段同时或之后发生的首个里程碑。缺失或顺序错误的里程碑会阻止所有后续阶段；之后有效的重试仍可使账户继续前进。重复事件只计一次，允许相同的时间戳。如果要求在固定期限内完成，请添加最长转化窗口。
保持队列和观察窗口不同
如果查询包含昨天的注册，则这些帐户的激活时间比月初的帐户要少。要么为每个队列提供完整的观察窗口，要么将最近的队列标记为不完整。将所有事件过滤到同一日历范围可能会无意中切断有效的后续里程碑。
选择用于帐户级激活的帐户标识符和用于个人级行为的用户标识符。不要在步骤之间切换身份。定义合并帐户、匿名会话、重新打开帐户和重复完成的工作方式。
始终在百分比旁边显示步数。转化变化可能来自分子、分母、流量组合或检测。经过测试的 注册漏斗配方 和 转化率指南 提供了一个完整的起点。当问题是激活后持续行为时，请使用 群组保留。