時(shí)間：2023-03-15 23:58:01 | 來源：電子商務(wù)
時(shí)間：2023-03-15 23:58:01 來源：電子商務(wù)
在線咨詢
# 導(dǎo)庫import numpy as npimport pandas as pdimport matplotlib.pyplot as pltimport seaborn as sns%matplotlib inlinefrom pyecharts.charts import *from pyecharts import options as opts# plt顯示中文plt.rcParams['font.sans-serif'] = ['SimHei'] #顯示中文標(biāo)簽plt.rcParams['axes.unicode_minus'] = False #顯示符號import warningswarnings.filterwarnings('ignore')導(dǎo)入數(shù)據(jù)data0 = pd.read_excel(r'E:/jupyter data/superstore_dataset2011-2015.csv')data0.head()出現(xiàn)報(bào)錯(cuò)：XLRDError: Unsupported format, or corrupt file: Expected BOF record; found b'Row ID,O'data0 = pd.read_excel(r'E:/jupyter data/superstore_dataset2011-2015.xlsx')data0.head()data1 = data0.copy()data1.columns=['行編號','訂單ID','訂單日期','發(fā)貨時(shí)間','發(fā)貨模式','客戶ID','客戶姓名','客戶類別','客戶所在城市','客戶城市所在州','客戶所在國家','郵編','marker所屬區(qū)域','marker所屬洲','產(chǎn)品ID','產(chǎn)品類別','產(chǎn)品子類別','產(chǎn)品名稱','銷售額','銷售量','折扣','利潤','發(fā)貨成本','訂單優(yōu)先級','不知名24','不知名25']2）缺失值處理(data1.isnull().sum().sort_values(ascending=False) / data1.shape[0]).apply(lambda x: format(x, '.2%'))由于不知名的那兩列不知道特征含義且缺失率非常大，可以刪除；“郵編”缺失率79%，但這個(gè)特征沒什么用，可以不用管。剩下的幾個(gè)特征的缺失值先不管，用到再說# 剔除掉不知名的兩列data2 = data1.iloc[:,:-2]3）數(shù)據(jù)格式轉(zhuǎn)化# "訂單日期"data2['訂單日期'] = pd.to_datetime(data2['訂單日期']) # 轉(zhuǎn)化為日期格式data2 = data2.sort_values(by='訂單日期', ascending=True) # 排序data2['訂單日期_月'] = data2['訂單日期'].map(lambda x:x.month) # 增加列data2['訂單日期_年'] = data2['訂單日期'].map(lambda x:x.year)# "發(fā)貨時(shí)間"data2['發(fā)貨時(shí)間'] = pd.to_datetime(data2['發(fā)貨時(shí)間']) # 轉(zhuǎn)化為日期格式data2.reset_index(drop=True,inplace=True) # 重置索引# 銷售額、利潤這兩個(gè)字段存在非數(shù)字的異常值data2['銷售額'] = data2['銷售額'].astype(float)ValueError: could not convert string to float: ' Metal"'data2.shape[0] - sum(data2['銷售額'].apply(lambda x:type(x) is float)), data2.shape[0] - sum(data2['利潤'].apply(lambda x:type(x) is float)) # 異常值數(shù)目(1555, 1438)# 直接剔除data2 = data2[data2['銷售額'].apply(lambda x:type(x) is float)]data2 = data2[data2['利潤'].apply(lambda x:type(x) is float)]data2.reset_index(drop=True,inplace=True)# 轉(zhuǎn)化類型data2['銷售額'] = data2['銷售額'].astype(float) data2['銷售量'] = data2['銷售量'].astype('int')data2['利潤'] = data2['利潤'].astype(float)4）增加指標(biāo)# 增加銷售單價(jià)列data2['銷售單價(jià)'] = data2['銷售額'] / data2['銷售量']data2['星期'] = data2['訂單日期'].apply(lambda x:x.isoweekday()) # 提取出周幾data2.describe()超過一半數(shù)據(jù)條的銷售額、銷售單價(jià)都以低價(jià)為主，但注意有25%的商品的利潤都為負(fù)，可能要減少庫存從而打折清倉處理data_sale = data2.groupby('訂單日期_年').sum()[['銷售額', '利潤', '銷售量']]data_sale['利潤率'] = data_sale['利潤'] / data_sale['銷售額']data_saleriqi_nian = list(data_sale.index.astype(str)) # pyechart里面要求str格式作x坐標(biāo)軸xiaoshoue = list(data_sale['銷售額'].astype(int)) # 為了避免小數(shù)，直接轉(zhuǎn)化為intlirun = list(data_sale['利潤'].astype(int))lirunlv = list(round(data_sale['利潤率']*100,2))rate = data_sale.pct_change() # 計(jì)算同比rate# 可視化bar1 = (Bar() .add_xaxis(riqi_nian) .add_yaxis('銷售額', xiaoshoue , xaxis_index=1, yaxis_index=1) .add_yaxis('利潤', lirun , xaxis_index=1, yaxis_index=1) .extend_axis(yaxis = opts.AxisOpts(name="百分比" ,type_="value" ,min_=6 ,axisline_opts=opts.AxisLineOpts(linestyle_opts=opts.LineStyleOpts(color="#d14a61")) #右邊坐標(biāo)軸顏色 ,axislabel_opts=opts.LabelOpts(formatter="{value} %"))) .set_global_opts(title_opts = opts.TitleOpts(title="2011年-2014年整體盈利情況") ,yaxis_opts = opts.AxisOpts(axislabel_opts=opts.LabelOpts(formatter="{value} 元")) # 左邊坐標(biāo)軸 ,toolbox_opts = opts.ToolboxOpts(is_show=True)) # 工具箱顯示 )line1 = (Line() .add_xaxis(riqi_nian) .add_yaxis("利潤率", lirunlv , xaxis_index=1 , yaxis_index=2) .set_global_opts(yaxis_opts=opts.AxisOpts(type_="value" ,min_=6 ,name='百分比')) )overlap1 = bar1.overlap(line1)line2 = (Line() .add_xaxis(riqi_nian[1:]) .add_yaxis("銷售額", list(round(rate['銷售額']*100,2))[1:] , xaxis_index=0, yaxis_index=0) .add_yaxis("利潤", list(round(rate['利潤']*100,2))[1:] , xaxis_index=0, yaxis_index=0) .add_yaxis("銷售量", list(round(rate['銷售量']*100,2))[1:] , xaxis_index=0, yaxis_index=0) .add_yaxis("利潤率", list(round(rate['利潤率']*100,2))[1:] , xaxis_index=0, yaxis_index=0) .set_global_opts(title_opts=opts.TitleOpts(title="2012年-2014年各項(xiàng)同比增長率" , pos_top="48%"), legend_opts=opts.LegendOpts(pos_top="48%") ,yaxis_opts = opts.AxisOpts(axislabel_opts=opts.LabelOpts(formatter="{value} %"))) )grid = (Grid(init_opts=opts.InitOpts(width="1000px", height="700px")) .add(line2, grid_opts=opts.GridOpts(pos_top="60%"), is_control_axis_index=True) .add(overlap1, grid_opts=opts.GridOpts(pos_bottom="60%"), is_control_axis_index=True) )# 這里先add(line2)，或許畫圖順序是從下面開始，所以上面line2的xaxis與yaxis都比overlap1先畫。grid.render_notebook()* 四年間銷售額和利潤都在穩(wěn)步增長，但與大型超市(如：沃爾瑪)的銷售額差距明顯巨大。data_sale_month = pd.pivot_table(data2 ,index='訂單日期_月' ,columns='訂單日期_年' ,values=['銷售額','利潤','銷售量'] ,aggfunc='sum')data_sale_month_lirunlv = data_sale_month['利潤']/data_sale_month['銷售量'] # 利潤率data_sale_monthfig,axes = plt.subplots(2,2,figsize = (12,8),dpi=100)data_sale_month['銷售額'].plot.area(colormap = 'Accent_r', stacked=False,ax=axes[0,0],title='銷售額')data_sale_month['利潤'].plot.area(colormap = 'Accent_r', stacked=False,ax=axes[0,1],title='利潤')data_sale_month['銷售量'].plot.area(colormap = 'Accent_r', stacked=False,ax=axes[1,0],title='銷售量')data_sale_month_lirunlv.plot.line(ax=axes[1,1], title='利潤率')plt.subplots_adjust(wspace=0.3, hspace=0.3) # wspace,hspace 把間距變大* 總體來看，季節(jié)性明顯，其中上半年淡季居多，而下半年旺季居多# 銷售額每月同比增長data_sale_month['銷售額'].pct_change(axis='columns').iloc[:,1:].style.background_gradient(cmap='Greens',low=0,high=1)每月銷售額都是同比增長的，但增長率大小并沒發(fā)現(xiàn)什么規(guī)律，這給下一年拆分業(yè)績指標(biāo)其實(shí)增加了一定難度week_xiaoshou = data2.groupby('星期')['銷售量'].sum()week_xiaoshoux = week_xiaoshou.index.tolist()y = week_xiaoshou.values.tolist()bar = (Bar() .add_xaxis(x) .add_yaxis("銷售額", y) .set_global_opts(title_opts=opts.TitleOpts(title="工作日銷售額明顯高于節(jié)假日")) )bar.render_notebook()原因可能是節(jié)假日消費(fèi)者更加喜歡線下消費(fèi)data2['打折損失'] = data2[data2['折扣']!=0.0]['折扣']*data2[data2['折扣']!=0.0]['銷售額']data2['打折損失'] = data2['打折損失'].fillna(0)data2.groupby('訂單日期_年')['打折損失'].sum()5.2. 商品分析table = data2[data2['產(chǎn)品子類別']=='Tables']other = data2[data2['產(chǎn)品子類別']!='Tables']# 打折率對比table[table['折扣']!=0]['折扣'].count() / table['折扣'].count(), other[other['折扣']!=0]['折扣'].count() / other['折扣'].count()(0.7617896009673518, 0.4453779747526728)# 打折程度對比table[table['折扣']!=0]['折扣'].mean(), other[other['折扣']!=0]['折扣'].mean()(0.38733333333333225, 0.3271326164874401)# 發(fā)貨成本對比table['發(fā)貨成本'].mean(), other['發(fā)貨成本'].mean()(92.52696493349451, 25.154532756411655)# table負(fù)利潤率sum(table['利潤']<0) / table['利潤'].count()0.5888754534461911# 假設(shè)打折率與其他類別相同a1 = int(table['折扣'].count() * (0.76-0.44)) # 不該打折的數(shù)據(jù)條數(shù)目a2 = table[table['折扣']!=0]['折扣'].count() - a1 # 應(yīng)該打折數(shù)table_ = table[table['折扣']!=0].sample(a2) loss0 = (table[table['折扣']!=0]['折扣']*table[table['折扣']!=0]['銷售額']).sum()loss1 = (table_['銷售額']*table_['折扣']).sum()loss2 = loss0-loss1 # 其他類被打折率相同時(shí)補(bǔ)回的損失# 假設(shè)table的打折程度和其他類別相同loss3 = (table_['折扣'].mean()-other[other['折扣']!=0]['折扣'].mean())*table_['銷售額'].sum() # 假設(shè)發(fā)貨成本和其他類別相同loss4 = table['利潤'].count()*67new_table_lirun = table['利潤'].sum()+loss2+loss3+loss4new_table_lirun該理想利潤處于各類別利潤的中位數(shù)，初步下結(jié)論table利潤為負(fù)的原因?yàn)椋捍黉N活動(dòng)與發(fā)貨成本高。表現(xiàn)為table促銷率比其他類別高7%，打折程度高6%，發(fā)貨成本高67元左右。初步判斷為清倉產(chǎn)品，但如果不是，說明這個(gè)產(chǎn)品在市場推廣上遇到了瓶頸，或者是遇到強(qiáng)競爭對手，需要結(jié)合實(shí)際業(yè)務(wù)進(jìn)行分析，適當(dāng)改善經(jīng)營策略。而發(fā)貨成本，由于table為大件物品，可以優(yōu)化貨倉的庫存位置，盡量以最短路配送到消費(fèi)者手中。# 各商品銷售額同比sale_leibie = pd.pivot_table(data2 ,index=['產(chǎn)品類別','產(chǎn)品子類別'] ,columns='訂單日期_年' ,values=['銷售額'] ,aggfunc='sum')sale_leibiesale_leibie.pct_change(axis='columns').style.background_gradient(cmap='Greens',low=0,high=1)幾乎所有種類都是同比增長的。basket = data2.pivot_table(index='訂單ID' ,columns='產(chǎn)品ID' ,values='銷售量' ,aggfunc=np.sum).fillna(0)basket出現(xiàn)報(bào)錯(cuò)，原因不明。data2['產(chǎn)品詳細(xì)類別'] = data2['產(chǎn)品類別'] +','+data2['產(chǎn)品子類別']price_max = data2.groupby('產(chǎn)品詳細(xì)類別')[['銷售單價(jià)']].max().round(2)price_min = data2.groupby('產(chǎn)品詳細(xì)類別')[['銷售單價(jià)']].min().round(2)price_max.rename(columns={'銷售單價(jià)':'最高價(jià)'}, inplace=True)price_min.rename(columns={'銷售單價(jià)':'最低價(jià)'}, inplace=True)price_width = price_min.join(price_max)price_width['價(jià)格帶(元)'] = '('+price_width['最低價(jià)'].astype(str)+', '+price_width['最高價(jià)'].astype(str)+')'price_width['價(jià)格帶寬度(元)'] = price_width['最高價(jià)'] - price_width['最低價(jià)']price_width價(jià)格帶深度：同一類商品品牌數(shù)或SKU數(shù)(保存庫存控制的最小可用單位)def drop_duplicates (group): # 用來去重 return group.drop_duplicates().count()place_deep = data2.groupby('產(chǎn)品詳細(xì)類別')[['產(chǎn)品ID']].agg(drop_duplicates)place_deep.rename(columns={'產(chǎn)品ID':'價(jià)格帶深度(個(gè))'}, inplace=True)place_deep價(jià)格帶廣度:同一類商品中不重復(fù)銷售價(jià)格的數(shù)量place_range = data2.groupby('產(chǎn)品詳細(xì)類別')[['銷售單價(jià)']].agg(drop_duplicates)place_range.rename(columns={'銷售單價(jià)':'價(jià)格帶廣度(個(gè))'}, inplace=True)place_rangeplace_analysis = price_width[['價(jià)格帶(元)']].join(price_width[['價(jià)格帶寬度(元)']]).join(place_deep[['價(jià)格帶深度(個(gè))']]).join(place_range[['價(jià)格帶廣度(個(gè))']])place_analysis由于產(chǎn)品子類別這個(gè)字段仍比較寬泛，導(dǎo)致這里價(jià)格帶寬度普遍都比較大，進(jìn)而導(dǎo)致這里價(jià)格帶的分析起來并不準(zhǔn)確，詳細(xì)分析需要更詳細(xì)的產(chǎn)品類別數(shù)據(jù)集leibie = data2['產(chǎn)品詳細(xì)類別'].unique().tolist()fig = plt.figure(figsize = (18,18))for i in range(len(data2['產(chǎn)品詳細(xì)類別'].unique().tolist())): df1 = data2[data2['產(chǎn)品詳細(xì)類別']==leibie[i]].groupby('銷售單價(jià)')[['銷售量']].sum() sns.set(style='whitegrid') plt.subplot(5,4, i+1) sns.distplot(df1['銷售量'], bins=10) plt.xlabel(' ') plt.title(str(leibie[i]))大多數(shù)品種價(jià)格以偏態(tài)分布為主，即價(jià)格越低，銷量越高。小部分品種(如fasteners紐扣)會呈現(xiàn)對稱分布，還有一些會出現(xiàn)多峰情況，企業(yè)要根據(jù)品種的價(jià)格分布合理生產(chǎn)，避免庫存積壓。# datetime64格式使用.days提取日期data2['發(fā)貨速度'] = (data2['發(fā)貨時(shí)間']-data2['訂單日期']).apply(lambda x:x.days)data2.groupby('產(chǎn)品詳細(xì)類別')['發(fā)貨速度'].mean()由于不了解具體業(yè)務(wù)流程，'訂單日期'與'發(fā)貨時(shí)間'先后關(guān)系不明確，這里就不進(jìn)行分析了customer_sale = data2.pivot_table('銷售額' ,index='客戶類別' ,columns='訂單日期_年' ,aggfunc='sum' )customer_sale # 銷售額customer_num = data2.pivot_table('客戶ID' ,index='客戶類別' ,columns='訂單日期_年' ,aggfunc='count' )customer_num # 客戶人數(shù)customer_num = customer_num.astype(float) # 不知道為什么pyechart可視化要變成float形式list1 = []for i in range(customer_sale.shape[1]): list1.append({"value":customer_sale.iloc[0,i], "percent":customer_sale.iloc[0,i]/customer_sale.iloc[:,i].sum()})list2 = []for i in range(customer_sale.shape[1]): list2.append({"value":customer_sale.iloc[1,i], "percent":customer_sale.iloc[1,i]/customer_sale.iloc[:,i].sum()})list3 = []for i in range(customer_sale.shape[1]): list3.append({"value":customer_sale.iloc[2,i], "percent":customer_sale.iloc[2,i]/customer_sale.iloc[:,i].sum()})from pyecharts.commons.utils import JsCodefrom pyecharts.globals import ThemeTypebar3 = (Bar(init_opts=opts.InitOpts(theme=ThemeType.LIGHT)) .add_xaxis([2011,2012,2013,2014]) .add_yaxis("Consumer", list1, stack='stack1', category_gap="50%") .add_yaxis("Corporate", list2, stack='stack1', category_gap="50%") .add_yaxis("Home Office", list3, stack='stack1', category_gap="50%") .set_series_opts(label_opts=opts.LabelOpts(position="right" ,formatter=JsCode("function(x){return Number(x.data.percent * 100).toFixed() + '%';}")) ) )bar3.render_notebook()list4 = []for i in range(customer_num.shape[1]): list4.append({"value":customer_num.iloc[0,i], "percent":customer_num.iloc[0,i]/customer_num.iloc[:,i].sum()})list5 = []for i in range(customer_num.shape[1]): list5.append({"value":customer_num.iloc[1,i], "percent":customer_num.iloc[1,i]/customer_num.iloc[:,i].sum()})list6 = []for i in range(customer_num.shape[1]): list6.append({"value":customer_num.iloc[2,i], "percent":customer_num.iloc[2,i]/customer_num.iloc[:,i].sum()})bar2 = (Bar(init_opts=opts.InitOpts(theme=ThemeType.LIGHT)) .add_xaxis([2011,2012,2013,2014]) .add_yaxis("Consumer", list4, stack='stack1', category_gap="50%") .add_yaxis("Corporate", list5, stack='stack1', category_gap="50%") .add_yaxis("Home Office", list6, stack='stack1', category_gap="50%") .set_series_opts(label_opts=opts.LabelOpts(position="right" ,formatter=JsCode("function(x){return Number(x.data.percent * 100).toFixed() + '%';}")) ) )bar2.render_notebook()每類消費(fèi)者的銷售額、消費(fèi)人數(shù)都穩(wěn)步增長，但Consumer體量最大，增長相對較慢，可能有點(diǎn)飽和，而Home office體量小增長快，似乎還有很大的市場，可以根據(jù)精準(zhǔn)營銷拉新。customer_month = pd.pivot_table(data2 ,index='訂單日期_月' ,columns='訂單日期_年' ,values=['銷售額', '訂單ID', '客戶ID', '銷售量'] ,aggfunc = {'銷售額':'sum' # 消費(fèi)額 ,'訂單ID':drop_duplicates # 訂單數(shù) ,'客戶ID':drop_duplicates # 消費(fèi)人數(shù) ,'銷售量':'sum'}) # 銷售總量customer_month.rename(columns = {'銷售額':'消費(fèi)額' ,'訂單ID':'訂單數(shù)' ,'客戶ID':'消費(fèi)人數(shù)'},inplace = True) customer_monthfig,axes = plt.subplots(2,2,figsize = (12,8),dpi=100)# plt顯示中文plt.rcParams['font.sans-serif'] = ['SimHei'] #顯示中文標(biāo)簽plt.rcParams['axes.unicode_minus'] = False #顯示符號customer_month['消費(fèi)人數(shù)'].plot.area(colormap = 'Accent_r', stacked=False,ax=axes[0,0],title='消費(fèi)人數(shù)')customer_month['訂單數(shù)'].plot.area(colormap = 'Accent_r', stacked=False,ax=axes[0,1],title='消費(fèi)次數(shù)')(customer_month['消費(fèi)額'] / customer_month['訂單數(shù)']).plot.line(colormap = 'Accent_r', stacked=False,ax=axes[1,0],title='單次消費(fèi)金額')(customer_month['銷售量'] / customer_month['訂單數(shù)']).plot.line(colormap = 'Accent_r', stacked=False,ax=axes[1,1],title='連帶率')plt.subplots_adjust(wspace=0.3, hspace=0.3) # wspace,hspace 把間距變大明顯看出每年大概6-11月都會是旺季，其余則為淡季。每年6月-7月、9-10月、12-1月都會出現(xiàn)不同程度的下跌。單次消費(fèi)金額與連帶率沒看出什么規(guī)律。data2['訂單日期_年月'] = data2['訂單日期_年'].astype(str)+'-'+data2['訂單日期_月'].astype(str)data2['訂單日期_年月'] = pd.to_datetime(data2['訂單日期_年月'])month_buy_ = data2.pivot_table(index='客戶ID' ,columns='訂單日期_年月' ,values='行編號' ,aggfunc='count').fillna(0) # 某些用戶在某月沒有消費(fèi)過，用nan表示，這里用0填充month_buy = month_buy_.applymap(lambda x: 1 if x>0 else 0)month_buy.head()#用戶狀態(tài)def active_status(data): status = [] for i in range(month_buy.shape[1]): if data[i] == 0: #若本月沒有消費(fèi) if len(status) > 0: # 2011-01之后 if status[i-1] == 'unreg': # 未注冊 status.append('unreg') else: status.append('unactive') else: # 2011-01 status.append('unreg') else: #若本月消費(fèi) if len(status) == 0: # 2011-01 status.append('new') else: # 2011-01之后 if status[i-1] == 'unactive': status.append('return') # 回流 elif status[i-1] == 'unreg': status.append('new') else: status.append('active') return pd.Series(status, index = month_buy.columns) # 這里需要對返回的值進(jìn)行轉(zhuǎn)換，將列表轉(zhuǎn)為Seriescustomer_status = month_buy.apply(active_status,axis=1)customer_status.head()customer_status_num = customer_status.apply(lambda x : pd.value_counts(x)).fillna(0).T # 統(tǒng)計(jì)每類的數(shù)量customer_status_num.index = pd.DataFrame(customer_status_num.index)['訂單日期_年月'].astype(str).apply(lambda x:x[:-3]).tolist()customer_status_numx_data = customer_status_num.index.tolist()y_data1 = customer_status_num['active'].values.tolist()y_data2 = customer_status_num['new'].values.tolist()y_data3 = customer_status_num['return'].values.tolist()y_data4 = customer_status_num['unactive'].values.tolist()y_data5 = customer_status_num['unreg'].values.tolist()line = (Line() .add_xaxis(x_data) .add_yaxis('active', y_data1, label_opts=opts.LabelOpts(is_show=False)) .add_yaxis('new', y_data2, label_opts=opts.LabelOpts(is_show=False)) .add_yaxis('return', y_data3, label_opts=opts.LabelOpts(is_show=False)) .add_yaxis('unactive', y_data4, label_opts=opts.LabelOpts(is_show=False)) .add_yaxis('unreg', y_data5, label_opts=opts.LabelOpts(is_show=False)) )line.render_notebook()四年來，活躍用戶都在穩(wěn)步增加，說明召回運(yùn)營工作有點(diǎn)成效，但總量相比與不活躍用戶占比仍比較??；而回流用戶變化不大，可能沒怎么注重該方面運(yùn)營；每月的新客不多，13后每月新客的增加甚至只有個(gè)位數(shù)了，可能受限于市場規(guī)模，拉新是個(gè)大問題。所以往后就更需要老顧客的消費(fèi)來拉動(dòng)銷售額，應(yīng)該特別注重召回運(yùn)營的工作，一旦用戶流失，在沒有新客進(jìn)入的情況下，企業(yè)受損會有點(diǎn)嚴(yán)重。# 只消費(fèi)一次的客戶a = data2.groupby('客戶ID')['訂單日期'].agg(['min', 'max']).reset_index()bug_one = (a['min'] == a['max']).value_counts().values (a['min'] == a['max']).value_counts()絕大多數(shù)客戶都不會只消費(fèi)一次，該超市口碑至少不錯(cuò)復(fù)購率復(fù)購率的定義：在某時(shí)間窗口內(nèi)消費(fèi)兩次及以上的用戶在總消費(fèi)用戶中占比。這里的時(shí)間窗口是月，如果一個(gè)用戶在同一天下了兩筆訂單，這里也將他算作復(fù)購用戶#轉(zhuǎn)換：消費(fèi)2次以上記為1，消費(fèi)1次記為0，消費(fèi)0次記為NAN #applymap針對dataframe所有數(shù)據(jù)transf = month_buy_.applymap(lambda x: 1 if x>1 else np.nan if x==0 else 0)transf.head()#count統(tǒng)計(jì)所有非空數(shù)據(jù)個(gè)數(shù)表示總消費(fèi)用戶數(shù)，sum計(jì)算非0數(shù)據(jù)的和表示消費(fèi)兩次以上的用戶數(shù)df_duplicate = pd.DataFrame(transf.sum() / transf.count()).reset_index()df_duplicate.columns = ['Date', 'DuplicatedRate']df_duplicate['Date'] = df_duplicate['Date'].astype(str).apply(lambda x:x[:-3])df_duplicatex_data = df_duplicate['Date'].tolist()y_data = df_duplicate['DuplicatedRate'].tolist()line = (Line() .add_xaxis(x_data) .add_yaxis('', y_data ,label_opts=opts.LabelOpts(is_show=False)) )line.render_notebook()# 每個(gè)客戶的每次購買時(shí)間間隔buy_diff = data2.groupby('客戶ID').apply(lambda x:x['訂單日期'] - x['訂單日期'].shift())# shift()把第一個(gè)元素變?yōu)镹an，并且彈出最后一個(gè)元素，類似求解等比數(shù)列的思想buy_diffdiff = (buy_diff / np.timedelta64(1, 'D')).dropna() sns.distplot(diff[(diff.values>0)&(diff.values<500)], bins=30, kde=True)呈現(xiàn)指數(shù)分布，大部分的訂單客戶消費(fèi)周期都很短，對該超市的黏性不錯(cuò)# 假設(shè)今天是2014-12-31日buy_life = (data2.groupby('客戶ID')['訂單日期'].max() - data2.groupby('客戶ID')['訂單日期'].min()).reset_index()buy_life['life_time'] = buy_life['訂單日期'] / np.timedelta64(1,'D')buy_lifebuy_life['life_time'].plot.hist(bins=15, title='客戶生命周期分布')大部分客戶生命周期都有3年，整體比較健康data2['是否特價(jià)'] = data2['折扣'].apply(lambda x: 1 if x>0.5 else 0) # 取折扣大于0.5為特價(jià)商品customer_tejia = pd.DataFrame(data2.groupby('客戶ID')['是否特價(jià)'].sum() / data2.groupby('客戶ID')['是否特價(jià)'].count()) #購買特價(jià)商品的占比customer_tejia_ = customer_tejia[customer_tejia['是否特價(jià)']>0.5] # 取占比大于0.5的為熱衷特價(jià)商品消費(fèi)用戶customer_tejia_['是否特價(jià)'].sort_values(ascending=False)-高單價(jià)商品消費(fèi)用戶pd.DataFrame(data2.groupby('客戶ID')['銷售單價(jià)'].mean().sort_values(ascending=False)).iloc[:10,:] # 高消費(fèi)的前十位rfm = pd.pivot_table(data2 ,index='客戶ID' ,values=['銷售額', '訂單日期','訂單ID'] ,aggfunc = {'銷售額':'sum', # money '訂單日期':'max', # 最近一次消費(fèi)時(shí)間 '訂單ID':'count'}) # frequencyrfm.rename(columns = {'銷售額':'M', '訂單ID':'F'},inplace = True) rfm.head()# 假設(shè)今天為2014-12-31rfm['R'] = (rfm['訂單日期'].max() - rfm['訂單日期'])/ np.timedelta64(1, 'D')# rfm['訂單日期']里面的數(shù)據(jù)類型是timedflta時(shí)間,無法直接作出直方圖，先換算成數(shù)值,換算的方式直接除timedelta函數(shù)即可，np.timedelta64(1, ‘D’)，D表示天，1表示1天，作為單位使用的。rfm.head()sns.pairplot(rfm[['F','M','R']])rfm_std = rfm[['R']].apply(lambda x: -(x-x.mean()) / x.std()) # 先轉(zhuǎn)化為極大值，再標(biāo)準(zhǔn)化rfm_std[['F','M']] = rfm[['F','M']].apply(lambda x: (x-x.mean()) / x.std())rfm_std# 構(gòu)建rfm模型公式,用平均值進(jìn)行劃分def get_rfm(x): """ R: 用戶最近一次消費(fèi)的間隔， 1為表現(xiàn)好 F: 用戶最近一段時(shí)間的消費(fèi)次數(shù)， 1為表現(xiàn)好 M: 用戶最近一段時(shí)間的交易金額， 1為表現(xiàn)好 """ level = x.apply(lambda x:'1' if x>=0 else '0') label = level['R'] + level['F'] + level['M'] dict = {'111':'重要價(jià)值客戶', '011':'重要保持客戶', '101':'重要挽留客戶', '001':'重要發(fā)展客戶', '110':'一般價(jià)值客戶', '010':'一般保持客戶', '100':'一般挽留客戶', '000':'一般發(fā)展客戶'} result = dict[label] return resultrfm['label'] = rfm_std.apply(get_rfm, axis=1)rfm.head()rfm_groupby = rfm.groupby('label')['M'].sum().sort_values()bar = ( Bar() .add_xaxis(rfm_groupby.index.tolist()) .add_yaxis("", np.around(rfm_groupby,0).tolist()) .reversal_axis() .set_series_opts(label_opts=opts.LabelOpts(position="right")) .set_global_opts(title_opts=opts.TitleOpts(title="各類用戶銷售額貢獻(xiàn)值")))bar.render_notebook()重要價(jià)值客戶貢獻(xiàn)了大部分的銷售額，將來運(yùn)營可以對不同的客戶類型進(jìn)行相應(yīng)營銷策略，特別要注重重要價(jià)值客戶的留存# 可以進(jìn)行打分quantiles = rfm.quantile(q=[0.25,0.5,0.75]).to_dict()quantiles# 我們最想要的客戶是：粘性高，忠誠度和收入高的用戶# 一般分成 3~5 段，這里我們分為4段def RScore(x,p,d): if x <= d[p][0.25]: return 1 elif x <= d[p][0.50]: return 2 elif x <= d[p][0.75]: return 3 else: return 4 def FMScore(x,p,d): if x <= d[p][0.25]: return 4 elif x <= d[p][0.50]: return 3 elif x <= d[p][0.75]: return 2 else: return 1rfm['r_quartile'] = rfm['R'].apply(RScore, args=('R',quantiles))rfm['f_quartile'] = rfm['F'].apply(FMScore, args=('F',quantiles))rfm['m_quartile'] = rfm['M'].apply(FMScore, args=('M',quantiles))#RFM score = r*100+f*10+m, 這里權(quán)重隨便設(shè)置的，111 為rfm score的最高分rfm['RFMScore'] = rfm.r_quartile*100 + rfm.f_quartile*10 +rfm.m_quartilerfm.head(5)# 劃分等級def rfm_level(RFMScore): if (RFMScore >= 0 and RFMScore < 122): return '1' elif (RFMScore >= 122 and RFMScore < 223): return '2' elif (RFMScore >= 223 and RFMScore < 333): return '3' return '4'rfm['RFMScore_level'] = rfm['RFMScore'].apply(rfm_level)rfm.head()# 不同rfm 分?jǐn)?shù)的分布import seaborn as snscolor = sns.color_palette()from pylab import mplmpl.rcParams['font.sans-serif'] = ['SimHei'] plt.figure(figsize=(6,8))sns.countplot(x='RFMScore_level',data=rfm, color = color[9])plt.ylabel('計(jì)數(shù)',fontsize=12)plt.xlabel('RFM得分水平', fontsize=12)plt.xticks(rotation='vertical')plt.title('RFM得分水平分布',fontsize=15)plt.show()關(guān)鍵詞：分析,數(shù)據(jù),零售
客戶&案例
營銷資訊
關(guān)于我們
微信公眾號
版權(quán)所有? 億企邦 1997-2025 保留一切法律許可權(quán)利。
