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

[Ckg_PBI]

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

[QueryWhiz]

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. 

[Kieran_Ultra_23]

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. 

For the geo location i used this code to create a Dim table with the user_pseudo_id as a primary key to link to the other tables.

SELECT  

distinct(user_pseudo_id),

event_date,

geo.continent,

geo.country,

geo.region,

geo.city,

geo.sub_continent,

geo.metro

FROM "Table"

 

Some other considereations and useful pages i 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 gives you some light on the situtation. Really not a ideal method and does take a lot of time to work it. 

[Ckg_PBI]

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!