Google Analytics 事件資料匯出的基本查詢

本頁的範例查詢適用於 Google Analytics 的 BigQuery 事件匯出資料。

查詢資料集，而非範例資料集

除非另有註明，否則這裡列出的所有查詢都會使用範例資料集，並應產生有效結果。如要使用您自己的 Google Analytics 資源的 BigQuery 事件匯出資料，請在每個查詢中尋找 -- Replace table 註解，然後取代範例表格。如要從資料集複製表格名稱，請按照下列步驟操作：

1. 前往 BigQuery 使用者介面，然後選取包含資料集的專案。
2. 在「Explorer」中找出資料表。
3. 按一下表格右側的三個垂直點，然後點選「複製 ID」。
4. 在查詢中，將範例資料表名稱替換為實際名稱。
5. 將表格的日期部分替換為 *。

舉例來說，如果「複製 ID」複製了 BigQuery 資料表名稱 my-first-gcp-project:analytics_28239234.events_20240718，請進行下列替換：

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

替換為：

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

附註： 在範例查詢中新增資料表時，請將「:」替換為「.」。

查詢特定日期範圍

如要從 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 Analytics 用戶端未在每次命中時傳回 user_id，或您不確定，請計算相異 user_pseudo_id 值的數量。
• 如果是新使用者，請計算 event_name 為下列值的不重複使用者人數：
  • first_visit
  • first_open
• 如果是回訪使用者，請計算這段期間有活動，但未在這段期間啟動第一個工作階段或首次啟動應用程式的使用者人數 (活躍使用者減去新使用者)。

-- Example: Get 'Total User' (Active User), 'New User', and 'Returning User' counts.

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 active_users,
  SUM(is_new_user) AS new_users,
  COUNT(*) - SUM(is_new_user) AS returning_users
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';

特定事件名稱的值

以下查詢會顯示所有 event_timestamp purchase 事件和相關聯的事件參數值：

-- 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_sample_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;

網頁瀏覽順序

這項查詢會顯示每位使用者瀏覽網頁的順序。查詢會使用下列欄位排序結果，讓事件依使用者發生順序列出，即使事件是在同一批次中傳送也一樣：

• user_pseudo_id
• user_id
• batch_page_id
• batch_ordering_id
• batch_event_index

雖然範例只會傳回 page_view 事件，但您可以移除 event_name 的 WHERE 子句條件，使用相同的 ORDER BY 子句，正確排序所有事件。

查詢也說明如何使用使用者定義函式 GetParamString 和 GetParamInt 減少重複內容，讓查詢更容易瞭解及維護。

附註： 範例資料集結構定義不包含 batch_page_id、batch_ordering_id 或 batch_event_index 欄位。將查詢的 FROM 子句中的資料表替換為您的資料表。

-- Example: Sequence of pageviews.

/** Temporary function to retrieve the string_value of an event parameter by event name. */
CREATE TEMP FUNCTION GetParamString(event_params ANY TYPE, param_name STRING)
AS ((SELECT ANY_VALUE(value.string_value) FROM UNNEST(event_params) WHERE key = param_name));

/** Temporary function to retrieve the int_value of an event parameter by event name. */
CREATE TEMP FUNCTION GetParamInt(event_params ANY TYPE, param_name STRING)
AS ((SELECT ANY_VALUE(value.int_value) FROM UNNEST(event_params) WHERE key = param_name));

SELECT
  user_pseudo_id,
  user_id,
  batch_page_id,
  batch_ordering_id,
  batch_event_index,
  event_name,
  GetParamInt(event_params, 'ga_session_id') as ga_session_id,
  GetParamString(event_params, 'page_location') as page_location,
  GetParamString(event_params, '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 '20240718' AND '20240731'
ORDER BY
  user_pseudo_id,
  user_id,
  batch_page_id,
  batch_ordering_id,
  batch_event_index;

事件參數清單

下列查詢會列出資料集中顯示的所有事件參數：

-- 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 Ads

如要擷取 Google Analytics 事件的額外 Google Ads 資料，請設定 Google Ads 適用的 BigQuery 資料移轉服務，然後將 Google Analytics 事件資料的 collected_traffic_source.gclid 彙整至 ads_ClickStats_customer_id 的 gclid 欄位 (透過 Google Ads 資料移轉取得)。

請注意，Google Analytics 事件資料匯出作業會為每一天分別建立表格，Google Ads 資料移轉作業則會為每個客戶的 ads_ClickStats_customer_id 表格 (每位客戶一份) 填入資料。