GA4 (Google Analytics) in Big Query - unnesting.
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
-
Bigquery query syntax has methods that allow you to access nested data. Here is their documentation with examples:
For example, say you have a column in a row and the value is a string with nested JSON/Object that looks like this:
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.
1
Answers
-
Bigquery query syntax has methods that allow you to access nested data. Here is their documentation with examples:
For example, say you have a column in a row and the value is a string with nested JSON/Object that looks like this:
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.
1 -
@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.0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 297 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 614 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 729 Beast Mode
- 53 App Studio
- 40 Variables
- 678 Automate
- 173 Apps
- 451 APIs & Domo Developer
- 46 Workflows
- 8 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 121 Manage
- 118 Governance & Security
- Domo Community Gallery
- 32 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive