How to deal with string, integer and float GA4 event parameter values in BigQuery using the coalesce function
In this tutorial I will show you - step by step - how to use the SQL coalesce function to collect the event_params values from multiple columns with different data types.
If you are wondering why the ga_session_id in the field event_params.value.string_value is missing: welcome to the club. It is one of the most asked questions regarding the GA4 BigQuery export. The answer? Scroll to the right.
Depending on its data type, every event parameter value in the GA4 export data is placed in one of these four columns:
event_params.value.string_value: text valuesevent_params.value.int_value: non-decimal numeric valuesevent_params.value.float_value: decimal numeric valuesevent_params.value.double_value: decimal numeric values with more precision
All other data types will most likely end up in the string column.
Get an overview of all event_params value types
To see the data types of your the GA4 event_params values you can use a query like this:
select
event_name,
params.key as event_parameter_key,
case when params.value.string_value is not null then 'string'
when params.value.int_value is not null then 'int'
when params.value.double_value is not null then 'double'
when params.value.float_value is not null then 'float'
else null end as event_parameter_value
from
-- change this to your google analytics 4 export location in bigquery
`ga4bigquery.analytics_250794857.events_20210401`,
unnest(event_params) as params
group by
event_name,
event_parameter_key,
event_parameter_value
order by
event_name,
event_parameter_keyLet's say we need multiple parameter values from the page_view event, but we are not sure which column to select. Of course we can look up our event using the query above or open the preview tab:
If those two options don't work for you and you want to deal with these values in a more scalable way: consider using the coalesce function. In this tutorial I will show you how to do that.
