GA4 (Google Analytics) in Big Query - unnesting.

cpbwg
cpbwg Member
edited January 2023 in Connectors

Anyone have any insights or best practices for un-nesting Google Analytics 4 data inside of Big Query for analysis in Demo? The nested data structure needs to unnesting in tools like Tableau, so wondering if anyone here has connected BigQuery GA4 data to DOMO, and how they are going about it.

Best Answer

  • rileystahura
    rileystahura Contributor
    edited March 2023 Answer ✓

    @cpbwg

    Bigquery query syntax has methods that allow you to access nested data. Here is their documentation with examples: https://cloud.google.com/bigquery/docs/reference/standard-sql/arrays

    For example, say you have a column in a row and the value is a string with nested JSON/Object that looks like this:

    image.png

    Your query in the Domo connector settings would include something that looks like this:

    SELECT e.key,e.value.string_value , aa.* 
FROM ‘your-table’ as aa,
UNNEST(even_params) as e
WHERE (include whatever condition you want)

    And the result would be that those nested values are extracted and become new columns.

Answers

  • rileystahura
    rileystahura Contributor
    edited March 2023 Answer ✓

    @cpbwg

    Bigquery query syntax has methods that allow you to access nested data. Here is their documentation with examples: https://cloud.google.com/bigquery/docs/reference/standard-sql/arrays

    For example, say you have a column in a row and the value is a string with nested JSON/Object that looks like this:

    image.png

    Your query in the Domo connector settings would include something that looks like this:

    SELECT e.key,e.value.string_value , aa.* 
FROM ‘your-table’ as aa,
UNNEST(even_params) as e
WHERE (include whatever condition you want)

    And the result would be that those nested values are extracted and become new columns.

  • nicofernandez
    nicofernandez Member

    @cpbwg
    @rileystahura

    The best way to import the full GA4 raw dataset into DOMO is using the Google BigQuery Service Connector (if you've already linked your GA4 account to BQ before and have the export ready - if you haven't done so, then you must use the API with it's limitations).

    If you try to import the params or properties without any transformation in the Connector Settings > Query, it will return a format very hard to work with:

    [FieldValue{attribute=RECORD, value=[FieldValue{attribute=PRIMITIVE, value= …

    As mentioned above, you can UNNEST() the entire column (be aware that's a CROSS JOIN) or a single key into column but if you have a very complex custom implementation that won't be enough.

    What I've done in order to work with the Struct REPEATED RECORDS (BigQuery GA4 Schema) in a format that I can use in DOMO was to first parse the Struct into an ARRAY_AGG concatenating every key value, and last using TO_JSON_STRING. The final output is a column with JSON format. What I do last is to use a Python script in the Magic ETL to parse the JSON into single columns. As the implementation I work with has over 50 params and 20 properties (also items), is the best I've figured out.

Categories