Google BigQuery

BigQuery isn't an analytics tool — it's the substrate on which serious analytics is built. Every meaningful analysis in modern web analytics typically starts with a BigQuery query. The serverless architecture means you never think about clusters, indexes, or storage management. You write SQL, it scans petabytes, and you pay per byte scanned. The cognitive simplification of this model is underrated.

The GA4 export schema is both BigQuery's greatest analytics use case and its most common source of frustration. Event parameters are stored as ARRAY<STRUCT<key STRING, value STRUCT<string_value, int_value, float_value, double_value>>>. Extracting a single parameter requires UNNEST with conditional logic or a subquery. Build a dbt model that flattens event_params into typed columns on day one — ga4_events_flat with page_location, page_title, session_id, engagement_time_msec as native columns. Every subsequent query becomes 10x simpler and 3x cheaper (because you're scanning fewer bytes).

Partitioning and clustering are non-negotiable for analytics tables. Partition by _TABLE_SUFFIX or event_date. Cluster by event_name, then user_pseudo_id. This single configuration decision reduces query costs by 80-95% and execution time by 5-20x. Auditing BigQuery bills often reveals cases where teams spend $500/month on queries that should cost $30/month — the only issue being missing partitioning. Checking this configuration early is essential.

Window functions are where BigQuery becomes an analytical powerhouse. LEAD/LAG for computing inter-event durations. FIRST_VALUE/LAST_VALUE with PARTITION BY session_id for entry/exit page attribution. ARRAY_AGG(page_location ORDER BY event_timestamp) for building complete user journeys as arrays. APPROX_QUANTILES for percentile distributions without full sorts. SUM() OVER(PARTITION BY user_pseudo_id ORDER BY event_date ROWS UNBOUNDED PRECEDING) for cumulative engagement metrics. These functions operate at the event grain without collapsing your data — which means you can compute session-level metrics and then aggregate to user-level in the same query.

BigQuery ML brings machine learning into your SQL workflow. CREATE MODEL with LOGISTIC_REG for conversion propensity. BOOSTED_TREE_CLASSIFIER for churn prediction with feature importance ranking. KMEANS for behavioural clustering. ARIMA_PLUS for time-series forecasting with automatic seasonality detection. The models train on your full dataset — no sampling, no export, no separate compute environment. For exploratory ML on analytics data, this eliminates all infrastructure friction. For production models, export to Vertex AI.

Federated queries and BI Engine are advanced capabilities worth knowing. Federated queries let you join BigQuery tables with Cloud SQL, Cloud Spanner, or Google Sheets data — meaning you can enrich analytics data with CRM records, product catalogues, or manual annotations without ETL. BI Engine provides sub-second query caching for Looker Studio dashboards, eliminating the refresh latency that makes BigQuery-backed dashboards feel sluggish.

Cost governance requires discipline. Set per-user and per-project quota limits. Use the INFORMATION_SCHEMA.JOBS view to monitor query costs by user, project, and query pattern. Build a dbt model that tracks daily BigQuery spend alongside analytical output. The analysts who run the most expensive queries aren't always producing the most valuable insights — and making cost visible changes behaviour.