Error GA4 import from BigQuery to PowerBI tables columns unnested values record types

[Syndicate_Admin]

Hello I am loading data of GA4 in PowerBI , no problem on connection and loading the data , but when accessing at columns tables there are some important problems regarding nested alues

1- Columns as event_params or user_properties needs to be unnested previously in Bigquery to create new columns with this data , it can be done with a simple sentence of SQL and once done does not represent any problem to load in PwBI.

 

 2- Other columns that as device or geo ( Record Types) that are not nested in BigQuery ( it´s not possible to unnest there) , in the table of Bigquery  are in different columns but  PowerQuery read as if were nested values , probably loading the schema instead of the table.

Not possible to unnest

Schema in BigQuery

Table Preview in BigQuery - geo values are split in columns

Table loaded in PowerQuery

Power Query does not load the individual columns of Geo data, instead loads only the Geo column of the schema.

I couldn´t  get those values with Json column split or other methods within PowerQuery.

Te question is HOW  can we make that PowerQuery loads the splitted columns in BigQuery table instead the schema of the table?

I´d appreciate any help !

Best regards

[Syndicate_Admin]

Hello all @experts, really there is nobody who has faced and/or resolved this issue ,  nobody is importing GA4 data from BigQuery?.. I think is a major issue of PowerQuery

Thank you!

[Syndicate_Admin]

Hi @Ckg_PBI I am currently working on GA4 data and importing the data into Power BI. You can't just connect the data into Power BI due to the nested colunms. I have needed to transform and model the data in Big query before importing it into Power BI. 

The first piece of code i used was this : 

SELECT new_event_params.*, events.* except (event_params)

from "name_of_ data_table" as events

left join UNNEST(event_params) as new_event_params

This unnests the event params column. However this is the first step i did and i have had to do alot of tranforming and modelling outside of Power BI to use the data. Also had to use the schedule queries function in BigQuery to make sure the saved queries actually refresh on a daily basis before pulling into Power BI. 

Some other considereation and useful pages i and used when working with GA4 data here- 

https://analyticscanvas.com/3-reasons-your-ga4-data-doesnt-match/ 

https://support.google.com/analytics/answer/7029846?hl=en 

https://support.google.com/analytics/answer/9037342?hl=en&ref_topic=9359001&sjid=8676522101072938706... 

https://towardsdatascience.com/how-to-query-and-calculate-ga-app-web-event-data-in-bigquery-a7793117... 

Hope that give you some light on the situtation. Really not a ideal method and does take a lot of time to work it. 

[Syndicate_Admin]

Hi @Ckg_PBI In this case as a workaround I think you can go directly from GA4 to PBI without using a DW. You can do that using for example an external connector. In our agency we use windsor.ai to pull GA4 data directly and once data comes to PBI we don't an issue like the one you have with BQ, because fields get pulled without those issues. In case you wonder, to make the connection first search for the GA4 connector in the data sources list:

After that, just grant access to your GA4 account using your credentials, then on preview and destination page you will see a preview of your G4 fields:

There just select the fields you need. Finally, just select PBI as your data destination and finally just copy and paste the url on PBI --> Get Data --> Web --> Paste the url.