Forecast a single time series with a TimesFM multivariate model

This tutorial teaches you how to perform multivariate forecasting across a single time series by using the AI.FORECAST function with the TimesFM 3.0 model that's built into BigQuery ML. Multivariate forecasting helps you improve forecast accuracy by incorporating other variables that influence the target.

This tutorial demonstrates how to forecast vodka and whiskey sales by incorporating historical transaction values (a past covariate) and whether the date is a weekend (a future covariate).

Objectives

  • Prepare a single unified dataset for multivariate forecasting.
  • Use the AI.FORECAST function with the TimesFM 3.0 model to generate predictions.

Costs

This tutorial uses billable components of Cloud de Confiance by S3NS, including the following:

  • BigQuery
  • BigQuery ML

For more information, see BigQuery pricing and BigQuery ML pricing.

Before you begin

  1. In the Cloud de Confiance console, on the project selector page, select or create a Cloud de Confiance project.

    Roles required to select or create a project

    • Select a project: Selecting a project doesn't require a specific IAM role—you can select any project that you've been granted a role on.
    • Create a project: To create a project, you need the Project Creator role (roles/resourcemanager.projectCreator), which contains the resourcemanager.projects.create permission. Learn how to grant roles.

    Go to project selector

  2. Verify that billing is enabled for your Cloud de Confiance project.

  3. Enable the BigQuery API, if it is not already enabled.

    Roles required to enable APIs

    To enable APIs, you need the serviceusage.services.enable permission. If you created the project, then you likely already have this permission through the Owner role (roles/owner). Otherwise, you can get this permission through the Service Usage Admin role (roles/serviceusage.serviceUsageAdmin). Learn how to grant roles.

    Enable the API

    For new projects, the BigQuery API is automatically enabled.

Required roles

To get the permissions that you need to complete the tasks in this tutorial, ask your administrator to grant you the following IAM roles:

For more information about granting roles, see Manage access to projects, folders, and organizations.

These predefined roles contain the permissions required to complete the tasks in this tutorial. To see the exact permissions that are required, expand the Required permissions section:

Required permissions

The following permissions are required to complete the tasks in this tutorial:

  • Create the dataset: bigquery.datasets.create
  • Create the model:
    • bigquery.jobs.create
    • bigquery.models.create
    • bigquery.models.getData
    • bigquery.models.updateData
  • Run inference:
    • bigquery.models.getData
    • bigquery.jobs.create

You might also be able to get these permissions with custom roles or other predefined roles.

For more information about IAM roles and permissions in BigQuery, see Introduction to IAM.

Prepare the input data

Unlike other multivariate models that might require separate tables for historical and future covariates, the AI.FORECAST function with TimesFM 3.0 expects a single input table or query.

Before running a forecast, ensure your data meets these requirements:

  • Historical rows: contain non-null values for timestamps, targets, and past covariates.
  • Future rows: contain non-null values for timestamps and future covariates only. Target columns and past covariate columns must be set to NULL for these future rows.

Your input table must contain columns with the following data:

  • The date of the sales.
  • The number of vodka bottles sold on that date. This is a target column.
  • The number of whiskey bottles sold on that date. This is a target column.
  • The average transaction value on that date. This is a past covariate used in the forecast.
  • Whether the date is a weekend. This is a future covariate used in the forecast.

Forecast the single multivariate time series

The following query forecasts number of vodka and whiskey bottles sold for the first two weeks of July, 2023 at store number 2633. It uses the average transaction value as a past covariate and whether the date is a weekend as a future covariate.

Follow these steps to forecast data with the TimesFM 3.0 model:

  1. In the Cloud de Confiance console, go to the BigQuery page.

    Go to BigQuery

  2. In the query editor, paste in the following query and click Run:

    WITH historical_sales AS (
  SELECT
    date AS sales_date,
    store_number,
    SUM(CASE WHEN category_name LIKE '%VODKA%' THEN bottles_sold ELSE 0 END) AS vodka_bottles,
    SUM(CASE WHEN category_name LIKE '%WHISKEY%' THEN bottles_sold ELSE 0 END) AS whiskey_bottles,
    AVG(sale_dollars) AS avg_transaction_value,                          -- Past covariate
    IF(EXTRACT(DAYOFWEEK FROM date) IN (1, 7), 1.0, 0.0) AS is_weekend   -- Future covariate
  FROM `bigquery-public-data.iowa_liquor_sales.sales`
  WHERE date BETWEEN '2023-01-01' AND '2023-06-30'
    AND store_number = '2633'
  GROUP BY sales_date, store_number
),
future_horizon AS (
  SELECT
    future_date AS sales_date,
    store_number,
    CAST(NULL AS INT64) AS vodka_bottles,
    CAST(NULL AS INT64) AS whiskey_bottles,
    CAST(NULL AS FLOAT64) AS avg_transaction_value,
    IF(EXTRACT(DAYOFWEEK FROM future_date) IN (1, 7), 1.0, 0.0) AS is_weekend
  FROM UNNEST(GENERATE_DATE_ARRAY('2023-07-01', '2023-07-14')) AS future_date
  CROSS JOIN (SELECT DISTINCT store_number FROM historical_sales)
),
combined_sales AS (
  SELECT * FROM historical_sales
  UNION ALL
  SELECT * FROM future_horizon
)
SELECT
  FORMAT_DATE('%Y-%m-%d', sales_date) AS sales_date,
  vodka_bottles.value AS vodka_value,
  vodka_bottles.prediction_interval_lower_bound AS vodka_lower,
  vodka_bottles.prediction_interval_upper_bound AS vodka_upper,
  whiskey_bottles.value AS whiskey_value,
  whiskey_bottles.prediction_interval_lower_bound AS whiskey_lower,
  whiskey_bottles.prediction_interval_upper_bound AS whiskey_upper
FROM AI.FORECAST(
  TABLE combined_sales,
  model => 'TimesFM 3.0',
  timestamp_col => 'sales_date',
  target_cols => ['vodka_bottles', 'whiskey_bottles'],
  past_covariate_cols => ['avg_transaction_value'],
  future_covariate_cols => ['is_weekend'],
  id_cols => ['store_number'],
  horizon => 14,
  confidence_level => 0.90
);

The output is similar to the following, with values rounded for clarity:

+------------+-------------+-------------+-------------+---------------+---------------+---------------+
| sales_date | vodka_value | vodka_lower | vodka_upper | whiskey_value | whiskey_lower | whiskey_upper |
+------------+-------------+-------------+-------------+---------------+---------------+---------------+
| 2023-07-01 | 408         | -334        | 1977        | 21            | -34           | 113           |
| 2023-07-02 | 806         | -281        | 2992        | 29            | -23           | 149           |
| ...        | ...         | ...         | ...         | ...           | ...           | ...           |
+------------+-------------+-------------+-------------+---------------+---------------+---------------+

Clean up

To avoid incurring charges to your Google Cloud account for the resources used in this tutorial, either delete the project that contains the resources, or keep the project and delete the individual resources.

Delete your project

  1. In the Cloud de Confiance console, go to the Manage resources page.

    Go to Manage resources

  2. In the project list, select the project that you want to delete, and then click Delete.
  3. In the dialog, type the project ID, and then click Shut down to delete the project.

What's next