UseCases/Hyper_Personalization/Hyper_Personalization_PY_SQL.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "78ad8a32",
   "metadata": {},
   "source": [
    "<header>\n",
    "   <p  style='font-size:36px;font-family:Arial; color:#F0F0F0; background-color: #00233c; padding-left: 20pt; padding-top: 20pt;padding-bottom: 10pt; padding-right: 20pt;'>\n",
    "       Hyper Personalization\n",
    "  <br>\n",
    "       <img id=\"teradata-logo\" src=\"https://storage.googleapis.com/clearscape_analytics_demo_data/DEMO_Logo/teradata.svg\" alt=\"Teradata\" style=\"width: 125px; height: auto; margin-top: 20pt;\">\n",
    "    </p>\n",
    "</header>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5e4b3b21-73d1-4e48-9f37-f33ddcb31d7f",
   "metadata": {},
   "source": [
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>Introduction</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "Customer experience (CX) refers to the way in which customers perceive an organization's ability to meet their expectations during every interaction. In today's rapidly evolving technological landscape, companies such as Amazon and Uber have revolutionized CX and raised the standards significantly. As a result, customers now have heightened expectations, seeking quicker, smoother, and highly personalized experiences. These factors play a crucial role in determining whether a consumer will remain loyal to a brand or opt to switch to a competitor.<br>\n",
    "As an increasing number of individuals transition to digital channels, the significance of providing outstanding customer experiences has grown immensely. In a crowded digital landscape, businesses must strive to deliver exceptional CX in order to distinguish themselves. This necessitates a fundamental reassessment of how organizations can utilize data-driven approaches to create hyper-personalized experiences for individuals in digital channels. Placing the customer at the forefront of every decision is crucial in this endeavor.<br>\n",
    "Delivering exceptional customer experiences necessitates a significant shift in capabilities, moving away from a focus on product offers and tailored offers for customer segments. Instead, the goal is to provide highly personalized experiences to individual customers based on the specific context of their current interaction, in real time. This shift emphasizes the importance of understanding each customer's unique needs and preferences, enabling organizations to deliver tailored experiences that truly resonate with individuals on a one-to-one basis.<br>To achieve hyper-personalization, a thorough comprehension of customers and anticipation of their needs are essential. This necessitates the use of advanced analytics to scientifically optimize offers in real time. Hyper-personalization entails a paradigm shift from managing a limited number of campaigns per year to tailoring millions, or even billions, of interactions. Accomplishing this feat will rely on effectively implementing AI/ML models on a large scale.<br>\n",
    " <blockquote 'font-size:20px;'>It is easy to reach 1 billion decisions, from considering just 1 million customers when there are 200 possible messages across 5 different channels. </blockquote>                                                         <p style = 'font-size:16px;font-family:Arial;color:#00233C'>Customers are dynamic entities, with ever-changing needs and interests influenced by various factors such as time of day, location, weather, and more. Hyper-personalization entails the ability to swiftly respond to these changing needs by delivering personalized content in real-time. Organizations that excel in hyper-personalization take into account each individual's unique behavior and real-time context to accurately anticipate and meet their specific requirements.<br>This necessitates a shift from a single generic model to a personalized model for each individual, leading to challenges in data management, scalability, and deployment. To effectively tackle these challenges and achieve hyper-personalization, businesses must establish three key capabilities:    \n",
    "<ol style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "<li>Listen to customers with continuous capture of data signals for a comprehensive customer 360.</li> \n",
    "<li>Decide the Next Best Offer (NBO) for each individual in real time by developing and deploying millions of personalized models.</li> \n",
    "<li>Activate these NBOs by integrating with the Martech stack to deliver personalized interactions. With Vantage and ClearScape analytics we enable our customers to Listen, Decide and Act seamlessly</li>\n",
    "        </ol>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>        \n",
    "Let's demo this use case with sample telco data for customer churn prediction.\n",
    "</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "22173c1b-a4eb-4cd7-b0ae-ba68bc39aba2",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<b style = 'font-size:20px;font-family:Arial;color:#00233C'>1. Connect to Vantage</b>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We import the required libraries, set environment variables and environment paths (if required).</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5199b325-11f8-4dbd-a13f-fadf334c58c6",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Standard Libraries\n",
    "import matplotlib.pyplot as plt\n",
    "import matplotlib.ticker as mtick\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import getpass\n",
    "\n",
    "# Teradata Libraries\n",
    "from teradataml import *\n",
    "import teradataml as tdml\n",
    "from teradataml.dataframe.sql_functions import case\n",
    "\n",
    "# Data Visualization Libraries\n",
    "import plotly.express as px\n",
    "from plotly.subplots import make_subplots\n",
    "import plotly.graph_objects as go\n",
    "import seaborn as sns\n",
    "\n",
    "# Miscellaneous Libraries\n",
    "from IPython.display import display, Markdown\n",
    "from sqlalchemy import func\n",
    "\n",
    "# Machine Learning Metrics\n",
    "from sklearn.metrics import mean_absolute_error, roc_auc_score, roc_curve\n",
    "\n",
    "# Configuration\n",
    "tdml.display.max_rows = 5\n",
    "configure.val_install_location = 'val'"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b7acf033-410d-4af5-9dce-e3082f5eabd5",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>You will be prompted to provide the password. Enter your password, press the Enter key, and then use the down arrow to go to the next cell.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "24635d87-9b61-4ad4-92ac-7193c2b7679c",
   "metadata": {},
   "outputs": [],
   "source": [
    "%run -i ../startup.ipynb\n",
    "eng = create_context(host = 'host.docker.internal', username = 'demo_user', password = password)\n",
    "print(eng)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "eba34c26-c874-49ad-9360-4cf1ab006ffb",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%capture\n",
    "execute_sql('''SET query_band='DEMO=Hyper_Personalization_PY_SQL.ipynb;' UPDATE FOR SESSION;''')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6d8f8388-e318-4bf4-84c1-7dfcf2d1dd40",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'> <b>Getting Data for This Demo</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We have provided data for this demo on cloud storage. You have the option of either running the demo using foreign tables to access the data without using any storage on your environment or downloading the data to local storage which may yield somewhat faster execution, but there could be considerations of available storage. There are two statements in the following cell, and one is commented out. You may switch which mode you choose by changing the comment string.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b3460a89-d570-488e-a97a-39423afa7b27",
   "metadata": {},
   "outputs": [],
   "source": [
    "# %run -i ../run_procedure.py \"call get_data('DEMO_Telco_cloud');\"    # Takes 1 minute\n",
    "%run -i ../run_procedure.py \"call get_data('DEMO_Telco_local');\"    # Takes 2 minutes"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "53237f92-8be4-40eb-941b-6c5b9a778146",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Next is an optional step – if you want to see status of databases/tables created and space used.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1dbc1f31-38b7-4d42-8573-5f05767c1591",
   "metadata": {},
   "outputs": [],
   "source": [
    "%run -i ../run_procedure.py \"call space_report();\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bdd6dd8c",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>2. Data Exploration</b></p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "13288769-f1b3-40a5-8cad-95e5f4ae92fd",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Customer Churn</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Create a \"Virtual DataFrame\" that points to the data set in Vantage. Check the shape of the dataframe as check the datatypes of all the columns of the dataframe.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d40df274-d9cb-439b-93bb-343d317f052c",
   "metadata": {},
   "outputs": [],
   "source": [
    "tdf = DataFrame(in_schema(\"DEMO_Telco\", \"Customer_Churn\"))\n",
    "tdf"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1d620292-c936-4546-89eb-59fd50c35221",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Check the demographics of data</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "279ee6be-9288-41ae-b21a-f2389add4623",
   "metadata": {},
   "outputs": [],
   "source": [
    "print(\"Shape of the data: \", tdf.shape)\n",
    "tdf.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4d9927f9-f0f6-4f45-966d-7b7b2ca36f84",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'> As we can see from shape and info methods our dataset has 7043 rows with 21 columns</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b03454cf-d47a-4edc-aea4-5b517b7da9d6",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C;color:#00233C'><b>Summary of columns</b><br>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C;'>The <b>ColumnSummary</b> function can be used to take a quick look at the columns, their datatypes, and summary of NULLs/non-NULLs for a given table.   </p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "21eece2c-533a-40e3-bcad-4ed4bb2b6cf3",
   "metadata": {},
   "outputs": [],
   "source": [
    "obj = ColumnSummary(\n",
    "                    data = tdf,\n",
    "                    target_columns = [':']\n",
    ")\n",
    "\n",
    "obj.result"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a91e3850-12c5-4b74-b17d-852092e81925",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>3. Exploratory Data Analysis</b></p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "befaaeb8-fab9-43f6-8a0a-efdb0e486377",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "Exploratory Data Analysis (EDA) refers to the process of visually and statistically examining, analyzing, and summarizing data to understand its characteristics, patterns, and relationships.<br>As Vantage is not a visualization tool we will use Python libraries for visualization. Here we can see how seamless it is to use Teradata Vantage with python.<p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b8442ced-4c20-4fd7-8604-152f11a1ab92",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Converting teradata dataframe to pandas dataframe for visualization\n",
    "df = tdf.to_pandas().reset_index()\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "07b54581-8f05-4ef4-bc99-08e66d92a89b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# customerid is unique for each row hence don't have any effect on the predictions\n",
    "df = df.drop(['CustomerID'], axis = 1 )\n",
    "# new column based on income band\n",
    "df['MonthlyIncomeBand'] = np.where(df['MonthlyCharges'] >= 60, 'High', 'Low')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "15803a1f-68ac-45d5-b14f-9fd88da10b42",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Gender and Churn percentage distribution\n",
    "g_labels = ['Male', 'Female']\n",
    "c_labels = ['No', 'Yes']\n",
    "# Create subplots: use 'domain' type for Pie subplot\n",
    "fig = make_subplots(rows = 1, cols = 2, specs = [[{'type':'domain'}, {'type':'domain'}]])\n",
    "fig.add_trace(go.Pie(labels = g_labels, values = df['Gender'].value_counts(), name = \"Gender\"),\n",
    "              1, 1)\n",
    "fig.add_trace(go.Pie(labels = c_labels, values = df['Churn'].value_counts(), name = \"Churn\"),\n",
    "              1, 2)\n",
    "\n",
    "# Use `hole` to create a donut-like pie chart\n",
    "fig.update_traces(hole = .4, hoverinfo = \"label+percent+name\", textfont_size = 16)\n",
    "\n",
    "fig.update_layout(\n",
    "    title_text = \"Gender and Churn Distributions\",\n",
    "    # Add annotations in the center of the donut pies.\n",
    "    annotations = [dict(text = 'Gender', x = 0.16, y = 0.5, font_size = 20, showarrow = False),\n",
    "                 dict(text = 'Churn', x = 0.84, y = 0.5, font_size = 20, showarrow = False)])\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6d394641-ba1c-44de-9db3-be2286aa3d13",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>26.6 % of customers switched to another firm.<br>\n",
    "Customers are 49.5 % female and 50.5 % male.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "273398e1-e20b-47fd-bc6f-9bad2b893080",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Churn per Gender\n",
    "plt.figure(figsize = (6, 6))\n",
    "labels = [\"Churn: Yes\", \"Churn:No\"]\n",
    "values = [1869, 5163]\n",
    "labels_gender = [\"F\", \"M\", \"F\", \"M\"]\n",
    "sizes_gender = [939, 930, 2544, 2619]\n",
    "colors = ['#ff6666', '#66b3ff']\n",
    "colors_gender = ['#c2c2f0', '#ffb3e6', '#c2c2f0', '#ffb3e6']\n",
    "explode = (0.3, 0.3) \n",
    "explode_gender = (0.1, 0.1, 0.1, 0.1)\n",
    "textprops = {\"fontsize\": 15}\n",
    "# Plot\n",
    "plt.pie(values, labels = labels, autopct = '%1.1f%%', pctdistance = 1.08,\n",
    "        labeldistance = 0.8, colors = colors, startangle = 90,frame = True,\n",
    "        explode = explode, radius = 10, textprops = textprops, counterclock = True)\n",
    "plt.pie(sizes_gender, labels = labels_gender, colors = colors_gender, startangle = 90,\n",
    "        explode = explode_gender, radius = 7, textprops = textprops, counterclock = True)\n",
    "\n",
    "# Draw circle\n",
    "centre_circle = plt.Circle((0, 0), 5, color = 'black', fc = 'white', linewidth = 0)\n",
    "fig = plt.gcf()\n",
    "fig.gca().add_artist(centre_circle)\n",
    "\n",
    "plt.title('Churn Distribution w.r.t Gender: Male(M), Female(F)', fontsize = 15, y = 1.1)\n",
    "\n",
    "# Show plot\n",
    "\n",
    "plt.axis('equal')\n",
    "plt.tight_layout()\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "eedfa546-3b86-4aa8-a4b9-1c47f922c5db",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>There is negligible difference in customer percentage/ count who changed the service provider. Both genders behaved in similar fashion when it comes to migrating to another service provider.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "929fa87d-4795-4113-9c70-a78f4a9e318c",
   "metadata": {},
   "outputs": [],
   "source": [
    "fig = px.histogram(df, x = \"Churn\", color = \"Contract\", barmode = \"group\", title = \"<b>Customer contract distribution<b>\")\n",
    "fig.update_layout(width = 700, height = 500, bargap = 0.1)\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cb97cf05-b143-43e9-bf3a-b7ed267c1ad7",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'> About 75% of customer with Month-to-Month Contract opted to move out as compared to 13% of customers with One Year Contract and 3% with Two Year Contract</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ba271d31-1109-4160-a323-4bb4807253da",
   "metadata": {},
   "outputs": [],
   "source": [
    "labels = df['PaymentMethod'].unique()\n",
    "values = df['PaymentMethod'].value_counts()\n",
    "\n",
    "fig = go.Figure(data = [go.Pie(labels = labels, values = values, hole = .3)])\n",
    "fig.update_layout(title_text = \"<b>Payment Method Distribution</b>\")\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "67b6230a-bdc4-4c35-8c5d-e1c890916407",
   "metadata": {},
   "outputs": [],
   "source": [
    "fig = px.histogram(df, x = \"Churn\", color = \"PaymentMethod\", title = \"<b>Customer Payment Method distribution w.r.t. Churn</b>\")\n",
    "fig.update_layout(width = 700, height = 500, bargap = 0.1)\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8fd84375-3d85-47cd-9076-6a6b8ea3e496",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Major customers who moved out were having Electronic Check as Payment Method.\n",
    "<br>Customers who opted for Credit-Card automatic transfer or Bank Automatic Transfer and Mailed Check as Payment Method were less likely to move out.  </p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "75ce1bcf-d151-4929-8b98-0454c5330c43",
   "metadata": {},
   "outputs": [],
   "source": [
    "fig = go.Figure()\n",
    "\n",
    "fig.add_trace(go.Bar(\n",
    "  x = [['Churn: No', 'Churn: No', 'Churn: Yes', 'Churn: Yes'],\n",
    "       [\"Female\", \"Male\", \"Female\", \"Male\"]],\n",
    "  y = [965, 992, 219, 240],\n",
    "  name = 'DSL',\n",
    "))\n",
    "\n",
    "fig.add_trace(go.Bar(\n",
    "  x = [['Churn: No', 'Churn: No', 'Churn: Yes', 'Churn: Yes'],\n",
    "       [\"Female\", \"Male\", \"Female\", \"Male\"]],\n",
    "  y = [889, 910, 664, 633],\n",
    "  name = 'Fiber optic',\n",
    "))\n",
    "\n",
    "fig.add_trace(go.Bar(\n",
    "  x = [['Churn: No', 'Churn: No', 'Churn: Yes', 'Churn: Yes'],\n",
    "       [\"Female\", \"Male\", \"Female\", \"Male\"]],\n",
    "  y = [690, 717, 56, 57],\n",
    "  name = 'No Internet',\n",
    "))\n",
    "\n",
    "fig.update_layout(title_text = \"<b>Churn Distribution w.r.t. Internet Service and Gender</b>\")\n",
    "\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1bc9f214-7877-43ad-853a-c780e6e22dba",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'> A lot of customers choose the Fiber optic service and it's also evident that the customers who use Fiber optic have high churn rate, this might suggest a dissatisfaction with this type of internet service.\n",
    "<br> Customers having DSL service are majority in number and have less churn rate compared to Fiber optic service.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bbbc934e-2f22-4ea2-842f-9f7b704cd62c",
   "metadata": {},
   "outputs": [],
   "source": [
    "color_map = {\"Yes\": \"#FF97FF\", \"No\": \"#AB63FA\"}\n",
    "fig = px.histogram(df, x = \"Churn\", color = \"Dependents\", barmode = \"group\",\n",
    "                   title = \"<b>Dependents distribution</b>\", color_discrete_map = color_map)\n",
    "fig.update_layout(width = 700, height = 500, bargap = 0.1)\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4af82750-0c91-41e9-9ec6-06f3e82eefc1",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Customers without dependents are more likely to churn</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7d325388-5221-4f83-b07d-88481d09d2f9",
   "metadata": {},
   "outputs": [],
   "source": [
    "color_map = {\"Yes\": '#FFA15A', \"No\": '#00CC96'}\n",
    "fig = px.histogram(df, x = \"Churn\", color = \"Partner\", barmode = \"group\",\n",
    "                   title = \"<b>Chrun distribution w.r.t. Partners</b>\", color_discrete_map = color_map)\n",
    "fig.update_layout(width = 700, height = 500, bargap = 0.1)\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "649567b1-3232-49bf-840a-8518b38c29b4",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Customers that don't have partners are more likely to churn</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1ae5d017-8d5a-49f7-ba99-8c2b528b253d",
   "metadata": {},
   "outputs": [],
   "source": [
    "color_map = {\"Yes\": '#FFA15A', \"No\": '#00CC96'}\n",
    "fig = px.histogram(df, x = \"Churn\", color = \"PaperlessBilling\",\n",
    "                   title = \"<b>Chrun distribution w.r.t. Paperless Billing</b>\", color_discrete_map = color_map)\n",
    "fig.update_layout(width = 700, height = 500, bargap = 0.1)\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fd6697e7-1dcc-44b1-9428-290856a1cb0c",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Customers with Paperless Billing are most likely to churn.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e37015ca-d148-4627-938b-c02f0b3bb399",
   "metadata": {},
   "outputs": [],
   "source": [
    "sns.set_context(\"paper\", font_scale = 1.1)\n",
    "ax = sns.kdeplot(df.MonthlyCharges[(df[\"Churn\"] == 'No') ],\n",
    "                color = \"Red\", shade = True);\n",
    "ax = sns.kdeplot(df.MonthlyCharges[(df[\"Churn\"] == 'Yes') ],\n",
    "                ax = ax, color = \"Blue\", shade = True);\n",
    "ax.legend([\"Not Churn\", \"Churn\"], loc = 'upper right');\n",
    "ax.set_ylabel('Density');\n",
    "ax.set_xlabel('Monthly Charges');\n",
    "ax.set_title('Distribution of monthly charges by churn');"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "602c0e21-d2da-40f3-b7d1-f63dec5c9df9",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Customers with higher Monthly Charges are also more likely to churn</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3bb60368-00f9-458f-9a3d-9521a27052a0",
   "metadata": {},
   "outputs": [],
   "source": [
    "ax = sns.kdeplot(df.TotalCharges[(df[\"Churn\"] == 'No') ],\n",
    "                color = \"Gold\", shade = True);\n",
    "ax = sns.kdeplot(df.TotalCharges[(df[\"Churn\"] == 'Yes') ],\n",
    "                ax = ax, color = \"Green\", shade = True);\n",
    "ax.legend([\"Not Churn\", \"Churn\"],loc = 'upper right');\n",
    "ax.set_ylabel('Density');\n",
    "ax.set_xlabel('Total Charges');\n",
    "ax.set_title('Distribution of total charges by churn');"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b7df170c-da51-4ab8-b263-b5a2bea02c12",
   "metadata": {},
   "outputs": [],
   "source": [
    "fig = px.box(df, x = 'Churn', y = 'Tenure')\n",
    "\n",
    "# Update yaxis properties\n",
    "fig.update_yaxes(title_text = 'Tenure (Months)', row = 1, col = 1)\n",
    "# Update xaxis properties\n",
    "fig.update_xaxes(title_text = 'Churn', row = 1, col = 1)\n",
    "\n",
    "# Update size and title\n",
    "fig.update_layout(autosize = True, width = 750, height = 600,\n",
    "    title_font = dict(size = 25, family = 'Courier'),\n",
    "    title = '<b>Tenure vs Churn</b>',\n",
    ")\n",
    "\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9a01ee8b-f894-4785-96ad-ac25998a218e",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>New customers are more likely to churn</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5f8ebfb6-c8c2-404f-8e6d-4e7724111745",
   "metadata": {},
   "outputs": [],
   "source": [
    "color_map = {\"Yes\": '#FFA15A', \"No\": '#00CC96'}\n",
    "fig = px.histogram(df, x = \"Churn\", color = \"MonthlyIncomeBand\",\n",
    "                   title = \"<b>Chrun distribution w.r.t. Monthly Income Band</b>\", color_discrete_map = color_map)\n",
    "fig.update_layout(width = 700, height = 500, bargap = 0.1)\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a60f337b-72fc-4f91-8a1d-b30b2cc292d9",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Customers in high income band are more likely are more likely to churn.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1f374c91-aa2f-49ed-8031-dfa564e64f9b",
   "metadata": {},
   "outputs": [],
   "source": [
    "plt.figure(figsize = (25, 10))\n",
    "\n",
    "corr = df.apply(lambda x: pd.factorize(x)[0]).corr()\n",
    "\n",
    "mask = np.triu(np.ones_like(corr, dtype = bool))\n",
    "\n",
    "ax = sns.heatmap(corr, mask = mask, xticklabels = corr.columns, yticklabels = corr.columns,\n",
    "                 annot = True, linewidths = .2, cmap = 'coolwarm', vmin = -1, vmax = 1)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "86de006c-ec54-4517-bab5-53563ce02254",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The above chart shows the heatmap of correlation of all the attributes with each other. Each variable is represented by a row and a column, and the cells show the correlation between them.The value of correlation can take any value from -1 to 1.<br>We have done our exploratory data analysis let us now create our prediction model.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fa5ea57f-b8ba-44eb-9d9b-cf07d37b77b5",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>4. Data Preprocessing</b></p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b58490d2-1f7a-4941-a62a-e99a4b7f7543",
   "metadata": {},
   "source": [
    " <p style = 'font-size:16px;font-family:Arial;color:#00233C'>Before the data can be used for model creation; we will need to do some data cleansing and transformation on it. We can do this InDb with Teradata Vantage's inbuilt functions.<br>The <b>CategoricalSummary</b> function displays the distinct values and their counts for each specified input DataFrame column.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ddf75f76-eb84-4f9d-856b-4051f0df6d70",
   "metadata": {},
   "outputs": [],
   "source": [
    "CatSum = CategoricalSummary(data = tdf, target_columns = [\"MultipleLines\", \"InternetService\", \"OnlineSecurity\", \"OnlineBackup\",\n",
    "                                                          \"DeviceProtection\", \"TechSupport\",\"StreamingTV\", \"StreamingMovies\"])\n",
    "CatSum.result.sort(\"ColumnName\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "84680551-fbca-45e4-83e9-c5f90fa3078b",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "As we can see from the sample data above and the categorical summary values, the columns </p>\n",
    "<ul style = 'font-size:16px;font-family:Arial;color:#00233C'><li>OnlineSecurity </li>  \n",
    "<li>OnlineBackup</li>     \n",
    "<li>DeviceProtection</li> \n",
    "<li>TechSupport</li>      \n",
    "<li>StreamingTV</li>      \n",
    "<li>StreamingMovies</li>\n",
    "</ul><p style = 'font-size:16px;font-family:Arial;color:#00233C'>are related to InternetService, wherever InternetService value is \"No\" the column have value of \"No internet service\". For our model let us replace \"No internet service\" to No in our  column. We will do similar operation for replacing \"No phone service\" to \"No\".</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9f0f4876-7f8d-4177-8afc-f7c474b6ac4a",
   "metadata": {},
   "outputs": [],
   "source": [
    "tdf = tdf.assign(oreplace_MultipleLines = func.oreplace(tdf.MultipleLines.expression, \"No phone service\",\"No\"),\n",
    "                oreplace_OnlineSecurity = func.oreplace(tdf.OnlineSecurity.expression, \"No internet service\",\"No\"),\n",
    "                oreplace_OnlineBackup = func.oreplace(tdf.OnlineBackup.expression, \"No internet service\",\"No\"),\n",
    "                oreplace_DeviceProtection = func.oreplace(tdf.DeviceProtection.expression, \"No internet service\",\"No\"),\n",
    "                oreplace_TechSupport = func.oreplace(tdf.TechSupport.expression, \"No internet service\",\"No\"),\n",
    "                oreplace_StreamingTV = func.oreplace(tdf.StreamingTV.expression, \"No internet service\",\"No\"),\n",
    "                oreplace_StreamingMovies = func.oreplace(tdf.StreamingMovies.expression, \"No internet service\",\"No\"))\n",
    "tdf"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "402911fc-5aa6-4e25-8555-495b42101012",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Let's add an additional column MonthlyChargeBand based on the MonthlyCharge Value where >=60 then \"High\" or 1 else \"Low\" or 0</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5d32cfa6-ca33-4a84-bd9b-b0ea0b13a52a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Now lets drop the extra columns, rename the columns in dataframe\n",
    "\n",
    "tdf2 = tdf.assign(\n",
    "    drop_columns = True,\n",
    "    CustomerID = tdf.CustomerID,\n",
    "    Gender = tdf.Gender,\n",
    "    SeniorCitizen = tdf.SeniorCitizen,\n",
    "    Partner = case({\"Yes\": 1, \"No\": 0}, value = tdf.Partner, else_ = 0),\n",
    "    Dependents = tdf.Dependents,\n",
    "    MonthlyChargeBand = case([(tdf.MonthlyCharges >= 60.0, 1)], else_ = 0),\n",
    "    Tenure = tdf.Tenure,\n",
    "    PhoneService = tdf.PhoneService,\n",
    "    MultipleLines = tdf.oreplace_MultipleLines,\n",
    "    InternetService = tdf.InternetService,\n",
    "    OnlineSecurity = tdf.oreplace_OnlineSecurity,\n",
    "    OnlineBackup = tdf.oreplace_OnlineBackup,\n",
    "    DeviceProtection = tdf.oreplace_DeviceProtection,\n",
    "    TechSupport = tdf.oreplace_TechSupport,\n",
    "    StreamingTV = tdf.oreplace_StreamingTV,\n",
    "    StreamingMovies = tdf.oreplace_StreamingMovies,\n",
    "    Contract = tdf.Contract,\n",
    "    PaperlessBilling = tdf.PaperlessBilling,\n",
    "    PaymentMethod = tdf.PaymentMethod,\n",
    "    MonthlyCharges = tdf.MonthlyCharges,\n",
    "    TotalCharges = tdf.TotalCharges,\n",
    "    Churn = case({\"Yes\": 1, \"No\": 0}, value = tdf.Churn, else_ = 0)\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c5bddfa5-6a90-477b-8c38-aa2985736fe6",
   "metadata": {},
   "outputs": [],
   "source": [
    "tdf2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c2400fd0-2dac-47ed-84ca-fb9f7c5631b3",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Copying the intermediate table to database\n",
    "tdf2.to_sql(\"churn\", if_exists = \"replace\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d256d17f-1c10-4bc2-978c-4b9a2a184437",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C;color:#00233C'><b>Onehotencoding & Ordinal encoding</b>.</p> \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>TD_OneHotEncodingFit and Transform </b>and <b>TD_OrdinalEncodingFit and Transform</b> are Teradata's function to convert the categorical attributes to numerical.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c26b1761-c5d0-4819-bc78-aabc6d514a52",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create fit table for onehotencoding\n",
    "query = '''\n",
    "CREATE MULTISET VOLATILE TABLE onehotencodingfittable AS (\n",
    "    SELECT *\n",
    "    FROM TD_OneHotEncodingFit (\n",
    "        ON Churn AS InputTable\n",
    "        USING\n",
    "            TargetColumn (\n",
    "                'Gender',\n",
    "                'Dependents',\n",
    "                'PhoneService',\n",
    "                'MultipleLines',\n",
    "                'OnlineSecurity',\n",
    "                'OnlineBackup',\n",
    "                'DeviceProtection',\n",
    "                'TechSupport',\n",
    "                'StreamingTV',\n",
    "                'StreamingMovies',\n",
    "                'PaperlessBilling'\n",
    "            )\n",
    "            IsInputDense ('true')\n",
    "            CategoryCounts (2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2)\n",
    "            Approach ('Auto')\n",
    "    ) AS dt\n",
    ") WITH DATA\n",
    "ON COMMIT PRESERVE ROWS;\n",
    "'''\n",
    "\n",
    "try:\n",
    "    execute_sql(query)\n",
    "except:\n",
    "    db_drop_table('onehotencodingfittable')\n",
    "    execute_sql(query)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a88d8a51-7432-45e4-8b45-c59196368248",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><i>*note the output from the above command is like \"sqlalchemy.engine.cursor.LegacyCursorResult at xxxxxxxx\" which signifies that the command is executed. As there is no other output to display.</i> </p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2c136b69-41c1-42f1-bf47-89d1dae800a3",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "The other categorical columns </p> \n",
    "<ul style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "<li>InternetService </li>  \n",
    "<li>Contract</li>     \n",
    "<li>PaperlessBilling</li> \n",
    "<li>PaymentMethod</li>      \n",
    "</ul><p style = 'font-size:16px;font-family:Arial;color:#00233C'>have more values where we can apply ordinalencoding on it  </p>       \n",
    "      \n",
    "         "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e57c5ee3-6c02-4d63-a560-91028c80ec64",
   "metadata": {},
   "outputs": [],
   "source": [
    "#create fit table for ordinalencoding\n",
    "query = '''\n",
    "SELECT *\n",
    "FROM TD_OrdinalEncodingFit (\n",
    "    ON Churn AS InputTable\n",
    "    OUT volatile table outputtable (ordinalencodingfittable)\n",
    "    USING\n",
    "        TargetColumn ('InternetService', 'Contract', 'PaperlessBilling', 'PaymentMethod')\n",
    "        DefaultValue (-1)\n",
    ") AS dt;\n",
    "'''\n",
    "\n",
    "try:\n",
    "    execute_sql(query)\n",
    "except:\n",
    "    db_drop_table('ordinalencodingfittable')\n",
    "    execute_sql(query)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7a437c68-b650-4300-a3e8-cd39a5e21054",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C;color:#00233C'><b>Scale the numerical values</b></p><p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>TD_ScaleFit and TDScaleTransform </b>scales specified input table columns i.e perform the specific scale methods like standard deviation, mean etc to the input columns</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f5c58db2-ddac-41a7-bdcd-7943296af85b",
   "metadata": {},
   "outputs": [],
   "source": [
    "#create fit table for scale function\n",
    "query = '''\n",
    "SELECT *\n",
    "FROM TD_scaleFit (\n",
    "    ON Churn AS InputTable\n",
    "    OUT VOLATILE TABLE OutputTable(scaleFitOut)\n",
    "    USING\n",
    "        TargetColumns ('MonthlyCharges', 'TotalCharges')\n",
    "        MissValue ('Keep')\n",
    "        ScaleMethod ('range')\n",
    "        GlobalScale ('f')\n",
    ") AS dt;\n",
    "'''\n",
    "\n",
    "try:\n",
    "    execute_sql(query)\n",
    "except:\n",
    "    db_drop_table('scaleFitOut')\n",
    "    execute_sql(query)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "faec04de-8197-4c10-bcc5-3bc3f605e81e",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C;color:#00233C'><b>Putting it altogether</b></p><p style = 'font-size:16px;font-family:Arial;color:#00233C'>We will use <b> TD_ColumnTransformer</b> function to apply all the transformations from the fit tables created below in one go.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "66901097-ed36-49b9-a385-ecd5b0f33ea7",
   "metadata": {},
   "outputs": [],
   "source": [
    "query = '''\n",
    "CREATE MULTISET TABLE Transformed_data AS (\n",
    "    SELECT \n",
    "        CustomerId,\n",
    "        Churn,\n",
    "        SeniorCitizen,\n",
    "        Partner,\n",
    "        MonthlyChargeBand,\n",
    "        Tenure,\n",
    "        InternetService,\n",
    "        Contract,\n",
    "        PaperlessBilling,\n",
    "        PaymentMethod,\n",
    "        MonthlyCharges,\n",
    "        TotalCharges,\n",
    "        Gender_0,\n",
    "        Gender_1,\n",
    "        Dependents_0,\n",
    "        Dependents_1,\n",
    "        PhoneService_0,\n",
    "        PhoneService_1,\n",
    "        MultipleLines_0,\n",
    "        MultipleLines_1,\n",
    "        OnlineSecurity_0,\n",
    "        OnlineSecurity_1,\n",
    "        OnlineBackup_0,\n",
    "        OnlineBackup_1,\n",
    "        DeviceProtection_0,\n",
    "        DeviceProtection_1,\n",
    "        TechSupport_0,\n",
    "        TechSupport_1,\n",
    "        StreamingTV_0,\n",
    "        StreamingTV_1,\n",
    "        StreamingMovies_0,\n",
    "        StreamingMovies_1,\n",
    "        PaperlessBilling_0,\n",
    "        PaperlessBilling_1\n",
    "    FROM TD_ColumnTransformer (\n",
    "        ON Churn AS inputtable\n",
    "        ON onehotencodingfittable AS Onehotencodingfittable DIMENSION\n",
    "        ON ordinalencodingfittable AS OrdinalEncodingFitTable DIMENSION\n",
    "        ON scaleFitOut AS ScaleFitTable DIMENSION\n",
    "    ) AS dt\n",
    ") WITH DATA;\n",
    "'''\n",
    "\n",
    "try:\n",
    "    execute_sql(query)\n",
    "except:\n",
    "    db_drop_table('Transformed_data')\n",
    "    execute_sql(query)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0756ec64-8992-4dc7-90d8-1a89414d6415",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>5. Segment Creation</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Segmentation is a fundamental step in hyper-personalization strategies as it allows businesses to divide their target audience into smaller, more manageable groups. By creating segments, businesses can tailor their marketing messages, offers, and experiences to resonate with each segment's unique needs and preferences.<br>\n",
    "Based on what we have seen in the profiling and data anlysis (step 2 and 3)- we see that we have key segments of <b>Senior Citizen status, Partner Status and Monthly Charge Band </b>that look highly significant. So we will use these to create segments to build our hyper-Predictive modelling on these segments.<br>Let's create views for each segment on our transformed and scaled dataset.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5d931e0b-35df-4bdb-883d-bd15646d19be",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Segment for SeniorCitizen (where SeniorCitizen equals 1)\n",
    "query_sr = '''\n",
    "REPLACE VIEW SeniorCitizen AS\n",
    "SELECT\n",
    "    CustomerId,\n",
    "    Churn,\n",
    "    Partner,\n",
    "    MonthlyChargeBand,\n",
    "    Tenure,\n",
    "    InternetService,\n",
    "    Contract,\n",
    "    PaperlessBilling,\n",
    "    PaymentMethod,\n",
    "    MonthlyCharges,\n",
    "    TotalCharges,\n",
    "    Gender_0,\n",
    "    Gender_1,\n",
    "    Dependents_0,\n",
    "    Dependents_1,\n",
    "    PhoneService_0,\n",
    "    PhoneService_1,\n",
    "    MultipleLines_0,\n",
    "    MultipleLines_1,\n",
    "    OnlineSecurity_0,\n",
    "    OnlineSecurity_1,\n",
    "    OnlineBackup_0,\n",
    "    OnlineBackup_1,\n",
    "    DeviceProtection_0,\n",
    "    DeviceProtection_1,\n",
    "    TechSupport_0,\n",
    "    TechSupport_1,\n",
    "    StreamingTV_0,\n",
    "    StreamingTV_1,\n",
    "    StreamingMovies_0,\n",
    "    StreamingMovies_1,\n",
    "    PaperlessBilling_0,\n",
    "    PaperlessBilling_1\n",
    "FROM\n",
    "    Transformed_data\n",
    "WHERE\n",
    "    SeniorCitizen = 1;\n",
    "'''\n",
    "\n",
    "#Segment of NotSeniorCitizens (where SeniorCitizen equals 0)\n",
    "query_nsr = '''\n",
    "REPLACE VIEW NotSeniorCitizen AS\n",
    "SELECT\n",
    "    CustomerId,\n",
    "    Churn,\n",
    "    Partner,\n",
    "    MonthlyChargeBand,\n",
    "    Tenure,\n",
    "    InternetService,\n",
    "    Contract,\n",
    "    PaperlessBilling,\n",
    "    PaymentMethod,\n",
    "    MonthlyCharges,\n",
    "    TotalCharges,\n",
    "    Gender_0,\n",
    "    Gender_1,\n",
    "    Dependents_0,\n",
    "    Dependents_1,\n",
    "    PhoneService_0,\n",
    "    PhoneService_1,\n",
    "    MultipleLines_0,\n",
    "    MultipleLines_1,\n",
    "    OnlineSecurity_0,\n",
    "    OnlineSecurity_1,\n",
    "    OnlineBackup_0,\n",
    "    OnlineBackup_1,\n",
    "    DeviceProtection_0,\n",
    "    DeviceProtection_1,\n",
    "    TechSupport_0,\n",
    "    TechSupport_1,\n",
    "    StreamingTV_0,\n",
    "    StreamingTV_1,\n",
    "    StreamingMovies_0,\n",
    "    StreamingMovies_1,\n",
    "    PaperlessBilling_0,\n",
    "    PaperlessBilling_1\n",
    "FROM\n",
    "    Transformed_data\n",
    "WHERE\n",
    "    SeniorCitizen = 0;\n",
    "'''\n",
    "\n",
    "#segment of HavePartner (where HavePartner equals \"Yes')\n",
    "query_ptr = '''\n",
    "REPLACE VIEW HavePartner AS\n",
    "SELECT\n",
    "    CustomerId,\n",
    "    Churn,\n",
    "    SeniorCitizen,\n",
    "    MonthlyChargeBand,\n",
    "    Tenure,\n",
    "    InternetService,\n",
    "    Contract,\n",
    "    PaperlessBilling,\n",
    "    PaymentMethod,\n",
    "    MonthlyCharges,\n",
    "    TotalCharges,\n",
    "    Gender_0,\n",
    "    Gender_1,\n",
    "    Dependents_0,\n",
    "    Dependents_1,\n",
    "    PhoneService_0,\n",
    "    PhoneService_1,\n",
    "    MultipleLines_0,\n",
    "    MultipleLines_1,\n",
    "    OnlineSecurity_0,\n",
    "    OnlineSecurity_1,\n",
    "    OnlineBackup_0,\n",
    "    OnlineBackup_1,\n",
    "    DeviceProtection_0,\n",
    "    DeviceProtection_1,\n",
    "    TechSupport_0,\n",
    "    TechSupport_1,\n",
    "    StreamingTV_0,\n",
    "    StreamingTV_1,\n",
    "    StreamingMovies_0,\n",
    "    StreamingMovies_1,\n",
    "    PaperlessBilling_0,\n",
    "    PaperlessBilling_1\n",
    "FROM\n",
    "    Transformed_data\n",
    "WHERE\n",
    "    Partner = 1;\n",
    "'''\n",
    "\n",
    "#Segment of NoPartner (where Partner equals \"No\")\n",
    "query_nptr = '''\n",
    "REPLACE VIEW NoPartner AS\n",
    "SELECT\n",
    "    CustomerId,\n",
    "    Churn,\n",
    "    SeniorCitizen,\n",
    "    MonthlyChargeBand,\n",
    "    Tenure,\n",
    "    InternetService,\n",
    "    Contract,\n",
    "    PaperlessBilling,\n",
    "    PaymentMethod,\n",
    "    MonthlyCharges,\n",
    "    TotalCharges,\n",
    "    Gender_0,\n",
    "    Gender_1,\n",
    "    Dependents_0,\n",
    "    Dependents_1,\n",
    "    PhoneService_0,\n",
    "    PhoneService_1,\n",
    "    MultipleLines_0,\n",
    "    MultipleLines_1,\n",
    "    OnlineSecurity_0,\n",
    "    OnlineSecurity_1,\n",
    "    OnlineBackup_0,\n",
    "    OnlineBackup_1,\n",
    "    DeviceProtection_0,\n",
    "    DeviceProtection_1,\n",
    "    TechSupport_0,\n",
    "    TechSupport_1,\n",
    "    StreamingTV_0,\n",
    "    StreamingTV_1,\n",
    "    StreamingMovies_0,\n",
    "    StreamingMovies_1,\n",
    "    PaperlessBilling_0,\n",
    "    PaperlessBilling_1\n",
    "FROM\n",
    "    Transformed_data\n",
    "WHERE\n",
    "    Partner = 0;\n",
    "'''\n",
    "\n",
    "#segment of LowMonthlyChargeBand (where MonthlyChargeBand equals \"Low\")\n",
    "query_low = '''\n",
    "REPLACE VIEW LowMonthlyChargeBand AS\n",
    "SELECT\n",
    "    CustomerId,\n",
    "    Churn,\n",
    "    SeniorCitizen,\n",
    "    Partner,\n",
    "    Tenure,\n",
    "    InternetService,\n",
    "    Contract,\n",
    "    PaperlessBilling,\n",
    "    PaymentMethod,\n",
    "    MonthlyCharges,\n",
    "    TotalCharges,\n",
    "    Gender_0,\n",
    "    Gender_1,\n",
    "    Dependents_0,\n",
    "    Dependents_1,\n",
    "    PhoneService_0,\n",
    "    PhoneService_1,\n",
    "    MultipleLines_0,\n",
    "    MultipleLines_1,\n",
    "    OnlineSecurity_0,\n",
    "    OnlineSecurity_1,\n",
    "    OnlineBackup_0,\n",
    "    OnlineBackup_1,\n",
    "    DeviceProtection_0,\n",
    "    DeviceProtection_1,\n",
    "    TechSupport_0,\n",
    "    TechSupport_1,\n",
    "    StreamingTV_0,\n",
    "    StreamingTV_1,\n",
    "    StreamingMovies_0,\n",
    "    StreamingMovies_1,\n",
    "    PaperlessBilling_0,\n",
    "    PaperlessBilling_1\n",
    "FROM\n",
    "    Transformed_data\n",
    "WHERE\n",
    "    MonthlyChargeBand = 0;\n",
    "'''\n",
    "\n",
    "#segment segment of HighMonthlyChargeBand (where MonthlyChargeBand equals \"High\")\n",
    "query_high = '''\n",
    "REPLACE VIEW HighMonthlyChargeBand AS\n",
    "SELECT\n",
    "    CustomerId,\n",
    "    Churn,\n",
    "    SeniorCitizen,\n",
    "    Partner,\n",
    "    Tenure,\n",
    "    InternetService,\n",
    "    Contract,\n",
    "    PaperlessBilling,\n",
    "    PaymentMethod,\n",
    "    MonthlyCharges,\n",
    "    TotalCharges,\n",
    "    Gender_0,\n",
    "    Gender_1,\n",
    "    Dependents_0,\n",
    "    Dependents_1,\n",
    "    PhoneService_0,\n",
    "    PhoneService_1,\n",
    "    MultipleLines_0,\n",
    "    MultipleLines_1,\n",
    "    OnlineSecurity_0,\n",
    "    OnlineSecurity_1,\n",
    "    OnlineBackup_0,\n",
    "    OnlineBackup_1,\n",
    "    DeviceProtection_0,\n",
    "    DeviceProtection_1,\n",
    "    TechSupport_0,\n",
    "    TechSupport_1,\n",
    "    StreamingTV_0,\n",
    "    StreamingTV_1,\n",
    "    StreamingMovies_0,\n",
    "    StreamingMovies_1,\n",
    "    PaperlessBilling_0,\n",
    "    PaperlessBilling_1\n",
    "FROM\n",
    "    Transformed_data\n",
    "WHERE\n",
    "    MonthlyChargeBand = 1;\n",
    "'''\n",
    "\n",
    "execute_sql(query_sr)\n",
    "execute_sql(query_nsr)\n",
    "execute_sql(query_ptr)\n",
    "execute_sql(query_nptr)\n",
    "execute_sql(query_low)\n",
    "execute_sql(query_high)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d5aa82b5-b775-48c1-ae6b-a926639d5046",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In the above codeblock we have created six segments </p><ul style = 'font-size:16px;font-family:Arial;color:#00233C'><li>SeniorCitizen</li><li>NotSeniorCitizens</li><li>HavePartner</li><li>NoPartner</li><li>LowMonthlyChargeBand</li><li>HighMonthlyChargeBand</li></ul>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6228cc70-1a28-4be1-a54a-93710c8f88a3",
   "metadata": {
    "tags": []
   },
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>6. Function definitions</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>    We will need same code to run for all segments, hence we are creating functions for the processing. We will call the functions for each segment for our model creation and predictions.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "45e1b5e2-f29a-4410-bcb4-e1cc6b4eed2f",
   "metadata": {
    "tags": []
   },
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>6.1 Function to create train and test data</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>TD_TrainTestSplit</b> helps us to divide our data in train and test sets for model training and scoring.<br>\n",
    "We are creating function to split each segment in train and test dataset. </p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c62a84a4-5816-4d23-b623-9c99696486ee",
   "metadata": {},
   "outputs": [],
   "source": [
    "def hyper_dataset(segment):\n",
    "    query = '''\n",
    "                CREATE MULTISET TABLE TrainTestSplit_output AS (\n",
    "                    SELECT * FROM TD_TrainTestSplit(\n",
    "                        ON {} AS InputTable\n",
    "                        USING\n",
    "                        IDColumn('CustomerId')\n",
    "                        Seed(21)\n",
    "                    ) AS dt\n",
    "                ) WITH DATA;\n",
    "            '''.format(segment)\n",
    "    try:\n",
    "        execute_sql(query)\n",
    "    except:\n",
    "        db_drop_table('TrainTestSplit_output')\n",
    "        execute_sql(query)\n",
    "    \n",
    "    df = DataFrame(\"TrainTestSplit_output\")\n",
    "    # Split into 2 virtual dataframes\n",
    "    df_train = df[df.TD_IsTrainRow == 1].drop([\"TD_IsTrainRow\"], axis = 1)\n",
    "    df_test = df[df.TD_IsTrainRow == 0].drop([\"TD_IsTrainRow\"], axis = 1)\n",
    "    \n",
    "    return df_train, df_test"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "88e87734-1eb6-47a6-87d4-3b2d0585088a",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We have done our preprocessing of data and we created our training and test datasets, let's now create some predictive models.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3624ff0f-2e80-450e-a76e-85398a8c73da",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>6.2 Function for InDb Model Training and Scoring</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We will create functions for model creation prediction and AUC & ROC curve to give us flexibility to run same code for each segment in a loop later.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2893fa15-2812-473d-b91c-5949ba436461",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>6.3 Function for Logistic Regression Model and Prediction</b></p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f4561645-5edd-4e2b-9983-59e77ab4745a",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <b>Logistic regression</b> is a statistical algorithm used for binary classification problems. It is a type of supervised learning algorithm that predicts the probability of an input belonging to a certain class (e.g., positive or negative) based on its features.<br>Logistic regression works by modeling the relationship between the input features and the probability of belonging to a certain class using a logistic function. The logistic function takes the input feature values and maps them onto a probability scale between 0 and 1, which represents the probability of belonging to the positive class.<br>\n",
    "    The <b>TD_GLM </b>function is a generalized linear model (GLM) that performs regression and classification analysis on data sets.\n",
    "<br>Please refer <a href ='https://docs.teradata.com/r/Enterprise/Teradata-Package-for-Python-Function-Reference-17.20/teradataml-Analytic-Database-SQL-Engine-Analytic-Functions/Supported-on-Database-Version-17.20.xx/MODEL-TRAINING-functions/GLM'>TD_GLM</a> for function elements and output."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "97afe72b-792d-4b0e-be61-16575b3f53c8",
   "metadata": {},
   "outputs": [],
   "source": [
    "def hyper_GLM(df_train, df_test):\n",
    "    from teradataml import GLM, TDGLMPredict\n",
    "\n",
    "    glm_model = GLM(data = df_train,\n",
    "                input_columns = ['3:'], \n",
    "                response_column = 'Churn',\n",
    "                family = 'Binomial')\n",
    "    \n",
    "    glm_prediction = TDGLMPredict(newdata = df_test,\n",
    "                           id_column = 'CustomerID',\n",
    "                           object = glm_model.result,\n",
    "                           accumulate = 'Churn',\n",
    "                           output_prob = True,\n",
    "                           output_responses = ['0', '1'])\n",
    "    \n",
    "    return glm_prediction.result"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8ae65a6c-76cf-447d-8fdf-27d8cfb1d44c",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In code block above we have created a function to create GLM model and its prediction using Teradata Vantage's InDb functions.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "74f2d47e-80c3-46b4-b667-770e52ef8eef",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>6.4 Function to visualize the results (ROC curve and AUC) for Logistic Regression Model</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Calculate mean absolute error and AUC(Area Under the Curve) for Receiver Operating Characteristic Curve</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Mean Absolute Error is the summation of the difference between actual and predicted values averaged over the number of observations.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "df42d64b-7b92-4699-b68c-f173cba00bcd",
   "metadata": {},
   "outputs": [],
   "source": [
    "def hyper_GLM_AUC(result_glm,segment):\n",
    "    glm_pred = result_glm.to_pandas()\n",
    "    ME = mean_absolute_error(glm_pred['Churn'], glm_pred['prob_1'])\n",
    "    AUC = roc_auc_score(glm_pred['Churn'], glm_pred['prob_1'])\n",
    "    fpr, tpr, thresholds = roc_curve(glm_pred['Churn'], glm_pred['prob_1'])\n",
    "    plt.plot(fpr, tpr, color = 'orange', label = 'ROC. AUC = {}'.format(str(AUC)))\n",
    "    plt.plot([0, 1], [0, 1], color = 'darkblue', linestyle = '--')\n",
    "    plt.xlabel('False Positive Rate')\n",
    "    plt.ylabel('True Positive Rate')\n",
    "    plt.title('Receiver Operating Characteristic (ROC) Curve for {} GLM'.format(segment))\n",
    "    plt.legend()\n",
    "    return plt, ME,AUC"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f72e8b71-e78a-489b-85ce-e364fd28fccc",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In code block above we have created a function to visualize the ROC curve and caculate the AUC for GLM model created."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2b4bd190-57dd-4947-a4da-0e2c3429ca88",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>6.5 Function for XGB Model and Prediction</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <b>XGBoost (eXtreme Gradient Boosting) </b> is based on the gradient boosting framework, which is an ensemble learning method that combines multiple weak or base models (typically decision trees) to create a more accurate and robust predictive model. XGBoost improves upon traditional gradient boosting by using a number of optimization techniques, including parallelization, regularization, and efficient handling of missing values, to achieve faster training times and better model performance.<br>\n",
    "    The <b>TD_XGBoost </b>is an implementation of the gradient boosted decision tree designed for speed and performance. In gradient boosting, each iteration fits a model to the residuals (errors) of the previous iteration to correct the errors made by existing models. The predicted residual is multiplied by this learning rate and then added to the previous prediction. Models are added sequentially until no further improvements can be made. It is called gradient boosting because it uses a gradient descent algorithm to minimize the loss when adding new models.\n",
    "<br>Please refer <a href ='https://docs.teradata.com/r/Enterprise_IntelliFlex_VMware/Teradata-VantageTM-Analytics-Database-Analytic-Functions-17.20/Model-Training-Functions/TD_XGBoost'>TD_XGBoost</a> for function elements and output.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f23e3681-c2de-4792-9369-9b72b76a23be",
   "metadata": {},
   "outputs": [],
   "source": [
    "def hyper_XGB(segment):\n",
    "    query_model = '''\n",
    "                    CREATE MULTISET TABLE xgb_model AS (\n",
    "                        SELECT * FROM TD_XGBoost (\n",
    "                            ON (SELECT * FROM TrainTestSplit_output WHERE TD_IstrainRow = 1) PARTITION BY ANY\n",
    "                            OUT TABLE MetaInformationTable(xgb_out)\n",
    "                            USING\n",
    "                            InputColumns ('[4:]')  -- Adjust this column selection as needed\n",
    "                            ResponseColumn('Churn')\n",
    "                            ModelType ('classification') \n",
    "                        ) AS dt\n",
    "                    ) WITH DATA;\n",
    "                '''\n",
    "                \n",
    "    query_pred = '''\n",
    "                    CREATE TABLE xgb_predict_out AS (\n",
    "                        SELECT * FROM TD_XGBoostPredict(\n",
    "                            ON (SELECT * FROM TrainTestSplit_output WHERE TD_IstrainRow = 0) AS inputtable PARTITION BY ANY\n",
    "                            ON xgb_model AS modeltable DIMENSION ORDER BY task_index, tree_num, iter, class_num, tree_order\n",
    "                            USING\n",
    "                            IdColumn('CustomerId')\n",
    "                            ModelType('Classification')\n",
    "                            OutputProb('t')\n",
    "                            Responses('0', '1')\n",
    "                            Accumulate('Churn')\n",
    "                        ) AS dt\n",
    "                    ) WITH DATA;\n",
    "             '''\n",
    "    try:\n",
    "        execute_sql(query_model)\n",
    "        try:\n",
    "            execute_sql(query_pred)\n",
    "        except:\n",
    "            db_drop_table('xgb_predict_out')\n",
    "            execute_sql(query_pred)\n",
    "    except:\n",
    "        db_drop_table('xgb_model')\n",
    "        db_drop_table('xgb_out')\n",
    "        execute_sql(query_model)\n",
    "        try:\n",
    "            execute_sql(query_pred)\n",
    "        except:\n",
    "            db_drop_table('xgb_predict_out')\n",
    "            execute_sql(query_pred)\n",
    "\n",
    "    result_xgb = DataFrame('xgb_predict_out')\n",
    "    \n",
    "    return result_xgb"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4221974e-3823-4864-a631-5394ff232abd",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In code block above we have created a function to create XGB model and its prediction using Teradata Vantage's InDb functions.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f7660678-f9e7-404e-8fa2-39ae626b6ff6",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>6.6 Function to visualize the results (ROC curve and AUC) for XGB Model</b></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "61ca763f-806f-4398-8374-e1a971a85cf3",
   "metadata": {},
   "outputs": [],
   "source": [
    "def hyper_XGB_AUC(result_xgb,segment):\n",
    "    xgb_pred = result_xgb.to_pandas().reset_index().sort_values(\"CustomerID\")\n",
    "    ME = mean_absolute_error(xgb_pred['Churn'], xgb_pred['Prob_1'])\n",
    "    #print(\"Mean absolute error for XGB prediction is\",mean_absolute_error(xgb_pred['Churn'], xgb_pred['Prob_1']))\n",
    "    AUC = roc_auc_score(xgb_pred['Churn'], xgb_pred['Prob_1'])\n",
    "    #print(\"AUC for XGB output for \", segment,\" is \",AUC)\n",
    "    fpr, tpr, thresholds = roc_curve(xgb_pred['Churn'], xgb_pred['Prob_1'])\n",
    "    plt.plot(fpr, tpr, color = 'orange', label = 'ROC. AUC = {}'.format(str(AUC)))\n",
    "    plt.plot([0, 1], [0, 1], color='darkblue', linestyle='--')\n",
    "    plt.xlabel('False Positive Rate')\n",
    "    plt.ylabel('True Positive Rate')\n",
    "    plt.title('Receiver Operating Characteristic (ROC) Curve for {} XGB'.format(segment))\n",
    "    plt.legend()\n",
    "    return plt, ME,AUC"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bd7184df-6042-42f8-ac77-7e502d02e45e",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In code block above we have created a function to visualize the ROC curve and caculate the AUC for XGB model created.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e0cc2564-4b49-489d-9c80-259bf21a7db8",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>6.7 Function for displaying values</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The below function is created to diaplay the mean_absolute_error and AUC values</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f667a58e-1871-42cd-b7f3-93d1902fddcd",
   "metadata": {},
   "outputs": [],
   "source": [
    "def display_template(a,b):\n",
    "    return f\"<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\" f'''The <b>Mean absolute error</b> of model is <b>{a} </b>\n",
    "<br><b> AUC </b>of model is <b>{b}</b>'''\"</p>\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b88c5fda-b54a-4a78-99a5-7cd4c9c2addb",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>7. Model Training and Scoring for each segment</b></p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a24bb51b-7115-486e-957a-848bad5bc4d9",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In the above steps we have created functions for model creation and prediction and for visualization of the model performance, let's combine all of this together and do the predictions for each segment.<br>Let us go step by step for one segment <b>SeniorCitizen</b> first for understandng and then we can run all the segments in a loop.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "294cd1ed-3984-416f-a5cb-a3d4849727b7",
   "metadata": {},
   "outputs": [],
   "source": [
    "#this command will create train and test dataset for each segment\n",
    "df_train, df_test = hyper_dataset('SeniorCitizen')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "030df89e-6e88-4a7d-9d6c-040aea7defca",
   "metadata": {},
   "outputs": [],
   "source": [
    "#this command will create GLM model and predictions\n",
    "globals()[\"result_glm_{}\".format('SeniorCitizen')] = hyper_GLM(df_train, df_test)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7a8f8e83-fdbb-4d98-8519-76ec2f07a602",
   "metadata": {},
   "outputs": [],
   "source": [
    "#this command will create XGB model and predictions\n",
    "globals()[\"result_xgb_{}\".format('SeniorCitizen')] = hyper_XGB('SeniorCitizen')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "da5b7dcc-ba72-4fe6-a364-cc2f452ffd4e",
   "metadata": {},
   "outputs": [],
   "source": [
    "#printing the output for GLM for SeniorCitizen segment\n",
    "print(\"GLM output for \", 'SeniorCitizen')\n",
    "print(globals()[\"result_glm_{}\".format('SeniorCitizen')])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "050b0681-9bb1-48a4-bb6c-83fdebf1d7f3",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The output above shows prob_1, i.e. customer will Churn and prob_0, i.e. customer will not Churn. The prediction column uses these probabilities to give a class label, i.e. prediction column.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "65dd23bf-f545-4166-8e4b-90916d4eaafe",
   "metadata": {},
   "outputs": [],
   "source": [
    "#printing the output for XGB for SeniorCitizen segment\n",
    "print(\"XGB output for \", 'SeniorCitizen')\n",
    "print(globals()[\"result_xgb_{}\".format('SeniorCitizen')])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f6fa1c87-afc7-451a-b525-339529d1c456",
   "metadata": {},
   "outputs": [],
   "source": [
    "#visualize the ROC curve and AUC calculation for GLM model for Senior Citizen segment \n",
    "roc_glm, me_glm, auc_glm = hyper_GLM_AUC(globals()[\"result_glm_{}\".format('SeniorCitizen')],'SeniorCitizen')\n",
    "roc_glm.show()\n",
    "display(Markdown(display_template(me_glm, auc_glm)))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3d0eff11-2ec7-4ea0-b709-2e10c9ae767e",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The ROC curve is a graph between TPR(True Positive Rate) and FPR(False Positive Rate). The area under the ROC curve is a metric of how well the model can distinguish between positive and negative classes. The higher the AUC, the better the model's performance in distinguishing between the positive and negative classes.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "07502515-5334-4aeb-ada4-ea8163b82763",
   "metadata": {},
   "outputs": [],
   "source": [
    "#visualize the ROC curve and AUC calculation for XGB Model for Senior Citizen segment \n",
    "roc_xgb, me_xgb, auc_xgb = hyper_XGB_AUC(globals()[\"result_xgb_{}\".format('SeniorCitizen')],'SeniorCitizen')\n",
    "roc_xgb.show()\n",
    "display(Markdown(display_template(me_xgb, auc_xgb)))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "58cff53e-06d7-42b5-9196-ae17efc27adf",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In the steps above we have seen how the funtions can be used to create model and it's accuracy. Now, let's run the functions together for all the segments we have created in section 5.<br><i>* Below step will take some time as we are creating two prediction model for each of six segments.</i>    </p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dd0700c5-4675-4165-a3cc-2866ecf17123",
   "metadata": {},
   "outputs": [],
   "source": [
    "#segment list\n",
    "segments = ['SeniorCitizen', 'NotSeniorCitizen', 'HavePartner','NoPartner','LowMonthlyChargeBand','HighMonthlyChargeBand']\n",
    "for segment in segments:\n",
    "    df_train, df_test = hyper_dataset(segment)\n",
    "    globals()[\"result_glm_{}\".format(segment)] = hyper_GLM(df_train, df_test)\n",
    "    globals()[\"result_xgb_{}\".format(segment)] = hyper_XGB(segment)\n",
    "    print(\"\\033[1m ============= Model & Predictions for segment\", segment,\"================ \\033[0m\")\n",
    "    print(\"\\033[1m GLM model output for segment \\033[0m\", segment)\n",
    "    print(globals()[\"result_glm_{}\".format(segment)])\n",
    "    print(\"\\033[1m XGB model output for segment \\033[0m\", segment)\n",
    "    print(globals()[\"result_xgb_{}\".format(segment)])\n",
    "    roc_glm, me_glm, auc_glm = hyper_GLM_AUC(globals()[\"result_glm_{}\".format(segment)], segment)\n",
    "    roc_glm.show()\n",
    "    display(Markdown(display_template(me_glm, auc_glm)))\n",
    "    roc_xgb, me_xgb, auc_xgb = hyper_XGB_AUC(globals()[\"result_xgb_{}\".format(segment)], segment)\n",
    "    roc_xgb.show()\n",
    "    display(Markdown(display_template(me_xgb, auc_xgb)))\n",
    "    print(\"\\033[1m =============================================================================== \\033[0m\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e91ddacb-4ca3-405c-93c8-c587788191c4",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>9. Conclusion</b></p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "516e0588-5e98-4373-8f30-8e8f40898835",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In this demo we have done a simple example of hyper personalization predictions for the basic segments that we created based on our data exploration. Segments can be dynamic and evolve over time as new data becomes available or as customer preferences change. It's essential to regularly analyze and update segments to ensure the hyper-personalization efforts remain effective and relevant.    "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "35ebb886-8da9-479a-8995-c6dd7ccebffd",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>10. Cleanup</b></p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f0b01f3e-03fa-4a14-b388-02eeb210b8c1",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C;color:#00233C'><b>Work Tables</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C;'>\n",
    "Cleanup work tables to prevent errors next time."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "866e83f4-096c-4216-a9e6-ff1a638ba139",
   "metadata": {},
   "outputs": [],
   "source": [
    "tables = ['churn', 'onehotencodingfittable', 'ordinalencodingfittable',\n",
    "         'scaleFitOut', 'Transformed_data', 'TrainTestSplit_output',\n",
    "         'xgb_model', 'xgb_out', 'xgb_predict_out']\n",
    "views = ['SeniorCitizen', 'NotSeniorCitizen', 'HavePartner',\n",
    "         'NoPartner', 'LowMonthlyChargeBand', 'HighMonthlyChargeBand']\n",
    "\n",
    "# Loop through the list of tables and execute the drop table command for each table\n",
    "for table in tables:\n",
    "    try:\n",
    "        db_drop_table(table_name = table)\n",
    "    except:\n",
    "        pass\n",
    "    \n",
    "# Loop through the list of views and execute the drop view command for each view\n",
    "for view in views:\n",
    "    try:\n",
    "        db_drop_view(view_name = view)\n",
    "    except:\n",
    "        pass"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "98028152-2401-429a-9141-0488eaecb0f5",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C;color:#00233C'><b>Databases and Tables</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The following code will clean up tables and databases created above.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7aae8012-ca09-4541-9389-1bb82f283ff1",
   "metadata": {},
   "outputs": [],
   "source": [
    "%run -i ../run_procedure.py \"call remove_data('DEMO_Telco');\"    # Takes 5 seconds"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9ad94d1e-d82d-4611-b5c7-4180397f6c94",
   "metadata": {},
   "outputs": [],
   "source": [
    "remove_context()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a8f9f644",
   "metadata": {},
   "source": [
    "<b style = 'font-size:20px;font-family:Arial;color:#00233C'>Dataset:</b>\n",
    "\n",
    "- `CustomerID`: unique id of customer\n",
    "- `Gender`: Whether the customer is a male or a female\n",
    "- `SeniorCitizen`:Whether the customer is a senior citizen or not (1, 0)\n",
    "- `Partner`:Whether the customer has a partner or not (Yes, No)\n",
    "- `Dependents`:Whether the customer has dependents or not (Yes, No)\n",
    "- `Tenure`:Number of months the customer has stayed with the company\n",
    "- `PhoneService`:Whether the customer has a phone service or not (Yes, No)\n",
    "- `MultipleLines`:Whether the customer has multiple lines or not (Yes, No, No phone service)\n",
    "- `InternetService`:Customer’s internet service provider (DSL, Fiber optic, No)\n",
    "- `OnlineSecurity`:Whether the customer has online security or not (Yes, No, No internet service)\n",
    "- `OnlineBackup`:Whether the customer has online backup or not (Yes, No, No internet service)\n",
    "- `DeviceProtection`:Whether the customer has device protection or not (Yes, No, No internet service)\n",
    "- `TechSupport`:Whether the customer has tech support or not (Yes, No, No internet service)\n",
    "- `StreamingTV`:Whether the customer has streaming TV or not (Yes, No, No internet service)\n",
    "- `StreamingMovies`:Whether the customer has streaming movies or not (Yes, No, No internet service)\n",
    "- `Contract`:The contract term of the customer (Month-to-month, One year, Two year)\n",
    "- `PaperlessBilling`:Whether the customer has paperless billing or not (Yes, No)\n",
    "- `PaymentMethod`:The customer’s payment method (Electronic check, Mailed check, Bank transfer (automatic), Credit card (automatic))\n",
    "- `MonthlyCharges`:The amount charged to the customer monthly\n",
    "- `TotalCharges`:The total amount charged to the customer\n",
    "- `Churn`:Whether the customer churned or not (Yes or No)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3eba2147-264c-4e0d-8b48-b2112351a290",
   "metadata": {},
   "source": [
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>Reference Links:</b></p>\n",
    "<ul style = 'font-size:16px;font-family:Arial;color:#00233C'> \n",
    "       <li>Teradata Vantage™ - Analytics Database Analytic Functions - 17.20: <a href = 'https://docs.teradata.com/r/Enterprise_IntelliFlex_VMware/Teradata-VantageTM-Analytics-Database-Analytic-Functions-17.20/Introduction-to-Analytics-Database-Analytic-Functions '>https://docs.teradata.com/r/Enterprise_IntelliFlex_VMware/Teradata-VantageTM-Analytics-Database-Analytic-Functions-17.20/Introduction-to-Analytics-Database-Analytic-Functions </a></li>    \n",
    "  <li>Teradata® Package for Python User Guide - 17.20: <a href = 'https://docs.teradata.com/r/Enterprise_IntelliFlex_VMware/Teradata-Package-for-Python-User-Guide-17.20/Introduction-to-Teradata-Package-for-Python'>https://docs.teradata.com/r/Enterprise_IntelliFlex_VMware/Teradata-Package-for-Python-User-Guide-17.20/Introduction-to-Teradata-Package-for-Python</a></li>\n",
    "  <li>Teradata® Package for Python Function Reference - 17.20: <a href = 'https://docs.teradata.com/r/Enterprise/Teradata-Package-for-Python-Function-Reference-17.20/Teradata-Package-for-Python-Function-Reference'>https://docs.teradata.com/r/Enterprise/Teradata-Package-for-Python-Function-Reference-17.20/Teradata-Package-for-Python-Function-Reference</a></li>      \n",
    "</ul>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c30802a4-8141-47f6-971d-0bb79be6f5bf",
   "metadata": {},
   "source": [
    "<footer style=\"padding-bottom:35px; background:#f9f9f9; border-bottom:3px solid #00233C\">\n",
    "    <div style=\"float:left;margin-top:14px\">ClearScape Analytics™</div>\n",
    "    <div style=\"float:right;\">\n",
    "        <div style=\"float:left; margin-top:14px\">\n",
    "            Copyright © Teradata Corporation - 2023. All Rights Reserved\n",
    "        </div>\n",
    "    </div>\n",
    "</footer>"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.9.10"
  },
  "toc-autonumbering": true
 },
 "nbformat": 4,
 "nbformat_minor": 5
}