The Amazon’s Best Test database for this assignment is available on Blackboard and should already be in the CIS 220 folder if you are using Microsoft Access through the virtual machine.
1
Simon Business School University of Rochester
CIS 220, Business Information Systems and Analytics
Fall 2020
Team Data Mining Assignment: Amazon’s Best
Assignment Due Sunday, November 15th, at 11:59pm
Optional Extra Credit: Due Sunday, November 15th, at 11:59pm
NOTE: The Amazon’s Best Test database for this assignment is available on Blackboard and should
already be in the CIS 220 folder if you are using Microsoft Access through the virtual machine.
Your time is better spent studying for Exam II than doing the optional extra credit. I would only do
this if you have time and interest. If you do the extra credit, your write-up (see notes at top of last
page) and Access database with a “Final” segmentation table and query must be submitted on
Blackboard. Also note that the extra credit is a separate submission from the regular assignment.
Background
The two keys to having a successful online business are generating traffic and monetizing it. Amazon has
been very successful at both. They have tens of millions of customers and they sell books and other
products to them, as well as Kindles and eBooks, as well as the products of other retailers. In this
hypothetical assignment we are considering the possibility of Amazon augmenting its monetization efforts
by promoting by email a set of product it calls “Amazon’s Best.” The data mining techniques they utilize
are similar to those that have been used by traditional book clubs for years. Note that the Amazon’s Best
program could promote any types of products, i.e., it’s not just limited to books.
The Publishing Industry
About 50,000 new book titles1 are published in the U.S. each year, giving rise to a $20 billion industry.
This industry is segmented into textbooks (27 percent of sales), tradebooks2 (21 percent), technical,
scientific, and professional books (21 percent), book club and other mail-order books (10 percent), mass-
market paperbound books (8 percent), and all other books (13 percent).
Book retailing in the 1970’s was characterized by the growth of U.S. chain bookstore operations, a trend
that was triggered by the development of shopping malls. Traffic in bookstores in the 1980’s was enhanced
by the spread of discounting. One of the driving forces behind more recent double-digit growth in book
retailing is the superstore concept. Generally situated near large shopping centers, superstores maintain
large inventories of 30,000 to 80,000 titles and employ well-informed sales personnel. Superstores are
putting intense competitive pressure on book clubs and mail-order firms as well as other retail outlets. In
response to these pressures, book clubs started looking at alternative business models that are more
responsive to their customers’ preferences.
Traditional Book Club Models
Historically, book clubs offered their readers continuity and negative-option programs that were based on
an extended contractual relationship between the club and its client. Under a continuity program, a reader
signs up for an offer of several books for a few dollars each (plus shipping and handling), and an agreement
to receive a shipment of one or two books each month thereafter. This is a “low maintenance” arrangement
1 Including new editions. 2 This is industry jargon for books sold in bookstores.
2
since a single contract guarantees a sequence of sales. It is most common for children’s books, where
parents are willing to delegate the right to make the selection to the book club. Then, much of the club’s
prestige depends of the quality of its selections. In a “negative option” program, readers get to choose
which and how many additional books they would receive, but the default option is that the club’s selection
will be delivered to them each month. The club informs them of the monthly selection and they are
specifically required to mark “no” on their order form if they do not want to receive it. Negative option
programs sometimes result in customer dissatisfaction and always give rise to significant mailing and
processing costs. In an attempt to reverse these trends and combat the success of superstores, some firms
are beginning to offer books on a positive option basis, but only to selected segments of their customer lists
that are deemed receptive to specific offers. Thus, book clubs are beginning to use data mining techniques
to work smarter rather than increase the number of mailings. They target individual consumers based on
data in their databases to select only those customers who are likely to be interested in their offers, and they
differentiate their offers across their customer population.
Predictive Modeling
One example of data mining is Doubleday Book & Music Clubs Inc., which recently started using the
results of predictive modeling to target its mailings. The company uses their database to identify good
customers early in their membership while cutting costs attributed to poor members. According to
Doubleday president Marcus Willhelm, “The database is the key to what we’re doing...We have to
understand what our customers want and be more flexible. I doubt book clubs can survive if they offer the
same 16 offers, the same fulfillment, to everybody.”
Doubleday’s predictive modeling looked at more than 80 variables during tests, including geography and
what types of books customers purchased. Three to five variables were eventually chosen as the model’s
basis. “A whole battery of tests were run,” Willhelm said. “The whole idea is to target subsets in the
membership files” of about three million names. “If a customer only buys two or three books a year, do
they need 16 catalogs? Maybe they only need to be sent eight. We look at profitability, not sales.” With
the use of data mining techniques, Doubleday is planning to switch to positive option plans that performed
well in its market tests.
Amazon’s Best
You have just started your summer internship at Amazon. While Amazon is pleased with the overall
performance of their book business, they have decided to not only make recommendations to a customer
when they come to Amazon’s website, but also to form a new business unit, Amazon’s Best, that emails
promotions to customers. Always careful to protect the customer experience, Amazon is initially trying the
Amazon’s Best promotion out on 11,000 of its customers. Amazon is concerned that customers may not
respond well to push marketing from them. While the Amazon’s Best business unit originally wanted to
send lots of offers to all customers (since the cost of an email is essentially zero), Amazon’s management
was concerned that customers would find this intrusive and decided to put in place a transfer price of $1 per
email offer to the new business unit to reflect the cost to Amazon of intruding on its customers, thereby
discouraging the unit from overwhelming customers with email offers. In order to be profitable, the
business unit would need to combine Amazon’s vast customer data with data mining techniques similar to
Doubleday. By only making offers to good prospects, the business unit should be able to generate a profit
even with the transfer price and without inundating customers with offers to the point they become
annoyed.
Typically with data mining, this process begins with a test phase. A sample of customers are selected from
the database and made an offer of a product. The responses to this offer are analyzed along with customers’
past purchase history and other data to determine how their purchase rates vary and what factors affect the
likelihood of purchase.
The Amazon’s Best business unit wishes to only make offers to Amazon customers who are likely to make
a purchase, since each email offer costs the business unit $1. The problem is how to predict who will
respond. Part of the answer lies in customers’ purchase history, which is stored in Amazon’s Best’s
database. Purchase history includes general information about how much money customers have spent in
3
the past (“Monetary”), how many purchases they have made in the past (“Frequency”) and how recently
they have purchased (“Recency”). For example, Amazon’s Best’s analysis has revealed that customers
who have made a purchase in the past 6 months are more likely to respond to its offers than those who
made no purchase over the same period. Taking advantage of this knowledge, Amazon’s Best may
increase its profitability by segmenting its customers into the two corresponding “Recency” segments and
making offers only to the first “Recency”-based segment. Similarly, customers may be segmented based
on all three ‘RFM” variables.3 More detailed purchase history data may be used to further refine the
customer segments, increase the accuracy of predictive modeling, and increase profitability. For example,
past purchasers of children’s books are likely to respond more positively to children book offers, yet they
are unlikely to purchase reference books.
Amazon’s Best thus develops a model of customer behavior and estimates it using the results of the test
phase. Then, customers are selected for the final phase based on their expected probability of purchase, the
profit generated by a sale, and the cost of making the offer. This final round of offers is referred to as the
roll phase.
Amazon’s Best Assignment4
The Amazon’s Best business unit is trying to decide which of its customers should be offered its latest
product it’s selected to promote, which is a book titled “The Art History of Florence.” Amazon’s Best has
already performed a test phase on a 1,000 customer sample, 80 of which purchased the book. Based on
these responses, Amazon’s Best wants to offer “The Art History of Florence” only to the remaining 10,000
customers that are in segments it expects to be profitable.
Amazon’s MIS department has produced a database, “Amazon’s Best Test.accdb,” of the 1,000 customers
and their responses to the offer in the test phase. This database consists of two tables: CUSTOMER and
CURRENT BOOK. Both tables have a single key, Account Number. The CUSTOMER table contains
summary information on the 1,000 customers’ past purchase history on an aggregate basis, on a category
by category basis, and on a book by book basis. Its structure is given below:
CUSTOMER Table
Field Name Description
Account Number Customer’s Account Number
Gender Customer’s gender: 1-male, 2-female
Recency Number of months since last purchase
Frequency Total number of purchases made by the customer
Monetary Total amount of money spent by the customer on Amazon’s Best books
Months Number of months the customer is on the house file
Children Category Number of purchases in the Children category made by the customer
Youth Category Number of purchases in the Youth category made by the customer
Cookbook Category Number of purchases in the Cookbook category made by the customer
Do-it-Yourself Category Number of purchases in the Do-it-Yourself category made by the
customer
Reference Category Number of purchases in the Reference category made by the customer
Art Category Number of purchases in the Art category made by the customer
Geographic Category Number of purchases in the Geographic category made by the customer
Italian Cooking 1 – if the bought the book “Secrets of Italian Cooking”, 0 – if not
Historical Atlas 1 – if the bought the book “Historical Atlas of Italy”, 0 – if not
Italian Art 1 – if the bought the book “Italian Art”, 0 – if not
The CURRENT BOOK table contains the results of the offers made in the test phase. Its structure is:
3 The most widely used segmentation criteria are indeed based on “RFM” – Recency, Frequency, and
Monetary. 4 It can be challenging to figure out how to do a segmentation. For this reason I recommend people work in
pairs for this part of the assignment.
4
CURRENT BOOK Table
Field Name Description
Account Number Customer’s Account Number
Art History 1 – if the bought the book “The Art History of Florence”, 0 – if not
Using the information in the above tables, Amazon’s Best wants you to develop a set of criteria for
deciding what customers should receive the offer for “The Art History of Florence”. Amazon will handle
Amazon’s Best fulfillment through their regular fulfillment operation. The business unit generates $7 of
profit for each book sold. Your task is to decide which customers should receive the offer.
Assignment (Due Friday, November 13th) If you have not recently completed the exercises, you
should review or do them before doing starting the assignment. If you can’t do the exercises, you will
not be able to do the assignment. Segment the customers by the Recency, Frequency, and Italian Art
variables, using the following breakdown (note that this breakdown leads to a total of 12 segments (be sure
you understand why)):
Recency: less than 10 months, 10 months or more
Frequency: 1, 2 – 4, 5 or more
Italian Art: 0 or 1
Be sure that your Segmentation table has a Segment # field. Which of the segments should receive the
offer for “The Art History of Florence”? What is the corresponding expected roll profit? NOTE: you can
copy and paste the query result into Excel and calculate the expected profit in Excel if you wish. Note that
since the virtual machine is a PC, you will need to use CTRL-C and V for copy and paste instead of
Command (which is what you use on a Mac). You should also verify you have 1000 customers, 80 of
whom purchased. If not, there is a problem with your segmentation, e.g., overlapping segments or
some customers not going in a segment. You should submit a write-up with your answer and the
Access database you used to come up with the answer. The example on the next page is a review of
one from lecture. You may find it helpful to review before doing the assignment.
If you are not doing the extra credit, you are done with the assignment.
Extra Credit (Due Sunday, November 15th) Come up with a segmentation of your own using whatever
variables you like and see if you can find a better strategy than the Recency, Frequency, and Italian Art one
in the assignment. What is your expected roll profit? To get full credit for the extra credit your expected
roll profit needs to be at least $900, which is not that hard. Getting one above $1400 is more challenging.
Again, you can use Excel to calculate the expected profit. In addition, you may wish to create a query with
most or all of the fields in the Customer table and Art History from the Current Book table, and then save
the result in an Excel spreadsheet (using the Save As command and changing the file type) for some
analysis to help you determine your segments. Minimum, maximum, averages, and correlations, would all
seem to be potentially useful. None of this is mandatory and some common sense and experimentation can
also lead to a good segmentation. If you do any analysis be sure and briefly describe it and include it
in your write-up.
Be sure to specify which segments you wish to make the offer to in the roll phase. A list of segment
numbers is sufficient (you should definitely have a unique Segment # field in your segmentation table.
Name the query for your final segmentation “Final Query” and the segmentation table it uses “Final
Table” and submit the entire database on Blackboard with filename: “Team NN Amazon’s Best”
where NN is your two-digit team number. NOTE that you should make sure your query works after
you change the query or table names. Sometimes changing a table name when a query is open causes
the query to stop working, so the best thing to do is close the table and query, rename them, and then
confirm they still work…
5
IMPORTANT NOTE: while it is possible to do a segmentation without a separate table, you MUST do
your final segmentation with a separate table because this table is required to evaluate the
performance of your segmentation in the roll phase. Be sure to test your final segmentation after you
have changed the name of the table and query to “Final Table” and “Final Query.” Sometimes changing
the table name causes problems (as discussed in more detail in the preceding paragraph).
As described above, for the Extra Credit you must submit your database with a Final Table and
Final Query. In addition, you should submit a short write-up that has a brief description of any
analysis you did to come up with a segmentation, and then tell us which tells us which segment
numbers you wish to make an offer to and what your estimated roll profits are.
Please submit your write-up and your database named “Team NN, Amazon’s Best” on Blackboard
before class. Note that the Assignment and Extra Credit are separate submissions on Blackboard.
Example
The cost of sending out 100 offers is 100 * $1/offer = $100. If 10 people buy, we generate $70 from those
sales (taking into account the cost of the book and mailing the books, but not the cost of the offers), so we
lose $30 ($70 - $100). If 20 people buy the book, we generate $140 from those sales and we make $40
($140 - $100). Profit will be maximized by only selling to groups where we believe at least the break-even
percentage (14.28%) will purchase.
The table below is from this week’s lecture notes.
Category # Gender Art Low Art High Count Sold AvgOfArt History
1 1 0 0 224 16 7.14%
2 2 0 0 475 16 3.37%
3 1 1 2 66 14 21.21%
4 2 1 2 216 24 11.11%
5 1 3 10 7 6 85.71%
6 2 3 10 12 4 33.33% Suppose we sell to everyone. We make 1000 offers and generate 80 sales. Our test phase profit is
80 * $7 – 1000 * $1 = ($440),
i.e., a loss of $440. Of course, what we really want to do is only make offers to those groups that have a
purchase percentage greater than the break-even percentage of 14.28%. In this case, that means groups 3,
5, and 6. If we only make offers to that group, we will make a total of 85 offers (66 + 7 + 12) and we will
sell 24 books (14 + 6 + 4). Our test phase profit would have been
24 * $7 – 85 * $1 = $168 - $85 = $83.
We don’t want to sell to groups 1, 2, or 4, because the cost of making offers to those groups exceeds the
expected value of the sales those groups will generate.
We could maximize the expected profit in the test phase by using the finest possible segmentation.
However, as the average group size decreases, the variance of our estimate of the percentage that will
purchase, increases. Thus, the goal is to find a profitable segmentation without having the group sizes be
too small. Even though group 5 is small, the % of purchasers is so high, they are a pretty safe bet. Also
note we could merge groups 3 and 5 into a single group of males that purchased 1 – 10 books.
There are 1,000 customers in the test phase. There are 10,000 consumers in the roll phase. Thus, we would
expect to generate ten times as much profit ($830) by making offers to groups 3, 5, and 6 in the roll phase.