+1 (415) 943-1448

Anomaly Detection and Root-Cause Analysis in BigQuery ML: ML.DETECT_ANOMALIES and Contribution Analysis

A revenue dip shows up on the dashboard at 9 a.m. Two questions follow. Is it really abnormal? And which segment caused it? BigQuery ML can answer both in SQL, with no data leaving the warehouse. This guide pairs ML.DETECT_ANOMALIES on an ARIMA_PLUS time-series model with contribution analysis (ML.GET_INSIGHTS).

The setup

Assume a daily table analytics.daily_orders with order_date DATE, country STRING, channel STRING, device STRING, and revenue NUMERIC. First build the total daily series.

CREATE OR REPLACE VIEW analytics.daily_revenue AS
SELECT order_date, SUM(revenue) AS revenue
FROM analytics.daily_orders
GROUP BY order_date;

Step 1: Train a time-series model on history

Train on a window you believe is mostly normal. Anomalies in the training data inflate the model's expected variance and hide future problems.

CREATE OR REPLACE MODEL analytics.revenue_anomaly
OPTIONS (
  model_type = 'ARIMA_PLUS',
  time_series_timestamp_col = 'order_date',
  time_series_data_col = 'revenue',
  holiday_region = 'US',
  auto_arima = TRUE
) AS
SELECT order_date, revenue
FROM analytics.daily_revenue
WHERE order_date BETWEEN '2025-01-01' AND '2025-12-31';

ARIMA_PLUS models trend, weekly and yearly seasonality, and holiday effects. That is why a Black Friday spike should not be flagged. Set holiday_region to match your business.

Step 2: Score new data

Pass fresh rows to ML.DETECT_ANOMALIES. The threshold is the probability above which a point counts as anomalous.

SELECT order_date, revenue, is_anomaly,
       lower_bound, upper_bound, anomaly_probability
FROM ML.DETECT_ANOMALIES(
  MODEL analytics.revenue_anomaly,
  STRUCT(0.99 AS anomaly_prob_threshold),
  (SELECT order_date, revenue
   FROM analytics.daily_revenue
   WHERE order_date >= '2026-01-01')
)
WHERE is_anomaly
ORDER BY order_date;

Start at 0.99 and lower it only if you are missing real incidents. A 0.95 threshold on daily data flags roughly one day in twenty by design, which causes alert fatigue.

Step 3: Find out why with contribution analysis

Knowing that March 14 was low is only half the answer. A contribution analysis model compares a test period with a control period across dimension combinations. It ranks the segments that explain the change.

CREATE OR REPLACE MODEL analytics.revenue_drivers
OPTIONS (
  model_type = 'CONTRIBUTION_ANALYSIS',
  contribution_metric = 'SUM(revenue)',
  dimension_id_cols = ['country', 'channel', 'device'],
  is_test_col = 'is_test',
  min_apriori_support = 0.05
) AS
SELECT
  country, channel, device, revenue,
  order_date = '2026-03-14' AS is_test
FROM analytics.daily_orders
WHERE order_date IN ('2026-03-14', '2026-03-07');

Comparing against the same weekday a week earlier keeps weekly seasonality out of the comparison. Then read the results:

SELECT contributors, metric_control, metric_test,
       difference, relative_difference, unexpected_difference
FROM ML.GET_INSIGHTS(MODEL analytics.revenue_drivers)
ORDER BY ABS(unexpected_difference) DESC
LIMIT 10;

Check the current documentation for the exact output columns in your project. A row such as [DE, paid_search, mobile] with a large negative unexpected_difference is where to start the investigation. It might be a broken campaign tag, a payment outage, or a regional holiday.

Step 4: Operationalize it

  • Schedule the detection query as a scheduled query, or as a Dataform action, that writes anomalies to an analytics.anomaly_log table. Alert from there.
  • Retrain on a cadence, for example monthly, with a rolling window. Use ML.EVALUATE to check the model before you swap it in.
  • Run contribution analysis only on flagged days, so you do not pay for it on every day.
  • Keep an eye on cost. Training scans the history once and scoring scans only the new rows. Filter by date partition and set maximum_bytes_billed on scheduled jobs.

Pitfalls we see in client projects

  1. Training on polluted history. Exclude known outages and one-off backfills from the training query.
  2. Too many tiny series. One model per segment multiplies cost. For many series, use time_series_id_col in a single model instead.
  3. Too many dimensions. Contribution analysis with high-cardinality columns such as user ID produces noise. Bucket them first.
  4. Treating "anomaly" as "incident." Pair every alert with the contribution output so the on-call person gets a likely cause with it.

Where to go next

If you want forecasts rather than detection, see our guide to forecasting in BigQuery. If you want this running as a monitored production pipeline, BQBrains builds BigQuery ML workloads for clients. Get in touch through our contact page.