{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "cMQCs0oQf5Jo"
   },
   "outputs": [],
   "source": [
    "# Copyright 2026 Google LLC\n",
    "#\n",
    "# Licensed under the Apache License, Version 2.0 (the \"License\");\n",
    "# you may not use this file except in compliance with the License.\n",
    "# You may obtain a copy of the License at\n",
    "#\n",
    "#     https://www.apache.org/licenses/LICENSE-2.0\n",
    "#\n",
    "# Unless required by applicable law or agreed to in writing, software\n",
    "# distributed under the License is distributed on an \"AS IS\" BASIS,\n",
    "# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.\n",
    "# See the License for the specific language governing permissions and\n",
    "# limitations under the License.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "iR0jdheRGG89"
   },
   "source": [
    "# Time Series Forecast"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "EiKsD6FM5i0m"
   },
   "source": [
    "| Author |\n",
    "| --- |\n",
    "| [Jeff Nelson](https://github.com/jeffonelson) |"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "intro_md"
   },
   "source": [
    "## Overview\n",
    "\n",
    "Accurate and granular demand forecasting is critical for managing retail inventory, but forecasting for every individual item and location is a major operational challenge. This tutorial demonstrates how to perform scalable time series forecasting directly in [BigQuery](https://cloud.google.com/bigquery/docs/introduction), using the public [Iowa Liquor Sales](https://console.cloud.google.com/marketplace/product/iowa-department-of-commerce/iowa-liquor-sales) dataset as a practical example.\n",
    "\n",
    "In this notebook, you will compare two distinct approaches to generate forecasts:\n",
    "* **A Trained Model:** train a traditional [**ARIMA**](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-create-time-series) model and generating predictions with [BigQuery Machine Learning](https://cloud.google.com/bigquery/docs/bqml-introduction).\n",
    "* **A Zero Shot Model:** use [**TimesFM**](https://github.com/google-research/timesfm), a foundation model, to generate predictions directly from the data with no model training required.\n",
    "\n",
    "You will apply both methods to a single aggregated time series and then scale to multiple series to see how each performs at a granular level."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "SwQLISjqGTHd"
   },
   "source": [
    "### Objectives\n",
    "\n",
    "You will learn to:\n",
    "\n",
    "* Prepare raw data for time series forecasting scenarios in BigQuery\n",
    "* Build a traditional forecasting model using [`CREATE MODEL`](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-create-time-series) AND generate predictions with [`ML.FORECAST`](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-forecast) (ARIMA)\n",
    "* Generate zero-shot forecasts directly from data using the [`AI.FORECAST`](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-ai-forecast) function (TimesFM)\n",
    "* Scale your analysis from a single time series to multiple time series\n",
    "* Visualize and compare the outputs to understand the tradeoffs between the two methods"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "YQ3g-h7uTaSf"
   },
   "source": [
    "### Set your project ID\n",
    "\n",
    "Input your Google Cloud project_id into the box below:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "PROJECT_ID = \"\" # @param {type:\"string\"}\n",
    "LOCATION = \"\" # @param {type:\"string\"}\n",
    "\n",
    "if not PROJECT_ID: \n",
    "    PROJECT_ID = input(\"Enter project id\")\n",
    "if not LOCATION: \n",
    "    LOCATION = input(\"Enter location\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "!pip3 install google-cloud-bigquery google-cloud-bigquery-storage tqdm pandas db-dtypes jupyter ipywidgets -q"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%load_ext google.cloud.bigquery"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "-fhqCNYQOLCq"
   },
   "source": [
    "#### Create a BigQuery Dataset\n",
    "\n",
    "Running the following query creates a [BigQuery dataset](https://cloud.google.com/bigquery/docs/datasets-intro) called **`bq_forecasting`** to house any tables or models created in this tutorial:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "ZR9S5lgpOGDr"
   },
   "outputs": [],
   "source": [
    "%%bigquery --project {PROJECT_ID}\n",
    "\n",
    "CREATE SCHEMA `bq_forecasting` OPTIONS (location = 'US');"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "dip-Ee4sshzh"
   },
   "source": [
    "## Time Series Forecasting with BigQuery"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "1Lg9UGSFspab"
   },
   "source": [
    "This tutorial uses the [Iowa Liquor Sales data](https://console.cloud.google.com/marketplace/product/iowa-department-of-commerce/iowa-liquor-sales), which contains every wholesale purchase of liquor in the state of Iowa from January 1, 2012 to today. We'll use this data for retail demand forecasting.\n",
    "\n",
    "Let's take a quick look at a few rows from the table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "P03CsDZnAeiD"
   },
   "outputs": [],
   "source": [
    "%%bigquery --project {PROJECT_ID}\n",
    "\n",
    "SELECT\n",
    "    invoice_and_item_number,\n",
    "    date,\n",
    "    county,\n",
    "    store_number,\n",
    "    category_name,\n",
    "    item_description,\n",
    "    bottles_sold,\n",
    "    sale_dollars\n",
    "FROM\n",
    "  `bigquery-public-data.iowa_liquor_sales.sales`\n",
    "WHERE sale_dollars > 0\n",
    "LIMIT 5;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "-B4KacMmDCsC"
   },
   "source": [
    "### Data Preparation\n",
    "\n",
    "The `sales` table can contain multiple rows for each date / county / store / item / other field combination. For this tutorial, we'll aggregate the table to generate total sales for each field we're interested in forecasting (e.g. `date`, `item_description`).\n",
    "\n",
    "We'll create two tables for two intended use cases:\n",
    "\n",
    "| Table | Granularity | Usage |\n",
    "|---|---|---|\n",
    "| liquor_sales_training_single | `total_sales` by date | Single time series |\n",
    "| liquor_sales_training_multiple | `total_sales` by multiple fields, including `item_name`| Multiple time series |"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "c3ljkYruspAh"
   },
   "outputs": [],
   "source": [
    "%%bigquery --project {PROJECT_ID}\n",
    "\n",
    "-- SALES AGGREGATED BY DATE, COUNTY, ITEM_NAME\n",
    "CREATE OR REPLACE TABLE bq_forecasting.liquor_sales_training_multiple AS (\n",
    "WITH top_sellers AS(\n",
    "      SELECT\n",
    "        item_description,\n",
    "        SUM( sale_dollars ) AS total_sales\n",
    "    FROM\n",
    "        `bigquery-public-data.iowa_liquor_sales.sales`\n",
    "    GROUP BY\n",
    "        item_description\n",
    "    ORDER BY total_sales DESC\n",
    "    LIMIT 5\n",
    ")\n",
    "SELECT\n",
    "    date,\n",
    "    county,\n",
    "    item_description AS item_name,\n",
    "    SUM( sale_dollars ) AS total_sales\n",
    "FROM\n",
    "    `bigquery-public-data.iowa_liquor_sales.sales`\n",
    "GROUP BY date, county, item_name\n",
    "HAVING\n",
    "    date BETWEEN '2018-01-01' AND '2024-12-31'\n",
    "    AND item_description IN (SELECT item_description FROM top_sellers)\n",
    ");\n",
    "\n",
    "-- SALES AGGREGATED BY DATE\n",
    "CREATE OR REPLACE TABLE bq_forecasting.liquor_sales_training_single AS (\n",
    "SELECT\n",
    "    date,\n",
    "    SUM( total_sales ) AS total_sales\n",
    "FROM\n",
    "    `bq_forecasting.liquor_sales_training_multiple`\n",
    "GROUP BY date\n",
    ");"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "rQAzgnAvc8Ck"
   },
   "source": [
    "## 1. Forecasting a Single Time Series\n",
    "\n",
    "In time series forecasting, a simple scenario is a **single time series**. This means we are looking to predict a single variable over time. Think of it as having one column for your dates and one column for the value you want to predict.\n",
    "\n",
    "**Scenario: Total Sales Over Time**\n",
    "\n",
    "For this example, we'll forecast `total_sales` over time. We aren't breaking down sales by any other category. We will take two approaches using BigQuery ML and see how they compare:\n",
    "\n",
    "1. **ARIMA**: A widely used statistical model that requires you to explicitly train a model on historical data.\n",
    "2. **TimesFM**: A pre-trained foundation model that allows you to generate a forecast with a simple function call, no training required."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "f8b4wR5ILkF8"
   },
   "source": [
    "### Method A: ARIMA"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "24oIqBzncYvV"
   },
   "source": [
    "#### Create a Model\n",
    "\n",
    "Before we can get a forecast, we need to train a dedicated model on our historical data. In BigQuery ML, we accomplish this using the `CREATE MODEL` statement, where we specify our timestamp column (`date`) and the data column we want to forecast (`total_sales`)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "ennEECggcVvW"
   },
   "outputs": [],
   "source": [
    "%%bigquery --project {PROJECT_ID}\n",
    "\n",
    "CREATE OR REPLACE MODEL bq_forecasting.arima_model_single\n",
    "OPTIONS(\n",
    "  MODEL_TYPE='ARIMA_PLUS',\n",
    "  TIME_SERIES_TIMESTAMP_COL='date',\n",
    "  TIME_SERIES_DATA_COL='total_sales'\n",
    ") AS\n",
    "SELECT\n",
    "    date,\n",
    "    total_sales\n",
    "FROM\n",
    "  bq_forecasting.liquor_sales_training_single;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "qyNrE-OxhIBF"
   },
   "source": [
    "#### Generate a Forecast\n",
    "\n",
    "With our `arima_model_single` now trained, we can use it to generate the actual forecast. We do this with the `ML.FORECAST` function. This function takes our trained model as input and requires us to specify the `horizon` (the number of future time steps we want to predict), and a confidence level. It returns the forecasted values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "OW9jRwL7hmbS"
   },
   "outputs": [],
   "source": [
    "%%bigquery df_arima --project {PROJECT_ID}\n",
    "\n",
    "SELECT *\n",
    "FROM ML.FORECAST(MODEL bq_forecasting.arima_model_single,\n",
    "       STRUCT(30 AS horizon,\n",
    "              0.95 AS confidence_level)\n",
    "      );"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "tUjrnS-ujTI_"
   },
   "source": [
    "#### Prepare Historical Data for Visualization\n",
    "\n",
    "Retrieve historical sales data for the single time series, which will be used for visualizing the ARIMA forecast."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "9KTR7-1xjSVl"
   },
   "outputs": [],
   "source": [
    "%%bigquery df_history --project {PROJECT_ID}\n",
    "\n",
    "SELECT\n",
    "    date,\n",
    "    total_sales\n",
    "FROM\n",
    "  bq_forecasting.liquor_sales_training_single\n",
    "WHERE\n",
    "  date BETWEEN '2024-10-01' AND '2024-12-31'\n",
    "ORDER BY date"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "EHrOFugXyX9M"
   },
   "source": [
    "#### Visualize the Forecast\n",
    "\n",
    "To check out our model, we can plot the historical sales data against the forecasted values and their 95% confidence interval."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "aCg-Vx2siaug"
   },
   "outputs": [],
   "source": [
    "import matplotlib.pyplot as plt\n",
    "\n",
    "plt.figure(figsize=(12, 6))\n",
    "plt.plot(df_history['date'], df_history['total_sales'], label='Historical Data')\n",
    "plt.plot(df_arima['forecast_timestamp'], df_arima['forecast_value'], label='ARIMA Forecast')\n",
    "plt.fill_between(df_arima['forecast_timestamp'], df_arima['prediction_interval_lower_bound'], df_arima['prediction_interval_upper_bound'], color='blue', alpha=0.2, label='ARIMA 95% Confidence Interval')\n",
    "plt.xlabel('Timestamp')\n",
    "plt.ylabel('Total Sales')\n",
    "plt.title('ARIMA Forecast vs 2024 Historical Data with 95% Confidence Interval')\n",
    "plt.legend()\n",
    "plt.xticks(rotation=45)\n",
    "plt.tight_layout()\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "EkXY4gVCy_XQ"
   },
   "source": [
    "### Method B: TimesFM\n",
    "\n",
    "Our second method uses TimesFM, which operates differently from \"traditional\" models like ARIMA. TimesFM is a pre-trained foundation model, meaning the complex model training has already been done. As a result, we can skip the `CREATE MODEL` step entirely and apply it directly to our data for a zero-shot forecast.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "XAkTjhpJ1EUq"
   },
   "source": [
    "#### Generate a Forecast\n",
    "\n",
    "To get our forecast, we use the [`AI.FORECAST`](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-ai-forecast) function. Unlike the two-step process for ARIMA (create, then forecast), this single function takes our historical data table directly as its input. We just configure the forecast by passing arguments for the `horizon`, `timestamp_col`, and `data_col`. Then, BigQuery returns the predicted values in one go.\n",
    "\n",
    "Below is a quick look at 5 forecasted days."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "dtzAy3XV1DWY"
   },
   "outputs": [],
   "source": [
    "%%bigquery --project {PROJECT_ID}\n",
    "\n",
    "SELECT *\n",
    "FROM\n",
    "  AI.FORECAST(\n",
    "    (\n",
    "      SELECT *\n",
    "      FROM bq_forecasting.liquor_sales_training_single\n",
    "    ),\n",
    "    horizon => 5,\n",
    "    confidence_level => 0.95,\n",
    "    timestamp_col => 'date',\n",
    "    data_col => 'total_sales')\n",
    "  ;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "dshxhVECMK09"
   },
   "source": [
    "### Prepare TimesFM Data for Plotting"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "pq_Qfe9M3smj"
   },
   "source": [
    "Let's expand the horizon to 30 days of forecasted data and save it to a DataFrame called `df_timesfm` so we can plot it alongside the ARIMA predictions."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "IDw8D-li4Anm"
   },
   "outputs": [],
   "source": [
    "%%bigquery df_timesfm --project {PROJECT_ID}\n",
    "\n",
    "SELECT *\n",
    "FROM\n",
    "  AI.FORECAST(\n",
    "    (\n",
    "      SELECT *\n",
    "      FROM bq_forecasting.liquor_sales_training_single\n",
    "    ),\n",
    "    horizon => 30,\n",
    "    confidence_level => 0.95,\n",
    "    timestamp_col => 'date',\n",
    "    data_col => 'total_sales')\n",
    "  ;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "NhiAxcmM6db5"
   },
   "source": [
    "#### Compare ARIMA and TimesFM Forecasts\n",
    "\n",
    "Plotting the results together provides a clear, side-by-side comparison. We can see that both models successfully identified the strong weekly seasonality in the sales data. However, the ARIMA forecast consistently underestimates the peaks of the sales cycles, while the TimesFM forecast appears to capture the magnitude of the recent historical data more accurately."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "qvHCBBrX5OuS"
   },
   "outputs": [],
   "source": [
    "plt.figure(figsize=(12, 6))\n",
    "plt.plot(df_history['date'], df_history['total_sales'], label='Historical Data')\n",
    "plt.plot(df_arima['forecast_timestamp'], df_arima['forecast_value'], label='ARIMA Forecast')\n",
    "plt.plot(df_timesfm['forecast_timestamp'], df_timesfm['forecast_value'], label='TimesFM Forecast')\n",
    "plt.xlabel('Timestamp')\n",
    "plt.ylabel('Total Sales')\n",
    "plt.title('ARIMA vs TimesFM Forecast')\n",
    "plt.legend()\n",
    "plt.xticks(rotation=45)\n",
    "plt.tight_layout()\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "izKiUVyzbsvA"
   },
   "source": [
    "### Recap\n",
    "\n",
    "In this scenario we:\n",
    "* Addressed a single time series problem by forecasting `total_sales` based on `date`.\n",
    "* Used a two-step process to first train an `ARIMA_PLUS` model with the `CREATE MODEL` statement, and then generate predictions using `ML.FORECAST`.\n",
    "* Used a one-step, zero-shot approach to generate a forecast directly from the data using `AI.FORECAST` and the TimesFM model.\n",
    "* Plotted both forecasts alongside the historical data to visually compare the outputs from each method."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "q_A4zzWousGx"
   },
   "source": [
    "\n",
    "---\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "CC_9GDUo79yY"
   },
   "source": [
    "## 2. Forecasting Multiple Time Series\n",
    "\n",
    "Forecasting a single series is useful, but most real-world scenarios require more granularity. With **multiple time series forecasting**, we can predict many individual time series at the same time. Instead of one forecast for `total_sales` by date, we will generate a unique forecast for every combination of `county` and `item_name`. This is powerful for tasks like inventory management and revenue predictions.\n",
    "\n",
    "Handling this complexity in BigQuery is straightforward. We simply specify which columns uniquely identify each individual time series. Both the ARIMA and TimesFM methods support a parameter that tells BigQuery to partition the data, treating each unique ID as a separate series to be forecasted. With this addition, we can scale forecasting efforts from a _single_ series to thousands or more, without a significant change to our workflow."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "cUwYplgUC_xt"
   },
   "source": [
    "### Method A: ARIMA\n",
    "\n",
    "Once again, we will start with the ARIMA model. This process is very similar to the single series example, but with an additional parameter to tell BigQuery how to handle different series within our dataset."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "rRgz-wifDOBE"
   },
   "source": [
    "#### Create a Model\n",
    "\n",
    "To train the model on multiple time series simultaneously, we introduce the `TIME_SERIES_ID_COL` option. This parameter accepts an array of column names that uniquely identify each series. In our case, the input table is uniquely identified by a combination of `date` and the array [`county`, `item_name`].\n",
    "\n",
    "BigQuery now partitions the data by these IDs and trains a distinct ARIMA model for each individual time series behind the scenes.\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "uNUzKi_b86ML"
   },
   "outputs": [],
   "source": [
    "%%bigquery --project {PROJECT_ID}\n",
    "\n",
    "CREATE OR REPLACE MODEL bq_forecasting.arima_model_multiple\n",
    "OPTIONS(\n",
    "  MODEL_TYPE='ARIMA_PLUS',\n",
    "  TIME_SERIES_TIMESTAMP_COL='date',\n",
    "  TIME_SERIES_DATA_COL='total_sales',\n",
    "  TIME_SERIES_ID_COL=['county','item_name']\n",
    ") AS\n",
    "SELECT\n",
    "    date,\n",
    "    total_sales,\n",
    "    county,\n",
    "    item_name\n",
    "FROM\n",
    "  bq_forecasting.liquor_sales_training_multiple;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "yruu9hg-Dbvi"
   },
   "source": [
    "#### Generate a Forecast\n",
    "\n",
    "After the model is trained, the process to generate a forecast is identical to what we did before. We use the `ML.FORECAST` function to call our new model (`arima_model_multiple`), which inherently understands it needs to produce a separate forecaste for each unique `county` and `item_name` combination.\n",
    "\n",
    "The result contains forecasts for all series. To make sense of the output, we will filter the results to visualize the forecast for single, specific series."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "5tBBX7VkCwga"
   },
   "outputs": [],
   "source": [
    "%%bigquery df_arima_multiple --project {PROJECT_ID}\n",
    "\n",
    "SELECT *\n",
    "FROM ML.FORECAST(MODEL bq_forecasting.arima_model_multiple,\n",
    "       STRUCT(50 AS horizon,\n",
    "              0.95 AS confidence_level)\n",
    "      )\n",
    "WHERE county = 'POLK'\n",
    "AND item_name = 'BLACK VELVET';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "9sK1OjEPFUFB"
   },
   "source": [
    "### Method B: TimesFM\n",
    "\n",
    "Adapting the TimesFM approach for multiple time series is just as straightforward. Since we don't have a model to create, we only need to add one parameter to the existing `AI.FORECAST` query to make it aware of the different series within our data."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "ldXoKrhRFaXU"
   },
   "source": [
    "#### Generate a Forecast\n",
    "\n",
    "The process remains a single-step query, with the key addition of the `id_cols` parameter. We simply pass an array of column names (`['county', 'item_name']`) to tell the function that each unique combination defines a separate time series.\n",
    "\n",
    "The `AI.FORECAST` function computes a forecast for every series in the dataset. As shown in the code, you can use a standard WHERE clause to output and easily filter the results for a specific series of interest. In this case, we'll look at Black Velvet liquor in Polk County."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "4onw5t2DF8Ua"
   },
   "outputs": [],
   "source": [
    "%%bigquery df_timesfm_multiple --project {PROJECT_ID}\n",
    "\n",
    "SELECT *\n",
    "FROM\n",
    "  AI.FORECAST(\n",
    "    (\n",
    "      SELECT *\n",
    "      FROM bq_forecasting.liquor_sales_training_multiple\n",
    "    ),\n",
    "    horizon => 50,\n",
    "    confidence_level => 0.95,\n",
    "    timestamp_col => 'date',\n",
    "    data_col => 'total_sales',\n",
    "    id_cols => ['county', 'item_name']\n",
    "    )\n",
    "  WHERE county = 'POLK'\n",
    "  AND item_name = 'BLACK VELVET'\n",
    "  ;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "xaC0DMogKk48"
   },
   "source": [
    "### Compare Forecasts for a Single Series\n",
    "\n",
    "Now, let's plot the two forecasts for 'BLACK VELVET' in 'POLK' county to visually compare their performance at a granular level. First, we need to retrieve the relevant historical data for our plot."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "UNceRjVWKo1C"
   },
   "outputs": [],
   "source": [
    "%%bigquery df_history_multiple --project {PROJECT_ID}\n",
    "\n",
    "SELECT\n",
    "    date,\n",
    "    county,\n",
    "    item_name,\n",
    "    total_sales\n",
    "FROM\n",
    "  bq_forecasting.liquor_sales_training_multiple\n",
    "WHERE\n",
    "  date BETWEEN '2024-10-01' AND '2025-03-20'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "cutV3uZbsTBL"
   },
   "source": [
    "Our query retrieved all historical data for the last few months. Before we can plot, we'll use pandas to filter this DataFrame down to the specific `county` and `item_name` that matches our forecasts."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "8mhjETXFKqeE"
   },
   "outputs": [],
   "source": [
    "df_history_filtered = df_history_multiple[(df_history_multiple['county'] == 'POLK') & (df_history_multiple['item_name'] == 'BLACK VELVET')]\n",
    "plt.figure(figsize=(12, 6))\n",
    "plt.plot(df_history_filtered['date'], df_history_filtered['total_sales'], label='Historical Data')\n",
    "plt.plot(df_arima_multiple['forecast_timestamp'], df_arima_multiple['forecast_value'], label='ARIMA Forecast')\n",
    "plt.plot(df_timesfm_multiple['forecast_timestamp'], df_timesfm_multiple['forecast_value'], label='TimesFM Forecast')\n",
    "plt.xlabel('Timestamp')\n",
    "plt.ylabel('Total Sales')\n",
    "plt.title('ARIMA vs TimesFM Forecast for BLACK VELVET in POLK County')\n",
    "plt.legend()\n",
    "plt.xticks(rotation=45)\n",
    "plt.tight_layout()\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "gQgWziUZsZVc"
   },
   "source": [
    "As with the single series, both models capture the general sales patterns. But the difference in their approaches becomes much clearer at this granular level. The **ARIMA forecast is notably more volatile**, attempting to predict the full amplitude of the historical peaks and troughs. In contrast, the **TimesFM forecast is smoother**, capturing the cyclical trend with a more conservative range.\n",
    "\n",
    "It's important to look at these forecasts visually, because it highlights the importance of evaluating models not just on aggregate data but also on the individual series that drive key business decisions."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "muvZ1BIGMVlv"
   },
   "source": [
    "### Recap\n",
    "\n",
    "In this section, we:\n",
    "* Moved to multiple time series, forecasting `total_sales` for each unique combination of `county` and `item_name`.\n",
    "* Adapted our ARIMA model by adding the `TIME_SERIES_ID_COL` parameter to the `CREATE MODEL` statement.\n",
    "* Adapted our TimesFM query by adding the `id_cols` parameter to the `AI.FORECAST` function.\n",
    "* Demonstrated how a single parameter change in BigQuery allows us to easily scale our forecasting efforts from one to many individual series."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "A96fx8hstrc9"
   },
   "source": [
    "### Choosing Your Model: Key Trade-Offs\n",
    "\n",
    "Both ARIMA and TimesFM are powerful approaches, but they are suited for different scenarios. The choice often comes down to a trade-off between detailed statistical explainability and the speed of zero-shot forecasting.\n",
    "\n",
    "* **Choose ARIMA** when you need more control and detailed statistical insights from your model, like detected seasonality and trend components.\n",
    "* **Choose TimesFM** when your priority is generating high-quality forecasts quickly and at scale, especially when you need to automate forecasting without a dedicated training step for each series.\n",
    "\n",
    "For a more detailed breakdown of the differences, see the [forecasting overview in the BigQuery documentation](https://cloud.google.com/bigquery/docs/forecasting-overview)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "QpoY5tWj84zv"
   },
   "source": [
    "\n",
    "\n",
    "---\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "cleanup_md"
   },
   "source": [
    "# Cleaning Up\n",
    "\n",
    "To clean up all Google Cloud resources used in this project, you can [delete the Google Cloud project](https://cloud.google.com/resource-manager/docs/creating-managing-projects#shutting_down_projects) you used for the tutorial.\n",
    "\n",
    "Otherwise, you can delete the individual resources you created in this tutorial:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "yRzpOQf_Wqqi"
   },
   "outputs": [],
   "source": [
    "# Delete the BigQuery tables\n",
    "! bq rm --table -f bq_forecasting.liquor_sales_training_single\n",
    "! bq rm --table -f bq_forecasting.liquor_sales_training_multiple\n",
    "\n",
    "# Delete the ARIMA models\n",
    "! bq rm --model -f bq_forecasting.arima_model_single\n",
    "! bq rm --model -f bq_forecasting.arima_model_multiple\n",
    "\n",
    "# Delete the BigQuery dataset\n",
    "! bq rm -r -f {PROJECT_ID}:bq_forecasting"
   ]
  }
 ],
 "metadata": {
  "colab": {
   "collapsed_sections": [
    "td9kx9LVgSve"
   ],
   "provenance": [],
   "toc_visible": true
  },
  "kernelspec": {
   "display_name": "myenv",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.13.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}