BigQuery ML → Train & Predict

Build and serve a churn model in pure SQL with BigQuery ML—train, evaluate, predict, and schedule retrains.

Difficulty: Advanced
Tech stack: BigQuery, BigQuery ML (CREATE MODEL / ML.EVALUATE / ML.PREDICT), Scheduled Queries/Composer
Estimated time: 1-2 hrs

Overview

You’ll aggregate transactional data to craft features (e.g., recency/frequency/monetary, tenure, support tickets), then train a BQML model via `CREATE MODEL`. Use `ML.EVALUATE` to review metrics and `ML.PREDICT` to generate churn probabilities into a downstream table. A scheduled query (or Composer) retrains and re-scores periodically, keeping predictions fresh for BI or activation.

Outcome

Warehouse-native ML: no data movement, fully in BigQuery.
Actionable predictions (churn likelihood) materialized to tables.
Operationalized ML with scheduled retrains and accuracy tracking.

What you’ll build

A curated features table (SQL aggregations over transactions).
`CREATE MODEL` (e.g., logistic_reg or autoML).
Evaluation queries using ML.EVALUATE (AUC, accuracy, precision/recall).
Batch scoring with ML.PREDICT to a predictions table.
A scheduled query / Composer DAG to retrain + rescore on cadence.