如何在 BigQuery 查詢並計算 Google Analytics 360 資料 (四)

本篇是此系列文的第三篇,系列文將涵蓋以下列點式的內容,本篇文章則是針對「事件追蹤」、「目標追蹤」、「加強型電子商務(交易)」、「電子商務(產品)」、「加強型電子商務(產品)」進行探討:

    • BigQuery 中的 Google Analytics 資料簡介
    • 查詢多個資料表
    • 使用者
    • 工作階段
    • 時間
    • 流量來源
    • 地理區域
    • 平台或裝置
    • 網頁追蹤
    • 事件追蹤
    • 目標追蹤
    • 加強型電子商務(交易)
    • 電子商務(產品)
    • 加強型電子商務(產品)
    • 自訂維度與自訂指標
    • 自訂管道分組
    • 當日 (intraday) 資料表
    • 即時報表與檢視

自訂維度與自訂指標 (Custom Dimensions & Custom Metrics)

以下的查詢範例包含所有的 GA 維度與指標。如果你只需要一個維度或指標,請看查詢範例中的 註解(– 開頭表註解)並由 SELECT 複製你要的欄位。另外,為確保查詢資料正確,請確認有加入其他必要的條件(在 FROM, WHERE, GROUP BY 與 ORDER BY)。

自訂維度

  • 自訂維度-使用者層級 (User)
  • 自訂維度-工作階段層級 (Session)
  • 自訂維度-匹配層級 (Hit)
  • 自訂維度-產品層級 (Product)

自訂指標

  • 自訂指標-匹配層級 (Hit)
  • 自訂指標-產品層級 (Product)

查詢範例

查詢範例結果

某些自訂維度的範例查詢,會回傳空值。

自訂管道分組 (Custom Channel Grouping)

如果需要查看自訂管道分組,可直接使用 channelGrouping 這個維度。不過基於各種因素你可能會想建立自己的管道分組。比如說,當你使用日間資料時,就沒有 channelGrouping 這個維度,或是當你需要改善歷史資料的品質時,GA 報表介面的預設管道分組也無法處理。我會示範如何重現 GA 預設管道分組在 BigQuery 中的定義。如果你用這個查詢方式開始,要自建管道分組或使用預設管道分組應該不會太難。

查詢範例

查詢範例結果

當日 (intraday) 資料表 (Intraday tables) 

對於每個匯出到 BigQuery 的 GA 資料檢視,都會包含一張每天匯出多次的ga_sessions_intraday_的資料表。我們來看看以下說明:

在每個資料集中,系統會將每天匯出的資料匯入表格,表格的格式為「ga_sessions_YYYYMMDD」。系統會以大約每天三次的頻率匯入當天資料。當天資料的表格(intraday tables)格式為「ga_sessions_intraday_YYYYMMDD」。在同一天中,系統匯入的每一筆當天資料都會覆寫之前匯入同一個表格的資料。
系統完成每天的匯入作業後,都會刪除前一天的當天資料表格。在匯入當天第一筆資料之前,系統不會建立當天資料表格。如果當天資料表寫入失敗,前一天的表格就會保留下來。每日匯入作業完成後,您才會看到最終版本的當天資料。在每日資料中,運作中的使用者工作階段 (session) 會跨越上一筆當天匯入資料的時間界線,只要以此為依據,您就能判斷當天資料與每日資料的差別。(來源

要結合 intraday 資料 (intraday data) 與歷史資料做計算,最簡單的方式,就是使用萬用字元資料表並結合 _table_suffix 條件篩選所要的資料表結尾。這樣的條件下就能得到由 ga_sessions_ 為開頭並包含 YYYYMMDD 日期格式的資料。

注意:請確保你的資料集裡面沒有以 ga_sessions_ 為開頭的資料表!

查詢範例

查詢範例結果

以上的 query 不會產生結果,因為樣本資料集中,並沒有 intraday 的資料 (intraday data) 。

即時報表與檢視 (Realtime tables & view)

如果你要看的不是 intraday 資料表 (intraday tables) 而是即時的資料表 (realtime table) 與資料檢視 (view) ,可以使用 GA 的串流匯出的功能。要查詢這份資料表並將 ga_sessions_ 的歷史資料與之結合,你會需要多一個動作:

串流匯出會為每一天建立一個新表格,每個表格又有一個 (BigQuery) 專屬的資料檢視:
表格 (table):ga_realtime_sessions_YYYYMMDD 是一個內部的「暫存」表格,當中包含當天所有活動的工作階段 (session) 記錄。系統會不斷地每 15 分鐘匯出資料一次;要是某工作階段橫跨多個匯出作業,這份表格裡會出現多筆該工作階段的紀錄。
ga_realtime_sessions_YYYYMMDD 表格不可用於查詢 (也不適用於 Google Analytics (分析) 技術支援),這是因為這類表格可能包含某些工作階段的重複紀錄,因此對表格進行查詢時可能產生非預期的結果。建議您改為查詢 ga_realtime_sessions_view_YYYYMMDD 資料檢視。
資料檢視 (view):ga_realtime_sessions_view_YYYYMMDD 位在匯出表格的頂端,用來篩除涵蓋多個匯出作業的重複工作階段的多筆紀錄,您可以查詢這份表格來取得去蕪存菁後的串流資料。(來源

查詢當日產生的即時資料檢視 (view),你只能只用舊版 SQL,任何標準 SQL 語法的 query 都會造成以下 error:

為了能使用標準 SQL,我們需要建立自己的即時資料檢視 (view)。我們可利用以下的 query 更換一個專案 (project) 與資料集 (dataset) 再來執行:

現在,你已經複製一份命名為 ga_realtime_view 的即時資料檢視 (view)。接下來,你就可以用標準 SQL 查詢這份資料了:

除了這個解決方法,還有另一點即時資料要注意的:並不是所有欄位都會在即時資料裡,例如廣告數據或預設管道分組的維度,都不在這份資料表中。

以下的範例我會結合即時資料與 ga_sessions_ 資料表中的歷史資料,並處理預設管道分組欄位缺少的問題,當作片尾彩蛋送給大家。這邊我們需要使用 UNION ALL

(原文翻譯自。)

 

相關文章

如何在 BigQuery 查詢並計算 Google Analytics 360 資料 (一)

如何在 BigQuery 查詢並計算 Google Analytics 360 資料 (三)

如何在 BigQuery 查詢並計算 Google Analytics 360 資料 (二)


連絡「GCP 專門家」