UseCases/Customer360/CustomerIdentityMatching_SQL.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "e39130df",
   "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",
    "       Creating the Golden Record and Possible Uses\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"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5dae4f7d",
   "metadata": {},
   "source": [
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>Introduction</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Companies rely on accurate customer valuations to correctly determine how to treat certain classes, what offers to serve them, and how much effort to exert in retaining them. Multi-channel companies often have silos of data from separate sales channels and supplement the data using third-party sources. These multi-channel companies need to combine the data souces to create a holistic view of their customers, particularly their valuation. This demonstration showcases Vantage's capabilities in matching customers from different datasets.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Data analysts match customers across different data sources by calculating the similarity between two names (or other fields) using one of several distance measurements. Steps involved in matching two strings are:</p>\n",
    "\n",
    "<table style = 'width:100%;table-layout:fixed;'>\n",
    "    <tr>\n",
    "        <td style = 'vertical-align:top' width = '50%'>\n",
    "            <ol style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "                <li>Select one string to be the base and the other to be the target</li>\n",
    "                <br>\n",
    "                <li>Select a string comparison type to use. Examples include Jaro, Jaro-Winkler, Hamming, and cosine</li>\n",
    "                <br>\n",
    "                <li>Determine the number of insertions, deletions, replacements, and tranpositions needed to morph the base string into the target string</li>\n",
    "                <br>\n",
    "                <li>Calculate the similarity of the two strings by using the formula for the string comparison type chosen and the insertions, deletions, replacements, and transposes</li>\n",
    "            </ol>\n",
    "        </td>\n",
    "        <td><img src = 'images/counting.png' width = '250'></td>\n",
    "    </tr>\n",
    "</table>\n",
    "\n",
    "\n",
    "<b style = 'font-size:16px;font-family:Arial;color:#00233C'>Leverage native Vantage processing for efficient data field matching workflows</b>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>ClearScape Analytics provides a StringSimilarity function to calculate how close two key strings are. Typically, analysts will use different key strings depending on the lists. For example, matching on name works when combining two customer lists, whereas matching on physical addresses works when comparing a customer list with a household list.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Data engineers  combine lists two at a time. For instance, combining four lists A, B, C, and D involves three matching steps. The first step combines A and B to form AB. The second step combines lists AB with C to form ABC. The final step combines ABC with D to form ABCD. Each step requires its own matching table and possibly different key strings.</p>\n",
    "\n",
    "<img src = 'images/fourlists.png' width = 50% align = \"center\">\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4adcffde",
   "metadata": {},
   "source": [
    "<b style = 'font-size:20px;font-family:Arial;color:#00233C'>Data</b>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The data for this demonstration is based on a brick-and-mortar equipment distributor selling to farmers in four states: New York, Ohio, Pennsylvania, and West Virginia. Over time, this company built a reliable customer list. As customers asked for electronic monitoring service, the company employed an online partner to sell this product. The company also outsourced its call center to a third-party to handle customer service at a cost. Finally, the company worked with a digital marketing group to place cookies on website visitors.</p>\n",
    "\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The goal is to create a golden record of customers by combining the four lists mentioned above.  Steps in this demonstration are as follows:</p>\n",
    "\n",
    "<table style = 'width:100%;table-layout:fixed;'>\n",
    "    <tr>\n",
    "        <td style = 'vertical-align:top' width = '50%'>\n",
    "            <ol style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "                <li>Analyze the raw data from each of the four sources</li>\n",
    "                <br>\n",
    "                <li>Append matched online data to the equipment data</li>\n",
    "                <br>\n",
    "                <li>Append call center data</li>\n",
    "                <br>\n",
    "                <li>Append tagging data</li>\n",
    "                <br>\n",
    "                <li>Calculate lifetime value</li>\n",
    "            </ol>\n",
    "        </td>\n",
    "        <td><img src = 'images/process.png' width = '250'></td>\n",
    "    </tr>\n",
    "</table>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "75157714-ad50-40f0-bd0e-38fea9f76c1e",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "\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'>You will be prompted to provide the password. Enter your password, press Enter, then use down arrow to go to next cell.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ba7c1828-741a-4089-a21e-f7ea46a4f577",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "%connect local, hidewarnings=true"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cad4f702-9018-4deb-b5e6-aa4f1d3b0cef",
   "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,
   "id": "d165bf64-906e-46c7-afe0-ac577a19d201",
   "metadata": {},
   "outputs": [],
   "source": [
    "Set query_band='DEMO=CustomerIdentityMatching_SQL.ipynb;' update for session;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cd952154-8e89-473c-a4e1-c15b9a4cc7aa",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "\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.  You have the option of either running the demo using foreign tables to access the data without using any storage on your environment or downloading the data to local storage which may yield somewhat faster execution, but there could be considerations of available storage.  There are two statements in the following cell, and one is commented out.  You may switch which mode you choose by changing the comment string.</p>\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bb54055c-7d11-469d-b589-ae1ad820e14b",
   "metadata": {},
   "outputs": [],
   "source": [
    "--call get_data('DEMO_Customer360_cloud'); ---takes about 10 seconds\n",
    "call get_data('DEMO_Customer360_local'); ---takes about 30 seconds"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b09c43a5-b29e-4ffa-ae85-0fe5b9134300",
   "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,
   "id": "ca177ad2-ac1d-4024-a256-c37f90e24297",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "call space_report();  -- optional, takes about 10 seconds"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "36a5d933",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>3. Analyze Raw Data</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Here, we will inspect each of the four data sets, and determine what fields to use from each table to create a comparison key string.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We will inspect:</p>\n",
    "<ol style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>The equipment distributor's customer table</li>\n",
    "    <li>The electronic monitor service's e-commerce list relative to the customer table</li>\n",
    "    <li>The call center list relative to the customer table</li>\n",
    "    <li>The tagging list relative to the customer table</li>        \n",
    "</ol>\n",
    "    \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Replace the following with a valid connection name:</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8d6c7441",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>3.1- Inspect the Equipment Reseller's Customer Table</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The simple SQL select query below shows that the fields include a customer ID, gender, name, address, and a lifetime value for this channel.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ad8b7760",
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT TOP 5 * FROM DEMO_Customer360.Equipment"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e7e47139",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>3.2- Inspect E-Commerce Table</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>A similar SQL query shows this table has a loyalty number, sex, name, physical address, email address and e-commerce lifetime value. Since both tables' records are at an individual level, we will use first name, last name, and city as a matching key string. Fields to import for now include the loyalty number, and email address.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3498e740",
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT TOP 5 * FROM DEMO_Customer360.Online"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "68c29344",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>3.3- Inspect Call Center List</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Note that this table also has records at an individual level, so we can use the same fields to create a matching key. Unique fields to this table include the phone number and call center cost. After matching, we will import the phone number.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "68cb5898",
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT TOP 5 * FROM DEMO_Customer360.CallCenter"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "45fa0ae6",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>3.4 Inspect Cookie List</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Since cookies are collected at the household level and there are no names provided, we will use address as the matching key string. We will append the cookie and campaign to any matching records.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bc47046f",
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT TOP 5 * FROM DEMO_Customer360.Cookie"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2e715879",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>3.5 Inspect Customer Across Lists</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We will also examine one customer closely to see how his information is matched throughout the lists. This customer is Jessica Rego, and she (or her address) appears in all four tables.  Note that her name is slightly misspelled in the e-commerce (as Jessika), her city is misspelled in call center table (as Nashporte), and her address is misspelled (with no space between 3114 and Park) in the cookie table. </p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ff2d2dc5",
   "metadata": {},
   "outputs": [],
   "source": [
    "-- Find Jessica Rego in in-store table\n",
    "SELECT * FROM DEMO_Customer360.Equipment\n",
    "WHERE FIRSTNAME = 'Jessica' AND LASTNAME = 'Rego'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "89f77f29",
   "metadata": {},
   "outputs": [],
   "source": [
    "-- Find Jessica Rego in online table\n",
    "SELECT * FROM DEMO_Customer360.Online\n",
    "WHERE FNAME LIKE 'Jes%' AND LNAME LIKE 'Reg%'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4b2bf6c3",
   "metadata": {},
   "outputs": [],
   "source": [
    "-- Find Jessica Rego in call center table\n",
    "SELECT * FROM DEMO_Customer360.CallCenter\n",
    "WHERE FIRST_NAME LIKE 'Jes%' AND LAST_NAME LIKE 'Reg%'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1b8603ec",
   "metadata": {},
   "outputs": [],
   "source": [
    "-- Find Jessica Rego's address in tagging table\n",
    "SELECT * FROM DEMO_Customer360.Cookie\n",
    "WHERE ADDR_LN_1_TXT LIKE '3114%Park%'"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "83392067",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>4. Append Online Data to Equipment Table</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>This section will illustrate matching between the equipment and e-commerce tables using the StringSimilarity function built into ClearScape Analytics. The <a href=\"https://docs.teradata.com/r/Database-Analytic-Functions/July-2021/Advanced-SQL-Engine-Analytic-Functions/StringSimilarity-SQL-Engine\">StringSimiliarity documentation</a> contains more information on the function. The StringSimiliarty function supports 10 different ways of comparing two strings. We will use the Jaro similiarity, which accounts for the number of matching characters and transpositions.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Steps for this section include: \n",
    "    <img src = 'images/step4.png' width = '350' align='right' padding='padding:50px'></p>\n",
    "\n",
    "<ol style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "        <li>Exploring which key strings will be matched from a full join</li> \n",
    "        <li>Creating a matching table consisting of: \n",
    "        <ul><li>The key string from the equipment table</li>\n",
    "            <li>The key string from the e-commerce table</li>\n",
    "            <li>The matching score</li>\n",
    "            <li>Any columns from the e-commerce table we want to append</li>\n",
    "        </ul></li>\n",
    "    <li style = 'font-size:16px;font-family:Arial;color:#00233C'>Joining the in-store table with the matching table to form an intermediate golden record</li>\n",
    "    </ol>\n",
    "    "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b7839a39",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>4.1 - Exploring full join key strings</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We will create a key string for each row of the equipment and online tables. The key string will consist of the first name, last name, and city with all spaces removed. Then each key string from the retail table will be compared with each key string in the e-commerce table. The statement below shows the first 20 rows of the comparison. Note that every equipment key is being compared to the first online key.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9287ff8f",
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT TOP 5\n",
    "    e.CUST_ID,\n",
    "    o.LOYALTY_NUM,\n",
    "    REGEXP_REPLACE(OREPLACE((e.GENDER||e.FIRSTNAME||e.LASTNAME||e.CITY),\n",
    "                            ' ',''), \n",
    "                   '[^a-zA-Z\\d:]','',1,0) as EQUIPMENT_KEY,\n",
    "    REGEXP_REPLACE(OREPLACE((o.SEX||o.FNAME||o.LNAME||o.CITY),\n",
    "                            ' ',''), \n",
    "                   '[^a-zA-Z\\d:]','',1,0) as ONLINE_KEY\n",
    "FROM DEMO_Customer360.Equipment e, DEMO_Customer360.Online o;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3eb098b0",
   "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 - Creating a matching table</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We will run the StringSimiliarity function on each row of the table above and only select the rows with a Jaro match of at least a certain threshold. Since the Jaro simliarity score is normalized between 0 and 1, we will choose a threshold of 0.90.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f6a89e43",
   "metadata": {},
   "outputs": [],
   "source": [
    "REPLACE VIEW C360_MatchAB_V AS (\n",
    "    SELECT * FROM StringSimilarity (\n",
    "        ON (\n",
    "            SELECT \n",
    "                e.CUST_ID, \n",
    "                o.LOYALTY_NUM, \n",
    "                CAST(REGEXP_REPLACE(OREPLACE((e.GENDER||e.FIRSTNAME||e.LASTNAME||e.CITY),' ',''), \n",
    "                                             '[^a-zA-Z\\d:]','',1,0) as VARCHAR(50)) \n",
    "                                             as EQUIPMENT_KEY,\n",
    "                CAST(REGEXP_REPLACE(OREPLACE((o.SEX||o.FNAME||o.LNAME||o.CITY),' ',''), \n",
    "                                             '[^a-zA-Z\\d:]','',1,0) as VARCHAR(50)) \n",
    "                                             as ONLINE_KEY,\n",
    "                o.email\n",
    "            FROM DEMO_Customer360.Equipment e, DEMO_Customer360.Online o\n",
    "        ) PARTITION BY ANY\n",
    "        USING\n",
    "        ComparisonColumnPairs(\n",
    "            'jaro (EQUIPMENT_KEY, ONLINE_KEY) AS jaro'\n",
    "        )\n",
    "        CaseSensitive('false')\n",
    "        Accumulate('CUST_ID', 'LOYALTY_NUM', 'EQUIPMENT_KEY', 'ONLINE_KEY', 'EMAIL')\n",
    "    ) AS dt \n",
    "WHERE jaro > .90)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "78e27c66",
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT Top 20 * FROM C360_MatchAB_V ORDER BY jaro DESC;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7d52a784",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We see that the first 14 rows consist of exact matches. But starting with the 15th row, the Jaro similarity score is less than 1, indicating there are some discrepancies. For example, taking a look at Jessica Rego, we see that her first name is spelled as Jessika in the online key field:</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a4f56adf",
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT * FROM C360_MatchAB_V WHERE EQUIPMENT_KEY LIKE '%JessicaRego%'"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e5f68727",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We find that out of the 100 records in the retail table and 70 in the e-commerce table, 67 were matched.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6fd28b5e",
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT COUNT(*) FROM DEMO_Customer360.Equipment"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bee28711",
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT COUNT(*) FROM DEMO_Customer360.Online"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7853c0b9",
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT COUNT(*) FROM C360_MatchAB_V"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3c2b44a4",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>4.3 - Join tables to create intermediate golden record</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Using the CUST_ID and LOYALTY_NUM fields from the above match table, we create an interim golden record from merged retail and e-commerce tables.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7b8f7fcf",
   "metadata": {},
   "outputs": [],
   "source": [
    "REPLACE VIEW C360_gr1_V AS (\n",
    "    SELECT e.CUST_ID, mab.LOYALTY_NUM, e.GENDER, \n",
    "           e.FIRSTNAME, e.LASTNAME, \n",
    "           e.ADDRESS, e.STATE, e.CITY, e.ZIP, \n",
    "           mab.EMAIL \n",
    "    FROM DEMO_Customer360.Equipment e LEFT JOIN C360_MatchAB_V mab\n",
    "    ON e.CUST_ID = mab.CUST_ID\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b48024a4",
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT Top 10 * FROM C360_gr1_V;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ec17fb6b-ee8f-4477-b83f-ff3b2f8f6649",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We see Jessica's record has been updated with an email address.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "43e859b1",
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT * FROM C360_gr1_V WHERE FIRSTNAME = 'Jessica' AND LASTNAME = 'Rego'"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e8ac4d9b",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>5. Append Call Center Data</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We repeat the process, this time appending the call center data.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Steps for this section include:\n",
    "    <img src = 'images/step5.png' width = '300' align='right' padding='padding:50px'></p>\n",
    "\n",
    "<ol style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Creating a matching table consisting of:\n",
    "        <ul><li>The key string from the intermediate golden record table</li>\n",
    "            <li>The key string from the call center table</li>\n",
    "            <li>The matching score</li>\n",
    "            <li>Any columns from the call center table we want to append</li>\n",
    "        </ul></li>\n",
    "    <li style = 'font-size:16px;font-family:Arial;color:#00233C'>Joining the intermediate golden record table with the matching table to form a new intermediate golden record</li>\n",
    "    </ol>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cb4a8c33",
   "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 - Creating a matching table</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>As in the previous section, we will create a view consisting of rows with a Jaro match greater than 0.9.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e0ff74b2",
   "metadata": {},
   "outputs": [],
   "source": [
    "REPLACE VIEW C360_MatchABC_V AS (\n",
    "    SELECT * FROM StringSimilarity (\n",
    "        ON (\n",
    "            SELECT \n",
    "                gr1.CUST_ID, \n",
    "                CAST(REGEXP_REPLACE(OREPLACE((gr1.FIRSTNAME||gr1.LASTNAME||gr1.CITY),' ',''), \n",
    "                                             '[^a-zA-Z\\d:]','',1,0) as VARCHAR(50)) \n",
    "                                             as GR1_KEY,\n",
    "                CAST(REGEXP_REPLACE(OREPLACE((cc.FIRST_NAME||cc.LAST_NAME||cc.CITY),' ',''), \n",
    "                                             '[^a-zA-Z\\d:]','',1,0) as VARCHAR(50)) \n",
    "                                             as CALLCENTER_KEY,\n",
    "                cc.PHONE                               \n",
    "            FROM C360_gr1_V gr1, DEMO_Customer360.CallCenter cc\n",
    "        ) PARTITION BY ANY\n",
    "        USING\n",
    "        ComparisonColumnPairs(\n",
    "            'jaro (GR1_KEY, CALLCENTER_KEY) AS jaro'\n",
    "        )\n",
    "        CaseSensitive('false')\n",
    "        Accumulate('CUST_ID', 'GR1_KEY', 'CALLCENTER_KEY', 'PHONE'/*, 'CALL_CENTER_COST'*/)\n",
    "    ) AS dt \n",
    "WHERE jaro > .90)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2b44dcd5",
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT * FROM C360_MatchABC_V ORDER BY jaro DESC"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a62d14d7",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We find that out of the 30 in the call center table, 27 were matched.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "af3d06cd",
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT COUNT(*) FROM DEMO_Customer360.CALLCENTER"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "40306d53",
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT COUNT(*) FROM C360_MatchABC_V"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f1e41204-81c7-4031-bc66-3dfba85c535e",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We see that Jessica Nashport was matched between the two lists even though her last name was misspelled in the Call Center list. The matching score was 0.983.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1decd9ad",
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT * FROM C360_MatchABC_V WHERE GR1_KEY LIKE '%JessicaRego%'"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "55f0dea9",
   "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 - Join tables to create intermediate golden record</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Using the CUST_ID, we create another interim golden record including data from the call center table.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "93fdf40e",
   "metadata": {},
   "outputs": [],
   "source": [
    "REPLACE VIEW C360_GR2_V AS (\n",
    "    SELECT gr.CUST_ID, gr.LOYALTY_NUM, gr.GENDER, \n",
    "           gr.FIRSTNAME, gr.LASTNAME, \n",
    "           gr.ADDRESS, gr.STATE, gr.CITY, gr.ZIP, \n",
    "           --gr.EQUIP_LTV, gr.ONLINE_LTV, \n",
    "           --COALESCE(mabc.CALL_CENTER_COST, 0) as CALL_CENTER_COST, \n",
    "            gr.EMAIL,\n",
    "            mabc.PHONE\n",
    "    FROM C360_GR1_V gr LEFT JOIN C360_MatchABC_V mabc\n",
    "    ON gr.CUST_ID = mabc.CUST_ID\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6d2e914f",
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT TOP 10 * FROM C360_GR2_V;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0aa2668f-f37a-44d6-9404-6f873a902539",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We see Jessica's record has been updated with an email address (from the last join) and a phone number (from this join).</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2e7a6bfd",
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT * FROM C360_GR2_V WHERE FIRSTNAME = 'Jessica' AND LASTNAME = 'Rego'"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e2fd136a",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>6. Append Cookie Data</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We repeat the process, this time appending the cookie data. Since cookies are collected at the household level, we will use address as the matching string.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Steps for this section include:\n",
    "<img src = 'images/step6.png' width = '300' align='right' padding='padding:50px'></p>\n",
    "\n",
    "<ol style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Creating a matching table\n",
    "    <li>Joining the intermediate golden record table with the matching table to form a new intermediate golden record</li>\n",
    "    </ol>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "72035eda",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>6.1 - Creating a matching table</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>As in the previous section, we will create a view consisting of rows with a Jaro match greater than 0.9.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f834fc3f",
   "metadata": {},
   "outputs": [],
   "source": [
    "REPLACE VIEW C360_MatchABCD_V AS (\n",
    "    SELECT * FROM StringSimilarity (\n",
    "        ON (\n",
    "            SELECT \n",
    "                gr2.CUST_ID, \n",
    "                gr2.EMAIL,\n",
    "                gr2.PHONE,\n",
    "                CAST(REGEXP_REPLACE(OREPLACE((gr2.ADDRESS||gr2.CITY),' ',''), \n",
    "                                             '[^a-zA-Z\\d:]','',1,0) as VARCHAR(50)) \n",
    "                                             as GR2_KEY,\n",
    "                CAST(REGEXP_REPLACE(OREPLACE((t.ADDR_LN_1_TXT||t.CITY_NAME),' ',''), \n",
    "                                             '[^a-zA-Z\\d:]','',1,0) as VARCHAR(50)) \n",
    "                                             as TAGGING_KEY,\n",
    "                t.COOKIE,\n",
    "                t.CAMPAIGN\n",
    "            FROM C360_GR2_V gr2, DEMO_Customer360.Cookie t\n",
    "        ) PARTITION BY ANY\n",
    "        USING\n",
    "        ComparisonColumnPairs(\n",
    "            'jaro (GR2_KEY, TAGGING_KEY) AS jaro'\n",
    "        )\n",
    "        CaseSensitive('false')\n",
    "        Accumulate('CUST_ID','EMAIL','PHONE', 'GR2_KEY', 'TAGGING_KEY', 'COOKIE','CAMPAIGN')\n",
    "    ) AS dt \n",
    "WHERE jaro > .90)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2f342ac1",
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT * FROM C360_MatchABCD_V ORDER BY jaro DESC"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "79e6a947",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We find that out of the 45 in the cookie table, 44 were matched.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1aa7465b",
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT COUNT(*) FROM DEMO_Customer360.Cookie"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c674ee59",
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT COUNT(*) FROM C360_MatchABCD_V"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5cc1624e-cf4d-4162-9477-797c37a73571",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We see that Jessica's physical address was matched between the two lists.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "352b3646-d2a7-4d10-8306-2f47e3ebc1fa",
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT * FROM C360_MatchABCD_V WHERE GR2_KEY LIKE '%3114Park%'"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "40e9daa6",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>6.2 - Join tables to create intermediate golden record</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Using the CUST_ID, we create another interim golden record from merged intermediate golden record and cookie tables.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "abadbdae",
   "metadata": {},
   "outputs": [],
   "source": [
    "REPLACE VIEW C360_GR3_V AS (\n",
    "    SELECT gr2.CUST_ID, gr2.LOYALTY_NUM, gr2.GENDER, \n",
    "           gr2.FIRSTNAME, gr2.LASTNAME, \n",
    "           gr2.ADDRESS, gr2.STATE, gr2.CITY, gr2.ZIP, gr2.EMAIL,gr2.PHONE,\n",
    "           mabcd.COOKIE,\n",
    "           mabcd.CAMPAIGN \n",
    "    FROM C360_GR2_V gr2 LEFT JOIN C360_MatchABCD_V mabcd\n",
    "    ON gr2.CUST_ID = mabcd.CUST_ID\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "48c33389",
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT TOP 5 * FROM C360_GR3_V"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "24a3e354-c321-404d-83e7-f44b36085fe3",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We see that Jessica's record has been updated with a cookie and a campaign.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7585ba3d",
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT * FROM C360_GR3_V WHERE FIRSTNAME = 'Jessica' AND LASTNAME = 'Rego'"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fc37c299",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>7. Calculate Lifetime Value Across All Channels</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Finally, we calculate the lifetime value by taking into account the channel lifetime value from equipment and online as well as the cost to service a customer from the call center using the formula:<br>\n",
    "&ensp; LTV = Equipment Purchases + Online Services Sales - Call Center Cost<br>\n",
    "    Since this is the final calculation, we will write out our results to a table rather than a view.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b959ab86",
   "metadata": {},
   "outputs": [],
   "source": [
    "CREATE TABLE C360_GoldenRecord AS (\n",
    "    SELECT gr3.CUST_ID, gr3.LOYALTY_NUM, gr3.GENDER, \n",
    "           gr3.FIRSTNAME, gr3.LASTNAME, gr3.EMAIL,gr3.PHONE,\n",
    "           gr3.ADDRESS, gr3.STATE, gr3.CITY, gr3.ZIP, \n",
    "           COALESCE(e.EQUIP_LTV,0) as EQUIPLTV , COALESCE(o.ONLINE_LTV,0) as ONLINELTV, COALESCE(c.CALL_CENTER_COST,0) as CALLCENTER_COST,\n",
    "            gr3.COOKIE, gr3.CAMPAIGN,\n",
    "           EQUIPLTV + ONLINELTV - CALLCENTER_COST as LTV\n",
    "    FROM C360_GR3_V gr3 LEFT JOIN DEMO_Customer360.Equipment e ON gr3.CUST_ID = e.CUST_ID\n",
    "    LEFT JOIN DEMO_Customer360.Online o ON gr3.EMAIL = o.EMAIL\n",
    "    LEFT JOIN DEMO_Customer360.CallCenter c ON gr3.PHONE= c.PHONE\n",
    "    \n",
    "    )\n",
    "WITH DATA;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5b960cd7",
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT TOP 5 * FROM C360_GoldenRecord"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e3359662-3469-45c2-9759-270b48f7809d",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Focusing in on Jessica, we see her lifetime value across all channels.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "adf5018b",
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT * FROM C360_GoldenRecord WHERE FIRSTNAME = 'Jessica' AND LASTNAME = 'Rego'"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d99d434c-2148-44ed-850c-665c6d449c93",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>8. Conclusion</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Thus by combinig the data from Multiple channels we are able to create a golden record for each customers and calculate the value based on the various channels used. This kind of combined data can be used for various kind of analysis including the one shown in this demo which calculates the life time value for each customers based on the various channels used by the customer and the cost involved.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b3f6ac2a-e8a0-4de1-98a1-1b91a8c7f085",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>9. 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'>Clean up the work tables to prevent error next time.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3525bac0-8856-4cc2-9f3a-6eb0ba1ffdc1",
   "metadata": {},
   "outputs": [],
   "source": [
    "DROP VIEW C360_MatchAB_V;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "eee1fb61-71cc-448d-93cd-cd45f926dc72",
   "metadata": {},
   "outputs": [],
   "source": [
    "DROP VIEW C360_gr1_V; "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a1e1a9d1-20a7-44e0-9db8-53f42b501e0e",
   "metadata": {},
   "outputs": [],
   "source": [
    "DROP VIEW C360_MatchABC_V;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f506f93e-3619-4eb4-baa5-62a99983c371",
   "metadata": {},
   "outputs": [],
   "source": [
    "DROP VIEW C360_MatchABCD_V; "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5f30b3c1-0b3e-4258-97a3-ec6f16725ba4",
   "metadata": {},
   "outputs": [],
   "source": [
    "DROP VIEW C360_GR3_V; "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "38748366-dc8d-43e3-a07f-dbead5902c85",
   "metadata": {},
   "outputs": [],
   "source": [
    "DROP TABLE C360_GoldenRecord;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3b466641-72cb-4b97-b1a8-ac73bb53aa6f",
   "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'>The following code will clean up tables and databases created above.</p>\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cf225777-6ea0-482e-858b-bc30068b02d5",
   "metadata": {},
   "outputs": [],
   "source": [
    "call remove_data('DEMO_Customer360'); "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "138065f0-9519-413c-a510-81caff85b45a",
   "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"
  },
  "toc-showmarkdowntxt": true,
  "widgets": {
   "application/vnd.jupyter.widget-state+json": {
    "state": {},
    "version_major": 2,
    "version_minor": 0
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}