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;
每個階段保留每個帳戶的一列資料，並選取在前一個有效階段同時或之後發生的首個里程碑。缺少或順序錯誤的里程碑會阻止所有後續階段；之後有效的重試仍可讓帳戶繼續前進。重複事件只計一次，允許相同的時間戳記。如果要求在固定期限內完成，請加入最長轉換期間。
保持佇列和觀察視窗不同
如果查詢包含昨天的註冊，則這些帳戶的啟用時間比月初的帳戶要少。要麼為每個佇列提供完整的觀察視窗，要麼將最近的佇列標記為不完整。將所有事件過濾到同一日曆範圍可能會無意中切斷有效的後續里程碑。
選擇用於帳戶級啟用的帳戶識別符號和用於個人級行為的使用者識別符號。不要在步驟之間切換身分。定義合併帳戶、匿名會話、重新開啟帳戶和重複完成的工作方式。
始終在百分比旁邊顯示步數。轉化變化可能來自分子、分母、流量組合或檢測。經過測試的 註冊漏斗配方 和 轉化率指南 提供了一個完整的起點。當問題是啟用後持續行為時，請使用 群組保留。