Google アナリティクス 4 のイベントデータのエクスポートに関する基本的なクエリ

このページで説明するサンプルクエリは、Google アナリティクス 4 の BigQuery イベント エクスポート データを対象としています。ユニバーサル アナリティクスを対象とする場合は、ユニバーサル アナリティクス向けの BigQuery クックブックをご覧ください。

こちらに記載されたクエリにはサンプル データセットが使用されているため、有効な結果が得られます。独自の Google アナリティクス プロパティの BigQuery イベント エクスポート データを使用する場合は、各クエリでコメント -- Replace table を検索して、サンプル データセット ID に置き換えてください。データセット ID は、BigQuery UI で BigQuery のエクスポート プロジェクトに移動すると確認できます。たとえば、BigQuery のエクスポート データセット ID が my- first-gcp-project:analytics_28239234 の場合、次のように置き換えます。

  -- Replace table
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`

これを次のように置き換えます。

  -- Replace table
  `my-first-gcp-project.analytics_28239234.events_*`

注: サンプルクエリにデータセット ID を追加する場合は、「:」を「.」に置き換えます。

特定の期間のクエリ

BigQuery イベント エクスポート データセットに対して、対象を特定の期間に限定したクエリを行うには、クエリの WHERE 句で _TABLE_SUFFIX 疑似列を使用します。詳しくは、選択したテーブルの _TABLE_SUFFIX によるフィルタリングをご覧ください。

たとえば、次のクエリでは、対象の期間とイベントを指定して、日付およびイベント名ごとにユニーク イベントの数をカウントします。

-- Example: Query a specific date range for selected events.
--
-- Counts unique events by date and by event name for a specifc period of days and
-- selected events(page_view, session_start, and purchase).

SELECT
  event_date,
  event_name,
  COUNT(*) AS event_count
FROM
  -- Replace table name.
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE
  event_name IN ('page_view', 'session_start', 'purchase')
  -- Replace date range.
  AND _TABLE_SUFFIX BETWEEN '20201201' AND '20201202'
GROUP BY 1, 2;

ユーザー数と新規ユーザー数

• ユーザー数の合計を取得するには、個別の user_id の数をカウントします。ただし、Google アナリティクスのクライアントがヒットごとに user_id を返さない場合、あるいは返すかどうかが不明な場合は、個別の user_pseudo_id の数をカウントします。
• 新規ユーザーの数も上記と同じ方法でカウントできますが、以下の event_name の値を使用します。
  • first_visit
  • first_open

-- Example: Get 'Total User' count and 'New User' count.

WITH
  UserInfo AS (
    SELECT
      user_pseudo_id,
      MAX(IF(event_name IN ('first_visit', 'first_open'), 1, 0)) AS is_new_user
    -- Replace table name.
    FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
    -- Replace date range.
    WHERE _TABLE_SUFFIX BETWEEN '20201101' AND '20201130'
    GROUP BY 1
  )
SELECT
  COUNT(*) AS user_count,
  SUM(is_new_user) AS new_user_count
FROM UserInfo;

購入ユーザーあたりの平均トランザクション数

次のクエリは、購入ユーザーあたりの平均トランザクション数を表示します。

-- Example: Average number of transactions per purchaser.

SELECT
  COUNT(*) / COUNT(DISTINCT user_pseudo_id) AS avg_transaction_per_purchaser
FROM
  -- Replace table name.
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE
  event_name IN ('in_app_purchase', 'purchase')
  -- Replace date range.
  AND _TABLE_SUFFIX BETWEEN '20201201' AND '20201231';

特定のイベント名の値

次のクエリは、すべての purchase イベントの event_timestamp と、関連するイベント パラメータ値を表示します。

-- Example: Query values for a specific event name.
--
-- Queries the individual timestamps and values for all 'purchase' events.

SELECT
  event_timestamp,
  (
    SELECT COALESCE(value.int_value, value.float_value, value.double_value)
    FROM UNNEST(event_params)
    WHERE key = 'value'
  ) AS event_value
FROM
  -- Replace table name.
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE
  event_name = 'purchase'
  -- Replace date range.
  AND _TABLE_SUFFIX BETWEEN '20201201' AND '20201202';

上記のクエリに変更を加えて、リストではなくイベント パラメータ値の合計を表示することもできます。

-- Example: Query total value for a specific event name.
--
-- Queries the total event value for all 'purchase' events.

SELECT
  SUM(
    (
      SELECT COALESCE(value.int_value, value.float_value, value.double_value)
      FROM UNNEST(event_params)
      WHERE key = 'value'
    ))
    AS event_value
FROM
  -- Replace table name.
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE
  event_name = 'purchase'
  -- Replace date range.
  AND _TABLE_SUFFIX BETWEEN '20201201' AND '20201202';

カートに追加された上位 10 個のアイテム

次のクエリは、多くのユーザーがカートに追加したアイテムの上位 10 個を表示します。

-- Example: Top 10 items added to cart by most users.

SELECT
  item_id,
  item_name,
  COUNT(DISTINCT user_pseudo_id) AS user_count
FROM
  -- Replace table name.
  `bigquery-public-data.ga4_obfuscated_web_ecommerce.events_*`, UNNEST(items)
WHERE
  -- Replace date range.
  _TABLE_SUFFIX BETWEEN '20201101' AND '20210131'
  AND event_name IN ('add_to_cart')
GROUP BY
  1, 2
ORDER BY
  user_count DESC
LIMIT 10;

購入ユーザーのタイプ別の平均ページビュー数（購入ユーザーと非購入ユーザー）

次のクエリは、購入ユーザーのタイプ（購入ユーザーと非購入ユーザー）別に平均ページビュー数を表示します。

-- Example: Average number of pageviews by purchaser type.

WITH
  UserInfo AS (
    SELECT
      user_pseudo_id,
      COUNTIF(event_name = 'page_view') AS page_view_count,
      COUNTIF(event_name IN ('in_app_purchase', 'purchase')) AS purchase_event_count
    -- Replace table name.
    FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
    -- Replace date range.
    WHERE _TABLE_SUFFIX BETWEEN '20201201' AND '20201202'
    GROUP BY 1
  )
SELECT
  (purchase_event_count > 0) AS purchaser,
  COUNT(*) AS user_count,
  SUM(page_view_count) AS total_page_views,
  SUM(page_view_count) / COUNT(*) AS avg_page_views,
FROM UserInfo
GROUP BY 1;

ページビューの順序

次のクエリは、ユニーク セッション中に発生した、ユーザーによるページビューの順序を表示します。

-- Example: Sequence of pageviews.

SELECT
  user_pseudo_id,
  event_timestamp,
  (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session_id,
  (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
FROM
  -- Replace table name.
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE
  event_name = 'page_view'
  -- Replace date range.
  AND _TABLE_SUFFIX BETWEEN '20201201' AND '20201202'
ORDER BY
  user_pseudo_id,
  ga_session_id,
  event_timestamp ASC;

イベント パラメータ リスト

次のクエリは、データセットに表示されるすべてのイベント パラメータを取得します。

-- Example: List all available event parameters and count their occurrences.

SELECT
  EP.key AS event_param_key,
  COUNT(*) AS occurrences
FROM
  -- Replace table name.
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`, UNNEST(event_params) AS EP
WHERE
  -- Replace date range.
  _TABLE_SUFFIX BETWEEN '20201201' AND '20201202'
GROUP BY
  event_param_key
ORDER BY
  event_param_key ASC;

Google 広告のデータと結合する

GA4 イベントでその他の Google 広告データを取得するには、Google 広告用 BigQuery Data Transfer Service を設定し、GA4 イベントデータの collected_traffic_source.gclid を Google 広告から転送された ads_ClickStats_customer_id の gclid フィールドに結合します。

なお、Google 広告の転送では顧客あたり 1 つの ads_ClickStats_customer_id 表が入力されますが、GA4 のイベントデータ エクスポートでは毎日 1 回表が作成されます。