BigQuery問世好久一直沒機會接觸，剛好碰到跟行銷相關案子就來研究一下了

正文開始

首先Firebase專案必須先關聯上BigQuery，我自己實測大約隔天開始才會有值

切換到GCP BigQuery，關聯上並且有資料後，在左側選單會有analytic的子項，Firebase Analytics的Event似乎都是存放在這裡，Event會以日期為單位切成Table，也可以直接從預覽看到Table的資料

下幾個簡單的SQL測試撈取結果
From的部分需要特別注意，經過幾次測試，Table要符合這個格式才不會報錯<project-id>.<analytics_XXXXXXXXX>.<events_YYYYMMDD>
events後面YYYYMMDD可直接用"星號"取代，就不會被日期限制搜尋條件

附上官方欄位說明及範例，有需要開發最好整個摸熟，不要老是找東西複製
https://support.google.com/firebase/answer/7029846?hl=zh-Hant
https://support.google.com/analytics/answer/9037342?hl=zh-Hant&ref_topic=7029512

接下來是關鍵了

通常行銷報表需要一些欄位來判斷成果

參考了一下網路上的文章，行銷公司的報表，應該會有以下幾個必備欄位
人數、瀏覽次數、平均停留時間、平均單次瀏覽頁面、跳出率、離開率、瀏覽時段、瀏覽地區

首先我們玩玩看DAU

人數 => N 天活躍使用者

來這邊給你複製
SELECT
COUNT(DISTINCT user_id) AS n_day_active_users_count
FROM
-- 請換成您的表格名稱。
YOUR_TABLE.events_*
WHERE
event_name = 'user_engagement'
-- 請換成所需日期範圍。
AND _TABLE_SUFFIX BETWEEN 'YYYYMMDD' AND 'YYYYMMDD';

瀏覽次數 => 需要設定頁面名稱

SELECT
COUNT(event_timestamp) AS page_browse_count
FROM
YOUR_TABLE.events_*
UNNEST(event_params) AS param
WHERE
event_name = 'screen_view'
AND param.key = "firebase_screen_class"
AND param.value.string_value = "page name"
AND _TABLE_SUFFIX BETWEEN 'YYYYMMDD' AND 'YYYYMMDD';

需要特別注意的是event資料表中有非常多欄位都是event_params.XXXX，按照BigQuery語法需要作轉換才能使用也就是UNNEST(event_params) AS param
firebase_screen_class key對應的value是在埋點的時候設定的，雖然官方有說預設就會紀錄screen_view這個event，如果是沒有特別埋點紀錄的話，以Android來說就是會直接紀錄Class name

#平均停留時間(秒)

大概研究了一下發現只有使用firebase analytics提供的基本欄位設定可能不足以計算，以下文章有提到相關原因
https://www.stevenhi.xyz/2019/01/14/google-analytics-time-on-site/
https://www.yesharris.com/session-avg-time-error/

停留時間計算可能會需要再頁面增加額外的event log timestamp才能較精確的計算。以Android來說有life cycle可以配合計算，onCreate(起始時間) -> onStop or onDestroy(結束時間)，網頁的方式可以參考上面文章

平均單次瀏覽頁面(頁)

SELECT
(COUNT( param.key = "firebase_screen_id") )as dist_count
FROM live-streaming-player.analytics_203057437.events_*,
UNNEST(event_params) AS param
WHERE
event_name = 'screen_view' AND param.key = "ga_session_id"
Group by param.value.int_value

瀏覽時段

event_timestamp可直接使用

跳出率、離開率

Google定義
https://support.google.com/analytics/answer/2525491?hl=zh-Hant
每一個screen_view event都會有ga_session_id <= 算是每個工作階段 可以算成一次完整的使用
firebase_previous_class, firebase_screen_class參數可以把每個工作階段的頁面轉跳串起來，就可以計算離開、跳出率

大概是我太菜了，暫時沒有想到有什麼SQL語法可以直接把這個結果串好