SQL ファネル分析
ファネルは、定義された母集団がマイルストーンを通じてどのように進歩するかを測定します。 SQL は、誰が入力するか、ステップを順番に実行する必要があるかどうか、ユーザーが進行する必要がある時間、どの ID が個人またはアカウントを表すか、再試行と重複イベントの動作など、製品定義が正確になって初めて分かりやすくなります。
イベントをアクターごとに 1 行に減らす
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;
各段階はアカウントごとに1行を保持し、前の有効な段階と同時刻以降に発生した最初のマイルストーンを選択します。欠落した、または順序が不正なマイルストーンは後続の全段階をブロックしますが、後の有効な再試行で進めます。重複イベントは1回だけ数え、同じタイムスタンプは許容します。一定期間内の進行が必要なら、コンバージョン期間の上限を追加してください。
コホートと観察ウィンドウを区別する
クエリに昨日のサインアップが含まれている場合、それらのアカウントは月初めのアカウントよりもアクティブ化までの時間が短くなります。すべてのコホートに完全な観察ウィンドウを許可するか、最近のコホートを不完全としてラベル付けします。すべてのイベントを同じカレンダー範囲にフィルターすると、その後の有効なマイルストーンが意図せずに切り取られる可能性があります。
アカウントレベルのアクティベーションにはアカウント識別子を選択し、個人レベルの動作にはユーザー識別子を選択します。ステップ間で ID を切り替えないでください。マージされたアカウント、匿名セッション、再オープンされたアカウント、および反復完了がどのように機能するかを定義します。
パーセンテージの横に常に歩数を表示します。コンバージョンの変化は、分子、分母、トラフィックの組み合わせ、または計測によって発生する可能性があります。テストされた サインアップファネルのレシピ そして 換算率ガイド 完全な出発点を提供します。使用する コホート維持率 アクティベーション後の継続行動を調べる場合.