MKTG 3509 Temple Predictive Models Using Logistic Regression Paper & Excel The requirements are to analyze SPSS and Excel data to answer questions in Word

MKTG 3509 Temple Predictive Models Using Logistic Regression Paper & Excel The requirements are to analyze SPSS and Excel data to answer questions in Word document answer sheet. There are others that have submitted the same questions so I want to make sure I am 100% correct because those answers vary. I have done 80% of the work already. Can send SPSS over email. Could take someone qualified 15-30 minutesThanks Course Number:
Semester:
MKTG 3509: Customer Data Analytics
Spring, 2020
Homework 5: Predictive Models Using Logistic Regression
Objectives
The objectives of this final exercise are to: 1) practice building and evaluating a predictive model to identify
which customers in our database should receive a future marketing communication from our company. You
will create a predictive model using logistic regression analysis, 2) evaluate the lift and gains of the model and
3) evaluate the profitability of the predictive model versus using no predictive model.
Assignment
As a direct marketer of specialty books, the BookBinders Book Club has achieved steady growth in their
customer base. Yet while sales have grown steadily, profits began falling when the database got larger and
when the company diversified its book selection and increased the number of offers sent to customers. The
falling profits have led Dave Lawton, BookBinders’ marketing director, to experiment with different
database marketing approaches in order improve BookBinder’s mailing yields and profits.
Dave began a series of live market tests, each involving a random sample of customers from the database. An
offer for the current book selection is sent to the sample and then the sample customers’ responses, either
purchase or no purchase, are recorded and used to calibrate a response model for the current offering. The
response model’s results are then used to “score” the remaining customers in the database and select
customers from the full customer database for the ‘rollout’ mailing campaign.
Dave’s team continues to debate the best method that will best identify potential targets for its new book,
‘The Art History of Florence’. The team is discussing whether to develop a predictive model using Logistic
Regression Analysis.
Dave has a dataset containing the responses of a random sample of 50,000 customers to “The Art History of
Florence” campaign. He is eager to assess the potential value of a predictive model in predicting customer
response and has asked you to complete the following analysis.
Note: In order to complete this analysis you will need the two attached files: 1) BBB.sav, SPSS data file
which contains all of the data needed to conduct the logistic regression analysis, and 2) Homework 5 Lift
Gains Profit Analysis.xlsx, which contains a pre-formatted worksheet to assist you in your assessment of lift,
gains and profitability. You will need to copy and paste data from SPSS output into Excel to complete your
assessment.
Due Date:
Monday, May 4 at 11:59 p.m.
MKTG 3509, Spring, 2020
1
Dr. Christopher Monos
During class we will develop identify the best logistic regression model and then use the predicted
probabilities from the model to segment customers.
In order to complete the assignment, you will need to:
1) Provide answers to the questions on the following pages of this document, and
2) Conduct a lift/gains analysis in Excel.
You need to upload both this document and the Excel file which has been provided.
Attention: This assignment requires you to perform another lift/gains/profit analysis. If needed, review the
three videos in Fox Video Vault: 1) Evaluating Models: Lift Analysis, 2) Evaluating Models: Gains Analysis,
and 3) Evaluating Models: Profit Analysis.
MKTG 3509, Spring, 2020
2
Dr. Christopher Monos
Question 1: Evaluate the following two Model Comparisons
Comparison 1:

: ln (
) = 0 +
1−

: ln (
) = 0 + 1 ( ) + 2 ( ) +
1−
-2LL(C)
30,355.464
PC
1
-2LL(A)
28,787.456
PA
Improvement Chi Square
1,568.008
Probability of
Improvement Chi Square (sig)
.000
Interpretation of Findings
(Include Improvement Chi Square and
Probability of Improvement Chi Square )
Prediction Equation of
Log Odds for Model A
Using the Prediction Equation of Log
Odds for Model A, what is the predicted
probability of buying ‘The Art History of
Florence’ for a customer with:
Totdol = $419
Last = 3 Months since last purchase
MKTG 3509, Spring, 2020
3
Dr. Christopher Monos
Comparison 2:

: ln (
) = 0 + 1 ( ) + 2 ( ) +
1−

) = 0 + 1 ( ) + 2 ( ) + 2 ( ) + 2 ( ℎ ) + 2 ( ℎ) + 2 ( ) + 2 ( _ ) + 2 ( ) + 2 ( ) +
: ln (
1−
-2LL(C)
28,787.456
PC
-2LL(A)
24,567.919
PA
Improvement Chi Square
4,219.537
Probability of
Improvement Chi Square
Interpretation of Findings
(Include Improvement Chi
Square and Probability of
Improvement Chi Square )
Prediction Equation of
Log Odds for Model A
MKTG 3509, Spring, 2020
4
Dr. Christopher Monos
Question 2:
Which set of variables gives
the best prediction of the
dependent variable?
The one with all 9 segments
What is your evidence?
Question 3:
Using the equation with all 9 independent variables, how are the odds of buying the ‘Art History of Florence’ affected
by:
a.
# purchases of Art books (Art) – How do the odds of buying change for an additional Art book
purchase?
b.
# purchases of Do it Yourself books (Do_it) – How do the odds of buying change for an additional
Do_It Yourself book purchase?
Question
Answer
3a
3b
MKTG 3509, Spring, 2020
5
Dr. Christopher Monos
Question 4:
Use the following cost information to assess the profitability of both modeling methods.
Cost to Mail Offer to Customer
Selling Price of ‘The Art History of Florence’
Contribution Margin
$.50
$18.00
$6.00
Using the data from the Lift/Gains/Profitability Analysis that you completed in Excel, complete the
following table.
ROI = Operating
Profit/ Total Fixed
Costs of
Campaign
Method to
Identify
Customers
Maximum
Cumulative
Operating Profit
% of Customers
Receiving Mailing
Mail All Customers
in Database
$2,132
100%
8.53%
Logistic Regression
Model
$11,760
30%
156.80%
Acquisition Costs
per Customer
Question 5:
Which of the 2 approaches
would you recommend be
used in the future? Why?
MKTG 3509, Spring, 2020
6
Dr. Christopher Monos
Data
Predicted
Number of Number of Cumulative
Probability
Cases
Responses
Cases
Decile
1
2
3
4
5
6
7
8
9
10
Total
5000
1882
5000
792
5000
536
5000
363
5000
279
5000
224
5000
168
5000
153
5000
80
5000
45
50000
4522
9.04%
5000
10000
15000
20000
25000
30000
35000
40000
45000
50000
Cumulative
Responses
1,882
2,674
3,210
3,573
3,852
4,076
4,244
4,397
4,477
4,522
Lift Per Decile
Decile
Overall
Response Respons
Rate
e Rate
(a)
(b)
37.64%
9.04%
15.84%
9.04%
10.72%
9.04%
7.26%
9.04%
5.58%
9.04%
4.48%
9.04%
3.36%
9.04%
3.06%
9.04%
1.60%
9.04%
0.90%
9.04%
Cumulative Lift
Lift
Index
(a/b)
4.16
1.75
1.19
0.80
0.62
0.50
0.37
0.34
0.18
0.10
Cum
Overall
Cumulative
Respons Respons
Lift Index
e Rate
e Rate
(d/e)
(d)
(e)
37.64%
9.04%
4.16
26.74%
9.04%
2.96
21.40%
9.04%
2.37
17.87%
9.04%
1.98
15.41%
9.04%
1.70
13.59%
9.04%
1.50
12.13%
9.04%
1.34
10.99%
9.04%
1.22
9.95%
9.04%
1.10
9.04%
9.04%
1.00
Gains
Cumulative Responses
1,882
2,674
3,210
3,573
3,852
4,076
4,244
4,397
4,477
4,522
Profitability Analysis
Gain
Index
Predicted
Contributio
Probabilit
n Margin
y Decile
0.42
0.59
0.71
0.79
0.85
0.90
0.94
0.97
0.99
1.00
1
2
3
4
5
6
7
8
9
10
Fixed
Costs
$11,292.00
$2,500.00
$4,752.00
$2,500.00
$3,216.00
$2,500.00
$2,178.00
$2,500.00
$1,674.00
$2,500.00
$1,344.00
$2,500.00
$1,008.00
$2,500.00
$918.00
$2,500.00
$480.00
$2,500.00
$270.00
$2,500.00
Segments above maxmiun = target segments
ROI
8.53%
ROI
156.80%
ility Analysis
Cum
Operating
Operating
Profit
Profit
$8,792.00
$8,792.00
$2,252.00
$11,044.00
$716.00
$11,760.00
-$322.00
$11,438.00
-$826.00
$10,612.00
-$1,156.00
$9,456.00
-$1,492.00
$7,964.00
-$1,582.00
$6,382.00
-$2,020.00
$4,362.00
-$2,230.00
$2,132.00
$2,132.00
xmiun = target segments
Percentile Group of PRE_1 * Bought “Art History of
Count
Percentile Group of
PRE_1
Total
1
2
3
4
5
6
7
8
9
10
Bought “Art History of
No
Yes
3118
1882
4208
792
4464
536
4637
363
4721
279
4776
224
4832
168
4847
153
4920
80
4955
45
45478
4522
Total
5000
5000
5000
5000
5000
5000
5000
5000
5000
5000
50000
Yes
1882
792
536
363
279
224
168
153
80
45
4522

Purchase answer to see full
attachment