How to Correctly Work With Google Analytics Data Types in BigQuery

Follow me on LinkedIn - AI, GA4, BigQuery

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.

So, when writing SQL queries for calculating engaged sessions, it is important to account for both data types.

I recommend going one step further and using the query logic that ensures that the value will always be extracted correctly, regardless of the data type used in GA4 BigQuery. You can do that by extracting the first non-null value from the four data types.

This query logic will handle cases where GA4 BigQuery fields can exist in multiple data types.

This approach not only avoids underreporting but also makes your queries future-proof if Google changes how GA4 stores event parameter types.

The objective is to eliminate the need to check the data type used to extract a particular field's value in GA4 BigQuery.

I teach you such logic in my GA4 BigQuery course, where you can learn to automate SQL generation via text prompts in ChatGPT.

There could be ‘N’ use cases, and it won’t be possible to provide ready-made SQL code for every possible case.

So, it is better that you understand the logic to scale SQL generation to the moon.

The future belongs to those who can query raw GA4 data in BigQuery, apply SQL logic, automate SQL generation via AI and drive insights beyond what GA4 UI can offer.

GA4’s event ingestion process doesn’t predefine strict data types for event parameters.

Differences in data type can result from changes in client-side GTM implementation, Google Tag script updates, or even server-side tagging.

For example, if one version of GTM sends ‘1’ as a string and another version sends 1 as an integer, you will see variability in BigQuery.

When working with GA4 BigQuery data, always assume that event parameters can be stored in different data type columns (string_value, int_value, float_value, double_value) across rows.

The screenshot below shows before and after accounting for all possible data types while calculating various metrics:

account for all possible data types in ga4 bigquery

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.

For example,

So, when writing SQL queries for calculating engaged sessions, it is important to account for both data types.

GA4’s event ingestion process doesn’t predefine strict data types for event parameters.

Related Articles:

Himanshu Sharma