質問編集履歴

1 1つ目のクエリに要望の追記

ichaemon

ichaemon score 10

2019/09/26 18:58  投稿

BigQueryでクローズドファネル分析をするクエリの記述方法が知りたい
BigQueryにて、**イベントA -> イベントB -> event_params.value.string_value**
の順にフローを踏んだユーザー数の集計を行いたい
[こちらの記事](https://qiita.com/osapiii/items/4c4ba5f55e0c313713ba)を参考にイベントA -> イベントB -> イベントC のようなファネル分析は集計できたのですが、**イベントA -> イベントB -> event_params.value.string_value** のようにevent_param値を含めたクエリの書き方が分かりません。
↓イベントA -> イベントB -> イベントC の集計クエリ
```sql
#standardSQL
-- FIrebaseAnalyticsのサンプルデータセットで、①初回起動 --> ②no_more_extra_steps --> ③spend_virtual_currencyのクローズドファネルを計測
WITH
data AS (
 SELECT
   user_pseudo_id,
   event_timestamp AS timestamp,
   -- ファネルステップ1のイベント名を入れる
   (CASE event_name WHEN "first_open" THEN event_timestamp END) AS step_0_timestamp,
   -- ファネルステップ2のイベント名を入れる
   (CASE event_name WHEN "no_more_extra_steps" THEN event_timestamp END) AS step_1_timestamp,
   -- ファネルステップ3のイベント名を入れる
   -- ファネルステップ3のイベント名を入れる(※ここでevent_params.value.string_value のようにevent_param値のtimestampを取りたい)
   (CASE event_name WHEN "spend_virtual_currency" THEN event_timestamp END) AS step_2_timestamp
 FROM
   -- 例では、Firebaseのサンプルデータセットを使用していますが、お使いの環境に合わせて変更します。
   -- 年次のファネルを作っていますが、読み込む日付範囲を変更する事で、ファネルのタイムウィンドウを絞り込めます
   `firebase-public-project.analytics_153293282.events_2018*`,
   UNNEST(event_params) as params
),
funnel AS (
 SELECT
   user_pseudo_id,
   timestamp,
   LAST_VALUE(step_0_timestamp IGNORE NULLS) OVER(PARTITION BY user_pseudo_id ORDER BY timestamp) AS step_0_funnel,
   LAST_VALUE(step_1_timestamp IGNORE NULLS) OVER(PARTITION BY user_pseudo_id ORDER BY timestamp) AS step_1_funnel,
   LAST_VALUE(step_2_timestamp IGNORE NULLS) OVER(PARTITION BY user_pseudo_id ORDER BY timestamp) AS step_2_funnel
 FROM data
)
SELECT
 1 as step_num,
 COUNT(
   DISTINCT CASE
   WHEN step_0_funnel IS NOT NULL
   THEN step_0_funnel END
 ) AS count
 FROM funnel
UNION ALL SELECT
 2 as step_num,
 COUNT(
   DISTINCT CASE
   WHEN step_0_funnel IS NOT NULL
     AND step_1_funnel IS NOT NULL AND step_0_funnel < step_1_funnel
   THEN step_0_funnel END
 ) AS count
 FROM funnel
UNION ALL SELECT
 3 as step_num,
 COUNT(
   DISTINCT CASE
   WHEN step_0_funnel IS NOT NULL
     AND step_1_funnel IS NOT NULL AND step_0_funnel < step_1_funnel
     AND step_2_funnel IS NOT NULL AND step_1_funnel < step_2_funnel
   THEN step_0_funnel END
 ) AS count
 FROM funnel
ORDER BY step_num
;
```
一応以下の記述でevent_param値の件数自体は出せたのですが、上記のファネル集計のクエリにどう組み込めばよいでしょうか。
または違うアプローチで集計できる方法があれば教えていただけないでしょうか。
```sql
WITH
data AS (
 SELECT
   (SELECT value.string_value FROM UNNEST(event_params) AS x WHERE x.key = "[event_params.key名]") AS content
 FROM
   `[表名]`
 WHERE
   event_name = '[イベント名]'
)
SELECT * FROM data
WHERE content = '[event_params.value.string_value名]'
```
  • SQL

    4950 questions

    SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

  • BigQuery

    176 questions

    BigQueryは、Google Cloud Platformが提供しているビッグデータ解析サービス。数TB(テラバイト)またはPB(ペタバイト)の膨大なデータに対し、SQL風のクエリを実行し、高速で集計・分析を行うサービスです。

思考するエンジニアのためのQ&Aサイト「teratail」について詳しく知る