GA4 (Google Analytics) in Big Query - unnesting.
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
- 10.6K All Categories
- 8 Connect
- 918 Connectors
- 250 Workbench
- 477 Transform
- 1.8K Magic ETL
- 69 SQL DataFlows
- 478 Datasets
- 218 Visualize
- 260 Beast Mode
- 2.1K Charting
- 12 Variables
- 19 Automate
- 356 APIs & Domo Developer
- 89 Apps
- 3 Workflows
- 20 Predict
- 5 Jupyter Workspaces
- 15 R & Python Tiles
- 249 Distribute
- 65 Domo Everywhere
- 243 Scheduled Reports
- 21 Manage
- 42 Governance & Security
- 191 Product Ideas
- 1.2K Ideas Exchange
- 11 Community Forums
- 27 Getting Started
- 14 Community Member Introductions
- 55 Community News
- 4.5K Archive