When working with Google Analytics 4 data in BigQuery, always assume that any event parameter can be stored in multiple data type columns. Account for all possible data types when querying.
You could be underreporting GA4 ‘Engaged Sessions’ in BigQuery and likely making mistakes while using all calculated metrics.
One of the most annoying aspects of writing SQL queries is ensuring that you extract the values of a field with the correct data type.
A single event parameter can have a value stored in one of the following four data types: string, integer, float, or double.
It is never the case that the value is stored in two or more data type columns at the same time.
However, there is always a possibility that the value of an event parameter can be stored in different data-type columns across different rows.
This is a direct result of how GA4 processes and sends event data to BigQuery using a “schema-on-read” approach, where field types can change based on the payload.
For example,
The value of the ‘session_engaged’ parameter is typically stored as a string value and often stored as ‘1’ for engaged sessions and ‘0’ for non-engaged sessions.
But sometimes, the value of the ‘session_engaged’ parameter can also be stored as an integer value.
