Utilizing GA4 Data Stored in BigQuery with PowerBI Integration

[Xavier]

Yes, you are trying to transform your current Google Analytics 4 (GA4) data from BigQuery, currently in JSON format, into a more readable Table format for usage in Power BI. The idea here is that you need to “unnest” and “pivot” your current data into separate columns. This is unfortunately not a straightforward process if you’re new to these tools and to the JSON format, but it’s a common need in data analysis. The operation you are trying to perform is called “normalizing” the data, i.e., transforming the nested JSON-format data into flat, table-format data. This process often involves writing queries or scripts to properly extract and restructure your JSON data. You may need further tutorials or to seek advice from more experienced data analysts.

Whether you can accomplish this task with Power BI alone or need the aid of BigQuery depends on what level of data manipulation Power BI supports. It may also be possible to solve this by using other tools or programming languages that can work with JSON and will allow you to reshape data, such as Python with pandas. If the structure of your JSON data is always the same, you should be able to automate this process once you’ve successfully done it once.

[Morgan]

Based on the format of your current table in BigQuery and your desired table, it seems like you are trying to unnest JSON into individual columns. To achieve this, you will need to use some SQL functionality.

Firstly, extracting JSON data into columns in BigQuery can be done using the JSON_EXTRACT_SCALAR function in SQL. The function requires two string-type parameters – the JSON string and the JSONPath query.

For instance, you can create a view in BigQuery that extracts the parameters from device, geo, and traffic_source and represents them as separate columns. The code will look similar to this:

SELECT
event_name,
JSON_EXTRACT_SCALAR(device, ‘$.v.f[0].v’) AS device_type,
JSON_EXTRACT_SCALAR(device, ‘$.v.f[1].v’) AS device_brand,
— Add more fields as needed with correct path to JSON fields
JSON_EXTRACT_SCALAR(geo, ‘$.v.f[0].v’) AS geo_continent,
JSON_EXTRACT_SCALAR(geo, ‘$.v.f[1].v’) AS geo_sub_continent,
— Add more fields as needed
FROM your_table;

In the sample SQL code, event_name is a given column, the other columns are extracted from the JSON inside device and geo.

Please note that this assumes a consistency in the order of the nested JSON data. If the order isn’t consistent, you might run into issue. Also, you need to replace ‘your_table’ with the name of your table.

Once you have this setup, querying the data in PowerBI should be pretty straightforward. From PowerBI’s navigation menu, go to ‘Get Data’ and select BigQuery as your data source. Then, use Power BI’s interface to write the SQL query, or select the view you created earlier, and load the data.

Finally, JSON data and its extraction can prove tricky and you might encounter some adjustments to be made according to the specific structure of your JSON data. But this should provide a decent start. If there are still issues, it might be worth investing time in understanding JSON structure more profoundly or consider reaching out for expert help.