feature image

BigQuery

分享關於BigQuery與GA4、UA(GA3)、Google Search Console(GSC)串接的資訊，以及各種SQL技術分享。

9 Posts
一招讓你自動複製BigQuery資料集(Dataset)的GA4每一天資料必殺技 post image
By Jason Kang

一招讓你自動複製BigQuery資料集(Dataset)的GA4每一天資料必殺技

前情提要 由於BigQuery的GA4資料集都是好幾年的資料，要進行查詢時難免會不小心Query大量資料，而且BigQuery的查詢價格又變貴了。 BigQuery 企業資料倉儲BigQuery 是符合成本效益的無伺服器多雲端資料倉儲系統，可協助您將大數據轉化為寶貴的業務深入分析結果。歡迎免費試用。Google Cloud 如果你需要讓其他人進行小規模GA4的BigQuery測試，例如僅需要一個月的資料，GA4的資料集是經過日期的partition，每一天是一張資料表，因此在複製一個月的資料時，你會需要一張一張資料表進行複製，那可是會複製到思考人生為何要浪費時間在這個重複的動作上。 各位有福了，今天無私分享複製必殺技，我們會透過shell script的方式進行資料表複製。 Step1. 進到GCP的Cloud Shell Step2. 開啟Open Editor Step3. 新增copy_bigquery.sh，把右邊的程式碼複製進去，程式碼傳送門。 小提醒：目前有新的編輯器上線，使用這個版本的編輯器會跳出警示。 Step4. 畫紅線的部分，要記得修改為你的設定哦

BigQuery Data Science 數據科學應用
[GA4]GA Session ID 異常重複現象會影響 BigQuery 數據嗎?
By Amy Wang - A dog lover

[GA4]GA Session ID 異常重複現象會影響 BigQuery 數據嗎?

前言 GA4 鼓勵大家將資源與 BigQuery 做串接，以利數據即時性或是製作精美的儀表板。大家會做的一定是比較在 BigQuery 中 query 完的結果與 GA4 報表的數據是否相符，在比較的過程中，發現一個異常現象，分享給大家。 異常現象如下 : 同一個用戶(user_pseudo_id 相同)在「不同日期」下的 GA 會話 ID（ga_session_id）出現重複。 正常情況，每次用戶訪問網站且有產生一個新的工作階段時，都會產生一個獨一無二的會話 ID，但發現到 BigQuery 數據中，每日約0.1%的情況，明明已經結束了工作階段(事件時間間隔30分鐘以上)，新產生的事件，所配到的 ga_session_id 會與上一個工作階段的

BigQuery GA4 網站分析商務實戰
[BigQuery] 在GA4的查詢中重現UA(GA3)的hitNumber，觀察使用者進入網站後的歷程 post image
By Bryce

[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」。在本文中，我將講述如何使用 BigQu

BigQuery GA4 網站分析商務實戰
[BigQuery] BigQuery查詢GA4事件參數的2個常見技巧 post image
By Bryce

[BigQuery] BigQuery查詢GA4事件參數的2個常見技巧

前言 在您把GA4與BigQuery作好連結後並進入BigQuery中查看資料，會發現它儲存的資料格式並非全部都是一列當中包含所有資料，而是在部份的欄位中又被切分為多行，而在每行裡的欄位又有值。 在上面的照片裡，我看可以看到第四個欄位的名稱是「event_params.key」這種在單一列中含有多筆資料的方式我們稱為STRUCT（結構化），過往我們在導入GA4時都會強調它是以事件（Event）為核心來記錄所有資料；以上圖為例，是記錄了一位使用者發生了event_name為page_view的事件，而在此事件中會含有多個參數，包含hour、category、author_id、author、page_location…等，意即此資料在資料列中只有單純一個事件，但卻含有多個事件參數，因此就非常合適使用BigQuery的STRUCT（結構化）資料型態來作儲存。 UNNEST 過往在使用SQL語法查詢一般資料列時都會用像是下面這樣的用法： SELECT FROM `ga4property.analytics_244536022.events_20230926` 但此查詢方法是適合

BigQuery GA4 網站分析商務實戰
[BigQuery] 如何建立分區資料表 (partitioned tables) post image
By Vince Liao

[BigQuery] 如何建立分區資料表 (partitioned tables)

一、什麼是分區資料表？ BigQuery分區資料表是一種在Google Cloud Platform的BigQuery中，將資料表根據特定欄位分割成多個部分的方法，以提高查詢效能和數據管理的效率。像是下圖依照Order_Date欄位將資料表分區。 二、為什麼需要將資料表分區？ 隨著業務拓展及時間的累積，BigQuery儲存的資料量越來越多，查詢所需要的花費及時間也會越來越多，儘管在查詢時用WHERE去過濾資料，運算時還是會掃描整張表，花費並不會因此而減少，這時候就需要將資料表進行分區，資料表被分區後能有以下幾個優點： 1. 提高查詢效率 當數據表變得非常多時，執行查詢可能需要大量的時間。分區資料表將數據分成更小的部分，能夠僅查詢感興趣的特定分區數據，可以顯著提高查詢的效率。例如，可以根據日期分區數據，這樣可以只查詢特定日期範圍內的數據。 2. 節省成本 在查詢時，只針對需要的分區資料表進行查詢，可以讓查詢量限縮在選擇的這些分區下。例如，以日期分區的資料表，只查詢特定日期範圍內的數據時，就只會掃描該日期範圍區間內的資料。也可以強制使用資料表的使用者，在進行查詢時，必定要先

BigQuery Data Science 數據科學應用
[BigQuery] GA4 事件層級(BigQuery)來源、媒介終於來了！gclid也給了 post image
By Bryce

[BigQuery] GA4 事件層級(BigQuery)來源、媒介終於來了！gclid也給了

前言 如果您先前看過我們站上的文章：GA4的使用者、工作階段、事件來源/媒介傻傻分不清楚 一文中曾詳細介紹各層級的來源、媒介。 而在該文中提到，如果把「subscription」事件當作是一個轉換，單獨看此事件的來源、媒介它會是「google/cpc」，這個就是我們提到的「事件層級」來源、媒介 BigQuery查詢事件層級的來源、媒介 透過事件參數查詢來源、媒介 以往在BigQuery查詢GA4事件層級的來源、媒介就是透過事件中的參數，針對名為「source」、「medium」的參數作查詢，如下SQL就是用來查詢事件層級的各來源、媒介總共使用者人數，如下： with prep_sql as ( SELECT user_pseudo_id, event_timestamp, CASE WHEN page_location LIKE '%gclid%

BigQuery GA4 網站分析商務實戰
[BigQuery] BigQuery上查詢GA4資料表日期選擇區間的設定 post image
By Bryce

[BigQuery] BigQuery上查詢GA4資料表日期選擇區間的設定

一般我們在BigQuery查詢GA4的資料，有時會需要橫跨多天日期區間作為一個資料集合，而有時我們又想以今日為變數，自動計算距今日之前N天資料表，本篇將一次解說如何在一個BigQuery中選則多天的GA4 table作查詢 查詢日期區間的設定 查詢單一日期 首先我們在BigQuery會查詢單一天GA4的資料會是如下的SQL： SELECT * FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210131` 上面的events_是每一張GA4 Daily Table的固定名稱，而20210131則是指「2021-01-31」這一天。 靜態日期區間 如果要查詢一段含包起始日、結束日之間的資料表的話，則可以改成下面這樣： SELECT * FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*` WHERE _table_suffix between '2021

BigQuery GA4 網站分析商務實戰
[GA4] GA4串接BigQuery並匯出原始資料 post image
By Bryce

[GA4] GA4串接BigQuery並匯出原始資料

前言 Google Analytics 4(下稱GA4)在推出後一個重大的變更就是跨裝置追踨，可以同時蒐集網站(web)與應用程式(app)並放在同一個資源下可以作查詢，另外就是支援免費版GA4用戶匯出原始資料至BigQuery；BigQuery是GCP (Google Cloud Platform)中的一個產品，其主要是作為雲端資料倉儲系統，可以統合、管理及控管所有類型的資料，並透過內建查詢加速功能即時分析資料。BigQuery也內建了機器學習功能(BigQuery ML)，能夠建構推薦與偵測系統(智慧數據分析設計模式)，協助建立電子商務推薦系統、預測顧客終身價值，以及設計購買傾向解決方案，可以說是非常適合用來儲存包含GA4、客戶CRM等的大量數據，把GA4數據匯出至BigQuery其優點有以下幾個： 1. 資料保存期限不再受到GA4的上限日期影響。在GA4預設的保存資料期限是2個月，可以透過設定最長延長為14個月，因此把資料匯出至BigQuery即能解決資料保存期限的難題。 資料來源：https://go.data360.cc/saiqo

BigQuery GA4 網站分析商務實戰
Google Search Console 開始支援 BigQuery! 怎麼串聯? post image
By Amy Wang - A dog lover

Google Search Console 開始支援 BigQuery! 怎麼串聯?

Google 於2/22年宣布Google Search Console 支援 BigQuery! * 文章內容深度 : 4/5顆星 * 適用對象 : 有在使用 BigQuery 分析用戶資料的人 * 直接看圖解步驟，滑至最底部 前情提要 : 什麼是 Google Search Console & BigQuery ? ▶ Google Search Console 是 Google 提供的一項免費服務，能夠協助您監控及維持網站在 Google 搜尋結果中的排名。 白話文就是，可以瞭解到進到網站的使用者都是在 Google 搜尋中輸入哪些搜尋字詞，進一步去優化網站的 SEO，提升網站的搜尋結果排名，讓使用者在搜尋這些字時，能更快的看到你的網站內容。 ▶ BigQuery 是由 Google Cloud Platform 提供的一種全雲端的大數據分析平台，可以快速地處理大量的資料，而且低成本，是企業級大數據分析的首選平台之一。而且能夠實現即時數據分析，

BigQuery GA4 網站分析商務實戰 Google Search Console