Forecasting Daily Revenue with ML.FORECAST

What ML.FORECAST is:

ML.FORECAST is a specialized BigQuery ML table-valued function used to perform batch inference on trained time-series models (such as ARIMA_PLUS and ARIMA_PLUS_XREG). It projects future values across a specified temporal range while calculating lower and upper bound prediction intervals directly within SQL queries.

How it meets the requirements:

• Forecasts future values (✓): ML.FORECAST executes multi-step ahead time-series forecasting, generating expected values for upcoming timestamps based on learned trends and seasonality.
• Configures the forecast horizon (✓): Setting 30 AS horizon explicitly instructs the model to predict the next 30 discrete time intervals (days) ahead from the end of the historical training data.
• Defines the confidence level (✓): Setting 0.8 AS confidence_level produces prediction intervals bounded between lower and upper prediction limits at an 80% confidence interval.
• Native SQL execution (✓): Runs directly in BigQuery without requiring data export or external machine learning pipelines.

Key advantages and capabilities:

• Automated decomposition: Automatically captures complex trend changes, daily/weekly/annual seasonality, and holiday effects.
• Prediction intervals: Returns forecast_value, standard_error, lower_bound, and upper_bound for each step in the horizon.
• Flexible parameter tuning: Accepts custom parameters in a STRUCT, including horizon (default is 30 or 1000 depending on cadence) and confidence_level (between 0.0 and 1.0; default is 0.95).
• Scalable multi-series support: Can forecast multiple distinct time series simultaneously when trained with the TIME_SERIES_ID_COL parameter.

Why this is optimal:

ML.FORECAST is the standard, purpose-built function in BigQuery ML for time-series projections using ARIMA_PLUS. It directly implements the required horizon and confidence parameters with proper decimal formatting (0.8 for 80%), avoiding the overhead of custom inference scripts or general-purpose prediction functions.