目前，电商发展趋势迅猛，网购已经渐渐成为主流的购买方式。用户在进行网络购物时，会产生海量的相关数据，这些数据蕴藏着无穷无尽的价值，本文就基于阿里云天池数据集，对用户的购物时产生的数据运用SQL进行分析。
1.提出问题用户在购物时会产生一系列的数据，可以将这些数据分为用户、商品两个维度。从用户维度来说，可以分析用户在哪一天最活跃，在一天中的哪个时间段最活跃，每个用户的购买情况如何，是否存在复购行为等等，同时可以对用户进行价值分析，将用户分类，针对不同的用户提出不同的留存方案，提高用户留存率；针对商品维度，可以分析每个商品的点击、收藏、加购到最终购买的各项数据，分析出销量、点击量等前十的商品，对商品类目进行同样的分析，可以找出畅销的商品，对于电商平台的运营方面有重要的帮助。
2.理解数据本文的数据来自于阿里云天池：User Behavior Data from Taobao for Recommendation，该数据包括淘宝app从2017年11月25日至2017年12月3日之间，在app上发生过操作记录的用户数据（操作记录包括浏览、加购、收藏、购买）。数据包含五个字段：用户id、商品id、商品类别id、行为类别、时间戳。
3.数据清洗 3.1 导入数据源数据包含100,150,807条行为记录，现随机取样100000条用户行为记录进行初步分析
create table userinformation (
select *
from 源数据
limit 100000
)
3.2 缺失值处理
找出源数据中字段存在空值的数据并删除
delete from userinformation
where (用户id is null) or (商品id is null)
or (商品类别id is null) or (行为类别 is null) or (时间戳 is null)
3.3 一致化处理
源数据表示时间的字段为时间戳格式，现将其转化成'xxxx-xx-xx'格式的年月日，'xx:xx:xx'格式的小时、分、秒格式，同时提取出日期格式对应的是星期几，时间格式对应的小时时间段：
alter table userinformation
add column 日期 varchar(255);
alter table userinformation
add column 星期 varchar(255);
alter table userinformation
add column 时间 varchar(255);
alter table userinformation
add column 小时 varchar(255);
update userinformation
set 日期 = from_unixtime(时间戳,'%Y-%m-%d');
update userinformation
set 星期 = from_unixtime(时间戳,'%w');
update userinformation
set 时间 = from_unixtime(时间戳,'%H:%I:%S');
update userinformation
set 小时 = from_unixtime(时间戳,"%H");
完成时间戳转化的用户信息表如下：
3.4 异常值处理此次分析的时间范畴为2017年11月25日至2017年12月3日，不在此范畴的数据需要删去
delete from userinformation where 日期 < '2017-11-25' or 日期 > '2017-12-03'
至此，数据清洗完成，接下来使用userinformation表进行数据分析
4.数据分析与可视化处理 4.1 用户pv、uv时间模型先将用户pv、uv按照日期、小时两个维度分别进行分析
--按日期 select 日期,count(用户id) pv from userinformation where 行为类别 = 'pv' group by 日期 order by 日期; --按小时 select 小时,count(用户id) pv from userinformation where 行为类别 = 'pv' group by 小时 order by 小时;
得出按照日期、小时两个维度用户的pv、uv具体数据：
将数据可视化
在以日期为维度分析用户行为时，可以看出用户行为趋势从11月27日起逐渐增长，在12月2日时登录用户增长趋势曲线上升较为迅猛，由此可以推测，淘宝双12活动在12月2日这一天开始预热，平台若想要引流，则可以参考12月2日这天的用户登陆量，在平时适量的增加一些优惠活动，以吸引更多活跃用户。
从浏览量随时间变化这张图可以看出，在20点至0点这个阶段，打开淘宝app并浏览商品的用户明显增多，商家可以根据这样的趋势在该时间段多推送商品以及活动信息，达到流量效益最大化。
4.2 用户留存率分析用户留存率对于app来说，是一个衡量的重要标准。由此，对用户进行留存率分析：
\创建表 liucunlv ，将用户id和日期分组，选出每个用户在哪些日期有操作记录\
create table liucunlv(
select 用户id,日期
from userinformation
group by 用户id, 日期
order by 用户id,日期
)
\在liucunlv中插入一个新字段 最早操作记录，记录用户首次有操作记录的日期\
alter table liucunlv
add column 最早操作日期 varchar(255);
update liucunlv
set 最早操作日期 =(select a.最早操作日期
from (select 用户id,min(日期) 最早操作日期
from liucunlv
group by 用户id) a
where liucunlv.用户id = a.用户id)
\在liucunlv中插入一个新字段 日期差值，记录同一个用户有操作记录的日期和最早操作日期的差值\
alter table liucunlv
add column 日期差值 varchar(255);
update liucunlv
set 日期差值 = datediff(日期,最早操作日期)
\在liucunlv中插入一个新字段 星期，记录日期所对应的星期\
alter table liucunlv
add column 星期 varchar(255);
update liucunlv
set 星期 = dayofweek(日期)
\得出得星期以7进制表示，使用case when将其转换成具体的星期几\
update liucunlv
set 星期 = (case when 星期 = '1' then '星期天'
when 星期 = '2' then '星期一'
when 星期 = '3' then '星期二'
when 星期 = '4' then '星期三'
when 星期 = '5' then '星期四'
when 星期 = '6' then '星期五'
when 星期 = '7' then '星期六'
else '' end)
\新建表 count_liucunlv，将日期差值分类，并计算差值，得出不同日期的不同留存率的人数\
create table count_liucunlv(
select 最早操作日期,
sum(case when 日期差值 = '0' then 1 else 0 end) day_1,
sum(case when 日期差值 = '1' then 1 else 0 end) day_2,
sum(case when 日期差值 = '2' then 1 else 0 end) day_3,
sum(case when 日期差值 = '3' then 1 else 0 end) day_4,
sum(case when 日期差值 = '4' then 1 else 0 end) day_5,
sum(case when 日期差值 = '5' then 1 else 0 end) day_6,
sum(case when 日期差值 = '6' then 1 else 0 end) day_7,
sum(case when 日期差值 = '7' then 1 else 0 end) day_8
from liucunlv
group by 最早操作日期
order by 最早操作日期
)
\对count_liucunv也加一行星期，方法同上\
alter table count_liucunlv
add column 星期 varchar(255);
update count_liucunlv
set 星期 = dayofweek(最早操作日期);
update count_liucunlv
set 星期 = (case when 星期 = '1' then '星期天'
when 星期 = '2' then '星期一'
when 星期 = '3' then '星期二'
when 星期 = '4' then '星期三'
when 星期 = '5' then '星期四'
when 星期 = '6' then '星期五'
when 星期 = '7' then '星期六'
else '' end);
得出用户留存率表：
对应不同首次登录日期的留存率而言，留存率在逐渐下降，但在双12活动预热开启时，用户首次登录时间对应预热活动日期的留存率从前几日的下降变成陡然上升，由此可以推断出，较大的优惠活动可以有效的留住用户，增大用户保存率，提高app活性。
4.3 用户购买次数分析用户购买次数是衡量用户价值的一个重要指标，在后续的RFM分析中也需要对用户的购买次数进行统计
select 用户id,count(用户id) 购买次数 from userinformation where 行为类别 = 'buy' group by 用户id;
将数据可视化为树状图
由树状图可以看出，购买1次的用户占比最多，随着购买次数的增加，购买次数所对应的人群数量也逐渐递减。在双12活动中，如果能将只购买一次的用户转化为购买多次的用户，销售额将进一步提高，用户黏性也会随之提高。但购买1次的用户的转化难度也比较大，需要产品运营部进一步针对这一部门顾客推出活动方案，比如满减、凑单等等活动。
4.4 用户行为转化率分析用户转化率可以用来直观的查看业务流程各环节的转化率，可以明确出转化率低的环节，并对其做出针对性的改善。
首先计算各操作类别的用户数，并创建表
create table leibie( select 行为类别,count(用户id) 浏览人数 from userinformation where 行为类别 = 'pv'); insert into leibie select 行为类别,count(用户id) 购买人数 from userinformation where 行为类别 = 'buy'; insert into leibie select 行为类别,count(用户id) 收藏人数 from userinformation where 行为类别 = 'fav'; insert into leibie select 行为类别,count(用户id) 加购人数 from userinformation where 行为类别 = 'cart';
得到用户行为类别记录表
对该表进行做进一步处理，并将其进行可视化处理
从类别分析漏斗图可以看出，最终购买的人数相比于点击的人数转化率只有2.37%，流失率最大的环节处于点击——加购环境，大量用户在这一环节流失，导致最终购买人数只占总点击人数的2.27%，而相较于上一环节的转化率，可以看出收藏——购买的转化率比较高，达到了78.64%，分析点击——加购环节流失的主要原因有两个，一是商品类别不符合用户期待，二是商品价格超过了用户的心里预期，针对这一流失问题，需要在向用户推送商品时精准推送，按照用户标签进行推送，以提高点击——加购这一环节的转化率。
4.5 复购天数间隔复购天数为用户第一天购买商品到下次购买商品的日期间隔，是用来衡量用户价值的重要量度
create table 复购间隔(
select a.用户id 用户id,a.第一天登录日期,b.日期
from (select distinct 用户id, 行为类别,min(日期) 第一天登录日期
from userinformation
group by 用户id,行为类别
) a
left join (select distinct 用户id, 日期, 行为类别
from userinformation
) b
on a.用户id = b.用户id
where (a.行为类别 = 'buy') and (b.行为类别 = 'buy') and (a.第一天登录日期 < b.日期)
);
select a.用户id,a.第一天登录日期,b.最早复购日期,c.复购间隔
from (select distinct 用户id,第一天登录日期
from 复购间隔) a
join
(select 用户id,min(日期) 最早复购日期
from 复购间隔
group by 用户id) b
on a.用户id = b.用户id
join
(select 用户id,min(复购间隔) 复购间隔
from 复购间隔
group by 用户id) c
on a.用户id = c.用户id
order by 第一天登录日期,最早复购日期;
create table 复购间隔天数分布(
select sum(case when 复购间隔 = 1 then 1 else 0 end) 间隔一天,
sum(case when 复购间隔 = 2 then 1 else 0 end) 间隔两天,
sum(case when 复购间隔 = 3 then 1 else 0 end) 间隔三天,
sum(case when 复购间隔 = 4 then 1 else 0 end) 间隔四天,
sum(case when 复购间隔 = 5 then 1 else 0 end) 间隔五天,
sum(case when 复购间隔 = 6 then 1 else 0 end) 间隔六天,
sum(case when 复购间隔 = 7 then 1 else 0 end) 间隔七天,
sum(case when 复购间隔 = 8 then 1 else 0 end) 间隔八天
from (select a.用户id, a.第一天登录日期, b.最早复购日期, c.复购间隔
from (select distinct 用户id, 第一天登录日期
from 复购间隔) a
join
(select 用户id, min(日期) 最早复购日期
from 复购间隔
group by 用户id) b
on a.用户id = b.用户id
join
(select 用户id, min(复购间隔) 复购间隔
from 复购间隔
group by 用户id) c
on a.用户id = c.用户id
order by 第一天登录日期, 最早复购日期) a
);
从复购天数间隔气泡图可以看出，复购间隔一天的人数是最多的，往后随着间隔天数的增加，复购人数递减，想要增加复购人数，提高用户粘性，可以在用户购买的第二天向客户推送在收藏或购物车中商品的相关信息，增强用户购买欲望，提高复购率。
4.6 用户价值分析 RFM用户价值分析包括三个方面，最近消费日期，指定时间段消费频率，消费金额，源数据不包含用户消费，故从最近消费日期、指定时间段消费频率进行分析，此次分析将指定时间段设定为2017年11月25日至2017年12月3日，现对用户进行RFM分析
RFM打分标准如下所示：
\找出每个用户的上次消费日期到2017-12-03的天数间隔\
select a.用户id 用户id,min(最近消费日期) 最近消费日期间隔
from (select 用户id, datediff('2017-12-03', 日期) 最近消费日期
from userinformation
where 行为类别 = 'buy'
group by 用户id, 日期) a
group by 用户id
order by 最近消费日期间隔 desc;
\找出每个用户的购买次数\
select 用户id,count(用户id) 购买次数
from userinformation
where 行为类别 = 'buy'
group by 用户id;
\两表联立\
select aaa.用户id 用户id,bbb.购买次数 购买次数,aaa.最近消费日期间隔 最近消费日期间隔
from (select a.用户id 用户id, min(最近消费日期) 最近消费日期间隔
from (select 用户id, datediff('2017-12-03', 日期) 最近消费日期
from userinformation
where 行为类别 = 'buy'
group by 用户id, 日期) a
group by 用户id
order by 最近消费日期间隔 desc) aaa
join (select 用户id, count(用户id) 购买次数
from userinformation
where 行为类别 = 'buy'
group by 用户id) bbb
on aaa.用户id = bbb.用户id
order by 购买次数 desc,最近消费日期间隔
\对用户RFM进行打分\
alter table rfm
add column R评分 varchar(255);
alter table rfm
add column F评分 varchar(255);
update rfm
set R评分 = (case when 最近消费日期间隔 >= 0 and 最近消费日期间隔 <= 1 then 1
when 最近消费日期间隔 >= 2 and 最近消费日期间隔 <= 3 then 2
when 最近消费日期间隔 >= 4 and 最近消费日期间隔 <= 5 then 3
when 最近消费日期间隔 >= 6 and 最近消费日期间隔 <= 7 then 4
when 最近消费日期间隔 >= 8 then 5
else '' end);
update rfm
set F评分 = (case when 购买次数 >= 0 and 购买次数 <= 1 then 1
when 购买次数 >= 2 and 购买次数 <= 5 then 2
when 购买次数 >= 6 and 购买次数 <= 20 then 3
when 购买次数 >= 21 and 购买次数 <= 40 then 4
when 购买次数 >= 41 then 1
else '' end);
\RFM平均值\
alter table rfm
add column R平均值 varchar(255);
alter table rfm
add column F平均值 varchar(255);
update rfm
set R平均值 = (select a.R平均值
from (select round(avg(R评分), 2) as R平均值
from rfm) a );
update rfm
set F平均值 = (select a.F平均值
from (select round(avg(F评分), 2) as F平均值
from rfm) a );
\用户分类\
alter table rfm
add column 用户分类 varchar(255);
update rfm
set 用户分类 = (case when R值高低 = '高' and F值高低 = '高' then '价值用户'
when R值高低 = '高' and F值高低 = '低' then '发展用户'
when R值高低 = '低' and F值高低 = '高' then '保持用户'
when R值高低 = '低' and F值高低 = '低' then '挽留用户'
else '' end);
\创建用户分类数量表\
create table RMF用户分类数量(
select 用户分类, count(用户分类) 用户数量
from rfm
where 用户分类 = '发展用户'
);
insert into rmf用户分类数量
select 用户分类, count(用户分类)
from rfm
where 用户分类 = '挽留用户';
insert into rmf用户分类数量
select 用户分类, count(用户分类)
from rfm
where 用户分类 = '保持用户';
insert into rmf用户分类数量
select 用户分类, count(用户分类)
from rfm
where 用户分类 = '价值用户';
得出用户RFM分布数据，并将其可视化
从用户RFM分析可以看出，保持用户，即购买次数较多但近期不活跃的用户，占比超过了50%，是用户组成中的重要组成部分，对于这一部分用户，可以定期发送召回短信或针对性推送，使其转换为价值用户，用户其他份额由发展用户、价值用户、挽留用户近乎均匀等分，针对这一部分群体，也需做出客户精细化运营策略，维护用户数量，保证用户质量。
4.7 Top10商品分析对商品进行排序分析，可以直观的了解到哪些商品为畅销商品，以此作为调整销售决策的参考值之一。
\选出浏览量、销量、加购量、收藏量各自为前10的商品\ select 商品id,count(商品id) 浏览量 from userinformation where 行为类别 = 'pv' group by 商品id order by 浏览量 desc limit 10; select 商品id,count(商品id) 销量 from userinformation where 行为类别 = 'buy' group by 商品id order by 销量 desc limit 10; select 商品id,count(商品id) 加购量 from userinformation where 行为类别 = 'cart' group by 商品id order by 加购量 desc limit 10; select 商品id,count(商品id) 收藏量 from userinformation where 行为类别 = 'fav' group by 商品id order by 收藏量 desc limit 10;
将所得到的数据进行可视化处理
得到各类行为类别的Top10商品后，对销量Top10的商品分析出其对应的浏览量，对浏览量Top10的商品分析出其对应的销量，并将数据做可视化处理。
\找出浏览量前十商品的最终销量\
select a.商品id,a.浏览量,b.销量
from (select 商品id, count(商品id) 浏览量
from userinformation
where 行为类别 = 'pv'
group by 商品id
order by 浏览量 desc
limit 10
) a
join (select 商品id,count(商品id) 销量
from userinformation
where 行为类别 = 'buy'
group by 商品id) b
on a.商品id = b.商品id;
\找出销量前十商品的起始浏览量\
select a.商品id,a.销量,b.浏览量
from (select 商品id, count(商品id) 销量
from userinformation
where 行为类别 = 'buy'
group by 商品id
order by 销量 desc
limit 10
)a
join (select 商品id,count(商品id) 浏览量
from userinformation
where 行为类别 = 'pv'
group by 商品id) b
on a.商品id = b.商品id;
4.8 Top10商品类目分析
对商品类目进行Top10分析的目的和分析Top10商品的目的一致，都是为了找出畅销的商品类别，以此参照对销售策略做出调整。
\找出浏览量、销量、加购量、收藏量前十的商品类别\ select 商品类别id,count(商品类别id) 浏览量 from userinformation where 行为类别 = 'pv' group by 商品类别id order by 浏览量 desc limit 10; select 商品类别id,count(商品类别id) 销量 from userinformation where 行为类别 = 'buy' group by 商品类别id order by 销量 desc limit 10; select 商品类别id,count(商品类别id) 加购量 from userinformation where 行为类别 = 'cart' group by 商品类别id order by 加购量 desc limit 10; select 商品类别id,count(商品类别id) 收藏量 from userinformation where 行为类别 = 'fav' group by 商品类别id order by 收藏量 desc limit 10;
得到各类行为类别的Top10商品类别后，对销量Top10的商品类别分析出其对应的浏览量，对浏览量Top10的商品类别分析出其对应的销量，并将数据做可视化处理。
4.9 商品、商品类别浏览量与销量相关性分析对于商品和商品类别来说，浏览量和最终销量是对其影响较大的两个度量，收藏量以及加购量对于销量都有一定程度的影响，这里单独分析浏览量和销量的相关性。
从商品相关性散点图可以看出，浏览量和销量整体呈正相关趋势，且大量数据为存在浏览量，但不存在销量的商品。
从商品类别散点图也可以看出，商品类别的销量与浏览量整体呈正相关趋势，同时和商品散点图相似，也存在大量的存在浏览量但不存在销量的商品类别。
5.结论与建议·针对pv、uv模型、留存率模型、购买次数，可以增加促销活动，提高用户访问量，在夜间时段增加推送信息，增加用户活跃度。
·针对转化率模型、复购率模型，根据流失率较高的环节，进行针对性的改善，提高转化率，同时在首次购买后，对购买用户发送促销信息，提高其复购率。
·用户价值分析模型，针对不同价值的用户，提出不同策略的用户激励政策，进行用户精细化运营策略，提高用户黏度。
·商品、商品类别Top模型，多宣传销量靠前的商品，实现盈利最大化，同时对于浏览量前十的商品以及商品类别，提高其转化率。
