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

[BigQuery] 在GA4的查詢中重現UA(GA3)的hitNumber,觀察使用者進入網站後的歷程
Photo by Austris Augusts / Unsplash
快速導覽

前言

在過去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語法再加以運用,例如計算出每一個頁面(或畫面)的停留時間之類的計算,會讓後期分析上有更明確的使用者輪廓唷!