1

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'

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?

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';
Ammar
  • 1,811
  • 5
  • 26
  • 60
  • How far off are they? What are the two values you're seeing in each one? – Todd Kerpelman Mar 08 '19 at 17:24
  • @ToddKerpelman When I run query, first mentioned above, on **BigQuery Query editor** I got **1_day_active_users_count = 44755** but **Firebase Analytics Dashboard** says it's **44520** for the same date i.e. **20190210** – Ammar Mar 14 '19 at 06:23
  • Hi @Ammar Did you find out why have the different between Firebase dashboard and BigQuery result? – Lê Quang Bảo Oct 10 '19 at 04:06

1 Answers1

1

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

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.

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.

Todd Kerpelman
  • 16,875
  • 4
  • 42
  • 40
  • *you should query the event_timestamp event in your BigQuery table* I believe you mean the **event_timestamp** column **value** and not the **event**. I just tested, this is not the case. E.g. the first **event_timestamp** value for **20190210** is **1549738800672000** which translates to **Saturday, February 9, 2019 19:00:00.672**. It means the stored timestamp in **BigQuery is with respect to my timezone i.e. +5**. – Ammar Mar 20 '19 at 07:26
  • Ah, that is weird. If that's the very first event_timestamp value you're seeing in your table, you should probably talk to the support team with the query you used. They might be able to figure out what's going on. – Todd Kerpelman Mar 21 '19 at 20:17