Calculating engagement time in BigQuery using both user_engagement and screen_view events from Firebase

user_10921516 0
Viewed 1187

I have a question regarding this post, which describes how to calculate the engagement time by summing up engagement_time_msec parameters in BigQuery: https://firebase.googleblog.com/2018/12/new-changes-sessions-user-engagement.html. The code is included in the article.

I've previously calculated engagement time based only on the user_engagement event, which corresponds to the engagement time reported in Firebase Analytics. However, if I try implementing the changes where I look for both user_engagement and screen_view events with the engagement_time_msec (as described in the article), I clearly get duplicate measures, because engagement time rises to the double. I get correct measures if I only use the user_engagement event or only the screen_view event, although there are slight discrepancies between the two, and screen_view does not always contain the engagement_time_msec parameter.

The article says the changes should be in effect from April 2020. Does anyone know whether this is correct? And are you using both events or just one of them?

firebase-analytics google-bigquery
1 Answers

The changes are in place. User engagements are spread across a variety of events and the correct way to get user engagements is to query for the values stored amongst the event_params, not only in the user_engagement-events. If you don't, you'd be under-estimating users' time in the app.

Technically speaking, the event_params is a struct in BigQuery where variable names (keys) are paired with values according to their format (strings, ints, floats, doubles, and "timestamps"). The engagement time is given in milliseconds as an integer, and we therefore want the value.int_value. To access the data we UNNEST() the struct. If you are not fluent in unnesting, this article on unnesting and working with structs in bigquery most helpful.

This more general, but very thorough tutorial, treats querying Firebase and Google Analytics data in general.

My commented template-code looks like this:

SELECT
    user_pseudo_id,                           /* The user-ID */
    event_name,                               /* */
    TIMESTAMP_MICROS(event_timestamp) AS ts,  /* Convert into actual time-stamp */
    params.value.int_value / 1000 AS secs,    /* Extract milliseconds from unnested struct */
FROM `proj.analytics_123456789.events_*`,     /* Star the table suffixes to query multiple days */
     UNNEST(event_params) AS params           /* Unnest the event_params to make every event-line
                                                 appear as duplicates for each "line" in the 
                                                 event_params-struct */
WHERE _TABLE_SUFFIX                           /* Query multiple tables… */
BETWEEN '20200101' AND                        /* …from 2020-01-01 … */
FORMAT_DATE("%Y%m%d", CURRENT_DATE())         /* …until today */
AND params.key='engagement_time_msec'         /* Select only the unnested rows where an
                                                 engagement_time int-value is stored in params */

You should see engagement times spread over screen_view, first_open, and app_exception in addition to the user_engagement-events of old.

user_49040950
Related