SQL によるコンバージョン率の測定
全体的なコンバージョン率が結果を示します。マイルストーン イベントは、ユーザーがどこでファネルを離れ、どのセグメントが変化したかを示すことで説明します。
隣接ステップのコンバージョン率により、最大の減少がすぐにわかります。
このガイドでは、1 つのイベント テーブルと 1 つの SQL クエリでユーザーレベルのサインアップ ファネルを構築します。各段階で個別のユーザーをカウントすることで、1 人がアクションを繰り返すときにコンバージョンが膨らむことを回避します。
1. イベントを追加する前にファネルを定義する
失敗する可能性のあるボタンのクリックではなく、完了した結果を表すマイルストーンを使用します。
signup_completedworkspace_createdfirst_event_receiveddashboard_created
次のような安定したサブジェクト識別子を 1 つ選択します。 user_id または team_id。分母とその後のすべてのステージでは、同じ主題を使用する必要があります。
2. 一貫したイベント形状を記録する
npm install telemetry-sh
import telemetry from "telemetry-sh";
telemetry.init(process.env.TELEMETRY_API_KEY);
await telemetry.log("product_events", {
event_name: "signup_completed",
user_id: user.id,
team_id: team.id,
acquisition_channel: "docs",
plan: "free",
});
以降のマイルストーンを同じログに記録する product_events 同じ識別子とセグメンテーション フィールドを持つテーブル:
await telemetry.log("product_events", {
event_name: "first_event_received",
user_id: user.id,
team_id: team.id,
acquisition_channel: user.acquisitionChannel,
plan: team.plan,
});
Telemetry が追加 timestamp_utc 自動的に。安定した内部識別子で十分な場合は、電子メール、名前、Cookie、または生のリクエスト コンテンツのログ記録を避けてください。
3. 1 つのクエリでファネルを計算する
以下のクエリは、ユーザーごとに 1 行を割り当て、各段階が発生したかどうかを記録し、元のサインアップ コホートからカウントとコンバージョンを計算します。
WITH user_funnel AS (
SELECT
user_id,
MIN(CASE WHEN event_name = 'signup_completed' THEN timestamp_utc END)
AS signed_up_at,
MIN(CASE WHEN event_name = 'workspace_created' THEN timestamp_utc END)
AS workspace_created_at,
MIN(CASE WHEN event_name = 'first_event_received' THEN timestamp_utc END)
AS first_event_at,
MIN(CASE WHEN event_name = 'dashboard_created' THEN timestamp_utc END)
AS dashboard_created_at
FROM product_events
WHERE timestamp_utc >= now() - INTERVAL '30 days'
AND event_name IN (
'signup_completed',
'workspace_created',
'first_event_received',
'dashboard_created'
)
GROUP BY user_id
),
stage_counts AS (
SELECT 1 AS step, 'Signed up' AS stage, COUNT(*) AS users
FROM user_funnel
WHERE signed_up_at IS NOT NULL
UNION ALL
SELECT 2, 'Created workspace', COUNT(*)
FROM user_funnel
WHERE workspace_created_at >= signed_up_at
UNION ALL
SELECT 3, 'Sent first event', COUNT(*)
FROM user_funnel
WHERE first_event_at >= workspace_created_at
UNION ALL
SELECT 4, 'Created dashboard', COUNT(*)
FROM user_funnel
WHERE dashboard_created_at >= first_event_at
)
SELECT
step,
stage,
users,
ROUND(
100.0 * users /
NULLIF(MAX(CASE WHEN step = 1 THEN users END) OVER (), 0),
2
) AS conversion_from_signup_pct,
ROUND(
100.0 * users /
NULLIF(LAG(users) OVER (ORDER BY step), 0),
2
) AS conversion_from_previous_pct
FROM stage_counts
ORDER BY step;
最初のステージには前ステージのレートがないため、 conversion_from_previous_pct その行では null です。後の行ごとに、サインアップからの合計コンバージョンと隣接ステップ コンバージョンの両方が表示されます。
4. 定義を変更せずにセグメント化する
獲得チャネルまたは計画を比較するには、サインアップ イベントのフィールドを保存し、それを user_funnel、そのフィールドごとにステージ数をグループ化します。分母をユーザーからイベントに黙って切り替えないでください。
役立つセグメント チェックには次のものがあります。
- 取得チャネル。
- 初期の計画またはオファー。
- デバイスまたはアプリケーションの表面。
- 登録週間。
- 実験課題は結果の前に記録されます。
分割されていないファネルが信頼できるようになるまでは、カーディナリティの高いディメンションを避けてください。
5. 結果を視覚化して検証する
横棒グラフを使用すると、 users によって注文されました step、結果テーブルに両方のレート列を表示します。グラフを共有する前に、以下を確認してください。
- ユーザーは、前のタイムスタンプよりも前に後のステージを完了します。
- 識別子が欠落しているか再利用されている。
- 内部アカウント、テストアカウント、ボットアカウント。
- 遅れて到着したイベント。
- 選択したウィンドウより前にサインアップが行われたユーザー。
- 最新のコホートが変換するのに十分な時間があったかどうか。
ファネルはコホート測定であり、単に 2 つの独立したイベント数ではありません。コンバージョン期間を長くするには、サインアップ日によってユーザーをコーホートし、一定の観察期間を設けます。
次のステップ
完成品を開く サインアップ アクティベーション ファネル SQL レシピ 結果と視覚化の例については、使用する 毎週のコホート維持率 アクティベーションが定義されたら、比較します アップグレード前の機能導入.