Tutorial: How to deal with string, integer and float event parameter values using the coalesce function (GA4)
3 min read

Tutorial: How to deal with string, integer and float event parameter values using the coalesce function (GA4)

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.
Tutorial: How to deal with string, integer and float event parameter values using the coalesce function (GA4)

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.

Trust me, you're not alone.

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 values
  • event_params.value.int_value: non-decimal numeric values
  • event_params.value.float_value: decimal numeric values
  • event_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_key

Let'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.

Coalesce to the rescue

According to the BigQuery documentation, the coalesce function:

Returns the value of the first non-null expression. The remaining expressions are not evaluated. An input expression can be any type. There may be multiple input expression types. All input expressions must be implicitly coercible to a common supertype. (source)

Let's try that.

select 
coalesce(null,1,2) as example

Example will return 1, because the first expression is skipped (because it is a null value) and the last one is not evaluated.

When we apply this logic to our GA4 data set, we ask BigQuery to figure out where the event_params values are hiding. We know that only one of the four possible expressions is a non-null expression. Translated to SQL it look like this:

select
    event_name,    
    key,
    coalesce(value.string_value,value.int_value,value.float_value,value.double_value) as value
from
    `ga4bigquery.analytics_250794857.events_20210401`,
    unnest(event_params) as event_params

Oops!

We triggered an error. BigQuery detected that the value columns contain different data types. Remember what the documentation stated? Although 'there may be multiple input expression types', we have to make sure that 'all input expressions must be implicitly coercible to a common supertype'.

This means that we have to choose one data type and force all values into that data type. Looking at the significant amount of string data in our GA4 export, the outcome of this decision is obvious.

Let's try again:

select
    event_name,    
    key,
    coalesce(value.string_value,cast(value.int_value as string),cast(value.float_value as string),cast(value.double_value as string)) as value
from
    `ga4bigquery.analytics_250794857.events_20210401`,
    unnest(event_params) as event_params

Et voilĂ ! All values in the same column.

Downsize of this approach is that you have to convert some values back to integer, float or double when aggregating data later on. But if you're comfortable with some data wrangling, that's probably worth the effort. In the end it all depends on your preferences.

Now it's your turn!

I hope you've enjoyed this tutorial and feel a bit more confident to utilise your own Google Analytics data in BigQuery. Please let me know if you have any feedback or suggestions to improve the quality of the content.


Enjoying this post? Subscribe to the GA4BigQuery newsletter!

You will receive regular updates (max 1x/week) straight in your inbox. You can unsubscribe anytime.