A multi layer recency frequency monetary method for customer priority segmentation in online transaction

Figures & data

Table 1. RFM score description

Score	Characteristic
5	Most Valuable
4	Promising
3	Can’t Lose Them
2	At Risk
1	Lost

Figure 1. MLRFM steps.

Table 2. Sample of transaction data

Category	Customer ID	Transaction date	Transaction cost (IDR)
L1	1	20 March 2022	10,000
L2	2	8 March 2022	12,000
N1	3	7 March 2022	25,000
L1	4	9 March 2022	8,000
L2	5	12 March 2022	17,000

Table 3. MLRFM scoring threshold

Threshold	Value
0.70–1.00	5
0.50–0.69	4
0.30–0.49	3
0.10–0.29	2
0.00–0.09	1

Table 4. Weight on multi layer

Frequency and Monetary	Value
1 Week	0.2
1 Month	0.2
3 Months	0.3
All	0.3
Total	1.0

Table 5. Weight factor

Factor	Value
Recency	0.2
Frequency	0.4
Monetary	0.4

Table 6. Transaction data summary

Category	ID	Recency—last order (day)	Frequency (1 week)	Frequency (1 month)	Frequency (3 months)	Frequency (all)	Monetary-IDR (1 week)	Monetary-IDR (1 month)	Monetary-IDR (3 months)	Monetary-IDR (all)
L1	1	20	0	1	10	500	10,000	10,000	350,000	2,000,000
L2	2	2	5	15	30	200	150,000	400,000	400,000	500,000
N1	3	1	7	30	30	30	600,000	600,000	600,000	600,000
L1	4	3	5	5	50	500	50,000	500,000	600,000	1,500,000
L2	5	5	6	10	30	400	80,000	700,000	800,000	1,000,000
N1	6	6	7	15	45	45	70,000	150,000	450,000	450,000
N2	7	7	5	10	30	30	30,000	50,000	450,000	450,000
L3	8	1	1	1	1	1	10,000	10,000	10,000	10,000
L2	9	3	5	50	70	200	100,000	500,000	900,000	3,000,000
N3	10	35	0	0	10	300	0	0	100,000	2,000,000
L3	11	5	2	4	6	10	40,000	90,000	120,000	200,000
N1	12	1	10	40	130	130	100,000	400,000	1,300,000	1,300,000
N2	13	10	0	15	45	60	0	300,000	900,000	1,200,000
L3	14	6	1	1	3	10	10,000	200,000	500,000	1,000,000
L1	15	5	14	60	120	900	100,000	600,000	1,200,000	10,000,000
N1	16	3	6	25	25	25	80,000	300,000	300,000	300,000
L2	17	4	5	20	35	80	150,000	800,000	1,200,000	6,000,000
N3	18	40	0	0	60	500	0	0	700,000	2,000,000
L3	19	1	4	20	50	70	300,000	500,000	750,000	1,800,000
L2	20	100	0	0	0	1,000	0	0	0	5,000,000

Table 7. MLRFM calculation example

ID	Category	Recency value	Frequency (1 Week) value	Frequency (1 Month) value	Frequency (3 month) value	Frequency (all) value	Monetary (1 week) value	Monetary (1 month) value	Monetary (3 month) value	Monetary (all) value	Recency Total Scoring	Frequency Total Scoring	Monetary Total Scoring	MLRFM Total Scoring
1	Data	20	0	1	10	500	10,000	10,000	350,000	2,000,000
	MLRFM Scoring	5	1	1	1	3	1	1	2	2	5	1.6	1.6	2.3
	Calculate Scoring	1—((20–1) /(100–1)) = 0.8	(0–0) /(14–0) = 0	(1–0) /(60–0) = 0.02	(10–0) /(130–0) = 0.08	(500–1) /(1000–1) = 0.49	(10,000–0) /(600,000–0) = 0.02	(10,000–0) /(800,000–0) = 0.01	(350,000–0) /(1,300,000–0) = 0.27	(2,000,000–0) /(10,000,000–0) = 0.2	5	(1x0.2) + (1x0.2) + (1x0.3) + (3x0.3) = 1.6	(1x0.2) + (1x0.2) + (2x0.3) + (2x0.3) = 1.6	(5x0.2) + (1.6x0.4) + (1.6x0.4) = 2.3
12	Data	1	10	40	130	130	100,000	400,000	1,300,000	1,300,000
	MLRFM Scoring	5	5	4	5	2	2	4	5	2	5	3.9	3.3	3.9
	Calculate Scoring	1—((1–1) /(100–1)) = 1	(10–0) /(14–0) = 0.71	(40–0) /(60–0) = 0.67	(130–0) /(130–0) = 1	(130–1) /(1000–1) = 0.13	(100,000–0) /(600,000–0) = 0.17	(400,000–0) /(800,000–0) = 0.5	(1,300,000–0) /(1,300,000–0) = 1	(1,300,000–0) /(10,000,000–0) = 0.13	5	(5x0.2) + (4x0.2) + (5x0.3) + (2x0.3) = 3.9	(2x0.2) + (4x0.2) + (5x0.3) + (2x0.3) = 3.3	(5x0.2) + (3.9x0.4) + (3.3x0.4) = 3.9
15	Data	5	14	60	120	900	100,000	600,000	1,200,000	10,000,000
	MLRFM Scoring	5	5	5	5	5	2	5	5	5	5	5	4.4	4.8
	Calculate Scoring	1—((5–1) /(100–1)) = 0.95	(14–0) /(14–0) = 1	(60–0) /(60–0) = 1	(120–0) /(130–0) = 0.92	(900–1) /(1000–1) = 0.90	(100,000–0) /(600,000–0) = 0.17	(600,000–0) /(800,000–0) = 0.75	(1,200,000–0) /(1,300,000–0) = 0.92	(10,000,000–0) /(10,000,000–0) = 1	5	(5x0.2) + (5x0.2) + (5x0.3) + (5x0.3) = 5	(2x0.2) + (5x0.2) + (5x0.3) + (5x0.3) = 4.4	(5x0.2) + (5x0.4) + (4.4x0.4) = 4.8
20	Data	100	0	0	0	1000	0	0	0	5,000,000
	MLRFM Scoring	1	1	1	1	5	1	1	1	4	1	2.2	1.9	1.84
	Calculate Scoring	1—((100–1) /(100–1)) = 0	(0–0) /(14–0) = 0	(0–0) /(60–0) = 0	(0–0) /(130–0) = 0	(1000–1) /(1000–1) = 1	(0–0) /(600,000–0) = 0	(0–0) /(800,000–0) = 0	(0–0) /(1,300,000–0) = 0	(5,000,000–0) /(10,000,000–0) = 0.5	1	(1x0.2) + (1x0.2) + (1x0.3) + (5x0.3) = 2.2	(1x0.2) + (1x0.2) + (1x0.3) + (4x0.3) = 1.9	(1x0.2) + (2.2x0.4) + (1.9x0.4) = 1.84

Table 8. MLRFM results

	ID	Recency value	Frequency (1 week) value	Frequency (1 month) value	Frequency (3 months) value	Frequency (all) value	Monetary (1 week) value	Monetary (1 month) value	Monetary (3 months) value	Monetary (all) value	Recency total scoring	Frequency total scoring	Monetary total scoring	MLRFM total scoring
L1	15	5	5	5	5	5	2	5	5	5	5.0	5.0	4.4	4.8
N1	12	5	5	4	5	2	2	4	5	2	5.0	3.9	3.3	3.9
L2	9	5	3	5	4	2	2	4	4	2	5.0	3.4	3.0	3.6
L2	17	5	3	3	2	1	2	5	5	4	5.0	2.1	4.1	3.5
L3	19	5	2	3	3	1	3	4	4	2	5.0	2.2	3.2	3.2
L2	5	5	3	2	2	3	2	5	4	1	5.0	2.5	2.9	3.2
N1	3	5	3	3	2	1	5	5	3	1	5.0	2.1	3.2	3.1
L1	4	5	3	1	3	3	1	4	3	2	5.0	2.6	2.5	3.0
N2	13	5	1	2	3	1	1	3	4	2	5.0	1.8	2.6	2.8
L2	2	5	3	2	2	2	2	3	3	1	5.0	2.2	2.2	2.8
N1	6	5	3	2	3	1	2	2	3	1	5.0	2.2	2.0	2.7
N1	16	5	3	3	2	1	2	3	2	1	5.0	2.1	1.9	2.6
N3	18	4	1	1	3	3	1	1	4	2	4.0	2.2	2.2	2.6
N2	7	5	3	2	2	1	1	1	3	1	5.0	1.9	1.6	2.4
L1	1	5	1	1	1	3	1	1	2	2	5.0	1.6	1.6	2.3
L3	14	5	1	1	1	1	1	2	3	1	5.0	1.0	1.8	2.1
L3	11	5	2	1	1	1	1	2	1	1	5.0	1.2	1.2	2.0
L2	20	1	1	1	1	5	1	1	1	4	1.0	2.2	1.9	1.8
N3	10	4	1	1	1	2	1	1	1	2	4.0	1.3	1.3	1.8
L3	8	5	1	1	1	1	1	1	1	1	5.0	1.0	1.0	1.8

Table 9. RFM results

	ID	Recency value	Frequency (all) value	Monetary (all) value	Recency Total scoring	Frequency total scoring	Monetary total scoring	RFM total scoring
L1	15	5	5	5	1.0	2.0	2.0	5.0
L2	20	1	5	3	0.2	2.0	1.2	3.4
L1	4	5	3	2	1.0	1.2	0.8	3.0
L1	1	5	3	2	1.0	1.2	0.8	3.0
L2	17	5	1	4	1.0	0.4	1.6	3.0
N3	18	4	3	2	0.8	1.2	0.8	2.8
L2	5	5	3	1	1.0	1.2	0.4	2.6
N1	12	5	2	2	1.0	0.8	0.8	2.6
L2	9	5	2	2	1.0	0.8	0.8	2.6
N3	10	4	2	2	0.8	0.8	0.8	2.4
L2	2	5	2	1	1.0	0.8	0.4	2.2
L3	19	5	1	2	1.0	0.4	0.8	2.2
N2	13	5	1	2	1.0	0.4	0.8	2.2
N1	3	5	1	1	1.0	0.4	0.4	1.8
N1	6	5	1	1	1.0	0.4	0.4	1.8
N1	16	5	1	1	1.0	0.4	0.4	1.8
N2	7	5	1	1	1.0	0.4	0.4	1.8
L3	14	5	1	1	1.0	0.4	0.4	1.8
L3	11	5	1	1	1.0	0.4	0.4	1.8
L3	8	5	1	1	1.0	0.4	0.4	1.8

Table 10. RFM and MLRFM result comparison

Rank	Categories	ID	RFM total scoring	Changing level compared to RFM score	Categories	ID	MLRFM total scoring
1	L1	15	5.0	No change	L1	15	4.8
2	L2	20	3.4	(16)	N1	12	3.9
3	L1	4	3.0	(5)	L2	9	3.6
4	L1	1	3.0	(11)	L2	17	3.5
5	L2	17	3.0	1	L3	19	3.2
6	N3	18	2.8	(7)	L2	5	3.2
7	L2	5	2.6	1	N1	3	3.1
8	N1	12	2.6	6	L1	4	3.0
9	L2	9	2.6	6	N2	13	2.8
10	N3	10	2.4	(9)	L2	2	2.8
11	L2	2	2.2	1	N1	6	2.7
12	L3	19	2.2	7	N1	16	2.6
13	N2	13	2.2	4	N3	18	2.6
14	N1	3	1.8	7	N2	7	2.4
15	N1	6	1.8	4	L1	1	2.3
16	N1	16	1.8	4	L3	14	2.1
17	N2	7	1.8	3	L3	11	2.0
18	L3	14	1.8	2	L2	20	1.8
19	L3	11	1.8	2	N3	10	1.8
20	L3	8	1.8	No change	L3	8	1.8

Table 11. K-Means clustering result

ID	Cluster	ID	Cluster	ID	Cluster	ID	Cluster
1	1	6	2	11	3	16	4
2	2	7	2	12	3	17	4
3	2	8	2	13	3	18	5
4	2	9	3	14	3	19	4
5	2	10	3	15	3	20	4

Table 12. Cluster evaluation

	Max	Min	Average
Cohesion	1.507	0.000	0.806
Separation	4.135	1.641	2.24
Silhouette Coefficient	1.000	0.368	0.61