then bill_amt else 0 end) as bill_1,
sum(case when trunc(months_between(bill_date, cutoff) = 2
then bill_amt else 0 end) as bill_2,
. . .
FROM billing b,
(select customer_id,
(case when status = ‘ACTIVE’ then sysdate
else stop_date end) as cutoff
from customer) c
where b.customer_id = c.customer_id
GROUP BY customer_id
This code fragment does use some extensions to SQL for the date calculations (these are expressed as Oracle functions in this example).
However, most databases have similar functions.
The above code is an example of a killer query, because it is joining a big table (the customer table) with an even bigger table (the customer billing table) and then doing a grouping operation. Fortunately, modern databases can take advantage of multiple processors and multiple disks to perform this query in a reasonable amount of time.
470643 c17.qxd 3/8/04 11:29 AM Page 574
574 Chapter 17
Summarizing Transactional Records
Transactional records are an example of an irregular time series—that is, the records can occur at any point in time. Such records are generated by customer interactions, as is the case with:
■■ Automated teller machine transactions
■■ Telephone calls
■■ Web site visits
■■ Retail purchases
There are several challenges when working with irregular time series. First, the transaction volumes are very, very large. Working with such voluminous data requires sophisticated tools and powerful computers. Second, there is no standard way of working with them. The regular time series data has a natural way of pivoting. For irregular time series, it is necessary to determine how best to summarize the data.
One way is to transform the irregular time series into regular time series and then to pivot the series. For instance, calculate the number of calls per month or the amount withdrawn from ATMs each month, and then pivot the sums by month. When working with transactions, these calculations can be more complex, such as the number of calls longer than 10 minutes or the number of withdrawals less than $50. These specialized summaries can be quite useful.
More complicated examples that describe customer behavior are provided just after the next section.
Another approach is to define a set of data transformations that are run on the transactional data as it is being collected. This is an approach taken in the telecommunications industry, where the volume of data is vast. Some variables may be as simple as minutes of use, others may be a complex as a score for whether the calling number is a business or residence. This approach hard-codes the calculations, and such calculations are hard to change. Although such variables can be useful, a more flexible environment for summarizing transactional data is strategically more useful.
Summarizing Fields across the Model Set
The last method for deriving variables is summarizing values across fields in the customer signature itself. There are several examples of such fields:
■■ Binning values into equal sized bins requires calculating the breakpoints for the bins.
■■ Standardizing a value (subtracting the mean and dividing by the standard deviation) requires calculating the mean and standard deviation for the field and then doing the calculation.
470643 c17.qxd 3/8/04 11:29 AM Page 575
Preparing Data for Mining 575
■■ Ranking a value (so the smallest value has a value of 1, the second smallest 2, and so on) requires sorting all the values to get the ranking.
Although these are complicated operations, they are performed directly on the model set. Data mining tools provide support for these operations, especially for binning numeric values, which is the most important of the three.
One type of binning that would be very useful is not readily available. This is binning for codes based on frequency. That is, it would be useful to keep all codes that have at least, say, 1,000 instances in the model set and to place all other codes in a single “other” category. This is useful for working with outliers, such as the many old and unpopular handsets that show up in mobile telephone data although few customers use them. One way to handle this is to identify the handsets to keep and to add a new field “handset for analysis”
that keeps these handsets and places the rest into an “other” category. A more automated way is to create a lookup table to map the handsets. However, perhaps a better way is to replace the handset ID itself with information such as the date the handset was released, its weight, and the features it uses—information that is probably available in a lookup table already.
Examples of Behavior-Based Variables
The real power of derived variables comes from the ability to summarize customer behaviors along known dimensions. This section builds on the ideas already presented and gives three examples of useful behavior-based variables.
Frequency of Purchase
Once upon a time, catalogers devised a clever method for characterizing customer behavior using three dimensions—recency, frequency, and monetary value. RFM, which relies on these three variables, has been used at least since the 1970s. Of these three descriptions of customer behavior, recency is usually the most predictive, but frequency is the most interesting. Recency simply means the length of time since a customer made a purchase. Monetary value is traditionally the total amount purchased (although we have found the average purchase value more useful since the total is highly correlated with frequency).
In traditional RFM analysis, frequency is just the number of purchases.
However, a simple count does not do a good job of characterizing customer behavior. There are other approaches to determining frequency, and these can be applied to other areas not related to catalog purchasing—frequency of complaints, frequency of making international telephone calls, and so on. The important point is that customers may perform an action at irregular intervals, and we want to characterize this behavior pattern because it provides potentially useful information about customers.
470643 c17.qxd 3/8/04 11:29 AM Page 576
576 Chapter 17
One method of calculating frequency would be to take the length of time indicated by the historical data and divide it by the number of times the customer made a purchase. So, if the catalog data goes back 6 years and a customer made a single purchase, then that frequency would be once every 6
years.
Although simple, this approach misses an important point. Consider two customers:
■■
John made a purchase 6 years ago and has received every catalog since then.
■■
Mary made a purchase last month when she first received the catalog.
Does it make sense that both these customers have the same frequency? No.
John more clearly has a frequency of no more than once every 6 years. Mary only had the opportunity to make one purchase in the past month, so her frequency would more accurately be described as once per month. The first point about frequency is that it should be measured from the first point in time that a customer had an opportunity to make a purchase.
There is another problem. What we really know about John and Mary is that their frequencies are no more than once every 6 years and no more than once per month, respectively. Historically, one observation does not contain enough information to deduce a real frequency. This is really a time to event problem, such as those discussed in Chapter 12.
Our goal here is to characterize frequency as a derived variable, rather than predict the next event (which is best approached using survival analysis). To do this, let’s assume that there are two or more events, so the average time between events is the total span of time divided by the number of events minus one, as shown in Figure 17.14. This provides the average time between events for the period when the events occurred.
There is no perfect solution to the question of frequency, because customer events occur irregularly and we do not know what will happen in the future—
the data is censored. Taking the time span from the first event to the most recent event runs into the problem that customers whose events all took place long ago may have a high frequency. The alternative is to take the time since the first event, in essence pretending that the present is an event. This is unsatisfying, because the next event is not known, and care must be taken when working with censored data. In practice, taking the number of events since the first event could have happened and dividing by the total span of time (or the span when the customer was active) is the best solution.
470643 c17.qxd 3/8/04 11:29 AM Page 577
Preparing Data for Mining 577
Purchase
First
Current
Contact
Time
Time
A
B
C
D
Frequency is 2 / (C – A), but does not include
time after C
Frequency is 3 / C, but does not include time after C
Frequency is 3 / (D – A), but data is censored
Frequency is 3/D, but data is censored
Figure 17.14 There is no perfect way to estimate frequency, but these four ways are all reasonable.