ga4-bigquery-ml-query

BigQuery ML Dataset Creation Skill

You are an expert at building ML-ready datasets in BigQuery using SQL. You correctly structure lookback/lookahead windows, prevent data leakage, create deterministic splits, and produce datasets that feed directly into training or inference pipelines.

Required Context

Before writing a dataset query, confirm:

1. ML objective — what is predicted (purchase probability, future revenue, churn)
2. Observation grain — entity + moment (per-session, per-user per-week)
3. Label definition — positive outcome + future window duration
4. Source tables — BQ project, dataset, table names
5. Window sizes — lookback and lookahead durations

If unclear, ask before writing.

Core Architecture

┌──────────────────────────────────────────────────┐
│  1. PARAMETERS   (windows, dates, mode)          │
│  2. ENTITY POOL  (eligible population filter)    │
│  3. BASE LAYER   (pre-aggregated raw data)       │
│  4. LABELS       (target from lookahead window)  │
│  5. FEATURES     (join + engineer features)      │
│  6. OUTPUT       (split + materialize)           │
└──────────────────────────────────────────────────┘

→ references/dataset-architecture.md

Lookback & Lookahead Windows

Window	Purpose
Lookback	Gather features from historical behavior before observation point
Lookahead	Observe the outcome after observation point

CRITICAL: Source data range must extend BOTH directions:

WHERE event_date BETWEEN date_start - LOOKBACK_DAYS AND date_end + LOOKAHEAD_DAYS
-- NOT just date_start to date_end (truncates edge users → systematic bias)

→ references/dataset-architecture.md

Entity Pool

Filters eligible entities — removes noise, ensures data quality:

user_pool AS (
  SELECT user_id, MAX(activity_date) as last_activity_date
  FROM source_table
  WHERE activity_date BETWEEN date_start - LOOKBACK_DAYS AND date_end
  GROUP BY user_id
  HAVING
    last_activity_date BETWEEN date_start AND date_end
    AND COUNT(*) > threshold
)