-
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.
