Team Assignment 2 - Charles Book Club Page 1 Second Team Assignment Submission Deadline: Monday, April 25, 11:59 PM Submit two files ONLY to your team folder under Springboard DROPBOX \"Team Assignment 2\" 1. Upload One PDF file that includes your report - File Name should be "Team # -TA2.pdf" for example, "Team01-TA2.pdf" 2. Upload ONE JMP file with your scripts saved (Team#-TA2.JMP). DO NOT upload zip files. Will They Buy The Book? 1 J anice Hernandez was finishing a degree in Marketing specialized in Business Analytics at Boston University. But her true passion was reading. She estimated that she had read over 1,000 books throughout her lifetime so far. Besides wearing out her library card three times, Janice had access to an extraordinary resource. Her family owned a bookstore on the Charles River, not too far from her University in Boston, Massachusetts. Janice had seen the bankruptcy and/or closing of one bookstore chain after another, with Borders being the most recent example. Barnes and Noble was hanging on by a thread it seemed, as they tried to catch up with Amazon.com and its pervasive influence with the Kindle. In such a cutthroat competitive environment, how could the Charles River Bookstore (CRB) survive? Fortunately, her family had taken some early actions that had proven to be quite fortuitous. They joined the Independent Online Booksellers Association shortly after it was founded, developing a web presence that was based on \"high touch\" customer service. Now they had about 500,000 registered online customers. CRB had also noted the trend of the formation of local book clubs long before Oprah launched hers on the air. In fact, Janice's aunt had been a founding member of the Washington Literary Society Book Club2, the longest continuously operating book club in the Washington DC area (started in 1990). They knew that book club members were some of the most avid readers, and highly likely to remain local to bookstores that gave them unusual choices that were well-suited to the book club members' tastes. With fewer physical bookstores available, Janice hoped to capitalize on their online presence. At the same time, Janice knew that typical works of fiction did not bring the store the highest margins. With the increasing availability of e-books, she wondered what the future held for those kinds of sales. Indeed, two recent titles illustrated this phenomenon all too well. The Man Booker prize-winning book in 2011, The Sense of an Ending, by Julian Barnes, already sold for just $5.88 on Amazon in paperback, and could be downloaded instantaneously for $11.99. To Janice's horror, Amazon then added \"an e-book lending service to its Prime membership offering\"3 in early November, 2011. Amazon may be under pressure due to their razor-thin profit 1 The original data and case have been credited to Bhandari, Vinni, and Dr. Nitin Patel, \"The Charles Book Club Case.\" Nissan Levin and Jacob Zahav, \"A Case Study in Database Marketing,\" Tel Aviv University. This case is included in the textbook \"Data Mining for Business Intelligence\" by Shmueli et al. (2010). So, we use it with permission. The scenario is modified by Dr. William McHenry at the University of Akron. 2 Some of this club's favorite titles (almost all fiction) included: The Help, by Kathryn Stockett; A Thousand Splendid Suns, by Khaled Hosseini; Corelli's Mandolin, by Louis de Bernieres; The King Must Die, by Mary Renault; and Midnight in the Garden of Good and Evil, by John Berendt. http://sites.google.com/site/washingtonliterarysociety/ 3 http://blogs.marketwatch.com/thetell/2011/11/03/does-amazon%E2%80%99s-e-book-lending-bring-more-pressure-to-margins/ Page 2 Team Assignment 2 - Charles Book Club margins, Janice read on a blog, but she understood this differently: razor thin margins for Amazon meant nonexistent margins for her. On the other hand, coffee table books with plenty of good looking, highresolution photographs could still command reasonable margins. A new title, \"The Art History of Florence,\" was being touted by one of her suppliers. Amazingly, the cost to CRB was only $10.00, but she could sell it (for the first few months at least), for $30.00. Margins like this seemed too good to be true! However, Janice also recognized a danger: she risked offending her customers by sending them offers that would not be congruent with their interests. Janice wondered whether her book club \"crowd\" might start defecting if they caught wind of her intentions. To achieve a maximum profit without such issues, Janice decided to do an analytics project. To achieve this objective, she needs to understand what kinds of customers are more likely to purchase The Art History of Florence. So, she did an experiment of database marketing: she randomly selected 4,000 customers from its 500,000 customer database, and sent the book to them and the customers responded as either buy or not-buy. The customer responses have been collated with past purchase data and put into one Excel file. She hoped that analysis of these data would help her to maximize her returns from marketing The Art History of Florence. Data Description Each row (or case) in the spreadsheet (other than the header) corresponds to one market test customer, total 4000 rows. Each column is a variable with the header row giving the name of the variable. The variable names and descriptions are given in the following Table: Table: List of Variables in the Sample \"Charles River Book Club\" VARIABLE NAMES DESCRIPTION ID# Customer Identification Number Gender 0 = Male, 1 = Female M Monetary- Total money spent on books R Recency - Months since last purchase F Frequency - Total number of purchases FirstPurch Months since first purchase ChildBks Number of purchases from the category: Child books YouthBks Number of purchases from the category: Youth books CookBks DoItYBks ArtBks Number of purchases from the Number of purchases from the Number of purchases from the Encyclopedias, Dictionaries) Number of purchases from the GeoBks Number of purchases from the category: Geography books ItalCook ItalAtlas ItalArt Number of purchases of book title: \"Secrets of Italian Cooking\" Number of purchases of book title: \"Historical Atlas of Italy\" Number of purchases of book title: \"Italian Art\" Number of related books purchased (= ArtBks+GeoBks+ItalCook+ItalAtlas+ItalArt) =1 \"The Art History of Florence\" was bought, =0 if not RefBks Related Purchase Florence category: Cookbooks category: Do-It-Yourself books category: Reference books (Atlases, category: Art books Page 3 Team Assignment 2 - Charles Book Club Task Descriptions Various analytical techniques can be used to analyze the data collected from the market test. No one technique may be universally better than another. The particular context and the particular characteristics of the data are the major factors in determining which techniques perform better in an application. For this team assignment, your team will apply Logistic Regression (LR) and Decision Tree (DT) data mining techniques that you have learned in this class. Your team will use the BA Lifecycle methodology to analyze this case by applying all six phases in BA Lifecycle. The data mining goal is to find the best model that can predict the probability of purchasing the book, P(Florence = 1), build profiles of customers who are likely to purchase the book, classify individual customers to either class1 (Florence=1) or class0 (Florence=0) based on P(Florence=1). Perform the following Tasks. Task 1) Modeling: Build model using the two DM techniques - LR & DT (use 70% data for training and 30% for validation) Task 2) Interpretation: Interpret all the results of the output from both JMP analyses Task 3) Evaluation: Evaluate the models using confusion matrix for validation data set only. 1) Using the confusion matrix for validation data, evaluate your model for accuracy and total net-profits (Refer to the table below). To calculate the total net profits for the validation data set, use the following information: - CRB will send the \"Art History of Florence\" book to individual customers that the model predicts as Florence = 1 (that is, customers who are predicted to purchase the book). o It costs $5 if a customer who received a book actually does not buy the book. That is, the cost of the error \"Model Predicts Florence = 1 for Actual Florence = 0\" = $5. o The sale price of a book is $30 as explained in the case. The total cost of a book (including delivery cost) is $10. Therefore, the net profit of selling one book = $20 2) Find a better cutoff probability You can find a better model by changing probability cutoff criteria in a way that increases the accuracy and the total net-profits of the model. Use three cutoffs (0.2, 0.5, 0.8) to find the best cutoff. Change the name of \"Most Likely Increased\" column to \"LogCutoff50\". With the validation data set, create a confusion matrix for each cutoff and calculate the accuracy and the total net profits/loss for each cutoff model. For example, if you use 0.2 (20%) as the cutoff, then in JMP, create a new column \"LogCutoff20\" with formula \"If(Prob[1]>0.2] 1, else 0\". The column \"LogCutoff50\" is automatically created by JMP based on 0.5 cutoff with formula \"If(Prob[1]>0.5] 1, else 0\" For 0.8 cutoff, create a new column \"LogCutoff80\" with formula \"If(Prob[1]>0.8] 1, else 0\" LogCutoff20 Confusion Matrix (for testing or Validation data) 1) Total Accuracy 2) Accuracy for Florence=1 3) Accuracy for Florence=0 LogCutoff50 LogCutoff80 Team Assignment 2 - Charles Book Club Page 4 Total Net-Profits (for the 2000 customers in the validation set) Total Net-Profits (for entire 500,000 customers) Task 4) Compare the two models regarding the accuracy, the total net profits, and profiles of customers who are likely to purchase the book. Task 5) Deployment - Explain how you would use the results in developing a new marketing plan that can increase the total net profits by selling more books \"The Art History of Florence\". Make sure the report is professional looking with Title page, TOC, Executive Summary, and the details of the report. Grading Rubric - Team Case Two (150 points) Overall Presentation (10 points) Title page, TOC, use of page numbers, etc. Free of grammar and spelling errors Well organized and easy to follow Submitted correct files to drop box Executive Summary (20 points) Logical flow, with an appropriate focus on the goals, results from models developed, and recommendations \"Real world\" emphasis Free of statistics jargon (do not say null hypothesis or significant or p-value or Alpha, etc. in the executive summary!) Start with overall business goals/your data-mining goals, discussion of the results & recommendations. Do not discuss the process you undertook to complete the assignment. BA Lifecycle methodology (20 points) Explain how you applied all six phases of BA Lifecycle methodology Logistic Regression Analysis (30 points) Steps involved in developing the LR analysis Interpret model significance, R-Square, etc. Interpret all Odds ratios Interpret Confusion Matrix Discuss Profiler Decision Tree Analysis (30 points) Steps involved in developing the DT analysis Interpret output (all results) Interpret Rules Interpret Confusion Matrix Model Comparison (30 points) Calculated Total Net Profit for both models (for all three scenarios - see Table above) Compared LR results with DT results and made recommendations on which technique to use and why Team Assignment 2 - Charles Book Club Discussed deployment strategy Overall Report (10 points) Recommendations Conclusions Final Checklist: Response Correctness Mechanics Writing Analysis Originality Did you do everything you were supposed to do? Is the analysis you did accurate (i.e. no mistakes in the application of the techniques, etc.) Is your document free of sloppy errors, misspellings, grammatical mistakes, poor formatting, etc. Did you deliver your analysis in a succinct and understandable fashion? How you understood your results? Did you supply an \"extra\" level of creative thinking to the assignment? Page 5