Learn business growth with Google Analytics 4 Forums Google Analytics 4 Understanding SQL Server parsing of GA4 BigQuery RECORD columns

  • Understanding SQL Server parsing of GA4 BigQuery RECORD columns

    Posted by Isaiah on 17 June 2023 at 9:36 am

    I’m using a Python script to pull daily GA4 event files from BigQuery and save them to a SQL Server DW. It’s mostly easy because the columns are Strings or Integers. But there are 11 fields of type RECORD which are kind of a headache. According to Google, RECORDS are storable as STRUCT type in GoogleSQL and sometimes show up as arrays. Have a look at these samples of ‘event_params’ data, which is an array of STRUCT types, and ‘device’ data, which is a STRUCT. Can anyone show me a SQL function that can parse STRUCTS or arrays of STRUCTS? Is there a way to cast this data as JSON so I can use the built-in JSON functions to query it?

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

    Member
    9 July 2023 at 2:37 am

    Alright! You can use the UNNEST function in BigQuery, which allows you to flatten array data into a table. Here’s an example using the ‘event_params’:

    `
    SELECT
    EP.key AS event_param_key,
    COUNT(*) AS occurrences
    FROM
    — Replace the table name with your table
    bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*,
    UNNEST(event_params) AS EP
    WHERE
    — Replace with your date range
    _TABLE_SUFFIX BETWEEN ‘20201201’ AND ‘20201202’
    GROUP BY
    event_param_key
    ORDER BY
    event_param_key ASC;
    `

    In this SQL query, each record in the ‘event_params’ array is transformed into a row in the ‘EP’ table, which you can then query as usual.

    For more details, have a look at [this article](https://medium.com/firebase-developers/using-the-unnest-function-in-bigquery-to-analyze-event-parameters-in-analytics-fb828f890b42) which explains how to use the UNNEST function in depth when analyzing event parameters in Analytics.

  • Oliver

    Member
    9 July 2023 at 8:37 am

    In BigQuery, you can use functions like UNNEST to handle arrays of STRUCTs and extract their individual elements. However, if you’d prefer to convert the STRUCTs data into JSON format, BigQuery also allows this by using the TO_JSON_STRING function. Then, you can easily parse and analyze your data using BigQuery’s built-in JSON functions.

Log in to reply.