UseCases/MultiTouch_Attribution/Analyst_MultiTouch_Attribution_PY_SQL.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "5169c9ee-1e13-461e-b599-d615c9873035",
   "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",
    "       Multi Touch Attribution 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>\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Target Audience</b></p>\n",
    " \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>This notebook is a simplified version of the MultiTouch_Attribution_PY_SQL notebook as it is targeted for the Business Analyst persona rather than the Data Scientist persona.</p>  \n",
    "    \n",
    "<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 attribution modelling techniques aim to determine the contribution of each marketing touchpoint or channel in influencing customer behavior and driving conversions. These models provide valuable insights into the effectiveness of marketing efforts, helping businesses make informed decisions regarding resource allocation and optimization.</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><a href='#rule'>Rule-based</a> attribution modelling relies on predetermined rules or heuristics to assign credit to various touchpoints along the customer journey. Common rule-based models include the First Touch, Last Touch, Uniform (linear) and Exponential(time decay) models. The First Touch model attributes all credit to the first touchpoint a customer interacts with, while the Last Touch model assigns all credit to the final touchpoint before conversion. The Uniform model evenly distributes credit across all touchpoints in the customer journey. The Exponential model assigns more credit to touchpoints closer to the conversion event.<p>\n",
    "    \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><a href='#stat'>Statistical</a> and <a href='#ml'>Algorithmic-based</a> attribution modelling, on the other hand, utilizes advanced statistical and machine learning techniques to determine the contribution of each touchpoint. These models take into account various factors such as the order, timing, and interaction patterns of touchpoints.<p>\n",
    "   \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>All approaches have their strengths and limitations. <a href='#rule'>Rule-based</a> models are relatively straight forward to implement and interpret, but they may oversimplify the complexity of customer journeys. <a href='#ml'>Algorithmic-based</a> models offer more sophisticated and granular insights but may require advanced analytics expertise and extensive data sets to achieve accurate results.\n",
    "It's important for businesses to select the most suitable attribution modelling approach based on their specific goals, available data, and resources. Implementing an effective marketing attribution model can significantly enhance decision-making and optimize marketing strategies.<p>\n",
    "    \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Marketing attribution modelling techniques aim to determine the contribution of each marketing touchpoint or channel. Determining the importance of each interaction can aid in influencing customer behavior and driving conversions. Using the touchpoints to create models can provide valuable insights into the effectiveness of marketing efforts, which in turn will help businesses make informed decisions regarding resource allocation and optimization. With Teradata Vantage and ClearScape Analytics, users can get a full picture of their customer’s digital actions.  Using pathing analytics, businesses can understand the common paths that customers take that lead to a variety of outcomes, such as sales conversion, cart abandonment, or product searches. When businesses use Vantage to analyze all their data at scale, they have the chance to increase customer satisfaction and conversion rates.</p>\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Business Value</b></p>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>Increased customer conversion and attribution rates</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>Decreased customer churn and broken journeys</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>Provides an understanding of customer activity and touchpoints</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>Improve customer satisfaction by optimizing processes related to the touchpoints</li><p></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Why Vantage? </b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Teradata Vantage provides a variety of attribution modeling including rule-based, statistical, and algorithmic-based attribution. Each has their own strengths for a variety of team across an organization. and limitations, while being useful across an organization. Vantage has unique analytic capabilities for understanding customer and user behavior over time. Thus, implementing an effective marketing attribution model, using Teradata Vantage, can significantly enhance decision-making and optimize marketing strategies.</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Also, ClearScape Analytics provides powerful, flexible attribution analysis, text processing, and statistical analytic techniques that can be applied to millions or billions of customers touchpoints. These results can be combined with other analytics to create more accurate models. Plus, Vantage allows organizations to scale these models horizontally (train segmented models per region, user type, etc.) or vertically (combine data from millions or billions of interactions). These models can be deployed operationally to understand and predict actions in real-time.</p>\n",
    "    \n",
    "    \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In this use case we will show several different analytic techniques to perform Multi Touch Attribution modelling and analysis using Vantage.<p>\n",
    "<img src=\"images/Attribution.png\">    \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Our innovative approach includes the use of <a href='#path'>Path Analysis</a> not only to identify and visualize customer conversion journeys but also to prepare data for advanced and sometimes creative techniques.<p>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ab390ec7-a37a-4554-8856-a10df8376831",
   "metadata": {
    "tags": []
   },
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>1. Connect to Vantage</b></p>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a89a8fb9-851a-4b22-9438-3e6bf5e0b0dd",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In the section, we import the required libraries and set environment variables and environment paths (if required).</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6d11eed5-7cdc-421d-aeb7-780a2274353e",
   "metadata": {},
   "outputs": [],
   "source": [
    "import teradataml as tdml\n",
    "import getpass\n",
    "import pandas as pd\n",
    "import plotly.express as px\n",
    "from sklearn.ensemble import RandomForestClassifier\n",
    "import seaborn as sns\n",
    "import matplotlib.pyplot as plt\n",
    "import tdnpathviz\n",
    "from teradataml import *\n",
    "import warnings\n",
    "warnings.filterwarnings('ignore')\n",
    "display.max_rows = 5\n",
    "from teradataml import configure\n",
    "configure.val_install_location = \"val\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4884487c-aaad-42bb-a4f6-d5c465349194",
   "metadata": {
    "tags": []
   },
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We will be prompted to provide the password. We will enter the password, press the Enter key, and then use the down arrow to go to the next cell. Begin running steps with Shift + Enter keys.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3c20182f-0ebb-452e-bc65-bc94ca218a0c",
   "metadata": {
    "tags": []
   },
   "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": "ffe0164d-72c1-4ce3-8e8a-242ca307e37a",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "%%capture\n",
    "execute_sql('''SET query_band='DEMO=Analyst_MultiTouch_Attribution_PY_SQL.ipynb;' UPDATE FOR SESSION; ''')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "731c11a2-f615-4c84-afd5-6341ed8fdd55",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>2. 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. We have the option of either running the demo using foreign tables to access the data without using any storage on our environment or downloading the data to local storage, which may yield somewhat faster execution. However, we need to consider available storage. There are two statements in the following cell, and one is commented out. We may switch which mode we choose by changing the comment string. In this demo as we are using the nPath function with needs all character data in LATIN character set, we will only use the local option of creating tables and DDL.</p>   \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "db442539-86f1-491a-985b-f6c634c62866",
   "metadata": {},
   "outputs": [],
   "source": [
    "%run -i ../run_procedure.py \"call get_data('DEMO_MultiTouchAttribution_local');\"\n",
    " # Takes about 1 minute 30 secs"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8076a002-0d9d-4ce1-a470-4fd63662de0c",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Next is an optional step – if you want to see status of databases/tables created and space used.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0ed7ac55-4477-4429-bae3-569f76a85ed4",
   "metadata": {},
   "outputs": [],
   "source": [
    "%run -i ../run_procedure.py \"call space_report();\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "95a05f9a-2d1b-417c-9ca7-bef0ddb1422c",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>3. Analyze the raw data set</b></p>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Data</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The dataset is digital marketing data containing 586,000 marketing touchpoints from July (2018), comprising 240,000 unique customers who generated ~18,000 conversions. A more detailed description of the features is shown below:\n",
    "\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>Cookie: Anonymous customer id enabling us to track the progression of a given customer</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>Timestamp: Date and time when the visit took place</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>Interaction: Categorical variable indicating the type of interaction that took place</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>Conversion: Boolean variable indicating whether a conversion took place</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>Conversion Value: Value of the potential conversion event (revenue)</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>Channel: The marketing channel that brought the customer to our site</li>\n",
    "</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Let us start by creating a teradataml dataframe. A \"Virtual DataFrame\" that points directly to the dataset in Vantage.</p>\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "290e859e-acdb-4908-84de-fddc3761be96",
   "metadata": {},
   "outputs": [],
   "source": [
    "attr_df = DataFrame(in_schema('DEMO_MultiTouchAttribution', 'Attribution_Data'))\n",
    "attr_df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "470bce51-9053-407e-a1e1-1bba46265fbd",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The Attribution data contains the channel details with the timestamp of the conversion , its conversion value and cost. We select the required data and do aggregations by channel to check conversions based on the types of channels.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b71b310c-66ad-431d-b38c-2068d9df95d3",
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy import literal_column\n",
    "column2 = literal_column(\"cast('2018-07-30' as Date)\")\n",
    "conversions_df = attr_df.loc[attr_df['conversion'] == 1]\n",
    "conversions_df = conversions_df.assign(time = conversions_df.tmstp.cast(type_=DATE))\n",
    "conversions_df = conversions_df[conversions_df['time'] < column2]\n",
    "conversions_df = conversions_df.drop(['cookie', 'interaction'], axis=1)\n",
    "conversions_df = conversions_df.select(['conversion', 'conversion_value',\n",
    "                           'cost', 'channel']).groupby('channel').sum()\n",
    "conversions_df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4336066b-2f41-49a9-9a32-85f066736595",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We can see that the aggregated data is available to us in a teradataml dataframe. Let's visualize this data to better understand the Conversion values by the types of Channels. ClearScape Analytics can easily integrate with 3rd party visualization tools like Tableau, PowerBI or many python modules available like plotly, seaborn etc. We can do all the calculations and pre-processing on Vantage and pass only the necessary information to visualization tools.  This will not only make the calculation faster but also reduce the overall time due to less data movement between tools. We only transfer data for this and the subsequent visualizations wherever necessary.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2a78a7a5-fd66-41dd-94ac-39de6c1ecd9c",
   "metadata": {},
   "outputs": [],
   "source": [
    "conversions = conversions_df.to_pandas()\n",
    "fig = px.bar(data_frame = conversions, x = 'channel', y = 'sum_conversion', color = 'channel')\n",
    "\n",
    "fig.update_layout(title = 'Channel Conversions',\n",
    "                   xaxis_title = 'Channel',\n",
    "                   yaxis_title = 'Conversions')\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b3cd4e5a-828b-4cd2-a65b-58769b7b8ba4",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The above chart shows the number of conversions by each Channel.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "757fe695-eda3-4f8d-b166-a5351d55bee4",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "channel_df = DataFrame(in_schema('DEMO_MultiTouchAttribution', 'Channel_Cost'))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f3ba2f3c-db0b-44fa-9fc4-fb62122f23f5",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The Channel data contains the channels and cost.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "180bfac7-22a1-4d03-8bef-4b1fe4a4c116",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_plot = channel_df.to_pandas().reset_index()\n",
    "fig, ax = plt.subplots(figsize=(8, 5))\n",
    "sns.barplot(x = 'channel',y = 'cost',data = df_plot)\n",
    "plt.xlabel('Channel')\n",
    "plt.ylabel('Cost of Conversion')\n",
    "plt.title('Channel Cost')\n",
    "\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a9436093-5e7d-4c97-a7fa-f34d63494fdb",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The cost of Online Video is the highest and Instagram is the lowest.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "328e077e-4c13-48ac-af1f-a8dbb7f72856",
   "metadata": {
    "tags": []
   },
   "source": [
    "<a id=\"path\"></a>\n",
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>4. PATH ANALYSIS</b></p>\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d859aeb5-d31c-441e-b732-b475a0578785",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>4.1. Use nPath® to visualize conversion journeys</b></p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "93e16ad0-af95-49cb-b7a0-9efd997bd36d",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We want to see how our customers are converting.</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The nPath function scans a set of rows, looking for patterns that you specify. For each set of input rows that matches the pattern, nPath produces a single output row. The function provides a flexible pattern-matching capability that lets you specify complex patterns in the input data and define the values that are output for each matched input set.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>nPath® is useful when your goal is to identify the paths that lead to an outcome. For example, you can use nPath to analyze:\n",
    "\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>Web site click data, to identify paths that lead to sales over a specified amount\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>Sensor data from industrial processes, to identify paths to poor product quality\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>Healthcare records of individual patients, to identify paths that indicate that patients are at risk of developing conditions such as heart disease or diabetes\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>Financial data for individuals, to identify paths that provide information about credit or fraud risks.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In the code here we can see a few key points:</p>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>The 'Pattern' we are searching for is 8 events followed by conversion (conversion =1).</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>The 'Symbols' we are using is anything but converting is 'EVENT' and conversion column = 1 is 'CONVERSION'.</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>We create a dummy 'Conversion' event to enable its visualization.</li>\n",
    "</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c4338d52-4cda-4d2b-af6c-7f990df00f16",
   "metadata": {},
   "outputs": [],
   "source": [
    "npath_sessions = NPath(data1 = attr_df, \n",
    "                      data1_partition_column = ['cookie'], \n",
    "                      data1_order_column = ['tmstp'], \n",
    "                      mode = 'NONOVERLAPPING', \n",
    "                      symbols = ['conversion=\\'1\\' as CONVERSION, conversion=\\'0\\' as EVENT'], \n",
    "                      pattern = 'EVENT{0,8}.CONVERSION', \n",
    "                      result = ['ACCUMULATE (case when conversion=\\'1\\' then \\'Conversion\\' else channel end OF ANY(CONVERSION,EVENT)) AS path',\n",
    "                                  'COUNT (* of ANY(CONVERSION,EVENT)) as event_cnt',\n",
    "                                  'FIRST (cookie OF ANY(CONVERSION,EVENT)) AS cookie'])\n",
    "\n",
    "\n",
    "convcntpath = npath_sessions.result\n",
    "convcntpath"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9bb60828-9957-4f76-847d-3937f7d8a474",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>A visualization of this gives us lots of insight into the most common paths (the top 50) that users are taking before converting. A Sankey Diagram can be created using the output(path) of the nPath function used in the query above.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><i>**The code in the below cell is the definition of the sankeyPlot which is used below when we visualize the Paths to Conversion and Paths to conversion by cost.</i></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b7497508-50e4-4115-87cc-7a3f9e2122a8",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Convert Teradata nPath output to plotly Sankey\n",
    "#can handle paths up to 999 links in length\n",
    "import pandas as pd\n",
    "import plotly.graph_objects as go\n",
    "from collections import defaultdict\n",
    "import random\n",
    "\n",
    "def sankeyPlot(res, direction, title_text=\"Sankey nPath\", topN=15):\n",
    "    npath_pandas = res.copy()\n",
    "\n",
    "    if topN:\n",
    "        npath_pandas = npath_pandas.sort_values(by='count_event_cnt', ascending=False).head(topN)\n",
    "\n",
    "    if direction == \"from\":\n",
    "        dataDict = defaultdict(int)\n",
    "\n",
    "        for index, row in npath_pandas.iterrows():\n",
    "            pathCnt = row['count_event_cnt']\n",
    "            rowList = [item.strip() for item in row['path'].replace('[','').replace(']','').split(',')]\n",
    "            for i in range(len(rowList)-1):\n",
    "                leftValue = rowList[i] + str(i)\n",
    "                rightValue = rowList[i+1] + str(i+1)\n",
    "                valuePair = leftValue + '+' + rightValue\n",
    "                dataDict[valuePair] += pathCnt\n",
    "\n",
    "        eventList = []\n",
    "        for key in dataDict.keys():\n",
    "            leftValue, rightValue = key.split('+')\n",
    "            if leftValue not in eventList:\n",
    "                eventList.append(leftValue)\n",
    "            if rightValue not in eventList:\n",
    "                eventList.append(rightValue)\n",
    "\n",
    "        sankeyLabel = [s[:-1] for s in eventList]\n",
    "        \n",
    "        sankeySource = []\n",
    "        sankeyTarget = []\n",
    "        sankeyValue = []\n",
    "\n",
    "        for key,val in dataDict.items():\n",
    "            sankeySource.append(eventList.index(key.split('+')[0]))\n",
    "            sankeyTarget.append(eventList.index(key.split('+')[1]))\n",
    "            sankeyValue.append(val)\n",
    "\n",
    "        sankeyColor = []\n",
    "        for i in sankeyLabel:\n",
    "            sankeyColor.append('#'+''.join([random.choice('0123456789ABCDEF') for _ in range(6)]))\n",
    "\n",
    "        link = dict(source = sankeySource, target = sankeyTarget, value = sankeyValue, color='light grey')\n",
    "        node=dict(label=sankeyLabel, color=sankeyColor)\n",
    "        data=go.Sankey(link=link, node=node)\n",
    "\n",
    "        fig=go.Figure(data)\n",
    "\n",
    "        fig.update_layout(\n",
    "            hovermode ='closest',\n",
    "            title = title_text,\n",
    "            title_font_size=20,\n",
    "            plot_bgcolor='white',\n",
    "            paper_bgcolor='white'\n",
    "        )\n",
    "\n",
    "        fig.show()\n",
    "\n",
    "    elif direction == \"to\":\n",
    "        \n",
    "        dataDict = defaultdict(int)\n",
    "        eventDict = defaultdict(int)\n",
    "        maxPath = npath_pandas['count_event_cnt'].max()\n",
    "    \n",
    "        for index, row in npath_pandas.iterrows():\n",
    "            rowList = row['path'].replace('[','').replace(']','').split(',')\n",
    "            pathCnt = row['count_event_cnt']\n",
    "            pathLen = len(rowList)\n",
    "            for i in range(len(rowList)-1):\n",
    "                leftValue = str(1000 + i + maxPath - pathLen) + rowList[i].strip()\n",
    "                rightValue = str(1000 + i + 1 + maxPath - pathLen) + rowList[i+1].strip()\n",
    "                valuePair = leftValue + '+' + rightValue\n",
    "                dataDict[valuePair] += pathCnt\n",
    "                eventDict[leftValue] += 1\n",
    "                eventDict[rightValue] += 1\n",
    "    \n",
    "        eventList = []\n",
    "        for key,val in eventDict.items():\n",
    "            eventList.append(key)\n",
    "    \n",
    "        sortedEventList = sorted(eventList)\n",
    "        sankeyLabel = []\n",
    "        for event in sortedEventList:\n",
    "            sankeyLabel.append(event[4:])\n",
    "    \n",
    "        sankeySource = []\n",
    "        sankeyTarget = []\n",
    "        sankeyValue = []\n",
    "\n",
    "        for key,val in dataDict.items():\n",
    "            sankeySource.append(sortedEventList.index(key.split('+')[0]))\n",
    "            sankeyTarget.append(sortedEventList.index(key.split('+')[1]))\n",
    "            sankeyValue.append(val)\n",
    "    \n",
    "        sankeyColor = []\n",
    "        for i in sankeyLabel:\n",
    "            sankeyColor.append('#'+''.join([random.choice('0123456789ABCDEF') for _ in range(10)]))\n",
    "    \n",
    "        link = dict(source = sankeySource, target = sankeyTarget, value = sankeyValue, color='light grey')\n",
    "        data=go.Sankey(link=link, node=dict(label=sankeyLabel))\n",
    "    \n",
    "        fig=go.Figure(data)\n",
    "        fig.update_layout(\n",
    "                hovermode ='closest',\n",
    "                title = title_text,\n",
    "                title_font_size=20,\n",
    "                plot_bgcolor='white',\n",
    "                paper_bgcolor='white'\n",
    "                )\n",
    "    \n",
    "        fig.show()\n",
    "\n",
    "    else:\n",
    "        print(\"Invalid direction.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d9ef449c-df36-4f86-8de9-584a4e78d6a6",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We will consider an example where we create a path for a cookie which leads to conversion.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7bbcbb7e-7ed0-4478-bdbd-01bc0877604c",
   "metadata": {},
   "outputs": [],
   "source": [
    "attr_df[attr_df['cookie'] == 'FFfBikCE3onF3hACFCCE9iDf3'].sort('tmstp')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "acb7bf99-8009-4a7b-a522-ad74f752258b",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The above table shows the output of 1 cookie ordered by Timestamp(tmstp). We can see that there were 3 touch points of the Facebook channel when conversion did not happen. Finally on the 4th touch point of the Facebook channel, conversion takes place. So, the path will be </p>\n",
    "<p style = 'font-size:14px;font-family:Arial;color:#00233C'><b>Facebook</b><b style = 'font-size:12px;font-family:Arial;color:#00233C'>(2018-07-02 16:08:02)--></b><b style = 'font-size:14px;font-family:Arial;color:#00233C'>Facebook</b><b style = 'font-size:12px;font-family:Arial;color:#00233C'>(2018-07-08 18:38:32)--></b><b style = 'font-size:14px;font-family:Arial;color:#00233C'>Facebook</b><b style = 'font-size:12px;font-family:Arial;color:#00233C'>(2018-07-10 12:30:15)--></b><b style = 'font-size:14px;font-family:Arial;color:#00233C'>Facebook</b><b style = 'font-size:12px;font-family:Arial;color:#00233C'>(2018-07-14 10:33:31)--></b><b style = 'font-size:14px;font-family:Arial;color:#00233C'>Conversion</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Below we plot the paths for Top 100 path that led to conversion based on the count of events.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><i>**The visualization takes around 1 minute 30 seconds to execute</i></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f1d9efd8-577e-48fc-90bc-2755ec08a303",
   "metadata": {},
   "outputs": [],
   "source": [
    "res = convcntpath\\\n",
    "                    .groupby(['path'])\\\n",
    "                    .count()\\\n",
    "                    .sort('count_event_cnt',ascending=False)\\\n",
    "                    .to_pandas()\\\n",
    "                    .head(100)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7999d508-7222-4c20-8d7d-f2e1d1094a19",
   "metadata": {},
   "outputs": [],
   "source": [
    "sankeyPlot(res, \"to\", \"Path to Conversion\", 100)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b42d1876-0d2e-4030-b64f-b51451938aad",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The above Sankey Diagram shows the paths that led to Conversion.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We can check the details of any path or node when we move the mouse pointer over it and check details. For example, if we move the pointer over the path having the largest width at the topmost path going towards the right most node(Conversion) it shows <b>2.30k, source: Facebook, target: Conversion.</b> It means there were 2.30k touch points where after going to Facebook the next event was Conversion. Similarly, 1.92k Online Video touch points, 1.98k Paid Search touch points, 873 Instagram touch points, 816 Online Display touch points which lead to Conversion. </p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>When we move the pointer over a Node, for example when we moved the pointer on the largest Node at the top before conversion is <b>Facebook </b>  it shows <b>incoming flow count: 5 and outgoing flow count: 1</b> which means that there are 5 different paths which lead to Facebook after which the next 1 event led to Conversion. Similarly, other nodes and paths can be analyzed.</p>\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "76f366c9-c1d9-416e-ae26-24e14fc9fe5c",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>4.2 Use nPath as a data preparation function and input to additional analytics techniques</b></p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ca45f6b9-aa46-45cd-8b9c-a24160433492",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In this step we are using nPath function to create input tables to be used by statistical and machine learning based approaches. We have used these tables in analysis below for example in TERM FREQUENCY - INVERSE DOCUMENT FREQUENCY (TF-IDF) analysis where we score these converting and non-converting journeys.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c5057c86-ffb6-413b-b354-0e31ec585e2a",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b> Create a table with all converting journeys</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We are creating a table with all kinds of paths that lead to Conversion.  To achieve this, we look at any sequence of events ending with a conversion.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b8ca9dd7-f87f-4adb-b041-b1d7074206f9",
   "metadata": {},
   "outputs": [],
   "source": [
    "npath_ConvJour = NPath(data1 = attr_df, \n",
    "                      data1_partition_column = ['cookie'], \n",
    "                      data1_order_column = ['tmstp'], \n",
    "                      mode = 'NONOVERLAPPING', \n",
    "                      symbols = ['conversion=\\'1\\' as C, conversion=\\'0\\' as E'], \n",
    "                      pattern = 'E*.C', \n",
    "                      result = ['ACCUMULATE (channel OF ANY(C,E)) AS path'\n",
    "                                ,'COUNT (* of ANY(C,E)) as event_cnt'\n",
    "                                ,'FIRST (cookie OF ANY(C,E)) AS cookie'])\n",
    "\n",
    "\n",
    "npath_ConvJour_df = npath_ConvJour.result\n",
    "npath_ConvJour_df = npath_ConvJour_df[npath_ConvJour_df['event_cnt']>1]\n",
    "npath_ConvJour_df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0444c48e-3e5d-436f-a62d-9e4488169c1a",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Create a table with all non-converting journeys (leaving out potential converting journeys)</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We are creating a table with all kinds of paths that do not lead to any Conversion. To achieve this, we look for all paths where cookies are not part of any converting journey (just previously defined) and leaving out any potential converting journey.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "debb6636-653b-4383-8d72-b888d7565e4c",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "dist = npath_ConvJour_df.get('cookie')\n",
    "dist_val = dist.get_values()\n",
    "list_val = [dist_val[i][0] for i in range(len(dist_val))]\n",
    "list_val = list(set(list_val))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "30203cd1-44cf-4a16-881b-97220fe02abf",
   "metadata": {},
   "outputs": [],
   "source": [
    "max_tmstp = attr_df[attr_df['conversion'] == '1'].select('tmstp').max()\n",
    "Journey_data = attr_df.merge(right = max_tmstp, how = \"inner\", on = [\"tmstp < max_tmstp\"])\n",
    "Journey_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "272250b4-a04d-4d4d-8ef7-5edc9184b305",
   "metadata": {},
   "outputs": [],
   "source": [
    "npath_NConvJour = NPath(data1 = Journey_data, \n",
    "                      data1_partition_column = ['cookie'], \n",
    "                      data1_order_column = ['tmstp'], \n",
    "                      mode = 'NONOVERLAPPING', \n",
    "                      symbols = ['TRUE as A'], \n",
    "                      pattern = 'A*', \n",
    "                      result = ['ACCUMULATE (channel of ANY(A)) as path'\n",
    "                                ,'ACCUMULATE (conversion of ANY(A)) as conv'\n",
    "                                ,'COUNT (* of ANY(A)) as event_cnt'\n",
    "                                ,'FIRST (cookie OF ANY(A)) AS cookie'])\n",
    "\n",
    "\n",
    "npath_NConvJour_df = npath_NConvJour.result\n",
    "npath_NConvJour_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "aa9118e8-fbf8-49c5-8b9d-e43eaa6ca8bc",
   "metadata": {},
   "outputs": [],
   "source": [
    "npath_NConvJour_df = npath_NConvJour_df[npath_NConvJour_df['event_cnt']>1]\n",
    "npath_NConvJour_df = npath_NConvJour_df[npath_NConvJour_df['conv'].str.contains('1') == False]\n",
    "npath_NConvJour_df = npath_NConvJour_df[~npath_NConvJour_df.cookie.isin(list_val)]\n",
    "npath_NConvJour_df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0de96765-c606-44de-8fbd-de50ab75768d",
   "metadata": {},
   "source": [
    "<a id=\"rule\"></a>\n",
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>5. RULE BASED MODELS</b></p>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2230b49b-f0b2-40c7-8ffd-f88d9fe50c4a",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Rule Based attribution models assign conversion credits (weights) to touchpoints in a conversion path according to certain predefined rules.\n",
    "</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>These rules are used to identify the position of an interaction on the conversion path and then assign conversion credit solely on the basis of its position.\n",
    "</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>To execute rule based models we can leverage the Vantage native Attribution function and easily consider the following methods:\n",
    "<ul style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Uniform: Conversion event is attributed uniformly to preceding attributable events.</li>\n",
    "    <li>First Click: Conversion event is attributed entirely to first attributable event.</li>\n",
    "    <li>Last Click: Conversion event is attributed entirely to most recent attributable event</li> \n",
    "    <li>Exponential:  Conversion event is attributed exponentially to preceding attributable events (the more recent the event, the higher the attribution).</li>\n",
    " </ul>\n",
    "</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The function takes data and parameters from multiple tables and outputs attributions. Please refer to Teradata Vantage™ - Analytics Database Analytic Functions documentation for more on Attribution function.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Attribution Input :\n",
    "<ol style = 'font-size:14px;font-family:Arial;color:#00233C'>\n",
    "<li style = 'font-size:14px;font-family:Arial;color:#00233C'>Input tables (maximum of five) (Contain data for computing attributions).</li>\n",
    "<li style = 'font-size:14px;font-family:Arial;color:#00233C'>ConversionEventTable (Contains conversion events).</li>\n",
    "<li style = 'font-size:14px;font-family:Arial;color:#00233C'>FirstModelTable (Defines type and distributions of model - we'll create one table per model)</li></ol>\n",
    "</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Attribution Syntax Elements:\n",
    "<ol style = 'font-size:14px;font-family:Arial;color:#00233C'>\n",
    "<li style = 'font-size:14px;font-family:Arial;color:#00233C'>EventColumn specifies the name of the input column that contains the events.</li>\n",
    "<li style = 'font-size:14px;font-family:Arial;color:#00233C'>TimeColumn specifies the name of the input column that contains the timestamps of the  events.</li>\n",
    "<li style = 'font-size:14px;font-family:Arial;color:#00233C'>WindowSize specifies how to determine the maximum window size for the attribution calculation</li></ol>\n",
    "    </p>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9a3ccf89-fe9a-45f0-aea6-805438da4674",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>5.1. Create Conversion Event Table.</b></p> \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'> Since we are focusing on the events that led to Conversion our ATTRIBUTION CONVERSION Table will have only one value <b>'conversion'</b>.</p>     \n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2da3aa4c-a45e-4da5-8e1a-1e22da9f40ae",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Drop the table if it already exists\n",
    "qry = 'DROP TABLE ATTRIBUTION_CONVERSION;'\n",
    "try:\n",
    "    execute_sql(qry)\n",
    "except Exception as e:\n",
    "    if str(e.args).find('3807') >= 1:\n",
    "        pass\n",
    "    else:\n",
    "        raise\n",
    "\n",
    "# Create the table\n",
    "qry = '''\n",
    "CREATE MULTISET TABLE ATTRIBUTION_CONVERSION\n",
    "(\n",
    "    CONVERSION VARCHAR(100)\n",
    ");\n",
    "'''\n",
    "\n",
    "# Execute the query\n",
    "execute_sql(qry)\n",
    "\n",
    "#Insert model specification values (line1)\n",
    "qry = '''\n",
    "INSERT INTO ATTRIBUTION_CONVERSION VALUES ('conversion');;\n",
    "'''\n",
    "\n",
    "# Execute the query\n",
    "execute_sql(qry)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "40403c92-8653-41d6-ba20-bc53f83d9fbf",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>5.2 Create model specifications tables</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We will need to create 1 model table for each type of Attribution: First Click , Last Click, Uniform and Exponential Attribution hence we are creating 4 different model tables below and creating data for each of these model types.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e81fba0d-7c17-4e27-9b18-5d8044776eab",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Uniform Model (applies equal weighting to all contributing touchpoints in the customer journey)</b></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5660803a-17de-4edb-862d-8050c8e3c652",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Drop the table if it already exists\n",
    "qry = 'DROP TABLE ATTRIBUTION_MODEL_UNIFORM;'\n",
    "try:\n",
    "    execute_sql(qry)\n",
    "except Exception as e:\n",
    "    if str(e.args).find('3807') >= 1:\n",
    "        pass\n",
    "    else:\n",
    "        raise\n",
    "\n",
    "# Create the table\n",
    "qry = '''\n",
    "CREATE MULTISET TABLE ATTRIBUTION_MODEL_UNIFORM\n",
    "(\n",
    "    ID   INT,\n",
    "    MODEL VARCHAR(100)\n",
    ");\n",
    "'''\n",
    "\n",
    "# Execute the query\n",
    "execute_sql(qry)\n",
    "\n",
    "#Insert model specification values (line1)\n",
    "qry = '''\n",
    "INSERT INTO ATTRIBUTION_MODEL_UNIFORM VALUES (0,'EVENT_REGULAR');\n",
    "'''\n",
    "\n",
    "# Execute the query\n",
    "execute_sql(qry)\n",
    "\n",
    "#Insert model specification values (line2)\n",
    "qry = '''\n",
    "INSERT INTO ATTRIBUTION_MODEL_UNIFORM VALUES (1,'ALL:1.0:UNIFORM:NA');\n",
    "'''\n",
    "\n",
    "# Execute the query\n",
    "execute_sql(qry)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "21d2b3ab-bf8c-418d-abc4-b50ccf544865",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b> First Click Model (100% of the credit is directly attributed to the first interaction in the customer journey)</b></p>\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0bbffd38-c7cd-410a-b66a-9d4b8984b36e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Drop the table if it already exists\n",
    "qry = 'DROP TABLE ATTRIBUTION_MODEL_FIRSTCLICK;'\n",
    "try:\n",
    "    execute_sql(qry)\n",
    "except Exception as e:\n",
    "    if str(e.args).find('3807') >= 1:\n",
    "        pass\n",
    "    else:\n",
    "        raise\n",
    "\n",
    "# Create the table\n",
    "qry = '''\n",
    "CREATE MULTISET TABLE ATTRIBUTION_MODEL_FIRSTCLICK\n",
    "(\n",
    "    ID   INT,\n",
    "    MODEL VARCHAR(100)\n",
    ");\n",
    "'''\n",
    "\n",
    "# Execute the query\n",
    "execute_sql(qry)\n",
    "\n",
    "#Insert model specification values (line1)\n",
    "qry = '''\n",
    "INSERT INTO ATTRIBUTION_MODEL_FIRSTCLICK VALUES (0,'EVENT_REGULAR');\n",
    "'''\n",
    "\n",
    "# Execute the query\n",
    "execute_sql(qry)\n",
    "\n",
    "#Insert model specification values (line2)\n",
    "qry = '''\n",
    "INSERT INTO ATTRIBUTION_MODEL_FIRSTCLICK VALUES (1,'ALL:1.0:FIRST_CLICK:NA');\n",
    "'''\n",
    "\n",
    "# Execute the query\n",
    "execute_sql(qry)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6cfa120a-67a2-401f-b081-d08a1a901195",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b> Last Click Model (100% of the credit is directly attributed to the last interaction in the customer journey)</b></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2545da9f-cfc1-4c9f-8739-a44c06f9e0de",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Drop the table if it already exists\n",
    "qry = 'DROP TABLE ATTRIBUTION_MODEL_LASTCLICK;'\n",
    "try:\n",
    "    execute_sql(qry)\n",
    "except Exception as e:\n",
    "    if str(e.args).find('3807') >= 1:\n",
    "        pass\n",
    "    else:\n",
    "        raise\n",
    "\n",
    "# Create the table\n",
    "qry = '''\n",
    "CREATE MULTISET TABLE ATTRIBUTION_MODEL_LASTCLICK\n",
    "(\n",
    "    ID   INT,\n",
    "    MODEL VARCHAR(100)\n",
    ");\n",
    "'''\n",
    "\n",
    "# Execute the query\n",
    "execute_sql(qry)\n",
    "\n",
    "#Insert model specification values (line1)\n",
    "qry = '''\n",
    "INSERT INTO ATTRIBUTION_MODEL_LASTCLICK VALUES (0,'EVENT_REGULAR');\n",
    "'''\n",
    "\n",
    "# Execute the query\n",
    "execute_sql(qry)\n",
    "\n",
    "#Insert model specification values (line2)\n",
    "qry = '''\n",
    "INSERT INTO ATTRIBUTION_MODEL_LASTCLICK VALUES (1,'ALL:1.0:LAST_CLICK:NA');\n",
    "'''\n",
    "\n",
    "# Execute the query\n",
    "execute_sql(qry)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "20831381-807c-499f-a2ff-68efbda43c3e",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b> Exponential Model (assigns exponentially more weight to the interactions which are closest in time to conversion)</b></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7b8df882-1864-4716-8869-7d38c7539cb7",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Drop the table if it already exists\n",
    "qry = 'DROP TABLE ATTRIBUTION_MODEL_EXPONENTIAL;'\n",
    "try:\n",
    "    execute_sql(qry)\n",
    "except Exception as e:\n",
    "    if str(e.args).find('3807') >= 1:\n",
    "        pass\n",
    "    else:\n",
    "        raise\n",
    "\n",
    "# Create the table\n",
    "qry = '''\n",
    "CREATE MULTISET TABLE ATTRIBUTION_MODEL_EXPONENTIAL\n",
    "(\n",
    "    ID   INT,\n",
    "    MODEL VARCHAR(100)\n",
    ");\n",
    "'''\n",
    "\n",
    "# Execute the query\n",
    "execute_sql(qry)\n",
    "\n",
    "#Insert model specification values (line1)\n",
    "qry = '''\n",
    "INSERT INTO ATTRIBUTION_MODEL_EXPONENTIAL VALUES (0,'EVENT_REGULAR');\n",
    "'''\n",
    "\n",
    "# Execute the query\n",
    "execute_sql(qry)\n",
    "\n",
    "#Insert model specification values (line2)\n",
    "qry = '''\n",
    "INSERT INTO ATTRIBUTION_MODEL_EXPONENTIAL VALUES (1,'ALL:1.0:EXPONENTIAL:0.5,ROW');\n",
    "'''\n",
    "\n",
    "# Execute the query\n",
    "execute_sql(qry)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1cbaf6c8-caf0-43ff-92a8-95d6a1f95af1",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>5.3. Compute all four models and store outputs in a table</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>After creating the four model tables we will use them in the calculation of ATTRIBUTION for each channel based on all these models as in the query below.</p> \n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In order to consider 20 rows from most to least recent preceding conversion to compute all Rule-based models we use the WindowSize argument of the Attribution function. More specifically we use the \"rows:K\" option which assigns attributions to at most K events before conversion event. In our case K=20.</p>\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7247527b-38e8-4730-a921-dbdfed263e4f",
   "metadata": {},
   "outputs": [],
   "source": [
    "attr_conversion =  DataFrame('ATTRIBUTION_CONVERSION')\n",
    "attr_uniform =  DataFrame('ATTRIBUTION_MODEL_UNIFORM')\n",
    "attr_fc =  DataFrame('ATTRIBUTION_MODEL_FIRSTCLICK')\n",
    "attr_lc =  DataFrame('ATTRIBUTION_MODEL_LASTCLICK')\n",
    "attr_exp =  DataFrame('ATTRIBUTION_MODEL_EXPONENTIAL')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "caf067e3-dc89-4c39-8db4-5a4dc2004882",
   "metadata": {},
   "outputs": [],
   "source": [
    "attribution_uniform = Attribution(data=attr_df,\n",
    "                                             data_partition_column=\"cookie\",\n",
    "                                             data_order_column=\"tmstp\",\n",
    "                                             event_column=\"interaction\",\n",
    "                                             conversion_data=attr_conversion,\n",
    "                                             timestamp_column = \"tmstp\",\n",
    "                                             window_size = \"rows:20\",\n",
    "                                             model1_type=attr_uniform)\n",
    "\n",
    "AttrUNI_df = attribution_uniform.result\n",
    "attribution_FC = Attribution(data=attr_df,\n",
    "                                             data_partition_column=\"cookie\",\n",
    "                                             data_order_column=\"tmstp\",\n",
    "                                             event_column=\"interaction\",\n",
    "                                             conversion_data=attr_conversion,\n",
    "                                             timestamp_column = \"tmstp\",\n",
    "                                             window_size = \"rows:20\",\n",
    "                                             model1_type=attr_fc)\n",
    "\n",
    "AttrFC_df = attribution_FC.result\n",
    "attribution_LC = Attribution(data=attr_df,\n",
    "                                             data_partition_column=\"cookie\",\n",
    "                                             data_order_column=\"tmstp\",\n",
    "                                             event_column=\"interaction\",\n",
    "                                             conversion_data=attr_conversion,\n",
    "                                             timestamp_column = \"tmstp\",\n",
    "                                             window_size = \"rows:20\",\n",
    "                                             model1_type=attr_lc)\n",
    "\n",
    "AttrLC_df = attribution_LC.result\n",
    "attribution_EXP = Attribution(data=attr_df,\n",
    "                                             data_partition_column=\"cookie\",\n",
    "                                             data_order_column=\"tmstp\",\n",
    "                                             event_column=\"interaction\",\n",
    "                                             conversion_data=attr_conversion,\n",
    "                                             timestamp_column = \"tmstp\",\n",
    "                                             window_size = \"rows:20\",\n",
    "                                             model1_type=attr_exp)\n",
    "\n",
    "AttrEXP_df = attribution_EXP.result\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0756f362-8cc7-45d3-9407-50b2e42e4f84",
   "metadata": {},
   "outputs": [],
   "source": [
    "attr_12_df = AttrUNI_df.merge(right = AttrFC_df, how = \"inner\" , on = [\"cookie\",\"tmstp\", \"channel\"], lsuffix = \"t1\", rsuffix = \"t2\")\n",
    "attr_34_df = AttrLC_df.merge(right = AttrEXP_df, how = \"inner\" , on = [\"cookie\",\"tmstp\", \"channel\"], lsuffix = \"t3\", rsuffix = \"t4\")\n",
    "attr_all_df = attr_12_df.merge(right = attr_34_df, how = \"inner\" , on = [\"t1_cookie = t3_cookie\",\"t1_tmstp = t3_tmstp\"\n",
    "                                                                            , \"t1_channel = t3_channel\"], lsuffix = \"t5\", rsuffix = \"t6\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "01063e6d-0fb0-4f1a-9993-49eca702a358",
   "metadata": {},
   "outputs": [],
   "source": [
    "attr_4model_df = attr_all_df.select(['t1_COOKIE','t1_TMSTP','t1_CHANNEL', \n",
    "                                     't1_attribution','t2_attribution','t3_attribution','t4_attribution',\n",
    "                                     't1_time_to_conversion', 't2_time_to_conversion', 't3_time_to_conversion',\n",
    "                                     't4_time_to_conversion'])\n",
    "\n",
    "attr_4model_df = attr_4model_df.assign(drop_columns = True, \n",
    "                                       cookie = attr_4model_df.t1_COOKIE,\n",
    "                                       tmstp = attr_4model_df.t1_TMSTP,\n",
    "                                       channel = attr_4model_df.t1_CHANNEL,\n",
    "                                       uni_attr = attr_4model_df.t1_attribution,\n",
    "                                       uni_ttc = attr_4model_df.t1_time_to_conversion,\n",
    "                                       fc_attr = attr_4model_df.t2_attribution,\n",
    "                                       fc_ttc = attr_4model_df.t2_time_to_conversion,\n",
    "                                       lc_attr = attr_4model_df.t3_attribution,\n",
    "                                       lc_ttc = attr_4model_df.t3_time_to_conversion,\n",
    "                                       exp_attr = attr_4model_df.t4_attribution,\n",
    "                                       exp_ttc = attr_4model_df.t4_time_to_conversion)\n",
    "attr_4model_df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f1b048b4-c338-42bb-a0b9-c0a70341cd11",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>5.4. Calculate attribution weights by channel and rule based model</b></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1b399cb5-a78e-49c9-9eb4-aca834b46ed1",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "attr_tot = attr_4model_df.select(['exp_attr','fc_attr','lc_attr','uni_attr']).sum()\n",
    "attr_channel_tot = attr_4model_df.select(['channel','exp_attr','fc_attr','lc_attr','uni_attr']).groupby('channel').sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2cff1ddc-b4a0-43c8-8389-319ea54dd55c",
   "metadata": {},
   "outputs": [],
   "source": [
    "attr_channel_tot = attr_channel_tot.merge(right=attr_tot, how=\"inner\", \n",
    "                                          on=[\"sum_uni_attr < sum_uni_attr\"]\n",
    "                                         , lsuffix = \"t1\",rsuffix=\"t2\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f6037151-e34b-441d-b3c6-9d1097300893",
   "metadata": {},
   "outputs": [],
   "source": [
    "attr_channel_total = attr_channel_tot.assign(drop_columns = True ,\n",
    "                                           channel = attr_channel_tot.channel,\n",
    "                                           tot_uni_attr = attr_channel_tot.t1_sum_uni_attr / attr_channel_tot.t2_sum_uni_attr,\n",
    "                                           tot_fc_attr = attr_channel_tot.t1_sum_fc_attr / attr_channel_tot.t2_sum_fc_attr,\n",
    "                                           tot_lc_attr = attr_channel_tot.t1_sum_lc_attr / attr_channel_tot.t2_sum_lc_attr,\n",
    "                                           tot_exp_attr = attr_channel_tot.t1_sum_exp_attr / attr_channel_tot.t2_sum_exp_attr)\n",
    "attr_channel_total"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "093d6ed0-e560-4ea2-b32e-737b8ceb43d9",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The above output shows the Attribution values for each type of channel using different models.</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We can see that the aggregated data is available to us in teradataml dataframe. Let's visualize this data to better understand the Attribution values by the types of Channels. ClearScape Analytics can easily integrate with 3rd party visualization tools like Tableau, PowerBI or many python modules available like plotly, seaborn etc. We can do all the calculations and pre-processing on Vantage and pass only the necessary information to visualization tools, this will not only make the calculation faster but also reduce the time due to less data movement between tools.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "57bba648-7e04-4304-a5d6-0af8b1c45391",
   "metadata": {},
   "outputs": [],
   "source": [
    "attr_channel_plot = attr_channel_total.to_pandas()\n",
    "import plotly.graph_objects as go\n",
    "\n",
    "fig = go.Figure(\n",
    "    data = [\n",
    "        go.Bar(name='Uniform', x=attr_channel_plot[\"channel\"], y=attr_channel_plot[\"tot_uni_attr\"], yaxis='y', offsetgroup=1,marker_color='#76B7B2'),\n",
    "        go.Bar(name='First Click', x=attr_channel_plot[\"channel\"], y=attr_channel_plot[\"tot_fc_attr\"], yaxis='y', offsetgroup=2, marker_color='#F28E2B'),\n",
    "        go.Bar(name='Last Click', x=attr_channel_plot[\"channel\"], y=attr_channel_plot[\"tot_lc_attr\"], yaxis='y', offsetgroup=3,marker_color='#E15759'),\n",
    "        go.Bar(name='Exponential', x=attr_channel_plot[\"channel\"], y=attr_channel_plot[\"tot_exp_attr\"], yaxis='y', offsetgroup=4,marker_color='#4E79A7')\n",
    "    ],\n",
    "    layout = {\n",
    "        'yaxis': {'title': 'Attribution '},\n",
    "\n",
    "    }\n",
    ")\n",
    " \n",
    "# Change the bar mode\n",
    "fig.update_layout(barmode = 'group')\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "81af4231-b658-4914-859c-da2c07d6afba",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The above output shows the Attribution values for each type of channel using different models.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6fd5a96f-1159-4b36-8c30-b30453c22c7c",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>From the above graph we can see that the Attribution Value for Facebook channel is highest in all the 4 models and that for Online Display is the lowest.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "699314f5-4317-4f21-84f5-c539fb87132d",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>5.5. Exploring Uniform Model in more details</b></p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "72843900-9c0c-4963-84d7-19dab0fad4ff",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Whatever the model the attribution function will output a score (or attribution weight) and compute the time to conversion.</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We can easily put this information in perspective with the cost to measure and visualize channel effectiveness.</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The uniform model can serve as a starting point or baseline for attribution analysis. It provides a benchmark against which more advanced attribution models can be compared. By evaluating the performance of other models relative to the uniform model, marketers can gain insights into the additional value or improvement offered by more sophisticated approaches like the Statistics based models or Machine learning models. We have used some of these models below in this notebook.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6fbafc63-4fee-425a-a7d4-70117bbb3011",
   "metadata": {},
   "outputs": [],
   "source": [
    "attr_uni = attr_4model_df.assign(uni_ttc_rev = attr_4model_df.uni_ttc * -1)\n",
    "channel_attr_cost = attr_uni.merge(right=channel_df, how=\"inner\", on = [\"channel\"],lsuffix=\"t1\", rsuffix = \"t2\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1d4ba9b8-45b1-462d-9ec6-db7ba1774857",
   "metadata": {},
   "outputs": [],
   "source": [
    "channel_attr_cost = channel_attr_cost.select(['t1_channel','uni_attr','uni_ttc_rev','cost']).groupby('t1_channel').agg({'uni_attr' : ['sum'], 'uni_ttc_rev' : ['mean'],'cost' : ['sum']})\n",
    "channel_attr_cost = channel_attr_cost.assign(drop_columns=True,\n",
    "                                             channel = channel_attr_cost.t1_channel,\n",
    "                                             total_attribution = channel_attr_cost.sum_uni_attr,\n",
    "                                             total_cost = channel_attr_cost.sum_cost,\n",
    "                                             time_to_conversion = channel_attr_cost.mean_uni_ttc_rev/86400)\n",
    "channel_attr_cost"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "de5ddd2e-adf2-447a-9ac9-2b32cd7bbea5",
   "metadata": {
    "tags": []
   },
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The total attribution , cost and time to conversion are used from the output of the Attribution function used above. Here we are considering only the attribution scores from the UNIFORM attribution model(sum(uniform_attribution)).</p> \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>All three dimensions - cost, attribution and time to conversion - can be plotted on a bubble chart, the size of the bubbles showing the cost. </p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "56dd7bd0-3126-4a62-8b0b-62aa03209976",
   "metadata": {},
   "outputs": [],
   "source": [
    "AttribUni_plot = channel_attr_cost.to_pandas()\n",
    "import plotly.express as px\n",
    "ax = px.scatter(AttribUni_plot, x=\"total_attribution\", y=\"time_to_conversion\",\n",
    "              size=\"total_cost\",size_max = 70,color=\"channel\",hover_data=['channel'],\n",
    "              width=900, height=400, \n",
    "              color_discrete_map = {'Online Display': '#E15759','Online Video': '#76B7B2','Facebook': '#4E79A7','Instagram': '#F28E2B' ,'Paid Search': '#59A14F'},\n",
    "             labels={\n",
    "                     \"total_attribution\": \"Total  Attribution\",\n",
    "                     \"time_to_conversion\": \"Time to Conversion (Days)\"\n",
    "        }\n",
    "             )\n",
    "ax.update_layout(showlegend=False)\n",
    "ax.update_layout(title_text='Channel Performance - Uniform Model', title_x=0.5)\n",
    "ax.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6e4ebf95-e5a5-4056-91f8-4b24a6fdcfb6",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The above graph shows the Channel Performance using the UNIFORM Model. The size of the circle depends on the total cost of the channel. When we move the mouse over the circles we can see channel, it's attribution value, time to conversion and also the cost, in the text. The largest circle is for Online Video followed by Facebook, which indicates that the Online Video channel is less performant than Facebook (higher cost, lower attribution).</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "876d47b9-ef24-4540-a31a-e5a2a5045b9e",
   "metadata": {},
   "source": [
    "<a id=\"stat\"></a>\n",
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>6. STATISTICAL BASED MODELS</b></p>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "51ebb9b8-6f19-4e33-a20a-684324da58b7",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>6.1 SIMPLE FREQUENCY ANALYSIS</b></p>\n",
    "\n",
    "\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>A simple frequency analysis (obtained by calculating the occurrences of the channel in the journeys leading to Conversion) can be used as a basic approach to compute marketing attribution.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>NGramSplitter considers each input row to be one document and returns a row for each unique n-gram in each document. NGramSplitter also returns, for each document, the counts of each n-gram and the total number of n-grams.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>NGramSplitter is an algorithm used in natural language processing to divide text into smaller units known as n-grams. An n-gram is a sequence of n items, such as words, letters or characters, taken from a given sample of text or speech. The NGramSplitter algorithm takes a string of text as input and returns a list of n-grams based on a specified value of n..</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We just need to tokenize paths in converting journeys and calculate the frequency. </p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Here for path tokenization, we use NGramSplitter function which splits the input stream of text (here paths) into \"terms\" (channel) of selected size (1:- which means each event) and count them.</p>\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "83d99041-f27f-460b-a976-8e66f66f9340",
   "metadata": {},
   "outputs": [],
   "source": [
    "ngram_df = npath_ConvJour_df[npath_ConvJour_df['event_cnt']<=20]\n",
    "tdf_grams = NGramSplitter(\n",
    "               data             = ngram_df\n",
    "              ,text_column      = 'path'\n",
    "              #,accumulate       = 'comment_id'\n",
    "              ,grams            = \"1\"\n",
    "              ,overlapping      = True\n",
    "              ,to_lower_case    = False\n",
    "              ,delimiter        = \",\"\n",
    "              #,punctuation      = '[`~#^&*()-]'\n",
    "              ,reset = \"[]\"\n",
    "              ,total_gram_count = False\n",
    "            ).result\n",
    "tdf_grams"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ff8b9609-eb7c-4e8f-bf7a-7beab7d30231",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Thus, in the output we can see the ngrams which are various channels here and the frequency of these channels in the paths </p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "992d30f1-074c-4892-85fc-d3e32ca96130",
   "metadata": {},
   "outputs": [],
   "source": [
    "freq_df = tdf_grams.select(['ngram','frequency']).groupby('ngram').sum()\n",
    "tot_freq = freq_df.select(['sum_frequency']).sum()\n",
    "freq_tot_df = freq_df.merge(right=tot_freq,how=\"cross\" ,on = [\"sum_frequency < sum_sum_frequency\"])\n",
    "ngram_freq_df = freq_tot_df.assign(drop_columns=True,\n",
    "                               channel = freq_tot_df.ngram,\n",
    "                               frequency = freq_tot_df.sum_frequency,\n",
    "                               # tot = tot_freq,\n",
    "                               tp = (1.000 * freq_tot_df.sum_frequency/freq_tot_df.sum_sum_frequency))\n",
    "ngram_freq_df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "38c3ae99-2f86-4e53-9f6a-9e5f5b278568",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;'> The output of the NGramSplitter contains ngram, the frequency of the channel, the Total frequency(to) and the percentage of the channel frequency to total frequency(tp). </p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "991d1db6-3449-4fd5-be09-cd9610ddbf6d",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Visualizing the results in a vertical bar chart.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1b69976e-f7f2-4d8c-87bb-9ba4210973d1",
   "metadata": {},
   "outputs": [],
   "source": [
    "import plotly.express as px\n",
    "freq = ngram_freq_df.to_pandas().reset_index()\n",
    "fig = px.bar(freq, y=\"tp\", x=\"channel\", \n",
    "             color='channel', orientation='v',\n",
    "             height=600,width=900,\n",
    "             color_discrete_map = {'Online Display': '#E15759','Online Video': '#76B7B2','Facebook': '#4E79A7','Instagram': '#F28E2B' ,'Paid Search': '#59A14F'},\n",
    "             title='Attribution Summary')\n",
    "fig.update_layout(title_text='Frequency Based Attribution Summary', title_x=0.5)\n",
    "fig.update_xaxes(title='Channel',tickangle=-45)\n",
    "fig.update_yaxes(title='Attribution Weight')\n",
    "fig.update_traces(width=0.5)\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1371852b-f06a-46ba-86ba-a4ffd87085e8",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The above graph shows the Frequency based Attribution value for each channel using the ngrams. We can see that the Attribution Value for Facebook channel is highest and that for Online Display is lowest.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "60c552ba-ed1f-42d3-be17-147d65f3e1d3",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>7. ASSOCIATION ANALYSIS (looking for association of channels driving conversion)</b></p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "972dc55d-ec67-4019-ba94-664643f3a8bd",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Association analysis can help identify channels that are frequently used in combination within converting journeys.  This information can guide resource allocation and enable marketers to focus on the most effective channel combinations to lift conversion.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "de2c7af3-8dda-404b-833a-a7a7bfbf3389",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>7.1. Prepare data</b></p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "38077989-e8e3-48ba-a75e-2f67d23ebdc0",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Association analysis can help identify channels that are frequently used in combination with other successful channels. This information can guide resource allocation and enable marketers to focus on the most effective channels.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We use the nPath function to identify all cookies that are leading to a conversion and use this cookies list as a filter to the original dataset.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "764ae08d-021c-457c-a5e9-dc728f6f3dd4",
   "metadata": {},
   "outputs": [],
   "source": [
    "npathsession = NPath(data1 = attr_df, \n",
    "                      data1_partition_column = ['cookie'], \n",
    "                      data1_order_column = ['tmstp'], \n",
    "                      mode = 'NONOVERLAPPING', \n",
    "                      symbols = ['conversion=\\'1\\' as C, conversion=\\'0\\' as E'], \n",
    "                      pattern = 'E*.C', \n",
    "                      result = ['ACCUMULATE (case when conversion=\\'1\\' then \\'converted\\' else channel end OF ANY(C,E)) AS path',\n",
    "                                  'COUNT (* of ANY(C,E)) as event_cnt',\n",
    "                                  'FIRST (cookie OF ANY(C,E)) AS cookie'])\n",
    "\n",
    "\n",
    "convdf = npathsession.result\n",
    "convdf = convdf[convdf.event_cnt > 1]\n",
    "convdf"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f899fafa-2ae0-4283-831b-00ecb48c73c7",
   "metadata": {},
   "outputs": [],
   "source": [
    "asso2_df = attr_df.merge(right=convdf, how=\"inner\", on = ['cookie'], lsuffix = \"t1\", rsuffix = \"t2\")\n",
    "asso2_df = asso2_df.assign(drop_columns=True\n",
    "                          ,channel = asso2_df.channel\n",
    "                          ,cookie = asso2_df.t1_cookie)\n",
    "asso2_df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3fbf0337-cc64-40dc-944c-6a20e869c86f",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>7.2. Compute Association Analysis</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We calculate the association by using the Association function from the Vantage Analytic Library(VAL). The source data will be the output of the nPath function.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9f01e240-8556-4188-b335-e948e43411ca",
   "metadata": {},
   "outputs": [],
   "source": [
    "convobj = valib.Association(data=asso2_df, group_column=\"cookie\", item_column=\"channel\")\n",
    " \n",
    "    # Print the affinity result. Only affinity result for default combination 11 is produced.\n",
    "asso_df=convobj.result_11\n",
    "asso_df "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "909f2619-c870-451d-b21c-60326dcd16e4",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The output of the association function has the above columns.</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Item1of2 and item2of2 are the channel for which the association is calculated. The measures are defined as follows:</p>\n",
    "\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "Support is percentage of groups containing the items on the left (left side support), on the right (right side support) or on both sides of a rule (rule support).</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>Confidence is percentage of groups containing the left side items that also contain the right side items.</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>Lift is a measure of how much the probability is raised that the right side items occur in a group given that the left side items occur in the group.</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>Z Score is a statistical measure of how much the expected and actual values of the number of groups containing all the items in the rule varies.  (Zero means expected and actual are the same.)</li>\n",
    "</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8d555c96-aa8b-47bb-8091-e80313d6f424",
   "metadata": {},
   "outputs": [],
   "source": [
    "import plotly.graph_objects as go\n",
    "ConvAsso = asso_df.to_pandas().reset_index()\n",
    "\n",
    "marker_text = [f\"{size}\" for size in round(ConvAsso['CONFIDENCE'],2)]\n",
    "hover_text = [f\"Lift: {value}\" for value in round(ConvAsso['LIFT'],2)]\n",
    "\n",
    "fig = go.Figure(data=go.Scatter(x=ConvAsso['ITEM1OF2'],\n",
    "                                y=ConvAsso['ITEM2OF2'],\n",
    "                                mode='markers+text',\n",
    "                               text=marker_text,  # Set the marker size text values\n",
    "    hovertext=hover_text,  # Set the hovertext values\n",
    "    hoverinfo='text',  # Only show hovertext on hover\n",
    "                                #text=hover_text, \n",
    "                                marker=dict(\n",
    "        size=ConvAsso['CONFIDENCE'],\n",
    "        sizemode='area',\n",
    "        sizeref=0.0004,\n",
    "        symbol='square',\n",
    "        color=ConvAsso['LIFT'],\n",
    "        colorscale='GnBu'\n",
    "    )))\n",
    "       # text=toto['LIFT'])) # hover text goes here\n",
    "\n",
    "fig.update_layout(title='Channel Associations in Converting Journeys', title_x=0.5)\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9a913703-54ff-4267-aba4-133dccdd0a76",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The strongest channel associations within conversion journeys are <b>Instagram</b> + <b>Facebook</b> and <b>Paid Search</b> + <b>Online Display</b>. \n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "36313ec9-84e8-4d20-a956-d7cdb7bde045",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>8. TERM FREQUENCY (Inverse Document Frequency (TF-IDF))</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>TF-IDF is a technique commonly used in natural language processing and text mining tasks to determine the importance of a term within a document or corpus.</p> \n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>TF-IDF can be defined as the calculation of how relevant a word in a series or corpus is to a text.\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The meaning increases proportionally to the number of times in the text a word appears but is compensated by the word frequency in the corpus.\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>It's commonly used for ranking word relevance and then compare text documents.\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Considering paths (sequence of events) as text we commute and compare the TF-IDF scores between the two sets of event paths (converting and non-converting). We can then examine the top-ranked terms - in our case, channels - with high TF-IDF scores in each set to identify the channels that are most distinctive or important within each set. Therefore, we can compare channel contribution across Converted and Non-Converted journeys and put calculated attribution weights in perspective.</p>\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "37187c09-6a9c-483f-a5a1-496f8727ae43",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>8.1. Prepare Data</b></p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "98c816a5-69b0-4f52-b8d0-3189bf1576d3",
   "metadata": {},
   "source": [
    " <p style = 'font-size:16px;font-family:Arial;color:#00233C'>We will tokenize paths for both converting and non-converting journeys and save output into a table. We use NGramSplitter function here for path tokenization which splits the input stream of text (here paths) into \"terms\" (grams) of selected size (1:- which means each event) and count them.\n",
    "</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7a34eb95-aa92-474f-9ad8-bfb5728b6c47",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b> Converting journeys.</b></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7ba177b9-61e0-412a-8aef-caaeb75e6dbb",
   "metadata": {},
   "outputs": [],
   "source": [
    "npath_ConvJour_df = npath_ConvJour_df[npath_ConvJour_df['event_cnt']<=20]\n",
    "conv_ngrams = NGramSplitter(\n",
    "               data             = npath_ConvJour_df\n",
    "              ,text_column      = 'path'\n",
    "              #,accumulate       = 'comment_id'\n",
    "              ,grams            = \"1\"\n",
    "              ,overlapping      = True\n",
    "              ,to_lower_case    = False\n",
    "              ,delimiter        = \",\"\n",
    "              #,punctuation      = '[`~#^&*()-]'\n",
    "              ,reset = \"[]\"\n",
    "              ,total_gram_count = False\n",
    "            ).result\n",
    "conv_ngrams"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "38acff15-ba53-49d2-b9f0-52540ded08a8",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b> Non-Converting journeys.</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Similar to the converting journeys we also use the NgramSplitter on the non-converting journeys"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6bba4b4d-5a5c-4439-b4cd-970bc0149581",
   "metadata": {},
   "outputs": [],
   "source": [
    "nconv_ngrams = NGramSplitter(\n",
    "               data             = npath_NConvJour_df\n",
    "              ,text_column      = 'path'\n",
    "              #,accumulate       = 'comment_id'\n",
    "              ,grams            = \"1\"\n",
    "              ,overlapping      = True\n",
    "              ,to_lower_case    = False\n",
    "              ,delimiter        = \",\"\n",
    "              #,punctuation      = '[`~#^&*()-]'\n",
    "              ,reset = \"[]\"\n",
    "              ,total_gram_count = False\n",
    "            ).result\n",
    "nconv_ngrams"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d381fd56-5947-4ad4-a03d-ff647bb7c371",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>8.2. Compute TF-IDF scores</b></p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7c651fec-db55-48a6-ae24-ed4fa80ac5c9",
   "metadata": {},
   "source": [
    " <p style = 'font-size:16px;font-family:Arial;color:#00233C'>We will calculate the TF-IDF scores for each term in the term-document sets (Converting and Non-Converting). TF-IDF is computed by multiplying the term frequency (TF) of a term in a document to the natural log of the inverse document frequency (IDF) across the collection of documents. The TF component measures the importance of a term within an individual event path, while the IDF component captures the rarity or distinctiveness of a term across the entire set of event paths.\n",
    "</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8fb2fffe-3acb-4898-b841-67e967b0c0d0",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b> Converting journeys.</b></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "54078ce4-3e9d-4bb4-964a-0aba84a5695e",
   "metadata": {},
   "outputs": [],
   "source": [
    "tfconv = conv_ngrams.assign(drop_columns = True \n",
    "                            ,ngram = conv_ngrams.ngram\n",
    "                            ,cookie = conv_ngrams.cookie\n",
    "                            ,tf = 1.00000 * conv_ngrams.frequency / conv_ngrams.event_cnt)\n",
    "tfconv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f333a7fe-d0b7-46cf-af62-8d3c986fd4d8",
   "metadata": {},
   "outputs": [],
   "source": [
    "cnt_cookie = conv_ngrams.select(['cookie']).count()\n",
    "ngram_cnt = conv_ngrams.select(['ngram','cookie']).groupby('ngram').count()\n",
    "idfconv = ngram_cnt.merge(right=cnt_cookie, how = \"inner\" , on = [ngram_cnt.count_cookie < cnt_cookie.count_cookie], lsuffix = \"t1\", rsuffix = \"t2\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7784b86e-8f82-4096-927b-e117055c4129",
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy import func\n",
    "idfconv = idfconv.assign(drop_columns = True\n",
    "                         , ngram = idfconv.ngram\n",
    "                         ,idf = (idfconv.t2_count_cookie/idfconv.t1_count_cookie).log10())\n",
    "idfconv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5bcd8a14-1da3-468e-9fdc-3038cf42cc92",
   "metadata": {},
   "outputs": [],
   "source": [
    "tfidfconv = idfconv.merge(right=tfconv , how = \"inner\" , on = \"ngram\" , lsuffix = \"t1\", rsuffix = \"t2\")\n",
    "tfidfconv = tfidfconv.assign(drop_columns = True\n",
    "                            ,ngram = tfidfconv.t1_ngram\n",
    "                            ,tfidf = tfidfconv.tf* tfidfconv.idf)\n",
    "tfidfconv = tfidfconv.groupby('ngram').sum()\n",
    "tfidfconv"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3c2e7582-9c52-4c9a-8d30-1e241795b1f6",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b> Non-Converting journeys.</b></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "916340d3-1d1b-4c32-bdd6-528c1779e451",
   "metadata": {},
   "outputs": [],
   "source": [
    "tfnconv = nconv_ngrams.assign(drop_columns = True \n",
    "                            ,ngram = nconv_ngrams.ngram\n",
    "                            ,cookie = nconv_ngrams.cookie\n",
    "                            ,tf = 1.00000 * nconv_ngrams.frequency / nconv_ngrams.event_cnt)\n",
    "tfnconv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "553b8549-1beb-4c2d-a6fb-30232796c3a3",
   "metadata": {},
   "outputs": [],
   "source": [
    "cnt_ncookie = nconv_ngrams.select(['cookie']).count()\n",
    "ngram_ncnt = nconv_ngrams.select(['ngram','cookie']).groupby('ngram').count()\n",
    "idfnconv = ngram_ncnt.merge(right=cnt_ncookie, how = \"inner\" , on = [ngram_ncnt.count_cookie < cnt_ncookie.count_cookie], lsuffix = \"t1\", rsuffix = \"t2\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3df45c11-d8b3-4900-84f0-0fca2cc26cff",
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy import func\n",
    "idfnconv = idfnconv.assign(drop_columns = True\n",
    "                         , ngram = idfnconv.ngram\n",
    "                         ,idf = (idfnconv.t2_count_cookie/idfnconv.t1_count_cookie).log10())\n",
    "idfnconv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "64237dd1-da4d-457b-84eb-527efdb090ea",
   "metadata": {},
   "outputs": [],
   "source": [
    "tfidfnconv = idfnconv.merge(right=tfnconv , how = \"inner\" , on = \"ngram\" , lsuffix = \"t1\", rsuffix = \"t2\")\n",
    "tfidfnconv = tfidfnconv.assign(drop_columns = True\n",
    "                            ,ngram = tfidfnconv.t1_ngram\n",
    "                            ,tfidf = tfidfnconv.tf* tfidfnconv.idf)\n",
    "tfidfnconv = tfidfnconv.groupby('ngram').sum()\n",
    "tfidfnconv"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5cd3b7ae-d330-4324-944c-9f4874389f29",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>8.3. Rank and Compare</b></p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "04ced18a-483b-4ac9-82e9-ad7e279b25b5",
   "metadata": {},
   "source": [
    " <p style = 'font-size:16px;font-family:Arial;color:#00233C'>We will rank and regroup the channel TF-IDF scores for channels in both Converting and Non-Converting journeys.\n",
    "</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d13235cc-dab6-45a5-b671-0fbcc14f1f43",
   "metadata": {},
   "outputs": [],
   "source": [
    "window_func_conv= tfidfconv.sum_tfidf.window(order_columns=\"sum_tfidf\")\n",
    "window_func_nonconv= tfidfnconv.sum_tfidf.window(order_columns=\"sum_tfidf\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c3ebab5f-201e-4972-bf5f-11af393f0b03",
   "metadata": {},
   "outputs": [],
   "source": [
    "rank_conv = tfidfconv.assign(rank_conv=window_func_conv.rank())\n",
    "rank_nonconv = tfidfnconv.assign(rank_nonconv=window_func_nonconv.rank())\n",
    "rank_com = rank_conv.merge(right=rank_nonconv ,how = \"inner\", on = \"ngram\", lsuffix = \"t1\", rsuffix=\"t2\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "75008bd4-a0db-46bf-a1bf-fbf5b3ffc871",
   "metadata": {},
   "outputs": [],
   "source": [
    "rank_com = rank_com.assign(drop_columns = True\n",
    "                           ,channel = rank_com.t1_ngram\n",
    "                           ,converted_rank = rank_com.rank_conv\n",
    "                           ,nonconverted_rank = rank_com.rank_nonconv)\n",
    "rank_com"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "91579337-cded-4350-8d49-94047a16f513",
   "metadata": {},
   "source": [
    " <p style = 'font-size:16px;font-family:Arial;color:#00233C'>We will create a Slope Chart to compare the channel significance ranking in both Converting and Non-Converting journeys.\n",
    "</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2c7908aa-88d1-42fb-8ae5-c984e97b5e3b",
   "metadata": {},
   "outputs": [],
   "source": [
    "import matplotlib.pyplot as plt\n",
    "df = rank_com.to_pandas()\n",
    "# Sort DataFrame by channel\n",
    "df.sort_values(by='channel', inplace=True)\n",
    "\n",
    "# Create figure and axis\n",
    "fig, ax = plt.subplots()\n",
    "\n",
    "# Set x and y values for the slope chart\n",
    "x = [0, 1]\n",
    "channels = df['channel']\n",
    "y_conv = df['converted_rank']\n",
    "y_nconv = df['nonconverted_rank']\n",
    "\n",
    "# Define custom colors for each channel\n",
    "color_mapping = {\n",
    "    'Instagram': '#F28E2B',\n",
    "    'Facebook': '#4E79A7',\n",
    "    'Online Display': '#E15759',\n",
    "    'Online Video': '#76B7B2',\n",
    "    'Paid Search': '#59A14F',\n",
    "    # Add more channels and corresponding colors as needed\n",
    "}\n",
    "\n",
    "# Plot the slope chart with assigned colors\n",
    "for channel, conv, nconv in zip(channels, y_conv, y_nconv):\n",
    "    color = color_mapping.get(channel, 'black')  # Default color if channel not found in the mapping\n",
    "    ax.plot(x, [conv, nconv], marker='o', markersize=10, color=color, label='_nolegend_')\n",
    "    ax.text(-0.1, conv, channel, ha='right', va='center', fontsize=8, color='black')\n",
    "    ax.text(1.05, nconv, channel, ha='left', va='center', fontsize=8, color='black')\n",
    "\n",
    "# Set x-axis ticks and labels\n",
    "ax.set_xticks(x)\n",
    "ax.set_xticklabels(['CONVERTING', 'NON CONVERTING'])\n",
    "\n",
    "# Set y-axis label\n",
    "ax.set_ylabel('Rank')\n",
    "\n",
    "# Set title\n",
    "ax.set_title('Comparing Channel in Converting and Non Converting Paths',loc='center', pad=30)\n",
    "\n",
    "# Remove spines (borders) of the plot\n",
    "ax.spines['right'].set_visible(False)\n",
    "ax.spines['top'].set_visible(False)\n",
    "ax.spines['left'].set_visible(False)\n",
    "ax.spines['bottom'].set_visible(False)\n",
    "\n",
    "# Hide ticks and tick labels on the left spine\n",
    "ax.yaxis.set_ticks_position('none')\n",
    "ax.xaxis.set_ticks_position('bottom')\n",
    "\n",
    "# Set the limits of the x-axis\n",
    "ax.set_xlim(-0.4, 1.2)\n",
    "\n",
    "# Format y-axis tick labels to remove decimal values with .5 and invert the scale\n",
    "ax.yaxis.set_major_locator(plt.MaxNLocator(integer=True))\n",
    "ax.invert_yaxis()\n",
    "\n",
    "# Display the plot\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "71621010-0091-401a-bd46-be0cb46b3d51",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Online Video</b> and <b>Facebook</b> are slightly more significantly appearing in Converting journeys and <b>Paid Search</b> is clearly more distinctive to Non-Converting journeys.\n",
    "</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d930a6bd-18cc-42a5-b1d0-acbcac34b6bc",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<a id=\"ml\"></a>\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>9. MACHINE LEARNING BASED MODELS</b></p>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "748c3279-480b-4d15-b123-6be52c5537e4",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Machine Learning based models allow us to switch from rule-based/heuristic methods to probabilistic ones, moving further up the maturity scale. With a data-driven algorithmic  approach, attribution outputs are predicated based on data and the modelling of that data.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5956ffcc-271f-4d4b-b047-c5bca07dd3de",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>NAIVE BAYES</b></p>\n",
    "\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Naive Bayes is a machine learning algorithm commonly used for classification tasks, including text classification, spam filtering, and sentiment analysis. While it is not typically used to directly compute marketing attribution, it can be employed as part of a broader marketing attribution framework.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We will use Naive Bayes for binary text classification of paths in two categories, converted and non converted. Once the Naive Bayes classifier is trained, it can be used to estimate the probability that a specific marketing touchpoint contributed to an outcome.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>By evaluating the likelihood of the observed features associated with conversion, the algorithm can provide a probability score representing the attribution weight.</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>To run a Naive Bayes classification model, we can leverage Vantage native Naive Bayes text classifier trainer function beside some in-database data preparation.</p>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9eb3b9f8-559a-42c9-8d76-e2e582fe10eb",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Prepare Data</b></p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a158dd98-12a9-4fa3-8fa3-c42f04968b71",
   "metadata": {},
   "source": [
    " <p style = 'font-size:16px;font-family:Arial;color:#00233C'>Tokenize paths for both converting and non-converting journeys and save output into a table. We use NGramSplitter function here for path tokenization which splits the input stream of text (here paths) into \"terms\" (grams) of selected size (1:- which means each event) and count them.</p>\n",
    " "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "172af362-b22e-4800-995e-78e1284493e7",
   "metadata": {},
   "outputs": [],
   "source": [
    "conv_ngrams_df = NGramSplitter(\n",
    "               data             = npath_ConvJour_df\n",
    "              ,text_column      = 'path'\n",
    "              #,accumulate       = 'comment_id'\n",
    "              ,grams            = \"1\"\n",
    "              ,overlapping      = False\n",
    "              ,to_lower_case    = False\n",
    "              ,delimiter        = \",\"\n",
    "              #,punctuation      = '[`~#^&*()-]'\n",
    "              ,reset = \"[]\"\n",
    "              ,total_gram_count = False\n",
    "              ,accumulate = 'cookie'\n",
    "            ).result\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "aaa7c06d-a26f-4e64-aed3-9656ec22267b",
   "metadata": {},
   "outputs": [],
   "source": [
    "conv_ngrams_df = conv_ngrams_df.assign(drop_columns=True\n",
    "                                       ,cookie = conv_ngrams_df.cookie\n",
    "                                       ,ngram = conv_ngrams_df.ngram\n",
    "                                       ,distcnt = '1' \n",
    "                                       ,totcnt = conv_ngrams_df.frequency\n",
    "                                       ,conv = '1')\n",
    "conv_ngrams_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f9a4b281-ebe9-403f-956e-7549f3cc5158",
   "metadata": {},
   "outputs": [],
   "source": [
    "nonconv_ngrams_df = NGramSplitter(\n",
    "               data             = npath_NConvJour_df\n",
    "              ,text_column      = 'path'\n",
    "              #,accumulate       = 'comment_id'\n",
    "              ,grams            = \"1\"\n",
    "              ,overlapping      = True\n",
    "              ,to_lower_case    = False\n",
    "              ,delimiter        = \",\"\n",
    "              #,punctuation      = '[`~#^&*()-]'\n",
    "              ,reset = \"[]\"\n",
    "              ,total_gram_count = False\n",
    "              ,accumulate = 'cookie'\n",
    "            ).result\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "168e72b4-b266-4037-adf3-1dcd8cb46f68",
   "metadata": {},
   "outputs": [],
   "source": [
    "nonconv_ngrams_df = nonconv_ngrams_df.assign(drop_columns=True\n",
    "                                       ,cookie = nonconv_ngrams_df.cookie\n",
    "                                       ,ngram = nonconv_ngrams_df.ngram\n",
    "                                       ,distcnt = '1' \n",
    "                                       ,totcnt = nonconv_ngrams_df.frequency\n",
    "                                       ,conv = '0')\n",
    "nonconv_ngrams_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "58c21e76-5ffc-4b2f-bb9d-4da5df90884f",
   "metadata": {},
   "outputs": [],
   "source": [
    "allngrams = conv_ngrams_df.concat(nonconv_ngrams_df)\n",
    "allngrams"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d1f7d5c8-10a8-4347-a410-4d230d7aedcf",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Run Naive Bayes Text Classifier model</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>TD_NaiveBayesTextClassifierTrainer function calculates the conditional probabilities for token-category pairs, the prior probabilities, and the missing token probabilities for all categories. The trainer function trains the model with the probability values (and the predict function - not used here - would use the values to classify paths into categories).</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bb4bac60-9343-4070-873c-45f455e226ac",
   "metadata": {},
   "outputs": [],
   "source": [
    "copy_to_sql(allngrams, table_name ='allngrams', if_exists = 'replace')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5a0af8ff-5974-49b5-b161-281076309f49",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Drop table if exists\n",
    "qry = 'DROP TABLE NBOUTPUT;'\n",
    "try:\n",
    "    execute_sql(qry)\n",
    "except Exception as e:\n",
    "    if str(e.args).find('3807') >= 1:\n",
    "        pass\n",
    "    else:\n",
    "        raise\n",
    "\n",
    "# Run Naive Bayes Text Classifier and output the result in a table  \n",
    "qry = '''\n",
    "CREATE MULTISET TABLE NBOUTPUT AS\n",
    "(\n",
    "  SELECT token,category, prob as channel_prob FROM TD_NaiveBayesTextClassifierTrainer (\n",
    "   ON allngrams AS InputTable\n",
    "   USING\n",
    "   TokenColumn ('ngram')\n",
    "   DocCategoryColumn ('conv')\n",
    "   DocIDColumn ('cookie')\n",
    "   ModelType ('Bernoulli')\n",
    ") AS dt)\n",
    "WITH DATA;\n",
    "'''\n",
    "\n",
    "# Execute the query\n",
    "execute_sql(qry)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "56857dbe-d770-46b3-8255-b735d9bd06c9",
   "metadata": {},
   "outputs": [],
   "source": [
    "df1= DataFrame('NBOUTPUT')\n",
    "df1"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "943c801d-5722-43dd-84f2-16ad05544f04",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Derive Attribution Weights and visualize</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The output of the Naive Bayes Text Classifier contains: \n",
    "    <li style = 'font-size:16px;font-family:Arial;color:#00233C'>token: The classified training tokens (channels from tokenized paths).</li>\n",
    "    <li style = 'font-size:16px;font-family:Arial;color:#00233C'>category: The category of the token (converted, non-converted).</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>prob: The probability of the token in the category.</li>\n",
    "</p>    \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>This output probability is used to calculate the attribution of the channels.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d628c038-9937-4afb-beb8-eb66247973e0",
   "metadata": {},
   "outputs": [],
   "source": [
    "nboutput_df = df1[df1.category == '1'] \n",
    "nboutput_df =  nboutput_df[nboutput_df.token.isin(['Online Display', 'Online Video', 'Facebook','Instagram','Paid Search'])]\n",
    "tot_attr = nboutput_df.select('channel_prob').sum()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "439142cf-b8e7-44cf-a9fd-14679c8d01ab",
   "metadata": {},
   "outputs": [],
   "source": [
    "nboutput_df = nboutput_df.merge(right=tot_attr, how = \"inner\", on = [nboutput_df.channel_prob < tot_attr.sum_channel_prob], lsuffix = \"t1\", rsuffix = \"t2\")\n",
    "nboutput_df = nboutput_df.assign(drop_columns=True\n",
    "                                ,channel = nboutput_df.token\n",
    "                                ,nb_attribution=nboutput_df.channel_prob/nboutput_df.sum_channel_prob)\n",
    "nboutput_df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "107cd57e-b3cb-4388-a82a-71133b2c6b74",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Visualizing the results in a vertical bar chart.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7fbcc542-5ec9-43d0-bce6-a1f20f44c636",
   "metadata": {},
   "outputs": [],
   "source": [
    "import plotly.express as px\n",
    "nbattribution = nboutput_df.to_pandas()\n",
    "fig = px.bar(nbattribution, y=\"nb_attribution\", x=\"channel\", \n",
    "             color='channel', orientation='v',\n",
    "             height=600,width=900,\n",
    "             color_discrete_map = {'Online Display': '#E15759','Online Video': '#76B7B2','Facebook': '#4E79A7','Instagram': '#F28E2B' ,'Paid Search': '#59A14F'},\n",
    "             title='Attribution Summary')\n",
    "fig.update_layout(title_text='Naive Bayes Model Attribution Summary', title_x=0.5)\n",
    "fig.update_xaxes(title='Channel',tickangle=-45)\n",
    "fig.update_yaxes(title='Attribution Weight')\n",
    "fig.update_traces(width=0.5)\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "733f8a55-94e6-4bce-98c1-348958d36d13",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The above graph shows the Attribution value using the Naive Bayes Model. The Attribution Value for Facebook channel is highest and that for Online Display is the lowest.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "589505e2-2363-400e-b82a-84c6a500e756",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>10. MULTITOUCH ATTRIBUTION MODELS SUMMARY</b></p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "14b9e5d3-e828-4cd5-9e6c-53368987a825",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>To compare the attribution results of all models into a single comparative chart, we will group them together using the below query and create a visualization chart.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1131c7a7-53a3-497b-ad98-d96b5aa7e04c",
   "metadata": {},
   "outputs": [],
   "source": [
    "summ1_df = nboutput_df.merge(right=attr_channel_total , how = \"inner\" , on = [\"Channel = channel\"], lsuffix = \"t1\", rsuffix = \"t2\")\n",
    "allsumm_df = summ1_df.merge(right=ngram_freq_df , how = \"inner\" , on = [\"t1_channel=channel\"], lsuffix = \"t3\", rsuffix = \"t4\")\n",
    "# summ4_df = summ1_df.merge(right=summ2_df , how = \"inner\" , on = [\"channel = t1_channel\"], lsuffix = \"s1\", rsuffix = \"s2\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8698c552-5ea8-43b7-839c-78684a5def32",
   "metadata": {},
   "outputs": [],
   "source": [
    "allsumm_df = allsumm_df.assign(drop_columns=True,\n",
    "                        CHANNEL = allsumm_df.channel,\n",
    "                        Uniform = allsumm_df.tot_uni_attr,\n",
    "                        FirstClick = allsumm_df.tot_fc_attr,\n",
    "                        LastClick = allsumm_df.tot_lc_attr,\n",
    "                        Exponential = allsumm_df.tot_exp_attr,\n",
    "                        NaiveBayes = allsumm_df.nb_attribution,\n",
    "                        frequency = allsumm_df.tp)\n",
    "allsumm_df\n",
    "                        \n",
    "                        "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "65b251c6-720c-4031-a3ec-958ffb6a5d57",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>To compare the attribution results of all models into a single comparative chart, we will group them together using the below query and create a visualization chart.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f4d2ca77-78ec-4322-9b76-ceba1eadd45a",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "import plotly.graph_objects as go\n",
    "summary_plot = allsumm_df.to_pandas()\n",
    "fig = go.Figure(\n",
    "    data=[\n",
    "        go.Bar(name='Uniform', x=summary_plot[\"CHANNEL\"], y=summary_plot[\"Uniform\"], yaxis='y', offsetgroup=1,marker_color='#76B7B2'),\n",
    "        go.Bar(name='First Click', x=summary_plot[\"CHANNEL\"], y=summary_plot[\"FirstClick\"], yaxis='y', offsetgroup=2, marker_color='#F28E2B'),\n",
    "        go.Bar(name='Last Click', x=summary_plot[\"CHANNEL\"], y=summary_plot[\"LastClick\"], yaxis='y', offsetgroup=3,marker_color='#E15759'),\n",
    "        go.Bar(name='Exponential', x=summary_plot[\"CHANNEL\"], y=summary_plot[\"Exponential\"], yaxis='y', offsetgroup=4,marker_color='#4E79A7'),\n",
    "        go.Bar(name='Naive Bayes', x=summary_plot[\"CHANNEL\"], y=summary_plot[\"NaiveBayes\"], yaxis='y', offsetgroup=7,marker_color='#EDC948'),\n",
    "        go.Bar(name='Frequency', x=summary_plot[\"CHANNEL\"], y=summary_plot[\"frequency\"], yaxis='y', offsetgroup=9,marker_color='#B07AA1')\n",
    "    ],\n",
    "    layout={\n",
    "        'yaxis': {'title': 'Attribution '},\n",
    "\n",
    "    }\n",
    ")\n",
    " \n",
    "# Change the bar mode\n",
    "fig.update_layout(barmode='group')\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bda4779c-4b2a-4d28-8ed5-39db4980c793",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Statistical based(Simple Frequency, Association and Term Frequency) and Algorithmic based(like Naive Bayes) models tend to produce slightly different attribution scores compared to rule based.</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The bar chart above shows how many conversions were attributed to each channel for each model. Analyzing the graph, specifically the statistical/ML based in comparison to the other methods, you can gain insights as to the relative importance of different marketing channels. For the first touch, last touch and linear touch models, Facebook and Paid Search are the most import channels driving conversions while Instagram and Online Display are the least important. However, according to the Statistical/ML based models, Instagram is far more important to our conversions than our simple attribution models suggest - indeed according to the probabilistic model it is infact our third most important channel. Also, according to Associations and Naive Bayes models, Online Video appears less important compared to what other models say.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0832ed7c-4945-4d27-86cd-234c639e12e8",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Conclusion</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We have seen that Teradata Vantage provides a variety of attribution modeling including rule-based, statistical, and algorithmic-based attribution. Vantage has unique analytic capabilities for understanding customer and user behavior over time. Thus, implementing an effective marketing attribution model, using Teradata Vantage, can significantly enhance decision-making and optimize marketing strategies.</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Also, with the help of ClearScape Analytics we can use powerful, flexible attribution analysis, text processing, and statistical analytic techniques that can be applied to millions or billions of customers touchpoints. These results can be combined with other analytics to create more accurate models. These models can be deployed operationally to understand and predict actions in real-time.</p>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "477f69ee-7ea0-4669-8a17-390b3c3bb68d",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>12. Cleanup</b></p>\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Work Tables</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We need to clean up our work tables to prevent errors next time.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e78b72e5-6f02-4a5c-b535-94b5ae930cbf",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "tables = ['ALLNGRAMS','ATTRIBUTION_CONVERSION','ATTRIBUTION_MODEL_UNIFORM','ATTRIBUTION_MODEL_FIRSTCLICK',\n",
    "          'ATTRIBUTION_MODEL_LASTCLICK','ATTRIBUTION_MODEL_EXPONENTIAL','NBOUTPUT']\n",
    "\n",
    "# Loop through the list of tables and execute the drop table command for each table\n",
    "for table in tables:\n",
    "    try:\n",
    "        db_drop_table(table_name = table)\n",
    "    except:\n",
    "        pass\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e13956d2-fa89-483c-a8a7-861228c19a0d",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Databases and Tables</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We will use the following code to clean up tables and databases created for this demonstration.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f51001dc-4080-49e4-a25d-760bb4c235ed",
   "metadata": {},
   "outputs": [],
   "source": [
    "%run -i ../run_procedure.py \"call remove_data('DEMO_MultiTouchAttribution');\" \n",
    "#Takes 40 seconds"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f542175f-afd5-408b-89da-3cd0f58c6bb9",
   "metadata": {},
   "outputs": [],
   "source": [
    "remove_context()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "aabd04b2-2bf7-4d92-af82-610c55ff1adb",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "\n",
    "<b style = 'font-size:20px;font-family:Arial;color:#00233C'>Required Materials</b>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Let’s look at the elements we have available for reference for this notebook:</p>\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Filters:</b></p> \n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Industry:</b> Retail</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Functionality:</b> Path Analytics</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Use Case:</b> Digital Customer Conversion</li>\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Related Resources:</b></p>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><a href = 'https://teradata.seismic.com/Link/Content/DCGBP9J9gjD288TPcG3HFgXDHDW8'>Broken Digital Journeys CX Solution Accelerator Demo via Python Video - External - SP004183</a></li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><a href = 'https://www.teradata.com/Blogs/Customer-360-Analytics-What-Lies-Ahead'>Customer 360 Analytics, What Lies Ahead?</a></li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><a href = 'https://www.teradata.com/Trends/Data-Analytics#:~:text=Data%20Analytics-,Royal%20Bank%20of%20Canada%20Deepens%20the%20Customer%20Experience,-Data%20Analytics'>Royal Bank of Canada Deepens the Customer Experience</a></li>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4392d58a-6330-4ba3-86ec-264a76c794d6",
   "metadata": {
    "tags": []
   },
   "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",
    "            © 2023, 2024 Teradata. All rights reserved.\n",
    "        </div>\n",
    "    </div>\n",
    "</footer>"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.10"
  },
  "toc-autonumbering": false
 },
 "nbformat": 4,
 "nbformat_minor": 5
}