【BigQuery】ファネル分析用のクエリ

やったこと

・BigQueryでファネル分析のためのクエリを書いた
・SQLだけで順序を取り扱うのが → だったのでUDF（ユーザー定義関数）を使った
・ファネルの層が増えて（減って）も（そこそこ）簡単に拡張できるように努力した

ファネル分析とは

こんなふうに、STEP1 → STEP2 → STEP3 → STEP4 と順番にステップを踏んだ人の数を集計して、各ステップの成績（達成人数）を可視化して改善につなげる分析手法

前提条件

・以下のようなレコードが与えられる（user_idはユーザーごとのユニークなID。createdはSTEPを達成した時刻）

user_id	event_id	created
1	STEP1	2022-04-12 00:00:00
1	STEP3	2022-04-12 01:00:00
1	STEP2	2022-04-12 02:00:00
1	STEP4	2022-04-12 03:00:00

・STEPが順番に実行された場合のみ集計対象とする（上の場合だと、STEP1 → STEP2 → STEP3 → STEP4 の順番に実行されたのはSTEP1とSTEP2までなので、この2つのレコードだけを集計対象にしたい）

クエリ

では早速（サンプル用のデータも書いていますのでそのままコピペで動きます）

-- イベントIDの配列（時系列にソート済み）を受け取り、達成したSTEP数を返す関数
CREATE TEMP FUNCTION achievedSteps(userId INT64, userActions ARRAY<STRING>)
  RETURNS ARRAY<INT64>
  LANGUAGE js AS r"""
  const funnels = ["STEP1", "STEP2", "STEP3", "STEP4"];
  let ret = new Array(funnels.length);
  let idx = 0;
  for (const action of userActions){
    if (action === funnels[idx]){
      ret[idx] = userId;
      idx++;
    }    
  };
  return ret;
  """;

-- サンプルデータの作成
WITH samples AS (
  -- user_id: 1
  SELECT 1 user_id, "STEP1" event_id, "2022-04-12 00:00:00" created UNION ALL 
  SELECT 1 user_id, "STEP3" event_id, "2022-04-12 01:00:00" created UNION ALL 
  SELECT 1 user_id, "STEP2" event_id, "2022-04-12 02:00:00" created UNION ALL 
  SELECT 1 user_id, "STEP4" event_id, "2022-04-12 03:00:00" created UNION ALL

  -- user_id: 2
  SELECT 2 user_id, "STEP1" event_id, "2022-04-12 00:00:00" created UNION ALL 
  SELECT 2 user_id, "STEP2" event_id, "2022-04-12 01:00:00" created UNION ALL 
  SELECT 2 user_id, "STEP3" event_id, "2022-04-12 02:00:00" created UNION ALL 
  SELECT 2 user_id, "STEP4" event_id, "2022-04-12 03:00:00" created UNION ALL

  -- user_id: 3
  SELECT 3 user_id, "STEP4" event_id, "2022-04-12 00:00:00" created UNION ALL 
  SELECT 3 user_id, "STEP1" event_id, "2022-04-12 01:00:00" created UNION ALL 
  SELECT 3 user_id, "STEP2" event_id, "2022-04-12 02:00:00" created UNION ALL 
  SELECT 3 user_id, "STEP3" event_id, "2022-04-12 03:00:00" created
) 

-- -- ファネル用集計データの作成
SELECT 
  COUNT(DISTINCT achievedIds[SAFE_OFFSET(0)]) step1,
  COUNT(DISTINCT achievedIds[SAFE_OFFSET(1)]) step2,
  COUNT(DISTINCT achievedIds[SAFE_OFFSET(2)]) step3,
  COUNT(DISTINCT achievedIds[SAFE_OFFSET(3)]) step4,
FROM (
  SELECT 
    achievedSteps(user_id, ARRAY_AGG(event_id ORDER BY created)) achievedIds
  FROM 
    samples
  GROUP BY 
    user_id);

-- => {  "step1": "3",  "step2": "3",  "step3": "2",  "step4": "1"}
-- user_id: 1 はSTEP2まで達成
-- user_id: 2 はSTEP4まで達成
-- user_id: 3 はSTEP3まで達成　なので期待通りの結果が取得できた！！

感想

・UDF強い
・なんでもSQLでやろうとするとツライ

次回予告

「GA4のデータをBigQuery連携してデータポータルから見る」の巻？