UseCases/Consumer_Complaints/Consumer_Complaints_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",
    "       Time Series Analysis - Analyzing Consumer Complaints Over Time\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",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "In this example we will be analyzing the number of complaints over time received by the Consumer Financial Protection Bureau (CFPB).</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "How can we use Vantage to extract insights and tell a story behind a dataset? In this use case, you will see how powerful and simple it is to extract answers from a public dataset available through <a href=\"http://data.gov\">Data.gov</a>. We use SQL and a visualization tool to analyze the number of complaints over time to answer the following questions:</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    " <i>What are the trends of complaints over time? How can we interpret the outliers in the dataset?</i>\n",
    "</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>    \n",
    "By answering questions like the ones above, we gain a deeper understanding of the dataset, and we can explain in plain language how the number of complaints evolve over time. In the Explore section, we focus on analyzing the number of complaints over time and identifying trends and outliers in the time series to answer the questions above.\n",
    "</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>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<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,
   "metadata": {
    "vscode": {
     "languageId": "teradata sql"
    }
   },
   "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>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "teradata sql"
    }
   },
   "outputs": [],
   "source": [
    "Set query_band='DEMO=TimeSeriesAnalysis.ipynb;' update for session;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>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. In this demo since we are using Temporal table we will be creating databases and tables in local storage and use them in the notebook. Please execute the procedure in the next cell.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "teradata sql"
    }
   },
   "outputs": [],
   "source": [
    "call get_data('DEMO_Financial_cloud');    -- takes about 50 seconds, estimated space: 0 MB\n",
    "--call get_data('DEMO_Financial_local');     -- takes about 4 minutes, estimated space: 300 MB"
   ]
  },
  {
   "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>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "teradata sql"
    }
   },
   "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>2. Querying the Data</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We have made our connection to the Vantage system, now let's start exploring the data. We'll start by counting the number of rows in the table.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "teradata sql"
    }
   },
   "outputs": [],
   "source": [
    "select count(*) from DEMO_Financial.Consumer_complaints;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "There are just under 1.3 million rows. Not a problem to analyze large datasets using Vantage, lets take a look at a sample of the data.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "teradata sql"
    }
   },
   "outputs": [],
   "source": [
    "select TOP 5 * from DEMO_Financial.Consumer_Complaints;"
   ]
  },
  {
   "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. Visualizing the Data</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "From the query above, we notice that this dataset has a lot of information. To derive some insights, we need to start grouping the data.</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "The first column is <b>date_received</b>. This is the date the complaints were received, and it means that we can look at a time series plot of the data. Let's start by grouping the counts of <b>complaint_id</b> over time, using <b>date_received</b> as our time axis.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "teradata sql"
    }
   },
   "outputs": [],
   "source": [
    "select date_received, count(complaint_id) as counts\n",
    "from DEMO_Financial.Consumer_Complaints\n",
    "group by date_received;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "This is great; we now have the number of complaints (<b>counts</b>) by time (<b>date_received</b>), but how do we make sense of this data? Let's plot this time series on a graph.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "teradata sql"
    }
   },
   "outputs": [],
   "source": [
    "%chart date_received, counts, title='Number of Complaints over Time', width=900, height=400"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "By visualizing the data above, we can see that the number of complaints varies a lot over time and there also seem to be more complaints as time progresses. There are also some unusual spikes in 2017. Let's understand more about our data. We start by looking at the general trend.\n",
    "<br>\n",
    "<br>\n",
    "Let's group the data by month and re-plot the graph above.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "teradata sql"
    }
   },
   "outputs": [],
   "source": [
    "select extract(year from date_received) || extract(month from date_received) as month_date, count(complaint_id) as counts\n",
    "from DEMO_Financial.Consumer_Complaints\n",
    "group by month_date\n",
    "order by month_date;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "teradata sql"
    }
   },
   "outputs": [],
   "source": [
    "%chart month_date, counts, title='Number of Complaints by Month and Year', width=900, height=400, mark=line"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Looking at complaints over month and year, we see there is clearly an upward trend. One hypothesis is that as time progresses, people get more conscious and spread the word. The media can also advertise the complaint channels over time. Through this chart we can see clearly the spikes that we saw above were in January 2017 and September 2017. Let's dive deeper into these dates and draw some insights on the next step.</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. Extracting Insights from the Data</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "Let's narrow down the two spikes above and see exactly where they are happening. We can do this by plotting another time series plot, this time only in 2017.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "teradata sql"
    }
   },
   "outputs": [],
   "source": [
    "select date_received, count(complaint_id) as counts\n",
    "from DEMO_Financial.Consumer_Complaints\n",
    "where year(date_received) = 2017\n",
    "group by date_received\n",
    "order by date_received;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "teradata sql"
    }
   },
   "outputs": [],
   "source": [
    "%chart date_received, counts, title='Complaints over time - 2017', width=900, height=400, mark=line"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>As we look at the peaks, we find that they occurred from January 15th to 21st and during the first week of September. To find the actual dates of the peaks, we can limit the query to pick up at least 1,500 complaints a day.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "teradata sql"
    }
   },
   "outputs": [],
   "source": [
    "select date_received,\n",
    "    month(date_received) as month_date,\n",
    "    count(complaint_id) as counts\n",
    "from DEMO_Financial.Consumer_Complaints\n",
    "where year(date_received) = 2017 and month_date in (1, 9)\n",
    "group by date_received\n",
    "having counts >= 1500\n",
    "order by month_date, counts desc;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Let's look at some of the issues that were reported during these dates.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "teradata sql"
    }
   },
   "outputs": [],
   "source": [
    "select date_received, company, count(company) as counts\n",
    "from DEMO_Financial.Consumer_Complaints\n",
    "where date_received in (\n",
    "    date '2017-01-19',\n",
    "    date '2017-01-20',\n",
    "    date '2017-09-08',\n",
    "    date '2017-09-09',\n",
    "    date '2017-09-13'\n",
    ")\n",
    "group by date_received, company\n",
    "having counts > 500\n",
    "order by date_received, counts desc;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Interestingly, we can see that the great majority of the the complaints were directed at two companies: Navient Solutions and EQUIFAX. These seem to be highly correlated with the Navient Lawsuit and the Equifax breach events that happened around those dates, respectively. Let's recap what happened:</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "<blockquote><i>Navient Lawsuit: On January 2017, the U.S. Consumer Financial Protection Bureau (CFPB) and the Illinois and Washington attorneys general sued Navient Solutions. Navient is a major servicer of private and federal student loans. According to the CFPB at least since January 2010 \"Navient has misallocated payments, steered struggling borrowers toward multiple forbearances instead of income-driven repayment plans, and provided unclear information about how to re-enrol in income-driven repayment plans and how to qualify for a co-signer release\"\n",
    "\n",
    "Equifax Breach: On September 7th, 2017 Equifax announced a cybersecurity breach, one of the largest in history, had happened from mid-May through July 2017. Some of the personal information that was accessed included names, social security numbers, birth dates, addresses and driver's license numbers.</i></blockquote>\n",
    "</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "Let's now look at the top issues for Navient Solutions and Equifax during those periods to confirm our hypothesis.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "teradata sql"
    }
   },
   "outputs": [],
   "source": [
    "-- analyze top issues reported against Navient Solutions on 2017-01-19 and 2017-01-20\n",
    "select company, product, issue, count(issue) as counts\n",
    "from DEMO_Financial.Consumer_Complaints\n",
    "where date_received in (\n",
    "    date '2017-01-19',\n",
    "    date '2017-01-20') and\n",
    "    company like 'Navient Solutions%'\n",
    "group by company, product, issue\n",
    "order by counts desc;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "We can see the top two issues represent the majority of complaint counts against Navient Solutions. Furthermore, by looking at the product and issue columns we can infer that they are indeed related to the lawsuit regarding student loans. Now let's do the same analysis for the Equifax issues.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "teradata sql"
    }
   },
   "outputs": [],
   "source": [
    "-- analyze top issues reported against EQUIFAX on 2017-01-19 and 2017-01-20\n",
    "select\n",
    "    company,\n",
    "    product,\n",
    "    issue,\n",
    "    count(issue) as counts\n",
    "from DEMO_Financial.Consumer_Complaints\n",
    "where date_received in (\n",
    "    date '2017-09-08',\n",
    "    date '2017-09-09',\n",
    "    date '2017-09-13') and\n",
    "        company like 'EQUIFAX%'\n",
    "group by company, product, issue\n",
    "order by counts desc;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Here we can also confirm our hypothesis. The top issues talk about improper use of the credit report, fraud alerts, identity theft etc. This really does seem related to the Equifax breach that happened around the same time frame.</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. Cleanup </b></p>\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>Database 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>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "teradata sql"
    }
   },
   "outputs": [],
   "source": [
    "call remove_data('DEMO_Financial');\n",
    "-- takes about 10 seconds, optional if you want to use the data later\n",
    "--the same data is used in UseCases/VantageAnalyticLibrary and UseCases/FSCustomerJourney"
   ]
  },
  {
   "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. Dataset</b>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "The Consumer Complaints Database has complaints data that was received by the Consumer Financial Protection Bureau (CFPB) on financial products and services, which include but are not limited to bank accounts, credit cards, credit reporting, debt collection, money transfers, mortgages, student loans and other types of consumer credit. The dataset is refreshed daily and contains information on the provider, the complaint, date, ZIP code and more. More information about the dataset can be found in the Consumer section of the <a href=\"data.gov\">Data.gov</a> website.</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "The <b>Demo_Financial.consumer_complaints</b> dataset has 1,273,782 rows, each representing a unique consumer complaint, and 18 columns, representing the following features:</p>\n",
    "\n",
    "- `date_received`: date that CFPB received the complaint\n",
    "- `product`: type of product the consumer identified in the complaint\n",
    "- `sub_product`: type of sub-product the consumer identified in the complaint\n",
    "- `issue`: issue the consumer identified in the complaint\n",
    "- `sub_issue`: sub-issue the consumer identified in the complaint\n",
    "- `consumer_complaint_narrative`: consumer-submitted description of \"what happened\" from the complaint\n",
    "- `company_public_response`: company's optional, public-facing response to a consumer's complaint\n",
    "- `company`: complaint is about this company\n",
    "- `state`: state of the mailing address provided by the consumer\n",
    "- `zip_code`: mailing ZIP code provided by the consumer\n",
    "- `tags`: data that supports easier searching and sorting of complaints submitted by or on behalf of consumers\n",
    "- `consumer_consent_provided`: identifies whether the consumer option in to publish their complaint narrative\n",
    "- `submitted_via`: how the complaint was submitted to the CFPB\n",
    "- `date_sent_to_company`: date the CFBP sent the complaint to the company\n",
    "- `company_response_to_consumer`: how the company responded\n",
    "- `timely_response`: whether the company gave a timely response\n",
    "- `consumer_disputed`: whether the company disputed the company's response\n",
    "- `complaint_id`: unique identification number for a complaint"
   ]
  },
  {
   "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. Explore</b>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>   \n",
    "Through this notebook, we saw the power and simplicity of running queries in the SQL Editor and how it can be leveraged to extract insights from the data to tell the story behind a dataset. Hopefully you've noticed how easy it is to use Vantage to write your own SQL queries.<br>You can continue to explore Vantage to extract more insights and find answers to other questions by using the preloaded dataset. Here are some suggestions:</p>\n",
    "<ul style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>What are the most common types of complaints? By grouping the <b>product</b> category, we can arrive at this answer. How does this change over time?</li>\n",
    "    <li>How are customers submitting their complaints? The column <b>submitted_via</b> can also be grouped to answer for this question.</li>\n",
    "    <li>What proportion of the customer complaints are disputed? By aggregating counts of <b>customer_disputed</b> we can answer this question.</li>\n",
    "    <li>Is there seasonality in the data? What is the reason for the seasonality? If we subtract the trend from the series we can analyze the seasonality in the dataset. Are most of the complaints filed during the week or on the weekends?</li>\n",
    "</ul>"
   ]
  },
  {
   "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
}