BigQuery ML: Predictive Analytics for Customer Behaviour

What BigQuery ML Enables

BigQuery ML (BQML) lets you create and run machine learning models using standard SQL — no Python, no data science infrastructure. For marketing analytics teams comfortable with SQL but not ML programming, BQML opens predictive analytics capabilities: churn prediction, purchase propensity scoring, LTV prediction, and customer segmentation.

Use Case 1: Purchase Propensity Score

Train a logistic regression model on historical user behaviour to predict whether a current visitor will purchase:

-- Step 1: Create training data
CREATE OR REPLACE TABLE `project.analytics.purchase_training` AS
SELECT
  user_pseudo_id,
  COUNT(IF(event_name='page_view', 1, NULL)) as page_views,
  COUNT(IF(event_name='view_item', 1, NULL)) as product_views,
  COUNT(IF(event_name='add_to_cart', 1, NULL)) as add_to_carts,
  MAX(IF(event_name='purchase', 1, 0)) as purchased -- label
FROM `project.analytics_PROPERTY.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20250101' AND '20251231'
GROUP BY user_pseudo_id;

-- Step 2: Train logistic regression model
CREATE OR REPLACE MODEL `project.analytics.purchase_propensity_model`
OPTIONS(model_type='logistic_reg', input_label_cols=['purchased']) AS
SELECT * FROM `project.analytics.purchase_training`;

-- Step 3: Predict on recent users
SELECT
  user_pseudo_id,
  predicted_purchased_probs[OFFSET(0)].prob AS purchase_probability
FROM ML.PREDICT(MODEL `project.analytics.purchase_propensity_model`,
  (SELECT user_pseudo_id, page_views, product_views, add_to_carts
   FROM `project.analytics.purchase_training`
   WHERE -- filter for current period users
   TRUE))
ORDER BY purchase_probability DESC

Use Case 2: Customer Segmentation (k-means clustering)

-- Segment customers by behaviour using k-means clustering:
CREATE OR REPLACE MODEL `project.analytics.customer_segments`
OPTIONS(model_type='kmeans', num_clusters=5) AS
SELECT total_spend, order_count, days_since_last_purchase
FROM `project.analytics.customer_summary`

Practical Applications

• High-propensity audiences: export users with purchase_probability > 0.7 as a Customer Match audience in Google Ads for high-intent remarketing
• Churn risk: identify customers with high predicted churn probability for retention campaigns
• LTV prediction: predict which new customers will have high LTV, then optimise acquisition campaigns to focus on those segments

Summary

BigQuery ML enables machine learning in SQL: CREATE MODEL, then ML.PREDICT. Practical marketing use cases include purchase propensity scoring (logistic regression), customer segmentation (k-means clustering), and LTV prediction (linear regression). Propensity scores can be exported as Customer Match audiences for high-precision advertising targeting. BQML requires no Python or data science infrastructure — SQL skills are sufficient to build and deploy simple but effective predictive models.

See our BigQuery Setup service for predictive analytics development.

Need predictive analytics in BigQuery? Contact Adslytics.