BigQuery で Unnested な View をつくる

Google Analytics → BigQuery に保存しているデータを分析しやすくする。

View を作る主なメリット

1. テーブル分割を意識せずに使える
   • events_* は日付ごとに分かれたテーブルなので、毎回 TABLESUFFIX を意識してクエリを書くのが面倒
   • View にまとめれば、常に SELECT * FROM events_view の形で使える
2. パラメータ名の UNNEST() を定型化して隠せる
   • event_params や user_properties は UNNEST() が必要で面倒
   • View 側ですでに UNNEST しておけば、使う側は JOIN や抽出に集中できる
3. JOIN を定型化できる（UU, セッション単位など）
   • たとえば、ga_session_id の抽出処理や JOIN を View に入れておけば、誰でも簡単にセッション単位の集計ができる
4. アクセス制御ができる
   • 本体の events_* テーブルに直接アクセスされるのを避け、
   • View にだけアクセス権を付けることで、分析用 or 一般社員用のデータ制御ができる
5. Looker Studio や BIツールとの接続が安定する
   • Looker Studio などでは _TABLE_SUFFIX を使ったクエリが設定しにくい
   • View にしておくと、毎日構造が変わることなく、ダッシュボードが安定稼働
6. 変数化・再利用しやすい
   • たとえば events_last_7days、events_this_month などの View を用途別に用意すれば、毎回 WHERE 条件を変えなくて済む

"users_unnested_13months" View

まず event ではなく pseudonymous_users_* の情報を UNNEST した View を作ってみる。

• 直近13か月（＝過去400日）を対象
• 全フィールドを含む（ネスト型含む）
• user_properties / audiences を UNNEST
  • user_properties は特定のユーザーに対して、カスタム属性（キーと値のセット）を付ける機能
    • 例：会員ランク、性別、年齢層、プランタイプなど
    • Webサイトやアプリから gtag() や Firebase SDK で送信できる
  • audiences は GA4 の「オーディエンス」機能で作成した、ユーザーの分類グループ
    • 条件に一致したユーザーが、自動的にそのグループに入る
• 1ユーザーにつき複数行入る可能性ある
  • user_properties x audiences がある場合だが、現時点での利用では想定できない
• 最初に ChatGPT が出してきたフィールドはヌケモレあったので、実際の pseudonymous_users_* テーブルのスクリーンショット撮って添付して書き出してもらった
• それでも1ヵ所ミスあったので手で修正

SELECT
  -- 基本情報
  pseudo_user_id,
  stream_id,
  occurrence_date,
  last_updated_date,

  -- user_info
  user_info.last_active_timestamp_micros,
  user_info.user_first_touch_timestamp_micros,
  user_info.first_purchase_date,

  -- device
  device.operating_system,
  device.category AS device_category,
  device.mobile_brand_name,
  device.mobile_model_name,
  device.unified_screen_name,

  -- geo
  geo.city,
  geo.country,
  geo.continent,
  geo.region,

  -- user_ltv
  user_ltv.revenue_in_usd,
  user_ltv.sessions,
  user_ltv.engagement_time_millis,
  user_ltv.purchases,
  user_ltv.engaged_sessions,
  user_ltv.session_duration_micros,

  -- predictions（存在する場合）
  predictions.in_app_purchase_score_7d,
  predictions.purchase_score_7d,
  predictions.churn_score_7d,
  predictions.revenue_28d_in_usd,

  -- privacy_info
  privacy_info.is_limited_ad_tracking,
  privacy_info.is_ads_personalization_allowed,

  -- audiences（UNNEST）
  aud.id AS audience_id,
  aud.name AS audience_name,
  aud.membership_start_timestamp_micros,
  aud.membership_expiry_timestamp_micros,
  aud.npa AS audience_npa,

  -- user_properties（UNNEST）
  up.key AS user_property_key,
  up.value.string_value AS user_property_value_string,
  up.value.set_timestamp_micros AS user_property_set_timestamp_micros,
  up.value.user_property_name AS user_property_name

FROM
  `your_project.your_dataset.pseudonymous_users_*`
  LEFT JOIN UNNEST(user_properties) AS up
  LEFT JOIN UNNEST(audiences) AS aud
WHERE
  _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 400 DAY))
                    AND FORMAT_DATE('%Y%m%d', CURRENT_DATE());

結果

クエリ叩いて、結果を「ビューとして保存」をしたらできた。