UseCases/Marketing_Campaign_Effectiveness/Marketing_Campaign_Effectiveness_Preditction_PY_SQL.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "38bc532d-c78c-4c89-b191-242da0733f39",
   "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",
    "       Marketing Campaign Effectiveness Prediction using Vantage\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": "fff71661-19b4-423a-867a-7c815b064c81",
   "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'>Marketing campaigns revolve around prioritizing customer needs and ensuring their overall satisfaction. However, the success of a marketing campaign hinges on various factors. Certain variables must be carefully considered when formulating a marketing campaign. The process by which companies create value for customers and build strong customer relationships in order to capture value from customers in return.</p>\n",
    "\n",
    "<center><img src=\"images/header_img.jpg\" alt=\"marketing tips1\" width=400 height=400/></center>\n",
    "<p>image source: <a href=\"https://unsplash.com/photos/--kQ4tBklJI\">unsplash.com</a></p>\n",
    "\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Marketing campaigns are characterize by focusing on the customer's needs and their overall satisfaction. Nevertheless, there are different variables that determine whether a marketing campaign will be successful or not. There are certain variables that we need to take into consideration when making a marketing campaign. We want to provide the best possible predictive model for the marketing campaign of their new product, which shows if a customer buys the new product or not.</p>\n",
    "\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Teradata Vantage provides us with the necessary capabilities to analyze the vast amounts of data collected for marketing campaigns, such as the customer's age, marital status, education, number of family members, etc. In addition to this, we have data related to the last contact of the current campaign, i.e., contact, month, day, and duration. By processing this data, we can find patterns of campaign effectiveness and take proactive measures to improve the next marketing campaign.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>With Teradata Vantage, we can help clients stay ahead of the curve, providing them with cutting-edge analytics capabilities to improve the next marketing campaign, reduce marketing costs, and reduce customer annoyance.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2ca49b9d-df0b-400a-acf8-0252ab8a2618",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233c'><b>Steps in the analysis:</b></p>\n",
    "<ol style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Configuring the environment</li>\n",
    "    <li>Connect to Vantage</li>\n",
    "    <li>Data Exploration</li>\n",
    "    <li>Data Preparation</li>\n",
    "    <li>Train-Test Split</li>\n",
    "    <li>In-Database Machine Learning</li>\n",
    "    <li>Visualize the results</li>\n",
    "    <li>Cleanup</li>\n",
    "</ol>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a833b5bf-74af-42be-8543-3782e1da95dc",
   "metadata": {},
   "source": [
    "<hr style='height:2px;border:none;background-color:#00233C;'>\n",
    "<b style = 'font-size:28px;font-family:Arial;color:#00233c'>1. Configuring the environment</b>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Here, we import the required libraries, set environment variables and environment paths (if required).</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ea50a4aa-1211-44fc-8166-317c35253207",
   "metadata": {},
   "outputs": [],
   "source": [
    "import io\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import seaborn as sns\n",
    "from PIL import Image\n",
    "import plotly.express as px\n",
    "import plotly.graph_objs as go\n",
    "import matplotlib.pyplot as plt\n",
    "from IPython.display import display, Markdown\n",
    "from sklearn.metrics import (\n",
    "    accuracy_score,\n",
    "    confusion_matrix,\n",
    "    ConfusionMatrixDisplay,\n",
    "    roc_auc_score,\n",
    "    roc_curve,\n",
    "    auc,\n",
    ")\n",
    "\n",
    "# teradata lib\n",
    "from teradataml import *\n",
    "from teradataml import ROC\n",
    "\n",
    "# Modify the following to match the specific client environment settings\n",
    "display.max_rows = 5\n",
    "configure.val_install_location = \"val\"\n",
    "\n",
    "# Suppress warnings\n",
    "warnings.filterwarnings(\"ignore\")\n",
    "display.max_rows = 5"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a59718f8-7af4-4d1a-abc7-a860eb7cbae3",
   "metadata": {},
   "source": [
    "<hr style='height:2px;border:none;background-color:#00233C;'>\n",
    "<b style = 'font-size:28px;font-family:Arial;color:#00233c'>2. Connect to Vantage</b>\n",
    "<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": "ef43d758-c0ff-4990-94df-2b799cf1a053",
   "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": "2a84691d-fd3c-4a18-9958-254343574205",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%capture\n",
    "execute_sql('''SET query_band='DEMO=Marketing_Campaign_Effectiveness_Preditction_PY_SQL.ipynb;' UPDATE FOR SESSION;''') "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b22db506-84a7-406b-be9f-9fe69d268ba4",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Begin running steps with Shift + Enter keys. </p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "93a90d5e-2e0a-4701-9368-adb97f7c9590",
   "metadata": {},
   "source": [
    "<hr style='height:1px;border:none;background-color:#00233C;'>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b>1.1 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 can either run the demo using foreign tables to access the data without any storage on your environment or download the data to local storage, which may yield faster execution. Still, there could be considerations of available storage. Two statements are 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": "6cf2b3c2-f90e-428d-a949-c16294016e85",
   "metadata": {},
   "outputs": [],
   "source": [
    "%run -i ../run_procedure.py \"call get_data('DEMO_MarketingCamp_cloud');\"        # Takes 1 minute\n",
    "# %run -i ../run_procedure.py \"call get_data('DEMO_MarketingCamp_local');\"        # Takes 2 minutes"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1670345f-dfd2-440b-919a-ff824c65c2c9",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Next is an optional step – if you want to see the status of databases/tables created and space used.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0ce328a8-e2bf-4b64-98aa-5f8007f86815",
   "metadata": {},
   "outputs": [],
   "source": [
    "%run -i ../run_procedure.py \"call space_report();\"        # Takes 10 seconds"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "490f3fe2-ed63-4838-bb19-4c0d0157453d",
   "metadata": {},
   "source": [
    "<hr style='height:2px;border:none;background-color:#00233C;'>\n",
    "<b style = 'font-size:28px;font-family:Arial;color:#00233c'>3. Data Exploration</b>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The goal of the Marketing Campaign Effectiveness prediction is to reduce marketing resources by identifying customers who would purchase the product and thereby directing marketing efforts to them.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The data is from the last marketing campaign, with thousands of rows of customer data like age, job, marital status, education, etc.<p/>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Each row is a snapshot of data taken during the last marketing campaign, and each column is a different variable. The input dataset can be divided into three categories, as below:</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'> \n",
    "<ol style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>customer data i.e. age, profession, eduction, monthly income, etc.</li>\n",
    "    <li>attributes related with the last contact of the current campaign i.e. contact, month, day, etc.</li>\n",
    "    <li>other attributes i.e. campaign, previous outcome, payment methods, etc.</li>\n",
    "   <li>target attribute - purchased.</li>\n",
    "\n",
    "</ol>\n",
    "</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The source data from <a href=\"https://www.kaggle.com/datasets/janiobachmann/bank-marketing-dataset\">kaggle</a> is loaded in Vantage and supplemented with information about city, monthly income, family members, etc. The data is loaded into vantage table named <i>Retail_Marketing</i>.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b><i>*Please scroll down to the end of the notebook for detailed column descriptions of the dataset.</i></b></p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "eda8e565-b58b-4cc6-8d9c-50790ca3dcab",
   "metadata": {},
   "source": [
    "<hr style='height:1px;border:none;background-color:#00233C;'>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b>3.1 Examine the Retail Marketing Campaign table</b></p>    \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Let's look at the sample data in the Retail_Marketing table.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4a9951cf-e2b0-40d7-9a8f-a01c7f48ef09",
   "metadata": {},
   "outputs": [],
   "source": [
    "tdf = DataFrame(in_schema(\"DEMO_MarketingCamp\", \"Retail_Marketing\"))\n",
    "df = tdf.to_pandas()\n",
    "print(\"Data information: \\n\", tdf.shape)\n",
    "tdf.sort(\"customer_id\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b0d6d954-b72f-41c3-9f8c-bc15cf74d672",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>There are 11K records in all, and there are 23 variables. Purchased is the target variable. We shall classify the purchased variable in accordance with the remaining features.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "91f88eb2-bb8f-4a9c-af5e-a2d40140a1b7",
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_histogram(df, x, y, color, title, x_title, y_title, width=800, height=500):\n",
    "    fig = px.histogram(\n",
    "        df,\n",
    "        x=x,\n",
    "        y=y,\n",
    "        title=title,\n",
    "        nbins=df.shape[0],\n",
    "        barmode=\"group\",\n",
    "        color=color,\n",
    "        color_discrete_map={\"no\": \"#dd8452\", \"yes\": \"#4c72b0\"},\n",
    "    )\n",
    "    fig.update_yaxes(title=y_title)\n",
    "    fig.update_xaxes(title=x_title)\n",
    "    fig.update_layout(\n",
    "        autosize=False,\n",
    "        width=width,\n",
    "        height=height,\n",
    "    )\n",
    "    return fig"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1f7eaa73-f67c-4763-a49c-41fc9c6bd332",
   "metadata": {},
   "source": [
    "<hr style='height:1px;border:none;background-color:#00233C;'>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b>3.1.1 Analyze how the marital status affects the feature of purchases.</b></p>    \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Now, let's do some data exploration with marital status and purchase.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "77bf4e1e-936d-4104-8dc3-d5dd61e114fc",
   "metadata": {},
   "outputs": [],
   "source": [
    "query = \"\"\"\n",
    "SELECT marital,\n",
    "       purchased,\n",
    "       Count(*) / Cast(Sum(Count(*)) OVER (partition BY marital) AS FLOAT) * 100 AS purchased_perc\n",
    "FROM   demo_marketingcamp.retail_marketing\n",
    "GROUP  BY 1, 2\n",
    "\"\"\"\n",
    "\n",
    "df_marital_purchased = DataFrame.from_query(query)\n",
    "df_marital_purchased.sort(\"purchased_perc\", ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7b1388fc-6273-4585-bdee-909378f20b4a",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_marital_purchased = df_marital_purchased.to_pandas()\n",
    "get_histogram(\n",
    "    df_marital_purchased,\n",
    "    x=\"marital\",\n",
    "    y=\"purchased_perc\",\n",
    "    color=\"purchased\",\n",
    "    title=\"Number of purchased by Marital Status\",\n",
    "    x_title=\"marital\",\n",
    "    y_title=\"Purchase (%)\",\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "01c2c27b-a03f-4bad-a696-8818d06dba52",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Few observations from the above graph are:</p>\n",
    "\n",
    "<ol style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li><b>Married customers</b> as a whole have a purchased a product rate of <b>38%</b>, compared to a non-purchased rate of <b>62%</b>.</li>\n",
    "    <li>Out of all the <b>divorcing customers</b>, <b>39%</b> have purchased a product while <b>61%</b> have not.</li>\n",
    "    <li>The percentage of <b>single customers</b> who have purchased a product is <b>76%</b>, while <b>24%</b> have not.</li>\n",
    "</ol>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Compared to other marital statuses, single clients are buying more products.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "81f72fdf-4467-4a25-9be5-aa65ccb32576",
   "metadata": {},
   "source": [
    "<hr style='height:1px;border:none;background-color:#00233C;'>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b>3.1.2 Study the impact of the customer's profession on the characteristic of the purchase.</b></p>    \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Exploring the customers profession and purchase features.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "43c00d0b-569d-429b-ae38-35b167c9b02c",
   "metadata": {},
   "outputs": [],
   "source": [
    "query = \"\"\"\n",
    "SELECT profession,\n",
    "       purchased,\n",
    "       Count(*) / Cast(Sum(Count(*)) OVER (partition BY profession) AS FLOAT) * 100 AS purchased_perc\n",
    "FROM   demo_marketingcamp.retail_marketing\n",
    "GROUP  BY 1,2\n",
    "\"\"\"\n",
    "\n",
    "df_profession_purchased = DataFrame.from_query(query)\n",
    "df_profession_purchased.sort(\"purchased_perc\", ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6d4f1696-909b-4891-a363-14eb967d85a0",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_profession_purchased = df_profession_purchased.to_pandas()\n",
    "get_histogram(\n",
    "    df_profession_purchased,\n",
    "    x=\"profession\",\n",
    "    y=\"purchased_perc\",\n",
    "    color=\"purchased\",\n",
    "    title=\"Number of purchased by profession\",\n",
    "    x_title=\"profession type\",\n",
    "    y_title=\"Purchase (%)\",\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "20feca12-b9f5-48b9-a9e4-d265e1183a5f",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>From the above graph we can observe that:</p>\n",
    "\n",
    "<ol style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>A product purchase rate of <b>73%</b> among all <b>Student</b> customers, as opposed to a non-purchase rate of <b>27%</b>.</li>\n",
    "    <li>A little more than half of all clients who are in <b>Technician, Management, admin and retired</b> have bought something.</li>\n",
    "</ol>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Customers in blue-collar jobs are the least likely to make purchases, whereas students make the greatest purchases out of all professions.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7ee32aef-0307-4c50-a64e-afd9de3f3c47",
   "metadata": {},
   "source": [
    "<hr style='height:1px;border:none;background-color:#00233C;'>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b>3.1.3 Investigate the impact of customers education on purchase</b></p>    \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Exploring the customers education and purchase.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7e73531a-7d5d-4062-be8f-2d0c614af740",
   "metadata": {},
   "outputs": [],
   "source": [
    "query = \"\"\"\n",
    "SELECT education,purchased,\n",
    "       Count(*) / Cast(Sum(Count(*)) OVER (partition BY education) AS FLOAT) * 100 AS purchased_perc\n",
    "FROM   demo_marketingcamp.retail_marketing\n",
    "GROUP  BY 1, 2\n",
    "\n",
    "\"\"\"\n",
    "\n",
    "df_edu_purchased = DataFrame.from_query(query)\n",
    "df_edu_purchased.sort([\"purchased_perc\", \"purchased\"], ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f03ef133-6575-46e5-b767-a68555586fc2",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_edu_purchased = df_edu_purchased.to_pandas()\n",
    "get_histogram(\n",
    "    df_edu_purchased,\n",
    "    x=\"education\",\n",
    "    y=\"purchased_perc\",\n",
    "    color=\"purchased\",\n",
    "    title=\"Number of purchased by education type\",\n",
    "    x_title=\"education type\",\n",
    "    y_title=\"Purchase (%)\",\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fdddd909-13ec-45d0-bc9b-4d650eb3952f",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>From the above graph we can observe that:</p>\n",
    "\n",
    "<ol style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>a <b>55%</b> rate for purchased, compared to a <b>45%</b> non-purchased rate, among all customers who completed their <b>tertiary</b>.</li>\n",
    "    <li>Approximately <b>50%</b> of all customers whose education is <b>unknown or secondary</b> have purchased a product.</li>\n",
    "</ol>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Customers with primary-level education are least likely to purchase.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "877dfcb2-314c-40ef-a125-bd412d44a660",
   "metadata": {},
   "source": [
    "<hr style='height:1px;border:none;background-color:#00233C;'>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b>3.1.4 Examine how prior marketing campaign results affected the buy feature.</b></p>    \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Exploring the results of earlier campaigns carried out with purchases.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "66f94f19-b727-4f72-a897-c15b02f398c9",
   "metadata": {},
   "outputs": [],
   "source": [
    "query = \"\"\"\n",
    "SELECT prev_campaign_outcome,\n",
    "       purchased,\n",
    "       Count(*) / Cast(Sum(Count(*)) OVER (partition BY prev_campaign_outcome) AS FLOAT) * 100 AS purchased_perc\n",
    "FROM   demo_marketingcamp.retail_marketing\n",
    "GROUP  BY 1,\n",
    "          2\n",
    "\"\"\"\n",
    "\n",
    "df_poutcome_purchased = DataFrame.from_query(query)\n",
    "df_poutcome_purchased.sort(\"purchased_perc\", ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "064e902d-bc05-408d-8aef-32d9a3163a4d",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_poutcome_purchased = df_poutcome_purchased.to_pandas()\n",
    "get_histogram(\n",
    "    df_poutcome_purchased,\n",
    "    x=\"prev_campaign_outcome\",\n",
    "    y=\"purchased_perc\",\n",
    "    color=\"purchased\",\n",
    "    title=\"Number of purchased by previous outcome type\",\n",
    "    x_title=\"poutcome type\",\n",
    "    y_title=\"Purchase (%)\",\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ac00bff5-7d44-4949-901a-7e3a136cd265",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>From the above graph we can observe that:</p>\n",
    "\n",
    "<ol style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>If previous outcome is <b>success</b> then there are high probability to purchase a product.</li>\n",
    "    <li>Approximately <b>50%</b> of chance that if previous outcome is <b>failure or unknown</b> then that customer will purchase a product.</li>\n",
    "</ol>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e43b9eb6-7cd4-4215-83f6-35636765296a",
   "metadata": {},
   "source": [
    "<hr style='height:1px;border:none;background-color:#00233C;'>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b>3.1.5 Examine how a customer's age affects a buying feature.</b></p>    \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Exploring the customer's age with purchase decision.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "57241062-0a38-4cf2-a1f9-9b8afd167fc9",
   "metadata": {},
   "outputs": [],
   "source": [
    "grp_gen = (\n",
    "    tdf.select([\"age\", \"purchased\"]).groupby([\"age\"]).agg([\"mean\", \"count\"]).to_pandas()\n",
    ")\n",
    "\n",
    "plt.figure(figsize=(15, 6))\n",
    "\n",
    "sns.barplot(x=\"age\", y=\"count_purchased\", data=grp_gen)\n",
    "\n",
    "plt.xticks(rotation=90)\n",
    "\n",
    "plt.title(\"purchased rate by age\")\n",
    "\n",
    "plt.ylabel(\"total count of purchase\")\n",
    "\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0ac4c5ca-5b1b-49f7-b69d-46a7fc3ca5ba",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>An obvious trend can be seen in the graph, showing a <b>positive</b> association between age and purchase rates <b>up to the age of 31</b>, and a <b>negative correlation</b> thereafter. To put it another way, we can say that buyers are less inclined to buy the product as they get older. Customers, for instance, purchase fewer than 50 product overall after the age of 61.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "38b2a65a-6af7-471b-8e22-60bf20840db6",
   "metadata": {},
   "source": [
    "<hr style='height:1px;border:none;background-color:#00233C;'>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b>3.1.6 Analyze the impact of client purchasing behavior on purchase feature</b></p>    \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Exploring the customers purchase frequency in past with purchase.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a4974175-dc31-48ab-9ec1-356854bb104a",
   "metadata": {},
   "outputs": [],
   "source": [
    "query = \"\"\"\n",
    "SELECT purchase_frequency,\n",
    "       purchased,\n",
    "       Count(*) / Cast(Sum(Count(*)) OVER (partition BY purchase_frequency) AS FLOAT) * 100 AS purchased_perc\n",
    "FROM   demo_marketingcamp.retail_marketing\n",
    "GROUP  BY 1, 2\n",
    "\"\"\"\n",
    "\n",
    "df_purchase_frequency_purchased = DataFrame.from_query(query)\n",
    "df_purchase_frequency_purchased.sort(\"purchased_perc\", ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c929959f-33b3-4c8b-9001-62fcee5a6ab2",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_purchase_frequency_purchased = df_purchase_frequency_purchased.to_pandas()\n",
    "get_histogram(\n",
    "    df_purchase_frequency_purchased,\n",
    "    x=\"purchase_frequency\",\n",
    "    y=\"purchased_perc\",\n",
    "    color=\"purchased\",\n",
    "    title=\"Number of purchased by customer's purchase frequency\",\n",
    "    x_title=\"purchase frequency\",\n",
    "    y_title=\"Purchase (%)\",\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "de811479-b059-459d-9ffc-b32330f8a259",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Purchase frequency describes the number of times that your customers make a purchase from you within a specified period of time. This information is crucial in helping you to understand your customer retention rate, your customers' buying behaviors, and even the degree to which they're satisfied.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Here we can observe that:</p>\n",
    "\n",
    "<ol style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>We can see that there is a <b>higher</b> likelihood of purchasing a product when the frequency of purchases is higher, such as <b>daily, weekly, biweekly, etc.</b></li>\n",
    "    <li>The likelihood of a customer buying a product is <b>lower</b> if they only buy <b> quarterly or annually</b>.</li>\n",
    "</ol>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6a1a17ac-da09-4ca5-a160-ace622713471",
   "metadata": {},
   "source": [
    "<hr style='height:1px;border:none;background-color:#00233C;'>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b>3.1.7 Determine which earlier campaigns were the most or least successful.</b></p>    \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Examining the success and failure rates of previous campaigns with the purchasing feature.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "45a5585c-2a92-446c-9699-4cd7a2802e83",
   "metadata": {},
   "outputs": [],
   "source": [
    "query = \"\"\"\n",
    "SELECT campaign,\n",
    "       STRTOK(campaign,'_',2) as camp_no,\n",
    "       purchased,\n",
    "       Count(*) / Cast(Sum(Count(*)) OVER (partition BY campaign) AS FLOAT) * 100 AS purchased_perc\n",
    "FROM   demo_marketingcamp.retail_marketing\n",
    "GROUP  BY 1, 3\n",
    "\"\"\"\n",
    "\n",
    "df_campaign_purchased = DataFrame.from_query(query)\n",
    "df_campaign_purchased.sort(\"purchased_perc\", ascending=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "977e00d0-f928-4bdc-b26e-97104b49f6fc",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Checking the number of contacts performed during each campaign.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c9d7be97-29d0-494d-acb1-2de6c5da8031",
   "metadata": {},
   "outputs": [],
   "source": [
    "query = \"\"\"\n",
    "SELECT campaign, \n",
    "       STRTOK(campaign,'_',2) as camp_no,  \n",
    "       COUNT(1)  as total_count\n",
    "FROM DEMO_MarketingCamp.Retail_Marketing\n",
    "GROUP  BY 1\n",
    "\"\"\"\n",
    "\n",
    "df_last_campaign = DataFrame.from_query(query)\n",
    "df_last_campaign.sort(\"total_count\", ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d14be11a-7468-4de0-a6d0-2a0cc998cfa4",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_last_campaign = df_last_campaign.to_pandas()\n",
    "df_campaign_purchased = df_campaign_purchased.to_pandas()\n",
    "f, ax = plt.subplots(1, 2, figsize=(25, 6))\n",
    "\n",
    "palette = colors = [\"#dd8452\", \"#4c72b0\"]\n",
    "\n",
    "plt.suptitle(\n",
    "    \"Information on how many contacts were made, and how many of those contacts made purchases.\",\n",
    "    fontsize=16,\n",
    ")\n",
    "\n",
    "# plot1\n",
    "sns.barplot(\n",
    "    x=\"camp_no\", y=\"total_count\", data=df_last_campaign, palette=palette, ax=ax[0]\n",
    ")\n",
    "ax[0].set_ylabel(\"Number of records in this campaign\", fontsize=12)\n",
    "ax[0].set_xlabel(\"campaign id\", fontsize=12)\n",
    "\n",
    "# plot2\n",
    "sns.barplot(\n",
    "    x=\"camp_no\",\n",
    "    y=\"purchased_perc\",\n",
    "    hue=\"purchased\",\n",
    "    data=df_campaign_purchased,\n",
    "    palette=palette,\n",
    ")\n",
    "ax[1].set_ylabel(\"Number of purchased by last contact month\", fontsize=12)\n",
    "ax[1].set_xlabel(\"last_contact_month\", fontsize=12)\n",
    "\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f95b8b12-997e-425d-8f93-65654fe8a445",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The graph above demonstrates below observations.:</p>\n",
    "\n",
    "<ol style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Campaigns like <b>campaign_33, campaign_27, campaign_41, and campaign_26, campaign_29</b> have a <b>100%</b> purchase rate since there are fewer contacts made during these campaigns and every customer reached makes a purchase. It might be test campaign for marketing efforts to tests.</li>\n",
    "    <li>The <b>non-purchase rate</b>, however, is <b>100%</b> for campaigns such as <b>campaign_31, campaign_25, campaign_43, campaign_20, campaign_63, campaign_23, campaign_28, and campaign_32</b>. As we can see, only a few client connections were made throughout this campaign, and none of those customers made a purchase.</li>\n",
    "</ol>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6b15977b-f046-4a24-9637-5e897fe80119",
   "metadata": {},
   "source": [
    "<hr style='height:1px;border:none;background-color:#00233C;'>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b>3.1.8 Examine the impact of the most recent contact month on purchasing patterns</b></p>    \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Analysis of how the client's purchasing decision was impacted by the last contact month..</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "316fa7a9-df31-4b65-ae58-58d80661ebd2",
   "metadata": {},
   "outputs": [],
   "source": [
    "query = \"\"\"\n",
    "SELECT last_contact_month, \n",
    "       purchased, COUNT(*) / CAST( SUM(count(*)) over (partition by last_contact_month) as float) * 100 as purchased_perc\n",
    "FROM DEMO_MarketingCamp.Retail_Marketing\n",
    "GROUP BY 1,2\n",
    "\"\"\"\n",
    "\n",
    "df_last_contact_month_purchased = DataFrame.from_query(query)\n",
    "df_last_contact_month_purchased.sort(\"purchased_perc\", ascending=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6210185e-2fad-46b8-a1f3-6835e2c7ed04",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Checking the number of contacts performed during each months.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ab164747-94d0-4dad-827b-c61da454493c",
   "metadata": {},
   "outputs": [],
   "source": [
    "query = \"\"\"\n",
    "SELECT last_contact_month,  \n",
    "       COUNT(1)  as total_count\n",
    "FROM DEMO_MarketingCamp.Retail_Marketing\n",
    "GROUP BY 1\n",
    "\"\"\"\n",
    "\n",
    "df_last_contact_month_tot = DataFrame.from_query(query)\n",
    "df_last_contact_month_tot.sort(\"total_count\", ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3165fb66-7f2f-46bc-9477-8ced6432d6ad",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_last_contact_month_tot = df_last_contact_month_tot.to_pandas()\n",
    "df_last_contact_month_purchased = df_last_contact_month_purchased.to_pandas()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fdb804df-1a6b-47fc-884d-4a7b6fd405f6",
   "metadata": {},
   "outputs": [],
   "source": [
    "f, ax = plt.subplots(1, 2, figsize=(16, 6))\n",
    "\n",
    "colors = [\"#dd8452\", \"#4c72b0\"]\n",
    "palette = [\"#dd8452\", \"#4c72b0\"]\n",
    "\n",
    "plt.suptitle(\"Information on Last contat month and purchase\", fontsize=16)\n",
    "\n",
    "# plot1\n",
    "sns.barplot(\n",
    "    x=\"last_contact_month\",\n",
    "    y=\"total_count\",\n",
    "    data=df_last_contact_month_tot,\n",
    "    palette=palette,\n",
    "    ax=ax[0],\n",
    ")\n",
    "ax[0].set_ylabel(\"Number of contact performed last month\", fontsize=12)\n",
    "ax[0].set_xlabel(\"last_contact_month\", fontsize=12)\n",
    "\n",
    "# plot2\n",
    "sns.barplot(\n",
    "    x=\"last_contact_month\",\n",
    "    y=\"purchased_perc\",\n",
    "    hue=\"purchased\",\n",
    "    data=df_last_contact_month_purchased,\n",
    "    palette=palette,\n",
    ")\n",
    "ax[1].set_ylabel(\"Number of purchased by last contact month\", fontsize=12)\n",
    "ax[1].set_xlabel(\"last_contact_month\", fontsize=12)\n",
    "\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7edbfa14-7f98-4541-954d-502e129468b8",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In May, June, July, and August of last year, the marketing team contacted the majority of their customers.Most of those customers were contacted in the month of <b>May</b>, which is also the month in which most clients show little interest in purchasing the product.The months of <b>March, September, and December</b> see very little engagement with the customers. The customers should be contacted more frequently throughout these months.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fb1e4b64-e3d1-4819-b9b9-431e190fa3a6",
   "metadata": {},
   "source": [
    "<hr style='height:2px;border:none;background-color:#00233C;'>\n",
    "<b style = 'font-size:28px;font-family:Arial;color:#00233c'>4. Data Preparation</b>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>We'll perform the following steps:</b></p>\n",
    "<ul style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Missing Value Analysis</li>\n",
    "    <li>Data distribution plot for numerical variables.</li>\n",
    "    <li>Features selection using correlation</li>   \n",
    "    <li>FutileColumns using CategoricalSummary</li>   \n",
    "    <li>Outlier Analysis</li>\n",
    "   <li>Data Transformation</li>\n",
    "</ul>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3cd608fd-3974-4153-9245-0f6cae779007",
   "metadata": {},
   "source": [
    "<hr style='height:1px;border:none;background-color:#00233C;'>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b>4.1 Missing Value analysis</b></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "838bd523-8017-4b7b-9e17-9172f51cda82",
   "metadata": {},
   "outputs": [],
   "source": [
    "tdf.info(null_counts=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a6952d35-7304-45ed-a8d8-e7f3e60e0696",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>From the above results, Fortunately, there are no missing values. If there were missing values we will have to fill them with the median, mean,  mode or some other techniques. So, we no longer need to process missing values separately.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b41cab27-0b17-4693-848a-0b256d2bd62c",
   "metadata": {},
   "source": [
    "<hr style='height:1px;border:none;background-color:#00233C;'>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b>4.2 Distribution plots for numeric variables</b></p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6de7b153-7482-4cba-a020-fa506b7cdbd7",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Since normal distribution is of so much importance, we need to check if the collected data is normal or not. Here, we will demonstrate the Q-Q plot to check the normality of skewness of data. Q stands for quantile and therefore, Q-Q plot represents quantile-quantile plot.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>To view the QQ plot using TD_plot, first we have to prepare the data to feed into TD_plot.</p>\n",
    "\n",
    "<ol style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Create a RankTable to rank all the columns</li>\n",
    "    <li>Create Distributions table using TD_QQNorm</li>\n",
    "    <li>Create a lineGraph table using TD_Plot</li>\n",
    "\n",
    "</ol>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "549195f3-b6ce-4bb2-9be7-95f01210402d",
   "metadata": {},
   "outputs": [],
   "source": [
    "q = \"\"\"\n",
    "CREATE MULTISET TABLE RankTable AS (\n",
    "    SELECT\n",
    "       age, \n",
    "       monthly_income_in_thousand,\n",
    "       last_contact_day,\n",
    "       last_contact_duration,\n",
    "       days_from_last_contact,\n",
    "       prev_contacts_performed, \n",
    "       recency, \n",
    "    \n",
    "    CAST (ROW_NUMBER() OVER (ORDER BY age ASC NULLS LAST) AS BIGINT)\n",
    "    AS rank_age,\n",
    "    \n",
    "    CAST (ROW_NUMBER() OVER (ORDER BY monthly_income_in_thousand ASC NULLS LAST) AS BIGINT)\n",
    "    AS rank_monthly_income_in_thousand,\n",
    "    \n",
    "    CAST (ROW_NUMBER() OVER (ORDER BY last_contact_day ASC NULLS LAST) AS BIGINT)\n",
    "    AS rank_last_contact_day,\n",
    "    \n",
    "    CAST (ROW_NUMBER() OVER (ORDER BY last_contact_duration ASC NULLS LAST) AS BIGINT)\n",
    "    AS rank_last_contact_duration,\n",
    "    \n",
    "    CAST (ROW_NUMBER() OVER (ORDER BY days_from_last_contact ASC NULLS LAST) AS BIGINT)\n",
    "    AS rank_days_from_last_contact,\n",
    "    \n",
    "    CAST (ROW_NUMBER() OVER (ORDER BY prev_contacts_performed ASC NULLS LAST) AS BIGINT)\n",
    "    AS rank_prev_contacts_performed,\n",
    "    \n",
    "    CAST (ROW_NUMBER() OVER (ORDER BY recency ASC NULLS LAST) AS BIGINT)\n",
    "    AS rank_recency\n",
    "    \n",
    "    FROM DEMO_MarketingCamp.Retail_Marketing AS dt\n",
    ") WITH DATA;\n",
    "\"\"\"\n",
    "\n",
    "try:\n",
    "    execute_sql(q)\n",
    "except:\n",
    "    db_drop_table(\"RankTable\")\n",
    "    execute_sql(q)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b6a5462b-0c5c-463d-a861-32e57ebb520e",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'> TD_QQNorm checks whether the values in the specified input table columns are normally distributed. The function returns the quantiles of the column values and corresponding theoretical quantile values from a normal distribution. If the column values are normally distributed, then the quantiles of column values and normal quantile values appear in a straight line when plotted on a 2D graph.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5655230d-6a14-4d65-a3fc-63ebc876fe09",
   "metadata": {},
   "outputs": [],
   "source": [
    "q = \"\"\"\n",
    "CREATE MULTISET VOLATILE TABLE Distributions AS (\n",
    "SELECT * FROM TD_QQNorm (\n",
    "  ON RankTable AS InputTable\n",
    "  USING\n",
    "  TargetColumns ('[0:6]')\n",
    "  RankColumns ('[7:13]')) AS dt) WITH DATA\n",
    "ON COMMIT PRESERVE ROWS;\n",
    "\"\"\"\n",
    "\n",
    "try:\n",
    "    execute_sql(q)\n",
    "except:\n",
    "    db_drop_table(\"Distributions\")\n",
    "    execute_sql(q)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "46e92a00-0bff-4280-9bcb-e92c18ebafc7",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'> Create Distributions_Table from Distributions to add idcol</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d8c61a80-d1cc-4e45-8ae6-a2dd98b26050",
   "metadata": {},
   "outputs": [],
   "source": [
    "q = \"\"\"\n",
    "CREATE MULTISET VOLATILE TABLE Distributions_Table AS (\n",
    "    SELECT 1 AS idcol,\n",
    "   age, age_theoretical_quantiles, monthly_income_in_thousand, monthly_income_in_thousand_theoretical_quantiles, \n",
    "last_contact_day,last_contact_day_theoretical_quantiles, last_contact_duration, last_contact_duration_theoretical_quantiles, \n",
    "days_from_last_contact, days_from_last_contact_theoretical_quantiles, prev_contacts_performed, prev_contacts_performed_theoretical_quantiles,\n",
    "recency, recency_theoretical_quantiles\n",
    "    FROM Distributions AS dt) \n",
    "WITH DATA\n",
    "ON COMMIT PRESERVE ROWS;\n",
    "\"\"\"\n",
    "\n",
    "try:\n",
    "    execute_sql(q)\n",
    "except:\n",
    "    db_drop_table(\"Distributions_Table\")\n",
    "    execute_sql(q)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1a84bdc5-8cee-4ea8-a125-ac237f801042",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>TD_PLOT provides the ability to generate charts. The generated charts can be in the JPG, PNG, or SVG formats.TD_PLOT takes single series, many series on a single plot, and composite plots that display different result sets on a single plot. TD_PLOT supports up to 1024 different series per plot.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9f4dce77-064c-4c95-8abd-7644a37b3e9b",
   "metadata": {},
   "outputs": [],
   "source": [
    "q = \"\"\"\n",
    "EXECUTE FUNCTION INTO VOLATILE ART(lineGraph)\n",
    "TD_Plot\n",
    "(\n",
    "    SERIES_SPEC\n",
    "    (\n",
    "        TABLE_NAME(Distributions_Table),\n",
    "        ROW_AXIS(SEQUENCE(age)),\n",
    "        SERIES_ID(idcol),\n",
    "        PAYLOAD\n",
    "        (\n",
    "           FIELDS(age_theoretical_quantiles),\n",
    "           CONTENT(REAL)\n",
    "        )\n",
    "    ),\n",
    "    \n",
    "    SERIES_SPEC\n",
    "    (\n",
    "        TABLE_NAME(Distributions_Table),\n",
    "        ROW_AXIS(SEQUENCE(monthly_income_in_thousand)),\n",
    "        SERIES_ID(idcol),\n",
    "        PAYLOAD\n",
    "        (\n",
    "           FIELDS(monthly_income_in_thousand_theoretical_quantiles),\n",
    "           CONTENT(REAL)\n",
    "        )\n",
    "    ),\n",
    "    \n",
    "    SERIES_SPEC\n",
    "    (\n",
    "        TABLE_NAME(Distributions_Table),\n",
    "        ROW_AXIS(SEQUENCE(last_contact_day)),\n",
    "        SERIES_ID(idcol),\n",
    "        PAYLOAD\n",
    "        (\n",
    "           FIELDS(last_contact_day_theoretical_quantiles),\n",
    "           CONTENT(REAL)\n",
    "        )\n",
    "    ),\n",
    "    \n",
    "    SERIES_SPEC\n",
    "    (\n",
    "        TABLE_NAME(Distributions_Table),\n",
    "        ROW_AXIS(SEQUENCE(last_contact_duration)),\n",
    "        SERIES_ID(idcol),\n",
    "        PAYLOAD\n",
    "        (\n",
    "           FIELDS(last_contact_duration_theoretical_quantiles),\n",
    "           CONTENT(REAL)\n",
    "        )\n",
    "    ),\n",
    "    \n",
    "    SERIES_SPEC\n",
    "    (\n",
    "        TABLE_NAME(Distributions_Table),\n",
    "        ROW_AXIS(SEQUENCE(days_from_last_contact)),\n",
    "        SERIES_ID(idcol),\n",
    "        PAYLOAD\n",
    "        (\n",
    "           FIELDS(days_from_last_contact_theoretical_quantiles),\n",
    "           CONTENT(REAL)\n",
    "        )\n",
    "    ),\n",
    "    \n",
    "    SERIES_SPEC\n",
    "    (\n",
    "        TABLE_NAME(Distributions_Table),\n",
    "        ROW_AXIS(SEQUENCE(prev_contacts_performed)),\n",
    "        SERIES_ID(idcol),\n",
    "        PAYLOAD\n",
    "        (\n",
    "           FIELDS(prev_contacts_performed_theoretical_quantiles),\n",
    "           CONTENT(REAL)\n",
    "        )\n",
    "    ),\n",
    "    \n",
    "    SERIES_SPEC\n",
    "    (\n",
    "        TABLE_NAME(Distributions_Table),\n",
    "        ROW_AXIS(SEQUENCE(recency)),\n",
    "        SERIES_ID(idcol),\n",
    "        PAYLOAD\n",
    "        (\n",
    "           FIELDS(recency_theoretical_quantiles),\n",
    "           CONTENT(REAL)\n",
    "        )\n",
    "    ),\n",
    "    FUNC_PARAMS\n",
    "    (\n",
    "        LAYOUT(4,3),\n",
    "        WIDTH(1920),\n",
    "        HEIGHT(1080),\n",
    "        TITLE('Distribution Visulization'),\n",
    "        PLOTS[\n",
    "            (\n",
    "                ID(1),\n",
    "                CELL(1,1),\n",
    "                TITLE ('age_theoretical_quantiles'),\n",
    "                TYPE('line'),\n",
    "                MARKER('o'),\n",
    "                LEGEND('best'),\n",
    "                XLABEL('x-axis'),\n",
    "                YLABEL('Distribution')\n",
    "            ),\n",
    "            (   ID(2),\n",
    "                CELL(2,1),\n",
    "                TITLE ('monthly_income_in_thousand_theoretical_quantiles'),\n",
    "                TYPE('line'),\n",
    "                MARKER('o'),\n",
    "                LEGEND('best'),\n",
    "                XLABEL('x-axis'),\n",
    "                YLABEL('Distribution')\n",
    "            ),\n",
    "            (   ID(3),\n",
    "                CELL(3,1),\n",
    "                TITLE ('last_contact_day_theoretical_quantiles'),\n",
    "                TYPE('line'),\n",
    "                MARKER('o'),\n",
    "                LEGEND('best'),\n",
    "                XLABEL('x-axis'),\n",
    "                YLABEL('Distribution')\n",
    "            ),\n",
    "            (\n",
    "                ID(4),\n",
    "                CELL(4,1),\n",
    "                TYPE('line'),\n",
    "                TITLE ('last_contact_duration_theoretical_quantiles'),\n",
    "                TYPE('line'),\n",
    "                MARKER('o'),\n",
    "                LEGEND('best'),\n",
    "                XLABEL('x-axis'),\n",
    "                YLABEL('Distribution')\n",
    "            ),\n",
    "            (\n",
    "                ID(5),\n",
    "                CELL(1,2),\n",
    "                TYPE('line'),\n",
    "                TITLE ('days_from_last_contact_theoretical_quantiles'),\n",
    "                TYPE('line'),\n",
    "                MARKER('o'),\n",
    "                LEGEND('best'),\n",
    "                XLABEL('x-axis'),\n",
    "                YLABEL('Distribution')\n",
    "            ),\n",
    "            (\n",
    "                ID(6),\n",
    "                CELL(2,2),\n",
    "                TYPE('line'),\n",
    "                TITLE ('prev_contacts_performed_theoretical_quantiles'),\n",
    "                TYPE('line'),\n",
    "                MARKER('o'),\n",
    "                LEGEND('best'),\n",
    "                XLABEL('x-axis'),\n",
    "                YLABEL('Distribution')\n",
    "            ),\n",
    "            (\n",
    "                ID(7),\n",
    "                CELL(3,2),\n",
    "                TYPE('line'),\n",
    "                TITLE ('recency_theoretical_quantiles'),\n",
    "                TYPE('line'),\n",
    "                MARKER('o'),\n",
    "                LEGEND('best'),\n",
    "                XLABEL('x-axis'),\n",
    "                YLABEL('Distribution')\n",
    "            )\n",
    "        ]\n",
    "    )\n",
    ");\n",
    "\"\"\"\n",
    "\n",
    "try:\n",
    "    execute_sql(q)\n",
    "except:\n",
    "    db_drop_table(\"lineGraph\")\n",
    "    execute_sql(q)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3112a258-1d37-42cb-90e4-c1ca38ecb381",
   "metadata": {},
   "outputs": [],
   "source": [
    "q = \"\"\"\n",
    "create multiset table lineGraph_result as (select * from lineGraph) with data;\n",
    "\"\"\"\n",
    "\n",
    "try:\n",
    "    execute_sql(q)\n",
    "except:\n",
    "    db_drop_table(\"lineGraph_result\")\n",
    "    execute_sql(q)\n",
    "\n",
    "plot_df = DataFrame(\"lineGraph_result\").to_pandas()\n",
    "\n",
    "img = plot_df.IMAGE.iloc[0]\n",
    "Image.open(io.BytesIO(img))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8bfad3a5-e5d3-4dfd-9162-bb46eeb6863a",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Overall, a QQ plot provides a visual comparison between the quantiles of the observed data and the quantiles expected from a theoretical distribution. It helps to identify departures from the assumed distribution, such as skewness, heavy tails, or other deviations.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Interpreting a QQ plot involves examining how the observed quantiles deviate from the expected quantiles. Here are some key aspects to consider:</p>\n",
    "\n",
    "<ul style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li><b>Linearity</b>: In an ideal scenario where the data perfectly follows the theoretical distribution, the observed quantiles will align with the expected quantiles, resulting in a straight line. Deviations from a straight line suggest departures from the theoretical distribution.</li>\n",
    "\n",
    "<li><b>Slope</b>: The slope of the line provides information about the data's spread. If the line is steeper than the reference line (y = x), it indicates heavier tails or a greater spread than the theoretical distribution. Conversely, a flatter line indicates lighter tails or a smaller spread.</li>\n",
    "\n",
    "<li><b>Endpoints</b>: The behavior of the plot at the endpoints is significant. If the observed quantiles deviate from the expected quantiles at the extremes, it suggests deviations in the tails of the distribution.</li>\n",
    "\n",
    "<li><b>Outliers</b>: Outliers in the dataset can be identified as points that significantly deviate from the expected quantiles. These points might indicate extreme values or errors in the data.</li>\n",
    "\n",
    "</ul>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>From the above results, we can observe the below points:</p>\n",
    "<ol style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "<li>Age: In the Age column, we can see that the observed quantiles are nearly straight lines. So we can conclude that it is following the <b>normal distribution</b>.</li>\n",
    "<li>Monthly_income_in_thousand: The behavior of the plot at the endpoints is significant, which leads one to conclude that it is <b>not following the normal distribution</b>.</li>\n",
    "<li>Last_contact_day: In the plot, the endpoints are significant, but the rest of the quantiles are slightly left-skewed, which means this column is <b>left-skewed and heavy-tailed</b>.</li>\n",
    "<li>Last_contact_duration: The observed quantiles are heavily left-skewed, which means this column is <b>left-skewed and heavy-tailed</b>.</li>\n",
    "<li>Days_from_last_contact: In the plot, one of the endpoints is significant, and the rest of the observed quantiles are heavily left-skewed, which means this column is <b>left-skewed and heavy-tailed</b>.</li>\n",
    "<li>Prev_contacts_performed: This column is left-skewed and heavy-tailed because only one of the endpoints in the plot is significant, and the remaining observed quantiles are substantially <b>left-skewed</b>.</li>\n",
    "<li>Recency: The endpoints in the plot are notable, but the remaining quantiles are almost straight lines, which suggests that this column is pointing to <b>deviations in the distribution's tails</b>.</li>\n",
    "</ol>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5e2dfc6e-5336-41be-aa0f-f202da0279d0",
   "metadata": {},
   "source": [
    "<hr style='height:1px;border:none;background-color:#00233C;'>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b>4.3 Features selection using correlation</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Here, we'll check the correlation of all the numeric features. Measuring correlation lets you\n",
    "    determine if the value of one variable is useful in predicting the value of another.</p>\n",
    "    \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>For instance, if <b>monthly income and age</b> have a positive correlation of <b>0.7</b>, then if <b>age increases by 1 unit, monthly income will grow by 0.7 X times.</b></p>\n",
    "    \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "The Sample Pearson product moment correlation coefficient is a measure of the linear association between variables. The boundary on the computed coefficient ranges from -1.00 to +1.00.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "Note that high correlation does not imply a causal relationship between the variables. The following table indicates the meaning of four extreme values for the coefficient of correlation between two variables.\n",
    "</p>\n",
    "\n",
    "<table style = 'font-size:16px;font-family:Arial'>\n",
    "    <th>IF the correlation coefficient has this value</th>\n",
    "    <th>THEN the association between the variables</th>\n",
    "    <tr>\n",
    "        <td>-1.00</td>\n",
    "        <td>is perfectly linear, but inverse. <br>\n",
    "        As the value for y varies, the value for x varies identically in the opposite direction.</td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "        <td>0</td>\n",
    "        <td>does not exist and they are said to be uncorrelated.</td>\n",
    "    </tr>\n",
    "     <tr>\n",
    "        <td>+1.00</td>\n",
    "        <td>is perfectly linear.<br>\n",
    "        As the value for y varies, the value for x varies identically in the same direction..</td>\n",
    "    </tr>\n",
    "</table>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1b2d14d2-fe6c-4379-a567-504f33206629",
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_heatmap(df):\n",
    "    # heatmap\n",
    "    corr = df.corr(numeric_only=True)\n",
    "    mask = np.triu(np.ones_like(corr, dtype=bool))\n",
    "    fig = px.imshow(\n",
    "        corr,\n",
    "        text_auto=\".2f\",\n",
    "        width=1100,\n",
    "        height=1100,\n",
    "        aspect=\"auto\",\n",
    "        color_continuous_scale=[\"lightblue\", \"lightyellow\"],\n",
    "    )\n",
    "    return fig.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "301fb4df-4b45-490f-93b1-1208b74338ed",
   "metadata": {},
   "outputs": [],
   "source": [
    "get_heatmap(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5f7502e7-11d6-4040-ba39-f3f87030952f",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>By examining the aforementioned correlation matrix, we can find that <b>days_from_last_contact</b> and <b>prev_contacts_performed</b> have a <b>positive correlation</b> with a value of <b>0.51</b>; however, this correlation is not statistically significant. despite the fact that the correlations between the other features are relatively low, at less than 0.5.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0ebfc39e-188c-4fe3-bcd4-8f4f0b4f1cbb",
   "metadata": {},
   "source": [
    "<hr style='height:1px;border:none;background-color:#00233C;'>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b>4.4 Check FutileColumns using CategoricalSummary</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The CategoricalSummary function displays the distinct values and their counts for each specified input DataFrame column.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The GetFutileColumns function returns the futile column names if either\n",
    "    of the conditions is met: </p>\n",
    "    <ul style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "        <li>If all the values in the columns are unique</li>\n",
    "        <li>If all the values in the columns are the same</li>\n",
    "        <li>If the count of distinct values in the columns divided by the count of the total number of rows in the input data is greater than or equal to the threshold value</li>\n",
    "    </ul>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ae79b1b4-94ca-4147-9170-30bcf97e21bf",
   "metadata": {},
   "outputs": [],
   "source": [
    "cat_cols = [\n",
    "    \"profession\",\n",
    "    \"marital\",\n",
    "    \"education\",\n",
    "    \"city\",\n",
    "    \"communication_type\",\n",
    "    \"last_contact_month\",\n",
    "    \"campaign\",\n",
    "    \"payment_method\",\n",
    "    \"purchase_frequency\",\n",
    "    \"prev_campaign_outcome\",\n",
    "    \"gender\",\n",
    "    \"purchased\",\n",
    "]\n",
    "\n",
    "\n",
    "num_cols = [\n",
    "    \"customer_id\",\n",
    "    \"age\",\n",
    "    \"monthly_income_in_thousand\",\n",
    "    \"family_members\",\n",
    "    \"last_contact_day\",\n",
    "    \"credit_card\",\n",
    "    \"num_of_cars\",\n",
    "    \"last_contact_duration\",\n",
    "    \"days_from_last_contact\",\n",
    "    \"prev_contacts_performed\",\n",
    "    \"recency\",\n",
    "]\n",
    "\n",
    "\n",
    "from teradataml import *\n",
    "\n",
    "\n",
    "CategoricalSummary_out = CategoricalSummary(data=tdf, target_columns=cat_cols)\n",
    "\n",
    "\n",
    "# futile column names\n",
    "\n",
    "GetFutileColumns_out = GetFutileColumns(\n",
    "    data=tdf,\n",
    "    object=CategoricalSummary_out,\n",
    "    category_summary_column=\"ColumnName\",\n",
    "    threshold_value=0.9,\n",
    ")\n",
    "\n",
    "print(GetFutileColumns_out.result)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e5d249b3-3596-4fcd-8b74-e704a5c28ea3",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>From the above results, fortunately, there are no futile columns in our dataset. If there are any futile columns, we will have to drop them as they are not going to contribute any significant value to our model. So, we no longer need to process this separately.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f4fbb9f4-28ae-4e2b-b65a-c7459f720f42",
   "metadata": {},
   "source": [
    "<hr style='height:1px;border:none;background-color:#00233C;'>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b>4.5 Outlier Analysis</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Outliers are those data points that are significantly different from the rest of the dataset. They are often abnormal observations that skew the data distribution, and arise due to inconsistent data entry, or erroneous observations.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Let's first visualize the outliers using box-plot. In the graph the pink dots outside the box are outliers.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "08af89c6-e1f3-4820-aa3a-d9c773c953b4",
   "metadata": {},
   "outputs": [],
   "source": [
    "flierprops = dict(\n",
    "    marker=\"o\",\n",
    "    markerfacecolor=\"r\",\n",
    "    markersize=12,\n",
    "    linestyle=\"none\",\n",
    "    markeredgecolor=\"b\",\n",
    ")\n",
    "\n",
    "\n",
    "def check_outliers(df, cols):\n",
    "    plotnumber = 1\n",
    "\n",
    "    h, l, c = 10, len(cols), 4\n",
    "\n",
    "    r = int(np.ceil(l / c))\n",
    "\n",
    "    plt.figure(figsize=(20, 5 * r))\n",
    "\n",
    "    for col in cols:\n",
    "        if plotnumber <= l:\n",
    "            ax = plt.subplot(r, c, plotnumber)\n",
    "\n",
    "            plt.boxplot(df[[col]].get_values(), flierprops=flierprops)\n",
    "\n",
    "            plt.xlabel(col, fontsize=12)\n",
    "\n",
    "        plotnumber += 1\n",
    "\n",
    "    plt.tight_layout()\n",
    "\n",
    "    plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1f0fa078-37b6-4799-9182-455dad928907",
   "metadata": {},
   "outputs": [],
   "source": [
    "check_outliers(\n",
    "    tdf,\n",
    "    [\n",
    "        \"age\",\n",
    "        \"monthly_income_in_thousand\",\n",
    "        \"family_members\",\n",
    "        \"last_contact_day\",\n",
    "        \"last_contact_duration\",\n",
    "        \"days_from_last_contact\",\n",
    "        \"prev_contacts_performed\",\n",
    "        \"recency\",\n",
    "    ],\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "60e14e8a-841f-4dbf-9f6e-9b5a858dc2ad",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The pink dots outside the box in the above visualization indicate that several values, in the columns like <b>age, monthly_income_in_thousand, last_contact_duration, days_from_last_contact, and prev_contacts_performed </b>, have outliers.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b494652b-7be8-49ba-a23d-848d0e9a4914",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Now, Let's check outliers using another approach - Vantage kurtosis Function</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Vantage kurtosis Function returns the kurtosis of the distribution of value_expression.\n",
    "    Kurtosis is the fourth moment of the distribution of the standardized (z) values.\n",
    "    It is a measure of the outlier (rare, extreme observation) character of the distribution as\n",
    "    compared with the normal (or Gaussian) distribution. </p>\n",
    "<ul style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>The normal distribution has a kurtosis of 0.</li>\n",
    "    <li>Positive kurtosis indicates that the distribution is more outlier-prone than the normal distribution.</li>\n",
    "    <li>Negative kurtosis indicates that the distribution is less outlier-prone than the normal distribution.</li>\n",
    "</ul>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4c8fb09a-9732-442a-aa11-26776fb13cbe",
   "metadata": {},
   "outputs": [],
   "source": [
    "tdf[num_cols].kurtosis()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6650966d-0294-4680-8f9c-44449f775890",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>From the above table we can observe that, below columns have a positive kurtosis: </p>\n",
    "<ol style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>age</li>\n",
    "    <li>monthly_income_in_thousand</li>\n",
    "    <li>last_contact_duration</li>\n",
    "    <li>days_from_last_contact</li>\n",
    "    <li>prev_contacts_performed</li>\n",
    "</ol>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f956e5b1-a1a8-4d8d-9978-60342af0b493",
   "metadata": {},
   "outputs": [],
   "source": [
    "cols_with_outliers = [\n",
    "    \"age\",\n",
    "    \"monthly_income_in_thousand\",\n",
    "    \"last_contact_duration\",\n",
    "    \"days_from_last_contact\",\n",
    "    \"prev_contacts_performed\",\n",
    "]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "27a57939-9b90-4499-9b27-446d90f40bce",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Now, Let's use The OutlierFilterFit function calculates the lower_percentile, upper_percentile, count of rows and median for all the \"target_columns\" provided by the user. These metrics for each column helps the function OutlierTransform detect outliers in the input table. It also stores parameters from arguments into a FIT table used during transformation.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'> In the function OutlierFilterFit, we are replacing outlier values with \"NULL\". In the next step we'll impute these outlier values by mean value of that perticular column.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a0961f70-dda8-4b17-bd33-9d9258c286ff",
   "metadata": {},
   "outputs": [],
   "source": [
    "# find the outlier values and replace it with null\n",
    "OutlierFilterFit_out = OutlierFilterFit(\n",
    "    data=tdf, target_columns=cols_with_outliers, replacement_value=\"NULL\"\n",
    ")\n",
    "\n",
    "# do the actual transformation\n",
    "OutlierFilterTransform_out = OutlierFilterTransform(\n",
    "    data=tdf, object=OutlierFilterFit_out.result\n",
    ")\n",
    "\n",
    "# impute outliers with mean values\n",
    "fit_obj_num = SimpleImputeFit(\n",
    "    data=OutlierFilterTransform_out.result,\n",
    "    stats_columns=[\n",
    "        \"age\",\n",
    "        \"monthly_income_in_thousand\",\n",
    "        \"last_contact_duration\",\n",
    "        \"days_from_last_contact\",\n",
    "        \"prev_contacts_performed\",\n",
    "    ],\n",
    "    stats=\"mean\",\n",
    ")\n",
    "\n",
    "# assign imputed data to new dataframe\n",
    "tdf2 = SimpleImputeTransform(data=tdf, object=fit_obj_num.output).result"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fb3290f8-8229-42e9-af32-da4e608acc2f",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The OutlierFilterTransform function filters the outliers from the input teradataml DataFrame. OutlierFilterTransform uses the result DataFrame from OutlierFilterFit() function to get statistics like median, count of rows, lower percentile and upper percentile for every column specified in target columns argument and filters the outliers in the input data.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'> The SimpleImputeFit function outputs values to substitute for missing values in the input data. The output values are input to SimpleImputeTransform function, which makes the substitutions. </p>\n",
    "    \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The SimpleImputeTransform function substitutes specified values for missing values in the input data. The specified values is generated by SimpleImputeFit function output.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1e81443d-dc6a-48e7-a4a9-9bd950d9de01",
   "metadata": {},
   "source": [
    "<hr style='height:1px;border:none;background-color:#00233C;'>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b>4.6 Data Transformation</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Machine learning models are only as good as the data that is used to train them. A key characteristic of good training data is that it is provided in a way that is optimized for learning and generalization. The process of putting together the data in this optimal format is known in the industry as feature transformation.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Data normalization</b> is the process of making sure all values in your dataset are on the same scale. It’s a common data transformation technique, and it’s often used when working with numerical data. For instance, we have a feature <b>last_contact_duration</b> with values that are measured in <b>seconds</b> and for feature <b>age</b> with values that are measured in <b>years</b>. To develop a machine learning model using this data, you would first need to normalize it so that all the features are on the same scale. Otherwise, the model wouldn't be able to predict outcomes accurately.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Label encoding</b> is a technique used in machine learning and data analysis to convert categorical variables into numerical format. It is particularly useful when working with algorithms that require numerical input, as most machine learning models can only operate on numerical data</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>ZScore</b> will allows rescaling of continuous numeric data in a more sophisticated way than a Rescaling transformation. In a Z-Score transformation, a numeric column is transformed into its Z-score based on the mean value and standard deviation of the data in the column. Z-Score transforms each column value into the number of standard deviations from the mean value of the column. This non-linear transformation is useful in data mining rather than in a linear Rescaling transformation.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "256e1f9e-5934-4fe4-86b9-f97c4856ea06",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Define the label encoders\n",
    "\n",
    "profession_encoder = LabelEncoder(\n",
    "    values=[\n",
    "        (\"admin.\", 1),\n",
    "        (\"technician\", 2),\n",
    "        (\"services\", 3),\n",
    "        (\"management\", 4),\n",
    "        (\"retired\", 5),\n",
    "        (\"blue-collar\", 6),\n",
    "        (\"unemployed\", 7),\n",
    "        (\"entrepreneur\", 8),\n",
    "        (\"housemaid\", 9),\n",
    "        (\"unknown\", 10),\n",
    "        (\"self-employed\", 11),\n",
    "        (\"student\", 12),\n",
    "    ],\n",
    "    columns=\"profession\",\n",
    "    datatype=\"integer\",\n",
    ")\n",
    "\n",
    "marital_encoder = LabelEncoder(\n",
    "    values=[(\"married\", 1), (\"single\", 2), (\"divorced\", 3)],\n",
    "    columns=\"marital\",\n",
    "    datatype=\"integer\",\n",
    ")\n",
    "\n",
    "education_encoder = LabelEncoder(\n",
    "    values=[(\"secondary\", 1), (\"tertiary\", 2), (\"primary\", 3), (\"unknown\", 4)],\n",
    "    columns=\"education\",\n",
    "    datatype=\"integer\",\n",
    ")\n",
    "city_encoder = LabelEncoder(\n",
    "    values=[\n",
    "        (\"Philadelphia\", 1),\n",
    "        (\"San Diego\", 2),\n",
    "        (\"New York\", 3),\n",
    "        (\"Phoenix\", 4),\n",
    "        (\"Los Angeles\", 5),\n",
    "        (\"Chicago\", 6),\n",
    "        (\"Houston\", 7),\n",
    "        (\"Dallas\", 8),\n",
    "        (\"San Jose\", 9),\n",
    "        (\"San Antonio\", 10),\n",
    "    ],\n",
    "    columns=\"city\",\n",
    "    datatype=\"integer\",\n",
    ")\n",
    "communication_type_encoder = LabelEncoder(\n",
    "    values=[(\"unknown\", 1), (\"cellular\", 2), (\"telephone\", 3)],\n",
    "    columns=\"communication_type\",\n",
    "    datatype=\"integer\",\n",
    ")\n",
    "last_contact_month_encoder = LabelEncoder(\n",
    "    values=[\n",
    "        (\"jan\", 1),\n",
    "        (\"feb\", 2),\n",
    "        (\"mar\", 3),\n",
    "        (\"apr\", 4),\n",
    "        (\"may\", 5),\n",
    "        (\"jun\", 6),\n",
    "        (\"jul\", 7),\n",
    "        (\"aug\", 8),\n",
    "        (\"sep\", 9),\n",
    "        (\"oct\", 10),\n",
    "        (\"nov\", 11),\n",
    "        (\"dec\", 12),\n",
    "    ],\n",
    "    columns=\"last_contact_month\",\n",
    "    datatype=\"integer\",\n",
    ")\n",
    "\n",
    "campaign_encoder = LabelEncoder(\n",
    "    values=[\n",
    "        (\"campaign_1\", 1),\n",
    "        (\"campaign_2\", 2),\n",
    "        (\"campaign_3\", 3),\n",
    "        (\"campaign_4\", 4),\n",
    "        (\"campaign_6\", 5),\n",
    "        (\"campaign_5\", 6),\n",
    "        (\"campaign_8\", 7),\n",
    "        (\"campaign_11\", 8),\n",
    "        (\"campaign_9\", 9),\n",
    "        (\"campaign_10\", 10),\n",
    "        (\"campaign_15\", 11),\n",
    "        (\"campaign_12\", 12),\n",
    "        (\"campaign_14\", 13),\n",
    "        (\"campaign_7\", 14),\n",
    "        (\"campaign_24\", 15),\n",
    "        (\"campaign_13\", 16),\n",
    "        (\"campaign_17\", 17),\n",
    "        (\"campaign_29\", 18),\n",
    "        (\"campaign_21\", 19),\n",
    "        (\"campaign_20\", 20),\n",
    "        (\"campaign_16\", 21),\n",
    "        (\"campaign_32\", 22),\n",
    "        (\"campaign_19\", 23),\n",
    "        (\"campaign_25\", 24),\n",
    "        (\"campaign_22\", 25),\n",
    "        (\"campaign_43\", 26),\n",
    "        (\"campaign_18\", 27),\n",
    "        (\"campaign_41\", 28),\n",
    "        (\"campaign_63\", 29),\n",
    "        (\"campaign_27\", 30),\n",
    "        (\"campaign_30\", 31),\n",
    "        (\"campaign_26\", 32),\n",
    "        (\"campaign_23\", 33),\n",
    "        (\"campaign_28\", 34),\n",
    "        (\"campaign_33\", 35),\n",
    "        (\"campaign_31\", 36),\n",
    "    ],\n",
    "    columns=\"campaign\",\n",
    "    datatype=\"integer\",\n",
    ")\n",
    "\n",
    "payment_method_encoder = LabelEncoder(\n",
    "    values=[\n",
    "        (\"QRcodes\", 1),\n",
    "        (\"credit_card\", 2),\n",
    "        (\"ewallets\", 3),\n",
    "        (\"cash\", 4),\n",
    "        (\"payment_links\", 5),\n",
    "        (\"debit_card\", 6),\n",
    "    ],\n",
    "    columns=\"payment_method\",\n",
    "    datatype=\"integer\",\n",
    ")\n",
    "\n",
    "purchase_frequency_encoder = LabelEncoder(\n",
    "    values=[\n",
    "        (\"biweekly\", 3),\n",
    "        (\"quarterly\", 5),\n",
    "        (\"yearly\", 6),\n",
    "        (\"monthly\", 4),\n",
    "        (\"weekly\", 2),\n",
    "        (\"daily\", 1),\n",
    "    ],\n",
    "    columns=\"purchase_frequency\",\n",
    "    datatype=\"integer\",\n",
    ")\n",
    "\n",
    "prev_campaign_outcome_encoder = LabelEncoder(\n",
    "    values=[(\"unknown\", 1), (\"other\", 2), (\"failure\", 3), (\"success\", 4)],\n",
    "    columns=\"prev_campaign_outcome\",\n",
    "    datatype=\"integer\",\n",
    ")\n",
    "\n",
    "# OneHotEncoder\n",
    "# credit_card_encoder = OneHotEncoder(style=\"contrast\", values=1, reference_value=0, columns=\"credit_card\")\n",
    "gender_encoder = OneHotEncoder(\n",
    "    style=\"contrast\", values=\"male\", reference_value=1, columns=\"gender\"\n",
    ")\n",
    "purchased_encoder = OneHotEncoder(\n",
    "    style=\"contrast\", values=\"yes\", reference_value=\"1\", columns=\"purchased\"\n",
    ")\n",
    "\n",
    "# Define the standard scaler\n",
    "z_scaler = ZScore(\n",
    "    columns=[\n",
    "        \"age\",\n",
    "        \"monthly_income_in_thousand\",\n",
    "        \"family_members\",\n",
    "        \"last_contact_day\",\n",
    "        \"num_of_cars\",\n",
    "        \"last_contact_duration\",\n",
    "        \"days_from_last_contact\",\n",
    "        \"prev_contacts_performed\",\n",
    "        \"recency\",\n",
    "    ]\n",
    ")\n",
    "\n",
    "# Define the retain object\n",
    "retain = Retain(columns=[\"credit_card\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c9c4081b-038a-4fbc-b821-f4b24ddc23b6",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Process the transformation\n",
    "df_transformed = valib.Transform(\n",
    "    data=tdf2,\n",
    "    zscore=z_scaler,\n",
    "    label_encode=[\n",
    "        profession_encoder,\n",
    "        marital_encoder,\n",
    "        education_encoder,\n",
    "        city_encoder,\n",
    "        communication_type_encoder,\n",
    "        last_contact_month_encoder,\n",
    "        campaign_encoder,\n",
    "        payment_method_encoder,\n",
    "        purchase_frequency_encoder,\n",
    "        prev_campaign_outcome_encoder,\n",
    "    ],\n",
    "    one_hot_encode=[gender_encoder, purchased_encoder],\n",
    "    index_columns=\"customer_id\",\n",
    "    key_columns=\"customer_id\",\n",
    "    retain=retain,\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3de8dfa7-d1ef-4530-b06b-7e1a91da71ac",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The Transform function applies numeric transformations to input columns,using Fit() output.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ad5a942f-1e9d-4362-a76d-f370f63f839f",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_transformed.result.to_sql(\n",
    "    \"marketing_campaign_trans_data\", primary_index=\"customer_id\", if_exists=\"replace\"\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "58410352-e863-494b-b23c-85191097e6de",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We have applied LabelEncoder and OneHotEncoder for convert categorical features to numerical. Also applied ZScore for rescaling of continuous numerical features</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Now, we have transformed data, so to use it further first we have to save the transformed dataframe into a vantage table named <b>marketing_campaign_trans_data</b>.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7d805fbf-7d7f-47e8-a010-9be1a3da83bd",
   "metadata": {},
   "source": [
    "<hr style='height:2px;border:none;background-color:#00233C;'>\n",
    "<b style = 'font-size:28px;font-family:Arial;color:#00233c'>5. Train-Test Split</b>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In the next step, we'll split the transformed dataset into training and testing datasets in the ratio 80:20, and we will save the datasets into Vantage.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "864fba64-af69-460a-be33-dfb85e2d7f9e",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Post spliting the dataset into train/test. Let's see number of records in train and test.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "81e3a2ec-ceb9-4548-a11b-2b52137e731a",
   "metadata": {},
   "outputs": [],
   "source": [
    "query = f\"\"\"CREATE MULTISET TABLE TrainTestSplit_output AS (\n",
    "    SELECT * FROM TD_TrainTestSplit(\n",
    "        ON marketing_campaign_trans_data AS InputTable\n",
    "        USING\n",
    "        IDColumn('customer_id')\n",
    "        trainSize(0.80)\n",
    "        testSize(0.20)\n",
    "        Seed(123)\n",
    "    ) AS dt\n",
    ") WITH DATA;\"\"\"\n",
    "\n",
    "try:\n",
    "    execute_sql(query)\n",
    "except:\n",
    "    db_drop_table(\"TrainTestSplit_output\")\n",
    "    execute_sql(query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8aa34dae-72f3-4e94-b5f6-93b374083871",
   "metadata": {},
   "outputs": [],
   "source": [
    "query = f\"\"\"CREATE MULTISET TABLE rmc_train AS (\n",
    "    SELECT * FROM TrainTestSplit_output WHERE TD_IsTrainRow = 1\n",
    ") WITH DATA;\"\"\"\n",
    "\n",
    "try:\n",
    "    execute_sql(query)\n",
    "except:\n",
    "    db_drop_table(\"rmc_train\")\n",
    "    execute_sql(query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "48265848-6e24-434b-9f15-d369a372d463",
   "metadata": {},
   "outputs": [],
   "source": [
    "query = f\"\"\"CREATE MULTISET TABLE rmc_test AS (\n",
    "    SELECT * FROM TrainTestSplit_output WHERE TD_IsTrainRow = 0\n",
    ") WITH DATA;\"\"\"\n",
    "\n",
    "try:\n",
    "    execute_sql(query)\n",
    "except:\n",
    "    db_drop_table(\"rmc_test\")\n",
    "    execute_sql(query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "946509a9",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_train = DataFrame(\"rmc_train\")\n",
    "df_test = DataFrame(\"rmc_test\")\n",
    "print(\n",
    "    \"Training Set = \"\n",
    "    + str(df_train.shape[0])\n",
    "    + \". Testing Set = \"\n",
    "    + str(df_test.shape[0])\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "346840f5-df98-46b8-8ee8-4820ac8a0c86",
   "metadata": {},
   "source": [
    "<hr style='height:2px;border:none;background-color:#00233C;'>\n",
    "<b style = 'font-size:28px;font-family:Arial;color:#00233c'>6. In-Database Machine Learning</b>\n",
    "\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233c'><b>6.1 Train a XGBoost Model</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In the next step, we'll use the TD_XGBOOST function to train an xgboost model using the yes_purchased column as the target variable for classification. XGBoost's tree-based ensemble approach, regularization techniques, handling of missing values, scalability, and feature importance capabilities make it a powerful and effective choice for modeling tabular data, often leading to superior performance compared to other machine learning algorithms.\n",
    "<br>\n",
    "<br>\n",
    "The TD_XGBoost function, eXtreme Gradient Boosting, implements the gradient-boosted decision tree designed for speed and performance. It has recently been dominating applied machine learning.\n",
    "<br>\n",
    "<br>\n",
    "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",
    "</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "584b0673-4775-4970-9777-6231ac93e190",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Create a table xgb_model using TD_XGBoost from Teradata\n",
    "# The TD_XGBoost function partitions the data by any column, trains an XGBoost regression model with default trees,\n",
    "# maximum depth of 5, and 10 iterations, and saves the output to a metadata table xgb_out.\n",
    "# If the table xgb_model already exists, drop it and the metadata table xgb_out before creating the new table.\n",
    "\n",
    "query = f\"\"\"CREATE MULTISET TABLE xgb_model AS (\n",
    "SELECT * FROM TD_XGBoost(\n",
    "ON rmc_train PARTITION BY ANY\n",
    "OUT TABLE MetaInformationTable(xgb_out) \n",
    "USING\n",
    "    ResponseColumn('yes_purchased')\n",
    "    InputColumns('credit_card', 'male_gender', 'profession', 'marital', 'education', 'city', 'communication_type', 'last_contact_month', 'campaign', 'payment_method',\n",
    "    'purchase_frequency', 'prev_campaign_outcome', 'age', 'monthly_income_in_thousand', 'family_members', 'last_contact_day', 'num_of_cars', 'last_contact_duration',\n",
    "    'days_from_last_contact', 'prev_contacts_performed', 'recency')\n",
    "    MaxDepth(5)\n",
    "    NumBoostedTrees(-1)\n",
    "    ModelType('classification')\n",
    "    Seed(465)\n",
    "    ShrinkageFactor(0.1)\n",
    "    IterNum(10) \n",
    "    ColumnSampling(1.0) \n",
    ") AS dt) WITH DATA;\n",
    "\"\"\"\n",
    "\n",
    "try:\n",
    "    execute_sql(query)\n",
    "except Exception as e:\n",
    "    # Drop the tables and try again if the table already exists\n",
    "    db_drop_table(\"xgb_model\")\n",
    "    db_drop_table(\"xgb_out\")\n",
    "    execute_sql(query)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cfdf90da-89ba-4b99-9a9f-0c8fa80b7917",
   "metadata": {},
   "source": [
    "<hr style='height:1px;border:none;background-color:#00233C;'>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b>6.2 XGBoost - Model Scoring</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "In the next step, we'll use the TD_XGBoostPredict function to score the xgboost model trained in the previous step.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b8bd16ea-de41-4920-9ebc-d19ad290a703",
   "metadata": {},
   "outputs": [],
   "source": [
    "query = \"\"\"CREATE MULTISET TABLE xgb_predict_out AS (\n",
    "SELECT * FROM TD_XGBoostPredict(\n",
    "ON rmc_test 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('customer_id')\n",
    "    ModelType('classification')\n",
    "    Accumulate('yes_purchased')\n",
    ") AS dt) WITH DATA;\n",
    "\"\"\"\n",
    "\n",
    "try:\n",
    "    execute_sql(query)\n",
    "except:\n",
    "    db_drop_table(\"xgb_predict_out\")\n",
    "    execute_sql(query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "50ef0d7a-bc1b-43b0-a2aa-0dbb73b0d827",
   "metadata": {},
   "outputs": [],
   "source": [
    "xgb_result = DataFrame(\"xgb_predict_out\")\n",
    "xgb_result_pd = (\n",
    "    xgb_result.to_pandas()\n",
    "    .reset_index()\n",
    "    .sort_values(\"customer_id\")\n",
    "    .rename(columns={\"yes_purchased\": \"Actual\"})\n",
    ")\n",
    "xgb_result_pd.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e6b928d6-568d-42ca-9106-4cde60fe87a4",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Next, we'll use the TD_ClassificationEvaluator function to evaluate the trained xgboost model on test data. This will let us know how well our model has performed on unseen data.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ccf9680e-9324-4129-ab11-6464cd6a0824",
   "metadata": {},
   "outputs": [],
   "source": [
    "query = \"\"\"\n",
    "CREATE multiset table xgb_predict_out1 as (\n",
    "     select customer_id,\n",
    "     CAST(yes_purchased AS INTEGER) AS purchased,\n",
    "     CAST(Prediction AS INTEGER) as prediction,\n",
    "    Confidence_Lower, \n",
    "    Confidence_upper\n",
    "    FROM xgb_predict_out\n",
    ") with data;\"\"\"\n",
    "\n",
    "try:\n",
    "    execute_sql(query)\n",
    "except:\n",
    "    db_drop_table(\"xgb_predict_out1;\")\n",
    "    execute_sql(query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "26749c5a-fd64-4900-9f90-e560a9b546b1",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Evaluate the XGBoost model's performance using TD_RegressionEvaluator\n",
    "# Check if the necessary tables exist before executing the query\n",
    "\n",
    "\n",
    "if not get_connection().dialect.has_table(get_connection(), \"xgb_predict_out1\"):\n",
    "    print(\"Error: xgb_predict_out1 table does not exist.\")\n",
    "    sys.exit(1)\n",
    "\n",
    "query = \"\"\"\n",
    "SELECT * FROM TD_ClassificationEvaluator(\n",
    "   ON (select prediction, purchased from xgb_predict_out1) AS InputTable\n",
    "   OUT VOLATILE TABLE OutputTable(additional_metrics_xgb)\n",
    "   USING\n",
    "   ObservationColumn('purchased')\n",
    "   PredictionColumn('prediction')\n",
    "   Labels(0,1)\n",
    ") AS dt;\n",
    "\"\"\"\n",
    "\n",
    "\n",
    "try:\n",
    "    execute_sql(query)\n",
    "except:\n",
    "    db_drop_table(\"additional_metrics_xgb\")\n",
    "    execute_sql(query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e4bf9100-2654-4d9f-9a2b-1ba53ad3ffbd",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = DataFrame.from_query(\"sel * from additional_metrics_xgb\")\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9e8ed8a2-6a6f-4ed5-aaeb-f6d5cf0fc31a",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The result table displays the evaluation metrics for XGBoost models retrieved from TD_ClassificationEvaluator.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The above output has the secondary output table that returns micro, macro, and weighted-averaged metrics of precision, recall, and F1-score values.</p>\n",
    "<table style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "  <tr>\n",
    "    <th>Column</th>\n",
    "    <th>Description</th>\n",
    "  </tr>\n",
    "  <tr>\n",
    "    <td>Precision</td>\n",
    "    <td>The positive predictive value. Refers to the fraction of relevant instances among\n",
    "the total retrieved instances.</td>\n",
    "  </tr>\n",
    "  <tr>\n",
    "    <td>Recall</td>\n",
    "    <td>Refers to the fraction of relevant instances retrieved over the total amount of\n",
    "relevant instances.</td>\n",
    "  </tr>\n",
    "  <tr>\n",
    "    <td>F1</td>\n",
    "    <td>F1 score, defined as the harmonic mean of the precision and recall.</td>\n",
    "  </tr>\n",
    "  <tr>\n",
    "    <td>Support</td>\n",
    "    <td>The number of times a label displays in the ObservationColumn.</td>\n",
    "  </tr>\n",
    "</table>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6675e134-641f-4952-8e4e-c84fcbd29c74",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>A <b>confusion matrix</b> is a useful machine learning method that allows you to measure <b>recall, precision, accuracy, and AUC-ROC curve</b>. The confusion matrix is a systematic way to allocate the predictions to the original classes to which the data originally belonged. A confusion matrix is also a performance measurement technique for machine learning classification. If you train a machine learning classification model on a dataset, the resulting confusion matrix will show how accurately the model categorized each record and where there might be errors. The matrix rows represent the actual labels contained in the training dataset, and the matrix columns represent the outcomes.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "94ba1f60-a2da-48df-bda9-fb5a18228c90",
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_conf_matrix(df):\n",
    "    # df = pd.read_sql('SELECT customer_id, cast(yes_purchased as int) \"purchased\", cast(prediction as int) prediction FROM xgb_predict_out', eng)\n",
    "    cm = confusion_matrix(df[\"purchased\"], df[\"prediction\"])\n",
    "    cmd = ConfusionMatrixDisplay(cm, display_labels=[\"Not_purchased\", \"purchased\"])\n",
    "    return cm, cmd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "977409e7-c806-4aa2-ba98-890ea105f2ef",
   "metadata": {},
   "outputs": [],
   "source": [
    "cm_xgb_df = DataFrame.from_query(\n",
    "    'SELECT customer_id, cast(yes_purchased as int) \"purchased\", cast(prediction as int) prediction FROM xgb_predict_out'\n",
    ").to_pandas()\n",
    "\n",
    "cm, cmd = get_conf_matrix(cm_xgb_df)\n",
    "\n",
    "cmd.plot()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0ea2c8ce-ae11-4568-b95a-6dff35ea3dce",
   "metadata": {},
   "outputs": [],
   "source": [
    "def conf_mat_template(cm):\n",
    "    return (\n",
    "        f\"<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\"\n",
    "        f\"\"\"From the above <b>confusion matrix</b> we can conclude that\n",
    "<br><b> Out of all the actual non-purchase cases ({cm[0][0] + cm[0][1]})</b> \n",
    "    <ul style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>{round(cm[0][0]/(cm[0][0] + cm[0][1])*100, 2)}% were correctly classified as non-purchase </li>\n",
    "    <li>{round(cm[0][1]/(cm[0][0] + cm[0][1])*100, 2)}% were incorrectly classified as purchased.</li></ul>\n",
    "    <p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Similarly, out of all the actual purchase cases ({cm[1][0] + cm[1][1]}) </b></p>\n",
    "    <ul style = 'font-size:16px;font-family:Arial;color:#00233C'><li>{round(cm[1][1]/(cm[1][0] + cm[1][1])*100, 2)}% were correctly classified as purchased</li>\n",
    "    <li>{round(cm[1][0]/(cm[1][0] + cm[1][1])*100, 2)}% were incorrectly classified as non-purchase. </li></ul>\"\"\"\n",
    "        \"</p>\"\n",
    "    )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c9dc074c-be2e-408e-8a26-88bbfbf8afc3",
   "metadata": {},
   "outputs": [],
   "source": [
    "from IPython.display import display, Markdown\n",
    "\n",
    "display(Markdown(conf_mat_template(cm)))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "27dc2739-15a4-4e10-9a23-26f6510dd725",
   "metadata": {},
   "source": [
    "<hr style='height:1px;border:none;background-color:#00233C;'>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b>6.3 Train a Decision Forest Model</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The Decision Forest is a powerful method used for predicting outcomes in both classification and regression problems. It's an improvement on the technique of combining (or \"bagging\") multiple decision trees. Normally, building a decision tree involves assessing the importance of each feature in the data to determine how to divide the information. This method takes a unique approach by only considering a random subset of features at each division point in the tree. This forces each decision tree within the \"forest\" to be different from one another, which ultimately improves the accuracy of the predictions. The function relies on a training dataset to develop a prediction model. Then, the TD_DecisionForestPredict function uses the model built by the TD_DecisionForest function to make predictions. It supports regression, binary, and multi-class classification tasks.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Typically, constructing a decision tree involves evaluating the value for each input feature in the data to select a split point. The function reduces the features to a random subset (that can be considered at each split point); the algorithm can force each decision tree in the forest to be very different to improve prediction accuracy. The function uses a training dataset to create a predictive model. The TD_DecisionForestPredict function uses the model created by the TD_DecisionForest function for making predictions. The function supports regression, binary, and multi-class classification.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Consider the following points:\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>All input features are numeric. Convert the categorical columns to numerical columns as preprocessing step.</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>For classification, class labels (ResponseColumn values) can only be integers. A maximum of 500 classes is supported for classification.</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>Observations with missing values in any input column will be ignored during training. To fill in missing values, use the TD_SimpleImpute function.</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>The number of trees built by the TD_DecisionForest function depends on the values of NumTrees, TreeSize, and CoverageFactor, as well as the data distribution in the cluster. The trees are built simultaneously by all the processing units (AMPs) that have a non-empty portion of the data.</li>\n",
    "</p>\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8eb52d8f-5f99-404c-b3ca-ef5ef051d4d3",
   "metadata": {},
   "outputs": [],
   "source": [
    "query = \"\"\"Create multiset table DF_train as (\n",
    "SELECT * FROM TD_DecisionForest (\n",
    "    ON rmc_train AS INPUTTABLE partition by ANY\n",
    "USING\n",
    "    ResponseColumn('yes_purchased')\n",
    "    InputColumns('credit_card', 'male_gender', 'profession', 'marital', 'education', 'city', 'communication_type', 'last_contact_month', 'campaign', 'payment_method',\n",
    "    'purchase_frequency', 'prev_campaign_outcome', 'age', 'monthly_income_in_thousand', 'family_members', 'last_contact_day', 'num_of_cars', 'last_contact_duration',\n",
    "    'days_from_last_contact', 'prev_contacts_performed', 'recency')\n",
    "    MaxDepth(10)\n",
    "    MinNodeSize(1)\n",
    "    NumTrees(5)\n",
    "    ModelType('CLASSIFICATION')\n",
    "    Seed(1)\n",
    "    Mtry(-1)\n",
    "    MtrySeed(1)\n",
    ") AS dt\n",
    ") with data;\n",
    "\"\"\"\n",
    "try:\n",
    "    execute_sql(query)\n",
    "except:\n",
    "    db_drop_table(\"DF_train\")\n",
    "    execute_sql(query)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d4dffb08-5cdc-4f98-89ef-008ceed716ca",
   "metadata": {},
   "source": [
    "<hr style='height:1px;border:none;background-color:#00233C;'>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b>6.4 Decision Forest - Model Scoring</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "In the next step, we'll use the TD_DecisionForestPredict function to score the decision forest model trained in the previous step.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8c6a1d81-4c46-49b5-b9dd-96778aed0c75",
   "metadata": {},
   "outputs": [],
   "source": [
    "query = \"\"\"\n",
    "Create multiset table DF_Predict_out as (\n",
    "SELECT * FROM TD_DecisionForestPredict (\n",
    "ON rmc_test AS InputTable PARTITION BY ANY\n",
    "ON DF_train AS ModelTable DIMENSION\n",
    "USING\n",
    "  IdColumn ('customer_id')\n",
    "  Detailed('false')\n",
    "  Accumulate('yes_purchased')\n",
    ") AS dt) with data;\"\"\"\n",
    "\n",
    "try:\n",
    "    execute_sql(query)\n",
    "except:\n",
    "    db_drop_table(\"DF_Predict_out\")\n",
    "    execute_sql(query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "473012c3-4272-4bfa-871b-ccf536dee698",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_result = DataFrame(\"DF_Predict_out\")\n",
    "df_result_pd = (\n",
    "    df_result.to_pandas()\n",
    "    .reset_index()\n",
    "    .sort_values(\"customer_id\")\n",
    "    .rename(columns={\"yes_purchased\": \"Actual\"})\n",
    ")\n",
    "df_result_pd.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "96b982cc-70dd-45f6-bcb2-76f0a70433ec",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The TD_CLASSIFICATIONEVALUATOR function computes metrics to evaluate and compare multiple models and summarizes how close predictions are to their expected values.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "368ab80f-c9c8-4a67-a573-30fdd61ec2db",
   "metadata": {},
   "outputs": [],
   "source": [
    "query = \"\"\"\n",
    "CREATE multiset table DF_Predict_out1 as (\n",
    "     select customer_id,\n",
    "     CAST(yes_purchased AS INTEGER) AS purchased,\n",
    "     CAST(Prediction AS INTEGER) as prediction,\n",
    "    Confidence_Lower, \n",
    "    Confidence_upper\n",
    "    FROM DF_Predict_out\n",
    ") with data;\"\"\"\n",
    "\n",
    "try:\n",
    "    execute_sql(query)\n",
    "except:\n",
    "    db_drop_table(\"DF_Predict_out1\")\n",
    "    execute_sql(query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8e74e776-fc89-47e7-8bf5-5efe1c08dabf",
   "metadata": {},
   "outputs": [],
   "source": [
    "query = \"\"\"\n",
    "SELECT * FROM TD_CLASSIFICATIONEVALUATOR(\n",
    "    ON  DF_Predict_out1 AS InputTable\n",
    "OUT TABLE OutputTable(additional_metrics_df)\n",
    "USING\n",
    "    Labels(0,1)\n",
    "    ObservationColumn('purchased')\n",
    "    PredictionColumn ('prediction')\n",
    ") as dt1 ; \n",
    "\"\"\"\n",
    "\n",
    "try:\n",
    "    execute_sql(query)\n",
    "except:\n",
    "    db_drop_table(\"additional_metrics_df\")\n",
    "    execute_sql(query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "de2b47e8-bc63-4845-87c4-ae1574d32566",
   "metadata": {},
   "outputs": [],
   "source": [
    "DF_metrics = DataFrame.from_query(\"sel * from additional_metrics_df\")\n",
    "DF_metrics"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d14a5946-76bb-4066-8d5c-1b5306bd7dc8",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The result table displays the evaluation metrics for DecisionForest models retrieved from TD_CLASSIFICATIONEVALUATOR.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b554f6fc-00af-4fdf-8285-7df389ef01af",
   "metadata": {},
   "outputs": [],
   "source": [
    "cm_df_df = DataFrame.from_query(\n",
    "    \"SELECT customer_id, purchased, prediction FROM DF_Predict_out1\"\n",
    ").to_pandas()\n",
    "\n",
    "cm, cmd = get_conf_matrix(cm_df_df)\n",
    "\n",
    "cmd.plot()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "870b3293-738f-4af8-b9c6-673d943b709d",
   "metadata": {},
   "outputs": [],
   "source": [
    "display(Markdown(conf_mat_template(cm)))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "66e69e89-62c6-4173-9cfe-d322fe0a58a3",
   "metadata": {},
   "source": [
    "<hr style='height:2px;border:none;background-color:#00233C;'>\n",
    "<b style = 'font-size:28px;font-family:Arial;color:#00233c'>7. Visualize the results</b>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Here we have used 2 models for training and evaluation. From Vantage TD_CLASSIFICATIONEVALUATOR function is used to evaluate and compare the models.</p>  "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0f776985-2b91-4ee1-83b8-7effd17612cc",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Let's visualise the the Decision Forest Vs XGBoost evaluation result to compare values in graph.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c3c18886-a78b-42f5-8642-cb075688ceb9",
   "metadata": {},
   "outputs": [],
   "source": [
    "query = \"\"\"CREATE MULTISET TABLE metric_union as (select cast('XGBoost' as VARCHAR(15)) as Model, trim(Metric) as Metric,MetricValue from additional_metrics_xgb a \n",
    "union all \n",
    "select 'DecisionForest' as Model ,  trim(Metric) as Metric,MetricValue from additional_metrics_df b\n",
    ")with data PRIMARY INDEX (Metric)\n",
    ";\n",
    "\"\"\"\n",
    "\n",
    "try:\n",
    "    execute_sql(query)\n",
    "except:\n",
    "    db_drop_table(\"metric_union\")\n",
    "    execute_sql(query)\n",
    "\n",
    "df_chart = DataFrame.from_query(\"select * from metric_union\")\n",
    "df_chart = df_chart.to_pandas().reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b0b2946e-c6ab-4c21-b274-4ef82a1c08c1",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_chart[\"Metric\"] = df_chart[\"Metric\"].str.replace(\"\\x00\", \"\")\n",
    "fig = px.bar(\n",
    "    df_chart,\n",
    "    x=\"Metric\",\n",
    "    y=\"MetricValue\",\n",
    "    color=\"Model\",\n",
    "    barmode=\"group\",\n",
    "    title=\"Compare models\",\n",
    "    labels={\"Metric\": \"Metrics\", \"MetricValue\": \"Metric Values\"},\n",
    ")\n",
    "\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "11483131-0cf2-495c-9366-3edd74dc8dbb",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The Decision Forest and XGBoost models are compared using the aforementioned measures. We can observe that the performance of the Decision Forest and XGBoost models is essentially the same.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1cfd4112-e2d8-4504-b8b4-7e5b985105d3",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Another way to compare and select the best model is by calculate AUC(Area Under the Curve) for Receiver Operating Characteristic Curve</p>\n",
    "\n",
    "<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. AUC above 0.75 is generally considered decent.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Here we can see the comparison for AUC and ROC for XGBoost and DecisionForest.</p> \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "27acc07c-5b0a-468e-9383-e0a23d2ff583",
   "metadata": {},
   "outputs": [],
   "source": [
    "# ROC curve for Decision Tree model\n",
    "result_dt_pandas = DataFrame(in_schema(\"demo_user\", \"DF_Predict_out1\")).to_pandas()\n",
    "fpr_dt, tpr_dt, thresholds_dt = roc_curve(\n",
    "    result_dt_pandas[\"purchased\"], result_dt_pandas[\"prediction\"]\n",
    ")\n",
    "auc_dt = roc_auc_score(result_dt_pandas[\"purchased\"], result_dt_pandas[\"prediction\"])\n",
    "plt.plot(\n",
    "    fpr_dt,\n",
    "    tpr_dt,\n",
    "    color=\"orange\",\n",
    "    label=\"Decision Tree ROC. AUC = {}\".format(str(round(auc_dt, 4))),\n",
    ")\n",
    "\n",
    "# ROC curve for XGB\n",
    "result_xgb_pandas = DataFrame(in_schema(\"demo_user\", \"xgb_predict_out1\")).to_pandas()\n",
    "fpr_xgb, tpr_xgb, thresholds_xgb = roc_curve(\n",
    "    result_xgb_pandas[\"purchased\"], result_xgb_pandas[\"prediction\"]\n",
    ")\n",
    "auc_xgb = roc_auc_score(result_xgb_pandas[\"purchased\"], result_xgb_pandas[\"prediction\"])\n",
    "plt.plot(\n",
    "    fpr_xgb,\n",
    "    tpr_xgb,\n",
    "    color=\"green\",\n",
    "    label=\"XGB ROC. AUC = {}\".format(str(round(auc_xgb, 4))),\n",
    ")\n",
    "\n",
    "\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\")\n",
    "plt.legend()\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0940076c-fea5-40b5-8a83-c590960e1083",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We may state with confidence that the model has performed well on testing data by looking at the ROC Curve shown above. The AUC number is close to 0.75, which supports our perception that the model is operating effectively. The graph above shows that the performance of both models (XGBoost and DecisionForest) is very similar. The performances barely differ from one another.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "402b7ab3-a5db-4392-a67a-36b1f016c887",
   "metadata": {},
   "source": [
    "<hr style='height:1px;border:none;background-color:#00233C;'>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b>7.1 Conclusion:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In conclusion, the implementation of a retail marketing campaign solution can greatly benefit to the client by reducing reducing the marketing efforts and cost along with annoyance of customers.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>If Multiple Campaigns and multiple contacts are performed for the customers, there is more chance for the customers to not interested to purchase the product. Almost 2 or 3 contacts can be preferred to perform for the customers.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "50b8b817-c0ff-42b3-a651-c8268ac40942",
   "metadata": {},
   "source": [
    "<hr style='height:2px;border:none;background-color:#00233C;'>\n",
    "<b style = 'font-size:28px;font-family:Arial;color:#00233c'>8. Cleanup</b>\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b>8.1 Work Tables</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Cleanup work tables to prevent errors next time.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a3fd8f89-67ed-4f8e-9942-ed37fbd30098",
   "metadata": {},
   "outputs": [],
   "source": [
    "tables = [\n",
    "    \"marketing_campaign_trans_data\",\n",
    "    \"rmc_train\",\n",
    "    \"rmc_test\",\n",
    "    \"TrainTestSplit_output\",\n",
    "    \"xgb_out\",\n",
    "    \"xgb_model\",\n",
    "    \"additional_metrics_xgb\",\n",
    "    \"xgb_predict_out1\",\n",
    "    \"DF_train\",\n",
    "    \"DF_Predict_out\",\n",
    "    \"DF_Predict_out1\",\n",
    "    \"additional_metrics_df\",\n",
    "    \"metric_union\",\n",
    "    \"RankTable\",\n",
    "    \"Distributions\",\n",
    "    \"Distributions_Table\",\n",
    "    \"lineGraph\",\n",
    "    \"lineGraph_Result\",\n",
    "]\n",
    "\n",
    "\n",
    "for t in tables:\n",
    "    try:\n",
    "        db_drop_table(table_name=t)\n",
    "\n",
    "    except:\n",
    "        pass"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0aacb87c-0d81-40f0-8754-608d47e602cc",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233c'> <b>8.2 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": "7b398148-9486-4535-8c06-d3f77669bbdc",
   "metadata": {},
   "outputs": [],
   "source": [
    "%run -i ../run_procedure.py \"call remove_data('DEMO_MarketingCamp');\"        # Takes 5 seconds"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "73ef4cbe-1beb-4c65-ac60-ffd6250668a4",
   "metadata": {},
   "outputs": [],
   "source": [
    "remove_context()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ae971f90-bef1-4228-bcbf-c333e9843284",
   "metadata": {},
   "source": [
    "<b style = 'font-size:28px;font-family:Arial;color:#00233c'>Dataset:</b>\n",
    "\n",
    "- `customer_id`: Unique row customer id\n",
    "- `age`: customer age (numeric)\n",
    "- `profession` : type of job (categorical: \"admin.\",\"unknown\",\"unemployed\",\"management\",\"housemaid\",\"entrepreneur\",\"student\",\"blue-collar\",\"self-employed\",\"retired\",\"technician\",\"services\")\n",
    "- `marital` : marital status (categorical: \"married\",\"divorced\",\"single\"; note: \"divorced\" meansdivorced or widowed)\n",
    "- `education` customer eduction (categorical: \"unknown\",\"secondary\",\"primary\",\"tertiary\")\n",
    "- `city`: city of customer (categorical: 'New York','Los Angeles','Chicago','Houston','Phoenix','Philadelphia','San Antonio','San Diego','Dallas','San Jose')\n",
    "- `monthly_income_in_thousand`: customer's monthly income, in dollar (numeric)\n",
    "- `family_members`: number of family members (numeric)\n",
    "- `communication_type`: communication type (categorical: \"unknown\",\"telephone\",\"cellular\")\n",
    "- `last_contact_day`: last contact day of the month (numeric)\n",
    "- `last_contact_month`: last contact month of year (categorical: \"jan\", \"feb\", \"mar\", ..., \"nov\", \"dec\")\n",
    "- `credit_card`: does customer have a credit card? (binary: 'yes','no')\n",
    "- `num_of_cars`: number of cars (numeric)\n",
    "- `last_contact_duration`: last contact duration, in seconds (numeric)\n",
    "- `campaign`: number of contacts performed during this campaign and for this client (categorical,includes last contact)\n",
    "- `days_from_last_contact`: number of days that passed by after the client was last contacted from a previouscampaign (numeric, -1 means client was not previously contacted)\n",
    "- `prev_contacts_performed`: number of contacts performed before this campaign and for this client (numeric)\n",
    "- `prev_campaign_outcome`: outcome of the previous marketing campaign (categorical:\"unknown\",\"other\",\"failure\",\"success\")\n",
    "- `payment_method`: payment method use by customer (categorical: 'cash','credit_card','debit_card','ewallets', 'payment_links', 'QRcodes')\n",
    "- `purchase_frequency`: how frequently customer is purchasing (categorical: 'daily','weekly','biweekly','monthly','quarterly','yearly')\n",
    "- `gender`: gender of customer? (binary: 'male','female')\n",
    "- `recency`: number of days since the last purchase (numeric)\n",
    "\n",
    "\n",
    "Output variable (desired target):\n",
    "- `purchased`: does customer did a purchase - target column (binary: 'yes','no')\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233c'><b>Links:</b></p>\n",
    "<ul style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Teradataml Python reference: <a href = 'https://docs.teradata.com/search/all?query=Python+Package+User+Guide&content-lang=en-US'>here</a></li>\n",
    "</ul>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e5463848-592f-4321-b852-287e133872dd",
   "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"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}