-
Creating a data-driven dashboard with Big Query for Google Analytics in Data Studio
Right now, I’m in the process of reimagining my Google Analytics dashboard using the Big Query connector in GA4. I’m designing a custom query that plucks the exact data I need from BigQuery to display it in Data Studio. When I do a simple KPI calculation using this data, everything comes through perfectly. Trouble seems to start when I attempt to delve into custom event data. To make this data accessible, I first have to “unnest” it. Then things get tricky because the freshly unnested data needs to be grouped or the query throws its toys out of the pram. It gets more complicated because this act of grouping seems to toss my previously calculated KPIs out of whack, inflating the values. Cue head scratching. So the golden question: how do I handle this query properly? I’ve attempted pulling all raw data in and using custom fields in Data Studio to calculate the fields I need, but that was like going down a rabbit hole.
--- Here's the SQL query I've been wrestling with --- SELECT distinct event_date, event_timestamp, event_name, user_pseudo_id, device.category, ...(snip)... GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23As you can see, I’m feeling all tangled up in SQL, not sure how to get the results without a hiccup. If you’ve been down this road before or have some savvy insight to share, I would really appreciate it.
-
2 Replies
-
Reimagining a Google Analytics dashboard can be challenging, especially when handling custom queries in BigQuery. If you’re running into hiccups with your custom event data, it might really help to try a query builder like Analytics Canvas. They even have a free trial, so it’s a no-risk way to validate your results and hopefully clear up your roadblocks.
Admittedly, navigating SQL can be a bit of a juggling act. Just remember when you’re using Looker Studio with BigQuery directly, it shoots out separate queries for each component of the report, like each chart, table, and even scorecard. To keep things tidy, it might be smart to use date partitioned summary tables which could save you from jumbled results, especially when compared to working with raw GA4 export tables. Stay patient, keep experimenting with your approach, and you’ll get there!
-
When dealing with nested data in BigQuery, un-nesting data and then using grouping is often required. Un-nesting flattens the data, but can replicate rows, which is why grouping is then used to produce original level metrics. This is likely why your KPIs are getting inflated. You could try using different types of joins, subqueries, or common table expressions (CTEs) to limit the un-nested data so it doesn’t inflate your KPIs. A possible approach could be to create subqueries for the basic KPIs and another one for the custom event data, and join them in the main query. Remember that SQL can be difficult when you’re dealing with complex data structures, but with a bit of patience and trial and error, you should be able to find a solution that works for your specific case.
Log in to reply.