Discrepancies on "active users metric" between Firebase Analytics dashboard and BigQuery export
<p>According to Firebase Analytics docs (<a href="https://support.google.com/firebase/answer/6317517#active-users" rel="noreferrer">https://support.google.com/firebase/answer/6317517#active-users</a>), the active number of users is the number of unique users who initiated sessions on a given day. Also according to the docs, every time a session is started an event with <code>session_start</code> name is sent. I am trying to get that metric using BigQuery's export, but my query is giving me different results (15636 on BigQuery, 14908 on FB analytics)</p>

<p>I have also tried converting to different timezones to see if that might be the issue, but no matter which timezone I try I never get the same (or similar) results</p>

<p>Which query should I run to get the same results I get on Firebase Analytics dashboard for active users?</p>

<p>My query is </p>

<pre class="lang-sql prettyprint-override"><code>SELECT EXACT_COUNT_DISTINCT(user_dim.app_info.app_instance_id)
FROM table_date_range([XXXXX.app_events_], timestamp('2016-11-26'), timestamp('2016-11-29')) 
WHERE DATE(event_dim.timestamp_micros) = '2016-11-27' 
AND  event_dim.name ='session_start'  
</code></pre>

<p>Thanks</p>

<p><strong>Update</strong></p>

<p>After <strong>@djabi</strong>'s answer I changed my query to use <code>user_engagement</code> rather than <code>session_start</code> and it works much better now. Still some minor differences though (they range from under ten to under 50 out of 16K, depending on the date).</p>

<p>I have tried once again using different timezones by playing around with <code>DATE(date_add(event_dim.timestamp_micros,1,'hour'))</code> but I never got the exact number I get on Firebase Analytics dashboard. </p>

<p>The new numbers are good enough to be considered statistically acceptable, but wondering if anyone has a suggestion to improve the query and get exact results?</p>

<p>The current query is:</p>

<pre class="lang-sql prettyprint-override"><code>SELECT
  COUNT(*) AS active_users
FROM (
  SELECT
    COALESCE(user_dim.user_id, user_dim.app_info.app_instance_id) AS user_id
  FROM
    TABLE_DATE_RANGE([XXXXX.app_events_], TIMESTAMP('2016-11-24'), TIMESTAMP('2016-11-29'))
  WHERE
    DATE(event_dim.timestamp_micros) = '2016-11-25'
    AND event_dim.name ='user_engagement'
  GROUP BY
    user_id )
</code></pre>

<p>Note: At the moment we are not sending user_id, so the <code>COALESCE</code> will always return the <code>app_instance_id</code>, in case anyone was going to suggest that could be the problem</p>