Preventing Customer Churn, Part 3. Inference from Amazon Aurora

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.

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.

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.

Table of contents

  1. Connect to Aurora Database
  2. Customer sentiment: Query Amazon Comprehend from Amazon Aurora
  3. Prepare the database for inference
  4. Query the Amazon SageMaker endpoint from Amazon Aurora
  5. Ready, Set, Go!

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.

If you'd like to read further on this setup, documentation on how to create the policy and a role can be found here. Details on how to create a custom database parameter group are described here.

Connect to Aurora Database

If the Python module mysql.connector is not installed, install it with pip.

In [1]:
import sys
# upgrade pip
!{sys.executable} -m pip install --upgrade pip 
!{sys.executable} -m pip install mysql.connector
import mysql.connector as mysql
Requirement already up-to-date: pip in /home/ec2-user/anaconda3/envs/python3/lib/python3.6/site-packages (20.0.2)
Processing /home/ec2-user/.cache/pip/wheels/23/27/3e/72be437e6a950b8972728d2a62935ae7cd0d7c3c251fb2e737/mysql_connector-2.2.9-cp36-cp36m-linux_x86_64.whl
Installing collected packages: mysql.connector
Successfully installed mysql.connector

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 "" during creation of the notebook instance, along with the DB secret name and ML endpoint name.

In [2]:
# import installed module
import mysql.connector as mysql
import os
import pandas as pd
import numpy as np
import boto3
import json
import cloudformation_values as cfvalues
In [3]:
# get the session information
session = boto3.Session()
# extract the region and account id
region = cfvalues.REGION

# AWS Secrets stores our database credentials. 
db_secret_name = cfvalues.DBSECRET

# Get the secret from AWS Secrets manager. Extract user, password, host.
from utilities import get_secret
get_secret_value_response = get_secret(db_secret_name, region)

creds = json.loads(get_secret_value_response['SecretString'])
db_user = creds['username']
db_password = creds['password']
# Writer endpoint
db_host = creds['host']
In [4]:
# This is the Amazon SageMaker preset endpoint_name created by the Cloud Formation
endpoint_name = cfvalues.ENDPOINT 
In [5]:
# Define the database and table names
database_name = "telecom_customer_churn"
churn_table = "customers"
customer_msgs_table = "customer_message"
customer_churn_results = "customer_churn_results"

Connect to the database using the credentials retrieved above.

In [6]:
# create connection to the database
cnx = mysql.connect(user = db_user, 
                    password = db_password,
                    host = db_host,
                    database = database_name)
dbcursor = cnx.cursor(buffered = True)

Customer sentiment: Query Amazon Comprehend from Amazon Aurora

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.

In [7]:
sql = """SELECT message,
       aws_comprehend_detect_sentiment(message, 'en') AS sentiment,
       aws_comprehend_detect_sentiment_confidence(message, 'en') AS confidence
  FROM {};""".format(customer_msgs_table)
[('Thank you very much for resolving the issues with my bill!',
 ("I don't understand how I paid for 100 minutes and got only 90, you are ripping me off!",
 ('Please fix this issue! I am sick of sitting on a phone every single day with you people!',
 ('This is a really great feature, thank for helping me store all my phone numbers.',
 ('Why am I paying so much for my international minutes?',
 ("Why do I have to wait for the response from the customer service for so long? I don't have time for this.",

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.

Prepare the database for inference

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.

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.

In the final model, we used only 1 of these values: int_plan_no. There are two ways to approach this problem:

  1. Add data transformation code to the endpoint.
  2. Create functions in the SQL database that will represent one-hot encoded variables.

Here we will demonstrate the second option.

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.

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']

In [8]:
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']
In [9]:
dbcursor.execute("DESCRIBE {churn_table};".format(churn_table=churn_table))
[('state', 'varchar(2048)', 'YES', '', None, ''),
 ('acc_length', 'bigint(20)', 'YES', '', None, ''),
 ('area_code', 'bigint(20)', 'YES', '', None, ''),
 ('phone', 'varchar(2048)', 'YES', '', None, ''),
 ('int_plan', 'varchar(2048)', 'YES', '', None, ''),
 ('vmail_plan', 'varchar(2048)', 'YES', '', None, ''),
 ('vmail_msg', 'bigint(20)', 'YES', '', None, ''),
 ('day_mins', 'double', 'YES', '', None, ''),
 ('day_calls', 'bigint(20)', 'YES', '', None, ''),
 ('day_charge', 'double', 'YES', '', None, ''),
 ('eve_mins', 'double', 'YES', '', None, ''),
 ('eve_calls', 'bigint(20)', 'YES', '', None, ''),
 ('eve_charge', 'double', 'YES', '', None, ''),
 ('night_mins', 'double', 'YES', '', None, ''),
 ('night_calls', 'bigint(20)', 'YES', '', None, ''),
 ('night_charge', 'double', 'YES', '', None, ''),
 ('int_mins', 'double', 'YES', '', None, ''),
 ('int_calls', 'bigint(20)', 'YES', '', None, ''),
 ('int_charge', 'double', 'YES', '', None, ''),
 ('cust_service_calls', 'bigint(20)', 'YES', '', None, ''),
 ('churn', 'varchar(2048)', 'YES', '', None, '')]

Create functions to perform one-hot encoding:

In [10]:
# one-hot encoding for int_plan

dbcursor.execute("DROP FUNCTION IF EXISTS IntPlanOneHot;")
sql = """CREATE FUNCTION IntPlanOneHot(int_plan varchar(2048))
 DECLARE int_plan_no INT;
 IF int_plan = 'no' THEN SET int_plan_no = 1;
 ELSE SET int_plan_no = 0;
 RETURN int_plan_no;

# one-hot encoding for area_code to generate area_code_510
# While this function is not used for this model run, we provide it as an additional demonstration, 
# and in case a similar feature is used in a later model run
dbcursor.execute("DROP FUNCTION IF EXISTS AreaCode510;")
sql = """CREATE FUNCTION AreaCode510(area_code bigint(20))
 DECLARE area_code_510 INT;
 IF area_code = 510 THEN SET area_code_510 = 1;
 ELSE SET area_code_510 = 0;
 RETURN area_code_510;

# one-hot encoding for area_code to generate area_code_510
# While this function is not used for this model run, we provide it as an additional demonstration, 
# and in case a similar feature is used in a later model run
dbcursor.execute("DROP FUNCTION IF EXISTS stateTX;")
sql = """CREATE FUNCTION stateTX(state varchar(2048))
 IF state = 'TX' THEN SET state_TX = 1;
 ELSE SET state_TX = 0;
 RETURN state_TX;

Quick demonstration that the functions have been created and work correctly:

In [11]:
dbcursor.execute("""SELECT IntPlanOneHot(int_plan), AreaCode510(area_code), stateTX(state),
                    int_plan, area_code, state FROM {} LIMIT 5;""".format(churn_table))
[(1, 0, 0, 'no', 415, 'KS'),
 (1, 0, 0, 'no', 415, 'OH'),
 (1, 0, 0, 'no', 415, 'NJ'),
 (0, 0, 0, 'yes', 408, 'OH'),
 (0, 0, 0, 'yes', 415, 'OK')]

Query the Amazon SageMaker endpoint from Amazon Aurora

We need to create a function that passes all the information needed by the Amazon SageMaker endpoint as described here (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.

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.

In [12]:
['acc_length', 'vmail_msg', 'day_mins', 'day_calls', 'eve_mins', 'night_mins', 'night_calls', 'int_calls', 'int_charge', 'cust_service_calls', 'int_plan_no']
In [13]:
# REMEMBER! to modify the columns in the SQL below to match the cols_used (if it doesn't already) 
dbcursor.execute("DROP FUNCTION IF EXISTS will_churn;")

sql = """CREATE FUNCTION will_churn (
    acc_length bigint(20),
    vmail_msg bigint(20),
    day_mins double,
    day_calls bigint(20),
    eve_mins double,
    night_mins double,
    night_calls bigint(20),
    int_calls bigint(20),
    int_charge double,
    cust_service_calls bigint(20),
    int_plan_no int)
    alias aws_sagemaker_invoke_endpoint
    endpoint name '{endpoint}' ; """.format(endpoint = endpoint_name) 
In [14]:

Now we can call the function with data from our table and ask for the results of the prediction.

In [15]:
# To make it easier to carry across SQL without error, we'll capture the parameters in a function here.
will_churn_fn = """will_churn(acc_length, vmail_msg, day_mins, day_calls, eve_mins, night_mins, 
night_calls, int_calls, int_charge, cust_service_calls, IntPlanOneHot(int_plan) )"""
In [16]:
sql = """SELECT state, area_code, phone, round(day_charge + eve_charge + night_charge + int_charge, 2),
        int_plan, cust_service_calls, 
        round({will_churn_fn},3) will_churn FROM {customers}
        LIMIT 5;""".format(will_churn_fn = will_churn_fn, customers = churn_table)
[('KS', 415, '382-4657', 75.56, 'no', 1, 0.01),
 ('OH', 415, '371-7191', 59.24, 'no', 1, 0.016),
 ('NJ', 415, '358-1921', 62.29, 'no', 0, 0.106),
 ('OH', 408, '375-9999', 66.8, 'yes', 2, 0.369),
 ('OK', 415, '330-6626', 52.09, 'yes', 3, 0.047)]

If the previous command returns a list of entries, then the request from Amazon Aurora for predictions from the model worked!

The last value in the tuple is the model's prediction score for whether this customer will churn.

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.

Now let's add sentiment detection into the SQL request.

In [17]:
dbcursor.execute("""SELECT day_mins, cust_service_calls, int_plan,
    round({will_churn_fn},3) will_churn,
    aws_comprehend_detect_sentiment('You morons! You charged me extra again!', 'en') AS sentiment,
    round(aws_comprehend_detect_sentiment_confidence('You morons! You charged me extra again!', 'en'),3) 
    AS confidence 
    FROM customers
    WHERE area_code=415 AND phone='358-1921';""".format(will_churn_fn = will_churn_fn))
[(243.4, 0, 'no', 0.106, bytearray(b'NEGATIVE'), 0.88)]

The values returned are:

  • day minutes
  • number of customer service calls
  • whether they have an International plan
  • the prediction score for whether this customer will churn, returned from the Amazon SageMaker model
  • the overall sentiment of the message, from Amazon Comprehend
  • the confidence in the message sentiment, from Amazon Comprehend

Ready, Set, Go!

Now we're finally ready to put all the pieces together in our campaign to prevent customer churn!

We've received our first round of proposed incentives from Marketing. We've coded their rules into a function, suggest_incentive, shown below.

After the function, we'll send it some test requests.

In [18]:
# Create a select with a join: customer_message, customers; with a call on the fly to do the one-hot encoding AND call comprehend
import random
import json

def suggest_incentive(day_mins, cust_service_calls, int_plan_no, will_churn, sentiment, confidence):
    # Returns a suggestion of what to offer as a rebate to this customer, based on their churn characteristics and this interaction 
    if sentiment == 'POSITIVE' and confidence>0.5:
        if will_churn < 0.5:   # Basically happy customer
            return "Sentiment POSITIVE and will_churn<0.5: No incentive."
        else:                # Good interaction, but at-risk-of churn; let's offer something
            return "Sentiment POSITIVE and will_churn>0.5: $5 credit"         
    elif sentiment == 'NEGATIVE' and confidence>0.7:
        if will_churn > 0.8:   # oh-oh! High odds! Pull out all stops
            return "Sentiment NEGATIVE and will_churn>0.8: $25 credit"
        elif will_churn > 0.4: # Not so bad, but still need to offer something. But what?
            if random.choice([0,1]) == 1:
                return "Will_churn confidence > 0.4, experiment: $15 credit"
                return "Will_churn confidence > 0.4, experiment: $5 credit"
        else:                # Pretty happy customer, we'll trust it's just a blip
            return "Will_churn confidence <= 0.4: No incentive."
    elif cust_service_calls > 2 and not int_plan_no:
        return "cust_service_calls > 4 and not int_plan_no: 1000 free minutes of international calls"
        return "NOT (cust_service_calls > 4 and not int_plan_no): No incentive."
    return "No incentive."

def assess_and_recommend_incentive(area_code, phone, message):
    sql = """SELECT day_mins, cust_service_calls, IntPlanOneHot(int_plan) as int_plan_no,
        round({will_churn_fn},3) as will_churn,
        aws_comprehend_detect_sentiment('{message}', 'en') AS sentiment,
        round(aws_comprehend_detect_sentiment_confidence('{message}', 'en'),3) 
        AS confidence
        FROM {customers}
        WHERE area_code={area_code}
        AND phone='{phone}';""".format(will_churn_fn = will_churn_fn,
                                       customers = churn_table,
                                       message = message,
                                       area_code = area_code,
                                       phone = phone)
    result = dbcursor.fetchone()
    incentive = suggest_incentive(result[0], result[1], result[2], result[3], result[4].decode(), result[5])
    ret = {"area_code": area_code,
           "phone": phone,
           "service_calls": result[1],
           "international_plan": 1 - result[2],
           "churn_prob": result[3],
           "msg_sentiment": result[4].decode(),
           "msg_confidence": result[5],
           "incentive": incentive
    return ret

print(assess_and_recommend_incentive(408, '375-9999' , "You morons! You charged me extra again!"), "\n")
print(assess_and_recommend_incentive(415, '358-1921', "How do I dial Morocco?"), "\n")
print(assess_and_recommend_incentive(415, '329-6603', "Thank you very much for resolving the issues with my account"), "\n")
{'area_code': 408, 'phone': '375-9999', 'service_calls': 2, 'international_plan': 1, 'churn_prob': 0.369, 'msg_sentiment': 'NEGATIVE', 'msg_confidence': 0.88, 'incentive': 'Will_churn confidence <= 0.4: No incentive.'} 

{'area_code': 415, 'phone': '358-1921', 'service_calls': 0, 'international_plan': 0, 'churn_prob': 0.106, 'msg_sentiment': 'NEUTRAL', 'msg_confidence': 0.83, 'incentive': 'NOT (cust_service_calls > 4 and not int_plan_no): No incentive.'} 

{'area_code': 415, 'phone': '329-6603', 'service_calls': 4, 'international_plan': 0, 'churn_prob': 0.573, 'msg_sentiment': 'POSITIVE', 'msg_confidence': 0.992, 'incentive': 'Sentiment POSITIVE and will_churn>0.5: $5 credit'} 

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.

We have the beginnings of understanding the key factors, and the beginnings of an experimentation platform.

We can now begin to add experimentation and nuance to our incentive program:

  • 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.
  • We can randomize the incentives offered, in order to perform "A/B" testing of different incentives to customers with similar characteristics.
  • We can experiment with different kinds of incentives, beyond the simple dollar rebate in our sample: new telephones, plan upgrades, and so on.
  • 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?
  • We can add economic analysis. How much is it worth to keep this customer? What's the optimal threshold score to maximize profit?

As we explore these alternatives, we move from prediction, to providing tangible and actionable business value. Over to you, Marketing!