一、需求
1.根據某*電商平台的某*店鋪曆史交易資料進行整體的統計分析,按月統計銷售數量及交易金額的變化趨勢,統計各省份的銷售情況,及銷售環比情況。
2.根據某*電商平台的某*店鋪曆史交易資料進行曆史客戶價值分析。考慮使用者對店鋪的貢獻,複購率、對品牌的忠誠等因素,為使用者貼标簽,客戶更加精準地進行劃分,進而實作針對性的營銷營運,降低營銷成本,同時提高營銷效果。
工具:mysql+python+tableau
可視化截圖
![](https://img.laitimes.com/img/9ZDMuAjOiMmIsIjOiQnIsICM38FdsYkRGZkRG9lcvx2bjxiNx8VZ6l2cs0TPB9ENNRlT0kFVNBDOsJGcohVYsR2MMBjVtJWd0ckW65UbM5WOHJWa5kHT20ESjBjUIF2X0hXZ0xCMx81dvRWYoNHLrdEZwZ1Rh5WNXp1bwNjW1ZUba9VZwlHdssmch1mclRXY39CXldWYtlWPzNXZj9mcw1ycz9WL49zZuBnLzUTM0ADOyMjMxEzMwAjMwIzLc52YucWbp5GZzNmLn9Gbi1yZtl2Lc9CX6MHc0RHaiojIsJye.png)
二、擷取源資料
資料存在公司資料庫,在做資料分析測試階段,我們提取2018.11-2019.5的資料集用于做前期的資料分析。
資料字段及解釋如下:orders(訂單明細)表和failedorder(交易未達成)表。
#pandas庫連接配接mysql擷取資料
import pandas as pd
import pymysql
from sqlalchemy import create_engine
# 初始化資料庫連接配接,使用pymysql子產品
# MySQL的使用者:root, 密碼:123456, 端口:3306,資料庫:lean2
con = create_engine('mysql+pymysql://root:[email protected]:3306/rfm')
sql01 = '''select * from orders;'''
sql02 = '''select * from failedorder;'''
# read_sql_query的兩個參數: sql語句, 資料庫連接配接
orders = pd.read_sql_query(sql01, con = con)
failedorder = pd.read_sql_query(sql02,con=con)
print(orders.shape,failedorder.shape) #-->(28809, 12) (1040, 2)
三、資料清洗(工具:python)
1、合并表
df=orders.merge(failedorder,how='left',on="order_id") #表合并
#合并後failed_state字段如果為空,則表示交易成功
df['failed_state'] = df['failed_state'].fillna("交易成功")
display(df.head())
2、資料過濾,删除交易失敗的資料集
df = df[df['failed_state']== "交易成功"] #布爾過濾
# df = df.query("failed_state == '交易成功'") #query()過濾
display(df.shape) #(27769, 13)
3、擷取統計資訊,初步發現異常值
display(df.describe()) #擷取統計資訊
display(df.info()) #發現缺失值
display(df[['money','postage','num']].describe()) #數值型變量單獨統計
display(df[df.duplicated(subset=['order_id'],keep='first')])#發現重複值
4、檢視異常值并處理
df = df.drop_duplicates(subset=['order_id'],keep='first',inplace=False) #删除重複值
display(df[df["paytime"].isnull()]) #檢視paytime為空的行資訊
df["paytime"] = df["paytime"].fillna('2018-11-03 10:24:06.000')
display(df.loc[28808:28809])
display(df[df["money"].isnull()]) #檢視money為空的行資訊,無,已被連帶删除
display(df[df["city"].isnull()]) #檢視city為空的行資訊
df["city"]=df["city"].fillna("未知")
display(df[df["city"]=="未知"])
5、離群值檢驗與處理
import numpy as np
import pandas as pd
import matplotlib as mpl
import matplotlib.pyplot as plt #最常用的繪圖庫
mpl.rcParams["font.family"]="SimHei" #使用支援的黑體中文字型
mpl.rcParams["axes.unicode_minus"]=False # 用來正常顯示負号 "-"
plt.rcParams['font.sans-serif']=['SimHei'] # 用來正常顯示中文标簽
# % matplotlib inline #jupyter中用于直接嵌入圖表,不用plt.show()
import warnings
warnings.filterwarnings("ignore") #用于排除警告
#畫布
figure = plt.figure(num="箱型圖",figsize=(8,3),dpi=80,facecolor="LightGray",edgecolor="blue",frameon=True)
# 資料
money = df["money"]
num = df["num"]
axes1= plt.subplot(1,2,1)
plt.title("money箱型圖")
plt.xlabel("money")
plt.ylabel("value")
axes1.boxplot(money,sym="o",whis=1.5)
axes2= plt.subplot(1,2,2)
plt.title("num箱型圖")
plt.xlabel("num")
plt.ylabel("value")
axes2.boxplot(num,sym="o",whis=1.5)
plt.show()
display(df[df["money"]==df["money"].max()])
df = df.drop(28796, inplace=False, axis=0) #删除
#在看圖
figure = plt.figure(num="箱型圖",figsize=(8,3),dpi=80,facecolor="LightGray",edgecolor="blue",frameon=True)
# 資料
money = df["money"]
axes1= plt.subplot(1,2,1)
plt.title("money箱型圖")
plt.xlabel("money")
plt.ylabel("value")
axes1.boxplot(money,sym="o",whis=1.5)
plt.show()
display(df[df["money"]>1000])
# 檢視資料,發現交易數量也比較多,是以屬于正常值,不再做資料處理,實際工作中還可以咨詢相關業務員人員查驗資料
6、儲存清洗後的資料
#儲存清洗後的資料
import pandas as pd
import pymysql
from sqlalchemy import create_engine
# 初始化資料庫連接配接,使用pymysql子產品
# MySQL的使用者:root, 密碼:123456, 端口:3306,資料庫:lean2
con = create_engine('mysql+pymysql://root:[email protected]:3306/rfm')
#直接寫入資料-->mydf-->mydf表會自動建立
# 将建立的DataFrame儲存為MySQL中的資料表,儲存index列
df.to_sql('orders_new', con, index=True)
print('Read from and write to Mysql table successfully!')
四、建立使用者标簽的RFM模型
1.什麼是RFM模型?
RFM模型是衡量客戶價值和客戶創利能力的重要工具和手段。在衆多的客戶關系管理(CRM)的分析模式中,RFM模型是被廣泛提到的。
該模型通過一個客戶的近期購買行為R、購買的總體頻次(F)以及消費金額(M)三項名額來描述該客戶的價值狀況,把客戶更加精準地進行劃分,進而實作針對性的營銷,降低營銷成本,同時提高營銷效果。
詳細來說,R指的是客戶最後一次下單時間距離今天多少天了,該名額與客戶的複購和流失直接相關。F名額指的是客戶的下單頻次,即客戶在某個時間段内共消費了多少次,該名額用于衡量客戶消費的活躍度。M名額是客戶在該時間段内共消費了多少錢,該名額用于反應客戶對于公司的貢獻值。
RFM模型給每一個使用者進行分層。這裡我們需要建立一個評判标準,由于RFM模型本身就是需要根據不同場景和業務需求來建立的,是以這個分層标準,也是需要我們溝通業務後,得到最後的分層标準。
2、特征選取
從模型所需字段來看,我們隻需要使用者名稱、交易金額兩個字段即可,但考慮到地域分布,是以把地區相關的字段也加入,其次我們說R是最近一次購買距離現在多少天,所日期字段需要進行處理。
import pandas as pd
import pymysql
from sqlalchemy import create_engine
# 初始化資料庫連接配接,使用pymysql子產品
# MySQL的使用者:root, 密碼:123456, 端口:3306,資料庫:lean2
con = create_engine('mysql+pymysql://root:[email protected]:3306/rfm')
sql = '''select buyername,shiptime,money,province,city from orders_new;'''
# read_sql_query的兩個參數: sql語句, 資料庫連接配接
df = pd.read_sql_query(sql, con = con)
display(df.head())
#由于訓練資料集固定的原因,先假定今天為2019年6月1日
df["day_s"]=(pd.to_datetime("2019/6/1") - pd.to_datetime(df['shiptime'],errors='coerce')).dt.days
display(df.tail())
3、特征值轉化
每個使用者最近一次購買到今天的天數R,每個使用者購買的總體頻率(F)以及消費金額(M)
group_df = df[["buyername",'province','city','money','day_s']].groupby(["buyername",'province','city']).agg({"day_s":"min","money":["count","sum"]})
group_df.head()
group_df.columns = ["R","F","M"]
display(group_df[group_df["F"]>5])
4、客戶分類
display(group_df.describe()) #檢視三特征變量的統計資訊
如果以上三個次元将資料劃分為5個資料等級,這樣就能夠細分出5x5x5=125類使用者,再根據每類使用者精準營銷……顯然不可能為125類使用者量體定制營銷政策。實際運用上,我們把每個次元劃分為2個資料等級(按照均值/中位數劃分),這樣在3個次元上我們依然得到了8組使用者,基本達到我們的需求。優質客戶等級用1表示,易流失、消費頻次低、消費金額少用0表示
RFM模型分析
- 重要價值客戶(111):最近消費時間近、消費頻次和消費金額都很高。
- 普通忠實客戶(110):最近消費時間較近、消費頻次高,但消費金額不高。
- 重要交流客戶(011):最近消費時間較遠,但消費頻次和金額都很高,說明這是個一段時間沒來的忠誠客戶,我們需要主動和他保持聯系。
- 重要發展客戶(101):最近消費時間較近、消費金額高,但頻次不高,忠誠度不高,很有潛力的使用者,必須重點發展。
- 新客戶(100):最近消費時間較近,但消費頻次和金額都不高。
- 重要挽留客戶(001):最近消費時間較遠、消費頻次不高,但消費金額高的使用者,可能是将要流失或者已經要流失的使用者,應當極力挽留措施。
- 盡量挽留客戶(010):最近消費時間較遠、消費頻次高,消費金額低的使用者,原來的普通忠實客戶,可能即将要流失或者已經流水,普通公司還得靠這群人支撐。這層級的使用者多,那麼就要考慮産品自身的因素了。
- 散客(000):最近消費時間較遠、消費頻次不高,消費金額也不高的使用者。
這裡我們按照中位數劃分,中位數劃分的好處在于受離群點的影響比均值更小。
#計算每個次元的中位數,按照中位數劃分
R_median= group_df.median()["R"]
F_median= group_df.median()["F"]
M_median= group_df.median()["M"]
display(R_median,F_median,M_median)
# 客戶分類
#注意時間天數是越小越好。
def func1(x):
if x<R_median:
return 1
else:
return 0
def func2(x):
if x>F_median:
return 1
else:
return 0
def func3(x):
if x>M_median:
return 1
else:
return 0
group_df["R_median"] = group_df["R"].apply(func1)
group_df["F_median"] = group_df["F"].apply(func2)
group_df["M_median"] = group_df["M"].apply(func3)
display(group_df.sample(10))
5、客戶貼标簽
#使用者貼标簽
def fun_abel(x):
if x.iloc[0]==1 and x.iloc[1]==1 and x.iloc[2]== 1:
return "1重要價值客戶"
elif x.iloc[0]==1 and x.iloc[1]==1 and x.iloc[2]== 0:
return "2普通忠實客戶"
elif x.iloc[0]==0 and x.iloc[1]==1 and x.iloc[2]== 1:
return "3重要交流客戶"
elif x.iloc[0]==1 and x.iloc[1]==0 and x.iloc[2]== 1:
return "4重要發展客戶"
elif x.iloc[0]==1 and x.iloc[1]==0 and x.iloc[2]== 0:
return "5新客戶"
elif x.iloc[0]==0 and x.iloc[1]==0 and x.iloc[2]== 1:
return "6重要挽留客戶"
elif x.iloc[0]==0 and x.iloc[1]==1 and x.iloc[2]== 0:
return "7盡量挽留客戶"
elif x.iloc[0]==0 and x.iloc[1]==0 and x.iloc[2]== 0:
return "8散客"
group_df["label"]=group_df[['R_median','F_median','M_median']].apply(fun_abel,axis=1)
display(group_df.head())
6、儲存RFM及labal資料
import pandas as pd
import pymysql
from sqlalchemy import create_engine
#轉化一下表,去除多個索引
df2 = group_df.reset_index(0, drop=False)
df2 = df2.reset_index(0, drop=False)
group_df = df2.reset_index(0, drop=False)
display(group_df.head())
# 初始化資料庫連接配接,使用pymysql子產品
# MySQL的使用者:root, 密碼:123456, 端口:3306,資料庫:lean2
con = create_engine('mysql+pymysql://root:[email protected]:3306/rfm')
#直接寫入資料-->mydf-->mydf表會自動建立
# 将建立的DataFrame儲存為MySQL中的資料表,儲存index列
group_df.to_sql('orders_rfm', con, index=True)
print('Read from and write to Mysql table successfully!')
7、統計分類客戶人群分布
label_df = group_df.groupby('label').agg({'label':"count"})
label_df.columns = ['count']
display(label_df.T )
label_df=label_df.sort_index(axis=0, ascending=False)
x=label_df.index.values
y=label_df["count"]
figure = plt.figure(num="使用者分類",figsize=(8,4),dpi=80,facecolor="LightGray",edgecolor="blue",frameon=True)
axes1=figure.add_subplot(1,1,1)
plt.barh(x, y, align='center',color=['gray','orange','r','y','c','m','y','g'], ecolor='black')
plt.title("使用者分類數量圖")
plt.xlabel("數量")
for y, x in enumerate(y):
plt.text(x+1, y, "%s" %x)
plt.show()
8、代碼封裝
略