Segmentation Models and Lift/Gains/Profitability Submit Assignment

MKTG 3509, Spring, 2018 Dr. Christopher Monos 1

Course Number: MKTG 3509: Customer Data Analytics Semester: Spring, 2018 Homework 3: RFM Segmentation and Lift/Gains Charts Objectives

The objectives of this exercise are to: 1) learn how to develop a RFM segmentation using SPSS, 2) practice creating and interpreting a cumulative lift chart and a gains chart to evaluate the effectiveness of a segmentation model, and 3) learn how to estimate the expected profitability of a campaign that uses a RFM segmentation.

Assignment There are four parts to Homework 3. In Part 1 you will learn how to assign customers to RFM segments using SPSS. In Part 2/3 you will practice creating three charts in Excel to evaluate the RFM Segmentation model: 1) Cumulative Lift Chart, 2) Cumulative Gains Chart and 3) a Cumulative Profits chart. A preformatted Excel workbook is attached. In Part 4, you will answer 9 additional questions based on the data that you compiled in Parts 2-3.

If you need assistance with this assignment, then you should consult the video of the most recent Webex session and several videos in the Fox Video Vault that are relevant to this assignment.

Available Videos in Fox Video Vault

• Evaluating Models: Lift Analysis

• Evaluating Models: Gains Analysis

• Evaluating Models: Profit Analysis

There is one additional resource that you also can consult for additional assistance.

• Using Lift Gains to Evaluate Models.pdf – Provides description of how to create and use lift/gains charts.

Due Date Please upload completed Excel workbook to Blackboard by Monday, March 19 by 8:00 p.m.

MKTG 3509, Spring, 2018 Dr. Christopher Monos 2

Part 1: Develop a RFM segmentation model using SPSS.

A RFM segmentation was already developed during the prior Webex session. SPSS is not needed to complete the remainder of the assignment.

Part 2: Conduct a Lift/Gains Analysis of the RFM segmentation model.

Open the following file, ‘Tuscan RFM Segmentation Data.xlsx’. Copy the data from last week’s SPSS analysis (Part 1) and paste the data into the Lift/Gains Data.

Complete the following tables in the ‘Lift/Gains Data’ tab in the workbook.

• Lift table (Remember to Sort the RFM segments from High to Low on response rate of each segment) • Cumulative Lift table • Cumulative Gains table.

Part 3: Conduct a Profitability Analysis of the RFM segments

Complete the Profitability table in the ‘Lift/Gains Data’ tab in the workbook.

Use the following data to determine operating profits:

• Cost to produce and mail a catalog = $1
 • Variable costs = 50% (of sales revenues) • Average order (of those who order) = $104.24 


Part 4: Answer the questions on the ‘Questions’ worksheet in ‘Tuscan RFM Segmentation Data.xlsx’