{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "# Gain customer insights, Part 3. Inference from Amazon Aurora\n", "\n", "Now that we've created the ML model and an endpoint to serve up the inferences, we'd like to connect that endpoint to Amazon Aurora. That way we can request a prediction on whether this customer will churn at the same time that we retrieve information about this customer.\n", "\n", "In addition, we'll call Amazon Comprehend to Amazon Aurora. That way, we can also request an assessment of the customer's sentiment when they send a message to customer service.\n", "\n", "With both of these pieces of information in hand, we can then make an on-the-fly decision about whether to offer the customer an incentive program of some kind. Of course, the details of that incentive and the rules on when to offer it must come from Marketing.\n", "\n", "----\n", "\n", "## Table of contents\n", "\n", "1. [Connect to Aurora Database](#Connect-to-Aurora-Database)\n", "2. [Customer sentiment: Query Amazon Comprehend from Amazon Aurora](#Customer-sentiment:-Query-Amazon-Comprehend-from-Amazon-Aurora)\n", "3. [Prepare the database for inference](#Prepare-the-database-for-inference)\n", "4. [Query the Amazon SageMaker endpoint from Amazon Aurora](#Query-the-Amazon-SageMaker-endpoint-from-Amazon-Aurora)\n", "5. [Ready, Set, Go!](#Ready,-Set,-Go!)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Note that for simplicity we're using a predefined Amazon SageMaker endpoint_name here. The AWS CloudFormation template created this endpoint (together with an endpoint configuration), added it to an IAM role (this role authorizes the users of Aurora database to access AWS ML services), and assigned the Aurora Database cluster parameter group value 'aws_default_sagemaker_role' to this IAM role. This combination of settings gives Aurora permission to call the Amazon SageMaker endpoint.\n", "\n", "If you'd like to read further on this setup, documentation on how to create the policy and a role can be found [here](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/mysql-ml.html#aurora-ml-sql-privileges). Details on how to create a custom database parameter group are described [here](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Integrating.Authorizing.IAM.AddRoleToDBCluster.html). \n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Connect to Aurora Database\n", "\n", "If the Python module `mysql.connector` is not installed, install it with pip." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "import sys\n", "# upgrade pip\n", "!{sys.executable} -m pip install --upgrade pip \n", "!{sys.executable} -m pip install mysql.connector\n", "import mysql.connector as mysql" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "For this use case, we've created the S3 bucket and appropriate IAM roles for you during the launch of the AWS CloudFormation template. The bucket name was saved in a parameter file called \"cloudformation_values.py\" during creation of the notebook instance, along with the DB secret name and ML endpoint name." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# import installed module\n", "import mysql.connector as mysql\n", "import os\n", "import pandas as pd\n", "import numpy as np\n", "import boto3\n", "import json\n", "import cloudformation_values as cfvalues" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# get the session information\n", "session = boto3.Session()\n", "# extract the region and account id\n", "region = cfvalues.REGION\n", "\n", "# AWS Secrets stores our database credentials. \n", "db_secret_name = cfvalues.DBSECRET\n", "\n", "# Get the secret from AWS Secrets manager. Extract user, password, host.\n", "from utilities import get_secret\n", "get_secret_value_response = get_secret(db_secret_name, region)\n", "\n", "creds = json.loads(get_secret_value_response['SecretString'])\n", "db_user = creds['username']\n", "db_password = creds['password']\n", "# Writer endpoint\n", "db_host = creds['host']\n" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# This is the Amazon SageMaker preset endpoint_name created by the Cloud Formation\n", "endpoint_name = cfvalues.ENDPOINT \n", "print(endpoint_name)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# Define the database and table names\n", "database_name = \"telecom_customer_churn\"\n", "churn_table = \"customers\"\n", "customer_msgs_table = \"customer_message\"\n", "customer_churn_results = \"customer_churn_results\"" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Connect to the database using the credentials retrieved above." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# create connection to the database\n", "cnx = mysql.connect(user = db_user, \n", " password = db_password,\n", " host = db_host,\n", " database = database_name)\n", "dbcursor = cnx.cursor(buffered = True)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Customer sentiment: Query Amazon Comprehend from Amazon Aurora\n", "\n", "Let's first test that we can call Amazon Comprehend from our SQL query, and return the sentiment for a customer message. We'll use the messages we inserted into our \"customer call history\" table in the part 1 to test this capability." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "sql = \"\"\"SELECT message,\n", " aws_comprehend_detect_sentiment(message, 'en') AS sentiment,\n", " aws_comprehend_detect_sentiment_confidence(message, 'en') AS confidence\n", " FROM {};\"\"\".format(customer_msgs_table)\n", "dbcursor.execute(sql)\n", "dbcursor.fetchall()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Here we can see the customer's sentiment, based on the text of their customer service contact text. We have an overall assessment, such as 'POSITIVE', and a numeric confidence. We can use the assessment and the score to make a decision on what to offer the customer. " ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Prepare the database for inference" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Now we need to set up the Aurora database to call the Amazon SageMaker endpoint and pass the data it needs to return an inference. \n", "\n", "Our original data contained numeric variables as well as several categorical variables (such as `area_code` and `int_plan`) which are needed for prediction. During creation of the ML model, the categorical variables were converted to one-hot vectors. \n", "\n", "In the final model, we used only 1 of these values: `int_plan_no`. There are two ways to approach this problem:\n", "\n", "1. Add data transformation code to the endpoint. \n", "2. Create functions in the SQL database that will represent one-hot encoded variables.\n", "\n", "Here we will demonstrate the second option.\n", "\n", "_**Below, we've listed the features used by our final model. If this list has changed in content or in order for your run, you will need to modify the steps below so that they match your list.**_\n", "\n", "cols_used = ['acc_length', 'vmail_msg', 'day_mins', 'day_calls', 'eve_mins', 'night_mins', 'night_calls', 'int_calls', 'int_charge', 'cust_service_calls', 'int_plan_no']" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "cols_used = ['acc_length', 'vmail_msg', 'day_mins', 'day_calls', 'eve_mins', 'night_mins', \n", " 'night_calls', 'int_calls', 'int_charge', 'cust_service_calls', 'int_plan_no']" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "dbcursor.execute(\"DESCRIBE {churn_table};\".format(churn_table=churn_table))\n", "dbcursor.fetchall()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Create functions to perform one-hot encoding:" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# one-hot encoding for int_plan\n", "\n", "dbcursor.execute(\"DROP FUNCTION IF EXISTS IntPlanOneHot;\")\n", "sql = \"\"\"CREATE FUNCTION IntPlanOneHot(int_plan varchar(2048))\n", "RETURNS INT\n", "BEGIN\n", " DECLARE int_plan_no INT;\n", " IF int_plan = 'no' THEN SET int_plan_no = 1;\n", " ELSE SET int_plan_no = 0;\n", " END IF;\n", " RETURN int_plan_no;\n", "END\n", ";\"\"\"\n", "dbcursor.execute(sql)\n", "\n", "# one-hot encoding for area_code to generate area_code_510\n", "# While this function is not used for this model run, we provide it as an additional demonstration, \n", "# and in case a similar feature is used in a later model run\n", "dbcursor.execute(\"DROP FUNCTION IF EXISTS AreaCode510;\")\n", "sql = \"\"\"CREATE FUNCTION AreaCode510(area_code bigint(20))\n", "RETURNS INT\n", "BEGIN\n", " DECLARE area_code_510 INT;\n", " IF area_code = 510 THEN SET area_code_510 = 1;\n", " ELSE SET area_code_510 = 0;\n", " END IF;\n", " RETURN area_code_510;\n", "END\n", ";\"\"\"\n", "dbcursor.execute(sql)\n", "\n", "# one-hot encoding for area_code to generate area_code_510\n", "# While this function is not used for this model run, we provide it as an additional demonstration, \n", "# and in case a similar feature is used in a later model run\n", "dbcursor.execute(\"DROP FUNCTION IF EXISTS stateTX;\")\n", "sql = \"\"\"CREATE FUNCTION stateTX(state varchar(2048))\n", "RETURNS INT\n", "BEGIN\n", " DECLARE state_TX INT;\n", " IF state = 'TX' THEN SET state_TX = 1;\n", " ELSE SET state_TX = 0;\n", " END IF;\n", " RETURN state_TX;\n", "END\n", ";\"\"\"\n", "dbcursor.execute(sql)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Quick demonstration that the functions have been created and work correctly:" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "dbcursor.execute(\"\"\"SELECT IntPlanOneHot(int_plan), AreaCode510(area_code), stateTX(state),\n", " int_plan, area_code, state FROM {} LIMIT 5;\"\"\".format(churn_table))\n", "dbcursor.fetchall()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Query the Amazon SageMaker endpoint from Amazon Aurora\n", "\n", "We need to create a function that passes all the information needed by the Amazon SageMaker endpoint as described [here](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/mysql-ml.html#aurora-ml-sql-privileges) (see section \"Enabling Aurora Machine Learning\"). Here we will create a function `will_churn` that will use the variables needed by the model. Notice that we are now including the columns created by one-hot encoding in the previous section. The endpoint name was declared in the beginning of the notebook.\n", "\n", "*If the list of columns printed below has changed in content or in order for your run, you will need to modify the steps below so that they match your list.*\n" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "print(cols_used)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# REMEMBER! to modify the columns in the SQL below to match the cols_used (if it doesn't already) \n", "dbcursor.execute(\"DROP FUNCTION IF EXISTS will_churn;\")\n", "\n", "sql = \"\"\"CREATE FUNCTION will_churn (\n", " acc_length bigint(20),\n", " vmail_msg bigint(20),\n", " day_mins double,\n", " day_calls bigint(20),\n", " eve_mins double,\n", " night_mins double,\n", " night_calls bigint(20),\n", " int_calls bigint(20),\n", " int_charge double,\n", " cust_service_calls bigint(20),\n", " int_plan_no int)\n", "RETURNS float\n", " alias aws_sagemaker_invoke_endpoint\n", " endpoint name '{endpoint}' ; \"\"\".format(endpoint = endpoint_name) \n", " \n", "dbcursor.execute(sql)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "cnx.commit()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Now we can call the function with data from our table and ask for the results of the prediction." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# To make it easier to carry across SQL without error, we'll capture the parameters in a function here.\n", "will_churn_fn = \"\"\"will_churn(acc_length, vmail_msg, day_mins, day_calls, eve_mins, night_mins, \n", "night_calls, int_calls, int_charge, cust_service_calls, IntPlanOneHot(int_plan) )\"\"\"" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "sql = \"\"\"SELECT state, area_code, phone, round(day_charge + eve_charge + night_charge + int_charge, 2),\n", " int_plan, cust_service_calls, \n", " round({will_churn_fn},3) will_churn FROM {customers}\n", " LIMIT 5;\"\"\".format(will_churn_fn = will_churn_fn, customers = churn_table)\n", "dbcursor.execute(sql)\n", "dbcursor.fetchall()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "If the previous command returns a list of entries, then the request from Amazon Aurora for predictions from the model worked!\n", "\n", "The last value in the tuple is the model's prediction score for whether this customer will churn.\n", "\n", "Currently the model returns a prediction value before assigning a cutoff (since we deployed it to return such a value). We could choose to convert this value to a Boolean True or False either via a modification to the SageMaker endpoint, or via an additional transformation on the DB side. However, in this case we'll leave it, so at some later time we can explore these values in greater detail. For example, there is likely a large range of \"maybe churn\", between \"will\" and will not churn\". From a Marketing perspective, these are the customers we'd ideally likely to identify and target.\n", "\n", "Now let's add sentiment detection into the SQL request." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "dbcursor.execute(\"\"\"SELECT day_mins, cust_service_calls, int_plan,\n", " round({will_churn_fn},3) will_churn,\n", " aws_comprehend_detect_sentiment('You morons! You charged me extra again!', 'en') AS sentiment,\n", " round(aws_comprehend_detect_sentiment_confidence('You morons! You charged me extra again!', 'en'),3) \n", " AS confidence \n", " FROM customers\n", " WHERE area_code=415 AND phone='358-1921';\"\"\".format(will_churn_fn = will_churn_fn))\n", "dbcursor.fetchall()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "The values returned are:\n", "* day minutes\n", "* number of customer service calls\n", "* whether they have an International plan\n", "* the prediction score for whether this customer will churn, returned from the Amazon SageMaker model\n", "* the overall sentiment of the message, from Amazon Comprehend\n", "* the confidence in the message sentiment, from Amazon Comprehend\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Ready, Set, Go!\n", "\n", "Now we're finally ready to put all the pieces together in our campaign to prevent customer churn!\n", "\n", "We've received our first round of proposed incentives from Marketing. We've coded their rules into a function, suggest_incentive, shown below. \n", "\n", "After the function, we'll send it some test requests." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# Create a select with a join: customer_message, customers; with a call on the fly to do the one-hot encoding AND call comprehend\n", "import random\n", "import json\n", "\n", "def suggest_incentive(day_mins, cust_service_calls, int_plan_no, will_churn, sentiment, confidence):\n", " # Returns a suggestion of what to offer as a rebate to this customer, based on their churn characteristics and this interaction \n", " if sentiment == 'POSITIVE' and confidence>0.5:\n", " if will_churn < 0.5: # Basically happy customer\n", " return \"Sentiment POSITIVE and will_churn<0.5: No incentive.\"\n", " else: # Good interaction, but at-risk-of churn; let's offer something\n", " return \"Sentiment POSITIVE and will_churn>0.5: $5 credit\" \n", " elif sentiment == 'NEGATIVE' and confidence>0.7:\n", " if will_churn > 0.8: # oh-oh! High odds! Pull out all stops\n", " return \"Sentiment NEGATIVE and will_churn>0.8: $25 credit\"\n", " elif will_churn > 0.4: # Not so bad, but still need to offer something. But what?\n", " if random.choice([0,1]) == 1:\n", " return \"Will_churn confidence > 0.4, experiment: $15 credit\"\n", " else:\n", " return \"Will_churn confidence > 0.4, experiment: $5 credit\"\n", " else: # Pretty happy customer, we'll trust it's just a blip\n", " return \"Will_churn confidence <= 0.4: No incentive.\"\n", " elif cust_service_calls > 2 and not int_plan_no:\n", " return \"cust_service_calls > 4 and not int_plan_no: 1000 free minutes of international calls\"\n", " else:\n", " return \"NOT (cust_service_calls > 4 and not int_plan_no): No incentive.\"\n", " return \"No incentive.\"\n", "\n", "def assess_and_recommend_incentive(area_code, phone, message):\n", " sql = \"\"\"SELECT day_mins, cust_service_calls, IntPlanOneHot(int_plan) as int_plan_no,\n", " round({will_churn_fn},3) as will_churn,\n", " aws_comprehend_detect_sentiment('{message}', 'en') AS sentiment,\n", " round(aws_comprehend_detect_sentiment_confidence('{message}', 'en'),3) \n", " AS confidence\n", " FROM {customers}\n", " WHERE area_code={area_code}\n", " AND phone='{phone}';\"\"\".format(will_churn_fn = will_churn_fn,\n", " customers = churn_table,\n", " message = message,\n", " area_code = area_code,\n", " phone = phone)\n", " dbcursor.execute(sql)\n", " result = dbcursor.fetchone()\n", " incentive = suggest_incentive(result[0], result[1], result[2], result[3], result[4].decode(), result[5])\n", " ret = {\"area_code\": area_code,\n", " \"phone\": phone,\n", " \"service_calls\": result[1],\n", " \"international_plan\": 1 - result[2],\n", " \"churn_prob\": result[3],\n", " \"msg_sentiment\": result[4].decode(),\n", " \"msg_confidence\": result[5],\n", " \"incentive\": incentive\n", " }\n", " return ret\n", "\n", "print(assess_and_recommend_incentive(408, '375-9999' , \"You morons! You charged me extra again!\"), \"\\n\")\n", "print(assess_and_recommend_incentive(415, '358-1921', \"How do I dial Morocco?\"), \"\\n\")\n", "print(assess_and_recommend_incentive(415, '329-6603', \"Thank you very much for resolving the issues with my account\"), \"\\n\")\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Now we have the ability to detect, during a customer interaction, whether this is an \"at-risk\" customer, and to intervene - to provide them with an incentive to stay. We can choose the factors on which we want to base our response: the sentiment of this interaction, combined with the customer's current characteristics, interaction history, and estimated risk. We can randomize our responses, too, in order to begin to gather more data on what effective incentives are. \n", "\n", "We have the beginnings of understanding the key factors, and the beginnings of an experimentation platform.\n", "\n", "We can now begin to add experimentation and nuance to our incentive program:\n", "\n", "* At the time the incentive is offered, we can record the customer's sentiment, churn prediction at this time, the current value of key drivers, and the incentive offered.\n", "* We can randomize the incentives offered, in order to perform \"A/B\" testing of different incentives to customers with similar characteristics.\n", "* We can experiment with different kinds of incentives, beyond the simple dollar rebate in our sample: new telephones, plan upgrades, and so on. \n", "* We can later analyze the incentives offered and the results achieved by those incentives. How much of an incentive must we offer to which kinds of customers to reduce churn? How much is retaining that customer worth to us, versus the cost of the incentive?\n", "* We can add economic analysis. How much is it worth to keep this customer? What's the optimal threshold score to maximize profit?\n", "\n", "As we explore these alternatives, we move from prediction, to providing tangible and actionable business value. Over to you, Marketing! " ] } ], "metadata": { "kernelspec": { "display_name": "Python 3", "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.8.1" } }, "nbformat": 4, "nbformat_minor": 4 }