- Google Analytics 4

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!

Li