前言

如果您先前看過我們站上的文章：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查詢後的資料如下：

總結

透過新增的欄位 collected_traffic_source 系列可以單獨記錄包含透過事件層級收集到的流量來源資料，這對我們在BigQuery整理資料上又更加方便了，而且還能不受限定的同時看到得WEB與APP中的資料，整個資料的結構性也越加完整了！