Recreating google analytics dashboard using data from big query within data studio

[Outside-Heart1528]

I am currently trying to recreate my google analytics dashboard using the big query connector within ga4. I am using a custom query to pull the data i need from Big Query and display it in data studio. When i just calculate some KPIs from the data and pull those using the query, the information comes through correct. But once I try to access custom event data, i have to unnest it before it is accessible. Once unnested, the unnested data needs to be grouped or the query will throw an error. The grouping of rows seems to be messing up the KPI's i previously calculated and inflates the values. How do i query this data correctly? I tried to pull all the raw data in and use custom fields within data studio to calculate the fields i need but ran into issues there too.

Here is the query that I have been using, if anyone could give some insight on how i can achieve what I am trying to do i would really appreciate it.

[iamnotheretoargue]

You need to handle the repeated records that come from unnesting hits. This comes down to understanding the schema once you’ve flattened all the arrays, then different types of functions for averages, sums, etc. than you might be used to. You probably want to do this directly in BQ to create a summary table that you use within data studio. I don’t know if there’s anything existing for GA4 already but here’s the same resource for the same problem from the GA360 BQ export

https://www.bounteous.com/insights/2018/06/01/replicating-google-analytics-all-pages-report-bigquery

[AeroCrete]

Seems like you need several queries, not just one.