GA/Firebase vs GBQ/Tableau Active Users Discrepancies

Question

I'm working on some App metrics and have trouble tying out 'Active Users' numbers from Google BigQuery to Firebase and Google Analytics. I'm also pulling data from GBQ into Tableau.

GBQ Query -

SELECT event_date, COUNT(DISTINCT user_id)
FROM `MyTable..events_*`, 
  UNNEST( event_params) as event
WHERE event_name in ('user_engagement')
GROUP BY  event_date
ORDER BY event_date ASC

I've also tried the N-th day Active Users query from Firebase Sample Queries.

/**
 * 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 --event_date,
  COUNT(DISTINCT user_id) AS n_day_active_users_count
FROM
  -- PLEASE REPLACE WITH YOUR TABLE NAME.
  `MyTable..events_*`
WHERE
  event_name = 'user_engagement'
  AND _TABLE_SUFFIX = '20191220';

The result I get when I run above query is 13,929 which matches with the number in Tableau. However, when I filter down to just that one day in either Firebase or Google Analytics dashboards, I get 17,726. You can see that in the table below too.

Tableau Logic - I created a calculated field called DAU like so,

{ FIXED [Event Date]: COUNTD(IF [event_name] = 'user_engagement' THEN [user_id] END)}

Tableau & GBQ yield the same numbers. However, I see different numbers in both Google Analytics & Firebase dashboards for 1-Day Active Users. I've gone through plenty of resources online but couldn't figure out why my numbers are way off than usual discrepancies due to timestamps and such.

I'm also fairly new to GA/Firebase and GBQ applications so I'm not quite sure what I've been doing wrong or if my interpretations are incorrect. Any thoughts would be a learning for me.

Thanks!

Victor Cid Victor Cid · Accepted Answer · 2019-12-27T10:29:28

As for the BigQuery N-Days active users query, I would suggest to build the exact same query structure as the example in order to have a similar behavior as the Firebase dashboard data:

The metrics in the report are relative to the last day in the date range you are using for the report. For example, if your date range is January 1 to January 28:

1-Day Active Users: the number of unique users who initiated sessions on your site or app on January 28 (the last day of your date range).

7-Day Active Users: the number of unique users who initiated sessions on your site or app from January 22 through January 28 (the last 7 days of your date range).

Unfortunately I don't have your exact same data to make some try & error testing but I would start trying something like that:

SELECT --event_date,
  COUNT(DISTINCT user_id) AS n_day_active_users_count
FROM
  -- PLEASE REPLACE WITH YOUR TABLE NAME.
  `MyTable..events_*`

WHERE
  event_name = 'user_engagement'
  AND event_timestamp >
      UNIX_MICROS(TIMESTAMP_SUB(CURRENT_TIMESTAMP, INTERVAL 1 DAY))
  AND _TABLE_SUFFIX = '20191220';

In order to get 1-day active customers for 'user_engagement' event for this day '20191220'. Please note that a fixed day range is picked but no N-day interval is selected for the event in your last query.

Here's some further reference about _TABLE_SUFFIX pseudo column filtering.

GA/Firebase vs GBQ/Tableau Active Users Discrepancies

1 Answers