GA4 BigQuery SQL 查詢：基礎語法與巢狀欄位處理實戰

基礎 SQL 語法：計算使用者與工作階段指標

GA4 中「不重複使用者數」與「工作階段數」是兩項最基本也最常被引用的指標。在 BigQuery 中，這兩個指標的計算方式與 GA4 標準報表略有差異，必須理解所使用的欄位定義。不重複使用者可透過 COUNT(DISTINCT user_pseudo_id) 計算，而工作階段數則需組合 user_pseudo_id 與 ga_session_id 兩個欄位。工作階段數不建議單純以 COUNT(session) 方式處理，因為背後的計數邏輯涉及使用者的連續互動行為，需透過事件參數還原。GA4 的標準報表中所看到的「工作階段數」，其實是基於「使用者與網站互動的一段連續時間」定義，而這一定義在原始資料中是透過 ga_session_id 參數標記的。每個事件都帶有 ga_session_id，但同一個工作階段可能產生多筆事件，因此直接計算事件列數會導致嚴重的重複計數。正確做法是將每個事件中的 user_pseudo_id 與 ga_session_id 配對為一個複合鍵，再針對此複合鍵進行去重計數。

計算不重複使用者數

計算不重複使用者數的 SQL 語法相對直觀。使用者識別碼欄位為 user_pseudo_id，代表 GA4 自動指派給每個瀏覽器或裝置的匿名 ID。這個 ID 是基於瀏覽器 Cookie 或應用程式實例產生的隨機字串，因此不會包含任何可辨識個人身份的資訊。在不重複使用者數的計算中，COUNT(DISTINCT user_pseudo_id) 的作用是對查詢範圍內所有事件對應的使用者 ID 進行去重，只保留唯一值後再計數。若事件資料範圍包含多個日期，由於同一位使用者可能在不同日期都有活動，跨日查詢時此語法也會自動進行全域去重，不會重複計算同一位使用者。若要查詢特定日期範圍內的不重複使用者數，語法如下：

SELECT
  COUNT(DISTINCT user_pseudo_id) AS unique_users
FROM `project_id.analytics_xxxxx.events_20240101`

此語法會回傳單一日期資料表中所有事件所對應的不重複使用者數。若需要查詢多日資料，可使用萬用字元及 _TABLE_SUFFIX 來涵蓋一段日期區間：

SELECT
  COUNT(DISTINCT user_pseudo_id) AS unique_users
FROM `project_id.analytics_xxxxx.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20240101' AND '20240131'

這種寫法能有效控制查詢範圍，也是控制 BigQuery 費用的重要手段。值得注意的是，_TABLE_SUFFIX 比對的是資料表名稱中萬用字元的部分，因此若資料表命名為 events_20240101，_TABLE_SUFFIX 即為 20240101，可使用字串函數進行日期區間的比較。

計算工作階段數與互動事件

工作階段數的計算較為複雜。ga_session_id 存放在 event_params 中，而一個工作階段內可能包含多個事件。因此，計算工作階段數須先從每個事件中提取 ga_session_id，再對「（user_pseudo_id, ga_session_id）」組合進行計數：

SELECT
  COUNT(DISTINCT CONCAT(user_pseudo_id, '-', session_id)) AS sessions
FROM (
  SELECT
    user_pseudo_id,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS session_id
  FROM `project_id.analytics_xxxxx.events_20240101`
)

這裡使用子查詢從 event_params 中取出 ga_session_id，再與使用者 ID 合併成一個複合鍵進行去重計數。此方式可避免同一使用者開啟多個分頁時，工作階段被重複計算的問題。此外，ga_session_id 屬於整數型別的參數，因此取值時需使用 value.int_value。在某些情況下，若 GA4 資源同時跨網域或應用程式追蹤，ga_session_id 的編號仍會在單一使用者層級內唯一，但為了避免跨使用者之間的編號重複，複合鍵的設計仍然必要。

若想進一步瞭解 GA4 BigQuery 在實際 SEO 分析中的應用與報表差異，可參考 GA4 BigQuery 能做什麼？原始事件資料分析與報表差異，內文有更多實務操作情境的說明。

基礎 SQL 語法：查詢事件與流量來源

事件層級的分析是 GA4 BigQuery 的強項之一。透過 SQL，可以精確計算特定事件的發生次數，並從 traffic_source 欄位中提取流量來源與媒介。此處需要熟悉兩個重點：一是使用 WHERE 子句篩選事件名稱與日期，二是透過 UNNEST 展開 event_params 以取得事件層級的參數值。在 GA4 中，每個事件都帶有若干參數，這些參數描述該事件的上下文，例如目前頁面、捲動深度、點擊元素等。透過 SQL 篩選事件名稱可以從海量資料中隔離出特定互動行為，而展開參數則能進一步獲取事件的詳細內容。

拆解 event_params 取得特定參數

page_view 是最常見的互動事件。若要查詢特定日期內「網頁瀏覽」事件的總次數，可使用以下語法：

SELECT
  COUNT(*) AS page_view_count
FROM `project_id.analytics_xxxxx.events_20240101`
WHERE event_name = 'page_view'

此處以 COUNT(*) 計算事件列數，因為每一列在此情況下對應一個 page_view 事件，故不需加上 DISTINCT。若需進一步取得「瀏覽的網址（page_location）」明細，就必須從 event_params 中提取參數。以下語法展示如何同時取得事件名稱、網址與使用者 ID：

SELECT
  user_pseudo_id,
  event_timestamp,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_url
FROM `project_id.analytics_xxxxx.events_20240101`
WHERE event_name = 'page_view'
LIMIT 1000

此寫法以子查詢配合 UNNEST 的方式，精準取得指定參數值。過程中無需先將整個 event_params 展開，也不會受到其他未使用參數的幹擾。子查詢在每一列中獨立執行，從該列的 event_params 陣列中取出 key 等於 page_location 的記錄，再回傳其 string_value。由於一列事件通常只會有一個同名的參數，這種寫法可以安全地取得唯一值，不需擔心多筆比對結果造成的膨脹。

實務上，page_location、page_title、source、medium 這類字串型參數皆以 value.string_value 儲存，取得方式相同。若要提取數值型別的參數（如 ga_session_id、engagement_time_msec），則需改用 value.int_value 或 value.float_value，這點在撰寫查詢時需特別留意。

提取 source/medium 流量來源

GA4 的流量來源資料存放在事件資料表頂層的 traffic_source 欄位。此欄位包含 source、medium 與 campaign 等屬性，代表「使用者最初進入網站時」的來源資訊。查詢時直接透過 traffic_source.source 與 traffic_source.medium 即可取得，不需要 UNNEST：

SELECT
  traffic_source.source AS source,
  traffic_source.medium AS medium,
  COUNT(DISTINCT user_pseudo_id) AS users
FROM `project_id.analytics_xxxxx.events_20240101`
GROUP BY source, medium
ORDER BY users DESC

此查詢可列出各來源與媒介組合下的不重複使用者數。需注意的是，traffic_source 代表的是「最初」的來源，而非「當次工作階段」的來源。若要分析每場工作階段實際的來源媒介，則需回到 event_params 中提取 source 與 medium 參數，兩者意義不同，應用時需依分析目的選擇。兩者的比較可歸納為：traffic_source 反映使用者首次進站時的管道，具有「終身歸屬」特性，不會因後續來自其他管道的互動而改變；而 event_params 中的 source 與 medium 則記錄每一場工作階段當下的來源資訊，適合分析使用者回訪時的行為變化。

基礎 SQL 語法：電子商務數據分析

GA4 電子商務資料在 BigQuery 中存放於事件表的 ecommerce 欄位。當網站安裝電子商務增強型事件（如 purchase、add_to_cart、view_item）時，相關交易資料會以結構化格式寫入此欄位。其中，ecommerce.purchase_revenue 儲存交易總金額，items 欄位則存放商品層級的陣列資料。只要查詢 purchase 事件，即可取得購買行為的核心數據。這些事件之所以能正確寫入 ecommerce 欄位，前提是網站前端必須確實安裝 GA4 的電子商務外掛或自訂程式碼，並依照 Google 規範傳送相對應的參數。

計算電子商務收益與購買事件

查詢總收益與購買次數的語法如下。購買事件名稱固定為 purchase，收益則直接取自 ecommerce.purchase_revenue：

SELECT
  COUNT(*) AS purchase_count,
  SUM(ecommerce.purchase_revenue) AS total_revenue
FROM `project_id.analytics_xxxxx.events_20240101`
WHERE event_name = 'purchase'

此查詢可快速取得單日購買次數與總收益。若交易含稅金或運費，ecommerce 欄位亦有對應的 tax_value、shipping_value 可供加總。此外，ecommerce 欄位中還包含 transaction_id（交易編號）、currency（貨幣代碼）以及 coupon（優惠券名稱）等欄位，這些資料可協助進行更細緻的訂單分析。若需排除測試交易，可搭配 transaction_id 進行過濾，但這部分需依據商業邏輯而定。

商品層級的資料存放在 items 陣列中，每個商品包含 item_name、item_id、quantity、item_revenue 等子欄位。由於是陣列結構，查詢時必須以 UNNEST(items) 展開。以下範例展示如何計算各商品的銷售數量與收益貢獻：

SELECT
  item.item_name AS product_name,
  SUM(item.quantity) AS qty_sold,
  SUM(item.item_revenue) AS revenue
FROM `project_id.analytics_xxxxx.events_20240101`,
UNNEST(items) AS item
WHERE event_name = 'purchase'
GROUP BY product_name
ORDER BY revenue DESC

此查詢將購買事件中的商品陣列展開，以商品維度彙整銷售數據。若後續要串接廣告費用或站內產品分類資料，可以 item_id 作為關聯鍵。展開 items 陣列後，每一列對應一筆商品記錄，因此同一個購買事件可能產生多列結果，這是在處理商品層級分析時必須理解的特性。另外，item_revenue 欄位代表單一商品的收益貢獻，並非單價，因此在計算商品單價時，可再除以 quantity，但需留意 item_revenue 是否已含折扣。

以下表格整理 GA4 核心指標在 BigQuery 中的查詢邏輯與對應欄位，方便快速查找：

此表格中的每一列對應一個指標，並列出其實現邏輯與常見的 SQL 函數。使用者在撰寫查詢時，可參考此錶快速確認方向，再依據實際業務需求調整日期範圍與額外條件。

查詢結果整合與後續視覺化應用

BigQuery 的查詢結果能作為各種後續應用的基礎，包含建立例行報表、數據稽覈或客製化儀錶板。由於語法以標準 SQL 撰寫，結果可以直接輸出至 Google Sheets、Colab 或透過 API 串接。最常見的延伸應用是將查詢結果導入 Looker Studio，建立可供團隊共享的視覺化報表。BigQuery 查詢結果的輸出格式與一般資料庫相同，皆為表格結構，因此可以無縫銜接多數分析工具與程式語言。

要以 Looker Studio 連結 BigQuery 資料，基本步驟如下：在 Looker Studio 中新增資料來源，選擇 BigQuery 連接器，接著選取專案與資料表或自訂查詢。若選擇自訂查詢，可將前段撰寫好的 SQL 語法直接貼上，系統會依據查詢結果自動產生欄位。此步驟需確認 BigQuery 專案與 Looker Studio 的帳戶具備足夠的權限，且查詢語法的命名與欄位別名應清楚易懂，以利後續建立報表時的欄位辨識。若使用 View 作為資料來源，則只需在 Looker Studio 中選取該 View，即可直接取用其虛擬資料表結構。

視覺化前，建議先將常用的 SQL 查詢儲存為 BigQuery View。View 不會實際存放資料，但能將複雜的 SQL 語法變成一個虛擬資料表。設定一次後，後續只要更新日期參數或調整維度，不需要在 Looker Studio 中重新編寫語法。這對需要每天更新數據的 SEO 報表來說，能大幅降低維護成本。此外，View 也有助於統一團隊內的指標定義，避免不同成員各自撰寫語法導致口徑不一致。若查詢中使用了固定的日期篩選條件，可在 View 內以參數方式設計，讓後續呼叫時可動態調整。

相較於 GA4 標準報表，透過 SQL 查詢可以更彈性地組合維度與指標，也能處理未經抽樣或彙總的原始事件資料。例如跨使用者歷程的來源比對、頁面層級的互動深度分析，或是與站內搜尋紀錄串接，這些都是標準報表難以達成、但可透過 BigQuery 自行定義的分析項目。標準報表通常只提供預先定義好的維度與指標組合，且大型資料集可能受到抽樣影響；而 BigQuery 中的原始資料則保留每一筆事件，不受抽樣限制，讓分析人員能進行更深入、更貼近實際行為的探索。