ga4-bigquery-schema

Name: ga4-bigquery-schema
Author: cognyai

github.com/cognyaiデータ・データベース11152 回インストール2 週間前に更新

リポジトリ

ga4-bigquery-schema について

BigQuery の GA4 エクスポートデータを扱う分析担当者向けのリファレンススキルです。ネストされたスキーマ、クエリ例、パフォーマンス上の注意点を説明します。

主な機能

01トップレベルとネスト構造のリファレンス
02一般的な分析の SQL 例
03クエリのパフォーマンス指針
04エクスポートデータの品質確認

活用シーン

01イベントパラメータとユーザープロパティの抽出
02セッション、ファネル、収益の分析
03エクスポート日とイベント数の確認

インストール

コマンドをコピーしてターミナルで実行します。

$npx skills add https://github.com/cognyai/claude-code-marketing-skills --skill ga4-bigquery-schema

インストール後、Claude Code で/ga4-bigquery-schemaを入力して読み込まれたか確認します。

詳細

GA4 BigQuery エクスポートスキーマのリファレンス

テーブル構造、ネストされたフィールド、一般的なクエリ、データ処理のベストプラクティスを含む、Google Analytics 4 BigQuery エクスポートスキーマの完全なリファレンスガイドです。

ドキュメント全文：https://cogny.com/docs/ga4-bigquery-export-schema

使い方

/ga4-bigquery-schema                      # Show full schema overview
/ga4-bigquery-schema event_params         # Explain event_params structure
/ga4-bigquery-schema conversion funnel    # Show conversion funnel query pattern
/ga4-bigquery-schema ecommerce            # Explain ecommerce fields

手順

あなたは GA4 BigQuery スキーマのエキスパートです。このリファレンスを使って、ユーザーが GA4 エクスポートスキーマを理解し、GA4 データに対する正しい BigQuery SQL クエリを作成し、パフォーマンスのベストプラクティスに従えるよう支援してください。

ユーザーから質問があった場合は、以下から関連するセクションを見つけ、すぐに使える SQL の例を含む、正確で実行可能な回答を提供してください。

ユーザーが引数として特定のトピックを指定した場合は、その領域に焦点を当ててください。指定がない場合は、スキーマ構造の概要を説明してください。

概要

Google Analytics 4 は、生のイベントデータをネストされた非正規化形式で BigQuery にエクスポートします。

日次テーブル： analytics_PROPERTY_ID.events_YYYYMMDD イントラデイテーブル： analytics_PROPERTY_ID.events_intraday_YYYYMMDD

各行は単一のイベントを表し、イベントパラメータ、ユーザープロパティ、e コマースデータのネストされたフィールドを含みます。

-- View table schema
SELECT
  column_name,
  data_type,
  description
FROM `project.analytics_123456789.INFORMATION_SCHEMA.COLUMNS`
WHERE table_name LIKE 'events_%'
ORDER BY ordinal_position

トップレベルのフィールド

フィールド	型	説明
`event_date`	STRING	イベントが記録された日付（YYYYMMDD 形式）
`event_timestamp`	INTEGER	イベントが記録された時刻（Unix エポックからのマイクロ秒）
`event_name`	STRING	イベント名（例：'page_view'、'purchase'）
`event_params`	ARRAY<STRUCT>	イベントパラメータの配列
`event_previous_timestamp`	INTEGER	このユーザーの前のイベントのタイムスタンプ
`event_value_in_usd`	FLOAT	イベントの米ドルでの値
`event_bundle_sequence_id`	INTEGER	イベントバンドルの連番 ID
`event_server_timestamp_offset`	INTEGER	収集時刻とサーバー時刻の差
`user_id`	STRING	setUserId API で設定されたユーザー ID
`user_pseudo_id`	STRING	ユーザーの仮名 ID（Cookie ベース）
`user_properties`	ARRAY<STRUCT>	ユーザープロパティの配列
`user_first_touch_timestamp`	INTEGER	ユーザーが初めて訪問した時刻（マイクロ秒）
`user_ltv`	STRUCT	ユーザーのライフタイムバリュー情報
`device`	STRUCT	デバイス情報
`geo`	STRUCT	地理情報
`app_info`	STRUCT	アプリ情報（モバイルアプリ）
`traffic_source`	STRUCT	トラフィックソース情報
`stream_id`	STRING	データストリームの数値 ID
`platform`	STRING	プラットフォーム（web、ios、android）
`ecommerce`	STRUCT	e コマースのトランザクションデータ
`items`	ARRAY<STRUCT>	商品詳細の配列

ネストされた構造

event_params

キーと値のペアとして保存されるイベントパラメータ：

STRUCT<
  key STRING,
  value STRUCT<
    string_value STRING,
    int_value INT64,
    float_value FLOAT64,
    double_value FLOAT64
  >
>

一般的なイベントパラメータ：

キー	型	説明
`page_location`	string	ページの完全な URL
`page_title`	string	ページのタイトル
`page_referrer`	string	リファラー URL
`engagement_time_msec`	int	エンゲージメント時間（ミリ秒）
`session_engaged`	int	セッションがエンゲージメントされたかどうか（1/0）
`ga_session_id`	int	セッション ID
`ga_session_number`	int	ユーザーのセッション番号

例 — イベントパラメータの抽出：

SELECT
  event_name,
  (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_title') as page_title,
  (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'engagement_time_msec') as engagement_time
FROM `project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX = FORMAT_DATE('%Y%m%d', CURRENT_DATE() - 1)
  AND event_name = 'page_view'
LIMIT 100

user_properties

キーと値のペアとして保存されるユーザープロパティ：

STRUCT<
  key STRING,
  value STRUCT<
    string_value STRING,
    int_value INT64,
    float_value FLOAT64,
    double_value FLOAT64,
    set_timestamp_micros INT64
  >
>

例 — ユーザープロパティの抽出：

SELECT
  user_pseudo_id,
  (SELECT value.string_value FROM UNNEST(user_properties) WHERE key = 'user_type') as user_type,
  (SELECT value.string_value FROM UNNEST(user_properties) WHERE key = 'plan_level') as plan_level
FROM `project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX = FORMAT_DATE('%Y%m%d', CURRENT_DATE() - 1)
GROUP BY user_pseudo_id, user_type, plan_level
LIMIT 100

device

STRUCT<
  category STRING,              -- desktop, mobile, tablet
  mobile_brand_name STRING,     -- Apple, Samsung, etc.
  mobile_model_name STRING,     -- iPhone 12, Galaxy S21, etc.
  mobile_marketing_name STRING,
  mobile_os_hardware_model STRING,
  operating_system STRING,      -- iOS, Android, Windows, macOS
  operating_system_version STRING,
  vendor_id STRING,
  advertising_id STRING,
  language STRING,              -- en-us, fr-fr, etc.
  is_limited_ad_tracking STRING,
  time_zone_offset_seconds INT64,
  browser STRING,               -- Chrome, Safari, Firefox
  browser_version STRING,
  web_info STRUCT<
    browser STRING,
    browser_version STRING,
    hostname STRING
  >
>

例 — デバイス別の内訳：

SELECT
  device.category as device_category,
  device.operating_system,
  device.browser,
  COUNT(*) as event_count,
  COUNT(DISTINCT user_pseudo_id) as users
FROM `project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX = FORMAT_DATE('%Y%m%d', CURRENT_DATE() - 1)
GROUP BY 1, 2, 3
ORDER BY event_count DESC

geo

STRUCT<
  continent STRING,     -- Americas, Europe, Asia, etc.
  sub_continent STRING, -- Northern Europe, Western Asia, etc.
  country STRING,       -- United States, United Kingdom, etc.
  region STRING,        -- California, England, etc.
  metro STRING,         -- Metro area
  city STRING
>

例 — 地域別の内訳：

SELECT
  geo.country,
  geo.city,
  COUNT(DISTINCT user_pseudo_id) as users,
  COUNT(*) as events
FROM `project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX = FORMAT_DATE('%Y%m%d', CURRENT_DATE() - 1)
GROUP BY 1, 2
ORDER BY users DESC
LIMIT 100

traffic_source

STRUCT<
  name STRING,   -- Traffic source name (google, facebook, etc.)
  medium STRING, -- Traffic medium (organic, cpc, referral, etc.)
  source STRING  -- Traffic source (google, facebook.com, etc.)
>

例 — トラフィックソースのパフォーマンス：

SELECT
  traffic_source.source,
  traffic_source.medium,
  COUNT(DISTINCT user_pseudo_id) as users,
  COUNT(DISTINCT CASE WHEN event_name = 'purchase' THEN user_pseudo_id END) as purchasers,
  SAFE_DIVIDE(
    COUNT(DISTINCT CASE WHEN event_name = 'purchase' THEN user_pseudo_id END),
    COUNT(DISTINCT user_pseudo_id)
  ) as conversion_rate
FROM `project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX = FORMAT_DATE('%Y%m%d', CURRENT_DATE() - 1)
GROUP BY 1, 2
ORDER BY users DESC

ecommerce

STRUCT<
  total_item_quantity INT64,
  purchase_revenue_in_usd FLOAT64,
  purchase_revenue FLOAT64,
  refund_value_in_usd FLOAT64,
  refund_value FLOAT64,
  shipping_value_in_usd FLOAT64,
  shipping_value FLOAT64,
  tax_value_in_usd FLOAT64,
  tax_value FLOAT64,
  unique_items INT64,
  transaction_id STRING
>

例 — 収益分析：

SELECT
  DATE(TIMESTAMP_MICROS(event_timestamp)) as date,
  COUNT(DISTINCT ecommerce.transaction_id) as transactions,
  SUM(ecommerce.purchase_revenue_in_usd) as revenue,
  AVG(ecommerce.purchase_revenue_in_usd) as avg_order_value
FROM `project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY))
                        AND FORMAT_DATE('%Y%m%d', CURRENT_DATE() - 1)
  AND event_name = 'purchase'
  AND ecommerce.transaction_id IS NOT NULL
GROUP BY 1
ORDER BY 1 DESC

items

STRUCT<
  item_id STRING,
  item_name STRING,
  item_brand STRING,
  item_variant STRING,
  item_category STRING,
  item_category2 STRING,
  item_category3 STRING,
  item_category4 STRING,
  item_category5 STRING,
  price_in_usd FLOAT64,
  price FLOAT64,
  quantity INT64,
  item_revenue_in_usd FLOAT64,
  item_revenue FLOAT64,
  item_refund_in_usd FLOAT64,
  item_refund FLOAT64,
  coupon STRING,
  affiliation STRING,
  location_id STRING,
  item_list_id STRING,
  item_list_name STRING,
  item_list_index STRING,
  promotion_id STRING,
  promotion_name STRING,
  creative_name STRING,
  creative_slot STRING
>

例 — 商品のパフォーマンス：

SELECT
  item.item_name,
  item.item_category,
  SUM(item.quantity) as total_quantity,
  SUM(item.item_revenue_in_usd) as total_revenue,
  COUNT(DISTINCT ecommerce.transaction_id) as transactions
FROM `project.analytics_123456789.events_*`,
  UNNEST(items) as item
WHERE _TABLE_SUFFIX = FORMAT_DATE('%Y%m%d', CURRENT_DATE() - 1)
  AND event_name = 'purchase'
GROUP BY 1, 2
ORDER BY total_revenue DESC
LIMIT 100

一般的なクエリパターン

セッションとユーザー

SELECT
  DATE(TIMESTAMP_MICROS(event_timestamp)) as date,
  COUNT(DISTINCT user_pseudo_id) as users,
  COUNT(DISTINCT CONCAT(user_pseudo_id,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id'))) as sessions,
  SAFE_DIVIDE(
    COUNT(DISTINCT CONCAT(user_pseudo_id,
      (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id'))),
    COUNT(DISTINCT user_pseudo_id)
  ) as sessions_per_user
FROM `project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY))
                        AND FORMAT_DATE('%Y%m%d', CURRENT_DATE() - 1)
GROUP BY 1
ORDER BY 1 DESC

ページビュー

SELECT
  (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_title') as page_title,
  COUNT(*) as page_views,
  COUNT(DISTINCT user_pseudo_id) as unique_users,
  AVG((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'engagement_time_msec')) / 1000 as avg_engagement_seconds
FROM `project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX = FORMAT_DATE('%Y%m%d', CURRENT_DATE() - 1)
  AND event_name = 'page_view'
GROUP BY 1, 2
ORDER BY page_views DESC
LIMIT 100

コンバージョンファネル

WITH funnel_steps AS (
  SELECT
    user_pseudo_id,
    COUNTIF(event_name = 'page_view') as step_1_landing,
    COUNTIF(event_name = 'view_item') as step_2_product_view,
    COUNTIF(event_name = 'add_to_cart') as step_3_add_to_cart,
    COUNTIF(event_name = 'begin_checkout') as step_4_checkout,
    COUNTIF(event_name = 'purchase') as step_5_purchase
  FROM `project.analytics_123456789.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() - 1)
  GROUP BY user_pseudo_id
)

SELECT
  'Landing' as step, 1 as step_number,
  COUNT(DISTINCT CASE WHEN step_1_landing > 0 THEN user_pseudo_id END) as users,
  1.0 as conversion_rate
FROM funnel_steps
UNION ALL
SELECT 'Product View', 2,
  COUNT(DISTINCT CASE WHEN step_2_product_view > 0 THEN user_pseudo_id END),
  SAFE_DIVIDE(COUNT(DISTINCT CASE WHEN step_2_product_view > 0 THEN user_pseudo_id END),
              COUNT(DISTINCT CASE WHEN step_1_landing > 0 THEN user_pseudo_id END))
FROM funnel_steps
UNION ALL
SELECT 'Add to Cart', 3,
  COUNT(DISTINCT CASE WHEN step_3_add_to_cart > 0 THEN user_pseudo_id END),
  SAFE_DIVIDE(COUNT(DISTINCT CASE WHEN step_3_add_to_cart > 0 THEN user_pseudo_id END),
              COUNT(DISTINCT CASE WHEN step_2_product_view > 0 THEN user_pseudo_id END))
FROM funnel_steps
UNION ALL
SELECT 'Checkout', 4,
  COUNT(DISTINCT CASE WHEN step_4_checkout > 0 THEN user_pseudo_id END),
  SAFE_DIVIDE(COUNT(DISTINCT CASE WHEN step_4_checkout > 0 THEN user_pseudo_id END),
              COUNT(DISTINCT CASE WHEN step_3_add_to_cart > 0 THEN user_pseudo_id END))
FROM funnel_steps
UNION ALL
SELECT 'Purchase', 5,
  COUNT(DISTINCT CASE WHEN step_5_purchase > 0 THEN user_pseudo_id END),
  SAFE_DIVIDE(COUNT(DISTINCT CASE WHEN step_5_purchase > 0 THEN user_pseudo_id END),
              COUNT(DISTINCT CASE WHEN step_4_checkout > 0 THEN user_pseudo_id END))
FROM funnel_steps
ORDER BY step_number

ユーザー獲得

WITH first_visit AS (
  SELECT
    user_pseudo_id,
    MIN(event_timestamp) as first_visit_timestamp,
    ARRAY_AGG(
      STRUCT(traffic_source.source, traffic_source.medium, traffic_source.name)
      ORDER BY event_timestamp LIMIT 1
    )[OFFSET(0)] as first_traffic_source
  FROM `project.analytics_123456789.events_*`
  WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY))
                          AND FORMAT_DATE('%Y%m%d', CURRENT_DATE() - 1)
    AND event_name = 'first_visit'
  GROUP BY user_pseudo_id
)

SELECT
  first_traffic_source.source as acquisition_source,
  first_traffic_source.medium as acquisition_medium,
  COUNT(DISTINCT user_pseudo_id) as new_users,
  COUNT(DISTINCT CASE WHEN purchase_count > 0 THEN user_pseudo_id END) as purchasers,
  SAFE_DIVIDE(
    COUNT(DISTINCT CASE WHEN purchase_count > 0 THEN user_pseudo_id END),
    COUNT(DISTINCT user_pseudo_id)
  ) as conversion_rate
FROM first_visit
LEFT JOIN (
  SELECT user_pseudo_id, COUNT(*) as purchase_count
  FROM `project.analytics_123456789.events_*`
  WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY))
                          AND FORMAT_DATE('%Y%m%d', CURRENT_DATE() - 1)
    AND event_name = 'purchase'
  GROUP BY user_pseudo_id
) purchases USING(user_pseudo_id)
GROUP BY 1, 2
ORDER BY new_users DESC

コホート継続率

WITH cohorts AS (
  SELECT
    user_pseudo_id,
    DATE(TIMESTAMP_MICROS(MIN(user_first_touch_timestamp))) as cohort_date
  FROM `project.analytics_123456789.events_*`
  WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY))
                          AND FORMAT_DATE('%Y%m%d', CURRENT_DATE() - 1)
  GROUP BY user_pseudo_id
),
user_activity AS (
  SELECT user_pseudo_id, DATE(TIMESTAMP_MICROS(event_timestamp)) as activity_date
  FROM `project.analytics_123456789.events_*`
  WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY))
                          AND FORMAT_DATE('%Y%m%d', CURRENT_DATE() - 1)
  GROUP BY user_pseudo_id, activity_date
)

SELECT
  cohort_date,
  DATE_DIFF(activity_date, cohort_date, DAY) as days_since_cohort,
  COUNT(DISTINCT c.user_pseudo_id) as cohort_size,
  COUNT(DISTINCT a.user_pseudo_id) as active_users,
  SAFE_DIVIDE(COUNT(DISTINCT a.user_pseudo_id), COUNT(DISTINCT c.user_pseudo_id)) as retention_rate
FROM cohorts c
LEFT JOIN user_activity a USING(user_pseudo_id)
WHERE DATE_DIFF(activity_date, cohort_date, DAY) IN (0, 1, 7, 14, 30, 60, 90)
GROUP BY 1, 2
ORDER BY 1 DESC, 2

パフォーマンスのベストプラクティス

パーティションプルーニング — 常に _TABLE_SUFFIX を使用する

-- GOOD: Uses partition pruning
WHERE _TABLE_SUFFIX BETWEEN '20250101' AND '20250131'

-- BAD: Scans all partitions
WHERE event_date BETWEEN '20250101' AND '20250131'

クラスタリング — event_name でフィルタリング

GA4 テーブルは event_name でクラスタリングされています。可能な限り、必ず event_name フィルターを含めてください。

マテリアライズドビュー

頻繁にアクセスされる集計には、マテリアライズドビューを作成してください。

CREATE MATERIALIZED VIEW `project.analytics_123456789.daily_summary`
AS
SELECT
  event_date,
  traffic_source.source,
  traffic_source.medium,
  device.category,
  COUNT(DISTINCT user_pseudo_id) as users,
  COUNT(*) as events,
  COUNTIF(event_name = 'purchase') as purchases,
  SUM(CASE WHEN event_name = 'purchase' THEN ecommerce.purchase_revenue_in_usd END) as revenue
FROM `project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX >= FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 365 DAY))
GROUP BY 1, 2, 3, 4

データ品質チェック

エクスポート日付の欠落を確認する

WITH date_range AS (
  SELECT date
  FROM UNNEST(GENERATE_DATE_ARRAY(DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY), CURRENT_DATE() - 1)) as date
),
exported_dates AS (
  SELECT DISTINCT PARSE_DATE('%Y%m%d', _TABLE_SUFFIX) as date
  FROM `project.analytics_123456789.events_*`
  WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY))
                          AND FORMAT_DATE('%Y%m%d', CURRENT_DATE() - 1)
)
SELECT dr.date, 'Missing' as status
FROM date_range dr
LEFT JOIN exported_dates ed USING(date)
WHERE ed.date IS NULL
ORDER BY dr.date DESC

イベント数の整合性を検証する

WITH daily_counts AS (
  SELECT
    DATE(TIMESTAMP_MICROS(event_timestamp)) as date,
    COUNT(*) as event_count,
    COUNT(DISTINCT user_pseudo_id) as user_count
  FROM `project.analytics_123456789.events_*`
  WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY))
                          AND FORMAT_DATE('%Y%m%d', CURRENT_DATE() - 1)
  GROUP BY 1
)
SELECT
  date, event_count, user_count,
  AVG(event_count) OVER (ORDER BY date ROWS BETWEEN 7 PRECEDING AND 1 PRECEDING) as avg_7day,
  event_count / NULLIF(AVG(event_count) OVER (ORDER BY date ROWS BETWEEN 7 PRECEDING AND 1 PRECEDING), 0) as variance_ratio
FROM daily_counts
ORDER BY date DESC

リソース

GA4 BigQuery エクスポートスキーマ： https://support.google.com/analytics/answer/7029846
BigQuery のベストプラクティス： https://cloud.google.com/bigquery/docs/best-practices
GA4 イベントリファレンス： https://developers.google.com/analytics/devguides/collection/ga4/reference/events
Cogny ドキュメント全文： https://cogny.com/docs/ga4-bigquery-export-schema

Markdown 対応

ログインしてコメント

基本情報

作者: cognyai
リポジトリ: cognyai/claude-code-marketing-skills
カテゴリ: データ・データベース
インストール数: 52
GitHub スター: 111

問題を報告