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_logtable. Alert from there. - Retrain on a cadence, for example monthly, with a rolling window. Use
ML.EVALUATEto 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_billedon scheduled jobs.
Pitfalls we see in client projects
- Training on polluted history. Exclude known outages and one-off backfills from the training query.
- Too many tiny series. One model per segment multiplies cost. For many series, use
time_series_id_colin a single model instead. - Too many dimensions. Contribution analysis with high-cardinality columns such as user ID produces noise. Bucket them first.
- 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.