Firebase与BigQuery活动用户差异 [英] Firebase vs BigQuery Active Users Discrepancies

问题描述

我已经将 Firebase 项目与 BigQuery 集成在一起.现在，我在尝试获取所选日期(即 20190210 )的 1天活跃用户时遇到数据差异问题，并从 BigQuery ;

I've integrated my Firebase project with BigQuery. Now I'm facing a data discrepancy issue while trying to get 1 day active users, for the selected date i.e. 20190210, with following query from BigQuery;

SELECT COUNT(DISTINCT user_pseudo_id) AS 1_day_active_users_count
FROM `MY_TABLE.events_*`
WHERE event_name = 'user_engagement' AND _TABLE_SUFFIX = '20190210'

但是从 BigQuery 返回的数字与在同一日期在 Firebase Analytics(分析)信息中心上报告的数字不匹配.任何线索，这里可能出什么问题了?

But the figures returned from BigQuery doesn't match with the ones reported on Firebase Analytics Dashboard for the same date. Any clue what's possibly going wrong here?

以下示例查询提到了我的Firebase团队，在这里 https ://support.google.com/firebase/answer/9037342?hl = zh_CN& ref_topic = 7029512 并没有帮助，因为它考虑了当前时间并相应地吸引了用户.

The following sample query mentioned my Firebase Team, here https://support.google.com/firebase/answer/9037342?hl=en&ref_topic=7029512, is not so helpful as its taking into consideration the current time and getting users accordingly.

N-day active users
/**
 * Builds an audience of N-Day Active Users.
 *
 * N-day active users = users who have logged at least one user_engagement
 * event in the last N days.
*/
SELECT
  COUNT(DISTINCT user_id) AS n_day_active_users_count
FROM
  -- PLEASE REPLACE WITH YOUR TABLE NAME.
  `YOUR_TABLE.events_*`
WHERE
  event_name = 'user_engagement'
  -- Pick events in the last N = 20 days.
  AND event_timestamp >
      UNIX_MICROS(TIMESTAMP_SUB(CURRENT_TIMESTAMP, INTERVAL 20 DAY))
  -- PLEASE REPLACE WITH YOUR DESIRED DATE RANGE.
  AND _TABLE_SUFFIX BETWEEN '20180521' AND '20240131';

推荐答案

因此，鉴于此处的微小差异，我认为问题是时区之一.

So given the small discrepancy here, I believe the issue is one of timezones.

当您在Firebase控制台中查看一天"时，您正在查看的时间是从午夜到午夜的时间间隔，该时间间隔是在您首次设置项目时指定的任何时区.当您在BigQuery中查看一天"时，您正在查看UTC中从午夜到午夜的时间间隔.

When you're looking at a "day" in the Firebase Console, you're looking at the time interval from midnight to midnight in whatever time zone you've specified when you first set up your project. When you're looking at a "day" in BigQuery, you're looking at the time interval from midnight to midnight in UTC.

如果要确保您正在查看与控制台中的事件匹配的事件，则应查询BigQuery表中的event_timestamp值(并记住它可能跨越多个表)以与事件匹配在您的时区.

If you want to make sure you're looking at the events that match up with what's in your console, you should query the event_timestamp value in your BigQuery table (and remember that it might span multiple tables) to match up with what's in your timezone.

这篇关于Firebase与BigQuery活动用户差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！