数据来源：https://tianchi.aliyun.com/dataset/dataDetail?dataId=46（阿里云天池）
一、分析目的与主要指标
我们的目的是查看双十一之后1个月的用户购物情况，主要有以下指标
1. 日常访问量
2. 购物点击总体情况：用户总访问量PV、 收藏次数、购物车加入数、购买次数；
3. 购物行为转化率与行为转化漏斗；
4. 用户总数、下单购买人数与用户转化率；
5. 回头客与复购情况；
6. 查看点击与购买次数最多/最少的客户情况
7. 查看用户购物活跃 时间
8. 查看用户对商品的喜好
二、数据解析
表名：tianchi_user, 合计1048675行数据，共有6列/字段：
user_id：用户编码
item_id：商品编码
behavior_type：用户行为分类（1点击，2收藏，3加入购物车，4下单购买四种）
user_geohash：地理位置（无效NULL值过多，无法有效分析，隐藏或删除此列）
item_category：商品的类别代号
time：用户行为发生的日期时间
三、数据整理
去重后，将该CSV文件导入Navicat
1.查看完整性，处理缺失值
除了user_geohash已经被隐藏/删除/或不被导入即可，导入检查其他5列，确认数据完整，无需填补。
2. 分列时间
表中time 字段日期和小时合在一起，不便分析，把该字段拆分为 2 个字段，一个日期+一个小时列。
-- 重命名 time 字段名称为 date
ALTER TABLE tianchi_user CHANGE time date VARCHAR(255)
-- 添加 time 列
ALTER TABLE tianchi_user ADD time VARCHAR(20);
-- 复制 date 列到 time 列
UPDATE tianchi_user SET time=date;
-- 在 date 列截取（年-月-日）信息
UPDATE tianchi_user SET date=REPLACE(date,date,SUBSTRING_INDEX(date,' ',1));
-- 在 time 列截取小时信息
UPDATE tianchi_user SET time=REPLACE(time,time,SUBSTRING_INDEX(time,' ',-1));
拆分后表格如下：
3.把用户行为behavior_type（1点击，2收藏，3加入购物车，4下单购买四种）改为便于识别的代号
UPDATE tianchi_user
SET behavior_type=REPLACE(behavior_type,1,'PV');
UPDATE tianchi_user
SET behavior_type=REPLACE(behavior_type,2,'Like');
UPDATE tianchi_user
SET behavior_type=REPLACE(behavior_type,3,'Add');
UPDATE tianchi_user
SET behavior_type=REPLACE(behavior_type,4,'Buy');
修改后如下：
四、数据查询分析
1.日常点击量
从图中可以看出，双十一之后那一个月每天的访问量都是比较平稳的，周末没有明显差别。一般维持在25000-30000之间，而双12前一天开始增加，当天则是翻倍的情况
2.总体情况：点击量PV总访问量、 购物车数量、收藏次数、购买次数
初步观察：点击量很大而加入购物的人数却才到点击量的3%，这里有待观察，可以继续看具体的行为转化率。
SELECT behavior_type, COUNT(*)
FROM tianchi_user
GROUP BY behavior_type;
3.用户行为转化率情况：漏斗模型（tableau）
从下图中可以看出，PV占了总行为数的94%，而添加购物车只占3%，而最后实际购买的在1%，所以从点击到添加购物车这一步，转化最低 ，流失很多。
原因猜想：
1）推荐的商品不到位用户没兴趣，推荐算法有待改进，
2）页面不明确，分类不清，用户难以到达自己想要的商品子类别，
3）商品搜索关键字关联度不足，用户难以搜到自己想要的商品。
4.净点击用户量UV，净购买用户数量，客户转化率
由下图，总客户有8500，最后购买的有4300，总体转化率接近51%，没有行业对比，暂时不清楚情况是好是差。
SELECT COUNT(DISTINCT user_id) AS '用户总数' FROM tianchi_user;
#查询下单购买用户量
SELECT COUNT(DISTINCT user_id) AS '下单购买用户量' FROM tianchi_user
WHERE behavior_type = 'Buy';
创建视图
CREATE VIEW behavior_temp AS
SELECT user_id, count(behavior_type) as '行为次数小计',
SUM(CASE WHEN behavior_type='PV' THEN 1 ELSE 0 END) AS '点击次数',
SUM(CASE WHEN behavior_type='Like' THEN 1 ELSE 0 END) AS '收藏次数',
SUM(CASE WHEN behavior_type='Add' THEN 1 ELSE 0 END) AS '购物车数',
SUM(CASE WHEN behavior_type='Buy' THEN 1 ELSE 0 END) AS '购买次数'
FROM tianchi_user
GROUP BY user_id
ORDER BY COUNT(behavior_type) DESC;
-- DROP VIEW behavior_temp
#查看用户购物情况视图
SELECT * FROM behavior_temp
5.查询购买用户数量、重复购买用户数量以及复购率
回头客近50%，也还需要对比，才知道是好是差。
#查询用户复购率
SELECT SUM(CASE WHEN 购买次数>=2 THEN 1 ELSE 0 END) AS '复购用户',
SUM(CASE WHEN 购买次数>0 THEN 1 ELSE 0 END) AS '总购用户',
CONCAT(ROUND(SUM(CASE WHEN 购买次数>=2 THEN 1 ELSE 0 END)/SUM(CASE WHEN 购买次数>0 THEN 1 ELSE 0 END)*100), '%') AS '复购率'
FR OM behavior_temp
6.#查看购买次数多的用户情况，及其购买率
1）点击量不大、购买次数多的用户，购物目的相对比较明确，一般是刚需，这种客户点击次数/收藏次数都较少而购买率自然就上去了，这种大概适合继续维护，不易开发
2）点击量大，购买次数多的用户，一般是理性偏热衷购物的用户，这大概是我们的重点维护，适当开发的对象
SELECT user_id, 点击次数, 收藏次数, 购物车数, 购买次数,
CONCAT(ROUND(购买次数/点击次数*100, 2),'%') AS '购买率'
FROM behavior_temp
GROUP BY user_id
ORDER BY 购买次数 DESC;
3）购买次数少点击少的用户，我们琢磨不透建议继续观察或放弃；
4）购买次数少，点击次数多的用户可能是选择困难症或是十分精明克制的客户，这种是否可以通过优惠活动或精准营销推送吸引其关注，重点开发。
SELECT user_id, 点击次数, 收藏次数, 购物车数, 购买次数,
CONCAT(ROUND(购买次数/点击次数*100, 2),'%') AS '购买率'
FROM behavior_temp
GROUP BY user_id
ORDER BY 购买次数 ASC;
7.#查看每天用户活跃时段
晚上8点到10点是用户购物活跃的高峰，所以在流量容量的分配，以及晚上客服的排班上可以有所扩张。在这个时段有效运用活动推广。
SELECT time, count(behavior_type) as '行为次数小计',
SUM(CASE WHEN behavior_type='PV' THEN 1 ELSE 0 END) AS '点击次数',
SUM(CASE WHEN behavior_type='Like' THEN 1 ELSE 0 END) AS '收藏次数',
SUM(CASE WHEN behavior_type='Add' THEN 1 ELSE 0 END) AS '购物车数',
SUM(CASE WHEN behavior_type='Buy' THEN 1 ELSE 0 END) AS '购买次数'
FROM tianchi_user
GROUP BY time
ORDER BY COUNT(behavior_type) DESC;
7.#查看用户对商品的喜好，以点击量为基准
#查看用户点击最多的商品类别
SELECT item_category, COUNT(item_category) AS '商品类别点击量'
FROM tianchi_user
WHERE behavior_type = 'PV'
GROUP BY item_category
ORDER BY 商品类别点击量 DESC;
点击最多的类别为1863，前6类商品的点击量均已超过2万，后续直线下降。
#查看用户点击最多商品名称
SELECT item_category, item_id, COUNT(item_id) AS '商品点击量'
FROM tianchi_user
WHERE behavior_type = 'PV'
GROUP BY item_id
ORDER BY 商品点击量 DESC;
点击量最多的商品为112921337，可以看到点击量最多的商品并不在点击最多的商品类别里面，这很正常，可以猜测用户在寻找这几个商品时都很明确。
五、总结问题与改善建议
1、整体上用户行为转化率近1%，主要在用户点击后加入购物车或收藏的这一环节转换率过低，可通过精准营销或作活动引流，增强商品关键字搜索关联性等等手段提高用户转化。
2、购买次数多的用户，购买率较高，同时点击量并不高，说明他们购物目标明确，很有可能是刚需，这些用户需要保持就好；购买次数多，点击量也多的用户可能是理性消费用户需要重点维护，适当开发。
购买次数少点击少的用户，我们琢磨不透建议继续观察或放弃；购买次数少，点击次数多的用户可能是选择困难症或是十分精明克制的客户，这种是否可以通过活动或精准营销推送吸引其关注，重点开发。
3、用户日常活跃度通常都比较平稳，维持在25000-30000之间。在一天中，用户在晚上8--11点最为活跃，这个时段网站流量，促销活动，客服支持等等可以向此倾斜。
4、用户点击最多的6类商品均已突破2万，初步预测那六类商品用户需求较大，而点击最多的具体商品112921337，用户点击前4的商品对应的类别不在点击最多的商品类别里面，说明这个类别的商品，用户寻找的目的比较明确。
