UseCases/Complaints_Analysis_GenAI_Gemini/Complaint_Summarization.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "1f44f3bc-51cc-47e3-a033-f2883ed97408",
   "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",
    "       Complaints Summarization Using Vantage and Google Gemini\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",
   "id": "62ff194b-33c1-4c2f-9c5f-042cb29e3dc6",
   "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 this demo we'll deep dive on Complaints Summarization using <b>Teradata Vantage</b> and <b>Google Gemini</b> model. This cutting-edge solution empowers organizations to efficiently manage and analyze customer complaints, providing actionable insights to enhance customer satisfaction and improve business operations.</p> \n",
    "\n",
    "<p style='font-size:16px;font-family:Arial;color:#00233C'><b>Key Features:</b></p> \n",
    "\n",
    "<ol style='font-size:16px;font-family:Arial;color:#00233C'>\n",
    "  <li><b>AI-Powered Summarization</b>: Utilizing advanced natural language processing (NLP) and machine learning algorithms, the system automatically summarizes complaints, identifying key issues, sentiment, and root causes.</li>\n",
    "  <li><b>Real-Time Analytics</b>: The platform provides real-time analytics and visualization tools, enabling users to track complaint trends, sentiment analysis, and issue resolution rates.</li>\n",
    "  <li><b>Customizable Dashboards</b>: Users can create personalized dashboards to monitor specific complaint categories, product lines, or geographic regions, ensuring targeted insights and swift action.</li>\n",
    "    <li><b>Integration with Google Gemini</b>: Seamless integration with <b>Teradata Vantage</b> and <b>Google Gemini</b> models enables users to leverage the power of cloud-based infrastructure and advanced analytics capabilities.</li>\n",
    "</ol>\n",
    "\n",
    "<p style='font-size:16px;font-family:Arial;color:#00233C'><b>Benefits:</b></p> \n",
    "<ol style='font-size:16px;font-family:Arial;color:#00233C'>\n",
    "  <li><b>Enhanced Customer Experience</b>: By quickly identifying and addressing customer concerns, organizations can improve customer satisfaction and loyalty.</li>\n",
    "  <li><b>Operational Efficiency</b>: Automated complaint summarization and analytics reduce manual processing time, allowing teams to focus on issue resolution and strategic decision-making.</li>\n",
    "  <li><b>Data-Driven Decision-Making</b>: The platform provides actionable insights, enabling organizations to make informed decisions and drive business growth.</li>\n",
    "</ol>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233c'><b>Steps in the analysis:</b></p>\n",
    "<ol style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "     <li>Configuring the environment</li>\n",
    "    <li>Connect to Vantage</li>\n",
    "    <li>Setup API key for Google Gemini</li>\n",
    "    <li>Complaints Summarization</li>\n",
    "    <li>Cleanup</li>\n",
    "</ol>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "439c593b-d12a-4edf-8b5f-6070f1122914",
   "metadata": {},
   "source": [
    "<hr style='height:2px;border:none;background-color:#00233C;'>\n",
    "<b style = 'font-size:20px;font-family:Arial;color:#00233c'>1. Configuring the environment</b>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b>1.1 Install the required libraries</b></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ebc0a97b-78f8-4274-9844-d3b1d31848e6",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%capture\n",
    "\n",
    "!pip install -r requirements.txt --quiet"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "54b6bcb4-ea98-42f9-9088-b15b4ddd03bc",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-block alert-info\">\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Note: </b><i>Please restart the kernel after executing these two lines. The simplest way to restart the Kernel is by typing zero zero: <b> 0 0</b></i></p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7e7e5046-3c5f-4f6d-aeaf-60028655ff13",
   "metadata": {},
   "source": [
    "<hr style='height:1px;border:none;background-color:#00233C;'>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b>1.2 Import the required libraries</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Here, we import the required libraries, set environment variables and environment paths (if required).</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "83dc0922-f932-4378-a505-3bb2d1f1243b",
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import timeit\n",
    "from tqdm import tqdm\n",
    "from teradataml import *\n",
    "import plotly.express as px\n",
    "\n",
    "# GenAI libs\n",
    "import google.generativeai as genai\n",
    "from google.generativeai import protos\n",
    "\n",
    "display.max_rows = 5\n",
    "pd.set_option('display.max_colwidth', 50)\n",
    "pd.set_option('display.max_rows', 5)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "768bf2ed-ae11-4969-b20a-88496e4a2b67",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<b style = 'font-size:20px;font-family:Arial;color:#00233C'>2. Connect to Vantage</b>\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b>2.1 Connect to Vantage</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We will be prompted to provide the password. We will enter the password, press the Enter key, and then use the down arrow to go to the next cell.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "454a2e81-c377-4058-9e68-78abd801ad9c",
   "metadata": {},
   "outputs": [],
   "source": [
    "%run -i ../startup.ipynb\n",
    "eng = create_context(host = 'host.docker.internal', username='demo_user', password = password)\n",
    "print(eng)\n",
    "execute_sql('''SET query_band='DEMO=Complaint_Summarization.ipynb;' UPDATE FOR SESSION;''')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "aed444a1-f0de-4bff-b0b9-d2c4e92573f5",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Begin running steps with Shift + Enter keys. </p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "782ff009-1182-45c0-aa75-2957518ae7c2",
   "metadata": {},
   "source": [
    "<hr style='height:1px;border:none;background-color:#00233C;'>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b>2.2 Getting Data for This Demo</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We have provided data for this demo on cloud storage. We have the option of either running the demo using foreign tables to access the data without using any storage on our environment or downloading the data to local storage, which may yield somewhat faster execution. However, we need to consider available storage. There are two statements in the following cell, and one is commented out. We may switch which mode we choose by changing the comment string.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "65bf814d-4783-4213-936f-886667adf4a7",
   "metadata": {},
   "outputs": [],
   "source": [
    "%run -i ../run_procedure.py \"call get_data('DEMO_ComplaintAnalysis_cloud');\"        # Takes 1 minute\n",
    "# %run -i ../run_procedure.py \"call get_data('DEMO_ComplaintAnalysis_local');\"        # Takes 2 minutes"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e1b19ab4-254a-4322-8b40-008fb7162e8c",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<b style = 'font-size:20px;font-family:Arial;color:#00233C'>3. Setup API key for Google Gemini</b>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Please enter the Google API Key, if you don't have one, please get it from <a href = 'https://ai.google.dev/gemini-api/docs/api-key'>here</a></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "49e92709-9a74-4372-a57a-7b078fe2ae2c",
   "metadata": {},
   "outputs": [],
   "source": [
    "GOOGLE_API_KEY = getpass.getpass(prompt = 'Please enter GOOGLE_API_KEY: ')\n",
    "genai.configure(api_key = GOOGLE_API_KEY)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "24ba7963-2d4c-4b31-8934-00dffbd6849d",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<b style = 'font-size:18px;font-family:Arial;color:#00233C'>3.1. Define the Gemini model and Prompt</b>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The following section defines the type of Gemini model used. Here we use <b>gemini-1.5-pro-latest</b></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ecada13c-1aac-45c1-8bf0-6db4be2eec82",
   "metadata": {},
   "outputs": [],
   "source": [
    "from google.generativeai.types import HarmCategory, HarmBlockThreshold\n",
    "\n",
    "model = genai.GenerativeModel(\n",
    "    model_name = \"models/gemini-1.5-pro-latest\"\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "596cfbe5-e6d7-4b14-9d46-08929397e1a3",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<b style = 'font-size:20px;font-family:Arial;color:#00233C'>4. Complaints summarization</b>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Complaints summarization with Language Model (LLM) models involves condensing lengthy complaints into concise, informative summaries. By leveraging advanced natural language processing techniques, LLMs efficiently extract key issues, sentiments, and resolutions, aiding in quicker understanding and response to customer grievances.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Streamlining the complaint summarization process, Language Model (LLM) models efficiently distill verbose grievances into concise, yet informative summaries. These summaries meticulously capture crucial elements including primary issues, prevalent sentiments, and possible resolutions. Harnessing advanced natural language processing capabilities, LLMs accelerate both comprehension and response to customer concerns, thereby elevating operational efficiency and bolstering overall customer satisfaction.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "30ac5e3a-9264-46f5-94eb-76e813503ac6",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = DataFrame(in_schema('DEMO_ComplaintAnalysis', 'Consumer_Complaints')).to_pandas(num_rows = 20)\n",
    "df['Summary'] = \"\"\n",
    "df['Reasoning with Chain of Thought'] = \"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0174db51-edcc-42d2-b251-c3171648a0e2",
   "metadata": {},
   "outputs": [],
   "source": [
    "for i in tqdm(range(len(df))):\n",
    "    try:\n",
    "        prompt = f'''\n",
    "            The following is text from a Bank Review:\n",
    "\n",
    "            “{df['consumer_complaint_narrative'][i]}”\n",
    "\n",
    "            Give me reasoning as well as summary for this review.\n",
    "\n",
    "            Instructions for Reasoning:\n",
    "            - Give me Reasoning in short\n",
    "            - Only one sentence reasoning\n",
    "            Instructions for Summary:\n",
    "            - A short one sentence Summary of everything the review states.\n",
    "\n",
    "            My output comes in the format:\n",
    "            Summary: ,\n",
    "            Reasoning: ,\n",
    "        '''\n",
    "\n",
    "        output = model.generate_content([prompt])\n",
    "        output = output.candidates[0].content.parts[0].text\n",
    "\n",
    "        summary = re.search('Summary:(.*)', output).group(1)\n",
    "        if summary == \"\":\n",
    "            summary = re.search('Summary:\\n(.*)', output).group(1)\n",
    "\n",
    "        reasoning = re.search('Reasoning:(.*)', output).group(1)\n",
    "        if reasoning == \"\":\n",
    "            reasoning = re.search('Reasoning:\\n(.*)', output).group(1)\n",
    "\n",
    "        df['Summary'][i] = summary\n",
    "        df['Reasoning with Chain of Thought'][i] = reasoning\n",
    "    except:\n",
    "        pass"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e5977498-de10-4392-888d-5541dae6801f",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['Summary'] = df['Summary'].apply(lambda x: x.strip())\n",
    "df['Reasoning with Chain of Thought'] = df['Reasoning with Chain of Thought'].apply(lambda x: x.strip())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "23626789-0317-4edb-856c-cb5ab05cb1fb",
   "metadata": {},
   "outputs": [],
   "source": [
    "df[['complaint_id', 'consumer_complaint_narrative', 'Summary', 'Reasoning with Chain of Thought']]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0d8ace4c-b948-413a-bdf7-53fa20088326",
   "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 Graph for Complaint and Summary Lengths</b></p><p style = 'font-size:16px;font-family:Arial;color:#00233c'>A graph illustrating the Narrative length vs summary length. On the x-axis, you'd have \"Narrative length\" ranging from short to long complaints or narratives. On the y-axis, you'd have \"Summary length\" ranging from brief to detailed summaries. As narrative length increases, summary length would generally decrease, indicating the summarization process effectively condenses longer narratives into shorter summaries. This relationship would likely follow a downward trend, showcasing the summarization efficiency of the LLM models.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3f8191c6-4ab5-4b6c-9a51-f8cbca2c9aba",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Truncate text for hover data\n",
    "max_chars = 50  # Maximum characters to display\n",
    "df['truncated_narrative'] = df['consumer_complaint_narrative'].apply(lambda x: x[:max_chars] + '...' if len(x) > max_chars else x)\n",
    "df['truncated_summary'] = df['Summary'].apply(lambda x: x[:max_chars] + '...' if len(x) > max_chars else x)\n",
    "\n",
    "# Calculate the length of consumer_complaint_narrative and Summary\n",
    "df['narrative_length'] = df['consumer_complaint_narrative'].apply(len)\n",
    "df['summary_length'] = df['Summary'].apply(len)\n",
    "\n",
    "# Create a scatter plot\n",
    "fig = px.scatter(df.sort_values(['narrative_length']), x='narrative_length', y='summary_length',\n",
    "                 hover_data=['complaint_id', 'truncated_narrative', 'truncated_summary'],\n",
    "                 labels={'narrative_length': 'Narrative Length', 'summary_length': 'Summary Length'},\n",
    "                 title='Complaint and Summary Lengths')\n",
    "\n",
    "# Update the x-axis to show values as they are (not in scientific notation)\n",
    "fig.update_xaxes(type='category')\n",
    "\n",
    "# Show the plot\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "06cbb0f4-d026-4a0b-8ab8-6982b7f7777a",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Now the results can be saved back to Vantage.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8b9a58e0-ec2e-4e76-8f51-f1757ca5a8b5",
   "metadata": {},
   "outputs": [],
   "source": [
    "copy_to_sql(df = df, table_name = 'Complaints_Summaries', if_exists = 'replace')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "561ff317-6468-4941-bf9b-840849bfb09d",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<b style = 'font-size:20px;font-family:Arial;color:#00233C'>5. Cleanup</b>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "476fb82b-d7a0-4e29-a6d4-8372a247cea8",
   "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>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9472c2ca-65d0-4116-9c45-6411fcea52ef",
   "metadata": {},
   "outputs": [],
   "source": [
    "%run -i ../run_procedure.py \"call remove_data('DEMO_ComplaintAnalysis');\"        # Takes 10 seconds"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "358f56a0-ad97-4317-ab4c-88be00b8d179",
   "metadata": {},
   "outputs": [],
   "source": [
    "remove_context()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6cf670cd-4594-458d-af98-8efff5a72f73",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<b style = 'font-size:18px;font-family:Arial;color:#00233C'>Dataset:</b>\n",
    "<br>\n",
    "<br>\n",
    "<p style='font-size: 16px; font-family: Arial; color: #00233C;'>The dataset is sourced from <a href='https://www.consumerfinance.gov/data-research/consumer-complaints/'>Consumer Financial Protection Bureau</a></p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "eeebf3ab-357c-488e-ba9d-78bf82f4d0dd",
   "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 - 2024. All Rights Reserved\n",
    "        </div>\n",
    "    </div>\n",
    "</footer>"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}