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

问题描述

我已将我的 Firebase 项目与 BigQuery 集成.现在，我在尝试获取 1 天活跃用户时遇到数据差异问题，针对所选日期，即 20190210，使用来自 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 分析仪表板上报告的同一日期的数据不一致.知道这里可能出了什么问题吗?

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屋！