Declining Usage
In telecommunications, one significant predictor of churn is declining usage—
customers who use services less and less over time are more likely to leave than other customers. Customers who have declining usage are likely to have many variables indicating this:
■■
Billing measures, such as recent amounts spent are quite small.
■■
Usage measures, such as recent amounts used are quite small or always at monthly minimums.
■■
Optional services recently have no usage.
■■
Ratios of recent measures to older measures are less than 1, often significantly less than one, indicating recent usage is smaller than historical usage.
The existence of so many different measures for the same underlying behavior suggests a situation where a derived variable might be useful to capture the behavior in a single variable. The goal is to incorporate as much information as possible into a “declining usage” indicator.
470643 c17.qxd 3/8/04 11:29 AM Page 578
578 Chapter 17
T I P When many different variables all suggest a single customer behavior, then it is likely that a derived variable that incorporates this information will do a better job for data mining.
Fortunately, mathematics provides an elegant solution, in the form of the best fit line, as shown in Figure 17.15. The goodness of fit is described by the R2
statistic, which varies from 0 to 1, with values near 0 being poor fit and values near 1 being very good. The slope of the line provides the average rate of increase or decrease in some variable over time. In statistics, this slope is called the beta function and is calculated according to the following formula: Sum of (x-average(x))*(y-average(y)) / sum((x-average(x))2)
To give an example of how this might be used, consider the following data for the customer shown in the previous figure. Table 17.4 walks through the calculation for a typical customer.
Table 17.4 Example of Calculating the Slope for a Time Series MONTH
X – AVG(X) ( X – AVG
Y (FROM Y
–
( X – AVG(X)) *
( X –VALUE)
(X))^2
CUST A)
AVG(Y) ( Y – AVG( Y))
1
–5.5
30.25
53.47
3.19
–17.56
2
–4.5
20.25
46.61
–3.67
16.52
3
–3.5
12.25
47.18
–3.10
10.84
4
–2.5
6.25
49.54
–0.74
1.85
5
–1.5
2.25
48.71
–1.57
2.35
6
–0.5
0.25
52.04
1.76
–0.88
7
0.5
0.25
48.45
–1.83
–0.91
8
1.5
2.25
54.16
3.88
5.83
9
2.5
6.25
54.47
4.19
10.47
10
3.5
12.25
53.69
3.42
11.95
11
4.5
20.25
45.93
–4.35
–19.59
12
5.5
30.25
49.10
–1.18
–6.51
TOTAL
143
14.36
SLOPE
0.1004
470643 c17.qxd 3/8/04 11:29 AM Page 579
Preparing Data for Mining 579
56
54
52
y = 0.1007x + 49.625
50
R2 = 0.0135
48
46
44
1
2
3
4
5
6
7
8
9
10
11
12
Figure 17.15 The slope of the line of best fit provides a good measure of changes over time.
This example shows a very typical use for calculating the slope—finding the slope over the previous year’s usage or billing patterns. The tabular format shows the calculation in a way most suitable for a spreadsheet. However, many data mining tools provide a function to calculate beta values directly from a set of variables in a single row. When such a function is not available, it is possible to express it using more basic arithmetic functions.
Although monthly data is often the most convenient for such calculations, remember that different months have different numbers of days. This issue is particularly significant for businesses that have strong weekly cycles. Some months have five full weekends, for instance, while others only have four. Different months have between 20 and 23 working days (not including holidays).
These differences can account for up to 25 percent of the difference between months. When working with data that has such cycles, it is a good idea to calculate the “average per weekend” or “average per working day” to see how the chosen measure is changing over time.
T I P When working with data that has weekly cycles but must be reported by month, consider variables such as “average per weekend day” or “average per work day” so that comparisons between months are more meaningful.
470643 c17.qxd 3/8/04 11:29 AM Page 580
580 Chapter 17
Revolvers, Transactors, and Convenience Users:
Defining Customer Behavior
Often, business people can characterize different groups of customers based on their behavior over time. However, translating an informal business description into a form useful for data mining is challenging. Faced with such a challenge, the best response is to determine measures of customer behavior that match the business understanding.
This example is about a credit card group at a major retail bank, which has found that profitable customers come in three flavors:
■■ Revolvers are customers who maintain large balances on their credit cards. These are highly profitable customers because every month they pay interest on large balances.
■■ Transactors are customers who have high balances every month, but pay them off. These customers do not pay interest, but the processing fee charged on each transaction is an important source of revenue. One component of the transaction fee is based on a percentage of the transaction value.
■■ Convenience users are customers who periodically charge large amounts, for vacations or large purchases, for example, and then pay them off over several months. Although not as profitable as revolvers, they are lower risk, while still paying significant amounts of interest.
The marketing group believes that these three types of customers are motivated by different needs. So, understanding future customer behavior would allow future marketing campaigns to send the most appropriate message to each customer segment. The group would like to predict customer behavior 6
months in the future.
The interesting part of this example is not the prediction, but the definition of the segments. The training set needs examples where customers are already classified into the three groups. Obtaining this classification proves to be a challenge.
470643 c17.qxd 3/8/04 11:29 AM Page 581
Preparing Data for Mining 581
Data
The data available for this project consisted of 18 months of billing data, including:
■■
Credit limit
■■
Interest rate
■■
New charges made during each month
■■
Minimum payment
■■
Amount paid
■■
Total balance in each month
■■
Amount paid in interest and related charges each month
The rules for these credit cards are typical. When a customer has paid off the balance, there is no interest on new charges (for 1 month). However, when there is an outstanding balance, then interest is charged on both the balance and on new charges. What does this data tell us about customers?
Segmenting by Estimating Revenue
Estimated revenue is a good way of understanding the value of customers. (By itself, this value does not provide much insight into customer behavior, so it is not very useful for messaging.) Basing customer value on revenue alone assumes that the costs for all customers are the same. This is not true, but it is a useful approximation, since a full profitability model is quite complicated, difficult to develop, and beyond the scope of this example.
Table 17.5 illustrates 1 month of billing for six customers. The last column is the estimated revenue, which has two components. The first is the amount of interest paid. The second is the transaction fee on new transactions, which is estimated to be 1 percent of the new transaction volume for this example .
470643 c17.qxd 3/8/04 11:29 AM Page 582
582 Chapter 17
E
UN
8
3
7
3
T.
3.7
5.00
7.1
EVE
8.3
ES
R
$5.4
$1
$3
$2
$65.00
$6
N
OTI E
U
SAC
N
0
AN
5.00
EVE
TR
R
$0.5
$0.00
$1.00
$2
$65.00
$0.00
T
ESR
8
3
7
3
8.3
2.7
7.1
INTE
$4.9
$1
$3
$0.00
$0.00
$6
NT
UO D
5
00
5
5
3
5
3
AI
AM
P
$1
$1
$1,000
$7
$6,5
$1
NT
EM
5
5
IN
Y
5
3
3
A
TEAMFLY
M
P
$1
$1
$99
$0
$0
$1
G
IN
E
N
C
IN
AN
00
00
00
EG
B
BAL
$400
$4,5
$3,3
$0
$0
$4,5
ES
00
00
EW
0
00
HARG
N
C
$5
$0
$1
$2,5
$6,5
$0
TE A
4.9%
1.9%
4.9%
2.9%
7.9%
1
4.9%
1
1
1
1
IT RD IT
E
00
0,000
R
M
C
LI
$5
$5,000
$6,000
$1
$8,000
$5,000
Six Credit Card Customers and 1 Month of Data
7.5
Customer 1
Customer 2
Customer 3
Customer 4
Customer 5
Customer 6
Table 1
Team-Fly®
470643 c17.qxd 3/8/04 11:29 AM Page 583
Preparing Data for Mining 583
Estimated revenue is a good way to compare different customers with a single number. The table clearly shows that someone who rarely uses the credit card (Customer 1) has very little estimated revenue. On the other hand, those who make many charges or pay interest create a larger revenue stream.
However, estimated revenue does not differentiate between different types of customers. In fact, a transactor (Customer 5) has very high revenue. So, does a revolver who has no new charges (Customer 6). This example shows that estimated revenue has little relationship to customer behavior. Frequent users of the credit card and infrequent users both generate a lot of revenue. And this is to be expected, since there are different types of profitable customers.