BigQueryにて、イベントA -> イベントB -> event_params.value.string_value
の順にフローを踏んだユーザー数の集計を行いたい
こちらの記事を参考にイベントA -> イベントB -> イベントC のようなファネル分析は集計できたのですが、イベントA -> イベントB -> event_params.value.string_value のようにevent_param値を含めたクエリの書き方が分かりません。
↓イベントA -> イベントB -> イベントC の集計クエリ
sql
1#standardSQL 2 3-- FIrebaseAnalyticsのサンプルデータセットで、①初回起動 --> ②no_more_extra_steps --> ③spend_virtual_currencyのクローズドファネルを計測 4 5WITH 6data AS ( 7 SELECT 8 user_pseudo_id, 9 event_timestamp AS timestamp, 10 -- ファネルステップ1のイベント名を入れる 11 (CASE event_name WHEN "first_open" THEN event_timestamp END) AS step_0_timestamp, 12 -- ファネルステップ2のイベント名を入れる 13 (CASE event_name WHEN "no_more_extra_steps" THEN event_timestamp END) AS step_1_timestamp, 14 -- ファネルステップ3のイベント名を入れる(※ここでevent_params.value.string_value のようにevent_param値のtimestampを取りたい) 15 (CASE event_name WHEN "spend_virtual_currency" THEN event_timestamp END) AS step_2_timestamp 16 FROM 17 -- 例では、Firebaseのサンプルデータセットを使用していますが、お使いの環境に合わせて変更します。 18 -- 年次のファネルを作っていますが、読み込む日付範囲を変更する事で、ファネルのタイムウィンドウを絞り込めます 19 `firebase-public-project.analytics_153293282.events_2018*`, 20 UNNEST(event_params) as params 21), 22 23funnel AS ( 24 SELECT 25 user_pseudo_id, 26 timestamp, 27 LAST_VALUE(step_0_timestamp IGNORE NULLS) OVER(PARTITION BY user_pseudo_id ORDER BY timestamp) AS step_0_funnel, 28 LAST_VALUE(step_1_timestamp IGNORE NULLS) OVER(PARTITION BY user_pseudo_id ORDER BY timestamp) AS step_1_funnel, 29 LAST_VALUE(step_2_timestamp IGNORE NULLS) OVER(PARTITION BY user_pseudo_id ORDER BY timestamp) AS step_2_funnel 30 FROM data 31) 32 33SELECT 34 1 as step_num, 35 COUNT( 36 DISTINCT CASE 37 WHEN step_0_funnel IS NOT NULL 38 THEN step_0_funnel END 39 ) AS count 40 FROM funnel 41UNION ALL SELECT 42 2 as step_num, 43 COUNT( 44 DISTINCT CASE 45 WHEN step_0_funnel IS NOT NULL 46 AND step_1_funnel IS NOT NULL AND step_0_funnel < step_1_funnel 47 THEN step_0_funnel END 48 ) AS count 49 FROM funnel 50UNION ALL SELECT 51 3 as step_num, 52 COUNT( 53 DISTINCT CASE 54 WHEN step_0_funnel IS NOT NULL 55 AND step_1_funnel IS NOT NULL AND step_0_funnel < step_1_funnel 56 AND step_2_funnel IS NOT NULL AND step_1_funnel < step_2_funnel 57 THEN step_0_funnel END 58 ) AS count 59 FROM funnel 60ORDER BY step_num 61;
一応以下の記述でevent_param値の件数自体は出せたのですが、上記のファネル集計のクエリにどう組み込めばよいでしょうか。
または違うアプローチで集計できる方法があれば教えていただけないでしょうか。
sql
1WITH 2data AS ( 3 SELECT 4 (SELECT value.string_value FROM UNNEST(event_params) AS x WHERE x.key = "[event_params.key名]") AS content 5 FROM 6 `[表名]` 7 WHERE 8 event_name = '[イベント名]' 9) 10SELECT * FROM data 11WHERE content = '[event_params.value.string_value名]'
あなたの回答
tips
プレビュー