用BigQuery计算GA/FB留存

说明

这里仅提供一种通过BigQuery获取GA/FB留存的方法——

一定有人会问，GA/FB不是已经整理好图表显示留存信息了吗？为什么还要多此一举通过sql查询呢？

原因如下：

GA和FB显示的留存并未给出具体计算方法，其中新用户定义/用户第一日起止时间等信息并未明确。导致进行其他更细致的数据分析时，如复核留存，则会发现计算数据总与面板给出的总是有些许偏差，无法确定是小数取舍问题，还是数据选取问题。

故如能确定原始数据留存计算的方法，则可以给后续的数据分析工作提供一个与平台一致的、明确的基准。这给了你就数据准确性和别人扯皮时的底气和治疗强迫症的良方。

方法

直接给出sql：

WITH 
first_open AS (
 SELECT DATE(TIMESTAMP_MICROS(event_timestamp),"Asia/Hong_Kong") AS first_open_dt, 
user_pseudo_id
 FROM `YourAppChartID.events_*` 
WHERE  
 event_name = "first_open" 
AND geo.country <> "China"
 AND app_info.version = "1.0.20"
-- AND user_pseudo_id in (
--SELECT user_pseudo_id
--FROM `YourAppChartID.events_*` 
--WHERE event_name like '%YourDefineData%')
),
active as (
 SELECT DISTINCT DATE(TIMESTAMP_MICROS(event_timestamp),"Asia/Hong_Kong") AS active_dt, 
user_pseudo_id
 FROM `YourAppChartID.events_*` 
WHERE 
event_name <> "app_remove" 
), cohort_size AS (
 SELECT first_open_dt, 
COUNT(DISTINCT first_open.user_pseudo_id) AS users
 FROM first_open
 GROUP BY 1
)
 SELECT first_open.first_open_dt AS first_open_dt, 
DATE_DIFF(active.active_dt, first_open.first_open_dt, DAY) AS days, 
cohort_size.users AS cohort_users, 
COUNT(DISTINCT active.user_pseudo_id) as retained,
 SAFE_DIVIDE(COUNT(DISTINCT active.user_pseudo_id), cohort_size.users) AS retention_rate
 FROM first_open
 LEFT JOIN cohort_size
 ON first_open.first_open_dt = cohort_size.first_open_dt
 LEFT JOIN active
 ON first_open.user_pseudo_id = active.user_pseudo_id
 GROUP BY 1,2,3 HAVING days IN (1,3,7,30)
 ORDER BY 1,2 ASC;

替换App的数据集名字即可使用。

 SELECT DATE(TIMESTAMP_MICROS(event_timestamp),"Asia/Hong_Kong") AS first_open_dt, 
user_pseudo_id

其中时区选择（”Asia/Hong_Kong”）根据GA/FB后台设置的时区，或你想要分析的基准填写。

--WHERE event_name like '%YourDefineData%')

这部分如未根据事件名划分用户则可以删去。

---

经2021年1月后三个app数个大小版本的验证，该方法计算出的留存完美符合平台给出的留存。