Forecast multiple time series with a TimesFM multivariate model

This tutorial teaches you how to perform multivariate forecasting across multiple 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 (covariates) that influence the target.

By specifying identifier columns, you can generate forecasts for multiple distinct entities simultaneously. This tutorial demonstrates how to forecast New York taxi trips and fares for different pickup zones by incorporating historical trip distance data, which is a past covariate.

Objectives

  • Prepare a unified dataset for multiple time series.
  • Generate predictions for New York taxi data by using the AI.FORECAST function with the TimesFM 3.0 model.

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.

For a multiple time series forecast, your data must include one or more identifier columns to separate the data into distinct time series. In this example, one time series contains data for pickup location 132 (JFK Airport) and the other time series contains data for pickup location 138 (LaGuardia Airport). Make sure your data meets the following requirements for every individual time series:

  • Historical rows: contain non-null values for timestamps, targets, and past covariates.

Because this example doesn't use future covariates, the input query only needs to provide historical data. Your input table must contain columns with the following data:

  • The date of the taxi trip.
  • The pickup location: either 132 or 138.
  • The number of trips on that date. This is a target column.
  • The average fare amount on that date. This is a target column.
  • The average distance of trips on that date. This is a past covariate column used in the forecast.

Forecast the multiple multivariate time series

The following query forecasts the number of trips and average fare per day for the next 14 days for the JFK and LaGuardia pickup zones. It defines past_cov_avg_distance as a feature that's only known historically. You must provide the id_cols argument so the model knows how to separate the different time series.

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:

    SELECT
  pickup_location_id,
  FORMAT_DATE("%Y-%m-%d", pickup_date) AS pickup_date,
  # Extract the forecast value and prediction intervals for the number of trips target
  target_num_trips.value AS forecasted_num_trips,
  target_num_trips.prediction_interval_lower_bound AS num_trips_lower,
  target_num_trips.prediction_interval_upper_bound AS num_trips_upper,
  # Extract the forecast value and prediction intervals for the average fare target
  target_avg_fare.value AS forecasted_avg_fare,
  target_avg_fare.prediction_interval_lower_bound AS avg_fare_lower,
  target_avg_fare.prediction_interval_upper_bound AS avg_fare_upper
FROM AI.FORECAST(
  (
    SELECT
      DATE(pickup_datetime) AS pickup_date,
      pickup_location_id,
      COUNT(*) AS target_num_trips,
      AVG(fare_amount) AS target_avg_fare,
      AVG(trip_distance) AS past_cov_avg_distance
    FROM `bigquery-public-data.new_york_taxi_trips.tlc_yellow_trips_2022`
    WHERE pickup_datetime >= "2022-01-01" AND pickup_datetime < "2022-04-01"
      AND pickup_location_id IN ("132", "138") -- JFK and LaGuardia Airports
    GROUP BY 1, 2
  ),
  model => "TimesFM 3.0",
  target_cols => ["target_num_trips", "target_avg_fare"],
  past_covariate_cols => ["past_cov_avg_distance"],
  timestamp_col => "pickup_date",
  id_cols => ["pickup_location_id"],
  horizon => 14
)
ORDER BY pickup_date;

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

+--------------------+-------------+----------------------+-----------------+-----------------+---------------------+----------------+----------------+
| pickup_location_id | pickup_date | forecasted_num_trips | num_trips_lower | num_trips_upper | forecasted_avg_fare | avg_fare_lower | avg_fare_upper |
+--------------------+-------------+----------------------+-----------------+-----------------+---------------------+----------------+----------------+
| 132                | 2022-04-01  | 5240                 | 4633            | 5842            | 47                  | 46             | 48             |
| 138                | 2022-04-01  | 3451                 | 2848            | 4008            | 32                  | 31             | 34             |
| ...                | ...         | ...                  | ...             | ...             | ...                 | ...            | ...            |
+--------------------+-------------+----------------------+-----------------+-----------------+---------------------+----------------+----------------+

The results show the forecasted number of trips and average fare amount for the next 14 days at the JFK and LaGuardia pickup zones. The results also include the lower and upper bounds for a 95% prediction interval for each forecasted value.

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