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 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 to the rescue
According to the BigQuery documentation, the
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
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.