From Raw GA4 Events to Analytics-Ready Data
Building the transformation layer between raw GA4 event exports and analytics-ready datasets for funnels, revenue, attribution, and product analysis.
The Result at a Glance:
Raw GA4 exports too complex for business users to query
Nested fields, event-level grain, no sessionization, no modeling
Layered SQL transformation: raw → staging → intermediate → mart
Delivered analytics-ready datasets that business users can query directly for funnels, revenue, attribution, and product performance.
Analytics / Data Engineering
Data architecture → SQL transformation → data modeling → quality checks → reporting
Data Source
The primary data source was the GA4 BigQuery export — daily tables containing every event collected by Google Analytics 4. Each row represents a single event with nested structures for event parameters, user properties, ecommerce data, and device/geo information.
Data Challenges
Raw GA4 BigQuery data presents several challenges for business analysis:
- Nested fields — event_params is a repeated RECORD requiring UNNEST to access individual parameters
- Repeated items — ecommerce.items is an array of product-level structs
- Event-level grain — every row is a single event, not a session or user
- Sessionization — GA4 does not export session-level aggregates; sessions must be reconstructed
- User identity — user_pseudo_id may fragment across devices without proper identity resolution
Architecture
I designed a layered transformation architecture:
SQL Transformation
The core transformation extracts nested event parameters into flat columns that analysts can query directly:
Data Modeling
The mart layer contains purpose-built analytical tables:
- fact_events — flattened event table with extracted parameters
- fact_sessions — session-level aggregates (duration, events, conversions)
- fact_orders — ecommerce transactions with item details
- dim_users — user-level attributes and first/last touch
- dim_products — product catalog with categories
- dim_campaigns — campaign and traffic source dimensions
Data Quality
Every model includes quality checks:
- NULL CHECK — required fields must not be null
- DUPLICATE CHECK — primary keys must be unique
- FRESHNESS — data must arrive within expected time window
- SCHEMA TEST — column types and names must match expectations
- REFERENTIAL INTEGRITY — foreign keys must reference valid records
Analytical Outputs
The analytics-ready datasets enable the following analyses without complex SQL:
- Funnel analysis — conversion rates between defined steps
- Revenue analysis — daily, weekly, monthly revenue with product and channel dimensions
- Attribution analysis — session-level source/medium with conversion credit
- Product performance — item-level metrics (views, cart adds, purchases, revenue)
Cost & Performance
Query optimization through partitioning and clustering:
- Tables partitioned by event_date — queries scan only required date ranges
- Clustered by event_name — common filters execute efficiently
- Incremental models — only new data is processed, not the full historical dataset
- Query cost reduced significantly compared to querying raw nested tables directly
Outcome
Delivered analytics-ready datasets that business users can query with simple SQL. Analysts no longer need to understand UNNEST, nested RECORD types, or sessionization logic to answer business questions.
Diagnostic Code & Query Architecture:
SELECT
event_date,
user_pseudo_id,
event_name,
ecommerce.transaction_id,
ecommerce.purchase_revenue,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_location,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'session_id') AS session_id
FROM `project.analytics.events_*`
WHERE event_name = 'purchase'
AND _TABLE_SUFFIX BETWEEN '20240101' AND '20240131';