【数据分析项目】淘宝用户行为分析【SQL+Tableau】
1. 数据说明
2. 导入数据
SELECT * FROM userbehavior;
3. 数据处理
- 所有字段都有用,不需要选择子集等操作
3.1 删除重复值
SELECT * FROM userbehavior GROUP BY User_ID,Item_ID,Category_ID,Behavior,Time_info HAVING COUNT(*)>1;
- 没有重复值
3.2 缺失值处理
SELECT COUNT(User_ID),COUNT(Item_ID),COUNT(Category_ID),COUNT(Behavior),COUNT(Time_info) FROM userbehavior;
- 没有缺失值
3.3 分列处理
ALTER TABLE userbehavior ADD COLUMN Date_Time TIMESTAMP(0) NULL; UPDATE userbehavior SET Date_time = FROM_UNIXTIME(`Time_info`);
ALTER TABLE userbehavior ADD COLUMN Date char(10) NULL; UPDATE userbehavior SET Date = FROM_UNIXTIME(`Timestamp`,'%y-%m-%d');
ALTER TABLE userbehavior ADD COLUMN Time char(10) NULL; UPDATE userbehavior SET Time = SUBSTRING(Date_time FROM 12 FOR 8);
3.4 异常值处理
SELECT MAX(Date),MIN(Date) FROM userbehavior;
DELETE FROM userbehavior WHERE Date >'17-12-03' OR Date <'17-11-25';
- 有44行数据被删除
4. 数据分析
- 分析方法
4.1 数据总览
(1)数据整体情况
SELECT COUNT(DISTINCT User_ID) AS 用户数, COUNT(DISTINCT Item_ID) AS 商品总数, COUNT(DISTINCT Category_ID) AS 商品种类数, COUNT(Behavior) AS 行为总数 FROM userbehavior;
(2)用户行为整体情况
CREATE VIEW 用户行为数据 AS SELECT User_ID,COUNT(Behavior) AS 用户行为总数, SUM(IF(Behavior='pv',1,0)) AS 点击数, SUM(IF(Behavior='fav',1,0)) AS 收藏数, SUM(IF(Behavior='cart',1,0)) AS 加购数, SUM(IF(Behavior='buy',1,0)) AS 购买数 FROM userbehavior GROUP BY User_ID ORDER BY 用户行为总数 DESC;
4.2 用户生命周期分析
(1)用户获取情况
- 通过查询日新增用户数分析用户的获取情况
CREATE VIEW 分组 AS SELECT User_ID,MIN(Date) AS fd FROM userbehavior GROUP BY User_ID; SELECT fd,COUNT(User_ID) FROM 分组 GROUP BY fd;
- 11.25 日用户新增数量最多,推测有新的促销活动吸引了新用户
- 整体用户增加数都处于较低的状态,推测目前的推广渠道效果一般,应该制定更加有效的策略
(2)用户活跃情况
- 通过计算UV、PV等指标和日变化趋势查看用户的活跃情况
① 总体独立访客数UV、点击数PV、人均浏览次数、成交量
SELECT COUNT(DISTINCT User_ID) AS 独立访客数, SUM(IF(Behavior='pv',1,0)) AS 点击数, SUM(IF(Behavior='pv',1,0))/COUNT(DISTINCT User_ID) AS 人均浏览次数, SUM(IF(Behavior='buy',1,0)) AS 成交量 FROM userbehavior;
② 日活跃情况
SELECT Date,COUNT(DISTINCT User_ID) AS 日独立访客数, SUM(IF(Behavior='pv',1,0)) AS 日点击数, SUM(IF(Behavior='pv',1,0))/COUNT(DISTINCT User_ID) AS 日人均浏览次数, SUM(IF(Behavior='buy',1,0)) AS 日成交量 FROM userbehavior GROUP BY Date;
- 日独立访客数和日点击数的增长趋势基本相同,在11.27和11.30号有下降的趋势
- 日成交量在11.27和11.30号呈现增长的趋势,推测用户在此期间直接购买商品的行为增加
- 日人均浏览趋势在12.01号达到峰值,可能为双12或其他活动宣传导致用户浏览量急速增加
(3)用户留存情况
① 用户次日、3日、5日、7日留存人数
- 第一天(17-11-25)活跃人数
CREATE TABLE retention AS SELECT COUNT(DISTINCT User_ID) AS 第一天活跃人数 FROM userbehavior WHERE Date = '17-11-25';
- 次日留存人数
ALTER TABLE retention ADD COLUMN 第二天留存人数 INT; UPDATE retention SET 第二天留存人数 = (SELECT COUNT(DISTINCT User_ID) FROM userbehavior WHERE Date = '17-11-26' AND User_ID IN (SELECT DISTINCT User_ID FROM userbehavior WHERE Date = '17-11-25'));
- 最终留存人数计算结果
② 留存率
SELECT CONCAT(ROUND(100*第二天留存人数/第一天活跃人数,2),'%') AS 次日留存率, CONCAT(ROUND(100*第三天留存人数/第一天活跃人数,2),'%') AS 第3日留存率, CONCAT(ROUND(100*第五天留存人数/第一天活跃人数,2),'%') AS 第5日留存率, CONCAT(ROUND(100*第七天留存人数/第一天活跃人数,2),'%') AS 第7日留存率 FROM retention;
- 用户在7日之后还保持75.97%的留存率,处于比较高的状态
SELECT User_ID FROM 用户行为数据 WHERE 用户行为总数=1;
(4)用户购买情况
① 商品、购买数量及次数
SELECT COUNT(DISTINCT Item_ID) AS 商品数 FROM userbehavior;
SELECT COUNT(DISTINCT Item_ID) AS 购买商品数 FROM userbehavior WHERE Behavior = "buy";
SELECT a.购买次数,COUNT(a.Item_ID) AS 商品数 FROM (SELECT Item_ID,COUNT(User_ID) AS 购买次数 FROM userbehavior WHERE Behavior="buy" GROUP BY Item_ID) AS a GROUP BY 购买次数 ORDER BY 购买次数 DESC;
② 复购率
SELECT SUM(IF(购买数 > 1,1,0)) AS 复购次数, SUM(IF(购买数 > 0,1,0)) AS 购买次数, CONCAT(ROUND((SUM(IF(购买数 > 1,1,0)) * 100/ SUM(IF(购买数 > 0,1,0))),2),'%') AS 复购率 FROM 用户行为数据;
4.3 用户行为路径分析
(1)行为转化漏斗(点击行为到其他行为的转化率分别为多少)
SELECT SUM(IF(Behavior='pv',1,0)) AS 点击, SUM(IF(Behavior='fav',1,0)) AS 收藏, SUM(IF(Behavior='cart',1,0)) AS 加购, SUM(IF(Behavior='buy',1,0)) AS 购买 FROM userbehavior;
(2)用户转化漏斗
SELECT SUM(IF(点击数>0,1,0)) AS 点击用户数, SUM(IF(收藏数>0,1,0)) AS 收藏用户数, SUM(IF(加购数>0,1,0)) AS 加购用户数, SUM(IF(购买数>0,1,0)) AS 购买用户数 FROM 用户行为数据;
(3)四种不同行为路径
- 购买行为可以由大致四种导致:点击+购买、点击+加购+购买、点击+收藏+购买、点击+收藏加购+购买
- 点击用户数为980
① 点击→购买
SELECT SUM(IF(购买数>0,1,0)) AS 购买用户数1 FROM 用户行为数据 WHERE 收藏数=0 AND 加购数=0;
② 点击→加购→购买
SELECT SUM(IF(购买数>0,1,0)) AS 购买用户数2, SUM(IF(加购数>0,1,0)) AS 加购用户数2 FROM 用户行为数据 WHERE 收藏数=0 AND 加购数<>0;
③ 点击→收藏→购买
SELECT SUM(IF(购买数>0,1,0)) AS 购买用户数2 FROM 用户行为数据 WHERE 收藏数=0 AND 加购数<>0;
④ 点击→加购收藏→购买
SELECT SUM(IF(购买数>0,1,0)) AS 购买用户数4, SUM(IF(收藏数>0,1,0)) AS 收藏用户数4, SUM(IF(加购数>0,1,0)) AS 加购用户数4 FROM 用户行为数据 WHERE 收藏数<>0 AND 加购数<>0;
(4)不同行为路径的转化漏斗图
- 商家可以通过增加此部分用户所占比例提高销量
4.4 用户偏好分析
(1)用户行为时间偏好分析
① 用户行为习惯(按天)
CREATE VIEW 用户行为习惯分布(日) AS SELECT Date,COUNT(Behavior) AS "用户行为数", SUM(IF(Behavior='pv',1,0)) AS "点击", SUM(IF(Behavior='fav',1,0)) AS "收藏", SUM(IF(Behavior='cart',1,0)) AS "加购", SUM(IF(Behavior='buy',1,0)) AS "购买" FROM userbehavior GROUP BY Date ORDER BY Date;
② 用户行为习惯(按时)
CREATE VIEW 用户行为习惯分布(时) AS SELECT SUBSTRING(Time FROM 1 FOR 2),COUNT(Behavior) AS "用户行为数", SUM(IF(Behavior='pv',1,0)) AS "点击", SUM(IF(Behavior='fav',1,0)) AS "收藏", SUM(IF(Behavior='cart',1,0)) AS "加购", SUM(IF(Behavior='buy',1,0)) AS "购买" FROM userbehavior GROUP BY SUBSTRING(Time FROM 1 FOR 2) ORDER BY SUBSTRING(Time FROM 1 FOR 2);
(2)用户行为偏好Top 20产品分析
SELECT Category_ID,SUM(IF(Behavior="pv",1,0)) AS 点击数 FROM userbehavior GROUP BY Category_ID ORDER BY 点击数 DESC LIMIT 20;
① 点击Top 20产品
② 加购Top 20产品
③ 收藏Top 20产品
④ 购买Top 20产品
- 创建每个行为Top20商品的视图,计算不同行为的商品重叠情况
CREATE VIEW 点击 AS SELECT Category_ID,SUM(IF(Behavior="pv",1,0)) AS 点击数 FROM userbehavior GROUP BY Category_ID ORDER BY 点击数 DESC LIMIT 20; SELECT COUNT(点击.Category_ID) FROM 点击 INNER JOIN 加购 ON 点击.Category_ID = 加购.Category_ID
4.5 用户价值分析
(1)RFM模型简介
- 采用RFM模型进行用户价值分析
- 由于本数据集不存在金额字段,所以只从RF两个维度进行分析
- R:最近一次购买时间(采用12.03和最近一次购买的时间间隔作为计算依据)
- F:消费频次(采用用户购买次数作为计算依据)
- 一般来说,R和F的值越大,说明用户的价值越高
(2)R值的计算
- 数据集的时间跨度为2017年的11.25到12.03,共9天
- 定义R值的划分规则:
- 间隔时间 > 7天:1
- 间隔时间 5-7天:2
- 间隔时间 3-4天:3
- 间隔时间 0-2天:4
SELECT User_ID, (CASE WHEN Rtime>7 THEN 1 WHEN Rtime BETWEEN 5 AND 7 THEN 2 WHEN Rtime BETWEEN 3 AND 4 THEN 3 WHEN Rtime BETWEEN 0 AND 2 THEN 4 ELSE NULL END) AS R值 FROM (SELECT User_ID,DATEDIFF('17-12-03',MAX(Date)) AS Rtime FROM userbehavior WHERE Behavior='buy' GROUP BY User_ID) AS 购买时间间隔 ORDER BY R值 DESC;
(3)F值的计算
SELECT User_ID,COUNT(Behavior) AS 购买次数 FROM userbehavior WHERE Behavior='buy' GROUP BY User_ID ORDER BY 购买次数 DESC;
- 可以看出,购买次数最多的为43次
- 定义F值的划分规则
- 购买次数 1-10:1
- 购买次数11-20:2
- 购买次数21-30:3
- 购买次数 >30:4
SELECT User_ID, (CASE WHEN Btime BETWEEN 1 AND 10 THEN 1 WHEN Btime BETWEEN 11 AND 20 THEN 2 WHEN Btime BETWEEN 21 AND 30 THEN 3 WHEN Btime >30 THEN 4 ELSE NULL END) AS F值 FROM (SELECT User_ID,COUNT(Behavior) AS Btime FROM userbehavior WHERE Behavior='buy' GROUP BY User_ID) AS 购买次数 ORDER BY F值 DESC;
(4)用户划分
- 将用户按照以下规则进行划分:
F(3-4) | 重要保持用户 | 重要价值用户 |
F(1-2) | 重要挽留用户 | 重要发展用户 |
R(1-2) | R(3-4) |
- 创建r_value和f_value分别记录之前计算的R值和F值
- 将R值和F值合并到一起
CREATE VIEW 用户价值分析 AS SELECT r.User_ID,R值,F值 FROM r_value AS r JOIN f_value AS f ON r.User_ID = f.User_ID
- 计算R值和F值的平均值
SELECT AVG(R值), AVG(F值) FROM 用户价值分析;
- 查看不同用户对应的份额
CREATE VIEW 用户类型 AS SELECT User_ID, (CASE WHEN R值>3.3413 AND F值>1.0298 THEN "重要价值用户" WHEN R值<3.3413 AND F值>1.0298 THEN "重要保持用户" WHEN R值>3.3413 AND F值<1.0298 THEN "重要发展用户" WHEN R值<3.3413 AND F值<1.0298 THEN "重要挽留用户" END) AS user_type FROM 用户价值分析
SELECT user_type,COUNT(User_ID) AS 人数 FROM 用户类型 GROUP BY user_type
(5)用户价值分析结论
- 重要发展用户占比最高,这些用户购买时间间隔较短,但购买次数比较低,重点应该提升消费频次
- 重要挽留客户占比第二,购买次数少,购买时间久远,是潜在客户,应该做精准化的营销
- 重要价值客户占比第三,需要重点关注,属于忠实买家,应提供定期的VIP服务
- 重要保持用户占比最少,购买次数多,但最近没有买,应该采取措施及时召回
5. 总结
- 日新增用户数不是很多,推广渠道的效果一般,12.02日的增长明显,推测可能为活动影响
- 用户留存率较高,跳失率为0,复购率也较高,用户的忠诚度较高
- 购买一次的用户占比最多,说明淘宝的销售情况并不是依靠爆款产品
- 用户从点击到最后的购买有大量的流失情况,建议通过活动或优惠券的方式吸引用户购买
- 通过加购收藏方式的用户最终购买的可能性最大,可以通过收藏有礼的方式提高收藏行为
- 用户多活跃在20时到22时,建议在此时间段内进行推广,使效果最大化
- 重要价值用户占比较少,大多数为重要发展和重要挽留客户,建议针对不同的人群,进行精准化营销