Google Analytics 4 - Json Transformation

[denisfi]

Hi guys,

Since few days ago, google analytics brings some challenges for me and I’d like to share with you.

The result about a request made to GA4 brings a Json splitted into some parts (dimensionsHeader, metricsHeader, rowsHeader, row count, metadata and kind) at once result.

If I use Json to Table node, I can split it into some columns as Json too

All I need it as a single table, where dimensions + metrics will be the header, the rows will be the content, but I don’t figure it out to transform it in just few nodes or have to rename columns to identify each one. (hard work)…

Do you know a simple way with json nodes to be correctly set to solve it? I try many many ways and didn’t match it.

Here the original sample result from the request.

GA4-result.json (26.0 KB)

KNIME_project_ga4json.knwf (72.4 KB)

The header will be as the values sequences from the Json at all and the table should be like it:

I’ve different needs for the report, and all will be result as Json and need to convert it as simple convert from the data inside.

Any ideas?

Thanks, Denis

[ArjenEX]

Hi @denisfi

If I were you I would just use one JSON Path node and one Ungroup node. Query the columns by its specific location within the JSON and name it accordingly. Example:

defaultChannelGroup = $['rows'][*]['dimensionValues'][0]['value']
firstUserSourceMedium = $['rows'][*]['dimensionValues'][1]['value']
etc.

It’s a one time job that took me two minutes maximum, unless the file that you retrieve changes frequently in format.

Generated output:

[denisfi]

HI @ArjenEX ,

Thanks for you advices, But I haven’t a way that the knime pick up the first and second block to name de columns, and the rows just to complete the values?

As I told before, I’ve some reportes and I’d like that a trick way can put the header and the content at once without make one-by-one.

If I haven’t other way, OK, I can do it as you told me. From my example, I could bring the dimensionsHeader and metricHeader separated and after that, combine it together; but how can I merge the header from a path with the content from other where they will stay at the same positions (from the origin) and create a table? Do I make me clear? An automation process to extract and create the table with the existing values.

Tks again, Denis

[ArjenEX]

In that case, you could use something like this:

This will automatically pick-up the content of dimensionHeaders and metricHeaders and insert them as columns headers on the top flow that handles the extractions of all values.

See WF:
Automatic JSON header insert.knwf (60.9 KB)