retail/clustering/bqml/bqml_scaled_clustering.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "ur8xi4C7S06n"
   },
   "outputs": [],
   "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."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "eHLV0D7Y5jtU"
   },
   "source": [
    "<table align=\"left\">\n",
    "  <td>\n",
    "    <a href=\"https://github.com/GoogleCloudPlatform/analytics-componentized-patterns/blob/master/retail/clustering/bqml/bqml_scaled_clustering.ipynb\">\n",
    "      <img src=\"https://cloud.google.com/ml-engine/images/github-logo-32px.png\" alt=\"GitHub logo\">\n",
    "      View on GitHub\n",
    "    </a>\n",
    "  </td>\n",
    "</table>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "tvgnzT1CKxrO"
   },
   "source": [
    "# How to build k-means clustering models for market segmentation using BigQuery ML\n",
    "\n",
    "A common marketing analytics challenge is to understand consumer behavior and develop customer attributes or archetypes.  As organizations get better at tackling this problem, they can activate marketing strategies to incorporate additional customer knowledge into their campaigns.  \n",
    "\n",
    "Clustering algorithms are a common vehicle to address this challenge. They allow businesses to better segment and understand their customers and users.  In the field of Machine Learning, which is a combination of both art and science, unsupervised learning may require more art compared to supervised learning algorithms.  By definition, unsupervised learning has no single metric to guide the algorithm's learning process. Instead, the data science team will need to work hand in hand with business owners to determine feature selection, optimal number of clusters (the number of clusters is often abbreviated as k), and most importantly, to gain a deeper understanding of what each cluster represents.    \n",
    "\n",
    "### How can clustering algorithms help businesses succeed?\n",
    "\n",
    "Clustering algorithms can help companies identify groups of similar customers that can be used for targeting in advertising campaigns. This is paramount as we are breathing a prediction era where customers expect personalization from brands. \n",
    " \n",
    "Using a public sample Google Analytics 360 e-commerce dataset on BigQuery, you will learn how to create and deploy clustering algorithms in production. You will also get an example of how to navigate unsupervised learning.  Keep in mind, your clusters will be even more meaningful when you bring additional data.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "6XbaFW4SQxGD"
   },
   "source": [
    "# Objective\n",
    "\n",
    "By the end of this notebook, you will know how to:\n",
    "* Explore features to understand what might be interesting for a clustering model\n",
    "* Pre-process data into the correct format needed to create a clustering model using BigQuery ML\n",
    "* Train (and deploy) the k-means model in BigQuery ML\n",
    "* Evaluate the model\n",
    "* Make predictions using the model\n",
    "* Write the results to be used for batch prediction, for example, to send ads based on segmentation\n",
    "\n",
    "## Dataset\n",
    "\n",
    "The [Google Analytics Sample](https://console.cloud.google.com/marketplace/details/obfuscated-ga360-data/obfuscated-ga360-data?filter=solution-type:dataset) dataset, which is hosted publicly on BigQuery, is a dataset that provides 12 months (August 2016 to August 2017) of obfuscated Google Analytics 360 data from the [Google Merchandise Store](https://www.googlemerchandisestore.com/), a real e-commerce store that sells Google-branded merchandise.\n",
    "\n",
    "\n",
    "## Costs \n",
    "\n",
    "This tutorial uses billable components of Google Cloud Platform:\n",
    "\n",
    "* BigQuery\n",
    "* BigQuery ML\n",
    "\n",
    "Learn about [BigQuery pricing](https://cloud.google.com/bigquery/pricing), [BigQuery ML\n",
    "pricing](https://cloud.google.com/bigquery-ml/pricing) and use the [Pricing\n",
    "Calculator](https://cloud.google.com/products/calculator/)\n",
    "to generate a cost estimate based on your projected usage."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "i7EUnXsZhAGF"
   },
   "source": [
    "## PIP install packages and dependencies"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "!pip install google-cloud-bigquery\n",
    "!pip install google-cloud-bigquery-storage\n",
    "!pip install pandas-gbq\n",
    "\n",
    "# Reservation package needed to setup flex slots for flat-rate pricing\n",
    "!pip install google-cloud-bigquery-reservation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Automatically restart kernel after installs\n",
    "import IPython\n",
    "app = IPython.Application.instance()\n",
    "app.kernel.do_shutdown(True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "BF1j6f9HApxa"
   },
   "source": [
    "### Set up your Google Cloud Platform project\n",
    "\n",
    "_The following steps are required, regardless of your notebook environment._\n",
    "\n",
    "1. [Select or create a project](https://console.cloud.google.com/cloud-resource-manager). When you first create an account, you get a $300 free credit towards your compute/storage costs.\n",
    "\n",
    "1. [Make sure that billing is enabled for your project.](https://cloud.google.com/billing/docs/how-to/modify-project)\n",
    "\n",
    "1. [Enable the AI Platform APIs and Compute Engine APIs.](https://console.cloud.google.com/flows/enableapi?apiid=ml.googleapis.com,compute_component)\n",
    "\n",
    "1. Enter your project ID and region in the cell below. Then run the  cell to make sure the\n",
    "Cloud SDK uses the right project for all the commands in this notebook.\n",
    "\n",
    "_Note_: Jupyter runs lines prefixed with `!` as shell commands, and it interpolates Python variables prefixed with `$` into these commands."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "W8SLpZgiyMSP"
   },
   "source": [
    "### Set project ID and authenticate\n",
    "\n",
    "Update your Project ID below.  The rest of the notebook will run using these credentials. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "PROJECT_ID = \"UPDATE TO YOUR PROJECT ID\" \n",
    "REGION = 'US'\n",
    "DATA_SET_ID = 'bqml_kmeans' # Ensure you first create a data set in BigQuery\n",
    "!gcloud config set project $PROJECT_ID\n",
    "# If you have not built the Data Set, the following command will build it for you\n",
    "# !bq mk --location=$REGION --dataset $PROJECT_ID:$DATA_SET_ID "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "XoEqT2Y4DJmf"
   },
   "source": [
    "### Import libraries and define constants"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 392
    },
    "colab_type": "code",
    "id": "bbnrwv-nyi82",
    "outputId": "d9b05979-e17b-411a-d910-8c91e8755501"
   },
   "outputs": [],
   "source": [
    "from google.cloud import bigquery\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import pandas_gbq\n",
    "import matplotlib.pyplot as plt\n",
    "\n",
    "pd.set_option('display.float_format', lambda x: '%.3f' % x) # used to display float format\n",
    "client = bigquery.Client(project=PROJECT_ID)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "Ks4xTsQJk1Ia"
   },
   "source": [
    "# Data exploration and preparation\n",
    "\n",
    "Prior to building your models, you are typically expected to invest a significant amount of time cleaning, exploring, and aggregating your dataset in a meaningful way for modeling.  For the purpose of this demo, we aren't showing this step only to prioritize showcasing clustering with k-means in BigQuery ML.  "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "z3NG3XuNYKnO"
   },
   "source": [
    "## Building synthetic data\n",
    "\n",
    "Our goal is to use both online (GA360) and offline (CRM) data.  You can use your own CRM data, however, in this case since we don't have CRM data to showcase, we will instead generate synthetic data.  We will generate estimated House Hold Income, and Gender.  To do so, we will hash fullVisitorID and build simple rules based on the last digit of the hash. When you run this process with your own data, you can join CRM data with several dimensions, but this is just an example of what is possible.  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# We start with GA360 data, and will eventually build synthetic CRM as an example.  \n",
    "# This block is the first step, just working with GA360\n",
    "\n",
    "ga360_only_view = 'GA360_View'\n",
    "shared_dataset_ref = client.dataset(DATA_SET_ID)\n",
    "ga360_view_ref = shared_dataset_ref.table(ga360_only_view)\n",
    "ga360_view = bigquery.Table(ga360_view_ref)\n",
    "\n",
    "ga360_query = '''\n",
    "SELECT\n",
    "  fullVisitorID,\n",
    "  ABS(farm_fingerprint(fullVisitorID)) AS Hashed_fullVisitorID, # This will be used to generate random data.\n",
    "  MAX(device.operatingSystem) AS OS, # We can aggregate this because an OS is tied to a fullVisitorID.\n",
    "  SUM (CASE\n",
    "       WHEN REGEXP_EXTRACT (v2ProductCategory, \n",
    "                           r'^(?:(?:.*?)Home/)(.*?)/') \n",
    "                           = 'Apparel' THEN 1 ELSE 0 END) AS Apparel,\n",
    "  SUM (CASE  \n",
    "       WHEN REGEXP_EXTRACT (v2ProductCategory, \n",
    "                           r'^(?:(?:.*?)Home/)(.*?)/') \n",
    "                           = 'Office' THEN 1 ELSE 0 END) AS Office,\n",
    "  SUM (CASE\n",
    "       WHEN REGEXP_EXTRACT (v2ProductCategory, \n",
    "                           r'^(?:(?:.*?)Home/)(.*?)/') \n",
    "                           = 'Electronics' THEN 1 ELSE 0 END) AS Electronics,\n",
    "  SUM (CASE\n",
    "       WHEN REGEXP_EXTRACT (v2ProductCategory, \n",
    "                           r'^(?:(?:.*?)Home/)(.*?)/') \n",
    "                           = 'Limited Supply' THEN 1 ELSE 0 END) AS LimitedSupply,\n",
    "  SUM (CASE\n",
    "       WHEN REGEXP_EXTRACT (v2ProductCategory, \n",
    "                           r'^(?:(?:.*?)Home/)(.*?)/') \n",
    "                           = 'Accessories' THEN 1 ELSE 0 END) AS Accessories,\n",
    "  SUM (CASE\n",
    "       WHEN REGEXP_EXTRACT (v2ProductCategory, \n",
    "                           r'^(?:(?:.*?)Home/)(.*?)/') \n",
    "                           = 'Shop by Brand' THEN 1 ELSE 0 END) AS ShopByBrand,\n",
    "  SUM (CASE\n",
    "       WHEN REGEXP_EXTRACT (v2ProductCategory, \n",
    "                           r'^(?:(?:.*?)Home/)(.*?)/') \n",
    "                           = 'Bags' THEN 1 ELSE 0 END) AS Bags,\n",
    "  ROUND (SUM (productPrice/1000000),2) AS productPrice_USD\n",
    "FROM\n",
    "  `bigquery-public-data.google_analytics_sample.ga_sessions_*`,\n",
    "  UNNEST(hits) AS hits,\n",
    "  UNNEST(hits.product) AS hits_product\n",
    "WHERE\n",
    "  _TABLE_SUFFIX BETWEEN '20160801'\n",
    "  AND '20160831'\n",
    "  AND geoNetwork.country = 'United States'\n",
    "  AND type = 'EVENT'\n",
    "GROUP BY\n",
    "  1,\n",
    "  2\n",
    "'''\n",
    "\n",
    "\n",
    "ga360_view.view_query = ga360_query.format(PROJECT_ID)\n",
    "ga360_view = client.create_table(ga360_view)  # API request\n",
    "\n",
    "print(f\"Successfully created view at {ga360_view.full_table_id}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<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>fullVisitorID</th>\n",
       "      <th>Hashed_fullVisitorID</th>\n",
       "      <th>OS</th>\n",
       "      <th>Apparel</th>\n",
       "      <th>Office</th>\n",
       "      <th>Electronics</th>\n",
       "      <th>LimitedSupply</th>\n",
       "      <th>Accessories</th>\n",
       "      <th>ShopByBrand</th>\n",
       "      <th>Bags</th>\n",
       "      <th>productPrice_USD</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0793220752145578759</td>\n",
       "      <td>4074807331962730552</td>\n",
       "      <td>Linux</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>148.960</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>6626084732166116798</td>\n",
       "      <td>9209336555480734198</td>\n",
       "      <td>Windows</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>28.990</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>6402635554390648387</td>\n",
       "      <td>6330846949202373940</td>\n",
       "      <td>Windows</td>\n",
       "      <td>102</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2247.980</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1774577907793414721</td>\n",
       "      <td>6826645565243937471</td>\n",
       "      <td>Chrome OS</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>289.910</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>9875610913644487984</td>\n",
       "      <td>8099941684224314656</td>\n",
       "      <td>iOS</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>24.990</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         fullVisitorID  Hashed_fullVisitorID         OS  Apparel  Office  \\\n",
       "0  0793220752145578759   4074807331962730552      Linux        4       0   \n",
       "1  6626084732166116798   9209336555480734198    Windows        1       0   \n",
       "2  6402635554390648387   6330846949202373940    Windows      102       0   \n",
       "3  1774577907793414721   6826645565243937471  Chrome OS        7       0   \n",
       "4  9875610913644487984   8099941684224314656        iOS        0       0   \n",
       "\n",
       "   Electronics  LimitedSupply  Accessories  ShopByBrand  Bags  \\\n",
       "0            0              0            0            0     0   \n",
       "1            0              0            0            0     0   \n",
       "2            0              0            0            0     0   \n",
       "3            0              1            0            0     0   \n",
       "4            0              0            1            0     0   \n",
       "\n",
       "   productPrice_USD  \n",
       "0           148.960  \n",
       "1            28.990  \n",
       "2          2247.980  \n",
       "3           289.910  \n",
       "4            24.990  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Show a sample of GA360 data\n",
    "\n",
    "ga360_query_df = f'''\n",
    "SELECT * FROM {ga360_view.full_table_id.replace(\":\", \".\")} LIMIT 5\n",
    "'''\n",
    "\n",
    "job_config = bigquery.QueryJobConfig()\n",
    "\n",
    "# Start the query\n",
    "query_job = client.query(ga360_query_df, job_config=job_config) #API Request\n",
    "df_ga360 = query_job.result()\n",
    "df_ga360 = df_ga360.to_dataframe()\n",
    "\n",
    "df_ga360"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create synthetic CRM data in SQL\n",
    "\n",
    "CRM_only_view = 'CRM_View'\n",
    "shared_dataset_ref = client.dataset(DATA_SET_ID)\n",
    "CRM_view_ref = shared_dataset_ref.table(CRM_only_view)\n",
    "CRM_view = bigquery.Table(CRM_view_ref)\n",
    "\n",
    "# Query below works by hashing the fullVisitorID, which creates a random distribution.  \n",
    "# We use modulo to artificially split gender and hhi distribution.\n",
    "CRM_query = '''\n",
    "SELECT\n",
    "  fullVisitorID,\n",
    "IF\n",
    "  (MOD(Hashed_fullVisitorID,2) = 0,\n",
    "    \"M\",\n",
    "    \"F\") AS gender,\n",
    "  CASE\n",
    "    WHEN MOD(Hashed_fullVisitorID,10) = 0 THEN 55000\n",
    "    WHEN MOD(Hashed_fullVisitorID,10) < 3 THEN 65000\n",
    "    WHEN MOD(Hashed_fullVisitorID,10) < 7 THEN 75000\n",
    "    WHEN MOD(Hashed_fullVisitorID,10) < 9 THEN 85000\n",
    "    WHEN MOD(Hashed_fullVisitorID,10) = 9 THEN 95000\n",
    "  ELSE\n",
    "  Hashed_fullVisitorID\n",
    "END\n",
    "  AS hhi\n",
    "FROM (\n",
    "  SELECT\n",
    "    fullVisitorID,\n",
    "    ABS(farm_fingerprint(fullVisitorID)) AS Hashed_fullVisitorID,\n",
    "  FROM\n",
    "    `bigquery-public-data.google_analytics_sample.ga_sessions_*`,\n",
    "    UNNEST(hits) AS hits,\n",
    "    UNNEST(hits.product) AS hits_product\n",
    "  WHERE\n",
    "    _TABLE_SUFFIX BETWEEN '20160801'\n",
    "    AND '20160831'\n",
    "    AND geoNetwork.country = 'United States'\n",
    "    AND type = 'EVENT'\n",
    "  GROUP BY\n",
    "    1,\n",
    "    2)\n",
    "'''\n",
    "\n",
    "CRM_view.view_query = CRM_query.format(PROJECT_ID)\n",
    "CRM_view = client.create_table(CRM_view)  # API request\n",
    "\n",
    "print(f\"Successfully created view at {CRM_view.full_table_id}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<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>fullVisitorID</th>\n",
       "      <th>gender</th>\n",
       "      <th>hhi</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>297008845417084558</td>\n",
       "      <td>F</td>\n",
       "      <td>85000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>8780554431432234301</td>\n",
       "      <td>F</td>\n",
       "      <td>65000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3912300160509220549</td>\n",
       "      <td>M</td>\n",
       "      <td>85000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0183860411504195373</td>\n",
       "      <td>M</td>\n",
       "      <td>55000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5824687589795910572</td>\n",
       "      <td>M</td>\n",
       "      <td>75000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         fullVisitorID gender    hhi\n",
       "0   297008845417084558      F  85000\n",
       "1  8780554431432234301      F  65000\n",
       "2  3912300160509220549      M  85000\n",
       "3  0183860411504195373      M  55000\n",
       "4  5824687589795910572      M  75000"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# See an output of the synthetic CRM data\n",
    "\n",
    "CRM_query_df = f'''\n",
    "SELECT * FROM {CRM_view.full_table_id.replace(\":\", \".\")} LIMIT 5\n",
    "'''\n",
    "\n",
    "job_config = bigquery.QueryJobConfig()\n",
    "\n",
    "# Start the query\n",
    "query_job = client.query(CRM_query_df, job_config=job_config) #API Request\n",
    "df_CRM = query_job.result()\n",
    "df_CRM = df_CRM.to_dataframe()\n",
    "\n",
    "df_CRM"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Build a final view for to use as trainding data for clustering\n",
    "\n",
    "You may decide to change the view below based on your specific dataset.  This is fine, and is exactly why we're creating a view.  All steps subsequent to this will reference this view.  If you change the SQL below, you won't need to modify other parts of the notebook. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Build a final view, which joins GA360 data with CRM data\n",
    "\n",
    "final_data_view = 'Final_View'\n",
    "shared_dataset_ref = client.dataset(DATA_SET_ID)\n",
    "final_view_ref = shared_dataset_ref.table(final_data_view)\n",
    "final_view = bigquery.Table(final_view_ref)\n",
    "\n",
    "final_data_query = f'''\n",
    "SELECT\n",
    "    g.*,\n",
    "    c.* EXCEPT(fullVisitorId)\n",
    "FROM {ga360_view.full_table_id.replace(\":\", \".\")} g\n",
    "JOIN {CRM_view.full_table_id.replace(\":\", \".\")} c\n",
    "ON g.fullVisitorId = c.fullVisitorId\n",
    "'''\n",
    "\n",
    "final_view.view_query = final_data_query.format(PROJECT_ID)\n",
    "final_view = client.create_table(final_view)  # API request\n",
    "\n",
    "print(f\"Successfully created view at {final_view.full_table_id}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<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>fullVisitorID</th>\n",
       "      <th>Hashed_fullVisitorID</th>\n",
       "      <th>OS</th>\n",
       "      <th>Apparel</th>\n",
       "      <th>Office</th>\n",
       "      <th>Electronics</th>\n",
       "      <th>LimitedSupply</th>\n",
       "      <th>Accessories</th>\n",
       "      <th>ShopByBrand</th>\n",
       "      <th>Bags</th>\n",
       "      <th>productPrice_USD</th>\n",
       "      <th>gender</th>\n",
       "      <th>hhi</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0314742824007569667</td>\n",
       "      <td>6946654416482415939</td>\n",
       "      <td>Android</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>106.940</td>\n",
       "      <td>F</td>\n",
       "      <td>95000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2242360300500476735</td>\n",
       "      <td>7127521692467899408</td>\n",
       "      <td>Macintosh</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1.990</td>\n",
       "      <td>M</td>\n",
       "      <td>85000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>6667898188086359119</td>\n",
       "      <td>928068574965520919</td>\n",
       "      <td>iOS</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>4.990</td>\n",
       "      <td>F</td>\n",
       "      <td>95000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4757349846427056292</td>\n",
       "      <td>4102516958220717880</td>\n",
       "      <td>Chrome OS</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1.500</td>\n",
       "      <td>M</td>\n",
       "      <td>55000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>7253626747991065846</td>\n",
       "      <td>2270834522148945194</td>\n",
       "      <td>Android</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>92.990</td>\n",
       "      <td>M</td>\n",
       "      <td>75000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         fullVisitorID  Hashed_fullVisitorID         OS  Apparel  Office  \\\n",
       "0  0314742824007569667   6946654416482415939    Android        6       0   \n",
       "1  2242360300500476735   7127521692467899408  Macintosh        0       0   \n",
       "2  6667898188086359119    928068574965520919        iOS        0       0   \n",
       "3  4757349846427056292   4102516958220717880  Chrome OS        0       0   \n",
       "4  7253626747991065846   2270834522148945194    Android        0       0   \n",
       "\n",
       "   Electronics  LimitedSupply  Accessories  ShopByBrand  Bags  \\\n",
       "0            0              0            0            0     0   \n",
       "1            0              0            1            0     0   \n",
       "2            0              0            0            0     0   \n",
       "3            1              0            0            0     0   \n",
       "4            0              0            0            0     0   \n",
       "\n",
       "   productPrice_USD gender    hhi  \n",
       "0           106.940      F  95000  \n",
       "1             1.990      M  85000  \n",
       "2             4.990      F  95000  \n",
       "3             1.500      M  55000  \n",
       "4            92.990      M  75000  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Show final data used prior to modeling\n",
    "\n",
    "sql_demo = f'''\n",
    "SELECT * FROM {final_view.full_table_id.replace(\":\", \".\")} LIMIT 5\n",
    "'''\n",
    "\n",
    "job_config = bigquery.QueryJobConfig()\n",
    "\n",
    "# Start the query\n",
    "query_job = client.query(sql_demo, job_config=job_config) #API Request\n",
    "df_demo = query_job.result()\n",
    "df_demo = df_demo.to_dataframe()\n",
    "\n",
    "df_demo"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "wpGc5AggVOBN"
   },
   "source": [
    "# Create our initial model\n",
    "\n",
    "In this section, we will build our initial k-means model.  We won't focus on optimal k or other hyperparemeters just yet.\n",
    "\n",
    "Some additional points:  \n",
    "\n",
    "1. We remove fullVisitorId as an input, even though it is grouped at that level because we don't need fullVisitorID as a feature for clustering. fullVisitorID should never be used as feature.\n",
    "2. We have both categorical as well as numerical features\n",
    "3. We do not have to normalize any numerical features, as BigQuery ML will automatically do this for us. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "DyOB3FePfiIe"
   },
   "source": [
    "## Build a function to build our model\n",
    "\n",
    "We will build a simple python function to build our model, rather than doing everything in SQL.  This approach means we can asynchronously start several models and let BQ run in parallel."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def makeModel (n_Clusters, Model_Name):\n",
    "    sql =f'''\n",
    "    CREATE OR REPLACE MODEL `{PROJECT_ID}.{DATA_SET_ID}.{Model_Name}` \n",
    "    OPTIONS(model_type='kmeans',\n",
    "    kmeans_init_method = 'KMEANS++',\n",
    "    num_clusters={n_Clusters}) AS\n",
    "\n",
    "    SELECT * except(fullVisitorID, Hashed_fullVisitorID) FROM `{final_view.full_table_id.replace(\":\", \".\")}`\n",
    "    '''\n",
    "    job_config = bigquery.QueryJobConfig()\n",
    "    client.query(sql, job_config=job_config)  # Make an API request."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "vDUIzlcYyMOt"
   },
   "outputs": [],
   "source": [
    "# Let's start with a simple test to ensure everything works. \n",
    "# After running makeModel(), allow a few minutes for training to complete.\n",
    "\n",
    "model_test_name = \"test\"\n",
    "makeModel(3, model_test_name)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# After training is completed, you can either check in the UI, or you can interact with it using list_models(). \n",
    "\n",
    "for model in client.list_models(DATA_SET_ID):\n",
    "    print(model)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "Y2mDX1-WZg8k"
   },
   "source": [
    "# Work towards creating a better model\n",
    "\n",
    "In this section, we want to determine the proper k value.  Determining the right value of k depends completely on the use case.  There are straight forward examples that will simply tell you how many clusters are needed.  Suppose you are pre-processing hand written digits - this tells us k should be 10. Or perhaps your business stakeholder only wants to deliver three different marketing campaigns and needs you to identify three clusters of customers, then setting k=3 might be meaningful. However, the use case is sometimes more open ended and you may want to explore different numbers of clusters to see how your datapoints group together with the minimal error within each cluster. To accomplish this process, we start by performing the 'Elbow Method', which simply charts loss vs k. Then, we'll also use the Davies-Bouldin score.\n",
    "(https://en.wikipedia.org/wiki/Davies%E2%80%93Bouldin_index)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "ajkLrlFjlvPQ"
   },
   "source": [
    "Below we are going to create several models to perform both the Elbow Method and get the Davies-Bouldin score.  You may change parameters like low_k and high_k. Our process will create models between these two values.  There is an additional parameter called model_prefix_name.  We recommend you leave this as its current value.  It is used to generate a naming convention for our models. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 238
    },
    "colab_type": "code",
    "id": "WAuyizlkzzQU",
    "outputId": "68fb1c75-045b-4e8a-a7c3-2979ee4b3ed2"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Model started: kmeans_clusters_3\n",
      "Model started: kmeans_clusters_4\n",
      "Model started: kmeans_clusters_5\n",
      "Model started: kmeans_clusters_6\n",
      "Model started: kmeans_clusters_7\n",
      "Model started: kmeans_clusters_8\n",
      "Model started: kmeans_clusters_9\n",
      "Model started: kmeans_clusters_10\n",
      "Model started: kmeans_clusters_11\n",
      "Model started: kmeans_clusters_12\n",
      "Model started: kmeans_clusters_13\n",
      "Model started: kmeans_clusters_14\n",
      "Model started: kmeans_clusters_15\n"
     ]
    }
   ],
   "source": [
    "# Define upper and lower bound for k, then build individual models for each. \n",
    "# After running this loop, look at the UI to see several model objects that exist. \n",
    "\n",
    "low_k = 3\n",
    "high_k = 15\n",
    "model_prefix_name = 'kmeans_clusters_'\n",
    "\n",
    "lst = list(range (low_k, high_k+1)) #build list to iterate through k values\n",
    "\n",
    "for k in lst:\n",
    "    model_name = model_prefix_name + str(k)\n",
    "    makeModel(k, model_name)\n",
    "    print(f\"Model started: {model_name}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "OVxjQFWmVVmH"
   },
   "source": [
    "## Select optimal k"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 255
    },
    "colab_type": "code",
    "id": "tp7y6mksNY4D",
    "outputId": "07fbbd01-2387-46ad-b365-62f34d471a62"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "bqml_kmeans.kmeans_clusters_10\n",
      "bqml_kmeans.kmeans_clusters_11\n",
      "bqml_kmeans.kmeans_clusters_12\n",
      "bqml_kmeans.kmeans_clusters_13\n",
      "bqml_kmeans.kmeans_clusters_14\n",
      "bqml_kmeans.kmeans_clusters_15\n",
      "bqml_kmeans.kmeans_clusters_3\n",
      "bqml_kmeans.kmeans_clusters_4\n",
      "bqml_kmeans.kmeans_clusters_5\n",
      "bqml_kmeans.kmeans_clusters_6\n",
      "bqml_kmeans.kmeans_clusters_7\n",
      "bqml_kmeans.kmeans_clusters_8\n",
      "bqml_kmeans.kmeans_clusters_9\n",
      "bqml_kmeans.test\n"
     ]
    }
   ],
   "source": [
    "# list all current models\n",
    "models = client.list_models(DATA_SET_ID)  # Make an API request.\n",
    "print(\"Listing current models:\")\n",
    "for model in models:\n",
    "    full_model_id = f\"{model.dataset_id}.{model.model_id}\"\n",
    "    print(full_model_id)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 34
    },
    "colab_type": "code",
    "id": "AC8GAkKxhN9B",
    "outputId": "f2d30fb1-8fd8-40e2-9c71-0597231d6e1a"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Deleted model 'bqml_kmeans.test'.\n"
     ]
    }
   ],
   "source": [
    "# Remove our sample model from BigQuery, so we only have remaining models from our previous loop\n",
    "\n",
    "model_id = DATA_SET_ID+\".\"+model_test_name\n",
    "client.delete_model(model_id)  # Make an API request.\n",
    "print(f\"Deleted model '{model_id}'\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "SnGjnnrvH2Ej"
   },
   "outputs": [],
   "source": [
    "# This will create a dataframe with each model name, the Davies Bouldin Index, and Loss.  \n",
    "# It will be used for the elbow method and to help determine optimal K\n",
    "\n",
    "df = pd.DataFrame(columns=['davies_bouldin_index', 'mean_squared_distance'])\n",
    "models = client.list_models(DATA_SET_ID)  # Make an API request.\n",
    "for model in models:\n",
    "    full_model_id = f\"{model.dataset_id}.{model.model_id}\"\n",
    "    sql =f'''\n",
    "        SELECT \n",
    "            davies_bouldin_index,\n",
    "            mean_squared_distance \n",
    "        FROM ML.EVALUATE(MODEL `{full_model_id}`)\n",
    "    '''\n",
    "\n",
    "    job_config = bigquery.QueryJobConfig()\n",
    "\n",
    "    # Start the query, passing in the extra configuration.\n",
    "    query_job = client.query(sql, job_config=job_config)  # Make an API request.\n",
    "    df_temp = query_job.to_dataframe()  # Wait for the job to complete.\n",
    "    df_temp['model_name'] = model.model_id\n",
    "    df =  pd.concat([df, df_temp], axis=0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "zLBMjSR-qqCq"
   },
   "source": [
    "The code below assumes we've used the naming convention originally created in this notebook, and the k value occurs after the 2nd underscore.  If you've changed the model_prefix_name variable, then this code might break. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 452
    },
    "colab_type": "code",
    "id": "4DSIBlqVahZ7",
    "outputId": "a47da0cb-0430-479d-b979-f98a55b8f388"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<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>davies_bouldin_index</th>\n",
       "      <th>mean_squared_distance</th>\n",
       "      <th>model_name</th>\n",
       "      <th>n_clusters</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2.146</td>\n",
       "      <td>8.920</td>\n",
       "      <td>kmeans_clusters_3</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.576</td>\n",
       "      <td>8.492</td>\n",
       "      <td>kmeans_clusters_4</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.446</td>\n",
       "      <td>7.611</td>\n",
       "      <td>kmeans_clusters_5</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2.272</td>\n",
       "      <td>7.459</td>\n",
       "      <td>kmeans_clusters_6</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.532</td>\n",
       "      <td>6.994</td>\n",
       "      <td>kmeans_clusters_7</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.678</td>\n",
       "      <td>6.582</td>\n",
       "      <td>kmeans_clusters_8</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.557</td>\n",
       "      <td>6.012</td>\n",
       "      <td>kmeans_clusters_9</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.285</td>\n",
       "      <td>5.870</td>\n",
       "      <td>kmeans_clusters_10</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.388</td>\n",
       "      <td>5.665</td>\n",
       "      <td>kmeans_clusters_11</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.607</td>\n",
       "      <td>5.075</td>\n",
       "      <td>kmeans_clusters_12</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.380</td>\n",
       "      <td>4.989</td>\n",
       "      <td>kmeans_clusters_13</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.280</td>\n",
       "      <td>4.840</td>\n",
       "      <td>kmeans_clusters_14</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.449</td>\n",
       "      <td>4.709</td>\n",
       "      <td>kmeans_clusters_15</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   davies_bouldin_index  mean_squared_distance          model_name  n_clusters\n",
       "0                 2.146                  8.920   kmeans_clusters_3           3\n",
       "0                 1.576                  8.492   kmeans_clusters_4           4\n",
       "0                 1.446                  7.611   kmeans_clusters_5           5\n",
       "0                 2.272                  7.459   kmeans_clusters_6           6\n",
       "0                 1.532                  6.994   kmeans_clusters_7           7\n",
       "0                 1.678                  6.582   kmeans_clusters_8           8\n",
       "0                 1.557                  6.012   kmeans_clusters_9           9\n",
       "0                 1.285                  5.870  kmeans_clusters_10          10\n",
       "0                 1.388                  5.665  kmeans_clusters_11          11\n",
       "0                 1.607                  5.075  kmeans_clusters_12          12\n",
       "0                 1.380                  4.989  kmeans_clusters_13          13\n",
       "0                 1.280                  4.840  kmeans_clusters_14          14\n",
       "0                 1.449                  4.709  kmeans_clusters_15          15"
      ]
     },
     "execution_count": 17,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# This will modify the dataframe above, produce a new field with 'n_clusters', and will sort for graphing\n",
    "\n",
    "df['n_clusters'] = df['model_name'].str.split('_').map(lambda x: x[2])\n",
    "df['n_clusters'] = df['n_clusters'].apply(pd.to_numeric)\n",
    "df = df.sort_values(by='n_clusters', ascending=True)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 297
    },
    "colab_type": "code",
    "id": "jLVVMKm8QIFv",
    "outputId": "df4ef800-3eb6-4018-ba2d-be751c313b5a"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<AxesSubplot:xlabel='n_clusters'>"
      ]
     },
     "execution_count": 18,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAWoAAAEHCAYAAACHsgxnAAAAOXRFWHRTb2Z0d2FyZQBNYXRwbG90bGliIHZlcnNpb24zLjMuMCwgaHR0cHM6Ly9tYXRwbG90bGliLm9yZy86wFpkAAAACXBIWXMAAAsTAAALEwEAmpwYAAA0TklEQVR4nO3dd3xUVf7/8dcnhfSQhAQIHWkiJUACiAUBCyqoqCg2VnGF79J1f9a1Ydvisq4FV0URXEVREVdQxAaIjZIECFVqgFBCOumknN8fdxISSMiQzGQmyef5eMxjJpk7dz53CO85c+bcc8QYg1JKKffl4eoClFJKnZ0GtVJKuTkNaqWUcnMa1Eop5eY0qJVSys15OWOn4eHhplOnTs7YtVJKNUpxcXGpxpiIqu5zSlB36tSJ2NhYZ+xaKaUaJRE5UN192vWhlFJuToNaKaXcnAa1Ukq5Obv6qEVkJjAREOBtY8zLzixKqfpUVFREUlISBQUFri5FNQG+vr60a9cOb29vux9TY1CLSG+skB4EnARWiMhXxpjdta5UKTeSlJREUFAQnTp1QkRcXY5qxIwxpKWlkZSUROfOne1+nD1dHz2BtcaYPGNMMfAjcGMt61TK7RQUFNCiRQsNaeV0IkKLFi3O+dObPUG9FRgqIi1ExB+4FmhfRQGTRCRWRGJTUlLOqQilXE1DWtWX2vyt1RjUxpgdwD+A74AVwGaguIrt5hpjYowxMRERVY7Zrlnsu5C8rXaPVUqpRsquUR/GmHnGmAHGmKFAOuD4/umCE/Dd0/DGRfDe9fD7CigtdfjTKKVUQ2NXUItIS9t1B+Am4COHV+IbDDM3wxXPQNoe+GgczImGdXOhMNvhT6eUO5s1axazZ88+58c99dRTfP/99y6toSrDhg2r8mzlBQsWMG3aNADefPNN/vvf/57zvmNjY5kxY8Y5PcaRx1Yf7D2F/DMRaQEUAVONMRlOqcY/DC65H4ZMhR3LYO0b8PVDsPI5GPAHGDQJQjs65amVagyeffZZV5dQa3/6059q9biYmBhiYmIcXI17sSuojTGXOruQSjy9ofdN1iUp1grsdW/C2v/A+aPgwinQYQjoF0DKwZ5Zto3tR044dJ8XtAnm6et61bjdCy+8wH//+1/at29PREQE0dHRvP3228ydO5eTJ0/StWtX3n//fYqKioiKimLfvn14eHiQl5dHjx492LdvHxMnTmT06NGMHTuWuLg4/vznP5OTk0N4eDgLFiwgMjKSV199lTfffBMvLy8uuOACFi1aVG1NmzdvZsSIERw6dIiHH36YiRMnYozh4Ycf5uuvv0ZEeOKJJxg3bhyrV69m9uzZfPnllwBMmzaNmJgY7rnnnkr7nD9/Pn/729+IjIyke/fu+Pj4AFYrNzAwkAcffJBhw4YxePBgVq1aRWZmJvPmzePSS6uOoYrPO2vWLA4ePMi+ffs4ePAg999/f3lru6rXF2Dv3r1MnTqVlJQU/P39efvtt+natStDhgzhn//8J8OGDeOxxx7Dw8ODF154ocZ/R2dwyqRMDtUuBsbOg6xnYcM7EDffam1HRlmB3etG8PJxdZVK1UlcXByLFi1i48aNFBcXM2DAAKKjo7npppuYOHEiAE888QTz5s1j+vTpREVF8eOPPzJ8+HCWLVvGyJEjK51AUVRUxPTp0/niiy+IiIjg448/5vHHH+fdd9/l73//O/v378fHx4fMzMyz1pWQkMDatWvJzc2lf//+jBo1it9++41NmzaxefNmUlNTGThwIEOHDrXrOI8ePcrTTz9NXFwczZs3Z/jw4fTv37/KbYuLi1m/fj3Lly/nmWeesbtLZ+fOnaxatYrs7Gx69OjB5MmTSUhIqPL1BZg0aRJvvvkm3bp1Y926dUyZMoWVK1eyYMECxo4dy6uvvsqKFStYt26dXc/vDO4f1GWat4UrnoahD0HCx1Yr+/P/g++egoH3QfQECKzlaBOlbOxp+TrDTz/9xI033oi/vz8A119/PQBbt27liSeeIDMzk5ycHEaOHAnAuHHj+Pjjjxk+fDiLFi1iypQplfb3+++/s3XrVq688koASkpKiIyMBKBv377ceeedjBkzhjFjxpy1rhtuuAE/Pz/8/PwYPnw469ev5+eff+b222/H09OTVq1acdlll7FhwwaCg4NrPM5169YxbNgwykaGjRs3jl27dlW57U033QRAdHQ0iYmJNe67zKhRo/Dx8cHHx4eWLVuSnJxc7eubk5PDr7/+yi233FL++MLCQgB69erF+PHjue666/jtt99o1qyZ3TU4WsMJ6jLN/CFmAkTfA/tWWYG96gVYMxv63gKDJ0Pr3q6uUqlzVtX42nvuuYf//e9/REVFsWDBAlavXg1YQfPYY4+Rnp5OXFwcI0aMqPQ4Ywy9evXit99+O2OfX331FWvWrGHp0qU899xzbNu2DS+vqqPg9JpEBGNMldt6eXlRWmGkVnUnddg7jrisS8TT05Pi4jNGBNf4uNMfW9XzlpaWEhISwqZNm6rc15YtWwgJCSE5Odnu53eGhjspkwh0GQF3fgrTYmHAeNi6BN68GBaMhp3LobTE1VUqZZehQ4fy+eefk5+fT3Z2NsuWLQMgOzubyMhIioqKWLhwYfn2gYGBDBo0iJkzZzJ69Gg8PT0r7a9Hjx6kpKSUB3VRURHbtm2jtLSUQ4cOMXz4cF588cXylnp1vvjiCwoKCkhLS2P16tXl3Rwff/wxJSUlpKSksGbNGgYNGkTHjh3Zvn07hYWFZGVl8cMPP5yxv8GDB7N69WrS0tIoKiri008/dcTLV6PqXt/g4GA6d+5cXocxhs2bNwOwZMkS0tLSWLNmDTNmzKixm8iZGl6Luirh3WDUv2DEExD/X2tI36LbIbQzDP4T9L8TfIJcXaVS1RowYADjxo2jX79+dOzYsfyLs+eee47BgwfTsWNH+vTpQ3b2qaGq48aN45ZbbilvZVfUrFkzFi9ezIwZM8jKyqK4uJj777+f7t27c9ddd5GVlYUxhgceeICQkJBq6xo0aBCjRo3i4MGDPPnkk7Rp04Ybb7yR3377jaioKESEF198kdatWwNw66230rdvX7p161Zl33NkZCSzZs1iyJAhREZGMmDAAEpKnN+gqu71BVi4cCGTJ0/m+eefp6ioiNtuu422bdvy6KOP8sMPP9C+fXumTZvGzJkzee+995xea1Wkuo8xdRETE2NcusJLSTHstA3vO7QOfIKh/3gYPAlCO7muLuWWduzYQc+ePV1dhmpCqvqbE5E4Y0yV4wwbbtfH2Xh6WaNB/vgtTFwJ3a+G9W/Bq/1h0Z1waIOrK1RKKbs1zqCuqG003Pw23L8FLvkzHPgV3h0J8e+7ujKl3ML8+fPp169fpcvUqVNdXdYZvvnmmzPqvPHGpjGRZ+Ps+jibwmz45A+wd6XVp33pg3riTBOnXR+qvmnXR018guD2j6HPrbDyeVj+oI4OUUq5tcYx6uNceTWDG9+CoNbw66uQcxxuehu8fV1dmVJKnaHptajLeHjAVc/ByL/CjqXwwU2Qn+nqqpRS6gxNN6jLDJkKN8+DQ+th/jVw4oirK1JKqUo0qAH6jIW7FkPmIXjnSkj53dUVKaWAxMREeve2f0qIivNeX3vttWc9m/Dll18mLy+vriXWCw3qMucNgwlfQclJa/jeofWurkipRutc5u6oreXLl5/1rMuGFNRN88vE6kRGWSfJfHCztRzYLfOhxzWurkrVp68fhWNbHLvP1n3gmr+fdZPExESuvvpqLrnkEtauXUtUVBQTJkzg6aef5vjx4yxcuJBevXoxffp0tmzZQnFxMbNmzeKGG24gMTGR8ePHk5ubC8CcOXO46KKLWL16NbNmzSI8PJytW7cSHR3NBx98UO2kSI8++ihLly7Fy8uLq666itmzZ7N//37uuOMOiouLufrqq/n3v/9NTk7OWeeefvbZZ1m2bBn5+flcdNFFvPXWW4gIw4YN46KLLuKXX37h+uuvZ9iwYVXOlx0XF8e9996Lv78/l1xyyVlft/z8fCZMmMD27dvp2bMn+fn55fd16tSJ2NhY/Pz8uPXWW0lKSqKkpIQnn3yS5ORkjhw5wvDhwwkPD2fVqlVMnjyZDRs2kJ+fz9ixY3nmmWfK93P33XezbNmy8vlJzj//fHJycpg+fTqxsbGICE8//TQ333wz3377LU8//TSFhYV06dKF+fPnExgYaPefS1W0RX26sM5WWLfsCYvugDjXnNuvmp49e/Ywc+ZMEhIS2LlzJx9++CE///wzs2fP5q9//SsvvPACI0aMYMOGDaxatYqHHnqI3NxcWrZsyXfffUd8fDwff/xxpWWpNm7cyMsvv8z27dvZt28fv/zyS5XPnZ6ezueff862bdtISEjgiSeeAGDmzJnlAVY2n0dNpk2bxoYNG9i6dSv5+fnlYQ6QmZnJjz/+yIwZM5g+fTqLFy8uD+bHH38cgAkTJvDqq69WOfPf6d544w38/f1JSEjg8ccfJy4u7oxtVqxYQZs2bdi8eTNbt27l6quvZsaMGbRp04ZVq1axatUqwFpYIDY2loSEBH788UcSEhLK9xEeHk58fDyTJ08uX8Lrueeeo3nz5mzZsoWEhARGjBhBamoqzz//PN9//z3x8fHExMTw0ksv2fW6nY1dLWoReQC4DzDAFmCCMabqOQwbg4BwuHsZfHoPLJsBOcnWPNh6YkzjV0PL15k6d+5Mnz59AGsu5MsvvxwRoU+fPiQmJpKUlMTSpUvLg6KgoICDBw/Spk0bpk2bxqZNm/D09Kw0v/OgQYNo164dAP369SMxMbHKVmpwcDC+vr7cd999jBo1itGjRwPwyy+/8NlnnwEwfvx4HnnkkRqPY9WqVbz44ovk5eWRnp5Or169uO666wBrIimofr7srKwsMjMzueyyy8qf8+uvv672ucpmtgNrnu2+ffuesU2fPn148MEHeeSRRxg9enS1K8V88sknzJ07l+LiYo4ePcr27dvL91dxbuwlS5YA8P3331daHSc0NJQvv/yS7du3c/HFFwNw8uRJhgwZUuNrVpMag1pE2gIzgAuMMfki8glwG7Cgzs/uznwC4faPYOkMa77r7KNw7Wzw8Kz5sUrVQsV5lD08PMp/9vDwoLi4GE9PTz777DN69OhR6XGzZs2iVatWbN68mdLSUnx9favc59nmdfby8mL9+vX88MMPLFq0iDlz5rBy5Uqg6nmcq5t7uqCggClTphAbG0v79u2ZNWtWpXmpAwICgOrny87MzLR7vuoyNW3fvXt34uLiWL58OY899hhXXXUVTz31VKVt9u/fz+zZs9mwYQOhoaHcc889lequam5sY8wZz22M4corr+Sjjxy7/re9XR9egJ+IeAH+QNMYw+bpDWP+A5c8ALHvWqeeF+XX/DilnGDkyJG89tpr5RP3b9y4EYCsrCwiIyPx8PDg/fffr9W0oTk5OWRlZXHttdfy8ssvl0+kf/HFF5e3GivOh13d3NNl4RYeHk5OTg6LFy+u8vmqmy87JCSE5s2b8/PPP5/xnFUZOnRo+TZbt26t1F1R5siRI/j7+3PXXXfx4IMPEh8fD0BQUFD5tLEnTpwgICCA5s2bk5ycfNZWfJmrrrqKOXPmlP+ckZHBhRdeyC+//MKePXsAyMvLq3YFm3NRY1AbYw4Ds4GDwFEgyxjz7enbicgkEYkVkdiUlJQ6F+Y2ROCKWXD1P2DnV/D+jZDvnEXYlTqbJ598kqKiIvr27Uvv3r158sknAZgyZQrvvfceF154Ibt27SpvtZ6L7OxsRo8eTd++fbnsssv497//DcArr7zC66+/zsCBA8nKyirfvn379uVzT995553lc0+HhIQwceJE+vTpw5gxYxg4cGCVz1c2X/YjjzxCVFQU/fr149dffwWsSaKmTp3KkCFD8PPzO2vdkydPJicnh759+/Liiy8yaNCgM7bZsmULgwYNol+/frzwwgvl/e+TJk3immuuYfjw4URFRdG/f3969erFvffeW951cTZPPPEEGRkZ9O7dm6ioKFatWkVERAQLFizg9ttvp2/fvlx44YXs3Lmzxn3VpMZJmUQkFPgMGAdkAp8Ci40xH1T3GLeelKkuti6x1mkM6wJ3fWat46gaPJ2UyX6BgYFnXRFG2ccZkzJdAew3xqQYY4qAJcBFda60Iep9kxXQJw7DvCvheN3fKZVSqib2BPVB4EIR8Rer5/xyYIdzy3JjnYfChOVQWmydGHNwrasrUuqc3HjjjWfM6/zNN9/Y9VhXtaab8lzUYOd81CLyDFbXRzGwEbjPGFNY3faNtuujoowD1kROWUnWXCE9R7u6IlVLO3bs4Pzzzz/n0QZK1YYxhp07dzp+PmpjzNPGmPONMb2NMePPFtJNRmhHuPdbaNUbPhkPsfNdXZGqJV9fX9LS0nDGIhpKVWSMIS0trdIQSnvoKeR1EdAC7l5qnRjz5f3WiTGXPaInxjQw7dq1IykpiUY1Wkm5LV9f3/KTkOylQV1XzQLgtg9h2f2w+m+2E2P+ZS2wqxoEb29vOnfu7OoylKqWpokjeHrDDXOsFWN+mg05KTB2HniffQyoUkrZQ4PaUUTg8ietsF7+kDX73oA/QHh3CO8G/mGurlAp1UBpUDvaoIkQEAFfTIOl00793r+FFdotutrC2xbgIR21m0QpdVaaEM7Qawz0vA4yD0LqbkjdBWm7rdu7VsDG909t6+ENLbpUCPBupwLdL8RVR6CUciMa1M7i4WnNbR3WGbpfVfm+/AxI3WMFeOouSLPd3rXCOpGmTEDLCuHd7dTt5u11Fj+lmhANalfwC4X2A61LRSVF1ok0ZQGeuttqiW/7HAoyT23n6WNrgXeDbldCn1vBq1m9HoJSqv7YdWbiuWoSZybWJ2MgL+1UN0pZiB/fAVkHIbgtXDTd+vKy2bnPnKaUcr2znZmoLeqGQMRadSYgHDpWWC3CGNjzA/z8Eqx4FH58ES6cbH2h6RfqunqVUg6layY2ZCLQ7Qprkqh7v4F2A63VaP7dG759ErKPubpCpZQDaFA3Fh0uhDs/gT/9DN1Hwm9z4OW+8OUDkL7f1dUppepAg7qxad0Hxr4L02Kh3+2w8QN4LRo+mwjJ211dnVKqFjSoG6sWXeC6V2BmgtVvvfMreGMIfHgbHNrg6uqUUudAg7qxC46EkS/AA1th2F/g0FqYdwUsGG19EalTeyrl9jSomwr/MBj2CNy/FUb+FdL2WgsfzB0G27+A0lJXV6iUqoYGdVPjEwhDpsLMTXDdq1B4Aj75A/xnMGxcaJ10o5RyKzUGtYj0EJFNFS4nROT+eqhNOZOXD0TfbX3pOHa+9fMXU+CVfrD2TTiZ5+oKlVI253Rmooh4AoeBwcaYA9Vtp2cmNkDGwJ7v4aeX4OCv1mx/F06GgRN1ciil6kGd10ys4HJg79lCWjVQIta8Ifd+DRNWQNtoWPn8qZNnMg+6ukKlmqxzbVG/C8QbY+ZUcd8kYBJAhw4dog8c0Cxv8I5tsVrY2/9n/dz9Ghh0H5w3XNeFVMrBztaitjuoRaQZcAToZYxJPtu22vXRyGQegrj5EPce5KVCi24w8D7rhBrf5q6uTqlGwVFdH9dgtabPGtKqEQppD5c/BX/eDjfOtfqsVzwC/+ppnaKuZzwq5VTnMnve7cBHzipENQBePhA1zrocjocN71hD+mLfhY6XWLP2nT/KWuxXKeUwdnV9iIg/cAg4zxiTVdP22vXRhOSmWUuLxc6zvnAMagMxE2DA3RDUytXVKdVgOKSP+lxoUDdBpSWw+1tYPxf2rrTWgrzgBhg0CdoP0i8flaqBLhygnM/DE3pcY11S91jdIpsWwtbF1ox+gyZB77HQzN/VlSrV4GiLWjlPYQ5s+QTWvwPHt4FvCPS/Cwb+EcLOc3V1SrkV7fpQrmUMHPjV6hbZsQxMqXVyzaBJ0OVy8NApZ5TSrg/lWiLQ6WLrcuIIxC2wLgvHQmhna0x2/zt1nUelqqFNGVW/gtvA8L9Y063ePA8CW8G3j1tjsn941uouUUpVokGtXMOrGfQZC3/8Bv7vJ2v89U//gjkDIeETXdBAqQo0qJXrRfaFsfPg3m8hsCUsmQjvjoQjG11dmVJuQYNauY8Og2HiKrh+DqTvg7nD4YtpkJPi6sqUcikNauVePDxgwHiYHmetRLP5I3htAPz6GhSfdHV1SrmEBrVyT77NrUV5p6yF9oPh2yfgjYtg93eurkypeqdBrdxbeDe4azHc8ak1/nrhWFh4q7U4r1JNhAa1ahi6X2W1rq98zjp55vXB1sozBSdcXZlSTqdBrRoOr2Zw8Qyr/7rvOPj1VXgtGjZ+AKWlrq5OKafRoFYNT1ArGPM6TFwJoZ3gi6nwzuVwaIOrK1PKKTSoVcPVNhr++K216kz2UZh3BSyZBCeOuroypRxKg1o1bCLWijPTYuHS/wfbPre6Q376FxQVuLo6pRxCg1o1Dj6B1rqOU9dDl+HWvCH/GQw7v9LT0VWDZ1dQi0iIiCwWkZ0iskNEhji7MKVqJawz3LYQxv8PvHxh0R3w/o1wfKerK1Oq1uxtUb8CrDDGnA9EATucV5JSDtBlOPzpF7jmRTgSb50s8/UjkHlIW9iqwalx4QARCQY2Yy1sa9dfuC4coNxKbhqset6aA9uUgre/tcJMiy4Q1qXydUCEru+oXKJOK7yISD9gLrAdqzUdB8w0xuSett0kYBJAhw4dog8cOFD3ypVypOM74cDP1lmNaXshfS9kJEJp8altmgVBi/PODPCwLuAfpiGunKauQR0DrAUuNsasE5FXgBPGmCere4y2qFWDUVIMWQchbZ8V3GUBnrYXMg9YLfAyvs2hRdfTQtwW6n4hLjsE1TjUdSmuJCDJGLPO9vNi4FFHFaeUS3l6Wd0gYecBV1S+r/ikFdYVwzt9LxxcC1s+BSo0cvxbnArw8G7Qpr910eXFlAPUGNTGmGMickhEehhjfgcux+oGUapx82pmhW54tzPvKyqwuk3KAjxtjzWH9r4fralZy4R1gbYDrJNz2kZD6z7g7Vdvh6AaB3sXt50OLBSRZsA+YILzSlKqAfD2hZbnW5fT5WfC0U1wOA4Ox0PiL7YWOODhBS0vsAW3LcAjzgcPz/qsXjUwNfZR14b2USt1mhNHrWGCZeF9JB4Ksqz7vP0hsp8tuG3hHdJRv7hsYuraR62UqqvgSAgeZS3iC9Zsf+n7Kof3+rehpNC6378FtBlwquXdZgAERriufuVSGtRKuYKHB4R3tS59b7V+V1IEydsqhPdG2PvDqZEnIR0qhHe0tfKNp/4Xbgr0X1kpd+HpDW36WZeYe63fFebA0c0VwjsOtv/Puq9lLxj1L+ioMzo0dhrUSrkzn0DodLF1KZObCntXWhNPzb8aom6HK5+FwJauq1M5lc6ep1RDExBudZdMXWdN7bplsTW167q3rBN4VKOjQa1UQ9UswJradcpvVp/11w/D3GHWCTmqUdGgVqqhC+8G4z+HW/8L+enw7kj43xTISXF1ZcpBNKiVagxE4IIbYNoGuOQBSPjE1h0yV7tDGgENaqUak2YBcMUsmPwrtO0PXz8Ebw+DQ+tdXZmqAw1qpRqjiO7WKje3LLDm4553pbVae26qqytTtaBBrVRjJQK9brS6Qy6eCZsXwWsDrDMgS0tcXZ06BxrUSjV2PoHWOOvJv0JkFCx/EN4eDoc2uLoyZScNaqWaioge8IelMPZdyDkO866AL6Zpd0gDoEGtVFMiAr1vtrpDLpphzZ39WjRsmKfdIW5Mg1qppsgnCK56zlqpvXUf+OrP8PYISIpzdWWqChrUSjVlLc+Hu5fBzfMg+xi8czksnWGNFFFuQ4NaqaZOBPqMtbpDhkyFjR/AnGiIna/dIW7CrhVeRCQRyAZKgOLqViEooyu8KNWAJW+H5Q/BgZ/BPxyCIq1FCwIqXAJbVv45IMJaY1LVmqNWeBlujNGvh5Vq7FpdAPd8CduWwL7V1pwhuSnWAr45KVCcX/XjfJtDgC3ATw/208PdJ0iXGjsHOh+1UupMZaNDet985n2FOVZw56ZC7nHrdlmY5x63fn98J+T+ZE0SVRUvX1toh0Ngawg7z1rtpoVt1ffAVhrkFdgb1Ab4VkQM8JYxZu7pG4jIJGASQIcOHRxXoVLKvfgEWpewzjVvW1IEeWnWuO3clFOXnOOngj7rkNVyr9hS9wmGFl1OBXeLrtZ1WBdo5u+0Q3NX9vZRtzHGHBGRlsB3wHRjzJrqttc+aqXUOSkthROHIW03pO6xXe+2uluyDlXetnn7U8FdFuTh3SCojbUWZQNV5z5qY8wR2/VxEfkcGARUG9RKKXVOPDwgpL116TKi8n0ncyFt75khvulDOJlzajtv/9Na4d1s3SldrT7xBqzGoBaRAMDDGJNtu30V8KzTK1NKKbCmbo3sa10qMsYa+12x9Z2621oIePv/Tq3eDtbIlbDzILRThUtn6zog3O37w+1pUbcCPhfrQLyAD40xK5xalVJK1UQEgiOtS+ehle8rLoT0fbYAt7XEMxJh7yrIPlJ5W++A0wK8k9X/HtrJ6mbx9q2PozmrGoPaGLMPiKqHWpRSyjG8fKBlT+tyuqJ8yDxoBXfZJX2/Fex7V542/FAguM2ZQV7PrXEdnqeUalq8/ayZBCN6nHmfMdaIlIohnrHf1hpfCdlHT9tXwJkt8UETHV6yBrVSSpURgaBW1qXD4DPvr9gaT99foUVua437t9CgVkopl6qpNZ6f4ZSnbbiDDpVSyp2IgH+YU3atQa2UUm5Og1oppdycBrVSSrk5DWqllHJzGtRKKeXmNKiVUsrNaVArpZSb06BWSik3p0GtlFJuToNaKaXcnAa1Ukq5OQ1qpZRyc3YHtYh4ishGEfnSmQUppZSq7Fxa1DOBHc4qRCmlVNXsCmoRaQeMAt5xbjlKKaVOZ2+L+mXgYaC0hu2UUko5WI1BLSKjgePGmLgatpskIrEiEpuSkuKwApVSqqmzp0V9MXC9iCQCi4ARIvLB6RsZY+YaY2KMMTEREREOLlMppZquGoPaGPOYMaadMaYTcBuw0hhzl9MrU0opBeg4aqWUcnvntAq5MWY1sNoplSillKqStqiVUsrNaVArpZSb06BWSik3p0GtlFJuToNaKaXcnAa1Ukq5OQ1qpZRycxrUSinl5jSolVLKzWlQK6WUm9OgVkopN6dBrZRSbk6DWiml3JwGtVJKuTkNaqWUcnMa1Eop5eY0qJVSys3Zswq5r4isF5HNIrJNRJ6pj8KUUkpZ7FmKqxAYYYzJERFv4GcR+doYs9bJtSmllMKOoDbGGCDH9qO37WKcWZRSSqlT7OqjFhFPEdkEHAe+M8asc2pVSimlytkV1MaYEmNMP6AdMEhEep++jYhMEpFYEYlNSUlxcJlKKdV0ndOoD2NMJrAauLqK++YaY2KMMTERERGOqU4ppZRdoz4iRCTEdtsPuALY6eS6lFJK2dgz6iMSeE9EPLGC/RNjzJfOLUsppVQZe0Z9JAD966EWpZRSVdAzE5VSys1pUCullJvToFZKKTenQa2UUm5Og1oppdycBrVSSrk5DWqllHJzGtRKKeXmNKiVUsrNaVArpZSb06BWSik3p0GtlFJuToO6AYtNTGdfSk7NGyqlGjQN6gbIGMN/Vu9h7Ju/ce2rP/FJ7CFXl6SUciJ75qNWbqSwuITHlmxhSfxhRveNJC3nJA8vTmD9/nSeu6E3fs08XV2iUsrB3Cqotx3JonurILw9taFflfTck/zf+7FsSMzgz1d2Z/qIrpQaeOX7Xby2ag8JSZn8584BdG0Z5OpSlVIO5DaJmJVfxG1z13LNKz+xZpcujnu63cnZ3PD6zyQkZfHa7f2ZcXk3RARPD+HPV/XgvQmDSMs5yfVzfuHzjUmuLlcp5UD2rJnYXkRWicgOEdkmIjOdUUiwrxcv3dqPopJS/vDueu57L5YDabnOeKoGZ82uFG76z6/knyxl0aQLuS6qzRnbDO0ewVczLqV3m+Y88PFmHluSQEFRiQuqVUo5mhhjzr6BSCQQaYyJF5EgIA4YY4zZXt1jYmJiTGxsbK0KKiwu4d2fE5mzcjdFJYY/XtqZacO7EuDjVr009eb93xKZtWw73VoGMu+egbQN8Tvr9sUlpbz03S7+s3ovPSODef2O/pwXEVhP1SqlaktE4owxMVXeV1NQV7GzL4A5xpjvqtumLkFdJvlEAf9YsZMl8YdpGeTDo9ecz5h+bfHwkDrtt6EoLinl+a92sODXRK7o2ZKXb+tP4Dm8Wa3aeZwHPtlEcYnh7zf3YXTfM1vhSin34bCgFpFOwBqgtzHmxGn3TQImAXTo0CH6wIEDtS64oviDGTyzdBubk7Lo3yGEWdf1Iqp9iEP27a5OFBQx7cONrNmVwsRLO/PoNT3xrMUb1JHMfKZ9GE/8wUzGX9iRJ0b3xMdLR4Uo5Y4cEtQiEgj8CLxgjFlytm0d0aKuqLTU8Fl8Ev9Y8TupOYWMjW7Hw1f3oGWQr8Oew10cSs/j3gUb2J+ay3NjenP7oA512l9RSSkvrtjJ2z/tp3fbYP5zRzQdWvg7qFqllKPUOahFxBv4EvjGGPNSTds7OqjLZBcUMWfVHt79eT8+Xp5MH9GVCRd3ppmX2wxeqZMNien83/txlJQa3rhrABd1CXfYvr/bnsz/+2QTBvjn2Ciu7t3aYftWStVdnYJaRAR4D0g3xtxvzxM6K6jL7E/N5fkvt/PDzuN0Dg/gydE9GXF+K6c9X31YEp/Eo59toW2oH/PujnHKF4CH0vOY9mE8m5OymHBxJx67pmejeZNTqqGra1BfAvwEbAFKbb/+izFmeXWPcXZQl1n9+3Ge/XI7+1JyGdYjgidHX0CXBjbCobTU8NJ3u5izag9DzmvBG3cNIMS/mdOe72RxKX9dbn1JGdU+hNfv6E+7UO0KUcrVHDrqwx71FdRg9cG+92sir3y/m/yiEu65qBMzruhGsK93vTx/XeSfLOH/fbqJ5VuOcdvA9jw3pne9nZX59ZajPLw4AQ8P4V+3RHHFBQ37E4lSDV2jDuoyqTmFzP7mdz6OPUSLgGY8NLIHt0S3d9vhfMknCpj431i2HM7i8Wt78sdLOmP1MtWfA2m5TFkYz7YjJ5g09DweGtlDT99XykWaRFCX2ZKUxTPLthF7IIM+bZsz6/oLiO4Y5pJaqrP1cBb3vRfLiYIiXr2tv0tbswVFJTz/1XY+WHuQ6I6hvHZ7f9rUcFKNM5SWGrd9U1WqPjSpoAZrGtClm4/wt+U7OXaigDH92vDoNT1p3dz1w/m+2XaM+xdtItTfm3fuHsgFbYJdXRIASzcf4bHPEmjm5cFL4/oxvEdLhz9HSanhSGY++1Nzyy/7UnPZn5rD4Yx8mvt50zbUj3Yh/tZ1qB/tQv1pG+JHuzC/BtGdpVRtNbmgLpN3spg3Vu/lrTX78BRh6vAu3Hfpefh61/9JH8YY3lqzj3+s2EnfdiG8/YdotxsHvjclh6kL49l5LJspw7rw5yu743WOXSHGGNJyT1pBnHIqiPen5pKYlsfJ4tLybQN9vOgcHkDn8ADahfqRlV9EUkY+SRl5HM7Mp6CotNK+g3y9aBfqT7tQPyu8bUHezhbqzf286737qDE7nl1AcYkhsrmvvq71oMkGdZlD6Xm88NUOVmw7RvswPyZf1pUerQNpH+ZPRKCP0/8ITxaX8vjnW/g0LonRfSOZfUuUS94s7FFQVMKspdtYtOEQgzqH8drt/WkVfOYbSk5hMYllLeKUU2G8LzWX7ILi8u28PYWOLawwPs8Wyp3DA+gcEXDW174s8A9n5FcK76SMfNvv8sg9WXnSqUAfr/IAP6NFHupHWEAzDZxqFJWUsuPoCeIPZBB/MJO4AxkczswHILK5L9EdQxnYKYzojqH0jAyu1Zmy6uyafFCX+XVPKs8s287vydnlv/P19qBDmD8dwvxpb7suu7QL9a/zRPzpuSf50wdxrN+fzozLu3H/5d0aRF/s5xuT+MuSrfg38+TBkT3ILiiygjjF6rI4nl1Yafu2IX6nQtgWxF3CA2kT4nvOrXJ7GGMqtcCTbIFeFuZJGXmV3jAA/Lw96RkZxKXdIhjaPYJ+7UOabOCk5hSWh3L8wQwSkjLLP8G0CvYhumMoAzqE4u3pQeyBDGIT0zmaVQBYb4j9O4QQ0zGMmE6h9Gsf0mQnTXMkDeoKSkoN+1NzOZSRx6H0PA6m5XEw/dQl77RWWssgnzODvIV1HRHoc9bQ3XM8hz++t4GjWQX8c2xfbujX1tmH51C7k7OZsjCe3cetdRnDAppVCuPzbIHcqUWAW35CyMov4nB5eFthHnfACqVSY02te0m3cIbagtsVX6LWh+KSUnYey2bjwVPBfCAtDwAvD6FX2+YM6BDCgA6hDOgYSptqujoOZ+YTm5hObGIGGxLT+T05G2PA00O4IDK4vNUd0ym0yk9h6uw0qO1kjCE992R5aB+qEOCH0vM5kpVPxZfLx8ujUiu84u0jmfnMWLQRHy8P3hofQ3THUNcdWB0UFJWw53gO7UL9nHoiTn3KzDvJz3tSWbMrhTW7Ujl2wmopdm0ZyNBuEVzWI4LBncPc8s3HHhm5J9l4KIO4AxnEH8hkc1JmeQMkPNCH6I6nQrlP2+a1Ps4TBUXEH7CeJzYxg42HMspb5e3D/Mpb3DEdw+jWMrBBfJKsi8LiEo6fKKR9WO1OINOgdpDC4hKOZBZUDvIKLfKcwsoftXu0CmLePTF65p8bM8aw+3gOa3al8OOuFNbtT+dkcSk+Xh4M6hzGZd2t1na3loFu2b9dUmrYfTyb+ANWv/LGgxnsS7UW3Chr6Q7oEMIAW1dGu1A/px1HUUkp24+cYENiOnEHMtiQmEFqjtVFFuzrRXTHUGI6hRHTMZSo9iEN9o2wpNRwIC2XXcnZ/H4sh13J2ew8doLEtDwiAn1Y+5fLa7VfDep6YIwhM6+oPLSz8ou4oV8bgnRIWYNSUFTCuv3pttZ2Snm3T+tgX4Z2D2do9wgu6Rper58uSksNqbmFJGcVcuxEAcdOFHA0M58th7PYeDCzvIEQFtDM1lK2Wsx92zXHv5nr+o6NMRxMzyM2MYPYA1aXSdnr6e0p9G7bnIGdwujfPoS2oX60CvalRUAzp3ynURvGGI5mFfD7sWx+T85ml+16z/EcCm2jl0SgQ5g/3VsF0aNVEN1bB3Fd38havRlqUCtVS0cy8/lpt9VF8tPuFE4UFOMh0LddCEO7R3BZ93Ci2oXUOlzyT5ZY4ZtVQLIthCveTs4q4Hh2IcWllf+fenoI3VsFnerG6BBKxxb+btnqrygj9yTxB63WdtyBdDYnZVUasukhVvdMq2BfWgX7EBFkXZf93DLItzzQHdmVkpZTWCGMrVbyrmPZZFf4lNw62JfurYPo0SrQCubWQXRtGeiwN0MNaqUcoLiklM1JWVZre3cKmw+d+lLy4q5Wa3to9wjahvhRWmoNL0y2Be+xEwVV3j5x2sgUsEZVtAr2oXVzK5RaB/tWut0q2JfwQPdpedZFYXEJu5NzrDen7AKSTxRy3Pb6JJ8o5Hh2Aak5J894nJeHEBHkQ8tgX1oF+dAy2IdWthBvWR7svoT6Vx5bn1NYXB7Cvydn8/uxbHYlZ1d6juZ+3vRofaqF3MPWWm7u79xPxxrUSjlBVl7RqS8ld6eUD18LD/QhK/8kRSWV/295CEQE+ZSHbZVB3Nz3nJZcawpOFpeSmlNYKbxP3T4V7Bl5RWc8tpmnBxFBPkQE+ZCSXVg+Nhys4Zqnt5B7tAoiIsj551ZURYNaKSczxrDneA4/7kphV3I2LQIrB3LrRtQKdlcFRSWkZJcFeYVgP2F1H4UFNKNH66Dy/uR2oX5uNRLlbEGtb91KOYCI0K1VEN1aBbm6lCbL19uT9rZhso2Nvr0rpZSb06BWSik3V2NQi8i7InJcRLbWR0FKKaUqs6dFvQC42sl1KKWUqkaNQW2MWQOk10MtSimlquCwPmoRmSQisSISm5KS4qjdKqVUk+ewoDbGzDXGxBhjYiIiIhy1W6WUavJ01IdSSrk5p5zwEhcXlyoiB5yxbwcJB1JdXYSD6LG4n8ZyHKDHUp86VndHjaeQi8hHwDCsg0wGnjbGzHNkdfVNRGKrO1WzodFjcT+N5ThAj8Vd1NiiNsbcXh+FKKWUqpr2USullJtrqkE919UFOJAei/tpLMcBeixuwSnTnCqllHKcptqiVkqpBkODWiml3FyTC2oR8RSRjSLypatrqQsRCRGRxSKyU0R2iMgQV9dUWyLygIhsE5GtIvKRiPi6uiZ7VTW7pIiEich3IrLbdh3qyhrtVc2x/NP2N5YgIp+LSIgLS7Tb2Wb9FJEHRcSISLgraquNJhfUwExgh6uLcIBXgBXGmPOBKBroMYlIW2AGEGOM6Q14Are5tqpzsoAzZ5d8FPjBGNMN+MH2c0OwgDOP5TugtzGmL7ALeKy+i6qlBVQx66eItAeuBA7Wd0F10aSCWkTaAaOAd1xdS12ISDAwFJgHYIw5aYzJdGlRdeMF+ImIF+APHHFxPXarZnbJG4D3bLffA8bUZ021VdWxGGO+NcaULZW+FmhX74XVwllm/fw38DDQoEZRNKmgBl7G+kcqdXEddXUekALMt3XjvCMiAa4uqjaMMYeB2VgtnKNAljHmW9dWVWetjDFHAWzXLV1cj6PcC3zt6iJqS0SuBw4bYza7upZz1WSCWkRGA8eNMXGursUBvIABwBvGmP5ALg3n43Ultv7bG4DOQBsgQETucm1V6nQi8jhQDCx0dS21ISL+wOPAU66upTaaTFADFwPXi0gisAgYISIfuLakWksCkowx62w/L8YK7oboCmC/MSbFGFMELAEucnFNdZUsIpEAtuvjLq6nTkTkbmA0cKdpuCdedMFqDGy2ZUA7IF5EWru0Kjs1maA2xjxmjGlnjOmE9WXVSmNMg2y5GWOOAYdEpIftV5cD211YUl0cBC4UEX8REaxjaZBfjFawFLjbdvtu4AsX1lInInI18AhwvTEmz9X11JYxZosxpqUxppMtA5KAAbb/S26vyQR1IzQdWCgiCUA/4K+uLad2bJ8KFgPxwBasv8kGc6qvbXbJ34AeIpIkIn8E/g5cKSK7sUYY/N2VNdqrmmOZAwQB34nIJhF506VF2qmaY2mw9BRypZRyc9qiVkopN6dBrZRSbk6DWiml3JwGtVJKuTkNaqWUcnMa1Eop5eY0qFWjISKdqprW0s7H3iMibRxdk1KOoEGtlOUerLlG7Gab7U8pp9OgVm7D1iLeISJv2xYS+FZE/KrZtquIfC8im0UkXkS6nHb/PSIyp8LPX4rIMNvCEQtsixRssS1aMBaIwTrTc5OI+IlItIj8KCJxIvJNhbk7VovIX0XkR2CmiNxi29dmEVnjxJdHNWHaIlDuphtwuzFmooh8AtwMVDV51kLg78aYz20rwnhg33Si/YC2tkUKEJEQY0ymiEwDHjTGxIqIN/AacIMxJkVExgEvYE3zCRBijLnM9vgtwEhjzOGGsvqJang0qJW72W+M2WS7HQd0On0DEQnCCtvPAYwxBbbf27P/fcB5IvIa8BVQ1dzXPYDeWPNbgLXqzNEK939c4fYvwALbm8oSewpQ6lxpUCt3U1jhdglQVdeHPYlcTOWuPV8AY0yGiEQBI4GpwK2cailX3P82Y0x161Dmlt0wxvxJRAZjrRy0SUT6GWPS7KhPKbtpH7VqcIwxJ4AkERkDICI+tonhK0oE+omIh22dvEG2bcMBD2PMZ8CTnJrHOxtrljiA34EIsS0YLCLeItKrqlpEpIsxZp0x5ikgFWjvoMNUqpy2qFVDNR54S0SeBYqAW6i8xNovwH6sqVO3Yk2jCtAWawmzskZK2WKtC4A3RSQfGAKMBV4VkeZY/09eBrZVUcc/RaQbViv8B6DBLfOk3J9Oc6qUUm5Ouz6UUsrNadeHcmsi8jrWepcVvWKMme+KepRyBe36UEopN6ddH0op5eY0qJVSys1pUCullJvToFZKKTf3/wFxL0pkcbG1qwAAAABJRU5ErkJggg==\n",
      "text/plain": [
       "<Figure size 432x288 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light",
      "tags": []
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "df.plot.line(x='n_clusters', y=['davies_bouldin_index', 'mean_squared_distance'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "OKWIJMXBcatm"
   },
   "source": [
    "Note - when you run this notebook, you will get different results, due to random cluster initialization.  If you'd like to consistently return the same cluster for reach run, you may explicitly select your initialization through hyperparameter selection (https://cloud.google.com/bigquery-ml/docs/reference/standard-sql/bigqueryml-syntax-create#kmeans_init_method). \n",
    "\n",
    "Making our k selection:  There is no perfect approach or process when determining the optimal k value. It can often be determined by business rules or requirements.  In this example, there isn't a simple requirement, so these considerations can also be followed:\n",
    "\n",
    "\n",
    "1.   We start with the 'elbow method', which is effectively charting loss vs k.  Sometimes, though not always, there's a natural 'elbow' where incremental clusters do not drastically reduce loss.  In this specific example, and as you often might find, unfortunately there isn't a natural 'elbow', so we must continue our process. \n",
    "2.  Next, we chart Davies-Bouldin vs k.  This score tells us how 'different' each cluster is, with the optimal score at zero.  With 5 clusters, we see a score of ~1.4, and only with k>9, do we see better values. \n",
    "3. Finally, we begin to try to interpret the difference of each model. You can review the evaluation module for various models to understand distributions of our features.  With our data, we can look for patterns by gender, house hold income, and shopping habits.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "6aUBVjqsy3Lo"
   },
   "source": [
    "# Analyze our final cluster\n",
    "\n",
    "There are 2 options to understand the characteristics of your model.  You can either 1) look in the BigQuery UI, or you can 2) programmatically interact with your model object.  Below you’ll find a simple example for the latter option.  \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "Uo6wjkebuOVB"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<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>centroid_id</th>\n",
       "      <th>feature</th>\n",
       "      <th>categorical_value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>OS</td>\n",
       "      <td>[{'category': 'Linux', 'value': 0.035714285714...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>gender</td>\n",
       "      <td>[{'category': 'M', 'value': 0.4285714285714285...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>OS</td>\n",
       "      <td>[{'category': 'iOS', 'value': 0.04276315789473...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2</td>\n",
       "      <td>gender</td>\n",
       "      <td>[{'category': 'F', 'value': 0.4967105263157895...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3</td>\n",
       "      <td>OS</td>\n",
       "      <td>[{'category': 'iOS', 'value': 0.09637391424238...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   centroid_id feature                                  categorical_value\n",
       "0            1      OS  [{'category': 'Linux', 'value': 0.035714285714...\n",
       "1            1  gender  [{'category': 'M', 'value': 0.4285714285714285...\n",
       "2            2      OS  [{'category': 'iOS', 'value': 0.04276315789473...\n",
       "3            2  gender  [{'category': 'F', 'value': 0.4967105263157895...\n",
       "4            3      OS  [{'category': 'iOS', 'value': 0.09637391424238..."
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "model_to_use = 'kmeans_clusters_5' # User can edit this\n",
    "final_model = DATA_SET_ID+'.'+model_to_use\n",
    "\n",
    "sql_get_attributes = f'''\n",
    "SELECT\n",
    "  centroid_id,\n",
    "  feature,\n",
    "  categorical_value\n",
    "FROM\n",
    "  ML.CENTROIDS(MODEL {final_model})\n",
    "WHERE\n",
    "  feature IN ('OS','gender')\n",
    "'''\n",
    "\n",
    "job_config = bigquery.QueryJobConfig()\n",
    "\n",
    "# Start the query\n",
    "query_job = client.query(sql_get_attributes, job_config=job_config) #API Request\n",
    "df_attributes = query_job.result()\n",
    "df_attributes = df_attributes.to_dataframe()\n",
    "df_attributes.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<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>centroid_id</th>\n",
       "      <th>Total_Users</th>\n",
       "      <th>Apparel</th>\n",
       "      <th>Office</th>\n",
       "      <th>Electronics</th>\n",
       "      <th>LimitedSupply</th>\n",
       "      <th>Accessories</th>\n",
       "      <th>ShopByBrand</th>\n",
       "      <th>Bags</th>\n",
       "      <th>Total_Purchases</th>\n",
       "      <th>productPrice_USD</th>\n",
       "      <th>hhi</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>31</td>\n",
       "      <td>90.800</td>\n",
       "      <td>11.200</td>\n",
       "      <td>9.300</td>\n",
       "      <td>4.000</td>\n",
       "      <td>5.900</td>\n",
       "      <td>1.600</td>\n",
       "      <td>1.300</td>\n",
       "      <td>158.900</td>\n",
       "      <td>3486.300</td>\n",
       "      <td>77857.100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>344</td>\n",
       "      <td>14.600</td>\n",
       "      <td>5.100</td>\n",
       "      <td>5.700</td>\n",
       "      <td>2.400</td>\n",
       "      <td>0.800</td>\n",
       "      <td>3.000</td>\n",
       "      <td>3.100</td>\n",
       "      <td>44.700</td>\n",
       "      <td>1268.700</td>\n",
       "      <td>75296.100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>7198</td>\n",
       "      <td>1.900</td>\n",
       "      <td>0.600</td>\n",
       "      <td>0.500</td>\n",
       "      <td>0.400</td>\n",
       "      <td>0.200</td>\n",
       "      <td>0.200</td>\n",
       "      <td>0.200</td>\n",
       "      <td>5.500</td>\n",
       "      <td>153.600</td>\n",
       "      <td>74898.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>301</td>\n",
       "      <td>4.100</td>\n",
       "      <td>2.500</td>\n",
       "      <td>1.600</td>\n",
       "      <td>1.600</td>\n",
       "      <td>7.300</td>\n",
       "      <td>0.400</td>\n",
       "      <td>0.000</td>\n",
       "      <td>22.300</td>\n",
       "      <td>383.700</td>\n",
       "      <td>75415.200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000</td>\n",
       "      <td>340.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>354.000</td>\n",
       "      <td>2022.900</td>\n",
       "      <td>75000.000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   centroid_id  Total_Users  Apparel  Office  Electronics  LimitedSupply  \\\n",
       "0            1           31   90.800  11.200        9.300          4.000   \n",
       "1            2          344   14.600   5.100        5.700          2.400   \n",
       "2            3         7198    1.900   0.600        0.500          0.400   \n",
       "3            4          301    4.100   2.500        1.600          1.600   \n",
       "4            5            1    0.000 340.000        0.000          0.000   \n",
       "\n",
       "   Accessories  ShopByBrand  Bags  Total_Purchases  productPrice_USD       hhi  \n",
       "0        5.900        1.600 1.300          158.900          3486.300 77857.100  \n",
       "1        0.800        3.000 3.100           44.700          1268.700 75296.100  \n",
       "2        0.200        0.200 0.200            5.500           153.600 74898.000  \n",
       "3        7.300        0.400 0.000           22.300           383.700 75415.200  \n",
       "4        0.000        0.000 0.000          354.000          2022.900 75000.000  "
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# get numerical information about clusters\n",
    "\n",
    "sql_get_numerical_attributes = f'''\n",
    "WITH T AS (\n",
    "SELECT \n",
    "  centroid_id,\n",
    "  ARRAY_AGG(STRUCT(feature AS name, \n",
    "                   ROUND(numerical_value,1) AS value) \n",
    "                   ORDER BY centroid_id) \n",
    "                   AS cluster\n",
    "FROM ML.CENTROIDS(MODEL {final_model})\n",
    "GROUP BY centroid_id\n",
    "),\n",
    "\n",
    "Users AS(\n",
    "SELECT\n",
    "  centroid_id,\n",
    "  COUNT(*) AS Total_Users\n",
    "FROM(\n",
    "SELECT\n",
    "  * EXCEPT(nearest_centroids_distance)\n",
    "FROM\n",
    "  ML.PREDICT(MODEL {final_model},\n",
    "    (\n",
    "    SELECT\n",
    "      *\n",
    "    FROM\n",
    "      {final_view.full_table_id.replace(\":\", \".\")}\n",
    "      )))\n",
    "GROUP BY centroid_id\n",
    ")\n",
    "\n",
    "SELECT\n",
    "  centroid_id,\n",
    "  Total_Users,\n",
    "  (SELECT value from unnest(cluster) WHERE name = 'Apparel') AS Apparel,\n",
    "  (SELECT value from unnest(cluster) WHERE name = 'Office') AS Office,\n",
    "  (SELECT value from unnest(cluster) WHERE name = 'Electronics') AS Electronics,\n",
    "  (SELECT value from unnest(cluster) WHERE name = 'LimitedSupply') AS LimitedSupply,\n",
    "  (SELECT value from unnest(cluster) WHERE name = 'Accessories') AS Accessories,\n",
    "  (SELECT value from unnest(cluster) WHERE name = 'ShopByBrand') AS ShopByBrand,\n",
    "  (SELECT value from unnest(cluster) WHERE name = 'Bags') AS Bags,\n",
    "  (SELECT value from unnest(cluster) WHERE name = 'productPrice_USD') AS productPrice_USD,\n",
    "  (SELECT value from unnest(cluster) WHERE name = 'hhi') AS hhi\n",
    "\n",
    "FROM T LEFT JOIN Users USING(centroid_id)\n",
    "ORDER BY centroid_id ASC\n",
    "'''\n",
    "\n",
    "job_config = bigquery.QueryJobConfig()\n",
    "\n",
    "# Start the query\n",
    "query_job = client.query(sql_get_numerical_attributes, job_config=job_config) #API Request\n",
    "df_numerical_attributes = query_job.result()\n",
    "df_numerical_attributes = df_numerical_attributes.to_dataframe()\n",
    "df_numerical_attributes.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "t95SlHjlx3Qn"
   },
   "source": [
    "In addition to the output above, I'll note a few insights we get from our clusters. \n",
    "\n",
    "Cluster 1 - The apparel shopper, which also purchases more often than normal.  This (although synthetic data) segment skews female.\n",
    "\n",
    "Cluster 2 - Most likely to shop by brand, and interested in bags. This segment has fewer purchases on average than the first cluster, however, this is the highest value customer.\n",
    "\n",
    "Cluster 3 - The most populated cluster, this one has a small amount of purchases and spends less on average.  This segment is the one time buyer, rather than the brand loyalist.  \n",
    "\n",
    "Cluster 4 - Most interested in accessories, does not buy as often as cluster 1 and 2, however buys more than cluster 3.  \n",
    "\n",
    "Cluster 5 - This is an outlier as only 1 person belongs to this group.  "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "Cv0hMz3rcb1s"
   },
   "source": [
    "# Use model to group new website behavior, and then push results to GA360 for marketing activation\n",
    "\n",
    "After we have a finalized model, we want to use it for inference.  The code below outlines how to score or assign users into clusters.  These are labeled as the CENTROID_ID.  Although this by itself is helpful, we also recommend a process to ingest these scores back into GA360. The easiest way to export your BigQuery ML predictions from a BigQuery table to Google Analytics 360 is to use the MoDeM (Model Deployment for Marketing, https://github.com/google/modem) reference implementation. MoDeM helps you load data into Google Analytics for eventual activation in Google Ads, Display & Video 360 and Search Ads 360."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "-fAsssnWnv5C",
    "outputId": "a4fd7009-c808-4f42-972c-8fb9d1a34bde"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<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>CENTROID_ID</th>\n",
       "      <th>fullVisitorID</th>\n",
       "      <th>Hashed_fullVisitorID</th>\n",
       "      <th>OS</th>\n",
       "      <th>Apparel</th>\n",
       "      <th>Office</th>\n",
       "      <th>Electronics</th>\n",
       "      <th>LimitedSupply</th>\n",
       "      <th>Accessories</th>\n",
       "      <th>ShopByBrand</th>\n",
       "      <th>Bags</th>\n",
       "      <th>productPrice_USD</th>\n",
       "      <th>gender</th>\n",
       "      <th>hhi</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5</td>\n",
       "      <td>3355435945434430291</td>\n",
       "      <td>2759439800079197041</td>\n",
       "      <td>Macintosh</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>1079.880</td>\n",
       "      <td>F</td>\n",
       "      <td>65000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   CENTROID_ID        fullVisitorID  Hashed_fullVisitorID         OS  Apparel  \\\n",
       "0            5  3355435945434430291   2759439800079197041  Macintosh        0   \n",
       "\n",
       "   Office  Electronics  LimitedSupply  Accessories  ShopByBrand  Bags  \\\n",
       "0       0            0              6            0            0     5   \n",
       "\n",
       "   productPrice_USD gender    hhi  \n",
       "0          1079.880      F  65000  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql_score = f'''\n",
    "SELECT * EXCEPT(nearest_centroids_distance)\n",
    "FROM\n",
    "  ML.PREDICT(MODEL {final_model},\n",
    "    (\n",
    "    SELECT\n",
    "      *\n",
    "    FROM\n",
    "      {final_view.full_table_id.replace(\":\", \".\")}\n",
    "      LIMIT 1))\n",
    "'''\n",
    "\n",
    "job_config = bigquery.QueryJobConfig()\n",
    "\n",
    "# Start the query\n",
    "query_job = client.query(sql_score, job_config=job_config) #API Request\n",
    "df_score = query_job.result()\n",
    "df_score = df_score.to_dataframe()\n",
    "\n",
    "df_score"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "lnHw5kQUYfkK"
   },
   "source": [
    "# Clean up: Delete all models and tables "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 238
    },
    "colab_type": "code",
    "id": "fJ8VwVcMYlW7",
    "outputId": "babbcc21-9ea3-4a4a-d107-6871aaa82bd9"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Deleted: bqml_kmeans.kmeans_clusters_10\n",
      "Deleted: bqml_kmeans.kmeans_clusters_11\n",
      "Deleted: bqml_kmeans.kmeans_clusters_12\n",
      "Deleted: bqml_kmeans.kmeans_clusters_13\n",
      "Deleted: bqml_kmeans.kmeans_clusters_14\n",
      "Deleted: bqml_kmeans.kmeans_clusters_15\n",
      "Deleted: bqml_kmeans.kmeans_clusters_3\n",
      "Deleted: bqml_kmeans.kmeans_clusters_4\n",
      "Deleted: bqml_kmeans.kmeans_clusters_5\n",
      "Deleted: bqml_kmeans.kmeans_clusters_6\n",
      "Deleted: bqml_kmeans.kmeans_clusters_7\n",
      "Deleted: bqml_kmeans.kmeans_clusters_8\n",
      "Deleted: bqml_kmeans.kmeans_clusters_9\n",
      "Deleted: bqml_kmeans.test\n"
     ]
    }
   ],
   "source": [
    "# Are you sure you want to do this? This is to delete all models\n",
    "\n",
    "models = client.list_models(DATA_SET_ID) # Make an API request.\n",
    "for model in models:\n",
    "    full_model_id = f\"{model.dataset_id}.{model.model_id}\"\n",
    "    client.delete_model(full_model_id)  # Make an API request.\n",
    "    print(f\"Deleted: {full_model_id}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 51
    },
    "colab_type": "code",
    "id": "CAXrHpAJYwCI",
    "outputId": "969f2d34-a56c-4e7b-c4c3-230f7d8bbecc"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Deleted: bqml_kmeans.CRM_View\n",
      "Deleted: bqml_kmeans.Final_View\n",
      "Deleted: bqml_kmeans.GA360_View\n"
     ]
    }
   ],
   "source": [
    "# Are you sure you want to do this? This is to delete all tables and views\n",
    "\n",
    "tables = client.list_tables(DATA_SET_ID)  # Make an API request.\n",
    "for table in tables:\n",
    "    full_table_id = f\"{table.dataset_id}.{table.table_id}\"\n",
    "    client.delete_table(full_table_id)  # Make an API request.\n",
    "    print(f\"Deleted: {full_table_id}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "LYucZEztyk2K"
   },
   "source": [
    "# Wrapping it all up"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "OfzOUkdiytXm"
   },
   "source": [
    "In this exercise, we’ve accomplished some cool things with k-means in BigQuery ML.  Most notably, we’re able to join online and offline user level information to gain more insight into a holistic view of our customers.  We’ve modeled user behavior, and detailed an approach to determine the optimal number of clusters.  We’re able to take this insight and apply to future behavior through inference.  Finally, we can import this inference score back into GA360 for future marketing campaigns. "
   ]
  }
 ],
 "metadata": {
  "colab": {
   "collapsed_sections": [],
   "name": "BQML_Scaled_Clustering_TC_Draft.ipynb",
   "provenance": [],
   "toc_visible": true
  },
  "environment": {
   "name": "common-cpu.m54",
   "type": "gcloud",
   "uri": "gcr.io/deeplearning-platform-release/base-cpu:m54"
  },
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.3-final"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}