notebooks/official/workbench/pricing_optimization/pricing-optimization.ipynb

{
  "cells": [
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "d1cc1c1fa076"
      },
      "outputs": [],
      "source": [
        "# Copyright 2022 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."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "9751bc48dbcb"
      },
      "source": [
        "# Analysis of pricing optimization on CDM Pricing Data\n",
        "\n",
        "<table align=\"left\">\n",
        "  <td style=\"text-align: center\">\n",
        "    <a href=\"https://colab.research.google.com/github/GoogleCloudPlatform/vertex-ai-samples/blob/main/notebooks/official/workbench/pricing_optimization/pricing-optimization.ipynb\">\n",
        "      <img src=\"https://cloud.google.com/ml-engine/images/colab-logo-32px.png\" alt=\"Google Colaboratory logo\"><br> Open in Colab\n",
        "    </a>\n",
        "  </td>\n",
        "  <td style=\"text-align: center\">\n",
        "    <a href=\"https://console.cloud.google.com/vertex-ai/colab/import/https:%2F%2Fraw.githubusercontent.com%2FGoogleCloudPlatform%2Fvertex-ai-samples%2Fmain%2Fnotebooks%2Fofficial%2Fworkbench%2Fpricing_optimization%2Fpricing-optimization.ipynb\">\n",
        "      <img width=\"32px\" src=\"https://cloud.google.com/ml-engine/images/colab-enterprise-logo-32px.png\" alt=\"Google Cloud Colab Enterprise logo\"><br> Open in Colab Enterprise\n",
        "    </a>\n",
        "  </td>    \n",
        "  <td style=\"text-align: center\">\n",
        "    <a href=\"https://console.cloud.google.com/vertex-ai/workbench/deploy-notebook?download_url=https://raw.githubusercontent.com/GoogleCloudPlatform/vertex-ai-samples/main/notebooks/official/workbench/pricing_optimization/pricing-optimization.ipynb\">\n",
        "      <img src=\"https://lh3.googleusercontent.com/UiNooY4LUgW_oTvpsNhPpQzsstV5W8F7rYgxgGBD85cWJoLmrOzhVs_ksK_vgx40SHs7jCqkTkCk=e14-rj-sc0xffffff-h130-w32\" alt=\"Vertex AI logo\"><br> Open in Workbench\n",
        "    </a>\n",
        "  </td>\n",
        "  <td style=\"text-align: center\">\n",
        "    <a href=\"https://github.com/GoogleCloudPlatform/vertex-ai-samples/blob/main/notebooks/official/workbench/pricing_optimization/pricing-optimization.ipynb\">\n",
        "      <img src=\"https://cloud.google.com/ml-engine/images/github-logo-32px.png\" alt=\"GitHub logo\"><br> View on GitHub\n",
        "    </a>\n",
        "  </td>\n",
        "</table>\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "8414ceb17c47"
      },
      "source": [
        "## Overview\n",
        "<a name=\"section-1\"></a>\n",
        "\n",
        "This notebook demonstrates analysis of pricing optimization on [CDM Pricing Data](https://github.com/trifacta/trifacta-google-cloud/tree/main/design-pattern-pricing-optimization) and automating the workflow using Vertex AI Workbench managed notebooks.\n",
        "\n",
        "**Note**: This notebook file is developed to run in a [Vertex AI Workbench managed notebooks](https://console.cloud.google.com/vertex-ai/workbench/list/managed) instance using the Python (Local) kernel. Some components of this notebook may not work in other notebook environments.\n",
        "\n",
        "Learn more about [Vertex AI Workbench](https://cloud.google.com/vertex-ai/docs/workbench/introduction) and Learn more about [BigQuery ML](https://cloud.google.com/vertex-ai/docs/beginner/bqml#machine_learning_directly_in)."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "71f69cfdff2b"
      },
      "source": [
        "### Objective\n",
        "\n",
        "The objective of this notebook is to build a pricing optimization model using BigQuery ML.\n",
        "\n",
        "This tutorial uses the following Google Cloud ML services and resources:\n",
        "\n",
        "- Google Cloud Storage\n",
        "- BigQuery\n",
        "\n",
        "\n",
        "The steps performed include:\n",
        "\n",
        "- Load the required dataset from a Cloud Storage bucket.\n",
        "- Analyze the fields present in the dataset.\n",
        "- Process the data to build a model.\n",
        "- Build a BigQuery ML forecast model on the processed data.\n",
        "- Get forecasted values from the BigQuery ML model.\n",
        "- Interpret the forecasts to identify the best prices.\n",
        "- Clean up.\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "d20422a5c34d"
      },
      "source": [
        "### Dataset\n",
        "<a name=\"section-3\"></a>\n",
        "\n",
        "The dataset used in this notebook is a part of the [CDM Pricing dataset](https://github.com/trifacta/trifacta-google-cloud/blob/main/design-pattern-pricing-optimization/CDM_Pricing_large_table.csv), which consists of product sales information on specified dates."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "c05bcd30859d"
      },
      "source": [
        "### Costs\n",
        "<a name=\"section-4\"></a>\n",
        "\n",
        "This tutorial uses the following billable components of Google Cloud:\n",
        "\n",
        "- Vertex AI\n",
        "- BigQuery\n",
        "- Cloud Storage\n",
        "\n",
        "\n",
        "Learn about [Vertex AI\n",
        "pricing](https://cloud.google.com/vertex-ai/pricing), [BigQuery pricing](https://cloud.google.com/bigquery/pricing) and [Cloud Storage\n",
        "pricing](https://cloud.google.com/storage/pricing), and use the [Pricing\n",
        "Calculator](https://cloud.google.com/products/calculator/)\n",
        "to generate a cost estimate based on your projected usage."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "61RBz8LLbxCR"
      },
      "source": [
        "## Get started"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "No17Cw5hgx12"
      },
      "source": [
        "### Install Vertex AI SDK for Python and other required packages\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "25fffcad67f0"
      },
      "outputs": [],
      "source": [
        "! pip3 install --quiet --upgrade pandas-gbq 'google-cloud-bigquery[bqstorage,pandas]' seaborn fsspec gcsfs\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "R5Xep4W9lq-Z"
      },
      "source": [
        "### Restart runtime (Colab only)\n",
        "\n",
        "To use the newly installed packages, you must restart the runtime on Google Colab."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "XRvKdaPDTznN"
      },
      "outputs": [],
      "source": [
        "import sys\n",
        "\n",
        "if \"google.colab\" in sys.modules:\n",
        "\n",
        "    import IPython\n",
        "\n",
        "    app = IPython.Application.instance()\n",
        "    app.kernel.do_shutdown(True)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "SbmM4z7FOBpM"
      },
      "source": [
        "<div class=\"alert alert-block alert-warning\">\n",
        "<b>⚠️ The kernel is going to restart. Wait until it's finished before continuing to the next step. ⚠️</b>\n",
        "</div>\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "dmWOrTJ3gx13"
      },
      "source": [
        "### Authenticate your notebook environment (Colab only)\n",
        "\n",
        "Authenticate your environment on Google Colab.\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "NyKGtVQjgx13"
      },
      "outputs": [],
      "source": [
        "import sys\n",
        "\n",
        "if \"google.colab\" in sys.modules:\n",
        "\n",
        "    from google.colab import auth\n",
        "\n",
        "    auth.authenticate_user()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "DF4l8DTdWgPY"
      },
      "source": [
        "### Set Google Cloud project information\n",
        "\n",
        "To get started using Vertex AI, you must have an existing Google Cloud project. Learn more about [setting up a project and a development environment](https://cloud.google.com/vertex-ai/docs/start/cloud-environment)."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "be175254a715"
      },
      "outputs": [],
      "source": [
        "PROJECT_ID = \"[your-project-id]\"  # @param {type:\"string\"}\n",
        "LOCATION = \"us-central1\"  # @param {type:\"string\"}\n",
        "\n",
        "# set the project id\n",
        "! gcloud config set project {PROJECT_ID}"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "2a1c270c7d34"
      },
      "source": [
        "### Import the required libraries and define constants\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "acc6fac1fa55"
      },
      "outputs": [],
      "source": [
        "import matplotlib.pyplot as plt\n",
        "import pandas as pd\n",
        "import seaborn as sns\n",
        "from google.cloud import bigquery\n",
        "from google.cloud.bigquery import Client"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "3d5ff24d3194"
      },
      "source": [
        "#### Set the BigQuery dataset ID and table ID"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "a06006dff8f9"
      },
      "outputs": [],
      "source": [
        "DATASET = \"pricing_optimization_unique\"  # set the BigQuery dataset-id\n",
        "TRAINING_DATA_TABLE = (\n",
        "    \"training_data_table\"  # set the BigQuery table-id to store the training data\n",
        ")"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "016c3d47cc69"
      },
      "source": [
        "## Create a BigQuery dataset\n",
        "<a name=\"section-5\"></a>\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "3a063f530682"
      },
      "source": [
        "If you're using ***Vertex AI Workbench managed notebooks instance***, every cell which starts with \"#@bigquery\" is a SQL Query. If you're using a Vertex AI Workbench user-managed notebooks instance or Colab, it's a markdown cell."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "12ccd8d7956e"
      },
      "source": [
        "#@bigquery\n",
        "-- create a dataset in BigQuery\n",
        "\n",
        "CREATE SCHEMA [your-dataset-id]\n",
        "OPTIONS(\n",
        "  location=\"us\"\n",
        "  )"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "00bd69008c92"
      },
      "outputs": [],
      "source": [
        "# Construct a BigQuery client object.\n",
        "client = Client(project=PROJECT_ID)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "5f7acd204413"
      },
      "outputs": [],
      "source": [
        "query = \"\"\"\n",
        "CREATE SCHEMA {DATASET}\n",
        "OPTIONS(\n",
        "  location=\"us\"\n",
        "  )\n",
        "\"\"\".format(\n",
        "    DATASET=DATASET\n",
        ")\n",
        "query_job = client.query(query)\n",
        "print(query_job.result())"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "c106b978a79b"
      },
      "source": [
        "## Load the BigQuery table from cloud storage\n",
        "<a name=\"section-6\"></a>\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "4e8d73657382"
      },
      "outputs": [],
      "source": [
        "table_id_name = f\"{PROJECT_ID}.{DATASET}.data\""
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "9592fa5370c4"
      },
      "outputs": [],
      "source": [
        "table_id = \"data\""
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "6a93b9314646"
      },
      "outputs": [],
      "source": [
        "job_config = bigquery.LoadJobConfig(\n",
        "    autodetect=True,\n",
        "    skip_leading_rows=1,\n",
        "    # The source format defaults to CSV, so the line below is optional.\n",
        "    source_format=bigquery.SourceFormat.CSV,\n",
        ")\n",
        "uri = \"gs://cloud-samples-data/ai-platform-unified/datasets/tabular/cdm_pricing_large_table.csv\"\n",
        "\n",
        "load_job = client.load_table_from_uri(\n",
        "    uri, table_id_name, job_config=job_config\n",
        ")  # Make an API request.\n",
        "\n",
        "load_job.result()  # Waits for the job to complete.\n",
        "\n",
        "destination_table = client.get_table(table_id_name)  # Make an API request.\n",
        "print(\"Loaded {} rows.\".format(destination_table.num_rows))"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "7b98d5f09842"
      },
      "source": [
        "Build a forecast model on this data to determine the best price for a product. For this type of model, you need only a few fields: the sales and price related ones. For the current exercise, focus is on the following fields:\n",
        "\n",
        "- `Product_ID`\n",
        "- `Customer_Hierarchy`\n",
        "- `Fiscal_Date`\n",
        "- `List_Price_Converged`\n",
        "- `Invoiced_quantity_in_Pieces`\n",
        "- `Net_Sales`\n",
        "\n",
        "## Data Analysis\n",
        "\n",
        "\n",
        "First, explore the data and it's distributions.\n",
        "\n",
        "#### Select the required columns from the dataframe."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "af4b41c5eb1f"
      },
      "outputs": [],
      "source": [
        "id_col = \"Product_ID\"\n",
        "date_col = \"Fiscal_Date\"\n",
        "categ_cols = [\"Customer_Hierarchy\"]\n",
        "num_cols = [\"List_Price_Converged\", \"Invoiced_quantity_in_Pieces\", \"Net_Sales\"]\n",
        "required_columns = [id_col] + [date_col] + categ_cols + num_cols\n",
        "required_columns"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "c2d25505947c"
      },
      "source": [
        "Create a view to extract only the required columns."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "8b96f7f20797"
      },
      "outputs": [],
      "source": [
        "query = \"\"\"\n",
        "    CREATE OR REPLACE TABLE {DATASET}.required_columns AS\n",
        "    ( SELECT Product_ID,Fiscal_Date,Customer_Hierarchy,List_Price_Converged,Invoiced_quantity_in_Pieces,Net_Sales FROM `{DATASET}.{table_id}` )\n",
        "    \n",
        "\"\"\".format(\n",
        "    DATASET=DATASET, table_id=table_id\n",
        ")\n",
        "\n",
        "query_job = client.query(query)  # Make an API request.\n",
        "print(query_job.result())"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "21ec9e50ba23"
      },
      "source": [
        "Inspect data in the `required_columns` view"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "bcd377aaed16"
      },
      "outputs": [],
      "source": [
        "query = \"\"\"\n",
        "    SELECT * FROM {DATASET}.required_columns \n",
        "    \n",
        "\"\"\".format(\n",
        "    DATASET=DATASET\n",
        ")\n",
        "\n",
        "query_job = client.query(query)  # Make an API request.\n",
        "print(query_job.result())"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "1e59ad4e0c30"
      },
      "outputs": [],
      "source": [
        "query_job.to_dataframe()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "3d780043ee5b"
      },
      "source": [
        "#### Check the column types and null values in the dataframe."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "f54c445a1288"
      },
      "outputs": [],
      "source": [
        "query_job.to_dataframe().info()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "cd817b414c4d"
      },
      "source": [
        "This data description reveals that there are no null values in the data. Also, the field `Fiscal_Date` which is a date field is loaded as an object type. \n",
        "\n",
        "#### Change the type of the date field to datetime."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ae528c679547"
      },
      "source": [
        "Change the `Fiscal_Date` data type from datetime to date and store the resulting data in a view."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "7391f2e0ca48"
      },
      "outputs": [],
      "source": [
        "query = \"\"\"\n",
        "CREATE OR REPLACE VIEW {DATASET}.required_columns_final AS\n",
        "(\n",
        "SELECT Product_ID,Customer_Hierarchy,List_Price_Converged,Invoiced_quantity_in_Pieces,Net_Sales,CAST(DATE(Fiscal_Date) AS DATE) AS Fiscal_Date FROM {DATASET}.required_columns    \n",
        ")\n",
        "\"\"\".format(\n",
        "    DATASET=DATASET\n",
        ")\n",
        "\n",
        "query_job = client.query(query)  # Make an API request.\n",
        "print(query_job.result())"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "0c4f4ac078fb"
      },
      "source": [
        "Inspect data in the `required_columns_final` view"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "c0c044768ff8"
      },
      "outputs": [],
      "source": [
        "query = \"\"\"\n",
        "SELECT * FROM {DATASET}.required_columns_final\n",
        "\n",
        "\"\"\".format(\n",
        "    DATASET=DATASET\n",
        ")\n",
        "\n",
        "query_job = client.query(query)  # Make an API request.\n",
        "print(query_job.result())"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "2a18b844eb1d"
      },
      "outputs": [],
      "source": [
        "required_columns_final_df = query_job.to_dataframe()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "4b1ba7de05fb"
      },
      "outputs": [],
      "source": [
        "required_columns_final_df"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "fb4778578064"
      },
      "source": [
        "#### Plot the distributions for the categorical fields."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "6e9d007b1f5c"
      },
      "outputs": [],
      "source": [
        "for i in categ_cols:\n",
        "    required_columns_final_df[i].value_counts(normalize=True).plot(kind=\"bar\")\n",
        "    plt.title(i)\n",
        "    plt.show()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "145deed255e0"
      },
      "source": [
        "#### Plot the distributions for the numerical fields."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "decb758d57dd"
      },
      "outputs": [],
      "source": [
        "for i in num_cols:\n",
        "    _, ax = plt.subplots(1, 2, figsize=(10, 4))\n",
        "    required_columns_final_df[i].plot(kind=\"box\", ax=ax[0])\n",
        "    required_columns_final_df[i].plot(kind=\"hist\", ax=ax[1])\n",
        "    ax[0].set_title(i + \"-Boxplot\")\n",
        "    ax[1].set_title(i + \"-Histogram\")\n",
        "    plt.show()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "f9b9c2e58380"
      },
      "source": [
        "#### Check the maximum date and minimum date in Fiscal_Date column."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "db9256b17109"
      },
      "outputs": [],
      "source": [
        "print(required_columns_final_df[\"Fiscal_Date\"].max())\n",
        "print(required_columns_final_df[\"Fiscal_Date\"].min())"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "4834f63e2e59"
      },
      "source": [
        "#### Check the product distribution across each category."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "0cd7662b34e8"
      },
      "outputs": [],
      "source": [
        "query = \"\"\"\n",
        "SELECT Customer_Hierarchy,COUNT(*) as count FROM (SELECT Customer_Hierarchy,Product_ID FROM {DATASET}.required_columns_final GROUP BY Customer_Hierarchy,Product_ID) GROUP BY  Customer_Hierarchy\n",
        "\"\"\".format(\n",
        "    DATASET=DATASET\n",
        ")\n",
        "query_job = client.query(query)\n",
        "print(query_job.result())"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "fe44a3318678"
      },
      "outputs": [],
      "source": [
        "query_job.to_dataframe()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "01ed02b9c8fd"
      },
      "source": [
        "#### Check the percentage changes in the orders based on the percentage changes in the price."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "8d15f712edf2"
      },
      "source": [
        "Execute the following steps to check percentage changes in the orders based on the percentage changes in their price.\n",
        "\n",
        "**Step 1**: First, generate a table that records each instance of a price change for a product. Include details such as the number of items ordered at each price point and the total net sales associated with that price."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "bb697268c17b"
      },
      "outputs": [],
      "source": [
        "query = \"\"\"\n",
        "create table {DATASET}.price_changes as (\n",
        "select\n",
        "       product_id,\n",
        "       list_price_converged,\n",
        "       total_ordered_pieces,\n",
        "       total_net_sales,\n",
        "       first_price_date,\n",
        "       lag(list_price_converged) over(partition by product_id order by first_price_date asc) as previous_list,\n",
        "       lag(total_ordered_pieces) over(partition by product_id order by first_price_date asc) as previous_total_ordered_pieces,\n",
        "       lag(total_net_sales) over(partition by product_id order by first_price_date asc) as previous_total_net_sales,\n",
        "       lag(first_price_date) over(partition by product_id order by first_price_date asc) as previous_first_price_date,\n",
        "       \n",
        "       \n",
        "       from (\n",
        "           select\n",
        "               product_id,list_price_converged,sum(invoiced_quantity_in_pieces) as total_ordered_pieces, sum(net_sales) as total_net_sales, min(fiscal_date) as first_price_date\n",
        "           from `{DATASET}.required_columns_final` AS cdm_pricing\n",
        "           group by 1,2\n",
        "           order by 1, 2 asc\n",
        "       )\n",
        ");\n",
        "\n",
        "\"\"\".format(\n",
        "    DATASET=DATASET\n",
        ")\n",
        "query_job = client.query(query)\n",
        "print(query_job.result())"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "e35666281164"
      },
      "outputs": [],
      "source": [
        "query = \"\"\"\n",
        "select * from {DATASET}.price_changes order by product_id, first_price_date \n",
        "\"\"\".format(\n",
        "    DATASET=DATASET\n",
        ")\n",
        "query_job = client.query(query)\n",
        "print(query_job.result())"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "6008013af710"
      },
      "outputs": [],
      "source": [
        "df_price_changes = query_job.to_dataframe()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "bbd7cb770cab"
      },
      "outputs": [],
      "source": [
        "df_price_changes"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ebcb87feecaa"
      },
      "source": [
        "**Step 2**. Next, with the temporary table in place, you can calculate the price change across SKUs\n",
        "\n",
        "Ex: (`previous_list`-`list_price_converged`)/nullif(`previous_list`,0)*100\n",
        "\n",
        "**Step 3**. Next, you can calculate the `total_ordered_pieces` change across SKUs\n",
        "(`total_ordered_pieces`-`previous_total_ordered_pieces`)/nullif(`previous_total_ordered_pieces`,0)*100 "
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "a68a5258ec78"
      },
      "outputs": [],
      "source": [
        "query = \"\"\"\n",
        "select *,(list_price_converged-previous_list)/nullif(previous_list,0)*100 as price_change_perc,(total_ordered_pieces-previous_total_ordered_pieces)/nullif(previous_total_ordered_pieces,0)*100 as order_change_perc  from `{DATASET}.price_changes`\n",
        "\"\"\".format(\n",
        "    DATASET=DATASET\n",
        ")\n",
        "query_job = client.query(query)\n",
        "print(query_job.result())"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "e7377794e288"
      },
      "source": [
        "Now you have a dataframe(`df_for_plot`) which has `price_change_perc`, `order_change_perc`fields"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "a75bebfb83ea"
      },
      "outputs": [],
      "source": [
        "df_for_plot = query_job.to_dataframe()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "ee24c3ea8067"
      },
      "outputs": [],
      "source": [
        "# sort values chronologically\n",
        "df_for_plot.sort_values(by=[\"product_id\", \"first_price_date\"], inplace=True)\n",
        "df_for_plot.reset_index(drop=True, inplace=True)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "f20bc3d598b5"
      },
      "outputs": [],
      "source": [
        "df_for_plot"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "7ae3e75de755"
      },
      "source": [
        "Finally, analyze the relationship between each price change and the total number of items ordered."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "84123a5c3581"
      },
      "outputs": [],
      "source": [
        "# plot a scatterplot to visualize the changes\n",
        "sns.scatterplot(\n",
        "    x=\"price_change_perc\",\n",
        "    y=\"order_change_perc\",\n",
        "    data=df_for_plot,\n",
        "    hue=\"product_id\",\n",
        "    legend=False,\n",
        ")\n",
        "plt.title(\"Percentage of change in price vs order\")\n",
        "plt.show()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "8259e916fe25"
      },
      "source": [
        "For most of the products, the percentage change in orders are high where the percentage changes in the prices are low. This suggests that too much change in the prices can affect the number of orders. \n",
        "\n",
        "**Note**: The data contains outliers, with percentage changes exceeding 800. In the current exercise, you can refrain from manually handling outliers, as the BigQuery ML timeseries model is designed to manage outliers automatically.\n",
        "\n",
        "## Preprocess the data for training\n",
        "\n",
        "#### Check which `Product_ID`'s  have the maximum orders."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "fad58b6d45a4"
      },
      "source": [
        "Create a view that stores the number of orders for each product categorized by `Customer_Hierarchy`."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "aa49377d094b"
      },
      "outputs": [],
      "source": [
        "query = \"\"\"\n",
        "CREATE OR REPLACE VIEW {DATASET}.total_orders AS\n",
        "(\n",
        "SELECT Customer_Hierarchy,Product_ID,SUM(Invoiced_quantity_in_Pieces) AS Invoiced_quantity_in_Pieces FROM {DATASET}.required_columns_final GROUP BY Customer_Hierarchy,Product_ID\n",
        "\n",
        ")\n",
        "\"\"\".format(\n",
        "    DATASET=DATASET\n",
        ")\n",
        "query_job = client.query(query)\n",
        "print(query_job.result())"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "5da1f11a5657"
      },
      "outputs": [],
      "source": [
        "query = \"\"\"\n",
        "SELECT * FROM {DATASET}.total_orders\"\"\".format(\n",
        "    DATASET=DATASET\n",
        ")\n",
        "query_job = client.query(query)\n",
        "print(query_job.result())"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "744d00c87cf3"
      },
      "outputs": [],
      "source": [
        "# sort values chronologically\n",
        "df_total_orders = query_job.to_dataframe()\n",
        "df_total_orders.sort_values(by=[\"Product_ID\"], inplace=True)\n",
        "df_total_orders.reset_index(drop=True, inplace=True)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "4412df821ad0"
      },
      "outputs": [],
      "source": [
        "df_total_orders"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "4179bec4c5ec"
      },
      "source": [
        "#### Select top products in each Customer_Hierarchy\n",
        "\n",
        "Below is an example demonstrating how to identify the top products within each `Customer_Hierarchy`.\n",
        "\n",
        "Example:\n",
        "Assume that the `total_orders` view is\n",
        "\n",
        "<table>\n",
        "    <tr>\n",
        "        <th>\n",
        "            Customer_Hierarchy\n",
        "        </th>\n",
        "        <th> \n",
        "            Invoiced_quantity_in_Pieces\n",
        "        </th>\n",
        "        <th> \n",
        "            Product_ID\n",
        "        </th>\n",
        "    </tr>    \n",
        "    <tr> \n",
        "        <td>Food</td>                 \n",
        "        <td>200</td>                       \n",
        "        <td>1</td> \n",
        "    </tr>\n",
        "    <tr> \n",
        "        <td>Paper</td>                 \n",
        "        <td>100</td>                       \n",
        "        <td>2</td> \n",
        "    </tr>\n",
        "    <tr> \n",
        "        <td>Food</td>                 \n",
        "        <td>300</td>                       \n",
        "        <td>3</td> \n",
        "    </tr>\n",
        "    <tr> \n",
        "        <td>Paper</td>                 \n",
        "        <td>400</td>                       \n",
        "        <td>4</td> \n",
        "    </tr>\n",
        "   \n",
        "</table>    \n",
        "\n",
        "After partitioning the `total_orders` view by `Customer_Hierarchy` and ordering by `Invoiced_quantity_in_Pieces` in descending order, it becomes\n",
        "\n",
        "<table>\n",
        "    <tr>\n",
        "        <th>\n",
        "            Customer_Hierarchy\n",
        "        </th>\n",
        "        <th> \n",
        "            Invoiced_quantity_in_Pieces\n",
        "        </th>\n",
        "        <th> \n",
        "            Product_ID\n",
        "        </th>\n",
        "    </tr>    \n",
        "    <tr> \n",
        "        <td>Food</td>                 \n",
        "        <td>300</td>                       \n",
        "        <td>3</td> \n",
        "    </tr>\n",
        "    <tr> \n",
        "        <td>Food</td>                 \n",
        "        <td>200</td>                       \n",
        "        <td>1</td> \n",
        "    </tr>\n",
        "    <tr> \n",
        "        <td>Paper</td>                 \n",
        "        <td>100</td>                       \n",
        "        <td>2</td> \n",
        "    </tr>\n",
        "    <tr> \n",
        "        <td>Paper</td>                 \n",
        "        <td>400</td>                       \n",
        "        <td>4</td> \n",
        "    </tr>\n",
        "</table>   \n",
        "\n",
        "Now, for every `Customer_Hierarchy`, `Invoiced_quantity_in_Pieces` is arranged in descending order. Then, apply the `ROW_NUMBER` function to the table.\n",
        "\n",
        "<table>\n",
        "    <tr>\n",
        "        <th>\n",
        "            Customer_Hierarchy\n",
        "        </th>\n",
        "        <th> \n",
        "            Invoiced_quantity_in_Pieces\n",
        "        </th>\n",
        "        <th> \n",
        "            Product_ID\n",
        "        </th>\n",
        "        <th>\n",
        "            rowNumber\n",
        "        </th>    \n",
        "    </tr>    \n",
        "    <tr> \n",
        "        <td>Food</td>                 \n",
        "        <td>300</td>                       \n",
        "        <td>3</td>\n",
        "        <td>1</td>\n",
        "    </tr>\n",
        "    <tr> \n",
        "        <td>Food</td>                 \n",
        "        <td>200</td>                       \n",
        "        <td>1</td>\n",
        "        <td>2</td>\n",
        "    </tr>\n",
        "    <tr> \n",
        "        <td>Paper</td>                 \n",
        "        <td>100</td>                       \n",
        "        <td>2</td> \n",
        "        <td>1 </td>\n",
        "    </tr>\n",
        "    <tr> \n",
        "        <td>Paper</td>                 \n",
        "        <td>400</td>                       \n",
        "        <td>4</td> \n",
        "        <td>2</td>\n",
        "    </tr>\n",
        "</table>   \n",
        "\n",
        "(Each unique `Customer_Hierarchy` is assigned a number starting from 1.)\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "d3876efae332"
      },
      "outputs": [],
      "source": [
        "query = \"\"\"\n",
        "SELECT \n",
        "  *,\n",
        "  ROW_NUMBER() OVER(PARTITION BY Customer_Hierarchy ORDER BY Invoiced_quantity_in_Pieces DESC) rowNumber\n",
        "  FROM {DATASET}.total_orders\n",
        "\"\"\".format(\n",
        "    DATASET=DATASET\n",
        ")\n",
        "query_job = client.query(query)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "cfb208583da9"
      },
      "outputs": [],
      "source": [
        "query_job.to_dataframe()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "a0092c1d6277"
      },
      "source": [
        "As observed, when arranging by `Customer_Hierarchy`, `Invoiced_quantity_in_Pieces` is in descending order, with rowNumber starting from 1"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "b9cc04ee390c"
      },
      "outputs": [],
      "source": [
        "query_job.to_dataframe().loc[query_job.to_dataframe()[\"Customer_Hierarchy\"] == \"Paper\"]"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "d3cbe5e74137"
      },
      "source": [
        "Select the row where `Invoiced_quantity_in_Pieces` is the highest within each Customer_Hierarchy, so choose the row with rowNumber 1."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "d69d19436b89"
      },
      "outputs": [],
      "source": [
        "query = \"\"\"\n",
        " SELECT A.Product_ID, A.Customer_Hierarchy,A.Invoiced_quantity_in_Pieces\n",
        "  FROM (\n",
        "  SELECT \n",
        "  *,\n",
        "  ROW_NUMBER() OVER(PARTITION BY Customer_Hierarchy ORDER BY Invoiced_quantity_in_Pieces DESC) rowNumber\n",
        "  FROM {DATASET}.total_orders\n",
        "  )A\n",
        "  WHERE A.rowNumber =1;\n",
        "\"\"\".format(\n",
        "    DATASET=DATASET\n",
        ")\n",
        "query_job = client.query(query)\n",
        "print(query_job.result())"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "9a7928e2f2e2"
      },
      "outputs": [],
      "source": [
        "query_job.to_dataframe()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "fd6d227e513e"
      },
      "source": [
        "From the above result, you can infer the following:\n",
        "\n",
        "- Under the **Food** category, **SKU 62** has the maximum orders.\n",
        "- Under the **Manufacturing** category, **SKU 17** has the maximum orders.\n",
        "- Under the **Paper** category, **SKU 107** has the maximum orders.\n",
        "- Under the **Publishing** category, **SKU 8** has the maximum orders.\n",
        "- Under the **Utilities** category, **SKU 140** has the maximum orders.\n",
        "\n",
        "Given that there are too many ids and only a few records for most of them, consider only the above `Product_ID`s for which there are a maximum number of orders. \n",
        "\n",
        "**Note**: The `Invoiced_quantity_in_Pieces` field seems to be a *float* type rather than an *int* type as it should be. This could be because the data itself might be averaged in the first place."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "2dbc0d64d157"
      },
      "source": [
        "#### Check the various prices available for these `Product_ID`s."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "b82f4e3dca26"
      },
      "source": [
        "First, select rows from the `required_columns_final` view that have your desired product ID and customer hierarchy."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "bdc99245b3f8"
      },
      "outputs": [],
      "source": [
        "query = \"\"\"\n",
        "SELECT * FROM {DATASET}.required_columns_final WHERE Product_ID=\"SKU 62\" AND Customer_Hierarchy=\"Food\"\n",
        "\"\"\".format(\n",
        "    DATASET=DATASET\n",
        ")\n",
        "query_job = client.query(query)\n",
        "df_sku_62 = query_job.to_dataframe()\n",
        "df_sku_62"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "fb87696b852e"
      },
      "source": [
        "Then plot various prices available for these `Product_ID`s."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "479bf3a1b430"
      },
      "outputs": [],
      "source": [
        "print(df_sku_62[\"List_Price_Converged\"].value_counts())"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "59668ec8106d"
      },
      "outputs": [],
      "source": [
        "query = \"\"\"\n",
        "SELECT * FROM {DATASET}.required_columns_final WHERE Product_ID=\"SKU 17\" AND Customer_Hierarchy=\"Manufacturing\"\n",
        "\"\"\".format(\n",
        "    DATASET=DATASET\n",
        ")\n",
        "query_job = client.query(query)\n",
        "df_sku_17 = query_job.to_dataframe()\n",
        "df_sku_17"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "244fd4f47a83"
      },
      "outputs": [],
      "source": [
        "print(df_sku_17[\"List_Price_Converged\"].value_counts())"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "52423923410e"
      },
      "outputs": [],
      "source": [
        "query = \"\"\"\n",
        "SELECT * FROM {DATASET}.required_columns_final WHERE Product_ID=\"SKU 107\" AND Customer_Hierarchy=\"Paper\"\n",
        "\"\"\".format(\n",
        "    DATASET=DATASET\n",
        ")\n",
        "query_job = client.query(query)\n",
        "df_sku_107 = query_job.to_dataframe()\n",
        "df_sku_107"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "d317e25f3c25"
      },
      "outputs": [],
      "source": [
        "print(df_sku_107[\"List_Price_Converged\"].value_counts())"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "b7a21f2e7d75"
      },
      "outputs": [],
      "source": [
        "query = \"\"\"\n",
        "SELECT * FROM {DATASET}.required_columns_final WHERE Product_ID=\"SKU 8\" AND Customer_Hierarchy=\"Publishing\"\n",
        "\"\"\".format(\n",
        "    DATASET=DATASET\n",
        ")\n",
        "query_job = client.query(query)\n",
        "df_sku_8 = query_job.to_dataframe()\n",
        "df_sku_8"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "4757a1682c48"
      },
      "outputs": [],
      "source": [
        "print(df_sku_8[\"List_Price_Converged\"].value_counts())"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "14cf1dde6946"
      },
      "outputs": [],
      "source": [
        "query = \"\"\"\n",
        "SELECT * FROM {DATASET}.required_columns_final WHERE Product_ID=\"SKU 140\" AND Customer_Hierarchy=\"Utilities\"\n",
        "\"\"\".format(\n",
        "    DATASET=DATASET\n",
        ")\n",
        "query_job = client.query(query)\n",
        "df_sku_140 = query_job.to_dataframe()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "8e215f3fe8a3"
      },
      "outputs": [],
      "source": [
        "print(df_sku_140[\"List_Price_Converged\"].value_counts())"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "f023af578c0f"
      },
      "source": [
        "In the publishing category, exclude `Product_ID`, `SKU 8`, and `SKU 17`, as they have fewer than or equal to two different prices in the entire dataset. Instead, consider the remaining products for building the forecast model on the timeseries data with varying prices.\n",
        "\n",
        "#### Join the data for all the `Product_ID`s into one dataframe and remove duplicate records."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "4e2c2cf928f3"
      },
      "outputs": [],
      "source": [
        "df_final = pd.concat([df_sku_62, df_sku_107, df_sku_140])\n",
        "df_final = (\n",
        "    df_final[\n",
        "        [\n",
        "            \"Product_ID\",\n",
        "            \"Fiscal_Date\",\n",
        "            \"Customer_Hierarchy\",\n",
        "            \"List_Price_Converged\",\n",
        "            \"Invoiced_quantity_in_Pieces\",\n",
        "        ]\n",
        "    ]\n",
        "    .drop_duplicates()\n",
        "    .reset_index(drop=True)\n",
        ")\n",
        "df_final"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "add5063df368"
      },
      "source": [
        "#### Save the data to a BigQuery table."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "fd82ba56571f"
      },
      "outputs": [],
      "source": [
        "bq_client = bigquery.Client(project=PROJECT_ID)\n",
        "\n",
        "job_config = bigquery.LoadJobConfig(\n",
        "    # Specify a (partial) schema. All columns are always written to the\n",
        "    # table. The schema is used to assist in data type definitions.\n",
        "    schema=[\n",
        "        bigquery.SchemaField(\"Product_ID\", bigquery.enums.SqlTypeNames.STRING),\n",
        "        bigquery.SchemaField(\"Fiscal_Date\", bigquery.enums.SqlTypeNames.DATE),\n",
        "        bigquery.SchemaField(\"List_Price_Converged\", bigquery.enums.SqlTypeNames.FLOAT),\n",
        "        bigquery.SchemaField(\n",
        "            \"Invoiced_quantity_in_Pieces\", bigquery.enums.SqlTypeNames.FLOAT\n",
        "        ),\n",
        "    ],\n",
        "    # Optionally, set the write disposition. BigQuery appends loaded rows\n",
        "    # to an existing table by default, but with WRITE_TRUNCATE write\n",
        "    # disposition it replaces the table with the loaded data.\n",
        "    write_disposition=\"WRITE_TRUNCATE\",\n",
        ")\n",
        "\n",
        "# save the dataframe to a table in the created dataset\n",
        "job = bq_client.load_table_from_dataframe(\n",
        "    df_final,\n",
        "    \"{}.{}.{}\".format(PROJECT_ID, DATASET, TRAINING_DATA_TABLE),\n",
        "    job_config=job_config,\n",
        ")  # Make an API request.\n",
        "print(job.result())  # Wait for the job to complete."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "fca77641b03b"
      },
      "source": [
        "# Train the model using BigQuery ML\n",
        "<a name=\"section-9\"></a>\n",
        "\n",
        "Train an [Arima-Plus](https://cloud.google.com/bigquery-ml/docs/reference/standard-sql/bigqueryml-syntax-create-time-series) model on the data using BigQuery ML."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "cded27507891"
      },
      "source": [
        "#@bigquery\n",
        "create or replace model [your-dataset-id].bqml_arima\n",
        "options\n",
        " (model_type = 'ARIMA_PLUS',\n",
        "  time_series_timestamp_col = 'Fiscal_Date',\n",
        "  time_series_data_col = 'Invoiced_quantity_in_Pieces',\n",
        "  time_series_id_col = 'ID'\n",
        " ) as\n",
        "select\n",
        " Fiscal_Date,\n",
        " Concat(Product_ID,\"_\" ,Cast(List_Price_Converged as string)) as ID,\n",
        " Invoiced_quantity_in_Pieces\n",
        "from\n",
        " [your-dataset-id].TRAINING_DATA\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "e25254d219b7"
      },
      "outputs": [],
      "source": [
        "query = \"\"\"\n",
        "create or replace model `{PROJECT_ID}.{DATASET}.bqml_arima`\n",
        "options\n",
        " (model_type = 'ARIMA_PLUS',\n",
        "  time_series_timestamp_col = 'Fiscal_Date',\n",
        "  time_series_data_col = 'Invoiced_quantity_in_Pieces',\n",
        "  time_series_id_col = 'ID'\n",
        " ) as\n",
        "select\n",
        " Fiscal_Date,\n",
        " Concat(Product_ID,\"_\" ,Cast(List_Price_Converged as string)) as ID,\n",
        " Invoiced_quantity_in_Pieces\n",
        "from\n",
        " `{DATASET}.{TRAINING_DATA_TABLE}`\"\"\".format(\n",
        "    PROJECT_ID=PROJECT_ID, DATASET=DATASET, TRAINING_DATA_TABLE=TRAINING_DATA_TABLE\n",
        ")\n",
        "query_job = client.query(query)\n",
        "print(query_job.result())"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "332fd11ff32b"
      },
      "source": [
        "## Generate forecasts from the model\n",
        "<a name=\"section-10\"></a>\n",
        "\n",
        "Predict the sales for the next 30 days for each id and save to a dataframe."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "ef926cdbf28e"
      },
      "outputs": [],
      "source": [
        "query = '''\n",
        "DECLARE HORIZON STRING DEFAULT \"30\"; #number of values to forecast\n",
        "DECLARE CONFIDENCE_LEVEL STRING DEFAULT \"0.90\"; ## required confidence level\n",
        "\n",
        "EXECUTE IMMEDIATE format(\"\"\"\n",
        "    SELECT\n",
        "      *\n",
        "    FROM \n",
        "      ML.FORECAST(MODEL {DATASET}.bqml_arima, \n",
        "                  STRUCT(%s AS horizon, \n",
        "                         %s AS confidence_level)\n",
        "                 )\n",
        "    \"\"\",HORIZON,CONFIDENCE_LEVEL)'''.format(\n",
        "    DATASET=DATASET\n",
        ")\n",
        "job = client.query(query)\n",
        "dfforecast = job.to_dataframe()\n",
        "dfforecast.head()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "608c7de72dae"
      },
      "source": [
        "## Interpret the results to choose the best price\n",
        "<a name=\"section-11\"></a>\n",
        "\n",
        "#### Calculate average forecast values for the forecast duration."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "e1e193680400"
      },
      "outputs": [],
      "source": [
        "dfforecast_avg = (\n",
        "    dfforecast[[\"ID\", \"forecast_value\"]].groupby(\"ID\", as_index=False).mean()\n",
        ")"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "5ce395d652a3"
      },
      "source": [
        "#### Extract the ID and Price fields from the ID field."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "452c56fa58ed"
      },
      "outputs": [],
      "source": [
        "dfforecast_avg[\"Product_ID\"] = dfforecast_avg[\"ID\"].apply(lambda x: x.split(\"_\")[0])\n",
        "dfforecast_avg[\"Price\"] = dfforecast_avg[\"ID\"].apply(lambda x: x.split(\"_\")[1])"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "3cee67f4028f"
      },
      "source": [
        "#### Plot the average forecasted sales vs. the price of the product."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "fb351c8f383d"
      },
      "outputs": [],
      "source": [
        "for i in dfforecast_avg[\"Product_ID\"].unique():\n",
        "    dfforecast_avg[dfforecast_avg[\"Product_ID\"] == i].set_index(\"Price\").sort_values(\n",
        "        \"forecast_value\"\n",
        "    ).plot(kind=\"bar\")\n",
        "    plt.title(\"Price vs. Average Sales for \" + i)\n",
        "    plt.show()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "67ff3acc74a5"
      },
      "source": [
        "Based on the plots for price vs. the average forecasted orders, the following prices can be recommended to maximize orders for each of the considered `Product_IDs`:\n",
        "\n",
        "- SKU 107's price range can be from 4.44 - 4.73 units\n",
        "- SKU 140's price can be 1.95 units\n",
        "- SKU 62's price can be 4.23 units\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "01fdc73828af"
      },
      "source": [
        "## Clean Up\n",
        "<a name=\"section-12\"></a>\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. The following code deletes the entire dataset."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "d78908b8134d"
      },
      "outputs": [],
      "source": [
        "# Set dataset_id to the ID of the dataset to fetch.\n",
        "dataset_id = \"{PROJECT_ID}.{DATASET}\".format(PROJECT_ID=PROJECT_ID, DATASET=DATASET)\n",
        "\n",
        "# Use the delete_contents parameter to delete a dataset and its contents.\n",
        "# Use the not_found_ok parameter to not receive an error if the dataset has already been deleted.\n",
        "client.delete_dataset(\n",
        "    dataset_id, delete_contents=True, not_found_ok=True\n",
        ")  # Make an API request.\n",
        "\n",
        "print(\"Deleted dataset '{}'.\".format(dataset_id))"
      ]
    }
  ],
  "metadata": {
    "colab": {
      "name": "pricing-optimization.ipynb",
      "toc_visible": true
    },
    "kernelspec": {
      "display_name": "Python 3",
      "name": "python3"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 0
}