Skip to content
Tutorials Center
My learning

Topics

Lesson 24 of 37 ยท BigQuery ML

Regression and Classification Models

Train linear, logistic and boosted-tree models in BigQuery ML: label columns, data splits, the TRANSFORM clause, regularization and feature preparation in SQL.

Most business questions are one of two kinds. "How much?" asks for a number, such as revenue next month: that is regression. "Which one?" asks for a category, such as will this customer cancel: that is classification. BigQuery ML covers both with the same CREATE MODEL statement and a different model_type.

This lesson explains the options you will use most, how BigQuery splits data for training and testing, how to preprocess features inside the model with TRANSFORM, and how logistic regression turns numbers into probabilities. As before, BQML statements are read-only; the runnable examples reproduce the logic with SQL and Python.

Choosing the model type

Goal model_type Notes
Predict a number linear_reg Fast, easy to explain
Predict a number boosted_tree_regressor, random_forest_regressor Handle nonlinear patterns
Predict a class (two or more) logistic_reg Binary or multiclass
Predict a class boosted_tree_classifier, random_forest_classifier Often most accurate on tables
Predict a number or class dnn_regressor, dnn_classifier Neural networks, need more data

Start with the simplest model that could work. A linear or logistic model trains quickly, gives readable weights, and sets a baseline that a tree model must beat to be worth its cost.

A classification model, read-only

This trains a churn classifier. The label churned is a BOOL; BigQuery treats a column with two values as binary classification:

SQL
CREATE OR REPLACE MODEL `shop.churn_model`
OPTIONS (
  model_type = 'logistic_reg',
  input_label_cols = ['churned'],
  auto_class_weights = TRUE,
  l2_reg = 0.1,
  data_split_method = 'AUTO_SPLIT'
) AS
SELECT
  churned,
  orders_last_90d,
  days_since_last_order,
  support_tickets,
  country
FROM `shop.customer_features`;

Useful options:

  • auto_class_weights = TRUE balances classes when one is rare, which is usual for churn or fraud.
  • l2_reg / l1_reg add regularization so the model does not memorize noise.
  • data_split_method: AUTO_SPLIT (the default) holds out about 20 percent for evaluation when you have enough rows; RANDOM with data_split_eval_fraction, SEQ with a data_split_col (good for time-based splits so the model never trains on the future), or NO_SPLIT.
  • max_iterations and early_stop control training time.

For tree models the options include num_parallel_tree, max_tree_depth, subsample and learn_rate.

Preprocessing with TRANSFORM

By default BigQuery standardizes numeric features and one-hot encodes strings. To control this, or to bake your preparation steps into the model so prediction uses the same steps, use TRANSFORM:

SQL
CREATE OR REPLACE MODEL `shop.churn_model2`
TRANSFORM (
  ML.QUANTILE_BUCKETIZE(days_since_last_order, 5) OVER () AS recency_bucket,
  ML.STANDARD_SCALER(orders_last_90d) OVER () AS orders_scaled,
  ML.FEATURE_CROSS(STRUCT(country, support_tickets > 2 AS many_tickets)) AS country_x_tickets,
  churned
)
OPTIONS (model_type = 'logistic_reg', input_label_cols = ['churned']) AS
SELECT * FROM `shop.customer_features`;

Preprocessing inside TRANSFORM is stored with the model, so ML.PREDICT applies the same buckets and scaling automatically. That avoids the classic bug where training and serving prepare features differently.

Preparing features with SQL

You can also prepare features in the SELECT before training. The runnable example below builds a feature table from orders and customers: counts, recency and a boolean flag.

SQL Build a feature table with SQL

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

Defining the label (here, no order for 60 days) is the most important design decision. Define it from data that would not be known at prediction time, and keep features strictly earlier than the label window to avoid leakage.

How logistic regression predicts

A logistic model computes a weighted sum of the features, then squeezes it through the sigmoid function 1 / (1 + e^-z) to get a probability between 0 and 1. The class is chosen by comparing the probability to a threshold, 0.5 by default. The example trains a tiny model with gradient descent so you can see this happen.

Python Logistic regression from scratch

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.

The weight is positive, meaning more days since the last order increases the churn probability. BQML exposes this learned weight through ML.WEIGHTS and, for each prediction, a predicted_churned_probs column.

Common mistakes

  • Leakage: including a column that reveals the label, such as cancellation_date, gives a perfect model that fails in production.
  • Random splits on time series: train on the past, test on the future (data_split_method = 'SEQ').
  • Ignoring class imbalance: 98 percent accuracy is meaningless when 98 percent of rows are one class. Use auto_class_weights and look at precision and recall.
  • Too many raw string features with thousands of values, which explode into one-hot columns.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Which model_type predicts a category such as churned or not churned?

    logistic_reg (and tree classifiers) predict classes; linear_reg predicts numbers.
  2. 2. What is the benefit of the TRANSFORM clause?

    Using the same preprocessing at training and prediction time avoids training-serving skew.
  3. 3. Which split suits a model that forecasts future behavior from past data?

    A sequential split trains on earlier rows and evaluates on later ones, so the model is not tested on the past.
  4. 4. What does auto_class_weights = TRUE help with?

    It weights rare classes more heavily so the model does not simply predict the majority class.
  5. 5. What does the sigmoid function turn a weighted sum into?

    Sigmoid squeezes any number into the 0 to 1 range, read as a probability.

Summary

  • Regression predicts numbers and classification predicts classes; BQML uses model_type to choose.
  • Start with linear_reg or logistic_reg, then try boosted trees if you need more accuracy.
  • Options such as data_split_method, l2_reg and auto_class_weights control evaluation and generalization.
  • TRANSFORM stores preprocessing with the model; leakage and imbalance are the common traps.
  • Logistic regression is a weighted sum passed through a sigmoid.

Next: Evaluating and Predicting with Models