bqml

BigQuery ML (BQML)

Parent Skill: /bigquery
Path: /bigquery/bqml

Purpose

Create and deploy machine learning models using only SQL - no Python or separate ML platform required.

When to Use

Trigger when:

• Keywords: predict, forecast, model, ML, classification, regression, clustering
• User wants to: predict churn, forecast sales, segment customers, recommend products

Chat commands:

/bigquery/bqml create churn prediction model
/bigquery/bqml forecast next month revenue
/bigquery/bqml segment customers by behavior
/bigquery/bqml recommend products for user

Requirements

---

Model Types

1. Linear Regression

Use for: Predicting continuous values (price, revenue, age)

CREATE OR REPLACE MODEL `dataset.price_prediction_model`
OPTIONS(
  model_type='LINEAR_REG',
  input_label_cols=['price']
) AS
SELECT
  bedrooms,
  bathrooms,
  sqft,
  location,
  price
FROM `dataset.housing_data`;

2. Logistic Regression (Binary Classification)

Use for: Yes/No predictions (churn, conversion, click)

CREATE OR REPLACE MODEL `dataset.churn_model`
OPTIONS(
  model_type='LOGISTIC_REG',
  input_label_cols=['churned']
) AS
SELECT
  user_tenure_days,
  total_purchases,
  last_purchase_days_ago,
  churned  -- 0 or 1
FROM `dataset.user_features`;

3. Multiclass Classification

Use for: Predicting categories (product category, customer segment)

CREATE OR REPLACE MODEL `dataset.category_model`
OPTIONS(
  model_type='LOGISTIC_REG',
  input_label_cols=['category']
) AS
SELECT
  title,
  description,
  price,
  category  -- 'electronics', 'clothing', 'home', etc.
FROM `dataset.products`;

4. K-Means Clustering

Use for: Grouping similar items (customer segmentation)

CREATE OR REPLACE MODEL `dataset.customer_segments`
OPTIONS(
  model_type='KMEANS',
  num_clusters=5
) AS
SELECT
  avg_purchase_value,
  purchase_frequency,
  days_since_last_purchase,
  total_revenue
FROM `dataset.customer_metrics`;