Skip to content
Tutorials Center
My learning

Topics

Lesson 25 of 37 ยท BigQuery ML

Evaluating and Predicting with Models

Read ML.EVALUATE metrics, run ML.PREDICT and ML.EXPLAIN_PREDICT, and compute precision, recall, accuracy and error from a confusion matrix in SQL.

A trained model is only useful if you know how well it performs and can apply it to new data. BigQuery ML provides table functions for both: ML.EVALUATE scores a model on a dataset, ML.PREDICT returns predictions, and ML.EXPLAIN_PREDICT adds the reasons. Because they are table functions, you use them in the FROM clause and can join, filter and save their output like any query result.

This lesson shows each function (read-only BigQuery syntax) and then computes the key metrics yourself with SQL and Python, so you understand what the numbers mean rather than just reading them off a result table.

ML.EVALUATE

Called with only a model, it returns metrics on the evaluation rows BigQuery held out during training:

SQL
SELECT * FROM ML.EVALUATE(MODEL `shop.churn_model`);

Called with a query, it scores the model on data you choose, which is how you test on a fresh time window:

SQL
SELECT *
FROM ML.EVALUATE(
  MODEL `shop.churn_model`,
  (SELECT * FROM `shop.customer_features` WHERE snapshot_date = '2026-03-01'),
  STRUCT(0.5 AS threshold)
);

The columns depend on the model type:

Model type Typical columns
Regression mean_absolute_error, mean_squared_error, mean_squared_log_error, median_absolute_error, r2_score, explained_variance
Classification precision, recall, accuracy, f1_score, log_loss, roc_auc
Clustering davies_bouldin_index, mean_squared_distance

The threshold in the struct moves the cutoff that turns probabilities into classes; raising it trades recall for precision.

ML.PREDICT

ML.PREDICT appends prediction columns to your input rows:

SQL
SELECT customer_id, predicted_churned, predicted_churned_probs
FROM ML.PREDICT(
  MODEL `shop.churn_model`,
  (SELECT * FROM `shop.customer_features_today`)
);

For a classifier you get predicted_<label> and predicted_<label>_probs, an array of structs {label, prob}. Pull a single probability out with UNNEST:

SQL
SELECT
  customer_id,
  (SELECT p.prob FROM UNNEST(predicted_churned_probs) p WHERE p.label = TRUE) AS churn_probability
FROM ML.PREDICT(MODEL `shop.churn_model`,
                TABLE `shop.customer_features_today`);

Predictions are ordinary rows, so you can write them back with CREATE TABLE ... AS or INSERT and use them in dashboards or marketing exports.

Why the model decided that: explainability

ML.EXPLAIN_PREDICT returns the top feature attributions for each prediction, and ML.GLOBAL_EXPLAIN ranks features by overall importance across the model. For linear and logistic models, ML.WEIGHTS shows each feature's coefficient directly:

SQL
SELECT * FROM ML.WEIGHTS(MODEL `shop.churn_model`);

SELECT *
FROM ML.EXPLAIN_PREDICT(MODEL `shop.churn_model`,
                        TABLE `shop.customer_features_today`,
                        STRUCT(3 AS top_k_features));

Use these before trusting a model. A feature with a surprising weight is often a sign of leakage.

The confusion matrix by hand

Every classification metric comes from four counts: true positives (TP), false positives (FP), true negatives (TN) and false negatives (FN). The example builds them from a table of predictions and actual labels.

SQL Metrics from predictions in SQL

Press Run (or Ctrl + Enter) to see the result here.

Precision answers: of the customers we flagged, how many really churned? Recall answers: of the customers who really churned, how many did we catch? Accuracy is the share of all predictions that were right, and it misleads on imbalanced data.

Threshold trade-offs and regression error

The same model gives different precision and recall at different thresholds. Run the sweep to see the trade-off, then compute the regression errors ML.EVALUATE would report.

Python Threshold sweep and regression error

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Raising the threshold makes the model more careful: fewer false alarms but more missed cases. Choose it from business cost. If a missed churner costs far more than a wasted discount, lower the threshold.

Using predictions safely

  • Evaluate on data from a later period than training.
  • Compare with a baseline, such as predicting the average or the majority class.
  • Monitor drift: re-run ML.EVALUATE on recent data on a schedule (see scheduled queries) and retrain when metrics fall.
  • Remember extrapolation: a model trained on one to seven visits is unreliable at nine.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which function returns precision, recall and accuracy for a classifier?

    ML.EVALUATE scores a model and returns metrics suited to its type.
  2. 2. Of 100 customers you flagged, 40 really churned. What is the precision?

    Precision is true positives divided by everything flagged: 40 / 100.
  3. 3. What happens to recall when you raise the probability threshold?

    A higher threshold flags fewer rows, so fewer true cases are caught.
  4. 4. What does ML.PREDICT add to the input rows of a classifier?

    You get the predicted class and an array of label and probability pairs.

Summary

  • ML.EVALUATE returns metrics; pass your own query to test on a different time window.
  • ML.PREDICT appends predictions; ML.EXPLAIN_PREDICT and ML.WEIGHTS explain them.
  • Precision, recall and accuracy come from TP, FP, TN and FN; accuracy misleads on imbalanced data.
  • The probability threshold trades precision against recall.
  • Evaluate on later data, compare with a baseline, and monitor for drift.

Next: Clustering, Forecasting and Remote Models