INSS 651 · Submit following files: · word file (ONE word file with everything in it) · 1 EXCEL file (with RFM analysis (Q1) in one worksheet and lift chart (Q 3) in another worksheet) · Tableau url...

Please see the attached file.


INSS 651 · Submit following files: · word file (ONE word file with everything in it) · 1 EXCEL file (with RFM analysis (Q1) in one worksheet and lift chart (Q 3) in another worksheet) · Tableau url (Q4) NAME ___________ __________ last first Student Number ________________ Please Show All Work -- for Full Credit 36 + 4 (BONUS) points Note: You can use EXCEL, ACCESS or ORACLE for these problems. Must show all intermediate and final tables, spreadsheets etc.. must show queries and their outputs--If ACCESS or ORACLE is used Ques #1 Lazy boy is using data analytics RFM technique to identify its best and worst customers. This will help them customize their marketing strategy. (12 points) Lazyboy sales files is available on the course website, called Lazyboy sales a. Perform RFM analysis (show all calculations in EXCEL, submit this excel file) (8 points) Submit following in WORD FILE b. provide the RFM score table in the word file also c. Identify three best and one worst customers (2 points) d. Discus marketing strategies that would be appropriate for them (2 points) Ques#2. (12 points) This is similar to question done in class but not exactly the same (note ownership/non ownership are different here) Given the following ownership data for Tractors based on income level and lot size (similar to example that we did in class but NOT the same; source shemuli etc) Revised tractor data file is available on class website as tractor data Develop a two level split decision tree using First level split at lot size = 19 Second level split follow following rule If lot size > 19 AND income <= 75.5="" etc..="" if="" lot=""><=19 and="" income=""><= 65.8 etc.. do not worry about other splits a. based on first and second level draw the partial tree ( 2 points) gini index a. before the split (4 points) b. after the first split (lot size =19) (6 points) c. after the second level splits (+2 bonus) q3 given the following given the following table with training records with their actual class and the probability of them being class 1 members estimated by a classifier. where 1 denotes a customer will buy washer/dryer combination and 0 implies they will not. (4 points) (source shmueli etc) probability of class 1 actual class cumulative actual class 0.9950 1 0.9875 1 0.9844 1 0.9804 0 0.9481 0 0.8893 1 0.8476 1 0.7628 0 0.7070 1 0.6807 1 0.6563 1 0.6224 0 0.5050 0 0.4713 1 0.3371 1 0.2179 0 0.1992 0 0.1494 0 0.0478 1 0.0383 1 0.0248 1 0.0218 0 0.0161 0 0.0035 0 a. draw a “lift” chart for a person buying a washer and dryer (1) using the above model. draw it in excel and submit excel file. ( 3 points) b. and provide the lift value for 10 cases of 1(success) ( 1 points) q 4. using tableau (8 points) hint: you can change the name of worksheet by double clicking on it using tractor data (ques #2) 1. worksheet 1: put your name on worksheet in title create 2 clusters and discuss between and within sum of square change the name of worksheet to your name_cluster (4 points) 2. worksheet 2: create the following worksheet in tableau, call it part 2 (4 points) save it in tableau public server and submit the url bonus +2 given the clusters below (note they maybe different from part a) develop a confusion matrix for cluster 1 (note cluster1 implies ownership) 1 7 65.8="" etc..="" do="" not="" worry="" about="" other="" splits="" a.="" based="" on="" first="" and="" second="" level="" draw="" the="" partial="" tree="" (="" 2="" points)="" gini="" index="" a.="" before="" the="" split="" (4="" points)="" b.="" after="" the="" first="" split="" (lot="" size="19)" (6="" points)="" c.="" after="" the="" second="" level="" splits="" (+2="" bonus)="" q3="" given="" the="" following="" given="" the="" following="" table="" with="" training="" records="" with="" their="" actual="" class="" and="" the="" probability="" of="" them="" being="" class="" 1="" members="" estimated="" by="" a="" classifier.="" where="" 1="" denotes="" a="" customer="" will="" buy="" washer/dryer="" combination="" and="" 0="" implies="" they="" will="" not.="" (4="" points)="" (source="" shmueli="" etc)="" probability="" of="" class="" 1="" actual="" class="" cumulative="" actual="" class="" 0.9950="" 1="" 0.9875="" 1="" 0.9844="" 1="" 0.9804="" 0="" 0.9481="" 0="" 0.8893="" 1="" 0.8476="" 1="" 0.7628="" 0="" 0.7070="" 1="" 0.6807="" 1="" 0.6563="" 1="" 0.6224="" 0="" 0.5050="" 0="" 0.4713="" 1="" 0.3371="" 1="" 0.2179="" 0="" 0.1992="" 0="" 0.1494="" 0="" 0.0478="" 1="" 0.0383="" 1="" 0.0248="" 1="" 0.0218="" 0="" 0.0161="" 0="" 0.0035="" 0="" a.="" draw="" a="" “lift”="" chart="" for="" a="" person="" buying="" a="" washer="" and="" dryer="" (1)="" using="" the="" above="" model.="" draw="" it="" in="" excel="" and="" submit="" excel="" file.="" (="" 3="" points)="" b.="" and="" provide="" the="" lift="" value="" for="" 10="" cases="" of="" 1(success)="" (="" 1="" points)="" q="" 4.="" using="" tableau="" (8="" points)="" hint:="" you="" can="" change="" the="" name="" of="" worksheet="" by="" double="" clicking="" on="" it="" using="" tractor="" data="" (ques="" #2)="" 1.="" worksheet="" 1:="" put="" your="" name="" on="" worksheet="" in="" title="" create="" 2="" clusters="" and="" discuss="" between="" and="" within="" sum="" of="" square="" change="" the="" name="" of="" worksheet="" to="" your="" name_cluster="" (4="" points)="" 2.="" worksheet="" 2:="" create="" the="" following="" worksheet="" in="" tableau,="" call="" it="" part="" 2="" (4="" points)="" save="" it="" in="" tableau="" public="" server="" and="" submit="" the="" url="" bonus="" +2="" given="" the="" clusters="" below="" (note="" they="" maybe="" different="" from="" part="" a)="" develop="" a="" confusion="" matrix="" for="" cluster="" 1="" (note="" cluster1="" implies="" ownership)="" 1="">