"Average session time" coming up significantly different in bigquery vs firebase analytics

Question

Here is my firebase dashboard:

and here my code to calculate the same,

with masterdata as

(

select user_eng_time, session_len.user_pseudo_id
from
(SELECT avg(params.value.int_value) as user_eng_time, user_pseudo_id
  from `rayn-deen-app.analytics_317927526.events_*`, unnest(event_params) as params
  where (event_name = "user_engagement") and (params.key = 'engagement_time_msec') 
   
   AND DATE_TRUNC(EXTRACT(DATE from TIMESTAMP_MICROS(event_timestamp)), DAY)
   BETWEEN DATETIME('2023-03-22 00:00:00') AND DATETIME('2023-05-21 00:00:00')

  GROUP BY user_pseudo_id
) as session_len

)


select avg(user_eng_time/1000) from masterdata

this gives result = 97.2 (seconds)

My question is, why do the values differ on bigquery vs firebase analytics

Answer 1

It is valid behaviour, that numbers do not match. GA4 uses another approach to calculate data than BigQuery. GA4 uses unique count approximation via HyperLog+++ algorithm. On the other hand, in BigQuery you need to calculate everything on your own. Under some circumstances GA4 calculation can be simulated in BigQuery as well. But be aware, that this is very time consuming.

Google has released very nice article about it and how to get close to numbers shown in GA4.

Always ask why do you need this? What is the reason for such calculation? You can also think of an option to switching GA4 reporting identity to device-based, which will count only the measured data and modeled data will be excluded.