python数据分析电商用户行为看完这一篇就够了

1、ython数据分析电商户为，看完这篇就够了本分析主要内容分为下四个部分：.项背景.数据集介绍三.数据清洗四.分析模型构建五.总结. 项背景项对京东电商运营数据集进指标分析以了解户购物为特征，为运营决策提供持建议。 本采了MySQL和Python两种代码进指标计算以适应不同的数据分析开发环境。. 数据集介绍本数据集为京东竞赛数据集，详细介绍请访问链接： 。 数据集共有五个件，包含了2021-02-012021-04-15之间的户数据，数据已进了脱敏处理，本使了其中的为数据表，表中共有五个字段，各字段含义如下图所 为数据表（jdata_action）字段说明三. 数据清洗import pandas

2、 as pd%matplotlib inline# 设置中编码和负号的正常显复制代码复制代码user_data = user_action(user_actionaction_time 2018-03-30) & (user_actionaction_time =2.count()/total_buy_count.count()复制代码复制代码复制代码# 单个户消费总次数可视化tbc_box = total_buy_count.reset_index()fig, ax = plt.subplots(figsize=16,6)ax.set_yscale(log)sns.countplot(x=t

3、bc_boxtotal,data=tbc_box,palette=Set1)for p in ax.patches:ax.annotate(:.2f%.format(100*p.get_height()/len(tbc_boxtotal), (p.get_x() - 0.1, p.get_height()复制代码整个计算周期内，最购物次数为133次，最低为1次，部分户的购物次数在6次以下，可适当增加推，完善购物体验，提户消费次数。购物次数前10户为1187177、502169等，应提其满意度，增留存率。复制代码复购率为13.419%复制代码复购率较低，应加强户召回机制，提升购物体验，也可能因数

4、据量较少，统计周期之内的数据 法解释完整的购物周期，从得出结论有误。消费次数、活跃数、消费数、消费数占、消费户均消费次数consumption_per_buyer = daily_buy_count / daily_buy_user复制代码复制代码# 消费数占可视化# 柱状图数据pob_line = proportion_of_buyer.reset_index().rename(columns=user_id:Rate)sns.barplot(x=date, y=Value, hue=Variable, data=pob_bar, ax=ax1, alpha=0.8, palette=hus

5、l)for a,b in zip(x,pob_lineRate):plt.text(a+0.1, b + 0.001, %.2f% % (b*100), ha=center, va= bottom,fontsize=12)fig1.legend(loc=upper center,ncol=2)plt.title()复制代码活跃数与消费数明显波动，消费数占均在20%以上。cpb_line = (consumption_per_buyer.reset_index().rename(columns=0:Frequency)sns.barplot(x=date, y=Num, data=cpb_bar

6、, ax=ax3, alpha=0.8, palette=pastel)sns.pointplot(cpb_linedate, cpb_lineFrequency, ax=ax4, markers=D, linestyles=-,color=teal)x=list(range(0,16)for a,b in zip(x,cpb_lineFrequency):plt.text(a+0.1, b + 0.001, %.2f % b, ha=center, va= bottom,fontsize=12)plt.title()复制代码消费数在25000以上，均消费次数于1次。复制代码dau3_num

7、= dau3_df.groupby(date)user_id.nunique()复制代码SELECT date, COUNT(DISTINCT user_id)FROM(SELECT date, user_id, COUNT(type)FROM behavior_sql复制代码fig, ax = plt.subplots(figsize=16,6)for a,b in zip(x,dau3_num.values):plt.text(a+0.1, b + 300 , %i % b, ha=center, va= bottom,fontsize=14)复制代码)每活跃户数在部分4万以上，2018-

8、04-04之前数量较平稳，之后数量直攀升，8号9号达到最，随后下降，推测数据波动应为营销活动产的。复制代码(SELECT date, user_id, COUNT(type)FROM behavior_sqlGROUP BY date, user_idHAVING COUNT(type) = 3) dau3GROUP BY user_id;复制代码ax.set_yscale(log)复制代码统计周期内，部分活跃户累计活跃天数在六天以下，但也存在达六天的超级活跃户数量，对累计天数较的户要推出连续登录奖励等继续维持其对平台的黏性，对累计天数较低的户要适当进推送活动消息等对其进召回。#每浏览量pv_

9、daily = behaviorbehaviortype = pv.groupby(date)user_id.count()#每访客数uv_daily = behavior.groupby(date)user_id.nunique()复制代码# SQL#每浏览量SELECT date, COUNT(type) pv_daily FROM behavior_sqlWHERE type = pvGROUP BY date;#每访客数SELECT date, COUNT(DISTINCT user_id) uv_daily FROM behavior_sqlGROUP BY date;复制代码# 每

10、浏览量可视化fig, ax = plt.subplots(figsize=16,6)sns.pointplot(pv_daily.index, pv_daily.values,markers=D, linestyles=-,color=dodgerblue)x=list(range(0,16)for a,b in zip(x,pv_daily.values):plt.text(a+0.1, b + 2000 , %i % b, ha=center, va= bottom,fontsize=14)plt.title()复制代码复制代码浏览量和访客数每变化趋势致相同，2018-04-04前后户数量

11、变化波动较，44为清明节假前天，各数据量在当天均有明显下降，但之后逐步回升，推测应为节假营销活动或推拉新活动带来的影响。#每时浏览量pv_hourly = behaviorbehaviortype = pv.groupby(hour)user_id.count()#每时访客数uv_hourly = behavior.groupby(hour)user_id.nunique()复制代码复制代码复制代码# 访客数随时变化可视化fig, ax = plt.subplots(figsize=16,6)sns.pointplot(uv_hourly.index, uv_hourly.values, ma

12、rkers=H, linestyles=-,color=m)复制代码浏览量及访客数随时变化趋势致，在凌晨1点到凌晨5点之间，部分户正在休息，整体活跃度较低。凌晨5点到10点户开始起床作，活跃度逐渐增加，之后趋于平稳，下午6点之后部分恢复空闲，浏览量及访客数迎来了第波攀升，在晚上8点中到达峰，随后逐渐下降。可以考虑在上午9点及晚上8点增商品推度，加营销活动投，可取的较好的收益，1点到5点之间适合做系统维护。type_detail_weekday = pd.pivot_table(columns = type,index = weekday, data = behavior,aggfunc=np.

13、size,values = user_id)type_detail_weekday = type_detail_weekday.reindex(Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday)复制代码# SQL# 户各操作随时变化SELECT hour,GROUP BY hourORDER BY hour# 户各操作随星期变化SELECT weekday,GROUP BY weekdayORDER BY weekday复制代码tdh_line = type_detail_hour.stack().reset_index().re

14、name(columns=0: Value)tdw_line = type_detail_weekday.stack().reset_index().rename(columns=0: Value)tdh_line= tdh_line(tdh_linetype = pv)复制代码复制代码户操作随时变化规律与PV、UV随时规律相似，与户作息规律相关，加购物车和付款两条曲线贴合较紧密，说明部分户习惯加购物车后直接购买。关注数相对较少，可以根据户购物车内商品进精准推送。评论数也相对较少，说明部分户不是很热衷对购物体验进反馈，可以设置些奖励制度提户评论数，增户粘性。plt.title()复制代码周到周

15、四作期间，户操作随星期变化较平稳，周五周六进休息，户操作明显增多，周恢复正常。# 导相关包复制代码复制代码type_dis = behaviortype.value_counts().reset_index()type_disrate = round(type_distype / type_distype.sum(),3)复制代码复制代码户整体为中，有82.6%为为浏览，实际付操作仅占6.4,除此之外，户评论及收藏的为占也较低，应当增强站有户之间的互动，提评论数量和收藏率。复制代码df_pv_uid = df_condf_contype = pvuser_id.unique()df_fav_u

16、id = df_condf_contype = favuser_id.unique()df_cart_uid = df_condf_contype = cartuser_id.unique()df_pay_uid = df_condf_contype = payuser_id.unique()复制代码pv - buyfav_cart_list = set(df_fav_uid) | set(df_cart_uid)复制代码pv_pay_df = pd.merge(left=df_pv, right=df_pay, how=inner, on=user_id, sku_id,suffixes=(

17、_pv, _pay)复制代码pv_pay_df = pv_pay_df(pv_pay_dfuser_id.isin(fav_cart_list) & (pv_pay_dfaction_time_pv pv_pay_dfaction_time_pay)复制代码uv = behavioruser_id.nunique()pv_pay_num = pv_pay_dfuser_id.nunique()pv_pay_data = pd.DataFrame(type: ,num:uv,pv_pay_num)pv_pay_dataconversion_rates = (round(pv_pay_datanu

18、m / pv_pay_datanum0),4) * 100)复制代码values1 = list(pv_pay_data.conversion_rates)data1 = attr1i, values1i for i in range(len(attr1)复制代码gap=2,)复制代码pv -cart -paypv_cart_df = pd.merge(left=df_pv, right=df_cart, how=inner, on=user_id, sku_id,suffixes=(_pv, _cart)复制代码pv_cart_df = pv_cart_dfpv_cart_dfaction_

19、time_pv pv_cart_dfaction_time_cartpv_cart_df = pv_cart_dfpv_cart_dfuser_id.isin(df_fav_uid)复制代码pv_cart_pay_df = pd.merge(left=pv_cart_df, right=df_pay, how=inner, on=user_id, sku_id)复制代码pv_cart_pay_df = pv_cart_pay_dfpv_cart_pay_dfaction_time_cart pv_cart_pay_dfaction_time复制代码pv_cart_pay_num = pv_ca

20、rt_pay_dfuser_id.nunique()pv_cart_pay_data = pd.DataFrame(type: ,num:uv,pv_cart_num,pv_cart_pay_num)pv_cart_pay_dataconversion_rates = (round(pv_cart_pay_datanum / pv_cart_pay_datanum0),4) * 100)复制代码attr2 = list(pv_cart_pay_data.type)values2 = list(pv_cart_pay_data.conversion_rates)data2 = attr2i, v

21、alues2i for i in range(len(attr2)复制代码gap=2,)复制代码pv -fav -paypv_fav_df = pd.merge(left=df_pv, right=df_fav, how=inner, on=user_id, sku_id,suffixes=(_pv, _fav)复制代码pv_fav_df = pv_fav_dfpv_fav_dfaction_time_pv pv_fav_dfaction_time_favpv_fav_df = pv_fav_dfpv_fav_dfuser_id.isin(df_cart_uid)复制代码复制代码复制代码pv_

22、fav_pay_num = pv_fav_pay_dfuser_id.nunique()pv_fav_pay_data = pd.DataFrame(type: ,num:uv,pv_fav_num,pv_fav_pay_num)pv_fav_pay_dataconversion_rates = (round(pv_fav_pay_datanum / pv_fav_pay_datanum0),4) * 100)复制代码attr3 = list(pv_fav_pay_data.type)values3 = list(pv_fav_pay_data.conversion_rates)data3 =

23、 attr3i, values3i for i in range(len(attr3)复制代码gap=2,)复制代码pv -fav -cart -pay复制代码复制代码复制代码复制代码复制代码复制代码pv_fav_cart_pay_data = pd.DataFrame(type: ,num:uv,pv_fav_n,pv_fav_cart_n,pv_fav_cart_pay_n)pv_fav_cart_pay_dataconversion_rates = (round(pv_fav_cart_pay_datanum / pv_fav_cart_pay_datanum0),4) * 100)复制

24、代码attr4 = list(pv_fav_cart_pay_data.type)values4 = list(pv_fav_cart_pay_data.conversion_rates)data4 = attr4i, values4i for i in range(len(attr4)复制代码.add(series_name=,data_pair=data4,gap=2,tooltip_opts=opts.TooltipOpts(trigger=item, formatter=b : c%),label_opts=opts.LabelOpts(is_show=True, position=i

25、nside),itemstyle_opts=opts.ItemStyleOpts(border_color=#fff, border_width=1).set_global_opts(title_opts=opts.TitleOpts(title= )pv_fav_buy.render_notebook()复制代码不同路径户消费时间间隔分析pv - cart - pay复制代码pcp_intervalinterval = pcp_interval0.apply(lambda x: x.seconds) / 3600pcp_intervalinterval = pcp_intervalinter

26、val.apply(lambda x: math.ceil(x)复制代码ax.set_yscale(log)plt.title(pv-cart-pay)复制代码pv - fav - paypfp_interval = pv_fav_pay_df.groupby(user_id, sku_id).apply(lambda x: (x.action_time.min() - x.action_time_fav.min().reset_index()复制代码pfp_intervalinterval = pfp_interval0.apply(lambda x: x.seconds) / 3600pf

27、p_intervalinterval = pfp_intervalinterval.apply(lambda x: math.ceil(x)复制代码fig, ax = plt.subplots(figsize=16,6)sns.countplot(pfp_intervalinterval,palette=Set1)for p in ax.patches:ax.annotate(:.2f%.format(100*p.get_height()/len(pfp_intervalinterval), (p.get_x() + 0.1, p.get_height() + 10)ax.set_yscale

28、(log)plt.title(pv-fav-pay)复制代码两种路径下部分户均在4时内完成了付，部分户的购物意向很明确，也侧说明了站的商品分类布局和购物结算式较合理。ORDER BY COUNT(DISTINCT user_id) DESCSELECT COUNT(DISTINCT b.user_id) AS pv_fav_num,COUNT(DISTINCT c.user_id) AS pv_fav_pay_numFROM(SELECT DISTINCT user_id, sku_id, action_time FROM users WHERE type=pv ) AS aLEFT JOIN

29、(SELECT DISTINCT user_id, sku_id, action_time FROM users WHERE type=favAND user_id NOT IN(SELECT DISTINCT user_idFROM behavior_sqlWHERE type = cart) AS bON a.user_id = b.user_id AND a.sku_id = b.sku_id AND a.action_time = b.action_timeLEFT JOIN(SELECT DISTINCT user_id,sku_id,item_category,times_new

30、FROM users WHERE behavior_type=pay) AS cON b.user_id = c.user_id AND b.sku_id = c.sku_id AND AND b.action_time 1000ORDER BY sku_num DESC;复制代码#RFM#MF（消费频率）对户进价值分析buy_group = behaviorbehaviortype=pay.groupby(user_id)date复制代码#2018-04-13作为每个户最后次购买时间来处理final_day = datetime.date(datetime.strptime(2018-04-14, %Y-%m-%d)复制代码#最近次购物时间recent_buy_time = buy_group.apply(lambda x:final_day-x.max()recent_buy_time = recent_buy_time.reset_index().rename(columns=date:recent)recent_buy_timerecent = re