GA4データをBigQueryにエクスポートしてSQLでユーザー行動を分析する方法【完全ガイド】｜実践SQLクエリ15選付き

【GA4 → BigQuery エクスポート設定手順】

1. Google Cloud プロジェクトを作成
   → console.cloud.google.com でプロジェクト作成
   → BigQuery API を有効化
   → 課金アカウントを設定（無料枠内でも設定が必要）

2. GA4 管理画面から設定
   → 管理 → プロパティ設定 → BigQueryのリンク設定
   → 「BigQueryプロジェクトに接続する」をクリック
   → 接続するGCPプロジェクトを選択

3. エクスポート設定
   → データロケーション: asia-northeast1（東京）を選択（推奨）
   → エクスポートの種類:
     「毎日のエクスポート」→ 前日のデータを毎朝エクスポート（無料）
     「ストリーミングエクスポート」→ リアルタイム（有料・要BigQuery Streaming費用）
   → エクスポートするイベント: 全て / 指定のみ（コスト削減目的）

4. 確認（翌日にデータが届く）
   BigQuery → your_project.analytics_XXXXXXXXX.events_YYYYMMDD
   （XXXXXXXXX はGA4のプロパティID、YYYYMMDDは日付）

-- テーブル名: analytics_XXXXXXXXX.events_YYYYMMDD
-- （XXXXXXXXX はGA4のプロパティID、YYYYMMDDは日付）

-- 主要カラム（よく使うもの）
event_date          -- 日付文字列（例: 20260314）
event_timestamp     -- マイクロ秒単位のUnixタイムスタンプ
event_name          -- イベント名（page_view, purchase, click等）
event_params        -- イベントパラメータ（REPEATED RECORD 型）

user_pseudo_id      -- 匿名のユーザーID（クッキーベース）
user_id             -- ログイン済みユーザーのID（設定した場合のみ）
user_properties     -- ユーザープロパティ（REPEATED RECORD 型）
user_first_touch_timestamp  -- 初回アクセスのタイムスタンプ

geo.country         -- 国
geo.city            -- 都市
geo.region          -- 地域

device.category     -- デバイスタイプ（mobile/tablet/desktop）
device.browser      -- ブラウザ名
device.operating_system  -- OS

traffic_source.source    -- 流入元（例: google）
traffic_source.medium    -- メディア（例: organic）
traffic_source.name      -- キャンペーン名

-- event_params からパラメータ値を取得する基本パターン
-- （UNNEST で REPEATED RECORD 型の配列を展開する）

SELECT
  event_name,
  (SELECT value.string_value
   FROM UNNEST(event_params)
   WHERE key = 'page_location') AS page_url,
  (SELECT value.string_value
   FROM UNNEST(event_params)
   WHERE key = 'page_title') AS page_title,
  (SELECT value.int_value
   FROM UNNEST(event_params)
   WHERE key = 'ga_session_id') AS session_id,
  (SELECT value.int_value
   FROM UNNEST(event_params)
   WHERE key = 'engagement_time_msec') AS engagement_msec
FROM `your-project.analytics_XXXXXXXX.events_20260314`
WHERE event_name = 'page_view'
LIMIT 10

SELECT
  REGEXP_REPLACE(
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'),
    r'\?.*$', ''  -- クエリパラメータを除去
  ) AS page_url,
  COUNT(*) AS page_views,
  COUNT(DISTINCT user_pseudo_id) AS unique_users
FROM `your-project.analytics_XXXXXXXX.events_*`
WHERE
  _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY))
                    AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())
  AND event_name = 'page_view'
GROUP BY 1
ORDER BY page_views DESC
LIMIT 20

SELECT
  device.category AS device_type,
  traffic_source.source AS source,
  traffic_source.medium AS medium,
  COUNT(DISTINCT
    CONCAT(user_pseudo_id, CAST(
      (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id')
    AS STRING))) AS sessions,
  COUNT(DISTINCT user_pseudo_id) AS users
FROM `your-project.analytics_XXXXXXXX.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20260307' AND '20260314'
GROUP BY 1, 2, 3
ORDER BY sessions DESC
LIMIT 30

WITH funnel AS (
  SELECT
    user_pseudo_id,
    COUNTIF(event_name = 'view_item') > 0 AS viewed_item,
    COUNTIF(event_name = 'add_to_cart') > 0 AS added_to_cart,
    COUNTIF(event_name = 'begin_checkout') > 0 AS began_checkout,
    COUNTIF(event_name = 'purchase') > 0 AS purchased
  FROM `your-project.analytics_XXXXXXXX.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20260301' AND '20260314'
  GROUP BY user_pseudo_id
)
SELECT
  COUNTIF(viewed_item) AS step1_view,
  COUNTIF(added_to_cart) AS step2_cart,
  COUNTIF(began_checkout) AS step3_checkout,
  COUNTIF(purchased) AS step4_purchase,
  ROUND(COUNTIF(added_to_cart) / COUNTIF(viewed_item) * 100, 1) AS view_to_cart_pct,
  ROUND(COUNTIF(began_checkout) / COUNTIF(added_to_cart) * 100, 1) AS cart_to_checkout_pct,
  ROUND(COUNTIF(purchased) / COUNTIF(began_checkout) * 100, 1) AS checkout_to_purchase_pct,
  ROUND(COUNTIF(purchased) / COUNTIF(viewed_item) * 100, 1) AS overall_cvr_pct
FROM funnel

-- 初回訪問した週から何週間後に再訪問したかを集計する
WITH first_visit AS (
  SELECT
    user_pseudo_id,
    MIN(DATE(TIMESTAMP_MICROS(event_timestamp))) AS first_visit_date
  FROM `your-project.analytics_XXXXXXXX.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20260101' AND '20260314'
    AND event_name = 'session_start'
  GROUP BY user_pseudo_id
),
user_visits AS (
  SELECT
    e.user_pseudo_id,
    DATE(TIMESTAMP_MICROS(e.event_timestamp)) AS visit_date,
    fv.first_visit_date,
    DATE_DIFF(DATE(TIMESTAMP_MICROS(e.event_timestamp)), fv.first_visit_date, WEEK) AS week_num
  FROM `your-project.analytics_XXXXXXXX.events_*` e
  JOIN first_visit fv USING (user_pseudo_id)
  WHERE _TABLE_SUFFIX BETWEEN '20260101' AND '20260314'
    AND e.event_name = 'session_start'
)
SELECT
  DATE_TRUNC(first_visit_date, WEEK) AS cohort_week,
  week_num,
  COUNT(DISTINCT user_pseudo_id) AS returning_users
FROM user_visits
WHERE week_num BETWEEN 0 AND 8
GROUP BY 1, 2
ORDER BY 1, 2

SELECT
  user_id,
  COUNT(DISTINCT event_date) AS purchase_days,
  COUNT(*) AS total_purchases,
  SUM((SELECT value.double_value FROM UNNEST(event_params) WHERE key = 'value')) AS total_ltv,
  AVG((SELECT value.double_value FROM UNNEST(event_params) WHERE key = 'value')) AS avg_order_value,
  MIN(event_date) AS first_purchase,
  MAX(event_date) AS last_purchase,
  DATE_DIFF(PARSE_DATE('%Y%m%d', MAX(event_date)), PARSE_DATE('%Y%m%d', MIN(event_date)), DAY) AS days_as_customer
FROM `your-project.analytics_XXXXXXXX.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20260101' AND '20260314'
  AND event_name = 'purchase'
  AND user_id IS NOT NULL
GROUP BY user_id
ORDER BY total_ltv DESC
LIMIT 20

WITH sessions AS (
  SELECT
    user_pseudo_id,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS session_id,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS landing_page,
    COUNT(*) AS event_count
  FROM `your-project.analytics_XXXXXXXX.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20260307' AND '20260314'
  GROUP BY 1, 2, 3
)
SELECT
  REGEXP_REPLACE(landing_page, r'\?.*$', '') AS page,
  COUNT(*) AS sessions,
  COUNTIF(event_count = 1) AS bounced_sessions,
  ROUND(COUNTIF(event_count = 1) / COUNT(*) * 100, 1) AS bounce_rate_pct
FROM sessions
WHERE landing_page IS NOT NULL
GROUP BY 1
HAVING sessions >= 100  -- 100セッション以上のページのみ
ORDER BY sessions DESC
LIMIT 30

SELECT
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'button_name') AS button_name,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_url,
  COUNT(*) AS click_count,
  COUNT(DISTINCT user_pseudo_id) AS unique_clickers
FROM `your-project.analytics_XXXXXXXX.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20260307' AND '20260314'
  AND event_name = 'button_click'  -- GTMで計測しているカスタムイベント名
GROUP BY 1, 2
ORDER BY click_count DESC
LIMIT 20

SELECT
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_url,
  (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'percent_scrolled') AS scroll_depth,
  COUNT(*) AS event_count,
  COUNT(DISTINCT user_pseudo_id) AS users
FROM `your-project.analytics_XXXXXXXX.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20260307' AND '20260314'
  AND event_name = 'scroll'
GROUP BY 1, 2
ORDER BY page_url, scroll_depth

-- 購入ユーザーがどのページを経由したかを分析する
WITH purchase_users AS (
  SELECT DISTINCT user_pseudo_id
  FROM `your-project.analytics_XXXXXXXX.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20260307' AND '20260314'
    AND event_name = 'purchase'
)
SELECT
  (SELECT value.string_value FROM UNNEST(e.event_params) WHERE key = 'page_location') AS page_url,
  COUNT(*) AS views,
  COUNT(DISTINCT e.user_pseudo_id) AS viewers
FROM `your-project.analytics_XXXXXXXX.events_*` e
WHERE e._TABLE_SUFFIX BETWEEN '20260307' AND '20260314'
  AND e.event_name = 'page_view'
  AND e.user_pseudo_id IN (SELECT user_pseudo_id FROM purchase_users)
GROUP BY 1
ORDER BY views DESC
LIMIT 20

SELECT
  event_date,
  COUNT(DISTINCT user_pseudo_id) AS dau,
  -- 7日間の移動平均（WAU相当）
  AVG(COUNT(DISTINCT user_pseudo_id)) OVER (
    ORDER BY event_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS wau_rolling,
  -- 30日間の移動平均（MAU相当）
  AVG(COUNT(DISTINCT user_pseudo_id)) OVER (
    ORDER BY event_date
    ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
  ) AS mau_rolling
FROM `your-project.analytics_XXXXXXXX.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20260101' AND '20260314'
  AND event_name = 'session_start'
GROUP BY event_date
ORDER BY event_date

【Looker Studio × BigQuery 連携手順】

1. Looker Studio（lookerstudio.google.com）を開く

2. 新しいレポートを作成 → データソースを追加

3. BigQuery コネクタを選択
   → プロジェクト・データセット・テーブルを選択
   → または「カスタムクエリ」でSQLを直接入力

4. ダッシュボードにグラフ・表を追加
   - 折れ線グラフ: 日別PV/セッション
   - 棒グラフ: ページ別PV
   - 表: ファネル分析結果
   - スコアカード: CVR・直帰率・LTV

5. フィルターを追加（日付範囲・デバイス種別・国など）

コスト最適化のポイント：
→ Looker Studio のデータソースに「スケジュールキャッシュ」を設定する
→ 毎回クエリを実行せず、N時間ごとにキャッシュを更新
→ BigQuery のクエリコストを大幅に削減できる

-- 毎日実行する集計クエリの例
-- 前日の基本KPIを集計してレポートテーブルに保存する

INSERT INTO `your-project.analytics_reports.daily_kpi`
SELECT
  FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)) AS report_date,
  COUNT(DISTINCT user_pseudo_id) AS dau,
  COUNT(DISTINCT CONCAT(
    user_pseudo_id,
    CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS STRING)
  )) AS sessions,
  COUNTIF(event_name = 'purchase') AS purchases,
  SUM(IF(event_name = 'purchase',
    (SELECT value.double_value FROM UNNEST(event_params) WHERE key = 'value'), 0
  )) AS revenue,
  ROUND(
    COUNTIF(event_name = 'purchase') /
    COUNT(DISTINCT CONCAT(user_pseudo_id, CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS STRING))) * 100, 2
  ) AS cvr_pct
FROM `your-project.analytics_XXXXXXXX.events_*`
WHERE _TABLE_SUFFIX = FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))

-- ❌ コストが高いクエリ：全テーブルをスキャン
SELECT * FROM `project.analytics_XXXXXXXX.events_*`

-- ✅ コストが低いクエリ①：テーブルサフィックスで日付を絞る
SELECT event_name, COUNT(*) FROM `project.analytics_XXXXXXXX.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20260307' AND '20260314'  -- 必ず日付を指定
GROUP BY 1

-- ✅ コストが低いクエリ②：必要なカラムのみSELECT（SELECT * は使わない）
SELECT event_date, event_name, user_pseudo_id
FROM `project.analytics_XXXXXXXX.events_20260314`

-- ✅ コストが低いクエリ③：パーティションを活用する
-- GA4テーブルは event_date でパーティション分割されているため
-- WHERE event_date = '20260314' でパーティションプルーニングが効く

-- ✅ コストが低いクエリ④：頻繁に使うクエリはマテリアライズドビューやテーブルに保存
CREATE TABLE IF NOT EXISTS `project.analytics_reports.daily_summary` AS
SELECT event_date, event_name, COUNT(*) AS cnt
FROM `project.analytics_XXXXXXXX.events_*`
GROUP BY 1, 2

# Python から BigQuery のデータを取得する例
# pip install google-cloud-bigquery pandas-gbq

from google.cloud import bigquery
import pandas as pd

client = bigquery.Client(project='your-project')

query = """
SELECT event_date, COUNT(*) AS page_views
FROM `your-project.analytics_XXXXXXXX.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20260101' AND '20260314'
  AND event_name = 'page_view'
GROUP BY event_date
ORDER BY event_date
"""

df = client.query(query).to_dataframe()
print(df.head())
# → 日付×PV数のDataFrameが取得できる