Learn business growth with Google Analytics 4 Forums Google Analytics 4 Troubleshooting Query for Obtaining 2 String Values in Google BigQuery

  • Troubleshooting Query for Obtaining 2 String Values in Google BigQuery

    Posted by Wyatt on 28 August 2022 at 7:02 am

    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!

    Sophia replied 11 months, 2 weeks ago 3 Members · 2 Replies
  • 2 Replies
  • Emma

    Member
    3 September 2022 at 12:55 pm

    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_id

    In 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.

  • Sophia

    Member
    19 January 2023 at 12:38 pm

    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.