/ Making GA4 Data Reporting-Ready with BigQuery

The Challenge

GA4 sends data to BigQuery in a complex format that is not ready for reporting. The data is nested and event-based, which makes it:

• Slow: dashboards took too long to load
• Expensive: each report scanned large amounts of data
• Hard to work with: funnel analysis had to be built separately in each report

Teams needed clear funnel reports, but the raw GA4 data structure made this difficult to achieve.

The Solution

I created a flattened table in BigQuery to make GA4 data easier to use.
I organized the data by unnesting event parameters and structuring key fields like session ID, user properties, conversion events, revenue, and product information. Then, I built funnel logic in SQL and defined clear funnel stages so they were calculated only once in BigQuery, rather than in every report. I also created a daily updated table that became the main source for Looker Studio reports.
This created a clean data layer between the raw export and the reports, enabling faster, more cost-efficient reporting.

The Impact

• Reports loaded 3 – 5x faster, going from minutes to seconds
• Lower reporting costs because less data was scanned each time
• Consistent numbers across teams and reports
• Better revenue tracking, connecting about $280K in revenue and 1,700+ transactions with reliable source attribution