Practical Guide: Deploying a Propensity Model in BigQuery ML

You can build and deploy a conversion propensity model entirely in SQL — no separate ML infrastructure needed.

Step 1: Create a training dataset view with one row per session (keyed by session_id, built from user_pseudo_id and ga_session_id, plus event_date) and these features: page_count, session_duration_seconds, scroll_depth_max, device_category, traffic_source, hour_of_day, day_of_week, is_returning_visitor. Label: did_convert (1/0).

Step 2: CREATE OR REPLACE MODEL project.dataset.conversion_propensity OPTIONS(model_type='BOOSTED_TREE_CLASSIFIER', input_label_cols=['did_convert'], data_split_method='AUTO_SPLIT', max_iterations=50, max_tree_depth=6) AS SELECT * EXCEPT(session_id, event_date) FROM training_view WHERE event_date BETWEEN your date range. Excluding the identifier and date columns stops them from being used as features.

Step 3: Score new sessions with ML.PREDICT: SELECT session_id, (SELECT prob FROM UNNEST(predicted_did_convert_probs) WHERE label = 1) AS conversion_probability FROM ML.PREDICT(MODEL project.dataset.conversion_propensity, (SELECT * FROM todays_sessions)). Filter on label = 1 rather than relying on the array's position.

Step 4: Use the probabilities in Looker Studio dashboards or export to your CRM for targeting. Segment users into high/medium/low propensity buckets for differentiated engagement strategies.

Retrain monthly by rerunning the CREATE MODEL statement with updated date ranges. Monitor with ML.EVALUATE to track AUC over time — when it drops below 0.65, revisit your feature engineering. There is no extra infrastructure to pay for, but on on-demand pricing CREATE MODEL is billed at BigQuery ML rates, which are higher per byte processed than ordinary queries.