[BigQuery] 在GA4的查詢中重現UA(GA3)的hitNumber，觀察使用者進入網站後的歷程

快速導覽

前言

在過去Universal Analytics (以下簡稱GA3)的原始資料匯出至BigQuery中提供了一個關鍵的指標：「hits.hitNumber」。

這個指標對於追蹤和分析用戶的網站互動行為至關重要。它不僅記錄了每次點擊或頁面的瀏覽，而且還按照用戶的每一次訪問順序進行了記錄。這意味著，通過分析「hits.hitNumber」，行銷人員和數據分析師能夠準確地瞭解用戶的瀏覽路徑，從而發掘用戶行為的模式和趨勢。

然而，隨著 Google Analytics 的演進到GA4，已將原先GA3要付費才能匯出raw data的功能變成了免費提供([GA4] GA4串接BigQuery並匯出原始資料)，但於此同時，由於架構上的改變，許多原本在 GA3 中現成的指標和功能並未直接被包含在內，像是前面提過的指標「hits.hitNumber」目前在BigQuery中就沒有此欄位，這對於許多依賴這些數據進行深入分析的人來說是一個挑戰。

幸運的是，有了 BigQuery 的強大功能，我們可以手動重現這些關鍵數據點，例如「hit number」。在本文中，我將講述如何使用 BigQuery 來重現 GA4 中類似於 GA3 的「hit number」指標，從而保持對用戶行為的深入洞察。

SQL寫法

這裡直接寫一段SQL語法來重現GA3中的hitNumner，日期是2023/12/01至2023/12/10：

SELECT
  ROW_NUMBER() OVER (PARTITION BY user_pseudo_id ORDER BY user_pseudo_id, event_timestamp) AS user_event_number,
  *
FROM
 `your-ga4-property-id.analytics_244536022.events_intraday_*`
WHERE
 _table_suffix between "20231201" and "20231210" 

上述的SQL代碼使用了 ROW_NUMBER() 函數，結合 OVER 子句來實現一個特定的功能，具體描述如下：

• 功能: ROW_NUMBER() 函數在這裡被用來為每個User的事件生成一個唯一的順序號。這個順序號表示了事件發生的相對順序。

• 分組 (PARTITION BY: 通過 PARTITION BY user_pseudo_id，數據會根據每個User的GA4 User ID (user_pseudo_id) 進行分組。這意味著每個User的事件都會分開來考慮，每個User會有自己的一套唯一的順序號。

• 排序 (ORDER BY): 接著，在每個User的分組內部，事件根據 event_timestamp（事件時間戳）進行排序。這確保了事件順序號是按照實際發生的時間順序來分配的。

• 重新命名: AS user_event_number 是將這個生成的順序號命名為 user_event_number。這樣，每條記錄都會包含一個欄位 user_event_number，表示該事件在User的事件序列中的位置。之所以不用hit是因為GA4中已沒有hit的動作，故不建議使用。

結論

上面的SQL目的是為每個User的每個事件分配一個編號，這個編號反映了該事件在用戶的整個瀏覽過程中的順序，這在分析User行為模式時極為有用，比如觀察或了解User在整個完成轉換過程中的踩點順序，也能透過上述SQL語法再加以運用，例如計算出每一個頁面(或畫面)的停留時間之類的計算，會讓後期分析上有更明確的使用者輪廓唷！

---