BigQuery ML for Marketing

9 min read

Learn

Quick Reading

Estimated 9 mins

Prereq

Intermediate

Basic ML concepts helpful

Interactive

Static Playbook

Static guide & reference tables

ML without leaving the warehouse

BigQuery ML (BQML) lets you train and serve machine learning models using standard SQL CREATE MODEL statements, directly against tables already sitting in BigQuery — no exporting data to a separate Python/notebook environment, no standing up a model-serving service for straightforward cases. For marketing use cases where the feature set is already warehouse-native (GA4 event history, CRM fields, ad spend), this collapses what would otherwise be a multi-tool pipeline (extract to Python, train, export predictions back to warehouse) into a single SQL workflow an analytics engineer can own end-to-end.

train_churn_model.sql

sql

CREATE OR REPLACE MODEL `marketing.churn_model`
OPTIONS(
  model_type = 'LOGISTIC_REG',
  input_label_cols = ['churned']
) AS
SELECT
  days_since_last_purchase,
  total_orders_90d,
  avg_order_value_90d,
  support_tickets_90d,
  email_open_rate_30d,
  churned
FROM `marketing.customer_features`
WHERE feature_date < '2026-09-01';  -- training cutoff, avoid leakage

The marketing use cases that fit BQML well

BQML is a strong fit for problems that are tabular, warehouse-native, and don't need exotic architectures: churn propensity (logistic regression or boosted trees), LTV regression, lead scoring, and time-series forecasting for demand/spend planning using the built-in ARIMA_PLUS model type. It's a poor fit for anything needing deep learning on unstructured inputs (image creative scoring, NLP on raw ad copy beyond basic embeddings) — those still belong in Vertex AI or a dedicated ML platform, though BQML can call Vertex AI models for inference via remote model references, keeping the SQL interface even when the model itself lives elsewhere.

Feature leakage is the most common BQML marketing mistake

Training a churn or LTV model on features computed using data from after the prediction point (e.g. including 'days until cancellation' as a feature when predicting churn) produces a model that looks excellent in evaluation and is useless in production, because that information doesn't exist yet at prediction time. Always construct feature tables with an explicit feature_date cutoff and verify every feature is computable using only information available as of that date.

Evaluating and predicting

ML.EVALUATE returns standard classification/regression metrics (precision, recall, ROC AUC for classifiers; RMSE, R² for regressors) against a held-out set, directly in SQL. ML.PREDICT runs inference against new rows and can be joined straight back into marketing marts — e.g. writing predicted churn probability onto a dim_customers table that a dbt model then feeds into a Klaviyo or Braze audience sync for 'at-risk customer' retention flows, entirely within the existing ELT pipeline.

score_and_segment.sql

sql

SELECT
  customer_id,
  predicted_churned_probs[OFFSET(0)].prob AS churn_probability
FROM ML.PREDICT(
  MODEL `marketing.churn_model`,
  (SELECT * FROM `marketing.customer_features` WHERE feature_date = CURRENT_DATE())
)
WHERE predicted_churned_probs[OFFSET(0)].prob > 0.7

Explainability and stakeholder trust

ML.EXPLAIN_PREDICT surfaces per-prediction feature attributions (Shapley values for tree-based models), which matters enormously for marketing adoption — a lifecycle marketing manager won't act on a 'this customer will churn' score they can't interrogate. Being able to show that a specific customer's score was driven primarily by a drop in email engagement and increased support tickets, rather than treating the model as a black box, is often the difference between a model that gets used and one that gets ignored.

What's Next?

BQML's accessibility means predictive scores can now live natively in the same place as the rest of marketing's data — the next step is making sure the SQL patterns feeding both dashboards and models are written in a way marketers, not just engineers, can maintain.

I build these systems professionally.

Whether it's a RAG pipeline, analytics migration, or AI workflow — let's talk.

Work With Me →