分类导航

程序问答发布时间：2022-06-01 发布网站：大佬教程 code.js-code.com

大佬教程收集整理的这篇文章主要介绍了Firebase 原始数据与 Google Analytics 不匹配，大佬教程大佬觉得挺不错的，现在分享给大家，也给大家做个参考。

如何解决Firebase 原始数据与 Google Analytics 不匹配？

开发过程中遇到Firebase 原始数据与 Google Analytics 不匹配的问题如何解决？下面主要结合日常开发的经验，给出你关于Firebase 原始数据与 Google Analytics 不匹配的解决方法建议，希望对你解决Firebase 原始数据与 Google Analytics 不匹配有所启发或帮助；

我正在使用 Bigquery 中来自 Firebase 数据导出的数据计算留存率。我使用了许多不同的方法，但是 Bigquery 中的数字总是比 Google Analytics 高得多。如何在 Google Analytics（分析）中找到数字？

Google Analytics 中报告的时区是 UTC，因此无需调整时区。

首先，我尝试了自己的方法：

WITH 
users as
(
  SELECT
    user_pseudo_ID,platform,DATE(timestAMP_MICROS(MIN(user_first_touch_timestamp))) as install_dt,array_agg(disTinct DATE(timestAMP_MICROS(event_timestamp))) as days
  FROM `project.analytics_xxx.events_*`
  WHERE _table_SUFFIX >= '20210120' 
    AND DATE(timestAMP_MICROS(user_first_touch_timestamp)) >= '2021-01-20'
    AND (SELECT value.int_value FROM UNnesT(event_params) WHERE key = 'engaged_session_event') = 1
  GROUP BY 1,2
)
SELEct 
  install_dt,count(*) as installs,AVG(IF(date_add(install_dt,interval 1 day) in UNnesT(days),1,0)) as ret_d1,interval 2 day) in UNnesT(days),0)) as ret_d2,interval 3 day) in UNnesT(days),0)) as ret_d3
from users
group by 1,2
order by 1,2

然后我从这篇文章中尝试了这个查询： https://medium.com/firebase-developers/retention-and-app-remove-analysis-using-goo[…]le-analytics-for-firebase-bigquery-and-data-studio-c96653c8d379

WITH
events AS 
(
  SELECT *
  FROM `project.analytics_xxx.events_*`
  WHERE _table_SUFFIX >= '20210110' 
),first_open AS 
(
  SELECT
    DATE(timestAMP_MICROS(event_timestamp)) AS first_open_dt,user_pseudo_ID
  FROM events
  WHERE event_name = "first_open" 
),active AS 
(
  SELECT disTinCT 
    DATE(timestAMP_MICROS(event_timestamp)) AS active_dt,user_pseudo_ID
  FROM 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)
ORDER BY 1,2