Tuscan Lifestyles: Targeting Customers with RFM Analysis
Part I: Preliminary and Decile Analysis
1. What percent of customers responded (i.e. bought anything) from this catalog?
2.5% of the customers bought from this last catalog.
Analyze → Descriptive Statistics → Frequencies
|
Bought from last catalog
|
|
|
Frequency
|
Percent
|
Valid Percent
|
Cumulative Percent
|
|
Valid
|
no
|
94180
|
97.5
|
97.5
|
97.5
|
|
yes
|
2371
|
2.5
|
2.5
|
100.0
|
|
Total
|
96551
|
100.0
|
100.0
|
|
2. Of those who bought, what was the average dollars ordered from this catalog? The average dollars ordered from this catalog was $104.24 per buying customer.
Analyze → Reports → Case Summaries
|
Case Summaries
|
|
Dollars ordered from last catalog
|
|
Bought from last catalog
|
N
|
Mean
|
|
no
|
94180
|
.00
|
|
yes
|
2371
|
104.24
|
|
Total
|
96551
|
2.56
|
3. Create decile variables for recency, frequency and monetary.
Transform → Rank Cases
4. Create a bar chart showing the response rate to this catalog by recencydecile.
Data → Aggregate
Graph → Legacy Dialogs → Bar
5. Create a bar chart showing the response rate to this catalog by frequency decile.
Data → Aggregate
Graph → Legacy Dialogs → Bar
6. Create a bar chart showing the response rate to this catalog by monetary decile.
Data → Aggregate
Graph → Legacy Dialogs → Bar
7. Using only those customers who placed an order from this catalog, create bar charts showing the average dollars ordered from this catalog by recency, frequency andmonetarydeciles.
Data → Select Cases
Graph → Legacy Dialogs → Bar
8. What do the above bar charts reveal about the likelihood of response and the size of the order across the different recency, frequency, and monetary deciles?
Part II: RFM Classification
9. Generate a report showing the number of customers, the number of buyers, and the response rate for each RFM cell (using the sequential n-tiles approach as recorded in the RFM_SEQ variable).
Analyze → Reports → Case Summaries
|
Case Summaries
|
|
Bought from last catalog
|
|
RFM (sequential ntiles)
|
N
|
Sum
|
Mean
|
|
111
|
796
|
69
|
.09
|
|
112
|
796
|
55
|
.07
|
|
113
|
798
|
51
|
.06
|
|
114
|
796
|
50
|
.06
|
|
115
|
796
|
48
|
.06
|
|
121
|
798
|
30
|
.04
|
|
122
|
795
|
30
|
.04
|
|
123
|
796
|
32
|
.04
|
|
124
|
797
|
36
|
.05
|
|
125
|
799
|
34
|
.04
|
|
131
|
786
|
20
|
.03
|
|
132
|
788
|
29
|
.04
|
|
133
|
781
|
29
|
.04
|
|
134
|
784
|
23
|
.03
|
|
135
|
789
|
32
|
.04
|
|
141
|
634
|
14
|
.02
|
|
142
|
615
|
19
|
.03
|
|
143
|
653
|
19
|
.03
|
|
144
|
633
|
17
|
.03
|
|
145
|
634
|
19
|
.03
|
|
151
|
806
|
20
|
.02
|
|
152
|
934
|
27
|
.03
|
|
153
|
751
|
21
|
.03
|
|
154
|
937
|
28
|
.03
|
|
155
|
808
|
19
|
.02
|
|
211
|
789
|
36
|
.05
|
|
212
|
792
|
43
|
.05
|
|
213
|
787
|
42
|
.05
|
|
214
|
790
|
47
|
.06
|
|
215
|
789
|
27
|
.03
|
|
221
|
724
|
20
|
.03
|
|
222
|
731
|
29
|
.04
|
|
223
|
725
|
32
|
.04
|
|
224
|
715
|
26
|
.04
|
|
225
|
729
|
39
|
.05
|
|
231
|
471
|
16
|
.03
|
|
232
|
475
|
5
|
.01
|
|
233
|
471
|
6
|
.01
|
|
234
|
468
|
12
|
.03
|
|
235
|
468
|
11
|
.02
|
|
241
|
719
|
19
|
.03
|
|
242
|
720
|
12
|
.02
|
|
243
|
716
|
13
|
.02
|
|
244
|
724
|
13
|
.02
|
|
245
|
722
|
19
|
.03
|
|
251
|
1152
|
15
|
.01
|
|
252
|
1114
|
27
|
.02
|
|
253
|
1210
|
26
|
.02
|
|
254
|
1159
|
20
|
.02
|
|
255
|
1144
|
15
|
.01
|
|
311
|
805
|
28
|
.03
|
|
312
|
807
|
27
|
.03
|
|
313
|
806
|
29
|
.04
|
|
314
|
807
|
27
|
.03
|
|
315
|
801
|
17
|
.02
|
|
321
|
747
|
14
|
.02
|
|
322
|
745
|
19
|
.03
|
|
323
|
750
|
20
|
.03
|
|
324
|
740
|
13
|
.02
|
|
325
|
753
|
16
|
.02
|
|
331
|
766
|
15
|
.02
|
|
332
|
768
|
15
|
.02
|
|
333
|
772
|
14
|
.02
|
|
334
|
746
|
15
|
.02
|
|
335
|
806
|
17
|
.02
|
|
351
|
1522
|
22
|
.01
|
|
352
|
1564
|
15
|
.01
|
|
353
|
1678
|
30
|
.02
|
|
354
|
1343
|
16
|
.01
|
|
355
|
1564
|
25
|
.02
|
|
411
|
885
|
27
|
.03
|
|
412
|
886
|
25
|
.03
|
|
413
|
885
|
24
|
.03
|
|
414
|
883
|
17
|
.02
|
|
415
|
882
|
16
|
.02
|
|
421
|
412
|
9
|
.02
|
|
422
|
413
|
4
|
.01
|
|
423
|
412
|
8
|
.02
|
|
424
|
425
|
10
|
.02
|
|
425
|
407
|
7
|
.02
|
|
431
|
826
|
9
|
.01
|
|
432
|
836
|
14
|
.02
|
|
433
|
794
|
16
|
.02
|
|
434
|
857
|
14
|
.02
|
|
435
|
833
|
21
|
.03
|
|
441
|
1838
|
29
|
.02
|
|
442
|
1884
|
29
|
.02
|
|
443
|
1490
|
13
|
.01
|
|
444
|
1903
|
20
|
.01
|
|
445
|
1719
|
25
|
.01
|
|
511
|
875
|
17
|
.02
|
|
512
|
877
|
24
|
.03
|
|
513
|
874
|
21
|
.02
|
|
514
|
877
|
13
|
.01
|
|
515
|
874
|
20
|
.02
|
|
521
|
476
|
8
|
.02
|
|
522
|
478
|
8
|
.02
|
|
523
|
478
|
14
|
.03
|
|
524
|
473
|
10
|
.02
|
|
525
|
478
|
4
|
.01
|
|
531
|
805
|
13
|
.02
|
|
532
|
813
|
12
|
.01
|
|
533
|
814
|
19
|
.02
|
|
534
|
791
|
13
|
.02
|
|
535
|
810
|
12
|
.01
|
|
541
|
1672
|
12
|
.01
|
|
542
|
1710
|
24
|
.01
|
|
543
|
1599
|
16
|
.01
|
|
544
|
1738
|
19
|
.01
|
|
545
|
1675
|
20
|
.01
|
|
Total
|
96551
|
2371
|
.02
|
Part III: Profitability Analysis
Now, compare the profitability of two scenarios:
• mailing to all 96,551 customers
• mailing only to those customers in RFM cells (using the sequential n-tiles approach) with a response rate that is equal to or greater than the breakeven response rate
10. Use the following costs to determine:
(a) the gross profit in dollars,
(b) the gross profit as a % of gross sales, and
(c) the return on marketing expenditures (gross profit/cost to mail catalogs) as a result of mailing the catalog to all 96,551 customers:
Cost to produce and mail catalog = $1
COGS and variable costs on orders = 50% (of sales revenues)
11. What is the breakeven response rate?
Breakeven response rate = cost to mail catalog / net profit of the average sale
$1 / ($104.24 * 50%) = 1 / 52.12 = 0.0192
12. Compute the following items. You can do these (1) by hand (not recommended), (2) in Excel (using the results from question 9 above) or (3) you can create an aggregate SPSS dataset. If you want SPSS to do the bulk of the calculations, follow the instructions in Exhibit 3 - and then you can use analyze ... reports... case summaries for the final computations:
• Determine which RFM segments (using the sequential n-tiles approach) have response rates exceeding the breakeven rate.
• Determine the number of customers belonging to these profitable segments.
• Determine the number of buyers belonging to these profitable segments.
• Finally, what would the
(a) the gross profit in dollars,
(b) the gross profit as a % of gross sales, and
(c) the return on marketing expenditures (gross profit/cost to mail catalogs) have been as a result of mailing the catalog only to those customers in the RFM cells with response rates exceeding the breakeven? That is, rather than mailing to all 96,551 customers - what would the profitability of the mailing have been if mailed to the subset of customers in ‘profitable' segments?
|
|
Mass Mailing
|
Targeted RFM Mailing
|
|
Number of Customers
|
96511
|
96511
|
|
Number Mailed
|
96511
|
53083
|
|
% of Customers mailed
|
100.0%
|
55.0%
|
|
Number of Buyers
|
2371
|
1769
|
|
Response Rate
|
2.46%
|
3.33%
|
|
|
|
|
|
Gross Revenues
|
$ 247,160
|
$ 184,401
|
|
COGS
|
$ 123,580
|
$ 92,200
|
|
Mailing
|
$ 96,551
|
$ 53,083
|
|
Gross Profit (a)
|
$ 27,029
|
$ 39,117
|
|
Gross Profit/Sales (b)
|
10.9%
|
21.2%
|
|
Return on Marketing Expenditures (c)
|
28.0%
|
73.7%
|
13. Examine the first 20 or so observations in the database.
What do you notice about theRFM1 and RFM2 values?