-- セッション単位で見たい項目を抽出
SELECT
  -- 1. ユーザーとセッションを一意に特定
  user_pseudo_id,
  (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS session_id,
  CONCAT(user_pseudo_id, '-', CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS STRING)) AS unique_session_id,

  -- 2. 時間情報を人間が読める形式に (JST変換)
  event_date,
  DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Tokyo') AS event_datetime,

  event_name,

  -- 3. 地理情報
  geo.region,
  geo.city,

  -- 4. ページ情報（NULL対応済み）
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_location,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_referrer') AS page_referrer,

  -- 5. セッション単位の流入元（event_paramsから取得推奨）
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'source') AS session_source,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'medium') AS session_medium

FROM
  `your-project.analytics_dataset.events_*`
WHERE
  -- コスト爆発を防ぐためパーティション指定は必須
  _TABLE_SUFFIX BETWEEN '20240101' AND '20251221';

-- ページ別のPV集計例
SELECT
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_location,
  COUNTIF(event_name = 'page_view') AS pageviews
FROM
  `your-project.analytics_dataset.events_*`
WHERE
  _TABLE_SUFFIX BETWEEN '20240101' AND '20251221'
GROUP BY
  page_location