[BigQuery] GA4 事件層級(BigQuery)來源、媒介終於來了！gclid也給了

BigQuery Jul 24, 2023

快速導覽

前言

如果您先前看過我們站上的文章：GA4的使用者、工作階段、事件來源/媒介傻傻分不清楚一文中曾詳細介紹各層級的來源、媒介。

而在該文中提到，如果把「subscription」事件當作是一個轉換，單獨看此事件的來源、媒介它會是「google/cpc」，這個就是我們提到的「事件層級」來源、媒介

BigQuery查詢事件層級的來源、媒介

透過事件參數查詢來源、媒介

以往在BigQuery查詢GA4事件層級的來源、媒介就是透過事件中的參數，針對名為「source」、「medium」的參數作查詢，如下SQL就是用來查詢事件層級的各來源、媒介總共使用者人數，如下：

with prep_sql as (
SELECT
  user_pseudo_id,
  event_timestamp,
  CASE
    WHEN page_location LIKE '%gclid%' THEN 'google'
    ELSE source_value
  END AS source,
  CASE
    WHEN page_location LIKE '%gclid%' THEN 'cpc'
    ELSE medium_value
  END AS medium
FROM (
        SELECT
            user_pseudo_id,
            event_timestamp,
            (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 = 'source') AS source_value,
            (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'medium') AS medium_value
        FROM `project_id.dataset_id.events_date`
        WHERE event_name = 'page_view' AND (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'medium') IS NOT NULL
        GROUP BY 1,2,3,4,5
    )
)

SELECT
  count(*) AS user,
  source,
  medium
FROM
    prep_sql
GROUP BY
    2,3
ORDER BY
    user desc

但上述這個SQL只能查詢得到「page_view」事件，意即只能夠查詢得到來自web的流量，因此使用上有一定的受限。

透過資料欄位查詢來源、媒介

這篇要介紹一個新的BigQuery GA4 Schema：collected_traffic_source，這個欄位是用來記錄透過事件收集到的來源、媒介、廣告活動…等，甚至連gclid (透過事件收集到的 Google cpc點擊 ID)也都有了。

下面的SQL即示範如何把上面用事件參數的查詢改寫為使用資料欄位直接作查詢：

with prep_sql as (
        SELECT
            user_pseudo_id,
            collected_traffic_source.manual_source AS source,
            collected_traffic_source.manual_medium AS medium,
        FROM  `project_id.dataset_id.events_date`
        WHERE collected_traffic_source.manual_source IS NOT NULL
        GROUP BY 1,2,3
)

SELECT
  count(*) AS user,
  source,
  medium
FROM
    prep_sql
GROUP BY
    2,3
ORDER BY
    user desc

執行上述SQL查詢後的資料如下： ---2023-07-24---3.36.20