This group assignment consists of three case studies, the first related to market basket analysis (association rules), the second to customer segmentation (clustering) and the third related to campaign management (customer response model). The first case study accounts for 15% of the assignmentβs grade, the second for 25% and the third for 60%.
The deliverable should be one report where you should provide answers to all the case studies. Beware that some answers are the deliverable to technical people whereas, some other, are the deliverable to business people so the writing style should be appropriate (you will be marked on this!). At the beginning of each case study, you should include an executive summary to be addressed to the management team of each organization that should contain the problem under consideration, how you tackled it, what methods you used, what decision support tool was utilized and what was the final result. Beware that an executive summary should be short (not more than half a page), clear and should not contain any technicalities.
In order to provide answers to the questions you must use SAS Visual Data Mining and Machine Learning on SAS Viya to explore and analyze the given data. The report should be sent in word and pdf format and their titles should be βLastName_FirstName.docxβ and βLastName_FirstName.pdfβ respectively.
You should hand in the report by the 1st of May 2022 through the turnitin facilities of moodle. Each day that the report will be delayed, a penalty of 10% will be applied to the grade. In the body of the mail that you will send you should also include the username and password from the SAS account that you created in order to access the SAS Viya for Learners software. Since this is a group assignment the credentials of one member of the group should be provided. The instructor will check whether the work in the software is in line with the results included in the report.
Business Analytics Practicum I SAS Assignment
Buy-books-on-line.com is an on line store that sells books about science and information technology. The store is very well known in the academic community so a lot of its customers are university professors and also librarians at universities buying on behalf of their institutions. A very popular category of the books that the store sells is that related to βBusiness Analyticsβ. In this category the store has a list of 56 books such as βCredit Risk Analyticsβ, βMarketing Analyticsβ, βAnalytics at Workβ etc. The past year 1,896 customers have bought at least one book that belongs to the βBusiness Analyticsβ category i.e. at least one of the 56 books.
The sales department of the store wants to exploit cross selling opportunities so as to sell as many books as possible. The optimal way to achieve this, is to do wise next best offer propositions to its customers by applying associations rules. The analytics department of the store has collected a data set with 19,805 past sales transactions related to the βBusiness Analyticsβ book category. The data set is called βOn_Line_Book_Storeβ.
You are hired as an analyst by the on-line store to aid the analytics department in this market basket analysis initiative. After the data analysis you should write a report to the analytics team of the company (technical people) to explain them what you did, which method you used, how it works and what were your results. As already said the report should contain an executive summary in a business format. Use as minimum support level the 0.05 and as minimum confidence level the 0.1. Also set the maximum number of items in a rule equal to 3 (three) (in the interface this option is referred erroneously as minimum number of items in a rule). Save the rules table in the CASUSER library with the title MBA_Results. In the main body of the report you should answer the following questions:
- 1) Β Write the Executive Summary. This part accounts for 20% of the case studyβs mark.
- 2) Β What are the sales (in units) of each book? Provide a relevant chart (bar chart) using
the SAS Visual Analytics software. Enrich the chart so as to show data labels, chart title, titles in both axis. This question accounts for 20% of the case studyβs mark.
3) Which two books should the store advertise to customers who bought/ are searching to buy only one of the following:
- ο· Β Managerial Analytics
- ο· Β Implementing Analytics
- ο· Β Customer Analytics for Dummies
- ο· Β Enterprise Analytics
2 / 12
Business Analytics Practicum I SAS Assignment
In other words create the Amazonβs βCustomers who Bought this Item also boughtβ list of books. What is the biggest lift of the rules with three (3) items where each one of the above mentioned 4 books is on the left side of the rule? How is it interpreted? This part accounts for 30% of the case studyβs mark.
4) If you set the maximum items in a rule to 3, which are the 3 books most bought together by customers? How many occurrences of this set of 3 books are found together? What does this number mean? What is the support metric of this set of 3 books and how is it calculated? This part accounts for 30% of the case studyβs mark.
Case Study 2 (
Sports-OnLine.com is an on line retailer that sells sports clothes and shoes and it is operating in the market since October 2001. On January 2007, after six year of operation, the management team of the store wants to exploit the electronic data captured the previous years to better understand the market. After a meeting with the marketing department, it was decided that a customer segmentation analysis should be performed and, based on the available data, a Recency Frequency Monetary (RFM) analysis would be the most suitable technique for the desired objective.
During the period Oct 2001 β Dec 2006, 995 customers have done 4906 sales transactions that have been recorded by the on line store and have been stored in the following data set:
3 / 12
|
Customer_ID |
Date_OF_Transaction |
Amount_Of_Transaction |
4,906 transactions
The IT department in cooperation with the Business Analytics department have transformed the above data set into RFM format, and have produced the SAS data set named RFM_Final_Practice.sas7bdat that is presented below. Since the 4,906 transactions of the previous data set have been produced by 995 customers the RFM data set has 995 rows, each one corresponding to a single customer.
|
Customer_ID |
R |
F |
M |
995 Customers
Cust001 Cust002 Cust003
……. Cust994
Cust995
4 5 485 14 4 350 13 2 233
……. ……. ……. 24 1 185
6 2 187
4 / 12
Business Analytics Practicum I SAS Assignment
You are hired as the Marketing Analytics consultant to perform the RFM segmentation with the machine learning software SAS Visual Data Mining and Machine Learning in SAS Viya. Do the clustering of the customers and the profiling of the segments created. Name the segments (e.g. churners, good customers, bad customers, first time customers etc) and describe briefly what marketing actions are appropriate for each segment (e.g. customer reactivation program, contact customers for feedback, cross sell activities, special promotions etc) and why. The breakdown of marks for this part of the cases study is 60% Business (20% Executive Summary, 40% Report) and 40% technical (e.g. methodology, graphs, tables etc).
Case Study 3
This case study refers to a fictitious insurance organization – XYZ β and more specifically to its marketing department that is currently organizing a targeted campaign to identify segments of customers who are likely to purchase a variable annuity (an insurance product). A variable annuity is a contract with an insurance company that includes investments you choose and a fixed insurance component. It allows you to receive monthly income payments that are guaranteed for the rest of your life, which makes them a popular choice for people afraid of running out of money in retirement (so it is designed to provide retirement income). Penalties can be incurred for early withdrawals.
As it can be understood the organization cannot contact all the customers in its database to promote the new product because this wonβt prove profitable since a solicitation has a cost. So the organization must decide who to contact from the data base next year and this will depend on how much likely those customers will respond positively with a purchase i.e. how much likely is that they will purchase or not the insurance product. In order for this to be achieved, the management of the insurance organization must optimize the ROI of the campaign so it has decided to invest in analytics based decision making and more specifically to develop a machine learning based customer response model. In order to gather data to develop the model the organization approached this year a sample of its customers and more specifically 30,129 and promoted the insurance product using telephone solicitations, personal contacts and offering of advertising material and gifts to each one of them and recorded whether the solicited customers replied positively to the purchase of the insurance
5 / 12
Business Analytics Practicum I SAS Assignment
product. For each customer 47 characteristics that represented other product usage and demographics were available.
The information that will be used to learn the behavior of the customers on whether they will purchase or not the new product is whether they responded positively to this yearβs campaign given the 47 characteristics mentioned previously. Next year the developed model will be applied to the rest of the customers in the database to predict whether they will purchase the new product. If the above methodology predicts that a person in the database is likely to respond to the solicitation with a purchase next year, the marketing department will make the solicitation whereas if the methodology predicts the opposite then the marketing department will ignore the person so as not to lose money from the marketing effort.
The business analytics department of the organization, in cooperation with the IT department, has extracted the sample customerβs database in a SAS data set named βinsurance_campaign_historyβ, which contains data about the customers included in the sample solicited. The data set contains the 47 customerβs characteristics – input variables (other product usage and demographics). You can find the relevant data dictionary of this data set at the end of this document. The data set consists also of a target variable that is coded as 1/ 0 and that it indicates whether a customer from the solicited ones bought the insurance products or not (1=bought, 0=not bought).
You are hired as a machine learning engineer to aid the insurance organization develop a model that will predict whether a customer will purchase the insurance product or not if s/he is solicited. The model should be built from the above mentioned data set. After the model is developed by using the historical data (this yearβs data), it can be applied to the rest of the customerβs database next year to predict whether the rest of the customers will buy the product or not. The IDβs of the members that are more likely to purchase the insurance product will be directed to the marketing department for solicitation. A sample of the rest of the customers to be scored are stored in the βinsurance_campaign_scoreβ SAS data set.
Please follow the following steps and answer the relevant questions:
Open SAS VDMML on SAS Viya (You will also need to open SAS Visual Analytics on SAS Viya to explore the data).
Create a new project.
6 / 12
Business Analytics Practicum I SAS Assignment
Create a new data source (βinsurance_campaign_historyβ) by consulting the relevant data dictionary at the end of this document.
1) Write the Executive Summary. This part accounts for 10% of the case studyβs mark. The management team of the marketing department has come up with the following profit matrix to be used for the evaluation of the models to be created. The numbers represent monetary units e.g. dollars, euro, pounds etc.
Prediction
Actual
Contact β Solicit Responder 800 Non-Responder -300
Ignore
0 0
2)
above. This part accounts for 7.5% of the case studyβs mark.
3) Based on the above profit matrix what minimum probability (cut – off point) should a customer have so as to be considered a buyer and hence to be considered for solicitation? Provide the mathematical calculations. This part accounts for 5.5% of the case studyβs mark. 4) Use the project settings for this question (the gear on the upper right corner of the screen). Partition the historical data set to training and validation using the 70% – 30% rule of thumb? Why this process must be done? The sampling in the data partition is stratified. What does this mean? Also use the Misclassification Rate (Event) as the performance criterion and input the previously calculated cut-off point into the software. This question accounts for 2.5% of the case studyβs mark.
5) Are there any missing values in the variables of the data set? Provide a screenshot of the SAS Visual Analytics software to prove this. What is the proportion of buyers and non- buyers in the data set? Provide a screenshot from the SAS Visual Analytics software to prove it (pie chart). This part accounts for 2.5% of the case studyβs mark.
6) The proportion of responders to non β responders in the historical data set is 30% – 70%. What would you do if this proportion was 10% – 90%? This part accounts for 5% of the case studyβs mark.
7) Provide a graph (pie chart) using SAS Visual Analytics on SAS Viya that shows the proportion of buyers and non-buyers for those customers that have purchased more than 5
Using any assumptions you like, give an interpretation of the profit matrix presented
7 / 12
Business Analytics Practicum I SAS Assignment
times credit cards. What do you observe? This question accounts for 2.5% of the case studyβs mark.
8) Use SAS Visual Analytics on SAS Viya to show the average deposit amount (DepAmt) for a) buyers and b) non-buyers. What does this mean with respect to the target variable? This question accounts for 2.5% of the case studyβs mark.
Add a decision tree node to the workspace and connect it to the data source node.
9) What is the variable used for the first split? Explain briefly why this variable is selected (hint: logworth). Which cases are directed to the left node and which to the right node? Where are the missing values directed to? This part accounts for 2.5% of the case studyβs mark.
Add a second decision tree node to the workspace. Name the new decision as Maximal tree. In the properties window of the tree change the method to largest (maximal) i.e. Pruning options — > Selection Method — > Largest. Run the tree node.
10) How many terminal leaves does the tree have? How is this tree called? Check the performance of the training and validation data set when the Misclassification Rate is used as the assessment criterion. Provide the relevant graph (subtree assessment plot) in your report. How is the phenomenon presented in line for the training data set (blue line) called? Explain it briefly in a couple of sentences. Describe what is the solution to the phenomenon. Provide a screenshot of the largest tree in your report. This part accounts for 7.5% of the case studyβs mark.
11) Run the first decision tree node. How many terminal leaves does the optimal tree have? Provide a screenshot of the optimal tree. Provide a screenshot of the subtree assessment plot when Misclassification Rate is selected as the performance criterion and comment on it (in a couple of sentences). This part accounts for 7,5% of the case studyβs mark. 12) Beware that the decision tree and the decision tree model are two different concepts. In the previous part you provided a screenshot of the decision tree. In this part provide a description of the decision tree model. This part accounts for 7,5% of the case studyβs mark. (Please interpret the tree by using only 5 of the terminal leaves).
13) Write a paragraph to interpret the decision tree as you would explain it to the management team of the insurance organization i.e. to non – technical people. What are the most important variables that separate buyers from non β buyers? This part accounts for 7.5% of the case studyβs mark. (Please interpret the tree by using only 5 of the terminal leaves).
8 / 12
Business Analytics Practicum I SAS Assignment
Add a logistic regression node to the pipeline. Accept the default settings and run the regression node.
Add a neural network node to the pipeline. Accept the default settings and run the neural network node.
14) Go to the results window of the model comparison node and focus on the score rankings overlay plots. Check the cumulative % response chart for the validation data set. Explain what this graph shows by using the 20% and 100% points in the x axis (the 20% and 100% most highly ranked customers). This part accounts for 5.5% of the case studyβs mark.
15) Check the % response chart for the validation data set. How is this graph constructed and what do the values of the x axis represent? Explain what this graph shows by using the 25% point in the x axis. This part accounts for 5.5% of the case studyβs mark.
16) Check the cumulative lift chart for the validation data set. Explain what his graph shows by using the 20% point in the x axis. This part accounts for 5.5% of the case studyβs mark.
17) Check the cumulative % captured response graph for the validation data set. Explain what this graph shows by using the 40% point in the x axis. This part accounts for 5.5% of the case studyβs mark.
By now you must have selected the optimal model, so it is time to put it into production and score the data set named βinsurance_campaign_scoreβ i.e. to predict which customers are more likely to be buyers in the next campaign. Insert the necessary node (Score Data) to do that, run it and provide a screenshot with the completed process flow (In the score data node attach the insurance_campaign_score data set and for the output library select the CASUSER). You should also notice that because this data set needs to be scored it does not contain a target variable. Name the new scored table as Scored_Insurance.
In order to answer the final three questions, do the following: Select the Score data node and go to the results. Select the Output data tab and View Output. Press the Explore and Visualize button, select the CASUSER library and name the table as Scored_Insurance_Visualize. You will be transferred to the SAS Visual Analytics environment.
9 / 12
Business Analytics Practicum I SAS Assignment
18) How many customers are there in the βinsurance_campaign_scoreβ data set? How many of them are predicted as buyers and how many as non-buyers? Provide a relevant bar chart using SAS Visual Analytics. This part accounts for 2.5% of the case studyβs mark.
19) What is the biggest probability of being a buyer assigned to a customer? What is the smallest one? This part accounts for 2.5% of the case studyβs mark.
20) Check the customers with Cust_ID= 07636 and and Cust_ID =29773. Based on which column of the score data set and why the software assigns 1 / 0 to these two customers i.e. predictsthattheywillbebuyers/non-buyers? Thispartaccountsfor2.5%ofthecasestudyβs mark.
10 / 12
Business Analytics Practicum I SAS Assignment
Data Dictionary for Insurance_Campaign_History Data Set
|
Variable |
Description |
Role |
Level |
ATM ATMAmt AcctAge Age Branch CC CCBal CCPurc CD CDBal CRScore CashBk Checks DDA DDABal Dep DepAmt DirDep HMOwn HMVal ILS ILSBal IRA IRABal InArea Income Ins
Inv InvBal LOC LOCBal LORes MM
Used ATM service (1=yes, 0=no) ATM withdrawal amount
Age of oldest account in years Age of customer in years
Branch of Bank (B1 β B19)
Has credit card account (1=yes, 0=no)
Credit card balance
Number of credit card purchases
Has certificate of deposit (1=yes, 0=no) Certificate of deposit balance
Credit score
Number of times customer received cash back Number of checks
Checking account (1=yes, 0=no)
Checking account balance
Number of checking deposits
Amount deposited
Direct deposit (1=yes, 0=no)
Owns home (1=yes, 0=no)
Home value in thousands of dollars
Has installment loan (1=yes, 0=no) Installment loan balance
Has retirement account (1=yes, 0=no) Retirement account balance
Local address (1=yes, 0=no)
Income in thousands of dollars
Purchase variable annuity account (1=yes, 0=no)
Has investment account (1=yes, 0=no) Investment account balance
Has line of credit (1=yes, 0=no)
Line of credit balance
Length of residence in years
Has money market account (1=yes, 0=no)
Input Input Input Input Rejected Input Input Input Input Input Input Input Input Input Input Input Input Input Input Input Input Input Input Input Input Input Target
Input Input Input Input Input Input
Binary Interval Interval Interval Nominal Binary Interval Interval Binary Interval Interval Interval Interval Binary Interval Interval Interval Binary Binary Interval Binary Interval Binary Interval Binary Interval Binary
Binary Interval Binary Interval Interval Binary
11 / 12
|
Variable |
Description |
Role |
Level |
MMBal MMCred MTG MTGBal Moved NSF NSFAmt POS POSAmt Phone
Res
SDB
Sav
SavBal
Teller
Cust_ID
Partition_ Indicator
Business Analytics Practicum I SAS Assignment
Money market balance
Number of money market credits
Has mortgage account (1=yes, 0=no) Mortgage balance
Recent address change (1=yes, 0=no) Occurrence of insufficient funds (1=yes, 0=no) Amount of insufficient funds
Number of point of sale transactions
Amount in point of sale transactions
Number of times customer used telephone banking
Area classification (R=rural, S=suburb, U=urban)
Has a safety deposit box (1=yes, 0=no) Saving account (1=yes, 0=no)
Saving balance
Number of teller visits
Customer Identification Number
Partition Indicator
Input Input Input Input Input Input Input Input Input Input
Rejected
Input Input Input Input ID Partition
Interval Interval Binary Interval Binary Binary Interval Interval Interval Interval
Nominal
Binary
Binary Interval Interval Nominal Binary
12 / 12
