7.4.1 Pivoting and aggregating transactional data at a customer level

The first steps of the data preparation process included a series of IBM SPSS Modeler Derive nodes for the construction of new attributes denoting categorization information for each transaction. More specifically, each transactional record was decoded and categorized according to date/time zone (weekday–weekend, morning–afternoon), product group, store, and payment type. Although in the real world product grouping would have been done by mapping the Universal Product Code (UPC) of each purchased item with a reference (lookup table) containing the product grouping hierarchy, for simplification, we assume in our case study that the first two characters of the ITEM_CODE field indicate the relevant product group.

At first, the weekday information was extracted from the transaction’s timestamp field (TRANS_DATE) using the Modeler datetime_weekday() function. Then, with the datetime_hour() function, the transaction hour has been extracted and grouped into time zones. This was achieved by using a Modeler Flag Derive node as shown in Figure 7.1. All transactions that took place before 4 p.m. were flagged as morning transactions and the rest as afternoon.

After recoding the information, it was time for pivoting the data to summarize the purchase amount (field AMOUNT) per product group, date/time zone, payment type (card/cash), and store. A series of Restructure nodes were used which created numerical fields based on the values of the grouping attributes.

The Restructure node for pivoting based on the payment type is displayed in Figure 7.2.

Similarly, a series of Restructure nodes were also applied to generate a set of numeric flag fields with a value of 1 for our categorizations. This way, in the subsequent aggregation, the sum of the 1s simply denotes the number of transactions per store, date/time period, etc.

The aggregation was carried out in two steps in order to count the transactions per customer. The first aggregation summarized the numeric fields per transaction/invoice (field INVOICE_NO). In the second step, the invoice information was further aggregated at a cardholder/card id (field CARD_ID) level as shown in Figure 7.3.

The number of aggregated records per card ID designates the number of order transactions (invoices) in the period examined.

The total spending amount and the sum of spending amount per store, product group, date/time zone, and payment type were computed for each cardholder. Similarly, the aggregated fields summarized customer behavior in terms of the number of transactions. The most recent transaction date was also captured as the maximum of all transaction dates (maximum of TRX_DATE field). This field will be used later for the calculation of the recency for each customer.

The demographics, listed in Table 7.6, were then retrieved and joined with the purchase data using the card id (field CARD_ID) as the key for merge. A Modeler Merge node had been used and an inner join had been applied as shown in Figure 7.4. The inner join merge retained only customers with demographics since for the rest of the customers, no contact data were available and hence any interaction and marketing action was not feasible.

7.4.2 Enriching customer data and building the customer signature

The summarized fields were then used for the construction of informative KPIs which meant to be used as inputs in the subsequent data mining applications. The derived attributes included:

• Customer tenure

  Based on the recorded registration date, the time in months of the relationship of the cardholder (TENURE field) with the retailer has been calculated (months on books).

• Relative spending

  To derive the relative spending for the categorizations of the transactional records (by product group, date/time zone, store, and payment type), each individual field was divided with the total spending amount. A Modeler multiple Conditional Derive node was applied for the calculation as shown in Figure 7.5. The derived fields were named with a PRC_ prefix.

• Monthly average purchase amount per product group

  To compute the monthly average purchase amount per product group, each spending amount was divided with 5 (months) for old customers or with the tenure months for new cardholders. Remember that the transactional data retrieved covered a 5-month period. The derived fields have been labeled with an AVG_ prefix.

• Ratio of transactions per date/time zone, store, and payment type

  In a way similar to the one applied for calculating the relative spending, the ratio of the number of transactions per date/time zone, store, and payment type has been derived. A Modeler multiple Conditional Derive node has been applied and each individual field was divided with the total number of transactions.

• Average basket size

  The average basket size indicates the average spending amount per transaction (in our example order/invoice). The logic of this field is to discern those customers that spend a lot at each visit to the stores (rare visits perhaps yet it remains for investigation after calculating the frequency of the visits) from those which tend to check out small baskets. Through a Conditional Derive node (Figure 7.6), the average basket size attribute (AVG_BASKET_SIZE field) was derived as the ratio of total spending amount to the total number of transactions in the period examined.

• Basket diversity

  The next derived field’s scope (NUM_PRODUCT_GROUPS field) was to identify customers who tend to purchase items from multiple product groups. The number of distinct product groups with purchases has been counted for each customer. The values of this attribute range from 1 for customers who only bought products from a single group to 7 for super buyers who have bought something from every group.

• Flags of product groups with purchases

  A series of flag (binomial) fields have also been created indicating spending for each product group. The derived flags have a FLAG_ prefix. They are ready to be used as inputs in Association modeling for market basket analysis and for the identification of products that tend to be purchased together.

• Recency

  The RECENCY field was the first RFM component attribute derived. It denotes the time (in days) from the last transaction (TRANS_DATE_Max field) to the analysis as-of (reference) date (June 1, 2012). A Modeler Derive node and the date_days_difference() function was applied for the computation and is displayed in Figure 7.7.

• Frequency

  The F part of the RFM stands for the frequency of transactions. It was calculated as the monthly average number of order transactions with a Modeler Conditional Derive node as shown in Figure 7.8.

• Monetary value

  The M component of RFM stands for the monetary value, and it was derived as the monthly average purchase amount by dividing the total spending amount with 5 (months) or with the tenure months for new customers as shown in Figure 7.9.

7.6.1 Grouping customers according to their value

The value-based segmentation was applied to the customers with a loyalty card, and it was based on the monthly average spent amount of each customer (the derived MONETARY field). Thus, all cardholders were ranked according to their purchases. The ordered records were grouped (binned) to chunks of equal frequency referred to as quantiles. These tiles were then used to construct the value segments.

In value-based segmentation, the number of binning tiles to be constructed depends on the specific needs of the organization. The derived segments are usually of the following form: highest n%, medium–high n%, medium–low n%, and low n%. In general, it is recommended to select a sufficiently rich and detailed segmentation level, especially in the top groups, in order to discern the most valuable customers. On the other hand, a detailed segregation level may not be required in the bottom of the value pyramid.

The segmentation bands selected by the retailer were the following:

1. CORE: bottom 50% of customers with lowest purchase amount
2. BRONZE: 30% of customers with medium–low purchase amount
3. SILVER: 15% of customers with high purchase amount
4. GOLD: top 5% of customers with highest purchase amount

Since customers with no purchases in the 5-month period examined were excluded from the procedure, a fifth segment not listed but implied is the one comprised of inactive customers.

Before beginning any data mining task, it is necessary to perform a health check in the data to be mined. Initial data exploration typically involves looking for missing data and checking for inconsistencies, identifying outliers, and examining the field distributions with basic descriptive statistics and charts like bar charts and histograms. IBM SPSS Modeler offers a tool called Data Audit that performs all these preliminary explorations and allows users to have a first look at the data, examine them, and spot potential abnormalities.

A snapshot of the results of the Data Audit node applied to the modeling file is presented in Figure 7.10.

The binning procedure for value-based segmentation is graphically depicted in Figure 7.11.

The Modeler stream used for the value (and RFM) segmentation is presented in Figure 7.12.

A Binning node was used for grouping cardholders in groups of equal frequency. In order to be able to discern the top 5% customers, binning into vingitiles, 20 tiles of 5% each, was selected as shown in Figure 7.13.

The “tiles (equal count)” was selected as the binning method, and customers were assigned to vingitiles according to their monthly average purchase amount by specifying the MONETARY field as the bin field. The derived bands have then been regrouped with a Set Derive node, in order to refine the grouping and construct the VBS field which assigns each customer to one of the desired segments: Core, Bronze, Silver, and Gold. The conditions for regrouping are displayed in Figure 7.14.

7.6.2 Value segments: exploration and marketing usage

The next step before starting to make use of the derived segments was the investigation of their main characteristics.

The initial goal of this segmentation was to capture the assumed large-scale differences between customers, in terms of spending. Thus, marketers begun to investigate this hypothesis by examining the contribution of each segment to the total sales (purchase amount). Table 7.8 presents the percentage of customers and the percentage of the total sales amount for the 5-month period examined by value segment.

The above table shows that a substantial percentage of the total sales comes from a disproportionately small number of high-value users. Almost half of the retailer’s sales arise from the top two value segments. The 20% of the most valuable customers account for about 45% of the total amount spent at the stores of the retailer. On the other hand, low-value users which comprise the mass (bottom 50%) segment provide a moderate 23% of the total sales. The comparison of mean spending also underlines the large-scale differences among segments. The mean sales value for the top 5% segment is almost 17 times higher than the one for the bottom 50%. These findings although impressive are not far from reality. On the contrary, in other industries, in banking, for example, the situation may be even more polarized, with an even larger part of the revenue originating from the top segments of the value pyramid.

In a next step and by using simple descriptive statistics and charts, value segments have also been profiled in terms of their demographics and usage.

The business benefits from the identification of value segments are prominent. The implemented segmentation can provide valuable help to the marketers in setting the appropriate objectives for their marketing actions according to each customer’s value. High-value customers are the heart of the organization. Their importance should be recognized and rewarded. They should perceive their importance every time they interact with the enterprise. Prevention of defection of these customers is vital for every organization. Identification of valuable customers at risk of attrition should trigger an enormous effort in order to avoid losing these customers to competitors. For medium- and especially low-value customers, marketing strategies should focus on driving up revenue through targeted cross- and up-selling campaigns in order to begin to approach the high-value customers.

7.7.1 RFM basics

RFM analysis is a common approach for understanding and monitoring consuming behaviors. It is quite popular, especially in the retail industry. As its name implies, it involves the calculation of three core KPIs, recency, frequency, and monetary value which summarize the corresponding dimensions of the customer relationship with the enterprise. The recency measurement indicates the time since the last purchase transaction. Frequency denotes the number and the rate of the purchase transactions. Monetary value measures the purchase amount. These indicators are typically calculated at a customer (cardholder) level through simple data processing of the recorded transactional data.

RFM analysis can be used to identify the good customers with the best scores in the relevant KPIs, who generally tend to be good prospects for additional purchases. It can also identify other purchase patterns and respective customer types of interest, such as infrequent big spenders or customers with small but frequent purchases who might also have sales perspectives, depending on the market and the specific product promoted.

In the retail industry, the RFM dimensions are usually defined as below:

• Recency: time (in units of time, typically in days or in months) since the most recent purchase transaction or shopping visit.
• Frequency: total number of purchase transactions or shopping visits in the period examined. An alternative, and more “normalized” approach that also takes into account the tenure of the customer, calculates frequency as the average number of transactions per unit of time, for instance, the monthly average number of transactions.
• Monetary value: the total or the average per time unit (e.g., monthly average value) amount of purchases within the examined period. According to an alternative yet not so popular definition, the monetary value indicator is defined as the average transaction value (average amount per purchase transaction). Since the total value tends to be correlated with the frequency of the transactions, the reasoning of this alternative definition is to capture a different and supplementing aspect of the purchase behavior.

The construction of the RFM indicators is a simple data management task which does not involve any data mining modeling. It does involve through a series of aggregations and simple computations that transform the raw purchase records into meaningful scores. In order to perform RFM analysis, each transaction should be linked with a specific customer (card ID) so that each customer’s purchase history can be tracked and traced over time. Fortunately, in most situations, the usage of a loyalty program makes the collection of “personalized” transactional data possible.

RFM components should be calculated on a regular basis and stored along with the other behavioral indicators in the organization’s mining datamart. They can be used as individual fields in subsequent tasks, for instance, as inputs, along with other predictors, in cross-selling models. They can also be included as clustering fields for the development of a multiattribute behavioral segmentation scheme. Typically, they are simply combined to form a single RFM measure and a respective cell segmentation scheme.

The RFM analysis involves the grouping (binning) of customers into chunks of equal frequency, named quantiles, in a way similar to the one presented for value-based segmentation. This binning procedure is applied independently on the three RFM component measures. Customers are ranked according to the respective measure, and they are grouped in classes of equal size. For instance, the break in four groups results quartiles of 25% and the break in five groups, quintiles of 20%. In the case of binning into quintiles, for example, the R, F, and M measures are converted into rank scores ranging from 1 to 5. Group 1 includes the 20% of customers with the lowest values and group 5 the top 20% of customers with the top values in the corresponding measure. Especially for the recency measure, the scale of the derived ordinal score should be reversed so that larger scores indicate the most recent buyers.

The derived R, F, and M bins become the components for the RFM cell assignment. These bins are combined with a simple concatenation to provide the RFM cell assignment. Customers with top frequency, recency, and monetary values are assigned to the RFM cell 555. Similarly, customers with average recency (quintile 3), top frequency (quintile 5), and lowest monetary values (quintile 1) form the RFM cell 351 and so on.

The aforementioned procedure for the construction of the RFM cells is illustrated in Figure 7.15.

When grouping customers in quintiles (groups of 20%), the procedure results a maximum of 5 × 5 × 5 = 125 RFM cells as displayed in Figure 7.16.

This combination of the R, F, and M components into RFM cells is widely used; however, it has a certain disadvantage. The large number of the derived cells makes the procedure quite cumbersome and hard to manage. An alternative method for segmenting customers according to their RFM patterns is to use the respective components as inputs in a clustering model and let the algorithm reveal the underlying natural groupings of customers.

7.10.1 Designing the cross-selling model process

The decided modeling process is outlined in the following paragraphs.

7.10.1.4 Time periods and historical information required

A 5-month “snapshot” of behavioral data, covering an observation period from January 1, 2012, to May 31, 2012, was retrieved and analyzed to identify the characteristics of those accepted the offer. The test-campaign approach may have its drawbacks in terms of required resources and delay in collecting the required response data, but it is more straightforward in regard to data preparation for modeling. No latency period was reserved and there was no need to ensure that predictors don’t overlap with the target event. And above all, the model is trained on cases which had gone through the exact customer interaction planned to be launched at a larger scale.

An outline of the mining procedure followed is presented in Figure 7.19.

7.11.1 Preparing the test campaign and loading the campaign responses for modeling

Initially, a random sample of customers of the modeling file has been drawn for inclusion in the test campaign. A Sample node has been applied to customers without purchases at the House department, and the campaign list had been stored at an external file. Upon conclusion of the pilot campaign, response data had been collected and stored in a dedicated file. The model had to be trained on known response. Therefore, the relevant data had been retrieved and merged with the rest of the modeling data. An inner join had been applied so that only customers who participated in the campaign were retained for model training. Figure 7.21 presents the Merge node used for joining the two data sources with the CARD_ID field used as the key for merge.

7.11.2 Applying a Split (Holdout) validation: splitting the modeling dataset for evaluation purposes

The loading of response data defined the final modeling population as well as the target attribute, the flag field CAMPAIGN_RESPONSE indicating customers who accepted the offer and used the discount voucher (value “T”). However, not all campaign participants took part in the model training. A random sample of them was left out (Holdout sample) to be used for model evaluation. As already stressed before, using the same data for training and testing the model can lead to optimistic measures of accuracy. Therefore, a standard procedure for ensuring realistic model evaluation is to apply a Split (Holdout) validation so that the model is evaluated on unseen instances. In our case study, a Partition node was applied to split the modeling dataset into Training and Testing datasets through random sampling as shown in Figure 7.22. The Training partition size was set to 75%. Therefore, the 75% of the 13 074 campaign participants were used for the training of the model. The remaining 25% were held out to be used for assessing the model accuracy.

Additionally, a random seed was specified in order to “stabilize” the underlying random sampling procedure and ensure the same partitioning on every model run.

7.11.3 Setting the roles of the attributes

The last column of Table 7.7 denotes the attributes used as predictors in the classification model. Obviously, the information regarding the offer acceptance, recorded in the CAMPAIGN_RESPONSE field, defined the model’s target. The role of each attribute in the model was set through a Modeler Type node (Figure 7.23). Predictors were assigned an input role (Direction In), the target attribute was assigned an output role (Direction Out), and fields omitted were given a None direction.

7.11.4 Training the cross-sell model

A series of classification models has been developed through an Auto Classifier node as shown in Figure 7.24. This node allows the training and the evaluation of multiple models for the same target attribute and set of predictors.

The generated models were initially assessed and ranked in terms of their Lift at the 2% percentile. Additionally, estimated revenue and cost information has been specified in order to enable the comparison of the generated models in terms of expected profit and ROI. By using the specified estimations for cost per offer/contact (50$), revenue if someone accepts the offer (200$), and the propensity scores derived by the models, Auto Classifier can compute the estimated maximum profitability along with the percentile where it occurs. By studying profitability curves, marketers can perform cost–benefit analysis, assess the models, and also decide on the optimal campaign size.

Three Decision Tree models and a Bayesian Network were selected for training (Figure 7.25). More specifically, the models built were CHAID, C5.0, C&R Tree, and a TAN (Tree Augmented Naïve Bayes) model with a feature selection preprocessing step to omit irrelevant predictors.

The parameters specified for each model are displayed in Table 7.9.

7.14.1 Value segmentation and RFM cells analysis

The RapidMiner process for both value segmentation and RFM analysis is shown in Figure 7.35. The upper part of the process implements the value segmentation and starts with a Repository Retrieve operator which loads the modeling dataset presented in Table 7.7.

The value segmentation is a simple discretization task, and it was performed by a Discretize by Frequency operator which was applied to the MONETARY attribute. Initially, cardholders were binned into 20 tiles (vingitiles) of 5% each as shown in Figure 7.36. The top 5% of customers with the highest purchases were assigned to the “range20” bin, while on the other end of the monetary pyramid, the bottom 5% of customers with the lowest purchases were assigned to “range1” bin.

The discretization converted the numeric MONETARY field into a nominal one. In order to retain the amount information as well, the original MONETARY field was replicated as MONETARY_NUM with a Generate Copy operator.

The value segmentation was refined with a Map operator which further grouped the 20 bins into the final four value segments:

• 1_CORE (Bottom 50%)
• 2_BRONZE (Medium–Low 30%)
• 3_SILVER (High 15%)
• 4_GOLD (Top 5%)

The importance of the derived segments was disproportionate to their sizes as shown in the pie chart in Figure 7.37 which depicts the share of each segment in the total purchase amount.

The lower part of the process builds the RFM segmentation scheme by using the same modeling dataset. The procedure is similar to the one applied for value segmentation and involved the discretization of the relevant numeric fields (RECENCY, FREQUENCY, MONETARY attributes) into five pools of 20% each (quintiles) through a Discretize by frequency operator (actually, due to lower dispersion and many value ties, the FREQUENCY attribute had to be binned into four bins of 25% each). Do you remember that the recency bins had to be inversed so that bin 5 would correspond to better (more frequent) customers? This task was carried out by a Map operator which aligned the RECENCY scale with the scale of the other two components.

Before concatenating the three RFM components, their nominal values of the form “range1” to “range5” were slightly modified. The “range” prefix which was assigned automatically by RapidMiner was trimmed with a Replace operator so that all values were of the form 1 to 5. The final step for RFM segmentation involved the concatenation of the individual R, F, and M values through a Generate Attributes operator and a concat() function as shown in Figure 7.38.

7.14.2 Developing the cross-selling model

The RapidMiner process file for the cross-selling model is shown in Figure 7.39.

The modeling data were initially loaded with a Repository Retrieve operator. Apparently, the model training requires instances with known outcome (purchase yes/no). Therefore, only customers who had participated in the test campaign were selected. Shoppers of the House department were filtered out with a Filter Examples operator. Then, in order to merge the modeling data with the campaign response data, a Join operator has been applied. The CARD_ID field was used as the key for merge and an inner join type was applied. Only customers present in both data sources, hence only customers who had participated in the test campaign, were retained.

A Select Attribute operator was then used to keep only the predictors and the target field (label field, in RapidMiner’s terminology) as listed in Table 7.7. The rest of the attributes were omitted and did not contribute in model building.

The role of each field in model building has been defined using a Set Role operator. Specifically, the CAMPAIGN_RESPONSE field, recording the campaign responses, was assigned a label (target) role for the subsequent cross-selling model. The rest of the fields kept their default regular role to participate as predictors in the classification model.

7.14.3 Applying a Split (Holdout) validation

The evaluation of the classification model is optimistic when based on the training dataset. In order to evaluate the model on a sample different than the one used for model training, a Split (Holdout) validation method has been applied through a Split Validation operator. This operator partitioned the modeling dataset into a training and a testing part of 70% and 30%, respectively, as shown in Figure 7.40.

The dataset was split with a stratified random sampling and a 0.7 split ratio. Therefore, 30% of the training instances were reserved for evaluation and didn’t participate in the model training. The sampling with stratification ensured that the distribution of the target field was maintained across both samples.

The Split Validation operator, contains two subprocesses as displayed in Figure 7.41.

The left subprocess corresponds to the training sample and covers the model training phase. Instead of a single model learner, a Bagging operator was applied in order to build a set of classifiers. This technique is explained thoroughly in the next paragraph. The right subprocess corresponds to the Testing dataset and includes all the evaluation steps.

7.14.4 Developing a Decision Tree model with bagging

A widely used ensemble method for combining multiple models in order to improve the classification accuracy, called bagging (bootstrap aggregation), has been applied in this case study. More specifically, five different samples with replacement have been chosen at random from the original training dataset, and a separate Decision tree model has been built for each sample. The Bagging operator used for this procedure is shown in Figure 7.42.

The average confidences option combines the five separate trees, and their average confidences are used for scoring.

The Decision Tree parameters specified are shown in Figure 7.43, and they are summarized in Table 7.10.

The execution of the RapidMiner process generated a bagged model, consisted of the five individual Decision trees. By browsing the trees and their branches from the root node down to their leaf nodes, we can see the attributes and the patterns which were associated with increased response. In each node, the lighter shade in the bar represents the proportion of buyers. The width of the bar represents the number of customers at each node. From a first, visual inspection of the trees, we can see an increased number of responders among frequent customers with increased diversity of purchases (purchases of distinct product groups). Customers with purchases at the Grocery, Childrenwear, Accessories, and Beauty departments, especially in the Downtown store, are also probable buyers. The first of the five Decision trees is presented in Figure 7.44 in Graph view.

7.14.5 Evaluating the performance of the model

The next step was to evaluate the performance of the bagged classifier. The Performance operator, placed in the testing subprocess of the validation, provided numerous performance metrics for assessing the classification accuracy of the model. Note that the Performance operator requires scored (labeled) instances; therefore, it was preceded by an Apply Model operator which classified each customer according to the model results. Also note that all evaluation measures are based on the 30% testing subsample.

The Confusion matrix of the ensemble classifier is presented in Figure 7.45. The overall accuracy (correct classification rate) was 79.19% since 3106 of the 3922 testing instances were classified correctly (2915 TN, True Negatives, and 191 TP, True Positives). The misclassification rate was 20.81% (134 FP, False Positives, and 682 FN, False Negatives).

The Precision of the model was 58.77%. It denotes the percentage of the predicted positives which were actual positives (TP/TP + FP). The Recall measure (true positive rate or sensitivity) was 21.88%. It denotes the percentage of actual positive instances predicted as such (TP/TP + FN). The F measure is a measure generated by combining Precision (a measure of exactness) and Recall (a measure of completeness) in a single metric. In our case, the F measure had a value of 31.89%.

The 325 customers predicted as responders are those with the highest response propensities, and they comprise the 8.3% top percentile. The percentage of total buyers appearing in the top 8 percentile rises to 21.88% (equal to the recall measure). A random 8% sample would have captured 8% of the total responders. Hence, the Lift at this percentile was 2.64 (21.88/8.3). The Lift further increases at a value of 3.4 at the 2% percentile (although not presented here a detailed Lift curve can easily be plotted using the Create Lift Chart operator).

The ROC curve plots the model’s true positive rate in the Y-axis against the false positive rate in the X-axis: in other words the proportion of positive instances in the Y-axis (percentage of actual responders) against the proportion of misclassified negative instances in the X-axis (percentage of nonresponders) at different propensity cutoffs and samples. The ROC curve for the bagged classifier is depicted in Figure 7.46.

It shows a steep incline at the left of the X-axis, hence at higher model propensities, indicating acceptable model performance. The AUC metric (Area Under the ROC Curve measure) was 0.755.

Since the bagged model presented satisfactory predictive performance according to all evaluation metrics examined, the next step was to deploy it on new instances to target the campaign.

7.14.6 Deploying the model and scoring customers

The final step of the procedure was the deployment of the ensemble model on customers who hadn’t participated in the test campaign. These customers were filtered with a Set Minus operator which discarded the participants of the pilot campaign from the modeling dataset. Then, the model was applied, through an Apply Model operator, to customers who hadn’t yet visited the House department. The respective RapidMiner process is displayed in Figure 7.47.

Three new, model derived fields were created after the model deployment: the model prediction (field prediction(CAMPAIGN_RESPONSE) with values T/F), the estimated likelihood for nonresponse (confidence(F) field), and the response propensity, that is, the estimated likelihood for response (confidence(T) field). The values of the confidence fields range between 0 and 1. A screenshot of the prediction fields for a sample of customers is shown in Figure 7.48.

Customers were finally ranked according to their response propensities, and the cross-selling mailing list was constructed, including those with the highest likelihood to accept.

7.15.1 Using the Classify Wizard to develop the model

To build a classification model in Data Mining for Excel, we have to use the simple steps of the Classify Wizard. The modeling dataset includes those customers that had been involved in the test campaign. As outlined before, old shoppers of the House department had already been filtered out. The target field (CAMPAIGN_RESPONSE) records the campaign responses.

In the first step of the Classify Wizard, the source data for training the model has been selected as shown in Figure 7.49. In this example, a data range of the active datasheet was specified as the model dataset, although in general, data can also be loaded using an external data source and/or an SQL query.

The predictors (“Inputs”) and the target field (“Column to analyze”) were then selected as shown in Figure 7.50. The complete list of predictors is presented in the data dictionary shown in Table 7.7.

7.15.2 Selecting a classification algorithm and setting the parameters

Two different Decision Tree models were trained, using two different attribute selection methods (“Score methods”), the default BDE (Bayesian Dirichlet Equivalent with Uniform prior) and the Entropy. The algorithms and the respective parameters were set through the “Parameters…” menu as shown in Figure 7.51.

The default “Complexity” value, based on the number of 10+ used inputs, was 0.9. It was relaxed to 0.5 to yield a larger tree. The default “Split method” of “Both” was applied. This method produces optimal groupings of the inputs, combining multiway and binary splits. Finally, a “Minimum Support” value of 50 was specified, setting the minimum acceptable size of the leaf nodes to 50 records.

7.15.3 Applying a Split (Holdout) validation

A Split (Holdout) validation method was applied in the next step of the Classify Wizard. The percentage of random, holdout test cases was set to 30% as shown in Figure 7.52. The rest 70% of the data was used for the development of the model.

In the last step of the wizard, the created mining structure and the model were stored (Figure 7.53). The testing (holdout) part of the dataset was stored in the created mining structure, enabling its use in the subsequent validation.

7.15.4 Browsing the Decision Tree model

The two derived Decision Tree models yielded comparable results in terms of discrimination as we’ll see in the next paragraph. The dependency network of the BDE tree is presented in Figure 7.54.

The Dependency network presents the inputs retained in the model. It also provides a graphical representation of the strength of their relationships, based on the split score. Each node represents one attribute, and each edge represents the relationship between two attributes. Heavier lines designate stronger relationships. In our model, the diversity of purchases denoted by the number of distinct product groups with purchases and the frequency of purchases were proven significant predictors. Visits to a specific store (downtown store) as well as previous purchases of women apparel, childrenwear, and groceries also appeared to be related with the campaign response.

The developed Decision Tree model is presented in Figure 7.55. By examining the tree branches, stemming from the root node down to the terminal, the leaf nodes, we can identify the characteristics associated with increased response and the profile of responders. In each node, a darker background color denotes increased concentration of buyers.

Customers with diverse buying habits, especially those with purchases of at least four distinct product groups, presented an increased percentage of responders. Increased concentration of responders was also found among visitors of a specific store (downtown store) and among frequent customers and buyers of Groceries and Childrenwear.

7.15.5 Validation of the model performance

The model validation was performed using the Classification Matrix wizard and the Accuracy Chart wizard of the Data Mining for Excel.

The Classification Matrix wizard produces a confusion (misclassification) matrix to summarize the accuracy and the error rate of the classification model. In the first step of the wizard, the entire structure was selected for validation, in order to evaluate and compare the two Decision Trees developed as shown in Figure 7.56.

The target field as well as the desired format of the results (in counts and/or percentages) was specified in the second step of the wizard as shown in Figure 7.57.

The model was selected to be tested on the testing (holdout) dataset which had been stored along with the entire mining structure (Figure 7.58). If the test data were from a different data source, then a mapping of the structure fields (“mining fields” in Excel terminology) to the external fields would be required. This mapping is done in the last step of the Classification Matrix wizard.

Table 7.11 presents the accuracy and the error rate of the two Decision Tree models based on the testing dataset. The two models performed equally well. They both achieved an accuracy of 80% with an error rate of 20%.

The confusion matrices for the BDE and the Entropy models are presented in Tables 7.12 and 7.13, respectively. The actual classes, in columns, are cross tabulated with the predicted classes. Both models presented a large false negative (FN) rate as they misclassified a large proportion of actual responders as nonresponders. Since the default propensity threshold for predicting an instance as a responder is 50%, the increased FN rate designates that the estimated response propensities were often below 50% among the actual responders. Does this mean that the model is not good? To answer that, we must ask a different question: does the model propensities rank well? Do they discriminate positives from negatives, responders from nonresponders? Are the actual responders assigned with higher estimated probabilities compared to nonresponders? The discrimination power of the model was examined with the Gains chart and the Accuracy Chart wizard.

The steps of the Accuracy Chart wizard are similar with those of the Classification Matrix wizard except from the definition of the target class which is required for producing Gains charts (Figure 7.59).

The Gains charts for the two models are presented in Figure 7.60.

Once again, the two models presented comparable performance. Table 7.14 lists the cumulative Gain %, that is, the cumulative percentage of responders among the top 20 propensity percentiles.

A random 2% sample would have captured 2% of the total responders. The ranking of customers according to the BDE model propensities raised the Gain % of the top 2% percentile to 6.81%. Hence, the Lift at this percentile was 3.4 (6.81/2).

Figure 7.61 presents the cumulative distribution of responders and nonresponders across the propensity percentiles. The plot suggests acceptable discrimination. The point where the maximum separation is observed was the 37% percentile. The maximum separation was 39.7%, and it is equal to the KS statistic.

7.15.6 Model deployment

Finally, the BDE model was deployed on new customers, who hadn’t participated in the test campaign. Using the “Query” wizard, the stored model was applied to the selected dataset. The model estimates that were selected to be derived included the predicted class (based on the 50% propensity threshold) and the prediction confidence, the estimated probability of the prediction (Figure 7.62).

The file of scored customers and the model derived estimates are shown in Figure 7.63.