Firebase BigQuery学习笔记

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语法可以直接把这个结果串好