首发于 数据分析案例
mysql---淘宝用户行为分析

mysql---淘宝用户行为分析

本文从天池获取数据源,结合电商8大指标,选取 AARRR漏斗模型进行用户行为转化率分析、RFM模型提取不同等级价值用户信息等,并使用mysql 工具进行数据清洗,EXCEL及tableau进行可视化;
链接为分析报告

下面是整个报告分析思路及代码展示部分

一、项目背景

随着移动互联网的不断发展,电商行业也从以前的粗犷式发展进入到了精准的精细化时代。就淘宝而言,作为B2C模式下的电商行业的典型代表,将入驻商家直接推到了与消费者交易的前台。商家要想提高销量,必须分析用户行为数据,构建用户画像,有针对性地为用户服务,做到精准营销,减短用户挑选和购买的时间,从而提高成交率。

二、提出问题

在提出问题前,我们先想思考一下,分析目的是什么?提问的问题能否帮助运营或销售解决?

2.1 分析目的

通过分析淘宝用户行为,找出用户行为规律,为促销等营销战略以及精细化推荐提供数据支撑,最终以提高成交率为目的。

2.2 提出问题

1、用户行为--时间角度分析:哪个时段用户活跃度最高?哪一天活跃指数最高?

  • 相关指标:每小时活跃数及每天活跃数,通过对比分析,筛选最佳时段
  • 目的:找出活跃最佳时段,实施销售策略(俗话说,刀用在刀刃上)

2、用户行为AARRR--流程转化率角度分析:哪个环节流失率最高?流失原因是什么?

  • 相关指标:转化率,从浏览-收藏-加购-购买,每一个环节之间转化情况,找出转化率较低的节点,进行假设检验,分析原因。
  • 目的:找出用户行为中转化率低的节点,分析原因,便于后续进行改进,及对策实施

3、用户粘性角度分析:用户留存率、复购率表现?一般喜欢在哪个时间段进行回购呢?

  • 相关指标:留存率 采用群组分析法,对比不同时间段留存情况(昨天客户今天还在不在,留住老客户成本总是低于挖掘新客户,侧面也确认店铺吸引力)、复购率:同一个用户2次及以上购买占总人数比例,侧面能挖掘出忠诚用户信息)
  • 目的:通过留存率、复购率挖掘出忠诚用户,同时帮助企业判断自身吸引力。便于及时调整策略

4、用户价值RFM分析:不同价值用户占比如何?便于精准营销

  • 相关指标:R 最近消费时间间距,F:消费频次,M:消费金额,
  • 目的:通过RFM打分,筛选不同等级客户,便于精准营销,

5、产品角度分析,确认用户喜好top10商品,便于个性化定制

  • 相关指标:点击量top10 商品,购买量top10商品,
  • 目的:对比分析,确认平台推荐是否为客户喜欢商品,同时找出平台的明星产品,进行鼓励

三、提取并理解数据

数据来自天池,地址如下:数据是淘宝App由2017年11月25日至2017年12月3日之间,有行为的约一百万随机用户的所有行为(行为包括点击、购买、加购、收藏)。

  • 获取数据(原始数据有134MB,本文其中提取了10万条数据)(在提取时将列名更改掉)
  • 数据类型
  • 数据解释

四、数据处理

按如下顺序进行操作

  • 选择子集

本文不需要子集选择,分析过程中将左右列进行操作;

  • 重复值删除

此处不需要删除重复值,因同一个用户有多次购买行为

  • 缺失值处理
-- 确认是否有空值,以及数据量大小
SELECT COUNT(userid),COUNT(itemid),COUNT(behavior),COUNT(categoryid),count(timestamps) from UserBehavior;

无缺失值,如下(备注,可以在数据表导入时设置 NOT NULL,导入无空值)

  • 数据类型转换

前期导入将时间用字符串varchar进行导入,此处我们把它转换成我们需要的时间by day, by hour ,by week

-- 将表格中timestamps 拆分成  日期和小时、周,便于后面我们进行分析
alter table UserBehavior add dates varchar(255); -- 先给表格增加日期列
update UserBehavior SET dates=FROM_UNIXTIME(timestamps,'%Y-%m-%d') ; -- 然后给列dates增加数据更新表格
alter table UserBehavior add hours varchar(255); -- 先给表格增加hour列
UPDATE UserBehavior SET hours=FROM_UNIXTIME(timestamps,'%H');  -- 然后给新的列hour增加数据
alter table UserBehavior add WEEKDAYS VARCHAR(255); -- 先给表格增加weekdays列
UPDATE UserBehavior set weekdays=FROM_UNIXTIME(timestamps,"%W"); -- 然后给新的列weekdays增加数据
SELECT * from UserBehavior limit 10; -- 选取前10行看下数据对不对
拆分时间后用户行为表
  • 异常值处理
-- 筛选异常值
select max(dates),MIN(dates) FROM UserBehavior;
-- 删除小于2017-11-25s时间
delete from UserBehavior WHERE dates <'2017-11-25' ;
-- 确认删除后数据即大小
SELECT COUNT(userid),COUNT(itemid),COUNT(behavior),COUNT(categoryid),count(timestamps) from UserBehavior;
select max(dates),MIN(dates) FROM UserBehavior;--再次确认数据

五、数据业务分析

根据问题拆解,下面我们按照如下模板进行分析,首先在分析问题前,了解下平台整体状态,及用户流量分析,判断整体情况

  1. 用户流量指标
  • UV/PV/平均点击量
-- 分析用户平均点击量
select count(distinct userid) as UV,
(select count(*) from UserBehavior where behavior='pv') AS PV,
(select count(*) from UserBehavior where behavior='pv')/count(distinct userid) as 'PV/UV'
from UserBehavior;

访问用户总数(UV):981

页面总访问量(PV):88445

平均每人访问量为90.15次页面

  • 跳失率
跳失率(用户只有一次点击的行为占总用户的比例)
-- 筛选用户只有PV即只有浏览,无其他动作,
select count(distinct userid) FROM UserBehavior
where userid not in 
(select userid from UserBehavior where behavior='cart'or behavior='fav'or behavior='buy');

跳失率=56/981=5.7%

小结:用户总体的活跃度较高,平均每个用户访问90次页面。但仍旧有5.7%用户点击一次就没有下文。接下来进行具体分析

2. 用户行为分析

  • 时间角度(分析用户是否有时间集中性)
select hours,
sum(case behavior when "pv" then 1 else 0 end ) as "浏览数",
sum(case behavior when "cart" then 1 else 0 end ) as "加购数",
sum(case behavior when "fav" then 1 else 0 end) as "收藏数",
sum(case behavior when "buy" then 1 else 0 end )as "付费数"
from UserBehavior group by hours
order by hours;

用户每天在12--18点较活跃其中在14:00-15:00为活跃顶峰;

select dates,
sum(case behavior when "pv" then 1 else 0 end ) as "浏览数",
sum(case behavior when "cart" then 1 else 0 end ) as "加购数",
sum(case behavior when "fav" then 1 else 0 end) as "收藏数",
sum(case behavior when "buy" then 1 else 0 end )as "付费数"
from UserBehavior group by dates
order by dates;

小结:12.1-12.2日用户最活跃,浏览数和加购物车行为最为突出,

提出疑问:是否再次期间有什么活动呢?

调查发现:12.1号为淘宝为双12开始的预售活动,且12.1为周五,12.2周六,周末用户可能进行商品挑选,所以浏览数和加购物车行为有明显上升

  • 行为转化率
select behavior,count(userid) from UserBehavior GROUP BY behavior ORDER BY behavior DESC

用户行为夹点:从浏览到加购流失率最高,流失率为什么这么高呢?我们可以继续进行流程拆分

小结:

  • 用户行为更倾向于浏览网页后进行下一步加购或收藏;说明浏览对用户下一步行为关键因素
  • 用户购买行为转化率:收藏+加购物车>加购物车>收藏(不分是否浏览网页)
  • 浏览后若有100用户浏览界面,会有1个用户直接购买;有100人加购物车则会有10人进行购买;有100人收藏该物品后会有8个人购买,有100个人收藏且加入购物车会有15人购买;可用来进行商品预测;
  • 从上数可见,用户浏览后流失率较高,进行假设检验(本文从商品角度进行假设)
假设检验

具体代码如下

-- 查找购买top10 产品
SELECT itemid,count(itemid) AS  "购买量" from UserBehavior WHERE behavior="buy" GROUP BY  ITEMID ORDER BY count(itemid) desc LIMIT 20;
-- 查找点击top10 产品
SELECT itemid,count(itemid) AS  "浏览量" from UserBehavior WHERE behavior="fav" GROUP BY  ITEMID ORDER BY count(itemid) DESC LIMIT 20;
-- 确认商品种类总数
select count(distinct categoryid) from UserBehavior;
-- 确认购买的商品种类数量
select count(DISTINCT categoryid) from UserBehavior where behavior="buy";
对比推荐top10 和购买top10 商品ID

推荐商品不合适及商品种类较少,造成用户在浏览后发生流失,还需要大数据进行分析,对大多数用户进行个性化推荐,提升用户购买转换率;

销售量top1 商品ID为1022848,销售部门可以对此进行活动退广,如买二送1等活动,提升转换,或者进行老用户要新用户购买优惠活动获取新用户

  • 用户粘性
  • 用户复购率=购买商品2次及以上用户个数比上总用户购买人数
-- 复购用户及商品(不同时间段,购物大于两次及以上用户)
select userid,itemid,count(distinct dates) as '购买次数' from UserBehavior WHERE behavior="buy" group by userid,itemid having count(distinct dates)>1 order by count(distinct dates) desc;
-- 购买商品用户总数
select count(distinct UserID) as '购买次数' from UserBehavior WHERE behavior="buy" 

复购人数为:456,购买商品总人数为683,,复购率=66.8%

说明商品销售不错,可对复购人群进行画像,筛选相似用户进行广告触达,提升商品销量

  • 复购间隔天数分布
 -- 查找复购人群购买的时间
CREATE VIEW  SHIJIANBIAO as 
select UserID,ITEMID,dates from UserBehavior WHERE behavior="buy" group by userid,itemid,DATES ;
 -- 计算复购时间
SELECT USERID,ITEMID,DATEDIFF(max(dates),min(dates)) FROM SHIJIANBIAO group by USERID,ITEMID

80%用户会考虑在3天内再次购买同一件商品。品牌可在购买后3天内提供复购场景,比如优惠卷过期提醒,到货提醒、大促满减活动

  • 留存率

留存率的计算方式为:今天新访问(存在任意行为记录)的用户A个,N日后还有B个访问,那么这批用户的N日访问留存率就是B / A;

select a.dateS,count(DISTINCT a.userid)as 日新增用户数,
CONCAT(round(100*count(DISTINCT if(datediff(b.dates,a.dates)=1,b.userid,null))/count(DISTINCT a.userid),2),'%')as 次日留存率,
CONCAT(round(100*count(DISTINCT if(datediff(b.dates,a.dates)=2,b.userid,null))/count(DISTINCT a.userid),2),'%')as 二日留存率,
CONCAT(round(100*count(DISTINCT if(datediff(b.dates,a.dates)=3,b.userid,null))/count(DISTINCT a.userid),2),'%')as 三日留存率,
CONCAT(round(100*count(DISTINCT if(datediff(b.dates,a.dates)=4,b.userid,null))/count(DISTINCT a.userid),2),'%')as 四日留存率,
CONCAT(round(100*count(DISTINCT if(datediff(b.dates,a.dates)=5,b.userid,null))/count(DISTINCT a.userid),2),'%')as 五日留存率,
CONCAT(round(100*count(DISTINCT if(datediff(b.dates,a.dates)=6,b.userid,null))/count(DISTINCT a.userid),2),'%')as 六日留存率,
CONCAT(round(100*count(DISTINCT if(datediff(b.dates,a.dates)=7,b.userid,null))/count(DISTINCT a.userid),2),'%')as 七日留存率,
CONCAT(round(100*count(DISTINCT if(datediff(b.dates,a.dates)=8,b.userid,null))/count(DISTINCT a.userid),2),'%')as 八日留存率
from UserBehavior as a LEFT JOIN UserBehavior as b
on a.userid = b.userid
GROUP BY a.dates

用户次留存在78%,图中发现在12月2日用户会活跃起来,与日活跃量成正相关;

总结用户行为分析结果

3.用户价值分析

通过分析用户价值,提取不同等级用户信息,进行多样营销策略,提升销售量

  • R:提取用户最近一次消费时间,时间越短说明用户价值越大,
  • F:频次越高,数字越大,说明用户创造价值越大,
  • M:消费金额,数字越大,说明用户用户贡献金额越大;(此处无金额数据,不加入评定)

①先计算最近用户消费时间

create view R as 
select userid,max(dates),DATEDIFF("2017-12-03",MAX(dates)) as '最近消费天数' from UserBehavior where behavior="buy " group by userid ;

用户消费时间集中在0-8次,均值:2.65天。

②计算用户消费频次,

-- 计算用户消费频次
create view F as 
select userid,sum(case behavior when "buy" then 1 else 0 end)as '消费频次' from UserBehavior where behavior="buy " group by userid ;

用户消费频次主要集中在5以内,平均消费2.87次;

③进行消费者评价

此处不计算消费金额,按照消费时间及频次划分为4个等级,如下

用户价值评分表
select *,
(case when R between 8 and 9 then 1
 when R between 5 and 7 then 2
 when R between 3 and 4 then 3
 when R <=2 then 4
 else 0 end) as 'R值评分',
(case when F between 1 and 5 then 1
 when F between 6 and 10 then 2
 when F between 11 and 15 then 3
 when F >=16 then 4
 else 0 end) as 'F值评分'
from
(select user_id,datediff('2017-12-4',max(date))as 'R',count(Behavior_type) as 'F'
from userbehavior_10
where Behavior_type = 'buy'
group by user_id) as a;

上面从RFM角度判定了用户评分情况,接下来我们针对用户进行打分,此处我们认为大于平均值均为高质量用户,将用户分为如下4种价值类型,并提取提取不同等级客户ID,请销售及运营部门可实施精准营销策略

用户价值等级
SELECT avg(R值评分),avg(F值评分) FROM 评分;
R平均3.075  F平均1.1148

-- 用户价值打分
create view RF as 
select R.userid,R.最近消费天数,F.消费频次,
(case 
when R.最近消费天数<=3.075 and F.消费频次<=1.148 then '重要挽留客户'
when R.最近消费天数>3.075 and F.消费频次<=1.148 then '重要发展客户'
when R.最近消费天数<=3.075 and F.消费频次>1.148 then '重要保存客户'
when R.最近消费天数>3.075 and F.消费频次>1.148 then '重要价值客户'end) AS "用户价值等级"
from R inner join F ON R.userid=F.userid group by R.userid ;
-- 分类用户等级
select RF.用户价值等级,count(*) from RF group by RF.用户价值等级


  • 用户价值最高占比:重要挽留用户52%重要发展用户 39%上;
  • 重要发展用户39%,其消费频率低,但最近消费距离现在时间较短,可通过短信推送和促销等方案,刺激用户在2-4天内进行复购提升销量;
  • 重要挽留用户52%,最近消费时间距离现在较远、消费频率低。这种用户有即将流失的危险,可进行用户行为埋点,确认用户收藏和加购等物品,可进行推送提醒提升购买转化率。
  • 重要价值用户 6%,消费频率高且最近消费距离现在时间短,可提供VIP服务,或发放优惠券奖励。
  • 重要保持用户3%,最近消费距离现在时间较远,也就是R值低,但是消费频次高。这样的用户,是一段时间没来的忠实客户,可以采取邮件推送、APP推送提醒、促销活动时短信提醒等方式主动和他们保持联系,提高复购率。
    4、产品角度分析

前面用户浏览后流失率高,已经假设检验进行分析,此处放置结论

六、总结

从用户流量来看,用户人均浏览量高,跳失率低、且留存率较高,说明整体运营状态较好;下面从几个维度进行总结及业务建议;

  • 用户行为--时间角度
  • 在用户活跃较高时间,可进行一些明星直播带货等销路,拉拢用户,后续持续关注用户留存率
  • 用户行为--转化率
  • 数据显示用户购买转化率: 收藏+加购物车>加购物车>收藏 ;可根据用户行为加购及收藏,进行奖励。如收藏立减5元,等活动策略;
  • 用户在浏览界面后,流失率93%,a: 可优化商品页面介绍,突出商品特点,吸引用户;b:店铺提升对商品好评率,从而提升转化率
  • 考虑在客户发生首购行为后,80%% 用户会在3天内复购,可通过客服提醒/短信发放优惠卷等活动提供复购场景,促使用户在短时间内完成复购。
  • 用户价值分析
  • 通过RFM模型分析得到的不同类型的用户,应该采取不同的激励方案。
  • 对于占比最高52%重要挽留用户这种用户有即将流失的危险,可进行用户行为埋点,确认用户收藏和加购等物品,可进行推送提醒提升购买转化率
  • 对于 6% 重要价值用户 ,可提供VIP服务,或发放优惠券奖励。
  • 商品分析
  • 用户浏览后流失率较高,建议后台进行埋点测试,筛选用户喜欢商品,进行个性化定制推广
  • 对于高购买率商品,建议提高曝光率,结合多平台宣传,提高浏览量
  • 对于明星商品,建议平台给予表扬与内部公开,且持续保持商品好评率,提高购买转化率

代做工资流水公司吉林流水单查询大庆打房贷收入证明绍兴对公账户流水公司东莞贷款银行流水报价温州银行流水账单费用石家庄打自存流水西宁签证银行流水 模板深圳购房银行流水查询温州对公账户流水鞍山代开入职流水惠州查入职工资流水贵阳开公司流水宜昌车贷工资流水 费用铜陵在职证明报价嘉兴查询入职工资流水镇江代做流水账单咸阳代开日常消费流水武汉签证银行流水 价格柳州代做收入证明西宁日常消费流水办理成都查询签证银行流水盐城银行流水修改开具淮安薪资银行流水代开潍坊流水单代开东莞背调银行流水开具菏泽做对公流水襄阳签证银行流水 报价银川代开银行流水PS江门背调工资流水制作南通打印入职工资流水香港通过《维护国家安全条例》两大学生合买彩票中奖一人不认账让美丽中国“从细节出发”19岁小伙救下5人后溺亡 多方发声卫健委通报少年有偿捐血浆16次猝死汪小菲曝离婚始末何赛飞追着代拍打雅江山火三名扑火人员牺牲系谣言男子被猫抓伤后确诊“猫抓病”周杰伦一审败诉网易中国拥有亿元资产的家庭达13.3万户315晚会后胖东来又人满为患了高校汽车撞人致3死16伤 司机系学生张家界的山上“长”满了韩国人?张立群任西安交通大学校长手机成瘾是影响睡眠质量重要因素网友洛杉矶偶遇贾玲“重生之我在北大当嫡校长”单亲妈妈陷入热恋 14岁儿子报警倪萍分享减重40斤方法杨倩无缘巴黎奥运考生莫言也上北大硕士复试名单了许家印被限制高消费奥巴马现身唐宁街 黑色着装引猜测专访95后高颜值猪保姆男孩8年未见母亲被告知被遗忘七年后宇文玥被薅头发捞上岸郑州一火锅店爆改成麻辣烫店西双版纳热带植物园回应蜉蝣大爆发沉迷短剧的人就像掉进了杀猪盘当地回应沈阳致3死车祸车主疑毒驾开除党籍5年后 原水城县长再被查凯特王妃现身!外出购物视频曝光初中生遭15人围殴自卫刺伤3人判无罪事业单位女子向同事水杯投不明物质男子被流浪猫绊倒 投喂者赔24万外国人感慨凌晨的中国很安全路边卖淀粉肠阿姨主动出示声明书胖东来员工每周单休无小长假王树国卸任西安交大校长 师生送别小米汽车超级工厂正式揭幕黑马情侣提车了妈妈回应孩子在校撞护栏坠楼校方回应护栏损坏小学生课间坠楼房客欠租失踪 房东直发愁专家建议不必谈骨泥色变老人退休金被冒领16年 金额超20万西藏招商引资投资者子女可当地高考特朗普无法缴纳4.54亿美元罚金浙江一高校内汽车冲撞行人 多人受伤

代做工资流水公司 XML地图 TXT地图 虚拟主机 SEO 网站制作 网站优化