一.分析背景与目的
用户购买行为分析是研究电子商务中非常重要的部分,它有助于企业根据用户行为特征来制定科学的营销策略,为用户提供更满意的商品或劳务。
本次报告将针对约100万的淘宝用户行为数据进行分析,结合业务知识,提出合理性建议帮助企业降低营销成本,扩大销售利润。
二.分析思路
三.分析内容
第一步:提出问题
本次通过对淘宝用户行为数据分析,期望解决以下业务问题:
1)用户从浏览到最终购买整个过程的流失情况,确定夹点位置。
2)找出用户最活跃的日期以及活跃时间段,了解用户的行为时间模式。
3)找出最具价值的核心付费用户群。
4)找出最受用户青睐的产品。
第二步:理解数据
1.数据来源
数据下载地址:
User Behavior Data from Taobao for Recommendation-数据集-阿里云天池
数据集(UserBehavior.csv)包含了2017年11月25日至2017年12月3日之间,有行为的约一百万随机用户的所有行为(行为包括点击、购买、加购、喜欢)。数据集大小情况为:用户数量约100万(987,994),商品数量约410万(4,162,024),商品类目数量9,439以及总的淘宝用户行为记录数量为1亿条(100,150,807)。
2.本次分析选取的数据样本
原数据集一共有1亿条数据记录,数据量庞大,本次分析选取100万条记录进行分析。
3.字段含义及数据量
第三步:数据清洗
- 选择子集
数据集中的每个字段均有效,选择全部。
- 列名重命名
原数据集没有表头,创建表UserBehavior,在该表中设置数据对应列名。
- 删除重复值
设置联合主键:user_id,item_id,timestamps,数据导入时没有重复值。
- 缺失值处理
字段均定义为NOT NULL,数据导入时没有缺失值。
- 一致化处理
数据集中timestamps使用的是epoch&unix timestamps格式,需转换成北京时间。
-- 使用FROM_UNIXTIME函数转换timestamps格式
ALTER TABLE UserBehavior ADD COLUMN date_time TIMESTAMP(0) NULL;
UPDATE UserBehavior
SET date_time = FROM_UNIXTIME(timestamps);
-- 使用SUBSTRING函数截取年月日信息
ALTER TABLE UserBehavior ADD COLUMN date CHAR(10) NULL;
UPDATE UserBehavior
SET date = SUBSTRING(date_time FROM 1 FOR 10);
-- 使用SUBSTRING函数截取时分秒信息
ALTER TABLE UserBehavior ADD COLUMN time CHAR(10) NULL;
UPDATE UserBehavior
SET time = SUBSTRING(date_time FROM 12 FOR 8);
在原数据表中增加3个新字段date_time,date, time,执行结果如下:
- 异常值处理
检查日期是否均在规定范围内:2017年11月25日至2017年12月3日。
-- 删除规定日期范围外的数据
DELETE FROM UserBehavior
WHERE date_time < '2017-11-25 00:00:00'
OR date_time > '2017-12-04 00:00:00';
第四步:构建模型
1)用户从浏览到最终购买整个过程的流失情况,确定夹点位置。(AARRR模型)
- 获取用户:日活跃用户
-- 计算日活跃用户量
SELECT date,COUNT(DISTINCT user_id)
FROM UserBehavior
WHERE behavior='buy'
GROUP BY date
ORDER BY date ASC;
11月25日、11月26日和12月2日、12月3日同为周末,相比后者却有更多的活跃用户,推测可能是平台做促销活动。
- 激活用户:转化及流失情况
①流量指标计算
-- 计算UV,PV,PV/UV
SELECT COUNT(DISTINCT user_id) AS 'UV',
(SELECT COUNT(behavior)
FROM UserBehavior
WHERE behavior = 'pv') AS 'PV',
(SELECT COUNT(behavior)
FROM UserBehavior
WHERE behavior = 'pv')/(COUNT(DISTINCT user_id) ) AS 'PV/UV'
FROM UserBehavior;
②跳失率计算
-- 计算只浏览一个页面就离开的用户人数
SELECT COUNT(DISTINCT user_id) AS '跳失用户数'
FROM UserBehavior
WHERE user_id NOT IN(SELECT DISTINCT user_id FROM UserBehavior WHERE behavior = 'fav')
AND user_id NOT IN(SELECT DISTINCT user_id FROM UserBehavior WHERE behavior = 'cart')
AND user_id NOT IN(SELECT DISTINCT user_id FROM UserBehavior WHERE behavior = 'buy');
结果显示只有点击行为没有收藏、加购物车以及购买行为的总用户数是567,除以总用户数9739得到跳失率为5.8%。
③用户行为转化漏斗
-- 计算各行为数量
SELECT behavior,
COUNT(behavior)
FROM UserBehavior
GROUP BY behavior;
用户点击行为占总行为数的89.61%,而收藏和加购行为加起来的行为数只占总行为数的8.36%,推测用户可能在挑选产品环节浪费了较多的时间。
④独立访客转化漏斗
-- 计算各行为人数
SELECT behavior,
COUNT(DISTINCT user_id)
FROM UserBehavior
GROUP BY behavior;
独立访客从浏览到最终购买的转化率为68.92%,产品对用户有着不错的购买吸引力。
- 留存用户:用户次日及多日留存率
-- 计算用户次日及多日留存人数、留存率(用户任何行为均视为留存)
SELECT day1,COUNT(DISTINCT a.user_id) AS 活跃人数,
COUNT(DISTINCT CASE WHEN DATEDIFF(day1,day2)=1 then a.user_id end) AS 次日留存人数,
COUNT(DISTINCT CASE WHEN DATEDIFF(day1,day2)=3 then a.user_id end) AS 三日留存人数,
COUNT(DISTINCT CASE WHEN DATEDIFF(day1,day2)=7 then a.user_id end) AS 七日留存人数,
CONCAT(COUNT(DISTINCT CASE WHEN DATEDIFF(day1,day2)=1 THEN a.user_id END)/COUNT(DISTINCT a.user_id)*100,'%') AS 次日留存率,
CONCAT(COUNT(DISTINCT CASE WHEN DATEDIFF(day1,day2)=3 THEN a.user_id END)/COUNT(DISTINCT a.user_id)*100,'%') AS 三日留存率,
CONCAT(COUNT(DISTINCT CASE WHEN DATEDIFF(day1,day2)=7 THEN a.user_id END)/COUNT(DISTINCT a.user_id)*100,'%') AS 七日留存率
FROM (SELECT user_id,DATE_FORMAT(date,'%Y%m%d') AS day1
FROM UserBehavior
WHERE behavior='pv' OR behavior='cart' OR behavior='fav' OR behavior='buy') AS a
LEFT JOIN
(SELECT user_id,DATE_FORMAT(date,'%Y%m%d') AS day2
FROM UserBehavior
WHERE behavior='pv' OR behavior='cart' OR behavior='fav' OR behavior='buy') AS b
ON a.user_id=b.user_id
GROUP BY day1;
11月25日至12月1日次日及多日留存率均在70%~80%,而12月2日次日回访率却高达98%,照应12月2日和12月3日做活动的推测。
- 增加收入:用户复购率
-- 计算只有一次购买行为的用户数量
SELECT user_id,COUNT(user_id)
FROM UserBehavior
where behavior='buy'
GROUP BY user_id
HAVING COUNT(user_id)=1
ORDER BY COUNT(user_id) desc;
-- 计算有购买行为的用户数量
SELECT COUNT(DISTINCT user_id)
FROM UserBehavior
where behavior='buy';
结果显示只有一次购买行为的用户数量为2260,有购买行为的用户数量为6689;
复购率计算过程:(6689-2260)/6689*100%=66.2%
在有购买行为的用户中,66.2%的用户选择重复购买。
2)找出用户最活跃的日期以及活跃时间段,了解用户的行为时间模式。
- 分析2017年11月25日至12月3日9天里用户每天的点击量
SELECT date, SUM(CASE WHEN behavior='pv' THEN 1 ELSE 0 END) AS '点击量'
FROM UserBehavior
GROUP BY date
ORDER BY date;
11月25日至12.1日点击量保持平稳,直到12月2日和12月3日点击量明显增长。
- 分析2017年11月25日至12月3日9天里用户每时段的点击量
SELECT
SUM(CASE WHEN time BETWEEN '00:00:00' AND '00:59:59' THEN 1 ELSE 0 END)/9 AS '00',
SUM(CASE WHEN time BETWEEN '01:00:00' AND '01:59:59' THEN 1 ELSE 0 END)/9 AS '01',
SUM(CASE WHEN time BETWEEN '02:00:00' AND '02:59:59' THEN 1 ELSE 0 END)/9 AS '02',
...
SUM(CASE WHEN time BETWEEN '23:00:00' AND '23:59:59' THEN 1 ELSE 0 END)/9 AS '23'
FROM UserBehavior
WHERE behavior = 'pv';
结合人们日常作息规律,0点至6点是休息时间,点击量处于低谷阶段;6点至10点,人们慢慢开始工作,点击量开始回暖;10点至18点为正常工作时间,点击量保持平稳;18点至20点,人们相继下班休息,点击量不断升高;在21点至22点期间,点击量到达高峰。
3)找出最具价值的核心付费用户群。(RFM模型)
由于数据缺少M(消费金额)列,暂且通过R(最近一次购买时间)和F(消费频率)的数据对客户价值进行打分。
以2017/12/4日为当前时间,使用DAYS()函数求得R的值。
对于user_value=33的用户,可划分为高价值用户,需要重视。
对于user_value=31的用户,可划分为高潜力用户,需要推广。
对于user_value=13的用户,可划分为重要唤回客户,需要唤回。
4)找出最受用户青睐的产品类目。
-- 查询购买数量排名在前十的商品类目ID
SELECT category_id , COUNT(*) AS cat_count
FROM userbehavior
WHERE behavior = 'buy'
GROUP BY category_id
ORDER BY cat_count DESC
LIMIT 10;
参照查询结果,并没有出现购买数量非常集中的商品,说明店铺盈利主要依靠长尾商品的累积效应。
四.结论与建议
本次报告从四个维度提出业务问题,使用AARRR模型和RFM模型给出以下结论与建议:
1.通过AARRR模型分析用户行为转化的各个环节
获取用户:
根据12月2日和12月3日活跃用户明显增长,推测在此期间店铺举办了营销活动。
获取用户意味着需要拓展页面流量,相对较大型的电商营销活动至少可以从以下三个方面获取流量:
- 充分利用站内资源
- 跨行合作
- 产品功能辅助流量增长(场次预约、SNS后置奖品分享)
激活用户:
计算跳失率为5.8%,独立访客从浏览到购买的转化率为68.92%,说明产品详情页对用户有着不错的吸引力;但从用户行为转化漏斗来看,用户点击行为占总行为数的89.61%,而收藏和加购行为加起来的行为数只占总行为数的8.36%,推测用户可能在挑选产品环节浪费了较多的时间。
对此,我们可以将精准推荐系统这个功能模块加入营销活动中,可以个性化地推荐用户感兴趣的商品,增强商品转化。据估计,该模块的成交可以占到整个卖场成交的10-20%之间,不容忽视。
留存用户:
用户留存其指标之于电商就是回访率。11月25日至12月1日次日及多日回访率均在70%~80%之间,而12月2日次日回访率却高达98%,照应12月2日和12月3日做活动的推测。电商大型营销活动持续时间都比较久,普遍在半个月左右,非常容易出现用户买一单之后就再也回不来的情况,所以这个层面需要针对促销活动各个阶段来增大用户回访的机会。
活动基本都会划分为三个阶段:
1. 预热期:预约造势,通过sns、定金裂变等玩法吸引用户关注
2. 正式期:前面如果证实是好的激励体系,可以让活动健康持续发展
3. 高潮期:进一步引爆高潮,使用的激励方式,成长值会员体系、签到体系、积分任务体系等。
增加收入:
在有购买行为的用户中,66.2%的用户选择重复购买。
对于收入这个维度,电商除了有产品的价格利润(如客单价,成本,税率,物流等)的考虑外,在运营段对收入的把控也是必不可少的,对推广费用的把控,各种优惠券的和优惠策略的精准投放,把有限的推广资金用在刀刃上。
2.研究用户时间模式,找到用户在不同时间周期下的活跃规律
分析2017年11月25日至12月3日9天里用户每天的点击量:
发现11月25日至12.1日点击量保持平稳,直到12月2日和12月3日点击量明显增长。值得注意的是,11月25日、11月26日和12月2日和12月3日同为周末,相比后者却有更高的点击量。假设推测成立(12月2日、12月3日平台做促销活动),则企业应合理利用周末多开办营销活动,提高用户活跃度。
分析2017年11月25日至12月3日9天里用户每时段的点击量:
结合人们日常作息规律,0点至6点是休息时间,点击量处于低谷阶段;6点至10点,人们慢慢开始工作,点击量开始回暖;10点至18点为正常工作时间,点击量保持平稳;18点至20点,人们相继下班休息,点击量不断升高;在21点至22点期间,点击量到达高峰。高峰期用户最活跃,企业应针对此时进行营销活动以获取更高的营销收益。
3.通过RFM模型对用户价值分层
- 对于评分为33的用户,应该提高满意度,增加留存。
- 对于评分为31的用户,可通过活动提高购买评率。
- 对于评分为13的用户,需要做触达,以防流失。
4.找出用户产品偏好,制定商品营销策略
用户偏好商品类别里并没有出现购买数量非常集中的商品,说明店铺主要依靠长尾商品的累积效应。对此,需要企业关注产品实时库存,尽量避免供不应求或滞销的情况。另外,企业应该努力开发头部产品,利用头部商品带动销量的再增长。
作者丨冰眸子
来源丨知乎
相关推荐
原标题:实操案例:用SQL分析用户行为数据 [scode]sql也能做分析? 常见的数据清洗,预处理,数据分类,数据筛选,分类汇总,以及数据透视等操作,用SQL....