if mysql sum 视图_电商用户行为分析-Mysql+Excel

一. 数据处理：

1.1 数据源导入

使用Navicat 将数据源（csv）导入至Mysql中，建立新的Table(train_user）。

数据集内容

1.2 数据理解：

数据字段含义

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

数据全为null,表示无重复数据

1.3.2 缺失值处理

这里user_geohash 位置信息，是加密处理的，所以后续不做详细分析

--计算各个值
select count(item_category),count(user_id),count(item_id),count(behavior_type),count(time)
from train_user1

数据集全为1048575条，无异常数据值

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))

格式转化完成

1.3.4 异常值处理

select SELECT max(Date_time),MIN(Date_time) from train_user

数据时间范围满足，无异常值

二. 指标分析

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

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

每日用户行为分析

双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

2.2.2 转化率分析

SELECT sum(pv),sum(Fav),sum(Car),sum(Buy)
from user_behavior

最后成交的数量转化率在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

从这里看出，在产生浏览行为的用户转化成下单的用户的比例是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

双12当天，数据指标都有大幅提升

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

在19-22点期间内，用户浏览及下单率达到顶峰

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

2.3.4 用户跳失率分析

(select count(*) from (
SELECT user_id
from train_user
GROUP BY user_id
having count(behavior_type=1))a)

跳失率=8477/1048575=)%

说明平台用户忠诚度非常高，复购率在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

产品销量排名

三. 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

R指标视图

--创建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

F指标视图

-- 计算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 用户分层

可以根据用户分组，对目标人群进行精准化的营销。