Chapter 7 exercise

1. Below are three pairs of customers residing on the ACME Direct database. Who, from each pair, is most likely to order a nature video series from ACME Direct? If you cannot make a selection, please explain. If you feel you need more information to make an informed decision, state what you need. If you need to further manipulate the data to answer the question, do so.

a) Customer Date of Entry onto the Database

M. Jones 8/17/93

R. Smith 9/22/98

b) Customer Total Number of Video Products Paid Ever

S. Samuel 14

D. Hawkins 5

Total # of Total # of

c) Customer Products Paid Ever Promotions Ever

T. Thomas 13 170

A. McGraw 4 40

2. ACME Direct test mailed a sample of 10,000 customers a promotion for a brand-new book club on 9/15/98. You will be e-mailed a portion of this sample in Excel by PDD. What the file should look like is shown on the next page. All characteristics were frozen point-in-time of the mailing. In addition, the sample has been updated with who responded (ordered) to the book club offer. This sample only contains 26 of the 10,000 names test mailed. The percent of customers joining the club was an astounding 50%.

a) Using Excel determine the correlation coefficient of joining the club (“ordering”) to “total promotions sent.”

b) Using Excel determine the correlation coefficient of joining the club (“ordering”) to “total paid orders.”

c) Are there any problems you can think of with respect to using “total promotions sent” on its own as a predictor of ordering?

d) Create the ratio variable “total paid/total promotions sent” in Excel.

e) What is the correlation of “ordering” with the new ratio variable constructed in part d?

f) How does the correlation calculated in part e compare to the correlations calculated in parts a and b? Explain.

g) Determine the order rate for each cell by tallying the total names falling into each cell and denoting how many of them ordered. For example, only one person in the sample had “Total Products Paid = 0” and “Total Promotions Sent = 0-20” and this one person was not an order. Therefore, the response rate for this cell was 0.00%.

h) If your breakeven is 60% or better for this new book club which cells in the above cross tab could you profitably promote?

Excel File to be e-mailed to you for Question #2, Chapter 7

3. Assume the universe the sample was drawn from for the cross-tabulation analysis done in class (Figure 7.6, Chapter 7) represented 1,256,479 names (Customers with 5+ paid orders in the past 24 months). During the assessment of this cross-tabulation, we noted in class that the product manager identified five cells/groups of names she could profitably promote and meet her break-even criteria of 3.00%. If the product manager decides to promote these five cells/groups of names from this universe of 1,256,479 names next month, how many names can she expect to be promoting?

4. ACME Direct is about to launch a new book title called “Famous Gardens of Europe.” This new book was test promoted to a random and representative sample of 10,000 names from the entire database of 8,500,000 names. 466 names out of the 10,000 ordered the book. The table below is a cross tabulation of two data elements, “Total Products Paid” and “Months Since Last Order,” created on this sample. Displayed in each cell is the total number of orders falling into the cell / total number of names falling into each cell = response rate.

Assume the product manager in charge of this promotion has determined in order to generate 8% profit after overhead (his goal for this campaign), he must achieve at least a 4.5% response rate.

a) Determine response rates for the three empty cells.

b) Which cells can the product manager profitably promote?

c) Based on your decision to part b, how many names will this represent from the entire database of 8,500,000.

d) If the product manager promotes the names meeting his profit criteria from the entire database (as identified in part c), how many orders in total can he expect to receive?

5. Examining the last three actions for two customers, Smith and Jones, who would you choose to promote for an upcoming promotion?

Customer Latest Promotion -2 Latest Promotion -1 Latest Promotion

Smith Order & Pay Order & Return Not Order

Jones Not Order Order & Return Order & Pay

6. ACME Direct is a direct marketer of books, music, videos, magazines and gift items. Assume the ACME Direct database in total contains 25,000,000 names. Explain whether the following data relationships on ACME Direct’s customer file have a positive, negative or zero correlation.

a) The number of books each customer purchased in the past vs. the number of videos each customer purchased in the past.

b) Total dollars credited (paid) by each customer vs. the total number of orders generated by each customer.

c) The probability that a customer will order a cookbook in the future vs. the total number of past orders generated for that customer.

d) The probability that a customer will order a heavy metal package vs. the customer’s age.

e) The probability that a customer will order a classical music package vs. the customer’s income level.

f) The probability a customer will pay for a future order vs. the number of times they have been delinquent with past payments.

g) The time elapsed in months since a customer’s last order vs. the probability a customer will order from an upcoming catalogue promotion.

h) The total number of classical music packages bought by each customer vs. the customer’s age.

7. The following table lists the midterm and final exam scores for seven students in the “Database Modeling” class.

Midterm Score
79

95

81

66

87

94

59

Final Exam Score

85

97

78

76

94

84

67

a) Do you expect the midterm and final exam scores to be positively or negatively related?

b) Plot a scatter diagram for these data in Excel using the ChartWizard feature. By looking at the scatter diagram, do you expect the correlation coefficient between these two variables to be close to zero, 1 or –1?

c) Referencing the slides from class, manually calculate the correlation coefficient showing your work. Is the value of r consistent with what you expected in parts (a) and (b)?

Total Products

Total Promotions Sent