BigQuery ML lets you train, evaluate, explain, and serve models with CREATE MODEL statements — no data export, no separate training cluster, no feature pipeline in another language. The pieces that teams most often miss are the unglamorous ones: a leak-free training table, a time-based split, a threshold chosen from business cost rather than 0.5, and a serving path that someone other than the author can run next quarter.
This tutorial walks a churn model end to end in SQL, then shows how to register it in Vertex AI when you need real-time predictions.
1. Build a point-in-time training table
Every churn model that looks amazing in evaluation and useless in production has the same bug: features computed after the label period. Fix the as-of date explicitly.
CREATE OR REPLACE TABLE `demo.churn_training`
PARTITION BY as_of_date
AS
WITH as_of AS (SELECT DATE '2026-01-01' AS as_of_date),
activity AS (
SELECT
e.customer_id,
a.as_of_date,
COUNTIF(e.event_date >= DATE_SUB(a.as_of_date, INTERVAL 30 DAY)) AS events_30d,
COUNTIF(e.event_date >= DATE_SUB(a.as_of_date, INTERVAL 90 DAY)) AS events_90d,
DATE_DIFF(a.as_of_date, MAX(e.event_date), DAY) AS days_since_last_event,
COUNT(DISTINCT e.feature_area) AS features_used
FROM `demo.events` e
CROSS JOIN as_of a
WHERE e.event_date < a.as_of_date -- nothing from the future
GROUP BY e.customer_id, a.as_of_date
),
billing AS (
SELECT
customer_id,
SUM(amount) AS revenue_180d,
COUNTIF(status = 'failed') AS failed_payments_180d
FROM `demo.invoices`, as_of
WHERE invoice_date BETWEEN DATE_SUB(as_of_date, INTERVAL 180 DAY) AND DATE_SUB(as_of_date, INTERVAL 1 DAY)
GROUP BY customer_id
),
labels AS (
SELECT
s.customer_id,
-- churn = cancelled in the 90 days AFTER the as-of date
IF(s.cancelled_date BETWEEN a.as_of_date AND DATE_ADD(a.as_of_date, INTERVAL 90 DAY), 1, 0) AS churned
FROM `demo.subscriptions` s
CROSS JOIN as_of a
WHERE s.started_date < a.as_of_date
AND (s.cancelled_date IS NULL OR s.cancelled_date >= a.as_of_date)
)
SELECT
l.customer_id,
ac.as_of_date,
ac.events_30d, ac.events_90d, ac.days_since_last_event, ac.features_used,
IFNULL(b.revenue_180d, 0) AS revenue_180d,
IFNULL(b.failed_payments_180d, 0) AS failed_payments_180d,
c.plan_tier, c.country, c.acquisition_channel,
l.churned
FROM labels l
JOIN activity ac USING (customer_id)
LEFT JOIN billing b USING (customer_id)
JOIN `demo.customers` c USING (customer_id);
Repeat the block for several as-of dates (a quarterly snapshot for the last two years is typical) and union them. More snapshots give the model seasonality without needing a single giant window.
Two rules to keep: no column that is only populated because the customer churned (cancellation reason, final invoice flag), and no identifier that encodes time of signup unless you want the model to learn cohort instead of behaviour.
2. Train with a time-based split
CREATE MODEL defaults to a random 80/20 split, which leaks future information across snapshots. Use DATA_SPLIT_METHOD = 'CUSTOM' with your own boolean column, or 'SEQ' on the as-of date.
CREATE OR REPLACE MODEL `demo.churn_xgb`
OPTIONS (
model_type = 'BOOSTED_TREE_CLASSIFIER',
input_label_cols = ['churned'],
data_split_method = 'SEQ',
data_split_col = 'as_of_date',
data_split_eval_fraction = 0.2,
auto_class_weights = TRUE, -- churn is usually 3-8% positive
enable_global_explain = TRUE,
max_iterations = 50,
early_stop = TRUE,
l2_reg = 1.0
) AS
SELECT * EXCEPT (customer_id)
FROM `demo.churn_training`;
Notes that matter:
auto_class_weights = TRUEstops the model from scoring 95% accuracy by predicting "nobody churns".enable_global_explain = TRUEmust be set at training time; you cannot add it later without retraining.- String columns are one-hot encoded automatically; you do not need to pre-encode
plan_tierorcountry. - Add
num_parallel_treeandsubsampleonly after you have a baseline. ALOGISTIC_REGmodel trained first is a useful floor — if boosted trees do not beat it by a clear margin, your features are the problem, not the algorithm.
Hyperparameter tuning is one clause away, and it runs as parallel trials inside BigQuery:
OPTIONS (
model_type = 'BOOSTED_TREE_CLASSIFIER',
num_trials = 20,
max_parallel_trials = 4,
hparam_tuning_objective = 'ROC_AUC',
learn_rate = HPARAM_RANGE(0.01, 0.3),
max_tree_depth = HPARAM_CANDIDATES([4, 6, 8, 10]),
...
)
Budget for it: trials bill as ordinary BigQuery ML training jobs, so 20 trials on a wide table is 20× the slot time.
3. Evaluate on what the business will feel
SELECT * FROM ML.EVALUATE(MODEL `demo.churn_xgb`);
SELECT * FROM ML.ROC_CURVE(MODEL `demo.churn_xgb`)
ORDER BY threshold;
SELECT * FROM ML.CONFUSION_MATRIX(MODEL `demo.churn_xgb`,
(SELECT * EXCEPT (customer_id) FROM `demo.churn_training` WHERE as_of_date = '2026-01-01'),
STRUCT(0.35 AS threshold));
ROC AUC is fine for comparing candidate models, but the number a retention lead cares about is lift in the top decile: of the 10% of customers the model flags first, what share actually churn?
WITH scored AS (
SELECT
customer_id,
predicted_churned_probs[OFFSET(0)].prob AS p_churn,
churned
FROM ML.PREDICT(MODEL `demo.churn_xgb`,
(SELECT * FROM `demo.churn_training` WHERE as_of_date = '2026-01-01'))
),
deciled AS (
SELECT *, NTILE(10) OVER (ORDER BY p_churn DESC) AS decile FROM scored
)
SELECT
decile,
COUNT(*) AS customers,
ROUND(AVG(churned), 4) AS actual_churn_rate,
ROUND(AVG(churned) / (SELECT AVG(churned) FROM scored), 2) AS lift
FROM deciled
GROUP BY decile
ORDER BY decile;
Then choose the threshold from cost, not convention. If a retention offer costs $40 and a saved customer is worth $600, you can afford roughly fourteen false positives per true positive — that is a threshold well below 0.5.
4. Explain individual predictions
Global feature importance answers "what drives churn". ML.EXPLAIN_PREDICT answers "why this account", which is what a CSM actually needs in the dashboard.
SELECT
customer_id,
ROUND(predicted_churned_probs[OFFSET(0)].prob, 3) AS p_churn,
ARRAY(
SELECT AS STRUCT feature, ROUND(attribution, 4) AS attribution
FROM UNNEST(top_feature_attributions)
ORDER BY ABS(attribution) DESC
LIMIT 3
) AS top_reasons
FROM ML.EXPLAIN_PREDICT(
MODEL `demo.churn_xgb`,
(SELECT * FROM `demo.churn_scoring_features`),
STRUCT(5 AS top_k_features)
)
WHERE predicted_churned_probs[OFFSET(0)].prob > 0.35;
ML.GLOBAL_EXPLAIN(MODEL \demo.churn_xgb`)` gives the model-level view for your documentation.
5. Serve: batch scoring and real-time
For most retention workflows, batch is enough. Schedule the scoring query and write to a partitioned table so you keep a history of scores to audit later:
CREATE OR REPLACE TABLE `demo.churn_scores`
PARTITION BY score_date
AS
SELECT
CURRENT_DATE() AS score_date,
customer_id,
predicted_churned_probs[OFFSET(0)].prob AS p_churn
FROM ML.PREDICT(MODEL `demo.churn_xgb`, TABLE `demo.churn_scoring_features`);
Critically, demo.churn_scoring_features must be produced by the same SQL as the training features, with the as-of date set to today. The cheapest way to guarantee that is a single table function used by both paths:
CREATE OR REPLACE TABLE FUNCTION `demo.customer_features`(as_of_date DATE)
AS (
-- identical feature logic, parameterised by as_of_date
SELECT ... FROM `demo.events` WHERE event_date < as_of_date ...
);
Train on SELECT * FROM demo.customer_features('2026-01-01'), score on demo.customer_features(CURRENT_DATE()). That one refactor eliminates the most common cause of training/serving skew.
When you need sub-second scoring from an application, export the model to Vertex AI and deploy an endpoint:
EXPORT MODEL `demo.churn_xgb`
OPTIONS (URI = 'gs://demo-models/churn_xgb/v3');
BigQuery ML models can also be registered directly to the Vertex AI Model Registry with the model_registry = 'vertex_ai' and vertex_ai_model_version_aliases options at training time, which is the cleaner route if a version history and online endpoint are part of the plan.
6. Keep it honest after launch
- Score drift. Compare the distribution of
p_churnweek over week; a sudden shift usually means an upstream pipeline changed, not that customers changed. - Feature drift. Dataplex data-quality scans on the feature table catch nulls and range shifts before they reach the model.
- Backtest monthly. Once 90 days have passed for a scoring snapshot, join scores to actual outcomes and recompute top-decile lift. That single number tells you when to retrain.
- Retrain on a schedule, not on vibes. A scheduled query that recreates the model with
CREATE OR REPLACE MODELand logsML.EVALUATEoutput into a metrics table gives you a versioned quality trail. - Cost. Training bills as a normal query against your on-demand bytes or reservation slots; a boosted-tree model over a few million rows is minutes of slot time. Hyperparameter tuning and repeated retraining are where the bill grows.
When BigQuery ML is the wrong tool
It is not a general ML platform. Deep-learning architectures, custom loss functions, image and audio pipelines, and anything that needs a rich Python training loop belong in Vertex AI — potentially reading the same BigQuery tables via BigQuery DataFrames. BigQuery ML earns its place when the data is already in the warehouse, the model is tabular, and the team's fluency is SQL. For churn, propensity, LTV, demand forecasting, and anomaly scoring, that describes most mid-market data teams.
Need help getting a model from notebook experiment to governed, scheduled, monitored production? Our BigQuery Machine Learning services cover feature design, training pipelines, and MLOps on BigQuery — get in touch to talk through your use case.