热文数据

项目实操:利用SQL和PowerBI对淘宝用户行为进行数据分析

淘宝数据分析.jpg

一.分析背景与目的

用户购买行为分析是研究电子商务中非常重要的部分,它有助于企业根据用户行为特征来制定科学的营销策略,为用户提供更满意的商品或劳务。
本次报告将针对约100万的淘宝用户行为数据进行分析,结合业务知识,提出合理性建议帮助企业降低营销成本,扩大销售利润。

二.分析思路

淘宝用户行为分析.png

三.分析内容

第一步:提出问题

本次通过对淘宝用户行为数据分析,期望解决以下业务问题:
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.字段含义及数据量

淘宝数据集.jpg

第三步:数据清洗

  • 选择子集

数据集中的每个字段均有效,选择全部。

  • 列名重命名

原数据集没有表头,创建表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,执行结果如下:

数据清洗.jpg

  • 异常值处理

检查日期是否均在规定范围内:2017年11月25日至2017年12月3日。

异常值处理.jpg

-- 删除规定日期范围外的数据
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;

日活跃用户量.jpg

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;

流量指标计算结果.jpg

②跳失率计算

跳失率 = 只浏览一个页面就离开的访问次数 / 该页面的全部访问次数

-- 计算只浏览一个页面就离开的用户人数
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;

用户转化漏斗.jpg

用户点击行为占总行为数的89.61%,而收藏和加购行为加起来的行为数只占总行为数的8.36%,推测用户可能在挑选产品环节浪费了较多的时间。

④独立访客转化漏斗

-- 计算各行为人数
SELECT behavior, 
       COUNT(DISTINCT user_id)
FROM UserBehavior
GROUP BY behavior;

独立访客转化漏斗.jpg

独立访客从浏览到最终购买的转化率为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;

用户留存率.jpg

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;

用户点击量.jpg

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';

时段点击量.jpg

结合人们日常作息规律,0点至6点是休息时间,点击量处于低谷阶段;6点至10点,人们慢慢开始工作,点击量开始回暖;10点至18点为正常工作时间,点击量保持平稳;18点至20点,人们相继下班休息,点击量不断升高;在21点至22点期间,点击量到达高峰。

3)找出最具价值的核心付费用户群。(RFM模型)

由于数据缺少M(消费金额)列,暂且通过R(最近一次购买时间)和F(消费频率)的数据对客户价值进行打分。

用户评分标准.png

以2017/12/4日为当前时间,使用DAYS()函数求得R的值。

RFM模型.jpg

对于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;

购买数量排行.jpg

参照查询结果,并没有出现购买数量非常集中的商品,说明店铺盈利主要依靠长尾商品的累积效应。

四.结论与建议

本次报告从四个维度提出业务问题,使用AARRR模型和RFM模型给出以下结论与建议:

1.通过AARRR模型分析用户行为转化的各个环节

获取用户:

根据12月2日和12月3日活跃用户明显增长,推测在此期间店铺举办了营销活动。
获取用户意味着需要拓展页面流量,相对较大型的电商营销活动至少可以从以下三个方面获取流量:

  1. 充分利用站内资源
  2. 跨行合作
  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模型对用户价值分层

  1. 对于评分为33的用户,应该提高满意度,增加留存。
  2. 对于评分为31的用户,可通过活动提高购买评率。
  3. 对于评分为13的用户,需要做触达,以防流失。

4.找出用户产品偏好,制定商品营销策略

用户偏好商品类别里并没有出现购买数量非常集中的商品,说明店铺主要依靠长尾商品的累积效应。对此,需要企业关注产品实时库存,尽量避免供不应求或滞销的情况。另外,企业应该努力开发头部产品,利用头部商品带动销量的再增长。

作者丨冰眸子
来源丨知乎

相关推荐

数据分析实操:用SQL分析淘宝用户行为数据

原标题:实操案例:用SQL分析用户行为数据 [scode]sql也能做分析? 常见的数据清洗,预处理,数据分类,数据筛选,分类汇总,以及数据透视等操作,用SQL一样可以实现(除了可视化,需要放....

本文来自投稿,不代表本站立场,如若转载,请注明出处:http://www.zuopm.com/data/188.html
-- 展开阅读全文 --
用户行为分析模型简述
« 上一篇 02-24
数据产品经理必修课:从零经验到令人惊艳epub电子书
下一篇 » 02-24

发表评论

作者信息

热门文章

标签TAG

热评文章