如何在 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

(原文翻譯自。)