一、数据集介绍
数据来源:阿里云天池淘宝APP用户行为数据集 https://tianchi.aliyun.com/dataset/dataDetail?dataId=46&userId=1 tianchi_mobile_recommend_train_user.csv
数据含义:
列 | 列说明 | 数据类型 |
user_id | 用户ID | int |
item_id | 商品ID | int |
behavior_type | 行为类型 | int |
user_geohash | 用户位置 | text |
item_category | 商品类目ID | int |
time | 时间 | text |
behavior_type(行为类型)包括:
click(点击) add-to-cart(加入购物车) collect(收藏) payment(购买)
二、数据概览及明确分析目的
SELECT COUNT(user_id) as user_id,COUNT(item_id) as item_id,COUNT(behavior_type) as behavior_type,COUNT(user_geohash) as user_geohash,COUNT(item_category) as item_category,COUNT(time) as time FROM ub;
数据集共含有12256906行数据,user_geohash列有缺失,其余列无缺失。
分析目的:了解淘宝APP用户行为变化情况,行为转化率,建立电商业务指标,对业务结果进行评价,提出优化建议
三、数据处理
1.添加辅助字段
1.1添加行为名称
alter table ub add COLUMN behavior_name text; UPDATE ub set behavior_name = ‘click‘ where behavior_type = 1; UPDATE ub set behavior_name = ‘collect‘ where behavior_type = 2; UPDATE ub set behavior_name = ‘add-to-cart‘ where behavior_type = 3; UPDATE ub set behavior_name = ‘payment‘ where behavior_type = 4;
1.2提取年月日
alter TABLE ub add COLUMN dates char(10); UPDATE ub set dates = substr(time,1,10);
1.3提取小时
alter TABLE ub add COLUMN hours char(10); UPDATE ub set hours = substr(time,12,2);
2.数据清洗
2.1重复值
CREATE TABLE ‘ub1‘( user_id INTEGER NOT NULL, item_id INTEGER NOT NULL, behavior_type INTEGER NOT NULL, user_geohash TEXT NULL, item_category INTEGER NOT NULL, `time` TEXT NOT NULL, behavior_name TEXT NOT NULL, dates TEXT NOT NULL, hours text NOT NULL ); --创建去重后的新表 INSERT into ub1( user_id, item_id, behavior_type, user_geohash, item_category, `time`, behavior_name, dates, hours) SELECT DISTINCT * FROM ub; --插入去重数据到新表
2.2缺失值
SELECT count(user_id),count(item_id),count(behavior_type),count(user_geohash),count(item_category),count(time) from ub; --缺少位置信息记录数 CREATE TABLE ub_lacation as SELECT * from ub where user_geohash is not null; --创建位置信息不为空的记录视图
2.3日期范围
SELECT min(dates),max(dates) from ub; --数据集日期范围从2014-11-18至2014-12-18 SELECT dates,count(*) from ub GROUP by dates order by count(*) desc; --每日数据记录数均大于30万条
四、数据分析
1.活跃度分析
SELECT u1.dates,u1.UV,u2.PV from (SELECT dates,count(DISTINCT user_id) as UV FROM ub group by dates) u1 join (SELECT dates,count(*) as PV from ub WHERE behavior_name = ‘click‘ GROUP BY dates) u2 on u1.dates = u2.dates; --计算每日PV,UV SELECT u1.hours,u1.UV,u2.PV from (SELECT hours,count(DISTINCT user_id) as UV FROM ub group by hours) u1 join (SELECT hours,count(*) as PV from ub WHERE behavior_name = ‘click‘ GROUP BY hours) u2 on u1.hours = u2.hours; --计算每时PV、UV
将查询结果数据导入tableau,制作独立访客数(UV)、商品点击量(UV)、平均点击量(PV/UV)每日变化趋势图及每时变化趋势图:
分析显示:
①UV、PV、PV/UV三者变化趋势基本一致;
②12月11日~12月12日UV、PV、PV/UV增长迅速,且商品点击量涨幅明显大于访客数涨幅。12月13日三个指标回落。
分析得出:
①UV、PV、PV/UV三者变化趋势基本一致;
②12月11日~12月12日UV、PV、PV/UV增长迅速,且商品点击量涨幅明显大于访客数涨幅。12月13日三个指标回落。
2.跳失率分析
2.1用户跳失率
--用户跳失率:只点击的用户数量/所有用户数量 SELECT sum(case t.num when 0 then 1 else 0 end)*1.0/COUNT(user_id) as Bounce_Rate from (SELECT user_id, sum(case behavior_name when ‘add-to-cart‘ then 1 when ‘collect‘ then 1 when ‘payment‘ then 1 ELSE 0 end) num FROM ub GROUP by user_id) t;
2.2商品跳失率
--商品跳失率:只进行点击的用户数量/所有点击该商品的用户数量 SELECT ub.item_id, (t.bounce_num)*1.0/COUNT(DISTINCT ub.user_id) as Bounce_Rate FROM ub join (SELECT item_id,count(user_id) as bounce_num from (SELECT item_id,user_id, sum(case behavior_name when ‘add-to-cart‘ then 1 when ‘collect‘ then 1 when ‘payment‘ then 1 ELSE 0 end) num FROM ub where item_id in (SELECT item_id FROM ub GROUP BY item_id order by COUNT(*) desc LIMIT 300) GROUP by item_id,user_id) t1 WHERE num = 0 group by item_id) t --t表统计出只进行了点击操作的用户数量 on ub.item_id = t.item_id where ub.item_id in (SELECT item_id FROM ub GROUP BY item_id order by COUNT(*) desc LIMIT 300) --只统计记录数前三百的商品 GROUP by ub.item_id;
将查询结果数据导入tableau,制作记录数前三百的商品跳失率图:
记录数前300的商品中,跳失率最小值为45%,最大值为98%,平均值86%。其中跳失率小于60%的商品只有三个,显示跳失率普遍较高,说明商品详情页吸引力有待提高。
3.转化分析
3.1各行为类型点击量每时变化趋势
分析显示:
①晚上19点~23点,商品点击量明显上涨,收藏、加购、购买数量也随之上涨,该时段用户开始频繁访问;
②四种行为的趋势基本一致;
③购买曲线与收藏曲线更为接近,相较于加购,收藏商品转化为购买的比例更大,这在一定程度上说明用户会将更想要购买的商品进行收藏。因此在进行个性化商品推送时,应当更加关注用户收藏的商品。
3.2转化漏斗图
CREATE VIEW action as SELECT user_id,sum(case behavior_name when ‘click‘ then 1 else 0 end) as ‘click‘, sum(case behavior_name when ‘add-to-cart‘ then 1 else 0 end) as ‘add-to-cart‘, sum(case behavior_name when ‘collect‘ then 1 else 0 end) as ‘collect‘, sum(case behavior_name when ‘payment‘ then 1 else 0 end) as ‘payment‘ FROM ub GROUP BY user_id; --创建用户行为视图,将各行为转化为列,方便后续操作 SELECT sum(click) as ‘点击量‘,sum(`add-to-cart`)+sum(collect) as ‘收藏&加购‘,sum(payment) as ‘购买‘ from action; DROP TABLE if EXISTS ‘月整体转化率‘; CREATE table ‘月整体转化率‘ ( behavior_name text not null, num integer not null); INSERT into ‘月整体转化率‘ VALUES(‘点击量‘ ,11550581); INSERT into ‘月整体转化率‘ VALUES(‘收藏&加购‘,586120); INSERT into ‘月整体转化率‘ VALUES(‘购买‘,120205); --创建月整体转化数据表
根据对业务的理解,转化流程为:点击>收藏&加购>购买。将数据导入tableau,制作用户行为转化漏斗图:
分析显示:
2014年11月18日~2014年12月18日期间,用户收藏&加购的比例为5.07%,最终购买的额比例为1.04%。由收藏转化为购买的比例为20.51%。转化漏斗图表明用户转化率情况较差。
4.留存分析
4.1首日用户留存率
将2014年11月18日登录的用户作为首日用户 ,分析该批用户在后续的留存率变化
SELECT dates,COUNT(DISTINCT ub.user_id)*1.0/ (SELECT count(*) from (SELECT DISTINCT user_id from ub WHERE dates = ‘2014-11-18‘) t) as retention from ub join (SELECT DISTINCT user_id from ub WHERE dates = ‘2014-11-18‘) t on ub.user_id = t.user_id GROUP BY ub.dates;
将查询结果数据导入tableau,制作留存率变化趋势图:
分析显示:
①前五日留存率下滑,七日留存率略有回升,留存率随时间具有一定的周期波动规律;
②每日的用户留存率相对稳定,12月12日留存率有明显升高,可能是“双12”购物活动的带动作用。
4.2新增用户次日留存率
SELECT COUNT(DISTINCT u1.user_id)*1.0/(SELECT COUNT(DISTINCT user_id) FROM ub) from ub u1 join ub u2 on u1.user_id = u2.user_id and u2.dates = DATE(u1.dates, ‘+1 day‘);
每日新增用户次日成功留存的比率为:
留存比率为97%,显示出良好的用户黏性
5.商品销量分析
5.1销售量前二十的商品
5.2销量前十的商品类目
五、项目总结
1.本项目利用SQL,对数据进行清洗和加工,结合电商业务理解,提取所需数据,建立电商分析指标;
2.利用tableau可视化工具,将数据指标进行可视化,直观分析数据变化;
3.基于业务指标分析,提出分析结果和业务建议。