RFM

RFM

1. 导入数据

import pandas as pd
import numpy as np
trad_flow = pd.read_csv('RFM_TRAD_FLOW.csv', encoding='gbk')
trad_flow.head()

	transID	cumid	time	amount	type_label	type
0	23351	10006	27SEP09:20:10:22	58.0	特价	Special_offer
1	23372	30031	27SEP09:21:33:35	69.0	特价	Special_offer
2	23447	40102	28SEP09:21:12:34	69.0	特价	Special_offer
3	23448	20173	28SEP09:21:12:34	69.0	特价	Special_offer
4	23449	40017	28SEP09:21:13:48	69.0	特价	Special_offer

2.计算 RFM

M = pd.pivot_table(trad_flow, index='cumid', columns='type', values='amount', aggfunc='sum')
M.head()

type	Normal	Presented	Special_offer	returned_goods
cumid
10001	3608.0	0.0	420.0	-694.0
10002	1894.0	0.0	NaN	-242.0
10003	3503.0	0.0	156.0	-224.0
10004	2979.0	0.0	373.0	-40.0
10005	2368.0	0.0	NaN	-249.0

F = pd.pivot_table(trad_flow, index='cumid', columns='type', values='transID', aggfunc='count')
F.head()

type	Normal	Presented	Special_offer	returned_goods
cumid
10001	15.0	8.0	2.0	2.0
10002	12.0	5.0	NaN	1.0
10003	15.0	8.0	1.0	1.0
10004	15.0	12.0	2.0	1.0
10005	8.0	5.0	NaN	1.0

R = pd.pivot_table(trad_flow, index='cumid', columns='type', values='time', aggfunc='max')
R.head()

type	Normal	Presented	Special_offer	returned_goods
cumid
10001	21JUL09:09:31:26	31MAR10:20:29:48	12OCT09:10:59:13	10JUL10:20:41:54
10002	29JUL09:19:21:41	31MAY09:14:19:33	None	04SEP09:14:51:57
10003	31AUG09:16:11:01	27JUN10:18:25:55	02JAN10:20:19:39	06JUN10:14:29:04
10004	26NOV09:20:38:32	29AUG10:12:44:52	20AUG09:20:00:52	03JUN10:21:16:01
10005	29MAY10:15:49:22	30JUN10:22:08:49	None	08APR10:19:53:48

3.衡量客户对打折商品的偏好

M['Special_offer'] = M['Special_offer'].fillna(0)

M['spe_ratio'] = M['Special_offer'] / (M['Special_offer'] + M['Normal'])
M_rank = M.sort_values('spe_ratio', ascending=False, na_position='last').head()
M_rank

type	Normal	Presented	Special_offer	returned_goods	spe_ratio
cumid
10151	765.0	0.0	870.0	NaN	0.532110
40033	1206.0	0.0	761.0	-848.0	0.386884
40236	1155.0	0.0	691.0	-793.0	0.374323
30225	1475.0	0.0	738.0	-301.0	0.333484
20068	1631.0	0.0	731.0	-239.0	0.309483

M_rank['spe_ratio_group'] = pd.qcut(M_rank['spe_ratio'], 4) # 这里以age_oldest_tr字段等宽分为4段
M_rank.head()

type	Normal	Presented	Special_offer	returned_goods	spe_ratio	spe_ratio_group
cumid
10151	765.0	0.0	870.0	NaN	0.532110	(0.387, 0.532]
40033	1206.0	0.0	761.0	-848.0	0.386884	(0.374, 0.387]
40236	1155.0	0.0	691.0	-793.0	0.374323	(0.333, 0.374]
30225	1475.0	0.0	738.0	-301.0	0.333484	(0.308, 0.333]
20068	1631.0	0.0	731.0	-239.0	0.309483	(0.308, 0.333]