RFM客户价值模型

1 说明

• 依赖库：time、numpy、pandas、sklearn、pyecharts
• 程序输入：sales.xlsx
• 程序输出：RFM得分数据写本地文件sales_rfm_score.xlsx和数据表（sales_rfm_score）

2 导入相关库

import time  # 时间库

import numpy as np  # numpy库
import pandas as pd  # pandas库
import pymysql  # mysql连接库
from sklearn.ensemble import RandomForestClassifier # RF库

2 读取数据

# 多个工作表的表名
sheet_names = ['2016','2017','2018','2019','会员等级']
# 读取多个工作表
sheet_datas = [pd.read_excel('sales.xlsx',sheet_name=i) for i in sheet_names]
# 显示头5条数据
print(type(sheet_datas))

3 数据审查

for each_name,each_data in zip(sheet_names,sheet_datas):    
    print('[data summary for {0:=^50}]'.format(each_name))
    print('Overview:','\n',each_data.head(4))# 展示数据前4条
    print('DESC:','\n',each_data.describe())# 数据描述性信息
    print('NALL records',each_data.isnull().any(axis=1).sum()) # 缺失值记录数    
    print('Datas_types',each_data.dtypes) # 数据类型

4 数据预处理

# 去除缺失值和异常值
for ind,each_data in enumerate(sheet_datas[:-1]):    
    sheet_datas[ind] = each_data.dropna()# 丢弃缺失值记录
    sheet_datas[ind] = each_data[each_data['订单金额'] > 1]# 丢弃订单金额<=1的记录
    sheet_datas[ind]['max_year_date'] = each_data['提交日期'].max() # 增加一列最大日期值

# 汇总所有数据
data_merge = pd.concat(sheet_datas[:-1],axis=0)
# 获取各自年份数据
data_merge['date_interval'] = data_merge['max_year_date']-data_merge['提交日期']
data_merge['year'] = data_merge['提交日期'].dt.year
# 转换日期间隔为数字
data_merge['date_interval'] = data_merge['date_interval'].apply(lambda x: x.days) # 转换日期间隔为数字
data_merge.head()

# 按会员ID做汇总
rfm_gb = data_merge.groupby(['year','会员ID'],as_index=False)
                                            .agg({'date_interval': 'min',  # 计算最近一次订单时间
                                                   '提交日期': 'count', # 计算订单频率
                                                   '订单金额': 'sum'})  # 计算订单总金额
# 重命名列名
rfm_gb.columns =  ['year','会员ID','r','f','m']
rfm_gb.head()

5 确定RFM划分区间

# 查看数据分布
desc_pd = rfm_gb.iloc[:,2:].describe().T
print(desc_pd)
# 定义区间边界
r_bins = [-1,79,255,365] # 注意起始边界小于最小值
f_bins = [0,2,5,130] 
m_bins = [0,69,1199,206252]

6 计算RFM因子权重

# 匹配会员等级和rfm得分
rfm_merge = pd.merge(rfm_gb,sheet_datas[-1],on='会员ID',how='inner')

rfm_merge['会员等级'].min()

# rf获得rfm因子得分
clf = RandomForestClassifier()
clf = clf.fit(rfm_merge[['r','f','m']],rfm_merge['会员等级'])
weights = clf.feature_importances_
print('feature importance:',weights)

7 RFM计算过程

# RFM分箱得分
rfm_gb['r_score'] = pd.cut(rfm_gb['r'], r_bins, labels=[i for i in range(len(r_bins)-1,0,-1)])  # 计算R得分
rfm_gb['f_score'] = pd.cut(rfm_gb['f'], f_bins, labels=[i+1 for i in range(len(f_bins)-1)])  # 计算F得分
rfm_gb['m_score'] = pd.cut(rfm_gb['m'], m_bins, labels=[i+1 for i in range(len(m_bins)-1)])  # 计算M得分

# 计算RFM总得分
# 方法一：加权得分
rfm_gb = rfm_gb.apply(np.int32) # cate转数值
rfm_gb['rfm_score'] = rfm_gb['r_score'] * weights[0] + rfm_gb['f_score'] * weights[1] + rfm_gb[
'm_score'] * weights[2]

# 计算RFM总得分
# 方法一：加权得分
rfm_gb = rfm_gb.apply(np.int32) # cate转数值
rfm_gb['rfm_score'] = rfm_gb['r_score'] * weights[0] + rfm_gb['f_score'] * weights[1] + rfm_gb[
'm_score'] * weights[2]

# 方法二：RFM组合
rfm_gb['r_score'] = rfm_gb['r_score'].astype(np.str)
rfm_gb['f_score'] = rfm_gb['f_score'].astype(np.str)
rfm_gb['m_score'] = rfm_gb['m_score'].astype(np.str)
rfm_gb['rfm_group'] = rfm_gb['r_score'].str.cat(rfm_gb['f_score']).str.cat(
rfm_gb['m_score'])

8 保存RFM结果到Excel

rfm_gb.to_excel('sales_rfm_score.xlsx')  # 保存数据为Excel

9 写数据到数据库

# # 数据库信息
# config = {'host': '127.0.0.1',  # 默认127.0.0.1
#           'user': 'root',  # 用户名
#           'password': '123456',  # 密码
#           'port': 3306,  # 端口，默认为3306
#           'database': 'python_data',  # 数据库名称
#           'charset': 'gb2312'  # 字符编码
#           }

# # 建表操作
# con = pymysql.connect(**config)  # 建立mysql连接
# cursor = con.cursor()  # 获得游标
# cursor.execute("show tables")  # 查询表
# table_list = [t[0] for t in cursor.fetchall()]  # 读出所有库
# # 查找数据库是否存在目标表，如果没有则新建
# table_name = 'sales_rfm_score'  # 要写库的表名
# if not table_name in table_list:  # 如果目标表没有创建
#     cursor.execute('''
#     CREATE TABLE %s (
#     userid               VARCHAR(20),
#     r_score               int(2),
#     f_score              int(2),
#     m_score              int(2),
#     rfm_score              DECIMAL(20,2),
#     rfm_group              VARCHAR(10),
#     insert_date              VARCHAR(20)
#     )ENGINE=InnoDB DEFAULT CHARSET=gb2312
#     ''' % table_name)  # 创建新表

# 梳理数据
write_db_data = rfm_gb[['会员ID','r_score','f_score','m_score','rfm_score','rfm_group']] # 主要数据
timestamp = time.strftime('%Y-%m-%d', time.localtime(time.time()))  # 日期

# 写库
for each_value in write_db_data.values:
    insert_sql = "INSERT INTO `%s` VALUES ('%s',%s,%s,%s,%s,'%s','%s')" % \
                 (table_name, each_value[0], each_value[1], each_value[2], \
                  each_value[3],each_value[4],each_value[5],
                  timestamp)  # 写库SQL依据
    cursor.execute(insert_sql)  # 执行SQL语句，execute函数里面要用双引号
    con.commit()  # 提交命令
cursor.close()  # 关闭游标
con.close()  # 关闭数据库连接