一. 数据处理:
1.1 数据源导入
使用Navicat 将数据源(csv)导入至Mysql中,建立新的Table(train_user)。
![8f7988184df1501e5b89fb4866dbcac7.png](https://i-blog.csdnimg.cn/blog_migrate/4735be8d4fbc21f8cfb9b3f5acf42859.png)
![81b5e929d9bdeedd454a46d43ce15af4.png](https://i-blog.csdnimg.cn/blog_migrate/9112b81470d5eb8a64d41eecf80844f9.png)
1.2 数据理解:
![17f64ef4cad7ecf353c870a9454a34e2.png](https://i-blog.csdnimg.cn/blog_migrate/6f58f359be294aac2a9def4a4b21a928.png)
1.3 数据清洗
1.3.1 去重复数据
--去除重复数据
SELECT * from train_user
group by item_category,item_id,user_id,behavior_type,user_geohash,time
having count(*) >1
这里运行时,发现有大量重复数据,应该是我之前有导入记录,再次导入时间没有选择在新表插入数据,所以这里对重复数据进行清洗;
-- 创建新表,过滤重复数据
CREATE TABLE train_user1 SELECT *
FROM train_user
GROUP BY user_id,item_id,behavior_type,user_geohash,item_category,time;
-- 检查是否还有重复数据存在
SELECT * from train_user1
group by item_category,item_id,user_id,behavior_type,user_geohash,time
having count(*) >1
![76554336d42aa57b65be721a93ceb16e.png](https://i-blog.csdnimg.cn/blog_migrate/22292347841436d8680fbcd807ac1e81.png)
1.3.2 缺失值处理
这里user_geohash 位置信息,是加密处理的,所以后续不做详细分析
--计算各个值
select count(item_category),count(user_id),count(item_id),count(behavior_type),count(time)
from train_user1
![508c67076dcb6d4a39eff672bb39c85e.png](https://i-blog.csdnimg.cn/blog_migrate/16edce7a9b9b40848b20af68c65ba6e9.png)
1.3.3 数据格式类型转化
在导入数据时,时间类型为varchar,这里需要转化为datetime 格式,方便后续数据分析
-- 把time按照%Y-%m-%d %H 拆分为两列
Alter TABLE train_user1 add COLUMN Date_time Datetime(8) not null after item_category
Update train_user1 set Date=time
-- 取Date中前10个字段,并替换
Update train_user1
set Date=REPLACE(Date,Date,SUBSTRING_INDEX(Date," ",1))
Update train_user1
Set Hour_time=REPLACE(time,time,SUBSTRING_INDEX(time," ",-1))
![06ed93f45fdd3eb22d1cb2ce2d91c01a.png](https://i-blog.csdnimg.cn/blog_migrate/d390af3b2c8ea6e5ec6d0c993f881a29.png)
1.3.4 异常值处理
select SELECT max(Date_time),MIN(Date_time) from train_user
![507b273f793d4e3066db61f7dd4bef60.png](https://i-blog.csdnimg.cn/blog_migrate/5b4b864a685f83a21bf139d3d63b505b.png)
二. 指标分析
2.1流量指标分析
2.1.1 总体分析PV,收藏,加购,下单及访问用户数
select COUNT(DISTINCT user_id) as '用户访问数',
sum(case when behavior_type='1' then 1 else 0 end ) as 'Pv',
sum(case when behavior_type='1' then 1 else 0 end)/COUNT(DISTINCT user_id)'人均Pv',
sum (case when behavior_type='2' then 1 else 0 end) as 'Fav',
sum (case when behavior_type='3' then 1 else 0 end) as 'Car',
sum(case when behavior_type='4'then 1 else 0 end ) as 'Buy'
from train_user
![c24753432dc614aed01dd22bea70e7e5.png](https://i-blog.csdnimg.cn/blog_migrate/a14f414f40a3e984b3c0030fb1c40896.png)
2.2.2 日均分析PV,收藏,加购,下单及访问用户数
select Date_time,COUNT(DISTINCT user_id) as '用户访问数',
sum(case when behavior_type='1' then 1 else 0 end ) as 'Pv',
sum(case when behavior_type='1' then 1 else 0 end)/COUNT(DISTINCT user_id)'人均Pv',
sum(case when behavior_type='2' then 1 else 0 end) as 'Fav',
sum(case when behavior_type='3' then 1 else 0 end) as 'Car',
sum(case when behavior_type='4'then 1 else 0 end ) as 'Buy'
from train_user
group by Date_time
order by Date_time asc
![166586b8984d39bc86817c7fe68bdc70.png](https://i-blog.csdnimg.cn/blog_migrate/1a8788565b095af5d4177f1fe28ea933.png)
![aca84301744f2757cc2edf5d246905a4.png](https://i-blog.csdnimg.cn/blog_migrate/94ca391668ea04d0d3cb472a0b7ad883.png)
双12当天,用户的浏览,下单都创造了有记录的新高。
2.2 转化指标分析
采用漏斗分析模型,获取各个环节的用户数量,并绘制漏斗图。
-- 创建视图user_behavior,存放每个用户的行为指标
create view user_behavior AS
select user_id,COUNT(behavior_type),
sum(case when behavior_type='1' then 1 else 0 end ) as 'Pv',
sum(case when behavior_type='2' then 1 else 0 end) as 'Fav',
sum(case when behavior_type='3' then 1 else 0 end) as 'Car',
sum(case when behavior_type='4'then 1 else 0 end ) as 'Buy'
from train_user
group by user_id
![d9276cecdaf9dbd17220e1bb821f1597.png](https://i-blog.csdnimg.cn/blog_migrate/fcd4c6fd8ba78a787c42c91b88dafdaf.png)
2.2.2 转化率分析
SELECT sum(pv),sum(Fav),sum(Car),sum(Buy)
from user_behavior
![3e9555556ad8e33bebd0274d02a64bd4.png](https://i-blog.csdnimg.cn/blog_migrate/16e9057ad3bcd41e7e8448418aa0dc24.png)
![e6f065d6655e00b271b57d89db03edcf.png](https://i-blog.csdnimg.cn/blog_migrate/116ebfd544622a23f9ba9085d81e254c.png)
最后成交的数量转化率在1.04%,用户在浏览页面后出现了极大的流失率,这个根据前面的视图可以看出每个用户的用户行为总数中,浏览次数占有非常高的比例。
-- 计算每个行为的用户数量
SELECT
sum(case when Pv>0 then 1 else 0 end) '浏览用户数',
sum(case when Fav>0 then 1 else 0 end) '收藏用户数',
sum(case when Car>0 then 1 else 0 end) '架构用户数',
sum(case when Buy>0 then 1 else 0 end) '下单用户数'
from user_behavior
![4f60f4f387054fade0aa5ec479c76cd7.png](https://i-blog.csdnimg.cn/blog_migrate/37b3a47d772c566be08355c00ca249c4.png)
从这里看出,在产生浏览行为的用户转化成下单的用户的比例是51.10%,说明该平台的转化率效果还算比较理想。
2.3 用户行为指标分析
2.3.1 整月用户行为分析
-- 按照时间排列用户行为
SELECT Date_time,
sum(case when behavior_type='1' then 1 else 0 end ) as 'Pv',
sum(case when behavior_type='2' then 1 else 0 end) as 'Fav',
sum(case when behavior_type='3' then 1 else 0 end) as 'Car',
sum(case when behavior_type='4'then 1 else 0 end ) as 'Buy'
from train_user
group by Date_time
order by Date_time asc
![507068d4c5103cfe2f6303463e77dc82.png](https://i-blog.csdnimg.cn/blog_migrate/54d5cb724a11535ea576addf30b08ab0.png)
2.3.2 24H用户行为分析
SELECT HOUR_time,
sum(case when behavior_type='1' then 1 else 0 end ) as 'Pv',
sum(case when behavior_type='2' then 1 else 0 end) as 'Fav',
sum(case when behavior_type='3' then 1 else 0 end) as 'Car',
sum(case when behavior_type='4'then 1 else 0 end ) as 'Buy'
from train_user
group by HOUR_time
order by HOUR_time asc
![d176bcca9599ce6903b19de81ba73fb8.png](https://i-blog.csdnimg.cn/blog_migrate/bb32b0d0d1f7d6cf6b2e236c8adba83a.png)
2.3.3 用户复购率分析
SELECT
sum(case when Buy>1 then 1 else 0 end)'复购次数',
sum(case when Buy>0 then 1 else 0 end)'购买次数',
(sum(case when Buy>1 then 1 else 0 end)/sum(case when Buy>0 then 1 else 0 end))'复购率'
from user_behavior
![8594f31b6d85306280fa838e0173244f.png](https://i-blog.csdnimg.cn/blog_migrate/c79776e85ec1c7124fd52d647c2f7613.png)
2.3.4 用户跳失率分析
(select count(*) from (
SELECT user_id
from train_user
GROUP BY user_id
having count(behavior_type=1))a)
![6b18bc011c0eb8037e1ba188f90bf80e.png](https://i-blog.csdnimg.cn/blog_migrate/912dda0616e9079cef7bb3f5df6fbfb5.png)
说明平台用户忠诚度非常高,复购率在53%,多数都有二次购买动作;跳失率非常低,基本所有用户浏览界面后都有下一步行动。
2.4 产品销量指标
-- 使用窗口函数,对下单产品进行排名
select item_id '产品名称',COUNT(item_id)'销量',(rank()over(order by COUNT(item_id) desc))'销量排名'
from train_user
where behavior_type=4
group by item_id
![3f0efd1585b851e9c2e2c554e3130865.png](https://i-blog.csdnimg.cn/blog_migrate/a8fd6a7238e8e1012b40b444523abb30.png)
三. RFM模型分析
-- 创建R视图,存放用户最近购买时间和统计日期'2014-12-18'的差值
create view R
as
select user_id ,DATEDIFF('2014-12-18',max(Date_time)) '近期购买时间'
from train_user
where behavior_type='4'
group by user_id
-- 对购买日期进行分组,每7天拆分一组
create view R指标
as
select user_id,
(case when 近期购买时间 <=7 then 5
when 近期购买时间 BETWEEN 8 and 14 then 4
when 近期购买时间 BETWEEN 15 and 21 then 3
when 近期购买时间 BETWEEN 22 and 28 then 2
when 近期购买时间 BETWEEN 29 and 31 then 1 else null end ) 'R指标'
from R
![9f6584184fa5dea36bc8d03e4af7d32d.png](https://i-blog.csdnimg.cn/blog_migrate/5294c6bab2c7a759bbc5e82233f98845.png)
--创建F视图,存放每个用户的购买次数
CREATE view F
as
SELECT user_id,count(behavior_type)'F'
from train_user
where behavior_type=4
group by user_id
order by F desc
-- 对购买次数进行分组
create view F指标
as
select user_id,
(case when F <=5 then 1
when F BETWEEN 6 and 10 then 2
when F BETWEEN 11 and 15 then 3
when F BETWEEN 16 and 20 then 4
when F >= 21 then 5 else null end ) 'F指标'
from F
![b05568182c7d95280483e0b73d71988b.png](https://i-blog.csdnimg.cn/blog_migrate/d372d9de7ace355e2906921b8e05199d.png)
-- 计算R,F两个指标的平均值
SELECT avg(R指标) as 'R平均值' FROM r指标; --4.0028
SELECT avg(F指标) as 'F平均值' FROM F指标; --1.0813
-- 根据两者平均值,将用户RF模型分层
create view RFM模型
as
select a.user_id,a.R指标,b.F指标,
(case
when a.R指标<4.0028 and b.F指标>1.0813 then '高价值客户'
when a.R指标>4.0028 and b.F指标>1.0813 then '唤回客户'
when a.R指标>4.0028 and b.F指标<1.0813 then '深耕客户'
when a.R指标<4.0028 and b.F指标<1.0813 then '挽留客户'
END
) as 用户分层
from `r指标` a, `f指标` b
where a.user_id=b.user_id
-- 计算每个分组的用户数量
SELECT 用户分层,count(user_id)
from `rfm模型`
group by 用户分层
![169d53d0be969c97e7ca78d63263de76.png](https://i-blog.csdnimg.cn/blog_migrate/65a2807a8629b2a3ad3b28dfde0f78c5.png)
可以根据用户分组,对目标人群进行精准化的营销。