SQL コホート維持分析

Retention は、定義されたアクターが開始マイルストーン後に戻るかどうかを尋ねます。有用な保持クエリは、アクター、コホート イベント、適格なリターン アクティビティ、間隔、および観察ウィンドウを示します。 「毎週の保持」は、これら 5 つの選択肢が明確になるまではあいまいです。

コホート表とアクティビティ表を個別に作成する

WITH cohorts AS (
  SELECT
    account_id,
    date_trunc('week', MIN(timestamp_utc)) AS cohort_week
  FROM product_events
  WHERE event_name = 'activation_completed'
  GROUP BY account_id
),
activity AS (
  SELECT DISTINCT
    account_id,
    date_trunc('week', timestamp_utc) AS activity_week
  FROM product_events
  WHERE event_name IN ('query_completed', 'dashboard_viewed')
)
SELECT
  cohort_week,
  activity_week,
  COUNT(DISTINCT cohorts.account_id) AS retained_accounts
FROM cohorts
JOIN activity
  ON cohorts.account_id = activity.account_id
 AND activity.activity_week >= cohorts.cohort_week
GROUP BY cohort_week, activity_week
ORDER BY cohort_week, activity_week;

この結果により、コホートと活動週が保存されます。レポート レイヤーは、選択した SQL エンジンでサポートされている日付演算を確認した後、ギャップを第 0 週、第 1 週、およびそれ以降の期間に変換できます。中間の日付を表示しておくと、境界の間違いを見つけやすくなります。

分母を一度定義します

分母はコホート内の適格な俳優の数であり、その後のアクティビティ イベントを偶然生成した数ではありません。コホート サイズを個別に計算し、保持数に結合します。パーセンテージを保護する NULLIF、両方のカウントを出力に保存します。

最近のコホートには、より遅い間隔に到達する時間がありませんでした。それらのセルを保持ゼロとして扱うのではなく、不完全としてマークします。また、週 0 がアクティベーション自体を意味するのか、それともアクティベーション後の個別の復帰を意味するのかも決定します。

バックグラウンドのハートビートではなく、永続的な値を表すイベントを使用します。テスト アカウントと、制御されたフィールドを含む自動アクティビティを除外します。小さなフィクスチャを使用して、ID のマージ、イベントの重複、遅延到着、タイムゾーンの境界を検証します。

読む ファネル分析 マイルストーンの進歩のために、 時間バケット化 間隔ルールの場合、 DataFusion SQL リファレンス 別の SQL 方言からの日付計算を適応させる前に。