UseCases/Financial_Customer_Journey/Financial_Customer_Journey_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",
    "       Financial Services Customer Journey\n",
    "  <br>\n",
    "       <img id=\"teradata-logo\" src=\"https://storage.googleapis.com/clearscape_analytics_demo_data/DEMO_Logo/teradata.svg\" alt=\"Teradata\" style=\"width: 125px; height: auto; margin-top: 20pt;\">\n",
    "    </p>\n",
    "</header>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>Introduction </b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In today's business landscape, customers engage with numerous in person and digital platforms, including various company websites and online interfaces. As a result, the experiences offered by firms have become increasingly critical. However, many enterprises struggle to consider all aspects of customers’ journeys at once. Fortunately, Teradata offers innovative solutions in Vantage and ClearScape Analytics to address these challenges. Teradata’s capabilities provide impactful strategies that companies can utilize to overcome common obstacles and enhance the digital experience.</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Customer journeys are imperative to consider in many industries, especially financial based scenarios. In this demo, we will consider the important interactions customers have with a retail bank. In Vantage, users can discover new insights across various steps in a customer journey. With these functionalities, users can enhance the financial services customer journey to increase customer acquisition and adoption. \n",
    "<p>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Business Value</b>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Customer Acquisition\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>Find new customers</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>Measure Marketing Attribution</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>Determine how to increase ROI and maximize marketing effectiveness, all while minimizing the time to conversion.</li></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Customer Adoption\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>Identify what leads customers to additional high-end products such as wealth management accounts</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>Analyze all online and offline touchpoints in one place (e.g. in-branch interactions with tellers, online banking, email, call center logs)</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>Analyze promotions and advertising networks by identifying which produce the most traction across channels</li>\n",
    "    <li style = 'font-size:16px;font-family:Arial;color:#00233C'>Aggregate data sources</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'>Vantage has unique analytic capabilities for understanding customer and user behavior over time. In fact, these analytic techniques can be applied at massive scale to derive more accurate results. Then, these results can be combined with other analytics to create more advanced and accurate prediction models. Vantage also allows organizations to scale these models horizontally, by training segmented models per region, user type, etc., or vertically, by combining data from millions or billions of interactions. All of this 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'>We will see later that the insights we find are coming from these various channels both online AND offline.</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>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. Begin running steps with Shift + Enter keys.</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'>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=FSCustomerJourney_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>\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_Financial_cloud');    -- takes about 3 minutes\n",
    "-- call get_data('DEMO_Financial_local');    -- takes about 7 minutes"
   ]
  },
  {
   "cell_type": "markdown",
   "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>\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "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. Customer Acquisition</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>This is the first part of our journey. We will focus on the opening of a credit card account. Our goal is to figure out where our customers are coming from and how we can maximize our marketing return on investment (ROI). Attribution modeling describes various methods we use as marketers to properly break up and assign conversion credits to various different channels in case users take multiple website visits, via multiple channels, to arrive at the ultimate conversion behavior. We will use the powerful marketing Attribution function in Vantage to look at the multi-channel data.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>This will allow us to quantify the marketing effectiveness of both our promotions and channels (online and offline). We can analyze which promotions are most effective. Using this information, we can then collectively optimize marketing spend and placement.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We will leverage Vantage native Attribution function and consider the following methods available in Attribution:\n",
    "<ul style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Uniform: Conversion event is attributed uniformly to preceding attributable events.</li>\n",
    "    <li>First Click: Conversion event is attributed entirely to first attributable event.</li>\n",
    "    <li>Last Click: Conversion event is attributed entirely to most recent attributable event</li> \n",
    "    <li>Exponential:  Conversion event is attributed exponentially to preceding attributable events (the more recent the event, the higher the attribution).</li>\n",
    " </ul>\n",
    "</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The function takes data and parameters from multiple tables and outputs attributions. Please refer to Teradata Vantage™ - Analytics Database Analytic Functions documentation for more on Attribution function.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Attribution Input :\n",
    "<ol style = 'font-size:14px;font-family:Arial;color:#00233C'>\n",
    "<li style = 'font-size:14px;font-family:Arial;color:#00233C'>Input tables (maximum of five) (Contain data for computing attributions).</li>\n",
    "<li style = 'font-size:14px;font-family:Arial;color:#00233C'>ConversionEventTable (Contains conversion events).</li>\n",
    "<li style = 'font-size:14px;font-family:Arial;color:#00233C'>FirstModelTable (Defines type and distributions of model - we'll create one table per model)</li></ol>\n",
    "</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Attribution Syntax Elements:\n",
    "<ol style = 'font-size:14px;font-family:Arial;color:#00233C'>\n",
    "<li style = 'font-size:14px;font-family:Arial;color:#00233C'>EventColumn specifies the name of the input column that contains the events.</li>\n",
    "<li style = 'font-size:14px;font-family:Arial;color:#00233C'>TimeColumn specifies the name of the input column that contains the timestamps of the  events.</li>\n",
    "<li style = 'font-size:14px;font-family:Arial;color:#00233C'>WindowSize specifies how to determine the maximum window size for the attribution calculation</li></ol>\n",
    "    </p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We will create the model and conversion tables that allow us to send large numbers of parameters programmatically to the Attribution analytic function.</p>\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We will create some tables that allow us to send large numbers of parameters programmatically to the Attribution analytic function.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "CREATE MULTISET TABLE FSCJ_conversion_events (\n",
    "    conversion_event VARCHAR(55)\n",
    ")\n",
    "NO PRIMARY INDEX;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We want to find when people have booked accounts both online and offline and use that as our success criteria: </p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "INSERT INTO FSCJ_conversion_events VALUES('ACCOUNT_BOOKED_ONLINE');\n",
    "INSERT INTO FSCJ_conversion_events VALUES('ACCOUNT_BOOKED_OFFLINE');"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Vantage allows us to specify what type of attribution models we would like to apply. In this case we will keep it simple and choose a basic 'UNIFORM' strategy.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>UNIFORM or Linear attribution provides a look at our marketing strategy as a whole. This model is especially useful if we need to maintain awareness throughout the entire journey of the customer. The credit for a conversion is equally distributed among all the channels a customer engages with, meaning each step in the conversion process receives an equal portion of the credit.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "CREATE MULTISET TABLE FSCJ_attribution_model (\n",
    "    id    INTEGER,\n",
    "    model VARCHAR(100)\n",
    ")\n",
    "NO PRIMARY INDEX;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "INSERT INTO FSCJ_attribution_model VALUES(0, 'SIMPLE');\n",
    "INSERT INTO FSCJ_attribution_model VALUES(1, 'UNIFORM:NA');"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Now we are ready to invoke the Attribution function on our dataset. The dataset has all kinds of cross channel customer interactions we can analyze.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "CREATE MULTISET TABLE FSCJ_marketing_attribution AS (\n",
    "    SELECT * FROM Attribution (\n",
    "        ON (\n",
    "            SELECT\n",
    "                customer_identifier, interaction_timestamp, \n",
    "                TRANSLATE(interaction_type USING UNICODE_TO_LATIN) AS interaction_type, customer_days_active, \n",
    "                customer_type, marketing_placement, marketing_description, marketing_category,\n",
    "                interaction_type || product_category AS interaction_product\n",
    "            FROM DEMO_Financial.Customer_Journey\n",
    "            WHERE\n",
    "                interaction_type IN ('ACCOUNT_BOOKED_OFFLINE', 'ACCOUNT_BOOKED_ONLINE', 'CLICK', 'REFERRAL', 'BROWSE')\n",
    "                AND product_category <> '-1'\n",
    "        ) \n",
    "        PARTITION BY customer_identifier\n",
    "        ORDER BY interaction_timestamp\n",
    "        ON FSCJ_conversion_events AS ConversionEventTable DIMENSION\n",
    "        ON FSCJ_attribution_model AS FirstModel DIMENSION\n",
    "        USING\n",
    "            EventColumn ('interaction_type')\n",
    "            TimestampColumn ('interaction_timestamp')\n",
    "            WindowSize('rows:10')\n",
    "    ) AS attrib\n",
    ")\n",
    "WITH DATA;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Marketing attribution aims to identify the events leading to the opening of a credit card account and assign value to them. The specific conversion events in the data are 'ACCOUNT_BOOKED_ONLINE', 'ACCOUNT_BOOKED_OFFLINE'. Thus, calculating the most influential events and channels that are driving customer acquisition. The attribution function in Vantage supports a variety of standard attribution models. Using Vantage, we can quickly see how changes to the attribution model/parameters will affect our analyses!</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Now let's get some summary statistics from the results:</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT marketing_description, AVG(attribution) AS avg_attrib, SUM(attribution) AS sum_attrib, AVG(-time_to_conversion)/3600 AS time_to_conversion\n",
    "FROM FSCJ_marketing_attribution \n",
    "WHERE marketing_description NOT IN ('\\N', '-1')\n",
    "GROUP BY marketing_description;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%chart x=avg_attrib, y=marketing_description, gridx=true, gridy=true, mark=bar, title=\"Average Attribution\", width=700, height=300"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The above visualization is centered around the various promotions we have run. The larger the bar the more influence they had towards a customer opening an account. </p>\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%chart x=sum_attrib, y=marketing_description, gridx=true, gridy=true, mark=bar, color=green, title=\"Total Attribution\", width=700, height=300"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The chart above shows the total attribution score the promotion had. So, the biggest drivers of signups will be the longest. Here the OTHER PROMOTIONS followed by REWARDS CARD PROMOTION and then AIRLINE CARD PROMOTION. </p>\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%chart x=time_to_conversion, y=marketing_description, gridx=true, gridy=true, mark=bar, color=red, title=\"Time to Conversion\", width=700, height=300"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The above chart shows time to conversion, so how many hours on average that promotion took place before the user went ahead and signed up. The shorter time - the faster people took action. We can see that the Gold Card Promotion II did overall the best, followed by the Hotel card and MoneySupermarket.com promotions.  </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. Channel analysis</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We have different promotions and advertising networks, now let's take a look at the traction we are getting with the various promotions across different channels:</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT marketing_category, marketing_placement, SUM(attribution) AS total_attribution \n",
    "FROM FSCJ_marketing_attribution \n",
    "WHERE marketing_description NOT IN ('\\N', '-1')\n",
    "GROUP BY 1, 2;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%chart x=total_attribution, y=marketing_placement, gridx=true, gridy=true, mark=bar, color=purple, title=\"Channel Performance\", width=700, height=300"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In the above chart we can see that the HOMEPAGE BANNER has resulted into maximum attribution and is the most effective.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The next visual revolves around the various channels where we ran our marketing. As a team, we observe that the length of the bar shows the overall total attribution to that channel. The colors correspond to the promotions that we looked at in the top view, allowing us to discern which promotions were on which channels and the performance of each. In our data result set, we can identify email, in-branch (offline), as well as web. </p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>For the digital channels - collectively, we can see that the homepage ads did the best, followed by email and Google searches.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Using a BI tool, we can take this analysis to another level. We do not have BI tool available in this environment, so we have captured a screen image displaying an analysis of data produced by the analysis we did using the queries above. </p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The Top chart shows the Total Attributions for various kinds of promotions. The biggest drivers are the promotions that have the largest circle size. </p>\n",
    "<img  src=\"images/ChannelPerformance.png\"/>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The chart below with vertical lines shows the various channels and the colors of the bars indicate the type of promotion. The larger the size of the colored bar , more the contribution of that promotion using the specified channel. </p>\n",
    "\n",
    "<img  src=\"images/AttrSumm.png\"/>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Now we can see the promotions and their success across the various channels. We collectively observe an interesting insight that the Money Supermarket promotion and the Gold Card Promotion II were our most effective promotions. It is intriguing for us to compare Gold Card II to the first Gold Card Promotion. Looking down at our channels, we can collectively see that the first Gold Card promotion was only run over email, and the Gold Card Promotion II was run on multiple channels, proving to be a lot more effective for the same exact product. </p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Using this dashboard and the power of Vantage, we can easily compare the different promotions. We also notice that we did a special promotion on MoneySupermarket.com that was particularly effective. This was only run on that particular channel and had both a quick time to conversion as well as strong average attribution. </p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Additionally, we can look at some other promotions. We collectively see that we did a Rewards Card promotion that performed well across channels but was particularly effective through in-branch referrals. The airline card promotion did the best on the homepage and Google compared to the other channels.</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>5. Path To Adoption</b>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We want to see how our customers are opening higher end accounts such as wealth management. Many retail banks have found wealth management to be a key profit center, so they are looking to build their business in this area.</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Pathing is the process of discovering a sequence of antecedent actions that occur prior to a specific event of interest on sessionized data. Pathing discovers the most salient patterns across a group of individuals or entities based on which further actions are considered. Pathing allows you to provide an explanation of the relation and the relative importance of each factor.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The nPath® 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. So, we can use the nPath® analytic function in ClearScape Analytics to do pattern/time series analysis that is very hard to do in simple SQL. We want to see the common paths that customers take when they go to open a wealth management account. We will also look at the affiliation between the other accounts that wealth management customers hold. </p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In the code here you can see a few key points:</p>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>We are concatenating the interaction and the product category to make unique events </li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>We are ignoring the starting / completing of the wealth management application because everyone does that by definition and in this case we want to reduce the noise. Further analysis could be done later on incomplete applications or other scenarios.</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>The __'PATTERN'__ we are searching for is 4 events followed by opening (ACCOUNT_BOOKED) a Wealth Management account.</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>The __'SYMBOLS'__ we are using is anything but opening a wealth management account is 'EVENT' and opening said account is 'ADOPTION' </li>\n",
    "</p>\n",
    "<br>\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT TOP 5 *\n",
    "FROM nPath (\n",
    "    ON (\n",
    "        SELECT\n",
    "            customer_identifier,\n",
    "            interaction_timestamp,\n",
    "            interaction_type,\n",
    "            product_category,\n",
    "            interaction_type || '_' || product_category AS event,\n",
    "            marketing_category,\n",
    "            marketing_description,\n",
    "            marketing_placement,\n",
    "            sales_channel,\n",
    "            conversion_sales,\n",
    "            conversion_cost,\n",
    "            conversion_margin\n",
    "        FROM\n",
    "            DEMO_Financial.Customer_Journey\n",
    "        WHERE\n",
    "            product_category <> '-1'\n",
    "            AND interaction_type || '_' || product_category <> 'STARTS_APPLICATION_WEALTH MANAGEMENT'\n",
    "            AND interaction_type || '_' || product_category <> 'COMPLETE_APPLICATION_WEALTH MANAGEMENT'\n",
    "    )\n",
    "    PARTITION BY\n",
    "        customer_identifier\n",
    "    ORDER BY\n",
    "        interaction_timestamp\n",
    "    USING\n",
    "        MODE (NONOVERLAPPING)\n",
    "        -- Limit to a depth of 4\n",
    "        PATTERN ('(EVENT){4}.ADOPTION')\n",
    "        SYMBOLS (\n",
    "            event NOT LIKE 'ACCOUNT_BOOKED%_WEALTH MANAGEMENT' AS EVENT,\n",
    "            event LIKE 'ACCOUNT_BOOKED%' AND product_category = 'WEALTH MANAGEMENT' AS ADOPTION\n",
    "        )\n",
    "    RESULT (\n",
    "        ACCUMULATE(event OF ANY(EVENT, ADOPTION)) AS interaction_type_list,\n",
    "        COUNT(event OF ANY(EVENT, ADOPTION)) AS click_depth,\n",
    "        FIRST(customer_identifier OF ADOPTION) AS customer_identifier,\n",
    "        FIRST(product_category OF ADOPTION) AS product_category\n",
    "    )\n",
    ") a;"
   ]
  },
  {
   "cell_type": "markdown",
   "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 that users are taking when opening Wealth Management accounts. This is an SQL notebook, and we have another Notebook for same usecase which has python code for visualizations. A Sankey Diagram was created using the output (interacion_type_list) of the nPath® function used in the query above and below is the image for the nPath® function.</p>\n",
    "\n",
    "<img  src=\"images/PathAnalysis.png\"/>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;'>We can filter it down to the most popular paths:</p>\n",
    "\n",
    "<img  src=\"images/PathForTopCust.png\"/>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;'>The above diagram shows paths which have at least 16 customers on the path.  Of course, this notebook is just working with small data to demonstrate the functionality. In a real-world data volume, you’d be filtering for paths repeated hundreds or thousands of times. </p>\n",
    "  \n",
    "<p style = 'font-size:16px;font-family:Arial;'>So, let’s start by looking at the drivers of wealth management accounts online. We can see that the ‘Compare’ tool that the bank has on its’ website is a main step in opening an account. This allows you to compare its’ wealth management offerings to its competitors and this is proving to be very compelling. So, customers are browsing the offerings then using the compare tool and ultimately booking. </p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;'>The other main drivers of online account signups are people signing up for an auto-savings plan. So, people that are savings minded tend to open wealth management accounts. Also, general browsing the offerings and opening a CD appear to be secondary paths as well. </p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;'>Moving to offline - which we can see has different drivers. It appears to be predominately people that are opening other types of accounts as well such as CDs and Brokerage - both online and offline. We can see that the main path to booking a wealth management account offline is from in-branch referrals. So, people go into a branch and are opening another type of account and they are referred to opening a wealth management account as well!</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<b style = 'font-size:18px;font-family:Arial;color:#00233C'>Conclusion</b>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Thus, we can use the unique analytic capabilities of Vantage for understanding customer and user behavior over time. These analytic techniques can be applied at massive scale to derive more accurate results. Vantage allows organizations to scale models horizontally, by training segmented models per region, user type, etc., or vertically, by combining data from millions or billions of interactions. All of this can be deployed operationally to understand and predict actions in real-time.</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>6. 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 FSCJ_conversion_events;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "DROP TABLE FSCJ_attribution_model;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "DROP TABLE FSCJ_marketing_attribution;"
   ]
  },
  {
   "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>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "call remove_data('DEMO_Financial');-- takes about 10 seconds, optional if you want to use the data later"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "<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",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Dataset</b>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The data for this use case is stored in the `FSCustomerJourney` database. </p>\n",
    "\n",
    "<b style = 'font-size:18px;font-family:Arial;color:#00233C'>Integrated Contact History </b>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The data for this use case is taken from Customer_Journey table(Details below) from the DEMO_FSCustomerJourney Database. It is data from various source systems and channels already combined and put into one big table. This is all of the customer interactions, in a customer system this might be a view on top of various source tables.</p>\n",
    "\n",
    "`Customer_Journey`\n",
    "\n",
    "- `customer_skey`: customer key\n",
    "- `customer_identifier`: unique customer identifier\n",
    "- `customer_cookie`: cookie placed on customers device\n",
    "- `customer_online_id`: boolean - does the customer have an online account\n",
    "- `customer_offline_id`: customer account number\n",
    "- `customer_type`: is this a high value customer or just a vistor browsing the website?\n",
    "- `customer_days_active`: how long has the customer been active\n",
    "- `interaction_session_number`: session identifier\n",
    "- `interaction_timestamp`: timestamp for this event\n",
    "- `interaction_source`: channel this event is from (online / offline, in branch etc.)\n",
    "- `interaction_type`: type of event\n",
    "- `sales_channel`: channel a sales event was in\n",
    "- `conversion_id`: sales conversion identifier\n",
    "- `product_category`: what type of product the event concerned (checking, savings, cd etc..)\n",
    "- `product_type`: unused\n",
    "- `conversion_sales`: unused\n",
    "- `conversion_cost`: unused\n",
    "- `conversion_margin`: unused\n",
    "- `conversion_units`: unused\n",
    "- `marketing_code`: marketing identifier\n",
    "- `marketing_category`: marketing channel (inbranch, website, email etc..)\n",
    "- `marketing_description`: marketing campaign name\n",
    "- `marketing_placement`: specific marketing outlet (Google, Bloomberg.com etc..)\n",
    "- `mobile_flag`: boolean was on a mobile device\n",
    "- `updt`: unused"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<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> Finance</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Functionality:</b> Pathing Analysis</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Use Case:</b> Customer Journeys</li>\n",
    "</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://www.teradata.com/blogs/nps-is-a-metric-not-the-goal'>In the fight to improve customer experience, NPS is a metric, not the goal</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://teradata.seismic.com/app#/doccenter/dc7eb2cf-bd2e-462a-a056-fcc02c9fd2f2/doc/%252Fddb2fe9eb1-f754-d3df-07fa-0724e0ddd3e9%252FdfODEyNmNlZmEtZmM4Mi00ODUyLTgzZTAtOTEzMTBlODQ5YjUw%252CPT0%253D%252CVmlkZW8%253D%252FdfYmI3ODY3ZDQtM2Q4Zi00ZTk5LTg2ZDYtNjBlZTk4ODY2YTY4%252CPT0%253D%252CRXh0ZXJuYWwgQXVkaWVuY2Vz%252Flfda62602b-d188-4769-aa36-a58d162732fd//?mode=view&parentPath=sessionStorage'>Broken Digital Journeys CX Solution Accelerator Demo via Vantage Console Demo</a></li>\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. 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"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}