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

BigQuery Jul 17, 2023

快速導覽

一般我們在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 '20210101' and '20210131'

上面關於FROM table的部份會把「events_{date}」改成「events_*」即代表要搜尋所有表格名稱開頭為events_的表格，然後在WHERE條件中再進一步指定日期的區間。

動態日期區間

另一種常碰到的狀況是以當下日期往前推N天的日期區間，這個「當下日期」應該是要隨著查詢的當下自動更新，而不是由我們手動輸入，那麼可以試著用下面的SQL語法：

SELECT
    *
FROM
   `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE
    _table_suffix between format_date('%Y%m%d',date_sub(current_date(), interval 30 day))
    and format_date('%Y%m%d',date_sub(current_date(), interval 1 day))

上面的作法即是達到選擇過去 30 天的日期區間：今天減去昨天之前的 30 天 (包括昨天)。

靜態+動態日期區間

那麼如果想要把上述的2種方式合併在一起可以嗎？例如我想要某一天的日期為起始日，並且查詢到今日之前的5天為結束日當作日期區間，那麼你可以這樣作：

SELECT
    *
FROM
   `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE
    _table_suffix between '20210131'
    and format_date('%Y%m%d',date_sub(current_date(), interval 5 day))