SQL项目之电商数据分析

流程：

一、确定分析目的
二、理解数据
三、数据清洗
四、构建模型
五、结论和建议

一、分析目的

提出问题：
1.网站的流量数据在什么水平？
2.跳失率及用户转化情况如何？
3.用户最活跃的日期和时间段是什么时候？
4.核心用户群在哪里？
5.哪些商品和商品类目最受欢迎？

二、理解数据

数据来源： tianchi.aliyun.com/dataset/dat…
数据介绍： 数据为2017年11月25日-2017年12月3日之间大约100万用户的数据，说明如下：

三、数据清洗

1.导入数据
导入数据的时候数据太多mysql查询会很慢，就取前100w条非空数据。

use my_db
/*创建表
create table users_data(
user_id int,
item_id int,
category_item int,
behavior varchar(255),
beha_time varchar(255),
primary key(user_id,item_id,beha_time));

/*读取数据
load data infile 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/UserBehavior_100.csv'
into table users_data
fields terminated by ','
lines terminated by '\n';

2.检查缺失值、重复值

select user_id,item_id,category_item,behavior,beha_time
from users_data
where user_id is null
or item_id is null
or category_item is null
or behavior is null
or beha_time is null;

缺失值在前期已经清洗掉了。
3.是否存在异常值？
（1）原数据的时间戳用epoch&unix timestamp格式，要转换成标准格式，可新增3个字段b_datetime,dates,hours存放：

alter table users_data add column b_datetime timestamp(0) null;
update users_data set b_datetime=from_unixtime(beha_time);
alter table users_data add column dates char(10) null;
update users_data set dates=substring(b_datetime from 1 for 10);
alter table users_data add column hours char(10) null;
update users_data set hours=substring(b_datetime from 12 for 8);

查询时间是否满足区间要求？

select max(beha_time),min(beha_time),max(b_datetime),min(b_datetime)
from users_data;

时间存在异常值，进行删除，删除了470条记录：

delete from users_data
where b_datetime<'2017-11-25 00:00:00'
or b_datetime>'2017-12-04 00:00:00';

（2）检查行为是否为'pv','buy','cart','fav'？

select *
from users_data
where behavior not in('pv','buy','cart','fav');

行为不存在异常值，无需处理。

四、构建模型

1.平台整体情况分析：流量、跳转率和订单量
(1)访客数UV、访问量PV、平均访问量PV/UV

/*访客数UV、访问量PV、平均访问量PV/UV*/
select count(distinct user_id) UV,
(select count(*) from users_data where behavior='pv') PV,
(select count(*) from users_data where behavior='pv')/(count(distinct user_id)) 'PV/UV'
from users_data;

网站这9天的访客数UV是9739，访问量PV是895636，平均访问量是91.96，每人的日均访问量约10.21。
(2)跳失率

/*跳失率=仅点击的用户数/总用户数*/
select count(distinct user_id)
from users_data
where user_id not in(select distinct user_id from users_data where behavior<>'pv')

得到仅pv、无cart、fav、buy的客户数有567位，UV为9739，跳失率为5.82%。
(3)总订单量及下单转化率

/*总订单量*/
select count(*) 总订单量 
from users_data
where behavior='buy';

/*访问下单转化率*/
select concat(round(
(select count(*) from users_data where behavior='buy')/(select count(*) from users_data where behavior='pv')*100,2),'%') 访问订单转化率
from users_data limit 1;

平台在这9天的总订单量为20359件，其中pv的订单转化率仅为2.27%。
2.用户行为模式分析：最活跃的日期和每天最活跃的时间段

/*平台活跃变动趋势*/
select dates,behavior,count(*) 点击次数,count(distinct user_id) 日活跃用户数
from users_data
group by dates,behavior;

活跃日期：从每日pv活跃数据可以看到，11月25日-12月1日基本保持稳定，12月2日-3日陡增，增长率约为25%，原因应该是工作日较忙，而周末休息，有闲暇时间访问淘宝，建议平台活动集中在周末。

/*分时段活跃情况*/
select hour(b_datetime),cast(count(*)/9 as decimal(18,2)) 点击次数,cast(count(distinct user_id)/9 as decimal(18,2)) 活跃用户数
from users_data
where behavior='pv'
group by hour(b_datetime);

活跃时间段:从19时开始pv活跃量稳步上升，21时-23时之间是高峰期，23时之后回落，说明大部分用户在晚上19时-23时频繁浏览网页，建议晚间该时段举办直播等营销活动。
3.用户行为数据分析:订单量和漏斗分析(行为次数/独立客户数)

select behavior,count(*) 次数,count(distinct user_id) 用户数,count(distinct item_id) 商品数
from users_data
group by behavior;

用户行为包括浏览、加购物车、收藏和购买，所有的行为中，pv占比89.61%，cart占比5.55%，而buy的占比只有2%，确定夹点位置在cart环节，分析原因：
1)客户大量浏览商品却没有加购物车，可能是商品推荐不够精准，优化平台的产品推荐精准度，或者是平台的筛选功能、产品对比功能完善，考虑优化或增加。
2)buy在cart+fav的占比24.40%偏低，是重点提升环节，可能是用户在考虑，可以及时推送优惠信息，或者简化下单流程。

上图是每个行为中独立客户数的分布情况，可以计算付费用户PUR约68.91%，转化率比较高。
4.产品销售分析：销量排名和用户价值
(1)商品销量和种类分布

create view product_rank
as
select item_id,count(user_id) 订单量
from users_data
where behavior='buy'
group by item_id;

select 订单量,count(item_id)
from product_rank
group by 订单量
order by count(item_id) desc;

本次分析的产品总共有17565种，从上图可以看出，购买1次的商品15536种占比88.44%很高，购买2次的1568种占比8.92%，大多数产品都是一次购买。
(2)销量top10的商品和品类

/*top10销量商品*/
select item_id,count(*) 订单量
from users_data
where behavior='buy'
group by item_id
order by 订单量 desc limit 0,10;

以上是销量最高的前十款商品,3122135销量明显高于其他产品，可重点分析该产品信息。

/*top10销量品类*/
select category_item,behavior,count(*) 订单量,count(distinct item_id) 商品数量
from users_data
where behavior='buy'
group by category_item
order by 订单量 desc limit 0,10;

以上是top10销量的品类及所含商品数量，2735466和1464116品类销量明显高于其他品类，但包含的商品种类却不是最高，分析原因。

(3)价值用户RFM

/*消费频率*/
create view buy_f(user_id,b,f)
as
select user_id,订单数,
(case when 订单数 between 1 and 17 then 0
when 订单数 between 18 and 34 then 1
when 订单数 between 35 and 52 then 2
when 订单数 between 53 and 70 then 3
when 订单数 between 71 and 84 then 4 else null end) f
from (select user_id,count(*) 订单数
from users_data
where behavior='buy' group by user_id) t
order by f desc;

/*最近购买时间*/
create view buy_r(user_id,a,r)
as
select user_id,间隔天数,
(case when 间隔天数 between 0 and 1 then 4
when 间隔天数 between 2 and 3 then 3
when 间隔天数 between 4 and 5 then 2
when 间隔天数 between 6 and 7 then 1
when 间隔天数 between 8 and 9 then 0 else null end) r
from (select user_id,datediff('2017-12-3',max(dates)) 间隔天数
from users_data 
where behavior='buy'
group by user_id) t
order by r desc;
select * from buy_r;

/*平均值：2.9504,0.0076*/
select avg(r) 'R平均值' from buy_r;
select avg(f) 'F平均值' from buy_f;

/*RFM模型*/
create view RFM
as
select a.user_id,a.r,b.f,
(case when a.r>=2.9504 and b.f>=0.0076 then '重要价值客户'
 when a.r<2.9504 and b.f>=0.0076 then '重要唤回客户'
 when a.r>=2.9504 and b.f<0.0076 then '重要深耕客户'
 when a.r<2.9504 and b.f<0.0076 then '重要挽留客户' else null end) '客户分类'
from buy_r a
inner join buy_f b
on a.user_id=b.user_id
order by r desc,f desc;

select 客户分类,count(*) 客户数量
from RFM
group by 客户分类;

RFM模型：得到了每位客户的分类，重要深耕客户占比68.07%，重要挽留客户占比31.34%，重要价值客户和重要唤回客户占比不足1%。

select a.订单量,count(*) 用户数
from (select user_id,count(user_id) 订单量
from users_data
where behavior='buy'
group by user_id) a
group by 订单量
order by 用户数 desc;

复购率：用户的整体复购率为66.21%，即所有客户中大概有66.2%的用户会回购，而大约33.79%的客户只会购买一次。

select user_id,count(user_id) 购买次数
from users_data
group by user_id
order by 购买次数 desc
limit 0,10;

以上是复购客户中下单量top10的客户，用户107932购买次数高达72次。

select category_item,count(*)
from users_data
where behavior='buy'
and user_id=107932
group by category_item
order by count(*) desc;

对下单量最大的用户107932的下单情况分析，以上是该用户最倾向于购买的10个商品品类。这样有针对性的对高价值客户进行分析，可以深度了解用户的产品喜好、下单频率等，有针对性的进行个性化产品推荐。

五、总结与建议

1、获取客户：周末、节假日客户的活跃度高于工作日，晚间19时至23时客户的活跃度高于其他时间段，淘宝客户技术大，可以利用该时间段开展活动发力拓客，如分享拼团优惠、线上直播分享、邀请有礼、KOL推广、热门视频等。
2、激活用户：用户的行为包括浏览、到加购物车、收藏或者购买，其中浏览点击量高达89.6%，而购买占比仅仅2.04%，转化率极低，可能的原因及对策有：
1）产品推荐不准确或筛选功能不完善，导致用户花很多时间浏览寻找产品，可以增加关键词的准确率，让客户更容易找到心仪的产品；
2）产品的对比功能不完善，导致客户花费时间多次浏览产品信息反复比较，考虑增加产品对比功能；
3）精简下单流程，提高用户体验。 3、提高留存：培养客户定期访问习惯，可以通过定期登录奖励、下单奖励或小游戏等方式提高留存率。
4、增加收入：提升转化率和复购率。
1）独立用户的下单转化率约68%比较高，还可以通过对加购物车、收藏等用户精准发送商家优惠活动提醒等，提高从加购物车/收藏行为到下单行为的转化率。
2）关注复购客户和高价值客户，推出复购优惠活动，进行精细化经营。
5、推荐：借鉴获客环节，在活跃度最高的时候进行推荐、邀请、转发、打卡等活动提高转发率和K因子。