-
Troubleshooting Query for Obtaining 2 String Values in Google BigQuery
Hey there,
I’m working with BigQuery GA4 export at the moment and there’s a small hiccup that I can’t seem to get past. I need to extract a couple of string values from it using a very specific table structure.
Here’s what the structure should look like:
The event name, event key, string value for event parameters, and an integer value for the same.
For instance, a ‘listing’ event will have a ‘product_id’ key and a corresponding string value. There are similar other events like ‘site_interaction’ and ‘ga_sessionid’ that follow this pattern.
The SQL query I’m running at the moment is looking something like this:
The output I was hoping for:
Product ID’s under ‘id’, count under ‘share’| product_id | share |
| — | — |
| 123456 | 4 |
| 123457 | 2 |
| 123458 | 2 |But I’m drawing a blank. I played around with the query a bit but still no luck.
Do you have any ideas on what could be going wrong here?
Anything you could suggest would be a huge help, thanks a ton!
-
2 Replies
-
It appears that you’re trying to unnest the same field ‘event_params’ twice in your query, which may contribute to the issue. If you’re instead trying to leverage information within the ‘event_params’ field, you can select these details after the unnest operation. Here’s an example of how you might structure your query, assuming each ‘listing’ event only has one ‘product_id’:
SELECT
params.key as product_id,
COUNT(*) as share
FROM
dataset,
UNNEST(event_params) as params
WHERE
event_name = ‘listing’
AND params.key = ‘product_id’
GROUP BY
product_idIn this query, ‘dataset’ should be replaced by your actual dataset and table names. The unnest operation ‘flattens’ the ‘event_params’. Following this, you filter events by ‘listing’ and aggregate by the ‘product_id’ key. The result will be the count of each product_id in ‘listing’ events.
-
There could be a few potential issues causing your current query to not return the expected result. The most likely one is that the ‘product_id’ and ‘site_interaction’ keys may not be present in the same event parameter. Unnesting the event_params columns will create a separate row for every key-value pair, and trying to join them on the same row later using a WHERE condition might result in an empty dataset if the conditions are not met.
Here, you might want to consider utilizing a CASE WHEN in an aggregated function to help in isolating the respective keys. Doing so would mean unnesting the array only once but testing which specific key we’re handling in each situation.
For example:
`
SELECT
MAX(CASE WHEN ep.key = ‘product_id’ THEN ep.value.string_value END) as product_id,
COUNT(1) as share
FROM
‘dataset’,
UNNEST(event_params) as ep
WHERE
event_name = ‘listing’
GROUP BY
product_id
`This will first find the ‘product_id’ for each ‘listing’ event, then count how many times each product_id appears in those events. This seems to align with your required output, but please adjust as needed based on your exact dataset and requirements.
Log in to reply.