iT邦幫忙

1

Firebase BigQuery學習筆記

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

正文開始

首先Firebase專案必須先關聯上BigQuery,我自己實測大約隔天開始才會有值
https://ithelp.ithome.com.tw/upload/images/20200430/20126774MLk3HqiKk0.png

切換到GCP BigQuery,關聯上並且有資料後,在左側選單會有analytic的子項,Firebase Analytics的Event似乎都是存放在這裡,Event會以日期為單位切成Table,也可以直接從預覽看到Table的資料
https://ithelp.ithome.com.tw/upload/images/20200430/20126774p8xLGsNPJc.png

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

附上官方欄位說明及範例,有需要開發最好整個摸熟,不要老是找東西複製
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';
https://ithelp.ithome.com.tw/upload/images/20200430/20126774dkVlRTviA4.png

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

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
https://ithelp.ithome.com.tw/upload/images/20200430/20126774xXQ3mwZmKO.png

#平均停留時間(秒)

大概研究了一下發現只有使用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語法可以直接把這個結果串好
https://ithelp.ithome.com.tw/upload/images/20200430/20126774G9aFwy9NcN.png


尚未有邦友留言

立即登入留言