Question: 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.
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'
1: What was the overall response rate when all 96,551 customers were contacted?
2: What is the breakeven response rate (BERR) given the available cost data?
3: How many RFM segments have response rates exceeding the BERR?
4: Across all of the segments that exceeded the BERR
how many customers were contacted?
5: Across all of the segments that exceeded the BERR how many buyers were there?
6: What are the following metrics if all 96,551 customers were mailed?
7: What are the following metrics if only the customers in those segments that exceeded the BERR were mailed?
8: Complete the following summary table.
|
|
Total Customers Contacted
|
Cumulative
Response Rate
|
Acquisition Costs
per Customer
|
Operating
Profit as % of Revenues
|
ROI
|
|
No Model (All Customers Contacted)
|
|
|
|
|
|
|
RFM Segmentation Model (Segments that exceed BERR)
|
|
|
|
|
|
|
Index
|
|
|
|
|
|
9: Based on your analysis of the summary table from Question 8, would you recommend using the RFM segmentation? Why?
Information related to above question is enclosed below:
Attachment:- Files.rar