Implementing Multiple Filters for GA4 Event Parameters in BigQuery

Google Analytics 4

Implementing Multiple Filters for GA4 Event Parameters in BigQuery

Posted by Amelia on 2 July 2022 at 9:39 pm
Hey there, data whiz! So, I’ve got this GA4 thingy data sitting in BigQuery and I’ve got a bit of a brain-twister on my hands. I know – rejoice, right! – I can use unnest to pick some values from the event_params. But, I’m scratching my head here trying to figure out how to sift out based on multiple values, like for example, dimensions, at the same time. My case in point is I’ve got these two dimensions called page_path and previous_page_path. No sweat, I can scoop them up separately, like so:
```
SELECT 
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_path') AS page_path,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'previous_page_path') AS previous_page_path,
FROM project.dataset.events_<span class="hljs-operator">*</span>
```
Now here’s the kicker – when I attempt to bring in a Where to add more filtering to the mix, those darn alias names don’t fly and BigQuery gives me an error. Like for instance, when I tried this it hiccuped:
```
SELECT 
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_path') AS page_path,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'previous_page_path') AS previous_page_path,
FROM project.dataset.events_<span class="hljs-operator">*</span>
WHERE page_path = "/"
```
I’m sure there’s a way to crack it, but got no clue. You wouldn’t know how to do this, would you?

Alright, the end game is I wanna be able to sift out multiple event_params. I think it’s gonna involve multiple unnesting steps since key can’t hold two values at once. But, I’m stuck on how the query should look to pull this off. Any clues?
Li replied 1 year, 5 months ago 2 Members · 1 Reply
1 Reply

Li

Member
7 December 2022 at 10:02 am
No worries, I see what you’re getting at. It can get a bit tricky trying to handle multiple dimensions at once with BigQuery. The idea is to sift both ‘page_path’ and ‘previous_page_path’ out of the data, right? Here’s how you might fix that “hiccup”:

When filtering based on a dimension, for instance, ‘page_path’, you can do an UNNEST and then a subquery. Check this out:
```
SELECT 
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_path') AS page_path,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'previous_page_path') AS previous_page_path
FROM project.dataset.events_*
```
When you include the WHERE clause to filter for ‘page_path’ = “/”, you were on the right track, only you can’t directly access the alias in the WHERE clause. BigQuery wants you to jump through a hoop first. You should wrap the original query as a subquery, like so:
```
SELECT *
FROM (
  SELECT 
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_path') AS page_path,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'previous_page_path') AS previous_page_path
  FROM project.dataset.events_*
) 
WHERE page_path = "/"
```
It’s basically nesting those dimensions in another SELECT. This way you sidestep the restriction and can filter based on both dimensions ‘page_path’ and ‘previous_page_path’. All about syntax fun, right? Hope this helps level up your data mojo!

Implementing Multiple Filters for GA4 Event Parameters in BigQuery

Li