UseCases/MultiTouch_Attribution/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",
    "    \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": "9c8025de-f0a9-4959-aaf0-e1bcd92edc57",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%capture\n",
    "# # '%%capture' suppresses the display of installation steps of the following packages\n",
    "!pip install tdmca\n",
    "# !pip install tdnpathviz==0.1.2.4\n",
    "# !pip install colorlover\n",
    "# !pip install teradataml --upgrade teradataml"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "220ba917-848d-4237-9adb-2ad9b0ae17ad",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-block alert-info\">\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Note: </b><i>The above statements may need to be uncommented if you run the notebooks on a platform other than ClearScape Analytics Experience that does not have the libraries installed. If you uncomment those installs, be sure to restart the kernel after executing those lines to bring the installed libraries into memory. The simplest way to restart the Kernel is by typing zero zero: <b> 0 0</b></i></p>\n",
    "</div>"
   ]
  },
  {
   "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 tdmca\n",
    "from tdmca.tdmca import run_model\n",
    "import seaborn as sns\n",
    "import matplotlib.pyplot as plt\n",
    "import tdnpathviz\n",
    "\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": "83f92ab0-df6f-421a-a7e5-778f873d7bc6",
   "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": "cfa04cc8-b871-41fd-a3ee-2fc5dd1a8b83",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "%%capture\n",
    "execute_sql('''SET query_band='DEMO=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'>Optional step – We should execute the below step only if we want to see the 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:14px;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:14px;font-family:Arial;color:#00233C'>Timestamp: Date and time when the visit took place</li>\n",
    "<li style = 'font-size:14px;font-family:Arial;color:#00233C'>Interaction: Categorical variable indicating the type of interaction that took place</li>\n",
    "<li style = 'font-size:14px;font-family:Arial;color:#00233C'>Conversion: Boolean variable indicating whether a conversion took place</li>\n",
    "<li style = 'font-size:14px;font-family:Arial;color:#00233C'>Conversion Value: Value of the potential conversion event (revenue)</li>\n",
    "<li style = 'font-size:14px;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": "6cc93c2c-7a71-4e26-aa50-5e625388d057",
   "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": "c7e7569f-ce04-43dd-af0e-c11628bdd8ca",
   "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": "212985b2-5ac5-4177-859b-63a7a1b55844",
   "metadata": {},
   "source": [
    "<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 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 calculations faster but also reduce the overall time due to less data movement between tools. We do the data transfer for this and the subsequent visualizations wherever necessary.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ef4ea679-8a3c-4034-ac59-a80cf0ef4146",
   "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 highest and that of Instagram is 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</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": "4291f6cb-1808-47c0-a131-ef64e14d4a3e",
   "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": "92c4322e-cc34-4305-844c-9a1d83a7c53e",
   "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",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>We will check the Total cost involved in different paths that led to Conversion</b></p>\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8c4b8b36-fbcf-4423-b736-659862b618c4",
   "metadata": {},
   "outputs": [],
   "source": [
    "npath_sessions_cost = 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",
    "                                  'SUM (cost of ANY(CONVERSION,EVENT)) as Sum_cost',\n",
    "                                  'COUNT (* of ANY(CONVERSION,EVENT)) as event_cnt',\n",
    "                                  'FIRST (cookie OF ANY(CONVERSION,EVENT)) AS cookie'])\n",
    "\n",
    "\n",
    "convsumpath = npath_sessions_cost.result\n",
    "convsumpath"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1501cb34-c7e3-4808-b35c-b6d3f677c30f",
   "metadata": {},
   "outputs": [],
   "source": [
    "res_sum = convsumpath\\\n",
    "                    .groupby(['path'])\\\n",
    "                    .max()\\\n",
    "                    .sort('max_sum_cost',ascending=False)\\\n",
    "                    .to_pandas()\\\n",
    "                    .head(100)\n",
    "                    \n",
    "res_sum = res_sum.rename(columns={'max_event_cnt':'count_event_cnt'})\n",
    "res_sum"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6a97555a-615d-4a1a-bb7b-c9d8ef4c5d73",
   "metadata": {},
   "outputs": [],
   "source": [
    "attr_df[attr_df['cookie'] == 'ooEf09FDDnBoD99FCiik37kF7'].sort('tmstp').to_pandas().head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e8f8f526-8557-4cee-bb0c-540b35638ed5",
   "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 8 touch points of the Online Video channel and finally on the 9th touch point of the Online Video channel, conversion takes place. So, the path will be as below, and the total cost is 45.</p>\n",
    "<p style = 'font-size:14px;font-family:Arial;color:#00233C'><b>Online Video</b><b style = 'font-size:12px;font-family:Arial;color:#00233C'>(2018-07-11 22:13:32, cost: 5)--><b><b style = 'font-size:14px;font-family:Arial;color:#00233C'>Online Video</b><b style = 'font-size:12px;font-family:Arial;color:#00233C'>(2018-07-11 22:14:45, cost: 5)--><b><b style = 'font-size:14px;font-family:Arial;color:#00233C'>Online Video</b><b style = 'font-size:12px;font-family:Arial;color:#00233C'>(2018-07-12 22:59:49, cost: 5)--><b><b style = 'font-size:14px;font-family:Arial;color:#00233C'>Online Video</b><b style = 'font-size:12px;font-family:Arial;color:#00233C'>(2018-07-12 23:01:24, cost: 5)--><b><b style = 'font-size:14px;font-family:Arial;color:#00233C'>Online Video</b><b style = 'font-size:12px;font-family:Arial;color:#00233C'>(2018-07-12 23:06:33, cost:5)--><b><b style = 'font-size:14px;font-family:Arial;color:#00233C'>Online Video</b><b style = 'font-size:12px;font-family:Arial;color:#00233C'>(2018-07-12 23:07:04, cost: 5)--><b><b style = 'font-size:14px;font-family:Arial;color:#00233C'>Online Video</b><b style = 'font-size:12px;font-family:Arial;color:#00233C'>(2018-07-12 23:07:08, cost: 5)--><b><b style = 'font-size:14px;font-family:Arial;color:#00233C'>Online Video</b><b style = 'font-size:12px;font-family:Arial;color:#00233C'>(2018-07-14 23:58:46, cost: 5)--><b><b style = 'font-size:14px;font-family:Arial;color:#00233C'>Online Video</b><b style = 'font-size:12px;font-family:Arial;color:#00233C'>(2018-07-18 01:17:57, cost: 5)--><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 cost.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8af3473f-cd77-46c3-9070-a1c07b4b48c9",
   "metadata": {},
   "outputs": [],
   "source": [
    "sankeyPlot(res_sum, \"to\", \"Path to Conversion by Cost\", 100)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "de366feb-9fde-4b83-90f9-457fea4ad8d5",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In the above Sankey Diagram we can see the Top 100 paths, based on the cost, which 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 as 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>647, source: Online Video, target: Conversion</b>, which means there are 647 touch points of Online Video that led to Conversion. Similarly, there are 87 Paid Search, 36 Online Display, 71 Facebook and 45 Instagram touch points that lead to Conversion. Thus, we can see that in the table and the Sankey chart the topmost path shows the highest cost.</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 dataframe with all converting journeys</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We are creating a dataframe 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": "f6a5dfaa-4ad4-4765-946d-8ebd9cc0cd9b",
   "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": "bfc57c1c-a177-4d43-8dd7-a509c1af6f17",
   "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": "0f227280-c4bd-480d-bc95-4fa255f6f144",
   "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": "1e59ae0f-ca59-4de1-8fcb-8243f569f117",
   "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": "8822ec9c-48dc-4104-afab-7f7edd587865",
   "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 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 see the links at the bottom of this notebook more information on the Vantage 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": "305c6c0d-034f-45ee-a95d-453c158d7d86",
   "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": "d5466886-e1a4-4811-b99c-9c9bdb8a89fe",
   "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": "5e8fe2cf-af13-4f9e-938f-7f2cbb644f16",
   "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": "6e273947-bb1e-499b-9e3a-686b0e548fa0",
   "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": "e2a8da60-ffb3-483e-94cc-e21066f8c604",
   "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": "88fe113a-58fb-4189-9a34-c96ad5ba9a1c",
   "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": "b2861071-0418-470e-b222-4a87a952ee53",
   "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": "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>\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 calculations faster but also reduce the overall time due to less data movement between tools.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f88b07a4-e036-4762-a993-78280cf8dd21",
   "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": "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": "7f6a3f57-f358-4c89-8707-ff30397d8c3e",
   "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": "9336925a-8c74-4a0e-abb4-2124bd3c36a3",
   "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": "c15e4d8f-e367-47f9-a5c3-1d31e3d57a59",
   "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": "b98ea0f8-e412-4db5-85c5-46a7d12de44c",
   "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",
    "<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": "c30001ba-6903-439b-af5e-f5fa7faf002c",
   "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": "7c90282e-9bce-4b69-880c-7f9362b438c0",
   "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": "8187fcaf-e0ea-4217-befb-eeb5e3cb83c8",
   "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": "43b86120-eed3-4b40-98e3-9c34be262c12",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'> 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": "aa6358bb-d244-4b2f-bfea-b374a7a11aa9",
   "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": "bbfb8364-da73-40b0-8827-963266f73de4",
   "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": "d7e9aef9-82ea-46ec-8b79-3ea6e372b357",
   "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 channels associated with conversion)</b></p>\n",
    "\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Association analysis can assist in implementing multi-touch attribution models. In multi-touch attribution, credit for a conversion is distributed among multiple marketing touchpoints that contributed to the converting customer journey. Association analysis can help identify channels that frequently occur with conversions. This information can be used to allocate appropriate credit to each channel based on its association with conversion.\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    To perform Association analysis, we can leverage the native Association function from the Vantage Analytics Library. Association function produces association rules and measures of frequency, relationship, and statistical significance (associated with these rules).</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The Association function requires the following input parameters : \n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>database: The database containing the table to analyze.</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>tablename:  The table containing the columns to analyze.\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>groupcolumn:  The column representing the unique identifier group in the association rules.\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>itemcolumn: The column representing the items that are associated within the groupcolumn\n",
    "</p>\n",
    "    "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "87ec193c-00f2-4ea2-abb0-77f672eb47c6",
   "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 ASSOCIATION BASED ATTRIBUTION MODEL</b></p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5637f43f-fd95-46b8-8d6a-10c27c35a0e5",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>By analyzing associations between marketing channels and customer conversions we identify channels that have a high likelihood of driving conversions, from which we can derive attribution weights\n",
    "<p style = 'font-size:16px;font-family:Arial"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c37b0b50-4e1c-45e2-b8e0-bb8a1a3965ca",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Prepare data</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'> Here we create a table to check the association based on conversion. So, for each channel in the data we create channel1 as the associated channel based on whether conversion is happening(conversion=1) or not.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ea957e72-8b36-4c3e-96eb-f8176105d150",
   "metadata": {},
   "outputs": [],
   "source": [
    "ATTRIBUTION_DATA4_ASSO = attr_df.assign(channel1 = case([(attr_df.conversion ==1 ,'Conversion')], else_ = attr_df.channel))\n",
    "ATTRIBUTION_DATA4_ASSO"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "39ce0a42-001c-4850-ab70-ad21a46d824e",
   "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'>Based on the data created above, we calculate the association between channels by using the Vantage Analytic Library(VAL) function. </p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Vantage Analytics Library (VAL) is a set of over 50 functions for analyzing data content, performing hypothesis tests and executing advanced analytics for in-database execution. We are analyzing the Association between the channels using this data. </p> \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "42978bd1-6d7c-4af4-80c6-19302a1c25e3",
   "metadata": {},
   "outputs": [],
   "source": [
    "obj = valib.Association(data=ATTRIBUTION_DATA4_ASSO, group_column=\"cookie\", item_column=\"channel1\")\n",
    " \n",
    "    # Print the affinity result. Only affinity result for default combination 11 is produced.\n",
    "asso_global_df=obj.result_11\n",
    "asso_global_df "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7e38c8ea-1019-41fb-9ee3-cf0a90e5f73e",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>7.3 Extract attribution weights and output results in a table</b></p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d5788564-4b1f-4067-bba4-750b69fdd877",
   "metadata": {},
   "source": [
    " <p style = 'font-size:16px;font-family:Arial;color:#00233C'>Filtering on item1of2='conversion', the \"confidence\" shows how likely a conversion occurs given that a channel is part of the converting journey. This value is used as the attribution weight.\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "93560e26-fcdc-4978-b7d6-8d1a02a5c3bb",
   "metadata": {},
   "outputs": [],
   "source": [
    "asso_tot_attr = asso_global_df[asso_global_df.ITEM1OF2 == 'conversion']\n",
    "asso_tot = asso_tot_attr.agg({'CONFIDENCE' : ['sum']})\n",
    "asso_attr = asso_tot_attr.merge(right=asso_tot, how = \"cross\" , on = [asso_tot_attr.ITEM1OF2 == 'conversion'])\n",
    "asso_attr = asso_attr.assign(drop_columns=True\n",
    "                                       ,channel = asso_attr.ITEM2OF2\n",
    "                                       ,attribution = asso_attr.CONFIDENCE/asso_attr.sum_CONFIDENCE)\n",
    "asso_attr"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6e6693d6-4ae6-4577-a5c1-93279cf277fd",
   "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": "b01608c4-34b2-4056-b2f8-9a80029f10cc",
   "metadata": {},
   "outputs": [],
   "source": [
    "import plotly.express as px\n",
    "asso = asso_attr.to_pandas()\n",
    "fig = px.bar(data_frame=asso, y=\"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='Associations Based 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": "7d85c457-39dc-4c17-96ec-5b9145d9a781",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The above graph shows a conversion is likely to happen when any of these channels is in the converting journey based on the calculated Attribution weights. We can see that a conversion is most likely to happen when Facebook channel is part of the converting journey as its association(Attribution Weight) is highest when we are using the Association model.</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>8. 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": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>8.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": "dfc2890d-6881-4482-9eff-63c94a45da6b",
   "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": "8419c5c5-d0e6-4400-8de3-b28f605a5205",
   "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>8.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": "73e8c6c6-78a6-475f-827e-73db863af046",
   "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",
    "<p style = 'font-size:16px;font-family:Arial"
   ]
  },
  {
   "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>9. 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"
   ]
  },
  {
   "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>9.1. Prepare Data</b></p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c50d05af-c1d9-4730-b9d2-abdd2b3171c9",
   "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": "96ade236-67d5-467a-a810-948bc7c150b1",
   "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": "78547c6e-a68d-4014-8332-a9b294d3d8bf",
   "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>9.2. Compute TF-IDF scores</b></p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c969f06c-c734-439e-af74-9e5cf99bb334",
   "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": "a1c9b32f-c458-4547-bd86-b05138a56a49",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b> Converting journeys.</b></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "03007198-425a-4e06-a94f-f20637fa645c",
   "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": "d275c3ce-6892-4302-ae35-75a439ea17bd",
   "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": "d9e99057-8486-406b-aa42-8064df047773",
   "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": "3b0a1296-73cb-4577-b60b-9d959011c167",
   "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": "7039735d-1020-446c-aed6-561b4b587410",
   "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": "84bb1168-e4bd-4adc-b21f-c46263fbd48d",
   "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": "66acaf8f-970d-4f4e-bcff-e96ed0840e4e",
   "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": "d6faca9e-af05-430a-b261-50c904647784",
   "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>9.3. Rank and Compare</b></p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a018546f-f4aa-4f78-bd3d-85b39d8f1005",
   "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": "f2d3790c-b439-412c-a325-73010ba3008d",
   "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": "79c3bffd-14dc-4c57-bd4d-93f7bbacde81",
   "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": "16d380e1-ec8d-4443-8d22-ae82da392616",
   "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": "8b6a73c0-4169-43c7-9753-3bad0f731e2c",
   "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>Paid Search</b> and <b>Facebook</b> are slightly more significantly appearing in Converting journeys and <b>Online Video</b> is clearly more distinctive to Non-Converting journeys.\n",
    "</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d930a6bd-18cc-42a5-b1d0-acbcac34b6bc",
   "metadata": {},
   "source": [
    "<a id=\"ml\"></a>\n",
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>10. 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>10.1 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",
    " <p style = 'font-size:16px;font-family:Arial;color:#00233C'>This data preparation step will serve both Naive Bayes and Random Forest models.\n",
    "</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a9d84884-ee15-4715-b0a1-74a0b93bbc96",
   "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": "72e669ff-46de-4dd7-ac9a-8c496bdbb873",
   "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": "d7c0521c-689d-4c73-9b79-6133bd69beea",
   "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": "04d209a5-1a22-4710-8e2d-b60d1fdccbbd",
   "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": "92806224-c9f1-4c42-873c-7ed2d7b84ab6",
   "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": "828b1211-8a03-4848-8b9a-444594d511b2",
   "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",
    "\n",
    "# Execute the query\n",
    "execute_sql(qry)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "39fd950a-bdac-4ab2-9105-c057cafbf558",
   "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": "18fb531a-f35f-42a3-ad5d-98dd7e1a1915",
   "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": "035d4f3f-487c-49d2-92fd-d566fcf0ed71",
   "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": "810e350e-3463-485b-a963-daca21a9d720",
   "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": "55afa626-f833-45e9-b623-863fc02f9238",
   "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": "f4e60939-f607-408b-9b60-5e7fa3c46478",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>10.2 RANDOM FOREST</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Random Forest is a suitable algorithm for multitouch attribution because it can handle both categorical and numeric features, capture non-linear relationships, and handle interactions between multiple marketing touchpoints. Moreover, it  provides feature importance scores, which natively give the relative contributions of different channels in driving customer actions.\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8da3560c-d1bc-45e0-b58b-09aa7adb1cd9",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b> Data preparation and feature engineering</b></p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2a4c4bbe-0bc3-4d40-bb32-0419c627d119",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We will create a features table : in each converting and non-converting path we count the number of times a channel appears and derive a variable for each channel.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0079e80d-3572-47f2-8884-79ddb034c0bf",
   "metadata": {},
   "outputs": [],
   "source": [
    "pivot_df = allngrams.pivot(aggfuncs=[allngrams.totcnt.sum()],\n",
    "                            col1=allngrams.ngram,\n",
    "                            col1_values= ['Online Display', 'Online Video','Facebook','Instagram', 'Paid Search' ])\n",
    "pivot_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fcb32edb-c5db-4d42-b083-333f02c38d4b",
   "metadata": {},
   "outputs": [],
   "source": [
    "MLFEATURESTOT = pivot_df.assign(drop_columns=True\n",
    "                                ,cookie = pivot_df.cookie\n",
    "                                ,conv = pivot_df.conv.cast(type_=INTEGER)\n",
    "                                ,online_display = pivot_df.sum_totcnt_onlinedisplay.zeroifnull()\n",
    "                                ,online_video = pivot_df.sum_totcnt_onlinevideo.zeroifnull()\n",
    "                                ,facebook = pivot_df.sum_totcnt_facebook.zeroifnull()\n",
    "                                ,instagram = pivot_df.sum_totcnt_instagram.zeroifnull()\n",
    "                                ,paid_search = pivot_df.sum_totcnt_paidsearch.zeroifnull()\n",
    "                               )\n",
    "MLFEATURESTOT"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b107dfe4-184c-4429-8000-fb46d542a6c6",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b> Ensure variables are not correlated</b></p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f9ee5236-2979-4558-91db-2126446736aa",
   "metadata": {
    "tags": []
   },
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We use a Vantage Analytics Library function to check for features correlation.</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>To create the correlation we use the function 'matrix'.</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The following types of matrix can be built with this function. If not specified, a sum-of-squares-andcross- products (SSCP) matrix is built.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>    \n",
    "    <li style = 'font-size:14px;font-family:Arial;color:#00233C'> SSCP = sum-of-squares-and-cross-products matrix</li>\n",
    "<li style = 'font-size:14px;font-family:Arial;color:#00233C'> ESSCP = Extended-sum-of-squares-and-cross-products matrix (the default)</li>\n",
    "<li style = 'font-size:14px;font-family:Arial;color:#00233C'> CSSCP = Corrected-sum-of-squares-and-cross-products matrix</li>\n",
    "    <li style = 'font-size:14px;font-family:Arial;color:#00233C'> COV = Covariance matrix</li>\n",
    "    <li style = 'font-size:14px;font-family:Arial;color:#00233C'> COR = Correlation matrix</li>\n",
    "</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Here we pass 'COR' as the parameter to get the correlation. A correlation matrix is built to determine the correlations or relationships between the various columns in the matrix.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6c980fb8-bfbe-47c8-bbe1-922e1cb60b0c",
   "metadata": {},
   "outputs": [],
   "source": [
    "obj_matrix = valib.Matrix(data=MLFEATURESTOT,\n",
    "                       columns=['online_display', 'online_video','facebook,instagram', 'paid_search'],\n",
    "                       type=\"COR\")\n",
    "obj_matrix.result"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "46a4ff65-6734-432e-9f0b-b46e4389d172",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>No correlation between features, we can now move to scaling.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a51a5292-1798-4de9-90a7-679a5c9b5c48",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Scale features</b></p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ba0c08bb-5521-47b3-904a-163ade7ec3fd",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Rescale is useful with algorithms that require or work better with data within a certain range. Rescale is only valid on numeric columns, and not columns of type date. Here we normalize the columns using the MinMaxScalar and Transform function from VAL(Vantage Analytic Library) by using the rescale option.</p>\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2944371f-ca37-4deb-82c2-735fb275455b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# MLFEATURESTOT = MLFEATURESTOT.set_index('cookie')\n",
    "rs_1 = MinMaxScalar(lbound=0, ubound=1, columns=[\"online_display\", \"online_video\", \"facebook\", \"instagram\",\"paid_search\"])\n",
    "obj = valib.Transform(data=MLFEATURESTOT,\n",
    "                          rescale=rs_1,\n",
    "                          key_columns=[\"cookie\",\"conv\"],\n",
    "                          index_columns = [\"cookie\",\"conv\"]) \n",
    "input_data = obj.result\n",
    "input_data"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6ce39e0b-41b4-412f-9d5c-331e826adab4",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Get the data and convert to pandas</b></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5ecbd2ea-62f7-41a4-8c6f-511bf937a3af",
   "metadata": {},
   "outputs": [],
   "source": [
    "rf = input_data.to_pandas().reset_index()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f865c64c-b48c-487d-b071-44afe782fcf0",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b> Run Random Forest Classifier model</b></p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d0bcc827-cae3-40b5-bdc7-232a68fc3326",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Run model</b></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "951d70e0-93c1-4e66-8864-8e059a32d87e",
   "metadata": {},
   "outputs": [],
   "source": [
    "X = rf.drop(['conv','cookie'], axis = 1)\n",
    "Y = rf['conv']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "312eff5d-494e-4f68-bd9a-25ae241318dd",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Run RF\n",
    "model = RandomForestClassifier(n_estimators = 100, random_state = 0)\n",
    "model.fit(X,Y)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "73f87208-9075-49a9-b596-8d83fd5aef7a",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Retrieve feature importance scores and send result back to Vantage</b></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d0d48080-22d0-4a40-aeee-f8d42d719f15",
   "metadata": {},
   "outputs": [],
   "source": [
    "importances = model.feature_importances_"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3ee99d4d-8eaa-4295-a875-c253e9f1d337",
   "metadata": {},
   "outputs": [],
   "source": [
    "print(importances)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fea3ef30-9a58-4ec6-b1d0-29bba2191898",
   "metadata": {
    "tags": []
   },
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Convert values to data frame</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2ce97167-3bbe-4c34-ae9f-fe5dbb020b3b",
   "metadata": {},
   "outputs": [],
   "source": [
    "rfout = pd.DataFrame([importances])\n",
    "rfout.columns = ['Online_Display', 'Online_Video', 'Facebook', 'Instagram','Paid_Search']\n",
    "rfout"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a1b3bdc4-9a07-4521-94a9-08efa8040088",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>As seen in the output above since we had considered the channels as the features for the Random Forest Classifier, the importance will be treated as attribution for each channel.</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We will store this Output to Vantage in a table</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "36108569-a5d6-4aff-938a-6639d4958851",
   "metadata": {},
   "outputs": [],
   "source": [
    "copy_to_sql(rfout, table_name = 'RFOUTPUT', if_exists = 'replace')\n",
    "rfout_df = DataFrame('RFOUTPUT')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9de4bb74-bf21-4fe4-89ea-46e2ed98ebf4",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Format RF Attribution Weights and Visualize</b></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9af2bca2-3e75-4cae-9c82-154bcafaab65",
   "metadata": {},
   "outputs": [],
   "source": [
    "unpivot_df = rfout_df.unpivot(columns={rfout_df.Online_Display:\"Online Display\", \n",
    "                                    rfout_df.Online_Video:\"Online Video\",\n",
    "                                    rfout_df.Facebook:\"Facebook\",\n",
    "                                    rfout_df.Instagram:\"Instagram\",\n",
    "                                    rfout_df.Paid_Search:\"Paid Search\"},\n",
    "                        transpose_column=\"Channel\",\n",
    "                        measure_columns=\"Attribution\")\n",
    "unpivot_df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bc3c2f95-a6f5-45c5-b61a-24334c07c028",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>So, the above output provides the attribution values using the RandomForestClassifier feature importance.</p>\n",
    "<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": "4b4cce4d-b44e-4e6d-ae56-afc880d5c667",
   "metadata": {},
   "outputs": [],
   "source": [
    "import plotly.express as px\n",
    "rfattribution=unpivot_df.to_pandas()\n",
    "fig = px.bar(rfattribution, y=\"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='Random Forest 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": "d1ceb4f5-60a1-4f9f-a478-a4cce7ad72cd",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The above graph shows the Attribution values for each channel using the Random Forest Model. The Attribution Value for Facebook channel is highest and that for Online Display is the lowest.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "adeb27d8-add3-4a10-92e8-3839c200c0b2",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>10.3 MARKOV CHAINS</b></p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "887a00ff-360e-4a51-942f-e8ebbceeca92",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'> Markov chains are probabilistic models that represent a sequence of events where the probability of each event depends only on the previous event. In the context of marketing attribution, Markov chains can help capture the transition probabilities between different marketing touchpoints in a customer journey. The algorithm for Markov chains can be summarized in 2 steps:\n",
    "<ul style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Calculate transition probabilities between all states (channels/touchpoints)</li>\n",
    "    <li>Calculate removal effects</li>\n",
    " </ul>   \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "We use a python library called markov-model-attribution which performs the above steps.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6bc50c26-4c8f-45c0-8f2b-35b086ed0a7b",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Prepare data</b></p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "447fb74f-41dc-4ce0-9108-8d467af39298",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'>Gather and transform converting and non-converting paths</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ea2c8c3e-bf2e-4e4b-9b09-1dbe422e8403",
   "metadata": {},
   "source": [
    "<ul style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>This library currently accepts a single-column Pandas dataframe containing paths.</li>\n",
    "    <li>Each path should begin with \"start\" and end with either \"conv\" (for converting paths) or \"null\" (for non-converting paths)</li>\n",
    "    <li>Each path should be delimited by \" > \"</li>\n",
    "    <li>We apply data transformation to the previously created converting paths and non-converting paths tables <a href='#npdp'>(as executed here)</a> </li>\n",
    " </ul>  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c9873a27-4440-4a93-b195-c153eaf613f9",
   "metadata": {},
   "outputs": [],
   "source": [
    "conv_df = npath_ConvJour_df[npath_ConvJour_df['event_cnt']<=20]\n",
    "nonconv_df = npath_NConvJour_df[npath_NConvJour_df['event_cnt']<=20]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d1af9213-a488-4d49-a588-399163305e5a",
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy import func\n",
    "conv_df = conv_df.assign(path1 = conv_df.path.replace(\"[\",\"\").replace(\"]\",\"\").replace(\",\",\">\"))\n",
    "conv_df = conv_df.assign(path1 = func.Regexp_Replace(conv_df.path1.expression,'>',' >'))\n",
    "conv_df = conv_df.assign(drop_columns=True, paths = func.concat(\"start > \",conv_df.path1.expression,\" > conv\" ))\n",
    "nonconv_df = nonconv_df.assign(path1 = nonconv_df.path.replace(\"[\",\"\").replace(\"]\",\"\").replace(\",\",\">\"))\n",
    "nonconv_df = nonconv_df.assign(path1 = func.Regexp_Replace(nonconv_df.path1.expression,'>',' >'))\n",
    "nonconv_df = nonconv_df.assign(drop_columns=True, paths = func.concat(\"start > \",nonconv_df.path1.expression,\" > null\" ))\n",
    "markov = conv_df.concat(nonconv_df)\n",
    "markov"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e61d2cc8-3b99-41a0-b5e8-c802f477b4ce",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'> Get the prepared data and convert to pandas</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e5b3f2ad-1a0d-4840-b773-49c45fd21199",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We now get the data as a Teradataml dataframe And convert the data as a pandas dataframe </p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c447f84e-f866-4385-929e-c243844de116",
   "metadata": {},
   "outputs": [],
   "source": [
    "# markov = DataFrame('MARKOVINPUT')\n",
    "mc = markov.to_pandas()#.reset_index()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dfd1066c-716e-4868-9c22-d49b37bf773d",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Run Markov Chains model</b></p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2c1b2a2d-1ec0-4095-8f9f-dcd8dbeab501",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Run model</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4b7a5671-c012-4266-a413-bfe40f443aa7",
   "metadata": {},
   "outputs": [],
   "source": [
    "model = run_model(paths = mc)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1e1a0773-a9b8-4f8f-b6b2-bcdc7039a24d",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Retrieve Markov total attribution values and send to Vantage</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f49fc543-dbc0-40cc-ac31-64a978a89df1",
   "metadata": {},
   "outputs": [],
   "source": [
    "print(model['markov_conversions'])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fdec2c3b-95c1-4ad2-abe7-8c763d0bb5ce",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We now get the data as a Teradataml dataframe.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bc571738-31f9-4c3b-be68-fe88c0b52802",
   "metadata": {},
   "outputs": [],
   "source": [
    "mcout = pd.DataFrame([model['markov_conversions']])\n",
    "mcout.columns = ['Facebook','Paid_Search','Online_Display', 'Instagram','Online_Video']\n",
    "mcout"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e633688d-f2bb-453f-a1c9-f9d65d1c4b2c",
   "metadata": {},
   "source": [
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We store the Output to Vantage in a table</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "10f8046f-9ec9-426e-b383-db1ff897fe13",
   "metadata": {},
   "outputs": [],
   "source": [
    "copy_to_sql(mcout, table_name = 'MCOUTPUT', if_exists = 'replace')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8629ee37-3564-419b-b261-5517c9856674",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Calculate Attribution Weights and Visualize</b></p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3b86e08b-136c-4990-abef-f551d8783d8a",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The following query formats data and calculates attribution weights.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f5478753-c1ac-4ad3-b0ab-d8116ca04c5d",
   "metadata": {},
   "outputs": [],
   "source": [
    "mcout=DataFrame('MCOUTPUT')\n",
    "mcout_tot = mcout.assign(drop_columns=True,\n",
    "                         total_conversion = mcout.Facebook + mcout.Paid_Search + mcout.Online_Display \n",
    "                         + mcout.Online_Video + mcout.Instagram)         "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f52731b7-7b98-4300-9bcb-1aa8232e49ba",
   "metadata": {},
   "outputs": [],
   "source": [
    "mcout_unpivot_df = mcout.unpivot(columns={mcout.Online_Display:\"Online Display\", \n",
    "                                    mcout.Online_Video:\"Online Video\",\n",
    "                                    mcout.Facebook:\"Facebook\",\n",
    "                                    mcout.Instagram:\"Instagram\",\n",
    "                                    mcout.Paid_Search:\"Paid Search\"},\n",
    "                        transpose_column=\"Channel\",\n",
    "                        measure_columns=\"Attribution\")\n",
    "mcout_unpivot_df = mcout_unpivot_df.merge(right=mcout_tot, how = \"inner\", on = [mcout_unpivot_df['Attribution'] < mcout_tot['total_conversion']], lsuffix = \"t1\", rsuffix = \"t2\")\n",
    "mcout_unpivot_df = mcout_unpivot_df.assign(drop_columns=True, \n",
    "                                           Channel = mcout_unpivot_df.Channel,\n",
    "                                           Attribution = mcout_unpivot_df.Attribution/mcout_unpivot_df.total_conversion)\n",
    "mcout_unpivot_df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7d570ed8-06f5-4b8e-a584-4a7788627523",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>So, the above output provides the attribution values using the MARKOV CHAINS model.</p>\n",
    "<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": "e5fe78fa-c7d5-4a99-95c6-dcdc2bc30e4a",
   "metadata": {},
   "outputs": [],
   "source": [
    "import plotly.express as px\n",
    "markovattribution = mcout_unpivot_df.to_pandas()\n",
    "fig = px.bar(data_frame=markovattribution, y=\"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='Markov Chains 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": "3868f40c-2437-45c9-8538-0bbbfdc8c5f7",
   "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 using the Markov Chains model 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>11. MULTITOUCH ATTRIBUTION MODELS SUMMARY</b></p>"
   ]
  },
  {
   "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": "6a97c98d-3f22-4c09-b084-a399b48911ab",
   "metadata": {},
   "outputs": [],
   "source": [
    "summ1_df = asso_df.merge(right=ngram_freq_df , how = \"inner\" , on = [\"item2of2 = channel\"])\n",
    "summ2_df = mcout_unpivot_df.merge(right=unpivot_df , how = \"inner\" , on = [\"Channel = channel\"], lsuffix = \"t1\", rsuffix = \"t2\")\n",
    "summ3_df = nboutput_df.merge(right=attr_channel_total , how = \"inner\" , on = [\"Channel = channel\"], lsuffix = \"t1\", rsuffix = \"t2\")\n",
    "summ4_df = summ1_df.merge(right=summ2_df , how = \"inner\" , on = [\"channel = t1_channel\"], lsuffix = \"s1\", rsuffix = \"s2\")\n",
    "allsumm_df = summ4_df.merge(right=summ3_df , how = \"inner\" , on = [\"channel = t1_channel\"], lsuffix = \"s1\", rsuffix = \"s2\")\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": "0f43398a-2a20-4c26-8357-3ac64f5b7b3e",
   "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",
    "                        MarkovChains = allsumm_df.t1_Attribution,\n",
    "                        RandomForest = allsumm_df.t2_Attribution,\n",
    "                        NaiveBayes = allsumm_df.nb_attribution,\n",
    "                        association = allsumm_df.CONFIDENCE,\n",
    "                        frequency = allsumm_df.tp)\n",
    "allsumm_df\n",
    "                        \n",
    "                        "
   ]
  },
  {
   "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='Random Forest', x=summary_plot[\"CHANNEL\"], y=summary_plot[\"RandomForest\"], yaxis='y', offsetgroup=5,marker_color='#9C755F'),\n",
    "        go.Bar(name='Markov Chains', x=summary_plot[\"CHANNEL\"], y=summary_plot[\"MarkovChains\"], yaxis='y', offsetgroup=6, marker_color='#BAB0AC'),\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='Association', x=summary_plot[\"CHANNEL\"], y=summary_plot[\"association\"], yaxis='y', offsetgroup=8,marker_color='#59A14F'),\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, Random Forest and Markov Chains) 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 Markov Chains, Associations and Naive Bayes models, Online Video appears less important compared to what other models say.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c3e7fc9c-9b41-4aec-9821-877f9608c971",
   "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": "831e0559-d8fa-40f4-98cb-cc82b33d7380",
   "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": "3e36efdb-a867-414d-913d-a6f89af96d99",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "tables = ['ALLNGRAMS','ATTRIBUTION_CONVERSION','ATTRIBUTION_MODEL_UNIFORM','ATTRIBUTION_MODEL_FIRSTCLICK',\n",
    "          'ATTRIBUTION_MODEL_LASTCLICK','ATTRIBUTION_MODEL_EXPONENTIAL','RFOUTPUT','NBOUTPUT','MCOUTPUT']\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": "c60443c3-5225-44d7-ad89-8ed8c54102b2",
   "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": "6fbfa951-2844-4f79-b7c6-08c6e3c3d5fe",
   "metadata": {},
   "outputs": [],
   "source": [
    "%run -i ../run_procedure.py \"call remove_data('DEMO_MultiTouchAttribution');\" \n",
    "#Takes 40 seconds"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e081f5ae-3b5c-40b3-a6af-42e0588f0103",
   "metadata": {},
   "outputs": [],
   "source": [
    "remove_context()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9eacb589-f1c4-439b-ae67-1e6fbbe35107",
   "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",
    "<br>\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",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><a href = 'https://docs.teradata.com/search/all?query=Analytics+Database+Analytic+Functions&content-lang=en-US'>Teradata Analytics Database Analytic Functions</a></li>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8527dd62-7c24-4382-8cfd-cd187c90091d",
   "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
}