TAYKO SOFTWARE CATALOGER

Tayko.xls is the dataset for this case study.

Background

Tayko is a software catalog firm that sells games and educational software.
3
 It started out as a software manufacturer and later added third-party titles to its offerings. It has recently put together a revised collection of items in a new catalog, which it is preparing to roll out in a mailing.

In addition to its own software titles, Tayko’s customer list is a key asset. In an attempt to expand its customer base, it has recently joined a consortium of catalog firms that specialize in computer and software products. The consortium affords members the opportunity to mail catalogs to names drawn from a pooled list of customers. Members supply their own customer lists to the pool and can “withdraw” an equivalent number of names each quarter. Members are allowed to do predictive modeling on the records in the pool so they can do a better job of selecting names from the pool.

The Mailing Experiment

Tayko has supplied its customer list of 200,000 names to the pool, which totals over 5 million names, so it is now entitled to draw 200,000 names for a mailing. Tayko would like to select the names that have the best chance of performing well, so it first conducts a test prior to withdrawing its full allotment of names – it draws 20,000 names from the pool and does a test mailing of the new catalog.

This mailing yielded 1,065 purchasers, a response rate of 0.05325, or 5.325%. Average spending was $102.56 per purchaser (i.e. only those who bought), or $5.46 per catalog mailed (i.e. all 20,000 names whether they purchased or not). To optimize the performance of the data mining techniques, it was decided to work with a stratified sample that contained equal numbers of purchasers and nonpurchasers. For ease of presentation, the dataset for this case includes just 1,000 purchasers and 1,000 nonpurchasers, an apparent response rate of 0.5, or 50%. Therefore, after using the dataset to predict who will be a purchaser, we must adjust the purchase rate back down by multiplying each case’s “probability of purchase” by 0.05325/0.5, or 0.1065.

Data

There are two response variables in this case. 
Purchase

indicates whether or not a prospect responded to the test mailing and purchased something. 
Spending
 indicates, for those who made a purchase, how much they spent.

The overall procedure in this case will be to develop two models. The first model will be used to classify records as Purchase or No purchase. This model will be used to identify the most likely purchasers in the consortium’s database. The second model will be used for those cases that are classified as having made a purchase and will predict the amount purchasers spend. By identifying the most likely responders and the highest spenders Tayko hopes to identify names from the consortium’s pool that will increase profit above a random draw of names.

The “Codes” tab in the spreadsheet provide a description of the variables available in this case. The Partition variable is used because we will be developing two different models in this case, and we need to ensure that records are in the same partition for each of the two models we will build.

Assignment Submission

The first tab in your submission workbook should be a tab labeled “Answers”. It should provide an answer and/or a reference to the name of the tab(s) which support your answer to each question and section below. This analysis will result in dozens of tabs in your submission sheet. It is important that the tabs are organized and answers reference specific tab names. Color coding tabs that are used in answers is very helpful.

Disorganized Excel workbooks will be returned ungraded.

Assignment Details

1. Each catalog costs $2 to mail (including printing, postage, and mailing costs).

a. Based on this cost and the response rate and average spend provided above, estimate the gross profit that the firm could expect from the remaining 180,000 names if it selected them randomly from the pool.

b. This is equivalent to the “naïve rule”. What does it tell us?

2. Using the “All Data” tab, which has both responders and non-responders, the first step is to develop a model for classification of a customer as a purchaser or nonpurchaser. This will identify the most likely responders to a mailing.

a. Partition the data into training data on the basis of the
Partition
variable, which has 800 training values (t), 700 validation values (v), and 500 test values (s) assigned randomly to cases.

b. Run a logistic regression model using all variables in the dataset. Exclude Spending as it is a dependent variable. In addition, use the feature selection option to select the best subset of variables. Using the best subset output, identify the best set of predictors to classify the data into purchasers and nonpurchasers. You will need detailed output for future steps so make sure to check the detailed output option for each of the partitions in the scoring tab.

3. Using the “Purchasers only” tab, the second step is to develop a model for predicting spending among the purchasers. This will identify the highest spenders.

a. Partition this dataset into training, validation, and test partitions on the basis of the
Partition
variable.

b. Develop models for predicting spending, using:

i. Multiple linear regression using best subset selection process.

ii. Regression trees using the full tree and best-pruned tree options.

c. Choose one model on the basis of its performance with the validation data. What is

your rationale for choosing the model you chose?

4. You now have developed the two models you need to identify the best names in the consortium’s pool. The following steps combine the two models to identify the names with the highest potential profit based on your modelling.

In Step 2a above, you partitioned the “All Data” tab. To start out, you’ll need the test data partition. Return to the STDPartition tab for your logistic regression created in Step 2a above. Make a copy of the
Partitioned Data
section of this sheet to work with, since we will be adding analysis to it. Only the last 500 records of the
Partitioned Data
section are test records, so delete the first 1,500 records, retaining the column headers for later use in scoring. Call this copy “Score Analysis”. Note that this test data partition includes both purchasers and nonpurchasers.

a. Copy the predicted probability of success (PostProb: 1) column from the “LogReg_TestScore” tab (there may be a number appended to the name) created when you ran the best subset logistic regression model in Step 2b to the “Score Analysis” sheet. You can ensure your rows from the step above align with what you copy in this step by moving the Record ID columns adjacent to each other and doing a quick check to make sure they match.

b. You now need predicted spending for the 500 records in the test partition. You will score the “Score Analysis” tab using the predictive model you identified in Step 3c above.

i. In the “Score Analysis” tab re-run the predictive model you identified in Step 3c.

ii. You will not need to partition the data because you are scoring the test partition.

iii. In the scoring option box, you will not need any reports. You will select the Score New Data In Worksheet option and match your variables in the dialog box.

iv. The output tab will have your predicted spend for the 500 test partition observations.

v. Copy the 500 predicted values from the “NewScore” tab to the “Score Analysis” tab ensuring that the rows align.

c. Arrange the following columns so that they are adjacent:

i. Predicted probability of purchase (PostProb: 1)

ii. Actual spending (dollars)

iii. Predicted spending (dollars)

d. Add a column for “adjusted probability of purchase” by multiplying “predicted probability of purchase” by 0.107. This is to adjust for oversampling the purchasers 

e. Add a column for expected spending (adjusted probability of purchase × predicted spending).

f. Sort all records on the “expected spending” column in descending order.

g. Create a Cumulative Expected Spending column and calculate cumulative expected spend over the 500 records.

h. Calculate the average expected spending and create and populate a cumulative average spend column over the 500 records.

i. Calculate cumulative lift by dividing the cumulative expected spend by the cumulative average spend for each record.

j. Using this cumulative lift curve, estimate the gross profit that would result from mailing to the 180,000 names on the basis of your data mining models. (Hint: 180,000 is what percentage of the 5 million record database? Apply that to the 500 record test file.)

Note: Although Tayko is a hypothetical company, the data in this case (modified slightly for illustrative purposes) were supplied by a real company that sells software through direct sales. The concept of a catalog consortium is based on the Abacus Catalog Alliance. Details can be found at
this website
.

3Resampling Stats, Inc. 2006; used with permission.