CDP_Workshop_Customer_segmentation_ltv.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "CDP Workshop - Customer segmentation - ltv",
      "provenance": [],
      "collapsed_sections": [],
      "toc_visible": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "code",
      "metadata": {
        "id": "ba7hiG89ayBc"
      },
      "source": [
        "# Copyright 2020 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."
      ],
      "execution_count": 1,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "# [Run in Colab](https://colab.research.google.com/github/dpanigra/cdp-workshop/blob/master/CDP_Workshop_Customer_segmentation_ltv.ipynb)"
      ],
      "metadata": {
        "id": "jnCaHWHsoIVG"
      }
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "PWx8GsnFbXZ1"
      },
      "source": [
        " # Overview\n",
        "CDP workshop - Customer segmentation using ltv.\n",
        "\n",
        "Step by step solution guide with explanation is [here.](https://cloud.google.com/architecture/building-audiences-clv)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "gfby1-CHbyrT"
      },
      "source": [
        "# Setup environment"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "xbV6Y_ELb6KQ"
      },
      "source": [
        "## *PIP install appropriate packages*"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "l3S2sOCidhpj",
        "outputId": "becbb08a-4488-42b1-8638-c4f5e6eaf31e"
      },
      "source": [
        "%pip install google-cloud-storage # for Storage Account\n",
        "%pip install google-cloud # for cloud sdk\n",
        "%pip install google-cloud-bigquery # for BigQuery\n",
        "%pip install google-cloud-bigquery-storage # for BigQuery Storage client\n",
        "# for data exploration\n",
        "%pip install pandas \n",
        "%pip install matplotlib \n",
        "%pip install pandas_profiling \n",
        "\n",
        "# Restart kernel after installs\n",
        "# import IPython\n",
        "# app = IPython.Application.instance()\n",
        "# app.kernel.do_shutdown(True)"
      ],
      "execution_count": 2,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Requirement already satisfied: google-cloud-storage in /usr/local/lib/python3.7/dist-packages (1.18.1)\n",
            "Requirement already satisfied: google-cloud-core<2.0dev,>=1.0.0 in /usr/local/lib/python3.7/dist-packages (from google-cloud-storage) (1.0.3)\n",
            "Requirement already satisfied: google-resumable-media<0.5.0dev,>=0.3.1 in /usr/local/lib/python3.7/dist-packages (from google-cloud-storage) (0.4.1)\n",
            "Requirement already satisfied: google-auth>=1.2.0 in /usr/local/lib/python3.7/dist-packages (from google-cloud-storage) (1.35.0)\n",
            "Requirement already satisfied: setuptools>=40.3.0 in /usr/local/lib/python3.7/dist-packages (from google-auth>=1.2.0->google-cloud-storage) (57.4.0)\n",
            "Requirement already satisfied: rsa<5,>=3.1.4 in /usr/local/lib/python3.7/dist-packages (from google-auth>=1.2.0->google-cloud-storage) (4.8)\n",
            "Requirement already satisfied: pyasn1-modules>=0.2.1 in /usr/local/lib/python3.7/dist-packages (from google-auth>=1.2.0->google-cloud-storage) (0.2.8)\n",
            "Requirement already satisfied: six>=1.9.0 in /usr/local/lib/python3.7/dist-packages (from google-auth>=1.2.0->google-cloud-storage) (1.15.0)\n",
            "Requirement already satisfied: cachetools<5.0,>=2.0.0 in /usr/local/lib/python3.7/dist-packages (from google-auth>=1.2.0->google-cloud-storage) (4.2.4)\n",
            "Requirement already satisfied: google-api-core<2.0.0dev,>=1.14.0 in /usr/local/lib/python3.7/dist-packages (from google-cloud-core<2.0dev,>=1.0.0->google-cloud-storage) (1.26.3)\n",
            "Requirement already satisfied: pytz in /usr/local/lib/python3.7/dist-packages (from google-api-core<2.0.0dev,>=1.14.0->google-cloud-core<2.0dev,>=1.0.0->google-cloud-storage) (2018.9)\n",
            "Requirement already satisfied: requests<3.0.0dev,>=2.18.0 in /usr/local/lib/python3.7/dist-packages (from google-api-core<2.0.0dev,>=1.14.0->google-cloud-core<2.0dev,>=1.0.0->google-cloud-storage) (2.23.0)\n",
            "Requirement already satisfied: googleapis-common-protos<2.0dev,>=1.6.0 in /usr/local/lib/python3.7/dist-packages (from google-api-core<2.0.0dev,>=1.14.0->google-cloud-core<2.0dev,>=1.0.0->google-cloud-storage) (1.53.0)\n",
            "Requirement already satisfied: packaging>=14.3 in /usr/local/lib/python3.7/dist-packages (from google-api-core<2.0.0dev,>=1.14.0->google-cloud-core<2.0dev,>=1.0.0->google-cloud-storage) (21.3)\n",
            "Requirement already satisfied: protobuf>=3.12.0 in /usr/local/lib/python3.7/dist-packages (from google-api-core<2.0.0dev,>=1.14.0->google-cloud-core<2.0dev,>=1.0.0->google-cloud-storage) (3.17.3)\n",
            "Requirement already satisfied: pyparsing!=3.0.5,>=2.0.2 in /usr/local/lib/python3.7/dist-packages (from packaging>=14.3->google-api-core<2.0.0dev,>=1.14.0->google-cloud-core<2.0dev,>=1.0.0->google-cloud-storage) (3.0.6)\n",
            "Requirement already satisfied: pyasn1<0.5.0,>=0.4.6 in /usr/local/lib/python3.7/dist-packages (from pyasn1-modules>=0.2.1->google-auth>=1.2.0->google-cloud-storage) (0.4.8)\n",
            "Requirement already satisfied: chardet<4,>=3.0.2 in /usr/local/lib/python3.7/dist-packages (from requests<3.0.0dev,>=2.18.0->google-api-core<2.0.0dev,>=1.14.0->google-cloud-core<2.0dev,>=1.0.0->google-cloud-storage) (3.0.4)\n",
            "Requirement already satisfied: idna<3,>=2.5 in /usr/local/lib/python3.7/dist-packages (from requests<3.0.0dev,>=2.18.0->google-api-core<2.0.0dev,>=1.14.0->google-cloud-core<2.0dev,>=1.0.0->google-cloud-storage) (2.10)\n",
            "Requirement already satisfied: certifi>=2017.4.17 in /usr/local/lib/python3.7/dist-packages (from requests<3.0.0dev,>=2.18.0->google-api-core<2.0.0dev,>=1.14.0->google-cloud-core<2.0dev,>=1.0.0->google-cloud-storage) (2021.10.8)\n",
            "Requirement already satisfied: urllib3!=1.25.0,!=1.25.1,<1.26,>=1.21.1 in /usr/local/lib/python3.7/dist-packages (from requests<3.0.0dev,>=2.18.0->google-api-core<2.0.0dev,>=1.14.0->google-cloud-core<2.0dev,>=1.0.0->google-cloud-storage) (1.24.3)\n",
            "Collecting google-cloud\n",
            "  Downloading google_cloud-0.34.0-py2.py3-none-any.whl (1.8 kB)\n",
            "Installing collected packages: google-cloud\n",
            "Successfully installed google-cloud-0.34.0\n",
            "Requirement already satisfied: google-cloud-bigquery in /usr/local/lib/python3.7/dist-packages (1.21.0)\n",
            "Requirement already satisfied: google-cloud-core<2.0dev,>=1.0.3 in /usr/local/lib/python3.7/dist-packages (from google-cloud-bigquery) (1.0.3)\n",
            "Requirement already satisfied: google-resumable-media!=0.4.0,<0.5.0dev,>=0.3.1 in /usr/local/lib/python3.7/dist-packages (from google-cloud-bigquery) (0.4.1)\n",
            "Requirement already satisfied: protobuf>=3.6.0 in /usr/local/lib/python3.7/dist-packages (from google-cloud-bigquery) (3.17.3)\n",
            "Requirement already satisfied: google-api-core<2.0.0dev,>=1.14.0 in /usr/local/lib/python3.7/dist-packages (from google-cloud-core<2.0dev,>=1.0.3->google-cloud-bigquery) (1.26.3)\n",
            "Requirement already satisfied: requests<3.0.0dev,>=2.18.0 in /usr/local/lib/python3.7/dist-packages (from google-api-core<2.0.0dev,>=1.14.0->google-cloud-core<2.0dev,>=1.0.3->google-cloud-bigquery) (2.23.0)\n",
            "Requirement already satisfied: setuptools>=40.3.0 in /usr/local/lib/python3.7/dist-packages (from google-api-core<2.0.0dev,>=1.14.0->google-cloud-core<2.0dev,>=1.0.3->google-cloud-bigquery) (57.4.0)\n",
            "Requirement already satisfied: packaging>=14.3 in /usr/local/lib/python3.7/dist-packages (from google-api-core<2.0.0dev,>=1.14.0->google-cloud-core<2.0dev,>=1.0.3->google-cloud-bigquery) (21.3)\n",
            "Requirement already satisfied: six>=1.13.0 in /usr/local/lib/python3.7/dist-packages (from google-api-core<2.0.0dev,>=1.14.0->google-cloud-core<2.0dev,>=1.0.3->google-cloud-bigquery) (1.15.0)\n",
            "Requirement already satisfied: pytz in /usr/local/lib/python3.7/dist-packages (from google-api-core<2.0.0dev,>=1.14.0->google-cloud-core<2.0dev,>=1.0.3->google-cloud-bigquery) (2018.9)\n",
            "Requirement already satisfied: google-auth<2.0dev,>=1.21.1 in /usr/local/lib/python3.7/dist-packages (from google-api-core<2.0.0dev,>=1.14.0->google-cloud-core<2.0dev,>=1.0.3->google-cloud-bigquery) (1.35.0)\n",
            "Requirement already satisfied: googleapis-common-protos<2.0dev,>=1.6.0 in /usr/local/lib/python3.7/dist-packages (from google-api-core<2.0.0dev,>=1.14.0->google-cloud-core<2.0dev,>=1.0.3->google-cloud-bigquery) (1.53.0)\n",
            "Requirement already satisfied: rsa<5,>=3.1.4 in /usr/local/lib/python3.7/dist-packages (from google-auth<2.0dev,>=1.21.1->google-api-core<2.0.0dev,>=1.14.0->google-cloud-core<2.0dev,>=1.0.3->google-cloud-bigquery) (4.8)\n",
            "Requirement already satisfied: pyasn1-modules>=0.2.1 in /usr/local/lib/python3.7/dist-packages (from google-auth<2.0dev,>=1.21.1->google-api-core<2.0.0dev,>=1.14.0->google-cloud-core<2.0dev,>=1.0.3->google-cloud-bigquery) (0.2.8)\n",
            "Requirement already satisfied: cachetools<5.0,>=2.0.0 in /usr/local/lib/python3.7/dist-packages (from google-auth<2.0dev,>=1.21.1->google-api-core<2.0.0dev,>=1.14.0->google-cloud-core<2.0dev,>=1.0.3->google-cloud-bigquery) (4.2.4)\n",
            "Requirement already satisfied: pyparsing!=3.0.5,>=2.0.2 in /usr/local/lib/python3.7/dist-packages (from packaging>=14.3->google-api-core<2.0.0dev,>=1.14.0->google-cloud-core<2.0dev,>=1.0.3->google-cloud-bigquery) (3.0.6)\n",
            "Requirement already satisfied: pyasn1<0.5.0,>=0.4.6 in /usr/local/lib/python3.7/dist-packages (from pyasn1-modules>=0.2.1->google-auth<2.0dev,>=1.21.1->google-api-core<2.0.0dev,>=1.14.0->google-cloud-core<2.0dev,>=1.0.3->google-cloud-bigquery) (0.4.8)\n",
            "Requirement already satisfied: certifi>=2017.4.17 in /usr/local/lib/python3.7/dist-packages (from requests<3.0.0dev,>=2.18.0->google-api-core<2.0.0dev,>=1.14.0->google-cloud-core<2.0dev,>=1.0.3->google-cloud-bigquery) (2021.10.8)\n",
            "Requirement already satisfied: urllib3!=1.25.0,!=1.25.1,<1.26,>=1.21.1 in /usr/local/lib/python3.7/dist-packages (from requests<3.0.0dev,>=2.18.0->google-api-core<2.0.0dev,>=1.14.0->google-cloud-core<2.0dev,>=1.0.3->google-cloud-bigquery) (1.24.3)\n",
            "Requirement already satisfied: chardet<4,>=3.0.2 in /usr/local/lib/python3.7/dist-packages (from requests<3.0.0dev,>=2.18.0->google-api-core<2.0.0dev,>=1.14.0->google-cloud-core<2.0dev,>=1.0.3->google-cloud-bigquery) (3.0.4)\n",
            "Requirement already satisfied: idna<3,>=2.5 in /usr/local/lib/python3.7/dist-packages (from requests<3.0.0dev,>=2.18.0->google-api-core<2.0.0dev,>=1.14.0->google-cloud-core<2.0dev,>=1.0.3->google-cloud-bigquery) (2.10)\n",
            "Requirement already satisfied: google-cloud-bigquery-storage in /usr/local/lib/python3.7/dist-packages (1.1.0)\n",
            "Requirement already satisfied: google-api-core[grpc]<2.0.0dev,>=1.14.0 in /usr/local/lib/python3.7/dist-packages (from google-cloud-bigquery-storage) (1.26.3)\n",
            "Requirement already satisfied: packaging>=14.3 in /usr/local/lib/python3.7/dist-packages (from google-api-core[grpc]<2.0.0dev,>=1.14.0->google-cloud-bigquery-storage) (21.3)\n",
            "Requirement already satisfied: pytz in /usr/local/lib/python3.7/dist-packages (from google-api-core[grpc]<2.0.0dev,>=1.14.0->google-cloud-bigquery-storage) (2018.9)\n",
            "Requirement already satisfied: googleapis-common-protos<2.0dev,>=1.6.0 in /usr/local/lib/python3.7/dist-packages (from google-api-core[grpc]<2.0.0dev,>=1.14.0->google-cloud-bigquery-storage) (1.53.0)\n",
            "Requirement already satisfied: setuptools>=40.3.0 in /usr/local/lib/python3.7/dist-packages (from google-api-core[grpc]<2.0.0dev,>=1.14.0->google-cloud-bigquery-storage) (57.4.0)\n",
            "Requirement already satisfied: six>=1.13.0 in /usr/local/lib/python3.7/dist-packages (from google-api-core[grpc]<2.0.0dev,>=1.14.0->google-cloud-bigquery-storage) (1.15.0)\n",
            "Requirement already satisfied: requests<3.0.0dev,>=2.18.0 in /usr/local/lib/python3.7/dist-packages (from google-api-core[grpc]<2.0.0dev,>=1.14.0->google-cloud-bigquery-storage) (2.23.0)\n",
            "Requirement already satisfied: protobuf>=3.12.0 in /usr/local/lib/python3.7/dist-packages (from google-api-core[grpc]<2.0.0dev,>=1.14.0->google-cloud-bigquery-storage) (3.17.3)\n",
            "Requirement already satisfied: google-auth<2.0dev,>=1.21.1 in /usr/local/lib/python3.7/dist-packages (from google-api-core[grpc]<2.0.0dev,>=1.14.0->google-cloud-bigquery-storage) (1.35.0)\n",
            "Requirement already satisfied: grpcio<2.0dev,>=1.29.0 in /usr/local/lib/python3.7/dist-packages (from google-api-core[grpc]<2.0.0dev,>=1.14.0->google-cloud-bigquery-storage) (1.42.0)\n",
            "Requirement already satisfied: cachetools<5.0,>=2.0.0 in /usr/local/lib/python3.7/dist-packages (from google-auth<2.0dev,>=1.21.1->google-api-core[grpc]<2.0.0dev,>=1.14.0->google-cloud-bigquery-storage) (4.2.4)\n",
            "Requirement already satisfied: pyasn1-modules>=0.2.1 in /usr/local/lib/python3.7/dist-packages (from google-auth<2.0dev,>=1.21.1->google-api-core[grpc]<2.0.0dev,>=1.14.0->google-cloud-bigquery-storage) (0.2.8)\n",
            "Requirement already satisfied: rsa<5,>=3.1.4 in /usr/local/lib/python3.7/dist-packages (from google-auth<2.0dev,>=1.21.1->google-api-core[grpc]<2.0.0dev,>=1.14.0->google-cloud-bigquery-storage) (4.8)\n",
            "Requirement already satisfied: pyparsing!=3.0.5,>=2.0.2 in /usr/local/lib/python3.7/dist-packages (from packaging>=14.3->google-api-core[grpc]<2.0.0dev,>=1.14.0->google-cloud-bigquery-storage) (3.0.6)\n",
            "Requirement already satisfied: pyasn1<0.5.0,>=0.4.6 in /usr/local/lib/python3.7/dist-packages (from pyasn1-modules>=0.2.1->google-auth<2.0dev,>=1.21.1->google-api-core[grpc]<2.0.0dev,>=1.14.0->google-cloud-bigquery-storage) (0.4.8)\n",
            "Requirement already satisfied: chardet<4,>=3.0.2 in /usr/local/lib/python3.7/dist-packages (from requests<3.0.0dev,>=2.18.0->google-api-core[grpc]<2.0.0dev,>=1.14.0->google-cloud-bigquery-storage) (3.0.4)\n",
            "Requirement already satisfied: certifi>=2017.4.17 in /usr/local/lib/python3.7/dist-packages (from requests<3.0.0dev,>=2.18.0->google-api-core[grpc]<2.0.0dev,>=1.14.0->google-cloud-bigquery-storage) (2021.10.8)\n",
            "Requirement already satisfied: urllib3!=1.25.0,!=1.25.1,<1.26,>=1.21.1 in /usr/local/lib/python3.7/dist-packages (from requests<3.0.0dev,>=2.18.0->google-api-core[grpc]<2.0.0dev,>=1.14.0->google-cloud-bigquery-storage) (1.24.3)\n",
            "Requirement already satisfied: idna<3,>=2.5 in /usr/local/lib/python3.7/dist-packages (from requests<3.0.0dev,>=2.18.0->google-api-core[grpc]<2.0.0dev,>=1.14.0->google-cloud-bigquery-storage) (2.10)\n",
            "Requirement already satisfied: pandas in /usr/local/lib/python3.7/dist-packages (1.1.5)\n",
            "Requirement already satisfied: pytz>=2017.2 in /usr/local/lib/python3.7/dist-packages (from pandas) (2018.9)\n",
            "Requirement already satisfied: python-dateutil>=2.7.3 in /usr/local/lib/python3.7/dist-packages (from pandas) (2.8.2)\n",
            "Requirement already satisfied: numpy>=1.15.4 in /usr/local/lib/python3.7/dist-packages (from pandas) (1.19.5)\n",
            "Requirement already satisfied: six>=1.5 in /usr/local/lib/python3.7/dist-packages (from python-dateutil>=2.7.3->pandas) (1.15.0)\n",
            "Requirement already satisfied: matplotlib in /usr/local/lib/python3.7/dist-packages (3.2.2)\n",
            "Requirement already satisfied: python-dateutil>=2.1 in /usr/local/lib/python3.7/dist-packages (from matplotlib) (2.8.2)\n",
            "Requirement already satisfied: kiwisolver>=1.0.1 in /usr/local/lib/python3.7/dist-packages (from matplotlib) (1.3.2)\n",
            "Requirement already satisfied: pyparsing!=2.0.4,!=2.1.2,!=2.1.6,>=2.0.1 in /usr/local/lib/python3.7/dist-packages (from matplotlib) (3.0.6)\n",
            "Requirement already satisfied: cycler>=0.10 in /usr/local/lib/python3.7/dist-packages (from matplotlib) (0.11.0)\n",
            "Requirement already satisfied: numpy>=1.11 in /usr/local/lib/python3.7/dist-packages (from matplotlib) (1.19.5)\n",
            "Requirement already satisfied: six>=1.5 in /usr/local/lib/python3.7/dist-packages (from python-dateutil>=2.1->matplotlib) (1.15.0)\n",
            "Requirement already satisfied: pandas_profiling in /usr/local/lib/python3.7/dist-packages (1.4.1)\n",
            "Requirement already satisfied: matplotlib>=1.4 in /usr/local/lib/python3.7/dist-packages (from pandas_profiling) (3.2.2)\n",
            "Requirement already satisfied: jinja2>=2.8 in /usr/local/lib/python3.7/dist-packages (from pandas_profiling) (2.11.3)\n",
            "Requirement already satisfied: six>=1.9 in /usr/local/lib/python3.7/dist-packages (from pandas_profiling) (1.15.0)\n",
            "Requirement already satisfied: pandas>=0.19 in /usr/local/lib/python3.7/dist-packages (from pandas_profiling) (1.1.5)\n",
            "Requirement already satisfied: MarkupSafe>=0.23 in /usr/local/lib/python3.7/dist-packages (from jinja2>=2.8->pandas_profiling) (2.0.1)\n",
            "Requirement already satisfied: pyparsing!=2.0.4,!=2.1.2,!=2.1.6,>=2.0.1 in /usr/local/lib/python3.7/dist-packages (from matplotlib>=1.4->pandas_profiling) (3.0.6)\n",
            "Requirement already satisfied: cycler>=0.10 in /usr/local/lib/python3.7/dist-packages (from matplotlib>=1.4->pandas_profiling) (0.11.0)\n",
            "Requirement already satisfied: python-dateutil>=2.1 in /usr/local/lib/python3.7/dist-packages (from matplotlib>=1.4->pandas_profiling) (2.8.2)\n",
            "Requirement already satisfied: kiwisolver>=1.0.1 in /usr/local/lib/python3.7/dist-packages (from matplotlib>=1.4->pandas_profiling) (1.3.2)\n",
            "Requirement already satisfied: numpy>=1.11 in /usr/local/lib/python3.7/dist-packages (from matplotlib>=1.4->pandas_profiling) (1.19.5)\n",
            "Requirement already satisfied: pytz>=2017.2 in /usr/local/lib/python3.7/dist-packages (from pandas>=0.19->pandas_profiling) (2018.9)\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "X1pyjJoKcAxI"
      },
      "source": [
        "## *Initialize all the variables*"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Qd0ouyyjb8GI",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "06d89753-a681-419e-e082-289cb57b4faa"
      },
      "source": [
        "CS_GCP_PROJECT = \"dpani-sandbox\" #@param {type:\"string\"}\n",
        "CS_DATASET_LOCATION = \"us-central1-b\" #@param {type:\"string\"}\n",
        "# provides a mechansim to version\n",
        "# e.g. different dataset could use different models\n",
        "#   to compare the performance of the models\n",
        "CS_BQ_DATASET_NAME = \"cdp_ws_cs\" #@param {type:\"string\"}\n",
        "\n",
        "# the below are tables to create clv model from Sales and Customer tables\n",
        "# (used in the customer ltv solution guide)\n",
        "CS_LTV_SOL_DS_INPUT_SALES_TBL = \"ltv_sol_input_sales\" #@param {type:\"string\"}\n",
        "CS_LTV_SOL_DS_INPUT_CUST_TBL = \"ltv_sol_input_customer\" #@param {type:\"string\"} \n",
        "CS_LTV_SOL_DS_CLV_AGGREGATE_TBL = \"ltv_sol_clv_aggregation\" #@param {type:\"string\"}\n",
        "CS_LTV_SOL_DS_FEATURE_TBL = \"ltv_sol_features\" #@param {type:\"string\"}\n",
        "CS_LTV_SOL_DS_ML_FEATURE_PREP_SP = \"ltv_sol_ml_prep_sp\" #@param {type:\"string\"}\n",
        "CS_LTV_SOL_DS_ML_MODEL_NAME = \"ltv_sol_clv_ml_model\" #@param {type:\"string\"}\n",
        "CS_LTV_SOL_DS_ML_PREDICT_UTIL_SP = \"ltv_sol_clv_ml_predict_util_sp\" #@param {type:\"string\"}\n",
        "CS_LTV_SOL_DS_ML_PREDICT_TBL = \"ltv_sol_clv_predict_tbl\" #@param {type:\"string\"}\n",
        "\n",
        "# use the two variables to remove the outliers from the\n",
        "#   aggregation table --- used in the customer ltv calc\n",
        "CS_CLV_MAX_STDV_MONETARY = 500 #@param {type:\"integer\"}\n",
        "CS_CLV_MAX_STDV_QTY  = 100 #@param {type:\"integer\"}\n",
        "\n",
        "# use the four below variables to set the proper \n",
        "#   segmentation --- used in the customer ltv calc\n",
        "CS_CLV_WINDOW_LENGTH = 0 #@param {type:\"integer\"}\n",
        "CS_CLV_WINDOW_STEP = 30 #@param {type:\"integer\"}\n",
        "CS_CLV_WINDOW_STEP_INITIAL = 90 #@param {type:\"integer\"}\n",
        "CS_CLV_LENGTH_FUTURE = 30 #@param {type:\"integer\"}\n",
        "\n",
        "# create a variable that you can pass to the bq Cell magic\n",
        "# import the variables to the shell\n",
        "import os\n",
        "cs_all_args = [key for key in locals().keys() if key.startswith('CS')]\n",
        "CS_BQ_ARGS = {}\n",
        "for cs_each_key in cs_all_args:\n",
        "    # print (f\"{cs_each_key}:{locals()[cs_each_key]}\")\n",
        "    # del locals()[cs_each_key]\n",
        "    if cs_each_key != 'CS_BQ_ARGS':\n",
        "      CS_BQ_ARGS[cs_each_key] = locals()[cs_each_key]\n",
        "      os.environ[cs_each_key] = str(CS_BQ_ARGS[cs_each_key])\n",
        "print (CS_BQ_ARGS)"
      ],
      "execution_count": 4,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "{'CS_GCP_PROJECT': 'dpani-sandbox', 'CS_DATASET_LOCATION': 'us-central1-b', 'CS_BQ_DATASET_NAME': 'cdp_ws_cs', 'CS_LTV_SOL_DS_INPUT_SALES_TBL': 'ltv_sol_input_sales', 'CS_LTV_SOL_DS_INPUT_CUST_TBL': 'ltv_sol_input_customer', 'CS_LTV_SOL_DS_CLV_AGGREGATE_TBL': 'ltv_sol_clv_aggregation', 'CS_LTV_SOL_DS_FEATURE_TBL': 'ltv_sol_features', 'CS_LTV_SOL_DS_ML_FEATURE_PREP_SP': 'ltv_sol_ml_prep_sp', 'CS_LTV_SOL_DS_ML_MODEL_NAME': 'ltv_sol_clv_ml_model', 'CS_LTV_SOL_DS_ML_PREDICT_UTIL_SP': 'ltv_sol_clv_ml_predict_util_sp', 'CS_LTV_SOL_DS_ML_PREDICT_TBL': 'ltv_sol_clv_predict_tbl', 'CS_CLV_MAX_STDV_MONETARY': 500, 'CS_CLV_MAX_STDV_QTY': 100, 'CS_CLV_WINDOW_LENGTH': 0, 'CS_CLV_WINDOW_STEP': 30, 'CS_CLV_WINDOW_STEP_INITIAL': 90, 'CS_CLV_LENGTH_FUTURE': 30}\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "o_8F6CQkcLM6"
      },
      "source": [
        "## *Setup your Google Cloud project*"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "AP_4n7qGcVwD",
        "outputId": "7c730b6e-21ea-407d-dc23-3613ed030bee"
      },
      "source": [
        "!export CS_GCP_PROJECT\n",
        "!echo $CS_GCP_PROJECT\n",
        "# set the desired Google Cloud project\n",
        "!gcloud config set project $CS_GCP_PROJECT\n",
        "import os\n",
        "os.environ['GOOGLE_CLOUD_PROJECT'] = CS_GCP_PROJECT\n",
        "# validate that the Google Cloud project has been set properly.\n",
        "# !gcloud info --format='value(config.project)'"
      ],
      "execution_count": 5,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "dpani-sandbox\n",
            "Updated property [core/project].\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ACxbw0XgcrIH"
      },
      "source": [
        "## *Authenticate with Google Cloud*"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "PpsWXhzpcwPW"
      },
      "source": [
        "### Authenticate using ServiceAccount Key file"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "PtaLwZaZczO_"
      },
      "source": [
        "# download the ServiceAccount key and provide the path to the file below\n",
        "# CS_GCP_APPLICATION_CREDENTIALS = \"<Full path with the file name to the above downloaded json file>\"\n",
        "# CS_GCP_APPLICATION_CREDENTIALS = \"/Users/dpani/Downloads/dpani-sandbox-2-3073195cd132.json\"\n",
        "\n",
        "# uncomment the below code in codelab environment\n",
        "# authenticate using service account\n",
        "# from google.colab import files\n",
        "# # Upload service account key\n",
        "# keyfile_upload = files.upload()\n",
        "# CS_GCP_APPLICATION_CREDENTIALS = list(keyfile_upload.keys())[0]\n",
        "\n",
        "# import os\n",
        "# os.environ['GOOGLE_APPLICATION_CREDENTIALS'] = CS_GCP_APPLICATION_CREDENTIALS\n",
        "# # set the account\n",
        "# !echo \"Setting Service Account:\" $CS_GCP_APPLICATION_CREDENTIALS\n",
        "# !gcloud auth activate-service-account --key-file=$CS_GCP_APPLICATION_CREDENTIALS"
      ],
      "execution_count": 6,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Qk_4a_F7c6Ln"
      },
      "source": [
        "### Authenticate using OAuth"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "wkHkN-9Cc2tP"
      },
      "source": [
        "# uncomment the below code in codelab environment\n",
        "# authenticate using oauth\n",
        "import sys\n",
        "if 'google.colab' in sys.modules:\n",
        "  from google.colab import auth as google_auth\n",
        "  google_auth.authenticate_user()"
      ],
      "execution_count": 7,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "WjSV2AipdPMa"
      },
      "source": [
        "## *Enable the below Google Cloud Services for the solution*"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "1cOr19rHdIHA",
        "outputId": "9b350cf3-e387-4ee0-fc34-a99d6a2c8a24"
      },
      "source": [
        "# set the proper Permission for the required Google Cloud Services\n",
        "!gcloud services enable \\\n",
        "    storage-component.googleapis.com \\\n",
        "    bigquery.googleapis.com \\\n",
        "    ml.googleapis.com \\\n",
        "    notebooks.googleapis.com"
      ],
      "execution_count": 8,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Operation \"operations/acf.p2-976354649621-b7b53b5a-2778-405f-bee7-9a9a0ee78296\" finished successfully.\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "BGgbQNledSbh",
        "outputId": "67558b15-4126-4e5d-c91a-90f26f16529e"
      },
      "source": [
        "# validate that all desired Permission have been set properl.\n",
        "!gcloud services list | grep 'storage-component.googleapis.com\\|bigquery.googleapis.com\\|ml.googleapis.com\\|notebooks.googleapis.com'"
      ],
      "execution_count": 9,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "automl.googleapis.com                   Cloud AutoML API\n",
            "bigquery.googleapis.com                 BigQuery API\n",
            "ml.googleapis.com                       AI Platform Training & Prediction API\n",
            "notebooks.googleapis.com                Notebooks API\n",
            "storage-component.googleapis.com        Cloud Storage\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "wvcp5Hv-eicr"
      },
      "source": [
        "## *Create a BigQuery client, import the libraries, load the bigquery Cell magic*"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "PAj8Pxg7dcxK"
      },
      "source": [
        "# create a BQ client\n",
        "from google.cloud import bigquery\n",
        "bq_client = bigquery.Client(project=CS_GCP_PROJECT)\n",
        "# load the bigquery Cell magic\n",
        "# %load_ext google.cloud.bigquery\n",
        "%reload_ext google.cloud.bigquery"
      ],
      "execution_count": 10,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "aIgNBkJZfuHg",
        "outputId": "34a17f9a-0cc6-4946-ee89-1e617489ba8f",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 423
        }
      },
      "source": [
        "# test that BQ client works\n",
        "sql = \"\"\"\n",
        "    SELECT name\n",
        "    FROM `bigquery-public-data.usa_names.usa_1910_current`\n",
        "    WHERE state = 'TX'\n",
        "    LIMIT 100\n",
        "\"\"\"\n",
        "\n",
        "# Run a Standard SQL query using the environment's default project\n",
        "df = bq_client.query(sql).to_dataframe()\n",
        "df"
      ],
      "execution_count": 11,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "\n",
              "  <div id=\"df-4eee36b4-23e9-434b-ae8f-2dafe7e0f6f0\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>name</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>Mary</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>Ruby</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>Annie</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>Willie</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>Ruth</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>...</th>\n",
              "      <td>...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>95</th>\n",
              "      <td>Leona</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>96</th>\n",
              "      <td>Lucile</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>97</th>\n",
              "      <td>Lucy</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>98</th>\n",
              "      <td>Manuela</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>99</th>\n",
              "      <td>Rosie</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>100 rows × 1 columns</p>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-4eee36b4-23e9-434b-ae8f-2dafe7e0f6f0')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-4eee36b4-23e9-434b-ae8f-2dafe7e0f6f0 button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-4eee36b4-23e9-434b-ae8f-2dafe7e0f6f0');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLink = '<div>Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.</div>';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          element.innerHTML += docLink;\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ],
            "text/plain": [
              "       name\n",
              "0      Mary\n",
              "1      Ruby\n",
              "2     Annie\n",
              "3    Willie\n",
              "4      Ruth\n",
              "..      ...\n",
              "95    Leona\n",
              "96   Lucile\n",
              "97     Lucy\n",
              "98  Manuela\n",
              "99    Rosie\n",
              "\n",
              "[100 rows x 1 columns]"
            ]
          },
          "metadata": {},
          "execution_count": 11
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "OYI9Z6vteqdD"
      },
      "source": [
        "# Utilities fuctions"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "-L2h1iO1et67"
      },
      "source": [
        "## *Create the BigQuery dataset (DDL)*"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "_8PjcUp0extk"
      },
      "source": [
        "# create_bq_ds\n",
        "def create_bq_ds(CS_GCP_PROJECT: str,\n",
        "                 CS_BQ_DATASET_NAME: str,\n",
        "                 CS_LOCATION: str\n",
        "                 ):\n",
        "  \"\"\"The function creates a BigQuery dataset if don't exist.\n",
        "\n",
        "      The idea is to create DataSet only one time.\n",
        "      Args:\n",
        "        CS_GCP_PROJECT:(:obj:`str`): Google Cloud project for deployment\n",
        "        CS_BQ_DATASET_NAME:(:obj:`str`): Name of the dataset.\n",
        "        CS_LOCATION:(:obj:`str`): Location of the Google Cloud region\n",
        "          of the BigQuery dataset\n",
        "  \"\"\"\n",
        "  from google.cloud import bigquery\n",
        "  from google.cloud.exceptions import NotFound\n",
        "  client = bigquery.Client()\n",
        "  dataset_id = f\"{CS_GCP_PROJECT}.{CS_BQ_DATASET_NAME}\"\n",
        "\n",
        "  ds_found = True\n",
        "  try:\n",
        "    client.get_dataset(dataset_id)  # Make an API request.\n",
        "    print('Dataset {} already exists'.format(dataset_id))\n",
        "  except NotFound:\n",
        "    print('Dataset {} is not found'.format(dataset_id))\n",
        "    ds_found = False\n",
        "\n",
        "  import traceback\n",
        "  if ds_found is False:\n",
        "    try:\n",
        "      # Construct a full Dataset object to send to the API.\n",
        "      dataset = bigquery.Dataset(dataset_id)\n",
        "      dataset.location = CS_LOCATION.split('-')[0].upper()\n",
        "      dataset = client.create_dataset(dataset)  # Make an API request.\n",
        "      print('Created dataset {}.{} in location: {}.'.\\\n",
        "            format(client.project, dataset.dataset_id, dataset.location))\n",
        "    except Exception as e:\n",
        "      error = traceback.format_exc()\n",
        "      print(error)\n",
        "      print(e)\n",
        "      raise RuntimeError(f\"Can't create the BigQuery DS {dataset_id}\")"
      ],
      "execution_count": 12,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Vp6BAH1O97qz"
      },
      "source": [
        "## *Load from Google Cloud Stroage the BigQuery dataset (DDL)*"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "5ros351b-FRE"
      },
      "source": [
        "def load_table_uri_autodetect_csv(CS_GCP_PROJECT: str,\n",
        "                 CS_BQ_DATASET_NAME: str,\n",
        "                 CS_BQ_TABLE_ID: str, \n",
        "                 uri: str\n",
        "                 ):\n",
        "  \"\"\"The function loads from CSV to a BQ table.\n",
        "\n",
        "      Args:\n",
        "        CS_GCP_PROJECT:(:obj:`str`): Google Cloud project for deployment\n",
        "        CS_BQ_DATASET_NAME:(:obj:`str`): Name of the dataset.\n",
        "        CS_BQ_TABLE_NAME:(:obj:`str`): Name of the table.\n",
        "        uri:(:obj:`str`): Google Cloud Storage uri of the csv file\n",
        "  \"\"\"\n",
        "  # [START bigquery_load_table_gcs_csv_autodetect]\n",
        "  from google.cloud import bigquery\n",
        "  from google.cloud.exceptions import NotFound\n",
        "\n",
        "  # table_id = f\"{CS_GCP_PROJECT}.{CS_BQ_DATASET_NAME}.{CS_BQ_TABLE_ID}\"\n",
        "  table_id = f\"{CS_BQ_DATASET_NAME}.{CS_BQ_TABLE_ID}\"\n",
        "\n",
        "  # Construct a BigQuery client object.\n",
        "  client = bigquery.Client()\n",
        "\n",
        "  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/bigquery/us-states/us-states.csv\"\n",
        "  # uri = \"gs://solutions-public-assets/analytics-componentized-patterns/ltv/sales_*\"\n",
        "  load_job = client.load_table_from_uri(\n",
        "      uri, table_id, job_config=job_config\n",
        "  )  # Make an API request.\n",
        "  load_job.result()  # Waits for the job to complete.\n",
        "  destination_table = client.get_table(table_id)\n",
        "  print(\"Loaded {} rows in table {}.\".format(destination_table.num_rows,\n",
        "                                             table_id))\n",
        "  # [END bigquery_load_table_gcs_csv_autodetect]"
      ],
      "execution_count": 13,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "GUwTSuNbfGRv"
      },
      "source": [
        "## *Delete a dataset in BigQuery (DDL)*"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "9NwZQBeUfHp_"
      },
      "source": [
        "# delete the BigQuery dataset...!!! BE CAREFUL !!!\n",
        "def delete_dataset(dataset_id):\n",
        "    \"\"\"Deletes a BigQuery dataset\n",
        "    This is not recommendated to use it in a production enviornment.\n",
        "    Comes handy in the iterative development and testing phases of the SDLC.\n",
        "    !!! BE CAREFUL !!!!\n",
        "    Args:\n",
        "        dataset_id(:obj:`str`): The BigQuery dataset name that we want to delete\n",
        "    \"\"\"\n",
        "    # [START bigquery_delete_dataset]\n",
        "    from google.cloud import bigquery\n",
        "    # Construct a BigQuery client object.\n",
        "    client = bigquery.Client()\n",
        "    # dataset_id = 'your-project.your_dataset'\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\n",
        "    #     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",
        "    print(\"Deleted dataset '{}'.\".format(dataset_id))"
      ],
      "execution_count": 14,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "P44MN_C-e-l2"
      },
      "source": [
        "## *Execute query in BigQuery (DDL + DML)*"
      ],
      "execution_count": 15,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "lxNg2I2rfM34"
      },
      "source": [
        "## *Execute SQL BigQuery*\n"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "aeWDs4TCfCrH"
      },
      "source": [
        "# execute_sql\n",
        "def execute_sql(sql_query: str):\n",
        "  \"\"\"The executes the sql.\n",
        "\n",
        "    Args:\n",
        "        sql_query:(:obj:`str`): SQL query to execute\n",
        "  \"\"\"\n",
        "  from google.cloud import bigquery\n",
        "  from google.cloud.exceptions import NotFound\n",
        "  client = bigquery.Client()\n",
        "  import traceback\n",
        "  try:\n",
        "    client = bigquery.Client()\n",
        "    query_job = client.query(sql_query)  # Make an API request.\n",
        "    print(f\"Querty executed.\")\n",
        "    results = query_job.result()  # Waits for job to complete.\n",
        "    for row in results:\n",
        "      # print(\"{} : {} views\".format(row.url, row.view_count))\n",
        "      print (row)\n",
        "  except Exception as e:\n",
        "    error = traceback.format_exc()\n",
        "    print(error)\n",
        "    print(e)\n",
        "    raise RuntimeError(f\"Can't execute the query {sql_query}\")"
      ],
      "execution_count": 16,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "CCnvIgo-fQUX"
      },
      "source": [
        "# delete BigQuery table if not needed...!!! BE CAREFUL !!!\n",
        "def delete_table(table_id):\n",
        "  \"\"\"Deletes a BigQuery table\n",
        "    This is not recommendated to use it in a production enviornment.\n",
        "    Comes handy in the iterative development and testing phases of the SDLC.\n",
        "    !!! BE CAREFUL !!!!\n",
        "    Args:\n",
        "      table_id(:obj:`str`): The BigQuery table name that we want to delete\n",
        "  \"\"\"\n",
        "  from google.cloud import bigquery\n",
        "  # Construct a BigQuery client object.\n",
        "  client = bigquery.Client()\n",
        "  # client.delete_table(table_id, not_found_ok=True)  # Make an API request.\n",
        "  client.delete_table(table_id)  # Make an API request.\n",
        "  print(\"Deleted table '{}'.\".format(table_id))"
      ],
      "execution_count": 17,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "aTys1vaOfVzI"
      },
      "source": [
        "## *Deletes an ML model* (DDL)"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "B6qWNs-jfYyA"
      },
      "source": [
        "# delete the BQML model if not needed...!!! BE CAREFUL !!!\n",
        "def delete_model(model_id):\n",
        "  \"\"\"Deletes a BigQuery table\n",
        "    This is not recommendated to use it in a production enviornment.\n",
        "    Comes handy in the iterative development and testing phases of the SDLC.\n",
        "    !!! BE CAREFUL !!!!\n",
        "    Args:\n",
        "      delete_model(:obj:`str`): The BigQuery ML model name that we want to delete\n",
        "  \"\"\"\n",
        "  from google.cloud import bigquery\n",
        "  # Construct a BigQuery client object.\n",
        "  client = bigquery.Client()\n",
        "  # TODO(developer): Set model_id to the ID of the model to fetch.\n",
        "  # model_id = 'your-project.your_dataset.your_model'\n",
        "  client.delete_model(model_id)  # Make an API request.\n",
        "  print(\"Deleted model '{}'.\".format(model_id))"
      ],
      "execution_count": 18,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "37cbJ2mVfbL1"
      },
      "source": [
        "# Collect phase"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "8gVYN10706y0"
      },
      "source": [
        "## *Creates the BigQuery dataset* (DDL)"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "WCG8ZXRZ05_V",
        "outputId": "fd3fa839-dadc-40c4-f489-c914ba05f60b"
      },
      "source": [
        "# create the bq dataset\n",
        "create_bq_ds(CS_GCP_PROJECT,\n",
        " CS_BQ_DATASET_NAME,\n",
        " CS_DATASET_LOCATION,\n",
        ")"
      ],
      "execution_count": 19,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Dataset dpani-sandbox.cdp_ws_cs is not found\n",
            "Created dataset dpani-sandbox.cdp_ws_cs in location: US.\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "5fLSOZeqwfnz"
      },
      "source": [
        "## *Create and populate Sales and Customer table* (DDL + DML)"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "31Pj1mhKAagn",
        "outputId": "6f598731-ec2b-4a6f-9a11-d2e3fa633aaf"
      },
      "source": [
        "# load sales data\n",
        "load_table_uri_autodetect_csv (CS_GCP_PROJECT, \n",
        "                               CS_BQ_DATASET_NAME, \n",
        "                               CS_LTV_SOL_DS_INPUT_SALES_TBL,\n",
        "                               \"gs://solutions-public-assets/analytics-componentized-patterns/ltv/sales_*\")"
      ],
      "execution_count": 20,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Loaded 20467201 rows in table cdp_ws_cs.ltv_sol_input_sales.\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "PrGhismHDSJ5",
        "outputId": "526c8920-2155-434e-da43-e19159bd66e9"
      },
      "source": [
        "# load customer data\n",
        "load_table_uri_autodetect_csv (CS_GCP_PROJECT, \n",
        "                               CS_BQ_DATASET_NAME, \n",
        "                               CS_LTV_SOL_DS_INPUT_CUST_TBL,\n",
        "                               \"gs://solutions-public-assets/analytics-componentized-patterns/ltv/crm.csv\")"
      ],
      "execution_count": 21,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Loaded 100000 rows in table cdp_ws_cs.ltv_sol_input_customer.\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "# Transform phase"
      ],
      "metadata": {
        "id": "qUjCG1KhZVkZ"
      }
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "dHlOoGbNEsNh"
      },
      "source": [
        "### *Data prep and transform for the Customer LTV model*"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "TP0bWzZREsgl",
        "outputId": "aefaeac3-1a6f-4280-c89e-f896839fc443"
      },
      "source": [
        "# sql to create the aggregate table\n",
        "cs_ltv_sol_create_aggregate_order_tbl = f\"\"\"\n",
        "  DECLARE MAX_STDV_MONETARY INT64 DEFAULT {CS_CLV_MAX_STDV_MONETARY};\n",
        "  DECLARE MAX_STDV_QTY INT64 DEFAULT {CS_CLV_MAX_STDV_QTY};\n",
        "\n",
        "  CREATE OR REPLACE TABLE `{CS_BQ_DATASET_NAME}.{CS_LTV_SOL_DS_CLV_AGGREGATE_TBL}` AS\n",
        "  SELECT\n",
        "    customer_id,\n",
        "    order_day,\n",
        "    ROUND(day_value_after_returns, 2) AS value,\n",
        "    day_qty_after_returns as qty_articles,\n",
        "    day_num_returns AS num_returns,\n",
        "    CEIL(avg_time_to_return) AS time_to_return\n",
        "  FROM (\n",
        "    SELECT\n",
        "      customer_id,\n",
        "      order_day,\n",
        "      SUM(order_value_after_returns) AS day_value_after_returns,\n",
        "      STDDEV(SUM(order_value_after_returns)) OVER(PARTITION BY customer_id ORDER BY SUM(order_value_after_returns)) AS stdv_value,\n",
        "      SUM(order_qty_after_returns) AS day_qty_after_returns,\n",
        "      STDDEV(SUM(order_qty_after_returns)) OVER(PARTITION BY customer_id ORDER BY SUM(order_qty_after_returns)) AS stdv_qty,\n",
        "      CASE\n",
        "        WHEN MIN(order_min_qty) < 0 THEN count(1)\n",
        "        ELSE 0\n",
        "      END AS day_num_returns,\n",
        "      CASE\n",
        "        WHEN MIN(order_min_qty) < 0 THEN AVG(time_to_return)\n",
        "        ELSE NULL\n",
        "      END AS avg_time_to_return\n",
        "    FROM (\n",
        "      SELECT\n",
        "        customer_id,\n",
        "        order_id,\n",
        "        -- Gives the order date vs return(s) dates.\n",
        "        MIN(transaction_date) AS order_day,\n",
        "        MAX(transaction_date) AS return_final_day,\n",
        "        DATE_DIFF(MAX(transaction_date), MIN(transaction_date), DAY) AS time_to_return,\n",
        "        -- Aggregates all products in the order\n",
        "        -- and all products returned later.\n",
        "        SUM(qty * unit_price) AS order_value_after_returns,\n",
        "        SUM(qty) AS order_qty_after_returns,\n",
        "        -- If negative, order has qty return(s).\n",
        "        MIN(qty) order_min_qty\n",
        "      FROM\n",
        "        `{CS_BQ_DATASET_NAME}.{CS_LTV_SOL_DS_INPUT_SALES_TBL}`\n",
        "      GROUP BY\n",
        "        customer_id,\n",
        "        order_id)\n",
        "    GROUP BY\n",
        "      customer_id,\n",
        "      order_day)\n",
        "  WHERE\n",
        "    -- [Optional] Remove dates with outliers per a customer.\n",
        "    (stdv_value < MAX_STDV_MONETARY\n",
        "      OR stdv_value IS NULL) AND\n",
        "    (stdv_qty < MAX_STDV_QTY\n",
        "      OR stdv_qty IS NULL);\n",
        "\"\"\"\n",
        "print (cs_ltv_sol_create_aggregate_order_tbl)\n",
        "execute_sql(cs_ltv_sol_create_aggregate_order_tbl)"
      ],
      "execution_count": 22,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "\n",
            "  DECLARE MAX_STDV_MONETARY INT64 DEFAULT 500;\n",
            "  DECLARE MAX_STDV_QTY INT64 DEFAULT 100;\n",
            "\n",
            "  CREATE OR REPLACE TABLE `cdp_ws_cs.ltv_sol_clv_aggregation` AS\n",
            "  SELECT\n",
            "    customer_id,\n",
            "    order_day,\n",
            "    ROUND(day_value_after_returns, 2) AS value,\n",
            "    day_qty_after_returns as qty_articles,\n",
            "    day_num_returns AS num_returns,\n",
            "    CEIL(avg_time_to_return) AS time_to_return\n",
            "  FROM (\n",
            "    SELECT\n",
            "      customer_id,\n",
            "      order_day,\n",
            "      SUM(order_value_after_returns) AS day_value_after_returns,\n",
            "      STDDEV(SUM(order_value_after_returns)) OVER(PARTITION BY customer_id ORDER BY SUM(order_value_after_returns)) AS stdv_value,\n",
            "      SUM(order_qty_after_returns) AS day_qty_after_returns,\n",
            "      STDDEV(SUM(order_qty_after_returns)) OVER(PARTITION BY customer_id ORDER BY SUM(order_qty_after_returns)) AS stdv_qty,\n",
            "      CASE\n",
            "        WHEN MIN(order_min_qty) < 0 THEN count(1)\n",
            "        ELSE 0\n",
            "      END AS day_num_returns,\n",
            "      CASE\n",
            "        WHEN MIN(order_min_qty) < 0 THEN AVG(time_to_return)\n",
            "        ELSE NULL\n",
            "      END AS avg_time_to_return\n",
            "    FROM (\n",
            "      SELECT\n",
            "        customer_id,\n",
            "        order_id,\n",
            "        -- Gives the order date vs return(s) dates.\n",
            "        MIN(transaction_date) AS order_day,\n",
            "        MAX(transaction_date) AS return_final_day,\n",
            "        DATE_DIFF(MAX(transaction_date), MIN(transaction_date), DAY) AS time_to_return,\n",
            "        -- Aggregates all products in the order\n",
            "        -- and all products returned later.\n",
            "        SUM(qty * unit_price) AS order_value_after_returns,\n",
            "        SUM(qty) AS order_qty_after_returns,\n",
            "        -- If negative, order has qty return(s).\n",
            "        MIN(qty) order_min_qty\n",
            "      FROM\n",
            "        `cdp_ws_cs.ltv_sol_input_sales`\n",
            "      GROUP BY\n",
            "        customer_id,\n",
            "        order_id)\n",
            "    GROUP BY\n",
            "      customer_id,\n",
            "      order_day)\n",
            "  WHERE\n",
            "    -- [Optional] Remove dates with outliers per a customer.\n",
            "    (stdv_value < MAX_STDV_MONETARY\n",
            "      OR stdv_value IS NULL) AND\n",
            "    (stdv_qty < MAX_STDV_QTY\n",
            "      OR stdv_qty IS NULL);\n",
            "\n",
            "Querty executed.\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "hZxaKw2mGb-J"
      },
      "source": [
        "### Total aggregate sales record (DML)"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 81
        },
        "id": "t8vHei7JGEIo",
        "outputId": "f9e24a48-f10c-4159-e49e-73f878a9fa04"
      },
      "source": [
        "# print total number of records in the table\n",
        "df = bq_client.query('''\n",
        "  SELECT count (*) AS uci_total_sales_aggregate_records\n",
        "  FROM `%s.%s`\n",
        "''' % (CS_BQ_DATASET_NAME, CS_LTV_SOL_DS_CLV_AGGREGATE_TBL)).to_dataframe()\n",
        "# print total number of records in the table\n",
        "df"
      ],
      "execution_count": 23,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "\n",
              "  <div id=\"df-8a9b4d1d-f985-46ba-aab6-4e97209ce8ef\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>uci_total_sales_aggregate_records</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>362212</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-8a9b4d1d-f985-46ba-aab6-4e97209ce8ef')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-8a9b4d1d-f985-46ba-aab6-4e97209ce8ef button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-8a9b4d1d-f985-46ba-aab6-4e97209ce8ef');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLink = '<div>Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.</div>';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          element.innerHTML += docLink;\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ],
            "text/plain": [
              "   uci_total_sales_aggregate_records\n",
              "0                             362212"
            ]
          },
          "metadata": {},
          "execution_count": 23
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "GkB6LBJsJoQd"
      },
      "source": [
        "## Create a Features table that you will use to model clv (DDL)"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "DtB2uOHHJvdj",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "283b6417-8fcf-4329-de15-de96fde2e83a"
      },
      "source": [
        "# create features for the clv model\n",
        "cs_ltv_sol_ds_create_feature_tbl = f\"\"\"\n",
        "  CREATE OR REPLACE TABLE `{CS_BQ_DATASET_NAME}.{CS_LTV_SOL_DS_FEATURE_TBL}`\n",
        "  (\n",
        "    customer_id INT64,\n",
        "    monetary FLOAT64,\n",
        "    frequency INT64,\n",
        "    recency INT64,\n",
        "    T INT64,\n",
        "    time_between FLOAT64,\n",
        "    avg_basket_value FLOAT64,\n",
        "    avg_basket_size FLOAT64,\n",
        "    has_returns STRING,\n",
        "    avg_time_to_return FLOAT64,\n",
        "    num_returns INT64,\n",
        "    -- threshold DATE,\n",
        "    -- step INT64,\n",
        "    target_monetary FLOAT64,\n",
        "  )\n",
        "\"\"\"\n",
        "print (cs_ltv_sol_ds_create_feature_tbl)\n",
        "execute_sql(cs_ltv_sol_ds_create_feature_tbl)"
      ],
      "execution_count": 24,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "\n",
            "  CREATE OR REPLACE TABLE `cdp_ws_cs.ltv_sol_features`\n",
            "  (\n",
            "    customer_id INT64,\n",
            "    monetary FLOAT64,\n",
            "    frequency INT64,\n",
            "    recency INT64,\n",
            "    T INT64,\n",
            "    time_between FLOAT64,\n",
            "    avg_basket_value FLOAT64,\n",
            "    avg_basket_size FLOAT64,\n",
            "    has_returns STRING,\n",
            "    avg_time_to_return FLOAT64,\n",
            "    num_returns INT64,\n",
            "    -- threshold DATE,\n",
            "    -- step INT64,\n",
            "    target_monetary FLOAT64,\n",
            "  )\n",
            "\n",
            "Querty executed.\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "B6d9Cw_9RyIa"
      },
      "source": [
        "## Create a stored proc to populate the Features table (DDL + DML)"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "EunqTNJxR65L",
        "outputId": "cc960927-f736-4158-80f6-1597a694deec"
      },
      "source": [
        "# create a stored proc to populate the Features table\n",
        "cs_ltv_sol_ds_create_sp_populate_feature_tbl = f\"\"\"\n",
        "  CREATE OR REPLACE PROCEDURE `{CS_BQ_DATASET_NAME}.{CS_LTV_SOL_DS_ML_FEATURE_PREP_SP}`(\n",
        "    WINDOW_LENGTH INT64,         -- How many days back for inputs transactions.\n",
        "    WINDOW_STEP INT64,          -- How many days between thresholds.\n",
        "    WINDOW_STEP_INITIAL INT64,  -- How many days for the first window.        \n",
        "    LENGTH_FUTURE INT64        -- How many days to predict for.  \n",
        "  )\n",
        "\n",
        "  BEGIN\n",
        "\n",
        "  DECLARE MIN_DATE DATE;        -- Date of the first order in the dataset.                                     \n",
        "  DECLARE MAX_DATE DATE;        -- Date of the final order in the dataset.\n",
        "  DECLARE THRESHOLD_DATE DATE;  -- Date that separates inputs orders from target orders.  \n",
        "  DECLARE WINDOW_START DATE;    -- Date at which an input transactions window starts.\n",
        "  DECLARE STEP INT64 DEFAULT 1; -- Index of the window being run.\n",
        "\n",
        "  -- Aggregates per date per customers.\n",
        "  -- Creates the inputs and targets accross multiple threshold dates.\n",
        "  SET (MIN_DATE, MAX_DATE) = (\n",
        "    SELECT AS STRUCT \n",
        "      MIN(order_day) AS min_days,\n",
        "      MAX(order_day) AS max_days\n",
        "    FROM\n",
        "      `{CS_BQ_DATASET_NAME}.{CS_LTV_SOL_DS_CLV_AGGREGATE_TBL}`\n",
        "  );\n",
        "\n",
        "  SET THRESHOLD_DATE = MIN_DATE;\n",
        "\n",
        "  LOOP\n",
        "    -- Can choose a longer original window in case \n",
        "    -- there were not many orders in the early days.\n",
        "    IF STEP = 1 THEN\n",
        "      SET THRESHOLD_DATE = DATE_ADD(THRESHOLD_DATE, INTERVAL WINDOW_STEP_INITIAL DAY); \n",
        "    ELSE\n",
        "      SET THRESHOLD_DATE = DATE_ADD(THRESHOLD_DATE, INTERVAL WINDOW_STEP DAY);\n",
        "    END IF;\n",
        "    SET STEP = STEP + 1;\n",
        "\n",
        "    IF THRESHOLD_DATE >= DATE_SUB(MAX_DATE, INTERVAL (WINDOW_STEP) DAY) THEN\n",
        "      LEAVE;\n",
        "    END IF;\n",
        "\n",
        "    -- Takes all transactions before the threshold date unless you decide\n",
        "    -- to use a different window lenght to test model performance.\n",
        "    IF WINDOW_LENGTH != 0 THEN\n",
        "      SET WINDOW_START = DATE_SUB(THRESHOLD_DATE, INTERVAL WINDOW_LENGTH DAY);\n",
        "    ELSE\n",
        "      SET WINDOW_START = MIN_DATE;\n",
        "    END IF;\n",
        "\n",
        "    INSERT `{CS_BQ_DATASET_NAME}.{CS_LTV_SOL_DS_FEATURE_TBL}`\n",
        "    SELECT\n",
        "      -- CASE\n",
        "      --   WHEN THRESHOLD_DATE <= DATE_SUB(MAX_DATE, INTERVAL LENGTH_FUTURE DAY) THEN 'UNASSIGNED'\n",
        "      --   ELSE 'TEST'\n",
        "      -- END AS dataset,\n",
        "      tf.customer_id,\n",
        "      ROUND(tf.monetary_orders, 2) AS monetary,\n",
        "      tf.cnt_orders AS frequency,\n",
        "      tf.recency,\n",
        "      tf.T,\n",
        "      ROUND(tf.recency/cnt_orders, 2) AS time_between,\n",
        "      ROUND(tf.avg_basket_value, 2) AS avg_basket_value,\n",
        "      ROUND(tf.avg_basket_size, 2) AS avg_basket_size,\n",
        "      has_returns,\n",
        "      CEIL(avg_time_to_return) AS avg_time_to_return,\n",
        "      num_returns,\n",
        "      -- THRESHOLD_DATE AS threshold,\n",
        "      -- STEP - 1 AS step,\n",
        "      ROUND(tt.target_monetary, 2) AS target_monetary,\n",
        "    FROM (\n",
        "        -- This SELECT uses only data before THRESHOLD_DATE to make features.\n",
        "        SELECT\n",
        "          customer_id,\n",
        "          SUM(value) AS monetary_orders,\n",
        "          DATE_DIFF(MAX(order_day), MIN(order_day), DAY) AS recency,\n",
        "          DATE_DIFF(THRESHOLD_DATE, MIN(order_day), DAY) AS T,\n",
        "          COUNT(DISTINCT order_day) AS cnt_orders,\n",
        "          AVG(qty_articles) avg_basket_size,\n",
        "          AVG(value) avg_basket_value,\n",
        "          CASE\n",
        "            WHEN SUM(num_returns) > 0 THEN 'y'\n",
        "            ELSE 'n'\n",
        "          END AS has_returns,\n",
        "          AVG(time_to_return) avg_time_to_return,\n",
        "          THRESHOLD_DATE AS threshold,\n",
        "          SUM(num_returns) num_returns,\n",
        "        FROM\n",
        "          `{CS_BQ_DATASET_NAME}.{CS_LTV_SOL_DS_CLV_AGGREGATE_TBL}`\n",
        "        WHERE\n",
        "          order_day <= THRESHOLD_DATE AND\n",
        "          order_day >= WINDOW_START\n",
        "        GROUP BY\n",
        "          customer_id\n",
        "      ) tf\n",
        "    INNER JOIN (\n",
        "      -- This SELECT uses all orders that happened between threshold and \n",
        "      -- threshold + LENGTH_FUTURE to calculte the target monetary.\n",
        "      SELECT\n",
        "        customer_id,\n",
        "        SUM(value) target_monetary\n",
        "      FROM\n",
        "        `{CS_BQ_DATASET_NAME}.{CS_LTV_SOL_DS_CLV_AGGREGATE_TBL}`\n",
        "      WHERE\n",
        "        order_day <= DATE_ADD(THRESHOLD_DATE, INTERVAL LENGTH_FUTURE DAY)\n",
        "        -- Overall value is similar to predicting only what's after threshold.\n",
        "        -- and the prediction performs better. We can substract later.\n",
        "        -- AND order_day > THRESHOLD_DATE\n",
        "      GROUP BY\n",
        "        customer_id) tt\n",
        "    ON\n",
        "      tf.customer_id = tt.customer_id;\n",
        "\n",
        "  END LOOP;\n",
        "  END\n",
        "\"\"\"\n",
        "print (cs_ltv_sol_ds_create_sp_populate_feature_tbl)\n",
        "execute_sql(cs_ltv_sol_ds_create_sp_populate_feature_tbl)"
      ],
      "execution_count": 25,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "\n",
            "  CREATE OR REPLACE PROCEDURE `cdp_ws_cs.ltv_sol_ml_prep_sp`(\n",
            "    WINDOW_LENGTH INT64,         -- How many days back for inputs transactions.\n",
            "    WINDOW_STEP INT64,          -- How many days between thresholds.\n",
            "    WINDOW_STEP_INITIAL INT64,  -- How many days for the first window.        \n",
            "    LENGTH_FUTURE INT64        -- How many days to predict for.  \n",
            "  )\n",
            "\n",
            "  BEGIN\n",
            "\n",
            "  DECLARE MIN_DATE DATE;        -- Date of the first order in the dataset.                                     \n",
            "  DECLARE MAX_DATE DATE;        -- Date of the final order in the dataset.\n",
            "  DECLARE THRESHOLD_DATE DATE;  -- Date that separates inputs orders from target orders.  \n",
            "  DECLARE WINDOW_START DATE;    -- Date at which an input transactions window starts.\n",
            "  DECLARE STEP INT64 DEFAULT 1; -- Index of the window being run.\n",
            "\n",
            "  -- Aggregates per date per customers.\n",
            "  -- Creates the inputs and targets accross multiple threshold dates.\n",
            "  SET (MIN_DATE, MAX_DATE) = (\n",
            "    SELECT AS STRUCT \n",
            "      MIN(order_day) AS min_days,\n",
            "      MAX(order_day) AS max_days\n",
            "    FROM\n",
            "      `cdp_ws_cs.ltv_sol_clv_aggregation`\n",
            "  );\n",
            "\n",
            "  SET THRESHOLD_DATE = MIN_DATE;\n",
            "\n",
            "  LOOP\n",
            "    -- Can choose a longer original window in case \n",
            "    -- there were not many orders in the early days.\n",
            "    IF STEP = 1 THEN\n",
            "      SET THRESHOLD_DATE = DATE_ADD(THRESHOLD_DATE, INTERVAL WINDOW_STEP_INITIAL DAY); \n",
            "    ELSE\n",
            "      SET THRESHOLD_DATE = DATE_ADD(THRESHOLD_DATE, INTERVAL WINDOW_STEP DAY);\n",
            "    END IF;\n",
            "    SET STEP = STEP + 1;\n",
            "\n",
            "    IF THRESHOLD_DATE >= DATE_SUB(MAX_DATE, INTERVAL (WINDOW_STEP) DAY) THEN\n",
            "      LEAVE;\n",
            "    END IF;\n",
            "\n",
            "    -- Takes all transactions before the threshold date unless you decide\n",
            "    -- to use a different window lenght to test model performance.\n",
            "    IF WINDOW_LENGTH != 0 THEN\n",
            "      SET WINDOW_START = DATE_SUB(THRESHOLD_DATE, INTERVAL WINDOW_LENGTH DAY);\n",
            "    ELSE\n",
            "      SET WINDOW_START = MIN_DATE;\n",
            "    END IF;\n",
            "\n",
            "    INSERT `cdp_ws_cs.ltv_sol_features`\n",
            "    SELECT\n",
            "      -- CASE\n",
            "      --   WHEN THRESHOLD_DATE <= DATE_SUB(MAX_DATE, INTERVAL LENGTH_FUTURE DAY) THEN 'UNASSIGNED'\n",
            "      --   ELSE 'TEST'\n",
            "      -- END AS dataset,\n",
            "      tf.customer_id,\n",
            "      ROUND(tf.monetary_orders, 2) AS monetary,\n",
            "      tf.cnt_orders AS frequency,\n",
            "      tf.recency,\n",
            "      tf.T,\n",
            "      ROUND(tf.recency/cnt_orders, 2) AS time_between,\n",
            "      ROUND(tf.avg_basket_value, 2) AS avg_basket_value,\n",
            "      ROUND(tf.avg_basket_size, 2) AS avg_basket_size,\n",
            "      has_returns,\n",
            "      CEIL(avg_time_to_return) AS avg_time_to_return,\n",
            "      num_returns,\n",
            "      -- THRESHOLD_DATE AS threshold,\n",
            "      -- STEP - 1 AS step,\n",
            "      ROUND(tt.target_monetary, 2) AS target_monetary,\n",
            "    FROM (\n",
            "        -- This SELECT uses only data before THRESHOLD_DATE to make features.\n",
            "        SELECT\n",
            "          customer_id,\n",
            "          SUM(value) AS monetary_orders,\n",
            "          DATE_DIFF(MAX(order_day), MIN(order_day), DAY) AS recency,\n",
            "          DATE_DIFF(THRESHOLD_DATE, MIN(order_day), DAY) AS T,\n",
            "          COUNT(DISTINCT order_day) AS cnt_orders,\n",
            "          AVG(qty_articles) avg_basket_size,\n",
            "          AVG(value) avg_basket_value,\n",
            "          CASE\n",
            "            WHEN SUM(num_returns) > 0 THEN 'y'\n",
            "            ELSE 'n'\n",
            "          END AS has_returns,\n",
            "          AVG(time_to_return) avg_time_to_return,\n",
            "          THRESHOLD_DATE AS threshold,\n",
            "          SUM(num_returns) num_returns,\n",
            "        FROM\n",
            "          `cdp_ws_cs.ltv_sol_clv_aggregation`\n",
            "        WHERE\n",
            "          order_day <= THRESHOLD_DATE AND\n",
            "          order_day >= WINDOW_START\n",
            "        GROUP BY\n",
            "          customer_id\n",
            "      ) tf\n",
            "    INNER JOIN (\n",
            "      -- This SELECT uses all orders that happened between threshold and \n",
            "      -- threshold + LENGTH_FUTURE to calculte the target monetary.\n",
            "      SELECT\n",
            "        customer_id,\n",
            "        SUM(value) target_monetary\n",
            "      FROM\n",
            "        `cdp_ws_cs.ltv_sol_clv_aggregation`\n",
            "      WHERE\n",
            "        order_day <= DATE_ADD(THRESHOLD_DATE, INTERVAL LENGTH_FUTURE DAY)\n",
            "        -- Overall value is similar to predicting only what's after threshold.\n",
            "        -- and the prediction performs better. We can substract later.\n",
            "        -- AND order_day > THRESHOLD_DATE\n",
            "      GROUP BY\n",
            "        customer_id) tt\n",
            "    ON\n",
            "      tf.customer_id = tt.customer_id;\n",
            "\n",
            "  END LOOP;\n",
            "  END\n",
            "\n",
            "Querty executed.\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "bC3jkFFCTbJW",
        "outputId": "293b009c-ff43-463d-dede-dc39f355433e"
      },
      "source": [
        "# populate the feature table\n",
        "# with the current dataset it takes ~1 minute to complete the execution\n",
        "cs_ltv_sol_ds_invoke_sp_populate_feature_tbl = f\"\"\"\n",
        "  DECLARE WINDOW_LENGTH INT64 DEFAULT {CS_CLV_WINDOW_LENGTH};\n",
        "  DECLARE WINDOW_STEP INT64 DEFAULT {CS_CLV_WINDOW_STEP};\n",
        "  DECLARE WINDOW_STEP_INITIAL INT64 DEFAULT {CS_CLV_WINDOW_STEP_INITIAL};\n",
        "  DECLARE LENGTH_FUTURE INT64 DEFAULT {CS_CLV_LENGTH_FUTURE};\n",
        "  BEGIN \n",
        "      CALL `{CS_BQ_DATASET_NAME}.{CS_LTV_SOL_DS_ML_FEATURE_PREP_SP}`(WINDOW_LENGTH, WINDOW_STEP, WINDOW_STEP_INITIAL, LENGTH_FUTURE);\n",
        "  EXCEPTION WHEN ERROR THEN\n",
        "    SELECT\n",
        "      @@error.message,\n",
        "      @@error.stack_trace,\n",
        "      @@error.statement_text,\n",
        "      @@error.formatted_stack_trace;\n",
        "  END\n",
        "\"\"\"\n",
        "print (cs_ltv_sol_ds_invoke_sp_populate_feature_tbl)\n",
        "execute_sql(cs_ltv_sol_ds_invoke_sp_populate_feature_tbl)"
      ],
      "execution_count": 26,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "\n",
            "  DECLARE WINDOW_LENGTH INT64 DEFAULT 0;\n",
            "  DECLARE WINDOW_STEP INT64 DEFAULT 30;\n",
            "  DECLARE WINDOW_STEP_INITIAL INT64 DEFAULT 90;\n",
            "  DECLARE LENGTH_FUTURE INT64 DEFAULT 30;\n",
            "  BEGIN \n",
            "      CALL `cdp_ws_cs.ltv_sol_ml_prep_sp`(WINDOW_LENGTH, WINDOW_STEP, WINDOW_STEP_INITIAL, LENGTH_FUTURE);\n",
            "  EXCEPTION WHEN ERROR THEN\n",
            "    SELECT\n",
            "      @@error.message,\n",
            "      @@error.stack_trace,\n",
            "      @@error.statement_text,\n",
            "      @@error.formatted_stack_trace;\n",
            "  END\n",
            "\n",
            "Querty executed.\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "J3YBPGjoTtcW"
      },
      "source": [
        "## Create the clv model (DDL)"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "1p4b45NlTyLm",
        "outputId": "5ea5a24a-01c9-499b-8045-10aa2bc36f2d"
      },
      "source": [
        "# create the clv model\n",
        "# takes ~1 hour and 30 mins\n",
        "cs_ltv_sol_ds_create_clv_model = f\"\"\"\n",
        "  CREATE OR REPLACE MODEL `{CS_BQ_DATASET_NAME}.{CS_LTV_SOL_DS_ML_MODEL_NAME}`\n",
        "        OPTIONS(MODEL_TYPE=\"AUTOML_REGRESSOR\",\n",
        "                INPUT_LABEL_COLS=[\"target_monetary\"],\n",
        "                OPTIMIZATION_OBJECTIVE=\"MINIMIZE_MAE\")\n",
        "  AS SELECT\n",
        "    * EXCEPT(customer_id)\n",
        "  FROM\n",
        "    `{CS_BQ_DATASET_NAME}.{CS_LTV_SOL_DS_FEATURE_TBL}`\n",
        "\"\"\"\n",
        "print (cs_ltv_sol_ds_create_clv_model)\n",
        "execute_sql(cs_ltv_sol_ds_create_clv_model)"
      ],
      "execution_count": 27,
      "outputs": [
        {
          "metadata": {
            "tags": null
          },
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "\n",
            "  CREATE OR REPLACE MODEL `cdp_ws_cs.ltv_sol_clv_ml_model`\n",
            "        OPTIONS(MODEL_TYPE=\"AUTOML_REGRESSOR\",\n",
            "                INPUT_LABEL_COLS=[\"target_monetary\"],\n",
            "                OPTIMIZATION_OBJECTIVE=\"MINIMIZE_MAE\")\n",
            "  AS SELECT\n",
            "    * EXCEPT(customer_id)\n",
            "  FROM\n",
            "    `cdp_ws_cs.ltv_sol_features`\n",
            "\n",
            "Querty executed.\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "cwoi3H4jUyog"
      },
      "source": [
        "## Predict Customer ltv (DDL + DML)"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Ym_-wAxzU1LC",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "f59e3916-b8b7-4a56-f077-cec1ec818226"
      },
      "source": [
        "# create a stored proc to populate the Features table\n",
        "cs_create_sp_populate_predict_tbl = f\"\"\"\n",
        "CREATE OR  REPLACE PROCEDURE `{CS_BQ_DATASET_NAME}.{CS_LTV_SOL_DS_ML_PREDICT_UTIL_SP}`(\n",
        "  WINDOW_LENGTH INT64\n",
        ")\n",
        "BEGIN\n",
        "  -- How many days back for inputs transactions. 0 means from the start.\n",
        "  -- DECLARE WINDOW_LENGTH INT64 DEFAULT WINDOW_LENGTH;\n",
        "  -- Date at which an input transactions window starts.\n",
        "  DECLARE WINDOW_START DATE;\n",
        "\n",
        "  -- Date of the first transaction in the dataset.\n",
        "  DECLARE MIN_DATE DATE;\n",
        "  -- Date of the final transaction in the dataset.\n",
        "  DECLARE MAX_DATE DATE;\n",
        "  -- Date from which you want to predict.\n",
        "  DECLARE PREDICT_FROM_DATE DATE;\n",
        "\n",
        "  SET (MIN_DATE, MAX_DATE) = (\n",
        "    SELECT AS STRUCT\n",
        "      MIN(order_day) AS min_days,\n",
        "      MAX(order_day) AS max_days\n",
        "    FROM\n",
        "      `{CS_BQ_DATASET_NAME}.{CS_LTV_SOL_DS_CLV_AGGREGATE_TBL}`\n",
        "  );\n",
        "\n",
        "  -- You can set any date here. In production, it is generally today.\n",
        "  SET PREDICT_FROM_DATE = MAX_DATE;\n",
        "  IF WINDOW_LENGTH != 0 THEN\n",
        "    SET WINDOW_START = DATE_SUB(PREDICT_FROM_DATE, INTERVAL WINDOW_LENGTH DAY);\n",
        "  ELSE\n",
        "    SET WINDOW_START = MIN_DATE;\n",
        "  END IF;\n",
        "\n",
        "  CREATE OR REPLACE TABLE `{CS_BQ_DATASET_NAME}.{CS_LTV_SOL_DS_ML_PREDICT_TBL}`\n",
        "  AS (\n",
        "  SELECT\n",
        "    cust_tbl.customer_id,\n",
        "    cust_tbl.full_name,\n",
        "    cust_tbl.job_title,\n",
        "    cust_tbl.email,\n",
        "    monetary AS monetary_so_far,\n",
        "    ROUND(predicted_target_monetary, 2) AS monetary_predicted,\n",
        "    ROUND(predicted_target_monetary - monetary, 2) AS monetary_future\n",
        "  FROM\n",
        "    ML.PREDICT(\n",
        "      -- To use your own model, set the model name here.\n",
        "      MODEL `{CS_BQ_DATASET_NAME}.{CS_LTV_SOL_DS_ML_MODEL_NAME}`,\n",
        "      (\n",
        "        SELECT\n",
        "          agg_tmp_tbl.customer_id,\n",
        "          ROUND(monetary_orders, 2) AS monetary,\n",
        "          cnt_orders AS frequency,\n",
        "          recency,\n",
        "          T,\n",
        "          ROUND(recency/cnt_orders, 2) AS time_between,\n",
        "          ROUND(avg_basket_value, 2) AS avg_basket_value,\n",
        "          ROUND(avg_basket_size, 2) AS avg_basket_size,\n",
        "          has_returns,\n",
        "          CEIL(avg_time_to_return) AS avg_time_to_return,\n",
        "          num_returns\n",
        "        FROM (\n",
        "          SELECT\n",
        "            agg_tbl.customer_id,\n",
        "            SUM(value) AS monetary_orders,\n",
        "            DATE_DIFF(MAX(order_day), MIN(order_day), DAY) AS recency,\n",
        "            DATE_DIFF(PREDICT_FROM_DATE, MIN(order_day), DAY) AS T,\n",
        "            COUNT(DISTINCT order_day) AS cnt_orders,\n",
        "            AVG(qty_articles) avg_basket_size,\n",
        "            AVG(value) avg_basket_value,\n",
        "            CASE\n",
        "              WHEN SUM(num_returns) > 0 THEN 'y'\n",
        "              ELSE 'n'\n",
        "            END AS has_returns,\n",
        "            AVG(time_to_return) avg_time_to_return,\n",
        "            SUM(num_returns) num_returns,\n",
        "          FROM\n",
        "            `{CS_BQ_DATASET_NAME}.{CS_LTV_SOL_DS_CLV_AGGREGATE_TBL}` agg_tbl\n",
        "          WHERE\n",
        "            order_day <= PREDICT_FROM_DATE AND\n",
        "            order_day >= WINDOW_START\n",
        "          GROUP BY\n",
        "            agg_tbl.customer_id\n",
        "        ) agg_tmp_tbl\n",
        "      )\n",
        "    ) pred_temp,\n",
        "    `{CS_BQ_DATASET_NAME}.{CS_LTV_SOL_DS_INPUT_CUST_TBL}` cust_tbl\n",
        "    where \n",
        "    pred_temp.customer_id = cust_tbl.customer_id\n",
        "  );\n",
        "END\n",
        "\"\"\"\n",
        "print (cs_create_sp_populate_predict_tbl)\n",
        "execute_sql(cs_create_sp_populate_predict_tbl)"
      ],
      "execution_count": 28,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "\n",
            "CREATE OR  REPLACE PROCEDURE `cdp_ws_cs.ltv_sol_clv_ml_predict_util_sp`(\n",
            "  WINDOW_LENGTH INT64\n",
            ")\n",
            "BEGIN\n",
            "  -- How many days back for inputs transactions. 0 means from the start.\n",
            "  -- DECLARE WINDOW_LENGTH INT64 DEFAULT WINDOW_LENGTH;\n",
            "  -- Date at which an input transactions window starts.\n",
            "  DECLARE WINDOW_START DATE;\n",
            "\n",
            "  -- Date of the first transaction in the dataset.\n",
            "  DECLARE MIN_DATE DATE;\n",
            "  -- Date of the final transaction in the dataset.\n",
            "  DECLARE MAX_DATE DATE;\n",
            "  -- Date from which you want to predict.\n",
            "  DECLARE PREDICT_FROM_DATE DATE;\n",
            "\n",
            "  SET (MIN_DATE, MAX_DATE) = (\n",
            "    SELECT AS STRUCT\n",
            "      MIN(order_day) AS min_days,\n",
            "      MAX(order_day) AS max_days\n",
            "    FROM\n",
            "      `cdp_ws_cs.ltv_sol_clv_aggregation`\n",
            "  );\n",
            "\n",
            "  -- You can set any date here. In production, it is generally today.\n",
            "  SET PREDICT_FROM_DATE = MAX_DATE;\n",
            "  IF WINDOW_LENGTH != 0 THEN\n",
            "    SET WINDOW_START = DATE_SUB(PREDICT_FROM_DATE, INTERVAL WINDOW_LENGTH DAY);\n",
            "  ELSE\n",
            "    SET WINDOW_START = MIN_DATE;\n",
            "  END IF;\n",
            "\n",
            "  CREATE OR REPLACE TABLE `cdp_ws_cs.ltv_sol_clv_predict_tbl`\n",
            "  AS (\n",
            "  SELECT\n",
            "    cust_tbl.customer_id,\n",
            "    cust_tbl.full_name,\n",
            "    cust_tbl.job_title,\n",
            "    cust_tbl.email,\n",
            "    monetary AS monetary_so_far,\n",
            "    ROUND(predicted_target_monetary, 2) AS monetary_predicted,\n",
            "    ROUND(predicted_target_monetary - monetary, 2) AS monetary_future\n",
            "  FROM\n",
            "    ML.PREDICT(\n",
            "      -- To use your own model, set the model name here.\n",
            "      MODEL `cdp_ws_cs.ltv_sol_clv_ml_model`,\n",
            "      (\n",
            "        SELECT\n",
            "          agg_tmp_tbl.customer_id,\n",
            "          ROUND(monetary_orders, 2) AS monetary,\n",
            "          cnt_orders AS frequency,\n",
            "          recency,\n",
            "          T,\n",
            "          ROUND(recency/cnt_orders, 2) AS time_between,\n",
            "          ROUND(avg_basket_value, 2) AS avg_basket_value,\n",
            "          ROUND(avg_basket_size, 2) AS avg_basket_size,\n",
            "          has_returns,\n",
            "          CEIL(avg_time_to_return) AS avg_time_to_return,\n",
            "          num_returns\n",
            "        FROM (\n",
            "          SELECT\n",
            "            agg_tbl.customer_id,\n",
            "            SUM(value) AS monetary_orders,\n",
            "            DATE_DIFF(MAX(order_day), MIN(order_day), DAY) AS recency,\n",
            "            DATE_DIFF(PREDICT_FROM_DATE, MIN(order_day), DAY) AS T,\n",
            "            COUNT(DISTINCT order_day) AS cnt_orders,\n",
            "            AVG(qty_articles) avg_basket_size,\n",
            "            AVG(value) avg_basket_value,\n",
            "            CASE\n",
            "              WHEN SUM(num_returns) > 0 THEN 'y'\n",
            "              ELSE 'n'\n",
            "            END AS has_returns,\n",
            "            AVG(time_to_return) avg_time_to_return,\n",
            "            SUM(num_returns) num_returns,\n",
            "          FROM\n",
            "            `cdp_ws_cs.ltv_sol_clv_aggregation` agg_tbl\n",
            "          WHERE\n",
            "            order_day <= PREDICT_FROM_DATE AND\n",
            "            order_day >= WINDOW_START\n",
            "          GROUP BY\n",
            "            agg_tbl.customer_id\n",
            "        ) agg_tmp_tbl\n",
            "      )\n",
            "    ) pred_temp,\n",
            "    `cdp_ws_cs.ltv_sol_input_customer` cust_tbl\n",
            "    where \n",
            "    pred_temp.customer_id = cust_tbl.customer_id\n",
            "  );\n",
            "END\n",
            "\n",
            "Querty executed.\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "eAVixKXGVdQo",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "c2425df9-3019-467c-90c4-a9ea7dcd42dd"
      },
      "source": [
        "# Create and predict the clv of customers\n",
        "# with the current dataset it takes ~1.5 minute to complete the execution\n",
        "cs_ltv_sol_ds_invoke_sp_populate_predict_tbl = f\"\"\"\n",
        "DECLARE WINDOW_LENGTH INT64 DEFAULT {CS_CLV_WINDOW_LENGTH};\n",
        "BEGIN\n",
        "    CALL `{CS_BQ_DATASET_NAME}.{CS_LTV_SOL_DS_ML_PREDICT_UTIL_SP}`(WINDOW_LENGTH);\n",
        "    EXCEPTION WHEN ERROR THEN\n",
        "    SELECT\n",
        "      @@error.message,\n",
        "      @@error.stack_trace,\n",
        "      @@error.statement_text,\n",
        "      @@error.formatted_stack_trace;\n",
        "END\n",
        "\"\"\"\n",
        "print (cs_ltv_sol_ds_invoke_sp_populate_predict_tbl)\n",
        "execute_sql(cs_ltv_sol_ds_invoke_sp_populate_predict_tbl)"
      ],
      "execution_count": 29,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "\n",
            "DECLARE WINDOW_LENGTH INT64 DEFAULT 0;\n",
            "BEGIN\n",
            "    CALL `cdp_ws_cs.ltv_sol_clv_ml_predict_util_sp`(WINDOW_LENGTH);\n",
            "    EXCEPTION WHEN ERROR THEN\n",
            "    SELECT\n",
            "      @@error.message,\n",
            "      @@error.stack_trace,\n",
            "      @@error.statement_text,\n",
            "      @@error.formatted_stack_trace;\n",
            "END\n",
            "\n",
            "Querty executed.\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 300
        },
        "id": "Plh5L_Zu8xYz",
        "outputId": "32cf2027-2a62-4c91-b676-5ab88fea7312"
      },
      "source": [
        "# adjust the below query to grab only a sample dataset e.g. use a where clause.\n",
        "df = bq_client.query('''\n",
        "  SELECT *\n",
        "  FROM `%s.%s`\n",
        "''' % (CS_BQ_DATASET_NAME,CS_LTV_SOL_DS_ML_PREDICT_TBL)).to_dataframe()\n",
        "df.describe()"
      ],
      "execution_count": 30,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "\n",
              "  <div id=\"df-95f73828-874d-405d-a012-419d15b97c3f\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>customer_id</th>\n",
              "      <th>monetary_so_far</th>\n",
              "      <th>monetary_predicted</th>\n",
              "      <th>monetary_future</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>count</th>\n",
              "      <td>99070.000000</td>\n",
              "      <td>99070.000000</td>\n",
              "      <td>99070.000000</td>\n",
              "      <td>99070.000000</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>mean</th>\n",
              "      <td>50008.361563</td>\n",
              "      <td>1524.427797</td>\n",
              "      <td>1525.180655</td>\n",
              "      <td>0.752854</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>std</th>\n",
              "      <td>28862.604042</td>\n",
              "      <td>1158.682822</td>\n",
              "      <td>1158.815462</td>\n",
              "      <td>0.576954</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>min</th>\n",
              "      <td>1.000000</td>\n",
              "      <td>0.000000</td>\n",
              "      <td>0.870000</td>\n",
              "      <td>-23.760000</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>25%</th>\n",
              "      <td>25021.250000</td>\n",
              "      <td>652.230000</td>\n",
              "      <td>652.722500</td>\n",
              "      <td>0.420000</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>50%</th>\n",
              "      <td>50010.500000</td>\n",
              "      <td>1279.300000</td>\n",
              "      <td>1279.895000</td>\n",
              "      <td>0.680000</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>75%</th>\n",
              "      <td>74992.500000</td>\n",
              "      <td>2131.797500</td>\n",
              "      <td>2132.495000</td>\n",
              "      <td>1.030000</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>max</th>\n",
              "      <td>99999.000000</td>\n",
              "      <td>31088.880000</td>\n",
              "      <td>31065.120000</td>\n",
              "      <td>10.580000</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-95f73828-874d-405d-a012-419d15b97c3f')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-95f73828-874d-405d-a012-419d15b97c3f button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-95f73828-874d-405d-a012-419d15b97c3f');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLink = '<div>Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.</div>';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          element.innerHTML += docLink;\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ],
            "text/plain": [
              "        customer_id  monetary_so_far  monetary_predicted  monetary_future\n",
              "count  99070.000000     99070.000000        99070.000000     99070.000000\n",
              "mean   50008.361563      1524.427797         1525.180655         0.752854\n",
              "std    28862.604042      1158.682822         1158.815462         0.576954\n",
              "min        1.000000         0.000000            0.870000       -23.760000\n",
              "25%    25021.250000       652.230000          652.722500         0.420000\n",
              "50%    50010.500000      1279.300000         1279.895000         0.680000\n",
              "75%    74992.500000      2131.797500         2132.495000         1.030000\n",
              "max    99999.000000     31088.880000        31065.120000        10.580000"
            ]
          },
          "metadata": {},
          "execution_count": 30
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "# Analyze phase"
      ],
      "metadata": {
        "id": "2F_3TJ3WZjQo"
      }
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "6YyY9Lu4qcnX"
      },
      "source": [
        "## Display customers list with their ltv"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 363
        },
        "id": "Qq8FE9HCpjfE",
        "outputId": "39e278d6-9172-4297-94d2-d0559579172e"
      },
      "source": [
        "# adjust the below query to grab only a sample dataset e.g. use a where clause.\n",
        "df = bq_client.query('''\n",
        "  SELECT *\n",
        "  FROM `%s.%s`\n",
        "  ORDER BY customer_id\n",
        "  # ORDER BY monetary_predicted desc\n",
        "  LIMIT 10\n",
        "''' % (CS_BQ_DATASET_NAME,CS_LTV_SOL_DS_ML_PREDICT_TBL)).to_dataframe()\n",
        "drop_columns = ['monetary_so_far', 'monetary_future']\n",
        "df.drop(drop_columns, axis=1, inplace=True)\n",
        "df"
      ],
      "execution_count": 31,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "\n",
              "  <div id=\"df-023e6e86-f8bd-4ccf-8cab-c6d9e3d4cc74\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>customer_id</th>\n",
              "      <th>full_name</th>\n",
              "      <th>job_title</th>\n",
              "      <th>email</th>\n",
              "      <th>monetary_predicted</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>1</td>\n",
              "      <td>Eric Hilton</td>\n",
              "      <td>Product Manager</td>\n",
              "      <td>jkocher0@163-example.com</td>\n",
              "      <td>415.03</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>2</td>\n",
              "      <td>Fritz S Hillis</td>\n",
              "      <td>Product Manager</td>\n",
              "      <td>lvandermark1@sciencedirect-example.com</td>\n",
              "      <td>1496.64</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>3</td>\n",
              "      <td>Joann Merrill</td>\n",
              "      <td>CEO</td>\n",
              "      <td>enorthwood2@biglobe.ne-example.jp</td>\n",
              "      <td>1122.01</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>4</td>\n",
              "      <td>Elvin Rochon</td>\n",
              "      <td>Software Engineer</td>\n",
              "      <td>eoller3@comcast-example.net</td>\n",
              "      <td>293.92</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>5</td>\n",
              "      <td>Rebecca V Larson</td>\n",
              "      <td>CIO</td>\n",
              "      <td>srippen4@wunderground-example.com</td>\n",
              "      <td>3850.99</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>6</td>\n",
              "      <td>Siobhan Riley</td>\n",
              "      <td>Vice President</td>\n",
              "      <td>ydouberday5@admin-example.ch</td>\n",
              "      <td>1031.98</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6</th>\n",
              "      <td>7</td>\n",
              "      <td>Erica Z Coffey</td>\n",
              "      <td>Software Engineer</td>\n",
              "      <td>meddisforth6@jalbum-example.net</td>\n",
              "      <td>1720.93</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>7</th>\n",
              "      <td>8</td>\n",
              "      <td>Melissia P Cespedes</td>\n",
              "      <td>Software Engineer</td>\n",
              "      <td>alockie7@cafepress-example.com</td>\n",
              "      <td>1544.95</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>8</th>\n",
              "      <td>9</td>\n",
              "      <td>Ida K Rodriquez</td>\n",
              "      <td>Software Engineer</td>\n",
              "      <td>fharbidge8@sitemeter-example.com</td>\n",
              "      <td>759.28</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>9</th>\n",
              "      <td>10</td>\n",
              "      <td>Tena F Bloomberg</td>\n",
              "      <td>CIO</td>\n",
              "      <td>fabrahamovitz9@tiny-example.cc</td>\n",
              "      <td>5696.04</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-023e6e86-f8bd-4ccf-8cab-c6d9e3d4cc74')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-023e6e86-f8bd-4ccf-8cab-c6d9e3d4cc74 button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-023e6e86-f8bd-4ccf-8cab-c6d9e3d4cc74');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLink = '<div>Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.</div>';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          element.innerHTML += docLink;\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ],
            "text/plain": [
              "   customer_id  ... monetary_predicted\n",
              "0            1  ...             415.03\n",
              "1            2  ...            1496.64\n",
              "2            3  ...            1122.01\n",
              "3            4  ...             293.92\n",
              "4            5  ...            3850.99\n",
              "5            6  ...            1031.98\n",
              "6            7  ...            1720.93\n",
              "7            8  ...            1544.95\n",
              "8            9  ...             759.28\n",
              "9           10  ...            5696.04\n",
              "\n",
              "[10 rows x 5 columns]"
            ]
          },
          "metadata": {},
          "execution_count": 31
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 363
        },
        "id": "lOEO48J6-W-M",
        "outputId": "4f8961ae-a47f-4d0c-be27-9ee6d7eed49c"
      },
      "source": [
        "# adjust the below query to grab only a sample dataset e.g. use a where clause.\n",
        "df = bq_client.query('''\n",
        "  SELECT *\n",
        "  FROM `%s.%s`\n",
        "  # ORDER BY id\n",
        "  ORDER BY monetary_predicted desc\n",
        "  LIMIT 10\n",
        "''' % (CS_BQ_DATASET_NAME,CS_LTV_SOL_DS_ML_PREDICT_TBL)).to_dataframe()\n",
        "drop_columns = ['monetary_so_far', 'monetary_future']\n",
        "df.drop(drop_columns, axis=1, inplace=True)\n",
        "df"
      ],
      "execution_count": 33,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "\n",
              "  <div id=\"df-02ffab11-1a9b-4b87-8bc5-575958b4ee12\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>customer_id</th>\n",
              "      <th>full_name</th>\n",
              "      <th>job_title</th>\n",
              "      <th>email</th>\n",
              "      <th>monetary_predicted</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>39030</td>\n",
              "      <td>Crystle N Hake</td>\n",
              "      <td>Vice President</td>\n",
              "      <td>rkalfu45@myspace-example.com</td>\n",
              "      <td>31065.12</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>23553</td>\n",
              "      <td>Shannon S Ashley</td>\n",
              "      <td>CIO</td>\n",
              "      <td>nhavocki68@360-example.cn</td>\n",
              "      <td>27738.73</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>28722</td>\n",
              "      <td>Melvin Strand</td>\n",
              "      <td>Product Manager</td>\n",
              "      <td>dlafflinam5t@nih-example.gov</td>\n",
              "      <td>20986.28</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>15918</td>\n",
              "      <td>Delmar Motley</td>\n",
              "      <td>CIO</td>\n",
              "      <td>mchantca5@ox.ac-example.uk</td>\n",
              "      <td>20073.68</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>35073</td>\n",
              "      <td>Judith Potter</td>\n",
              "      <td>Software Engineer</td>\n",
              "      <td>rbellshamr28@surveymonkey-example.com</td>\n",
              "      <td>19320.57</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>14480</td>\n",
              "      <td>Casey W Burress</td>\n",
              "      <td>CEO</td>\n",
              "      <td>hrameauxb67@mozilla-example.com</td>\n",
              "      <td>17465.78</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6</th>\n",
              "      <td>99422</td>\n",
              "      <td>Orval X Hutton</td>\n",
              "      <td>CEO</td>\n",
              "      <td>oriseley24pp@independent.co-example.uk</td>\n",
              "      <td>16931.41</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>7</th>\n",
              "      <td>54786</td>\n",
              "      <td>Rosemary Zacharias</td>\n",
              "      <td>Product Manager</td>\n",
              "      <td>hduling169t@bbb-example.org</td>\n",
              "      <td>15924.72</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>8</th>\n",
              "      <td>38732</td>\n",
              "      <td>Lesley N Trussell</td>\n",
              "      <td>Vice President</td>\n",
              "      <td>jholdintvv@opera-example.com</td>\n",
              "      <td>14387.30</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>9</th>\n",
              "      <td>61895</td>\n",
              "      <td>Amanda B Holland</td>\n",
              "      <td>CIO</td>\n",
              "      <td>fpirolini1bra@ustream-example.tv</td>\n",
              "      <td>13193.63</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-02ffab11-1a9b-4b87-8bc5-575958b4ee12')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-02ffab11-1a9b-4b87-8bc5-575958b4ee12 button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-02ffab11-1a9b-4b87-8bc5-575958b4ee12');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLink = '<div>Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.</div>';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          element.innerHTML += docLink;\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ],
            "text/plain": [
              "   customer_id  ... monetary_predicted\n",
              "0        39030  ...           31065.12\n",
              "1        23553  ...           27738.73\n",
              "2        28722  ...           20986.28\n",
              "3        15918  ...           20073.68\n",
              "4        35073  ...           19320.57\n",
              "5        14480  ...           17465.78\n",
              "6        99422  ...           16931.41\n",
              "7        54786  ...           15924.72\n",
              "8        38732  ...           14387.30\n",
              "9        61895  ...           13193.63\n",
              "\n",
              "[10 rows x 5 columns]"
            ]
          },
          "metadata": {},
          "execution_count": 33
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "# Google Cloud Resource Clean up"
      ],
      "metadata": {
        "id": "sGugfcw9ZpWZ"
      }
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "yTDK-KwN0R8z"
      },
      "source": [
        "## Delete the BigQuery Dataset"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "Arht3q6d0VLV",
        "outputId": "892d52d8-4e8e-4d3c-ff7f-f25b2fb33578"
      },
      "source": [
        "# deletes the dataset\n",
        "# delete_dataset(CS_BQ_DATASET_NAME)"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "Deleted dataset 'cdp_cs'.\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "z-BEHJU50MF2"
      },
      "source": [
        "## Delete the Google Cloud Project\n",
        "To avoid incurring charges to your Google Cloud Platform account for the resources used in this tutorial is to **Delete the project**.\n",
        "\n",
        "The easiest way to eliminate billing is to delete the project you created for the tutorial.\n",
        "\n",
        "**Caution**: Deleting a project has the following effects:\n",
        "* *Everything in the project is deleted.* If you used an existing project for this tutorial, when you delete it, you also delete any other work you've done in the project.\n",
        "* <b>Custom project IDs are lost. </b>When you created this project, you might have created a custom project ID that you want to use in the future. To preserve the URLs that use the project ID, such as an appspot.com</b> URL, delete selected resources inside the project instead of deleting the whole project. \n",
        "\n",
        "If you plan to explore multiple tutorials and quickstarts, reusing projects can help you avoid exceeding project quota limits.\n",
        "<br>\n",
        "<ol type=\"1\">\n",
        "    <li>In the Cloud Console, go to the <b>Manage resources</b> page.</li>\n",
        "    Go to the <a href=\"https://console.cloud.google.com/iam-admin/projects\">Manage resources page</a>\n",
        "    <li>In the project list, select the project that you want to delete and then click <b>Delete</b> Trash icon.</li>\n",
        "    <li>In the dialog, type the project ID and then click <b>Shut down</b> to delete the project. </li>\n",
        "</ol>\n"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "HmtalObFfgIW"
      },
      "source": [
        ""
      ],
      "execution_count": null,
      "outputs": []
    }
  ]
}