UseCases/Knee_Replacement/Knee_Replacement_Attribution_SQL.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "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",
    "       Total Knee Replacement: Attribution Analysis\n",
    "  <br>\n",
    "       <img id=\"teradata-logo\" src=\"https://storage.googleapis.com/clearscape_analytics_demo_data/DEMO_Logo/teradata.svg\" alt=\"Teradata\" style=\"width: 125px; height: auto; margin-top: 20pt;\">\n",
    "    </p>\n",
    "</header>\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>Introduction</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Attribution modelling techniques aim to determine the contribution of each touchpoint or channel in a path or journey of healthcare decisions. Determining the importance of each touchpoint can aid in influencing the need for the necessary treatments. Using the touchpoints to create models can provide valuable insights into the effectiveness of the paths, which in turn will help in making informed decisions regarding the various treatments procedures that can be considered. With Teradata Vantage and ClearScape Analytics, users can get a full picture of these touchpoints.  Using pathing analytics, businesses can understand the common paths that lead to a variety of outcomes, such as Total Knee Replacement.</p>\n",
    "\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'>Comprehensive health predictions and a reduced number of false positive and false negative results.</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>Reduced cost to patients and hospitals.</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>Identify patterns and symptoms leading to total knee replacements.</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>Provides an understanding of various touchpoints that lead to total Knee Replacement.</li></p>\n",
    "\n",
    "<p style = 'font-size:18px;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. Thus, implementing an effective attribution model, using Teradata Vantage, can significantly enhance decision-making and optimize 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 touchpoints. These results can be combined with other analytics to create more accurate models.</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>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"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In this script, we will apply the `ATTRIBUTION` function on a pathing dataset using all four prepared attribution models that define the weight of events in a sequence. Below are the types of Attribution models we will be using.</p>\n",
    "\n",
    "\n",
    "\n",
    " <table style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "  <tr>\n",
    "    <th>Distribution Model</th>\n",
    "    <th>Description</th>\n",
    "  </tr>\n",
    "  <tr>\n",
    "    <td>'LAST_CLICK'</td>\n",
    "    <td>Conversion event is attributed entirely to most recent attributable event.</td>  \n",
    "  </tr>\n",
    "  <tr>\n",
    "    <td>'FIRST_CLICK'</td>\n",
    "    <td>Conversion event is attributed entirely to first attributable event.</td>  \n",
    "  </tr>\n",
    "  <tr>\n",
    "    <td>'UNIFORM'</td>\n",
    "    <td>Conversion event is attributed uniformly to preceding attributable events.</td>  \n",
    "  </tr>\n",
    "  <tr>\n",
    "    <td>'EXPONENTIAL'</td>\n",
    "    <td>Conversion event is attributed exponentially to preceding attributable events (the more recent the event, the higher the attribution).</td>  \n",
    "  </tr>\n",
    "</table>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "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",
    "<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. </p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "%connect local, hidewarnings=true"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'> We will setup for execution of notebook. Begin running steps with Shift + Enter keys.</p>\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Set query_band='DEMO=Knee_Replacement_Attribution_SQL.ipynb;' update for session;"
   ]
  },
  {
   "cell_type": "markdown",
   "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.</p>\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "call get_data('DEMO_KneeReplacement_cloud'); ---takes about 10 seconds\n",
    "---call get_data('DEMO_KneeReplacement_local'); ---takes about 30 seconds"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Optional step – if you want to see status of databases/tables created and space used.</p>\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "call space_report();  -- optional, takes about 10 seconds"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>3. Create Some Attribution Models</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In order to apply the `ATTRIBUTION` function on a pathing dataset, first we need to specify an attribution model. An attribution model defines how we are going to weight the activities that precede an event. </p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>There are five model types that we can use: \n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>*first event model*: the first event gets 100% of the weight </li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>*last event model*: the last event gets 100% of the weight </li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>*uniform event model*: all events are given equal weight  </li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>*exponential model*: each subsequent event in a sequence is given a higher weight than the preceding activity</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>*weighted model*: attributed to preceding attributable events with weights specified by PARAMETERS.SEGMENT_SECONDS (specified by 'rows:K&seconds:K' in WindowSize syntax element)</li></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The total weight of all activities should equal to 1 (100%). Below are some Create table and Insert statements which will create model tables which will be used in the Attribution function.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'> <b> Model 1: First Click Model</b></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "CREATE TABLE attribution_model_first (\n",
    "    id INT, \n",
    "    model VARCHAR(100) \n",
    "); "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "INSERT INTO attribution_model_first VALUES (0,'EVENT_REGULAR'); \n",
    "INSERT INTO attribution_model_first VALUES (1, 'ALL:1.0:FIRST_CLICK:NA'); "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'> <b> Model 2: Last Click Model </b></p>\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "CREATE TABLE attribution_model_last (\n",
    "    id INT,\n",
    "    model VARCHAR(100) \n",
    "); "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "INSERT INTO attribution_model_last VALUES (0, 'EVENT_REGULAR'); \n",
    "INSERT INTO attribution_model_last VALUES (1, 'ALL:1.0:LAST_CLICK:NA'); "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'> <b> Model 3: Uniform Event Model</b></p>\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "CREATE TABLE attribution_model_uniform (\n",
    "    id INT, \n",
    "    model VARCHAR(100) \n",
    "); "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "INSERT INTO attribution_model_uniform VALUES (0, 'EVENT_REGULAR'); \n",
    "INSERT INTO attribution_model_uniform VALUES (1, 'ALL:1.0:UNIFORM:NA'); "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'> <b> Model 4: Exponential Event Model</b></p>\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "CREATE TABLE attribution_model_exponential (\n",
    "    id INT, \n",
    "    model VARCHAR(100) \n",
    "); "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "INSERT INTO attribution_model_exponential VALUES (0, 'EVENT_REGULAR'); \n",
    "INSERT INTO attribution_model_exponential VALUES (1, 'ALL:1.0:EXPONENTIAL:0.5,ROW'); "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'> <b> Create Event of Interest (EOI) Table</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Since here we are trying to analyze the events that led to Total Knee Replacement our final Event of interest will be the same. </p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "CREATE TABLE ATTRIBUTION_EOI( \n",
    "    event_of_interest VARCHAR(100)\n",
    ");"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "INSERT INTO ATTRIBUTION_EOI VALUES ('TOTAL KNEE REPLACEMENT');  "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Before moving ahead let's check the data in these model tables to verify that we have correct data. </p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT * FROM attribution_model_first; "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT * FROM attribution_model_last; "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT * FROM attribution_model_uniform; "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT * FROM attribution_model_exponential; "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT * FROM ATTRIBUTION_EOI; "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:14px;font-family:Arial'><b> *Caution*: If more than the 2 rows are inserted for any attribution model table, it will err. Drop the table and reinsert.</b></p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>4. Sessionize Function </b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The Sessionize function maps each click in a session to a unique session identifier. A session is a sequence of clicks by one user that are separated by at most n seconds. The function is useful for both sessionization and detecting web crawler (\"bot\") activity. A typical use is to understand user browsing behavior on a web site.</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>However, we've also found it useful to define a sequence of events along a longitudinal record of a member's patient journey. In this case, we will define a session as one day.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>one day = 60 seconds x 60 minutes x 24 hours = 86,400 seconds</p>\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "CREATE MULTISET TABLE KneeSessions AS (\n",
    "    SELECT \n",
    "        *\n",
    "    FROM SESSIONIZE (\n",
    "        ON(\n",
    "            SELECT memberid, \n",
    "                diagcode, \n",
    "                proccode, \n",
    "                TRANSLATE(shortdesc USING UNICODE_TO_LATIN) as shortdesc,\n",
    "                amount, \n",
    "                tstamp, \n",
    "                lastname, \n",
    "                firstname, \n",
    "                state, \n",
    "                email\n",
    "            FROM DEMO_KneeReplacement.knee_replacement\n",
    "        )\n",
    "        PARTITION BY memberid\n",
    "        ORDER BY tstamp\n",
    "        USING\n",
    "            TIMECOLUMN('tstamp')\n",
    "            TIMEOUT(86400)\n",
    "            EMITNULL('true')\n",
    "    ) \n",
    ") WITH DATA;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'> <b>View Data</b></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true,
    "tags": []
   },
   "outputs": [],
   "source": [
    "SELECT TOP 5 * FROM KneeSessions; "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>5. Attribution.</b</p>\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'> <b> Run the Attribution Function Using A Uniform Attribution Distribution.</b></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "SELECT TOP 5\n",
    "    F.memberid, F.SESSIONID, F.tstamp, F.shortdesc as event,\n",
    "    F.ATTRIBUTION AS attribution_score\n",
    "FROM ATTRIBUTION (\n",
    "         ON KneeSessions AS INPUT\n",
    "         PARTITION BY memberid\n",
    "         ORDER BY tstamp\n",
    "         ON ATTRIBUTION_EOI AS CONVERSION DIMENSION\n",
    "         ON ATTRIBUTION_MODEL_UNIFORM AS MODEL1 DIMENSION\n",
    "         USING\n",
    "             EventColumn ('shortdesc')\n",
    "             TIMESTAMPCOLUMN ('tstamp')\n",
    "             WINDOWSIZE('seconds:31536000') -- 5 years\n",
    "     ) F\n",
    "     order by 1,2,5;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": []
   },
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The output of the Attribution function using Uniform Attribution assigns equal weight to each event in a particular path apart from the Event of Interest which is 'TOTAL KNEE REPLACEMENT' in this case.</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'> <b>Create Attribution Model Outputs Table</b></p>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Write Outputs of all Attribution Model Types for Comparison"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "CREATE MULTISET TABLE ATTRIBUTION_MODEL_OUTPUT AS (\n",
    "    SELECT F.memberid as MEMBERID, F.SESSIONID as EVENTORDER, \n",
    "        F.tstamp as datestamp, F.shortdesc as EVENT,\n",
    "        F.ATTRIBUTION AS FIRST_CLICK_ATTRIBUTION,\n",
    "        L.ATTRIBUTION AS LAST_CLICK_ATTRIBUTION,\n",
    "        U.ATTRIBUTION AS UNIFORM_ATTRIBUTION,\n",
    "        E.ATTRIBUTION AS EXPONENTIAL_ATTRIBUTION\n",
    "    FROM ATTRIBUTION (\n",
    "         ON KneeSessions AS INPUT\n",
    "         PARTITION BY memberid\n",
    "         ORDER BY tstamp\n",
    "         ON ATTRIBUTION_EOI AS CONVERSION DIMENSION\n",
    "         ON ATTRIBUTION_MODEL_FIRST AS MODEL1 DIMENSION\n",
    "         USING\n",
    "             EventColumn ('shortdesc')\n",
    "             TIMESTAMPCOLUMN ('tstamp')\n",
    "             WINDOWSIZE('seconds:31536000') -- 5 years\n",
    "     ) F\n",
    "    ,ATTRIBUTION (\n",
    "         ON KneeSessions AS INPUT\n",
    "         PARTITION BY memberid\n",
    "         ORDER BY tstamp\n",
    "         ON ATTRIBUTION_EOI AS CONVERSION DIMENSION\n",
    "         ON ATTRIBUTION_MODEL_LAST AS MODEL1 DIMENSION\n",
    "         USING\n",
    "             EventColumn ('shortdesc')\n",
    "             TIMESTAMPCOLUMN ('tstamp')\n",
    "             WINDOWSIZE('seconds:31536000') -- 5 years\n",
    "     ) L\n",
    "    ,ATTRIBUTION (\n",
    "         ON KneeSessions AS INPUT\n",
    "         PARTITION BY memberid\n",
    "         ORDER BY tstamp\n",
    "         ON ATTRIBUTION_EOI AS CONVERSION DIMENSION\n",
    "         ON ATTRIBUTION_MODEL_UNIFORM AS MODEL1 DIMENSION\n",
    "         USING\n",
    "             EventColumn ('shortdesc')\n",
    "             TIMESTAMPCOLUMN ('tstamp')\n",
    "             WINDOWSIZE('seconds:31536000') -- 5 years\n",
    "     ) U\n",
    "    ,ATTRIBUTION (\n",
    "         ON KneeSessions AS INPUT\n",
    "         PARTITION BY memberid\n",
    "         ORDER BY tstamp\n",
    "         ON ATTRIBUTION_EOI AS CONVERSION DIMENSION\n",
    "         ON ATTRIBUTION_MODEL_EXPONENTIAL AS MODEL1 DIMENSION\n",
    "         USING\n",
    "             EventColumn ('shortdesc')\n",
    "             TIMESTAMPCOLUMN ('tstamp')\n",
    "             WINDOWSIZE('seconds:31536000') -- 5 years\n",
    "     ) E\n",
    "WHERE F.memberid = L.memberid\n",
    "AND   F.memberid = U.memberid\n",
    "AND   F.memberid = E.memberid\n",
    "AND   F.tstamp = L.tstamp\n",
    "AND   F.tstamp = U.tstamp\n",
    "AND   F.tstamp = E.tstamp\n",
    "AND   F.shortdesc = L.shortdesc\n",
    "AND   F.shortdesc = U.shortdesc\n",
    "AND   F.shortdesc = E.shortdesc\n",
    ")\n",
    "WITH DATA;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>View Attribution Outputs for Single Member</b></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT * FROM ATTRIBUTION_MODEL_OUTPUT WHERE MEMBERID=350031 ORDER BY 2 ASC ;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The above output of the Attribution Models is as below: \n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>*first event model*: the first event gets 100% of the weight :-  In the above output for the given member 'OTH ARTHROTOMY-KNEE' is the first click attribution i.e. the first event.</li> \n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>*last event model*: the last event gets 100% of the weight :- As seen in the output 'KNEE JOINT BIOPSY' is the last click attribution i.e. the last event before the event of Interest.</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'> *uniform event model*: all events are given equal weight :- As in the Uniform distribution column each event for that particular member is given equal weight i.e. 1/(Numberofevents-1) will give the attribution for each event.</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>*exponential model*: each subsequent event in a sequence is given a higher weight than the preceding activity :- the exponential attribution is lowest for the first event and highest for the last event.</li>\n",
    "</p>\n",
    "\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'>Sum up the attribution values for each event/model combination</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Totals for each event/model combination should equal 1.0 (100%) for each member.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT TOP 5 MEMBERID\n",
    "       ,SUM(FIRST_CLICK_ATTRIBUTION) AS FIRST_CLICK_TOTAL\n",
    "       ,SUM(LAST_CLICK_ATTRIBUTION)  AS LAST_CLICK_TOTAL\n",
    "       ,SUM(UNIFORM_ATTRIBUTION)     AS UNIFORM_TOTAL\n",
    "       ,SUM(EXPONENTIAL_ATTRIBUTION) AS EXPONENTIAL_TOTAL\n",
    "FROM ATTRIBUTION_MODEL_OUTPUT\n",
    "GROUP BY MEMBERID; "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>As seen in the above output the totals for each event/model combination is equal to/nearing 1.0 (100%) for each member.</p>\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'>Sum attribution values for each event</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT EVENT\n",
    "       ,SUM(FIRST_CLICK_ATTRIBUTION) AS FIRST_CLICK_ATTRIBUTION\n",
    "       ,SUM(LAST_CLICK_ATTRIBUTION)  AS LAST_CLICK_ATTRIBUTION\n",
    "       ,SUM(UNIFORM_ATTRIBUTION)     AS UNIFORM_ATTRIBUTION\n",
    "       ,SUM(EXPONENTIAL_ATTRIBUTION) AS EXPONENTIAL_ATTRIBUTION\n",
    "FROM ATTRIBUTION_MODEL_OUTPUT\n",
    "GROUP BY EVENT\n",
    "ORDER BY 3 DESC; "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "jp-MarkdownHeadingCollapsed": true,
    "tags": []
   },
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The above output shows the values of each type of Attribution for EACH event that we have in the different PATHS and the totals for each event/model combination.</p>\n",
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>6. Visualization</b></p>\n",
    "    <p style = 'font-size:18px;font-family:Arial;color:#00233C'> <b>Visualize Using Data from the Last Query</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%chart EVENT, FIRST_CLICK_ATTRIBUTION  , title=\"First Click Attribution\", width = 600 "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "jp-MarkdownHeadingCollapsed": true,
    "tags": []
   },
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The above output shows the total of each type of event for FIRST CLICK ATTRIBUTION. It means that the event <b>RANGE OF MOTION TESTING</b> was the first event in maximum number of cases followed by SKEL XRAY - THIGH/KNEE/LEG.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "%chart EVENT, LAST_CLICK_ATTRIBUTION, title=\"Last Click Attribution\", width = 600"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "jp-MarkdownHeadingCollapsed": true,
    "tags": []
   },
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The above output shows the total of each type of event for LAST CLICK ATTRIBUTION i.e. the last event before TOTAL KNEE REPLACEMENT. The event <b>KNEE JOINT BIOPSY </b> was the last event in maximum number of cases followed by KNEE ARTHROSCOPY.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "%chart EVENT, UNIFORM_ATTRIBUTION, title=\"Uniform Attribution\", width = 600"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "jp-MarkdownHeadingCollapsed": true,
    "tags": []
   },
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The above output shows the total of each type of event for UNIFORM ATTRIBUTION i.e. the occurrence of each event when the weights are uniformly distributed. The event <b>PHYSICAL THERAPY NEC</b> shows the maximum number of occurrences followed by OTH ARTHROTOMY-KNEE.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%chart EVENT, EXPONENTIAL_ATTRIBUTION, title=\"Exponential Attribution\" , width = 600"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "jp-MarkdownHeadingCollapsed": true,
    "tags": []
   },
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The above output shows the total of each type of event for EXPONENTIAL ATTRIBUTION i.e. the first event has minimum weight and the last event has maximum weight. The above chart shows that the events KNEE ARTHROSCOPY, KNEE JOINT BIOPSY and OTH ARTHROTOMY-KNEE show almost similar number of cases where these events have the highest weights.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "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'>Thus , we can see how attribution modelling techniques help us in determining the importance of each touchpoint and how it can aid in influencing the need for the necessary treatments. Using these touchpoints and creating models have provided valuable insights into the effectiveness of the paths, which helped in making informed decisions. With Teradata Vantage and ClearScape Analytics, users can get a full picture of these touchpoints.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>7. 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,
   "metadata": {},
   "outputs": [],
   "source": [
    "DROP TABLE attribution_model_first;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "DROP TABLE attribution_model_last; "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "DROP TABLE attribution_model_uniform;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "DROP TABLE attribution_model_exponential; "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "DROP TABLE ATTRIBUTION_EOI; "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "DROP TABLE KneeSessions;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "DROP TABLE ATTRIBUTION_MODEL_OUTPUT;  "
   ]
  },
  {
   "cell_type": "markdown",
   "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>\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "call remove_data('DEMO_KneeReplacement'); "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<b style = 'font-size:20px;font-family:Arial;color:#00233C'>Resources</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",
    "\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> HealthCare </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> Total Knee Replacement</li></p>\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/Forecasting-COVID-19-Using-Teradata-Vantage'>Forecasting COVID-19 Using Teradata Vantage</a></li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><a href='https://usc-word-edit.officeapps.live.com/we/%E2%80%A2%09https:/www.teradata.com/Blogs/Predicting-Heart-Failure-with-Teradata'>Saving Lives, Saving Costs: Predicting Heart Failure with Teradata</a></li></p>\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<footer style=\"padding-bottom:35px; background:#f9f9f9; border-bottom:3px solid #00233C\">\n",
    "    <div style=\"float:left;margin-top:14px\">ClearScape Analytics™</div>\n",
    "    <div style=\"float:right;\">\n",
    "        <div style=\"float:left; margin-top:14px\">\n",
    "            Copyright © Teradata Corporation - 2023,2024. All Rights Reserved\n",
    "        </div>\n",
    "    </div>\n",
    "</footer>"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Teradata SQL",
   "language": "Teradata SQL",
   "name": "teradatasql"
  },
  "language_info": {
   "codemirror_mode": "Teradata SQL",
   "file_extension": ".tdrs",
   "mimetype": "application/vnd.teradata.resultset",
   "name": "Teradata SQL",
   "nbconvert_exporter": "",
   "pygments_lexer": "",
   "version": "16.20"
  },
  "widgets": {
   "application/vnd.jupyter.widget-state+json": {
    "state": {},
    "version_major": 2,
    "version_minor": 0
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}