以下内容是本人作为一个小白参加的拉勾数据分析训练营期间所写的一些笔记，以及把学到的内容作了个小延伸，其实这个课程内容还是挺丰富的，技术内容讲得也比较细，每章节都会有些贴近工作实战的案例作为作业，经过几次的sql作业，慢慢也找到了感觉，有时候学习进度较快时候，老师还会加餐作业，曾经加了一个数据量达1400万行的数据分析作业，光导数据就要花6小时，从这种难题中实战起来，并经过老师思路指点，会走少很多歪路，效率会更高，还是挺不错的。至于导师方面日常线上的回答还是比较及时，也有定期的问题汇总给到大家，让大家相互知道大家都有些什么问题疑惑。
课程内容还会不断迭代更新，主要是案例的更新，这就能学到后面也能同时回顾前面所学内容，并学到新的案例解析。
本次笔记主要是记录通过SQL计算电商各项指标数据,包括AARRR部分指标、RFM模型等常用的指标数据；
平台指标：
- PV、UV、浏览深度（PV/UV）；
- 跳失率；
- 购买转化率；
商品指标：
- 商品购买转化率；
- 商品品类购买转化率；
用户行为指标：
- 每时段的浏览量；
- 用户复购情况；
- 用户购买路径；
RFM模型：见之前的文章《SQL建立RFM模型指标的两种方法对比》
用户留存率：见之前文章《SQL 查询用户留存率（根据两种不同定义计算）》
数据清洗
首先我们导入相关数据，并去重数据放进新表 temp_trade；
由于时间关系，以导入如下数据，期间利用
SET date_time = STR_TO_DATE(time,'%Y-%m-%d %H');
set dates=date(date_time);
这两个函数对原表（红框）日期进行处理；
image.png
create table o_retailers_trade_user
(
user_id int (9),
item_id int (9), -- 商品id
behavior_type int (1), -- 用户行为类型(1-曝光;2-购买;3-加入购物⻋;4-加入收藏夹。)
user_geohash varchar (14),
item_category int (5), -- 品类ID
time varchar (13) -- 用户发生行为的时间
);
-- 日期时间数据处理 增加新列date_time、dates
ALTER TABLE o_retailers_trade_user
ADD COLUMN date_time datetime null;
UPDATE o_retailers_trade_user
SET date_time = STR_TO_DATE(time,'%Y-%m-%d %H');
alter table o_retailers_trade_user add column dates char(10) null;
update o_retailers_trade_user
set dates=date(date_time);
desc o_retailers_trade_user;
再检查一下关键字段有无缺失值
SELECT COUNT(user_id) ,COUNT(item_id) ,COUNT(item_category) ,COUNT(behavior_type) ,COUNT(time)
FROM o_retailers_trade_user;
查询后得出并无缺失。
image
查询结果无异常值；
检查用户行为数据有没有其他类型；
SELECT behavior_type FROM temp_trade
WHERE behavior_type NOT IN (1,2,3,4);
查询结果无异常值；
-- 建新表，放进 去重后的 数据
create table temp_trade like o_retailers_trade_user;
insert into temp_trade select distinct * from o_retailers_trade_user;
平台指标
1. PV、UV、浏览深度（PV/UV）
SELECT
dates,
COUNT(DISTINCT user_id) AS uv,
COUNT(if(behavior_type=1,user_id,null)) AS pv,
CAST(COUNT(if(behavior_type=1,user_id,null))/COUNT(DISTINCT user_id) as decimal(10,2)) AS 'pv/uv'
FROM
temp_trade
GROUP BY
dates;
查询结果：
image.png
2. 跳失率
这里定义跳失率=只有浏览行为的用户数/总用户数
SELECT
CONCAT(TRUNCATE(SUM(IF(a.buy_num=0 AND a.car_num=0 AND a.fav_num=0 , 1, 0))/COUNT(a.user_id) * 100,2) ,"%") AS 跳失率
FROM
(
SELECT
user_id,
SUM(IF(behavior_type=2,1,0)) AS buy_num,
SUM(IF(behavior_type=3,1,0)) AS car_num,
SUM(IF(behavior_type=4,1,0)) AS fav_num
FROM
temp_trade
GROUP BY user_id
)a
查询结果
image.png
3. 购买转化率
这里的购买转化率定义为：某段时间产生购买行为的用户数/所有到达店铺的访客人数
SELECT
dates,
COUNT(DISTINCT user_id) AS 访客人数,
COUNT(DISTINCT(if(behavior_type=2,user_id,null))) AS 产生购买用户数,
CONCAT(TRUNCATE(COUNT(DISTINCT(if(behavior_type=2,user_id,null)))/COUNT(DISTINCT user_id) * 100,2) ,"%") AS 转化率
FROM
temp_trade
GROUP BY
dates;
查询结果：
image.png
同时可以通过这算法求得：每天总行为次数、每天点击次数、收藏次数、加购物⻋次数、购买次数
SELECT
dates,
count( 1 ) AS '每日的总数',
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 'buy',
sum( CASE WHEN behavior_type = 3 THEN 1 ELSE 0 END ) AS 'cart',
sum( CASE WHEN behavior_type = 4 THEN 1 ELSE 0 END ) AS 'fav',
count( DISTINCT CASE WHEN behavior_type = 2 THEN user_id ELSE NULL END ) / count( DISTINCT user_id ) AS buy_rate
FROM
temp_trade
GROUP BY
dates;
查询结果如下：
image.png
商品指标
1. 商品购买转化率
由于转化率的对象是商品，所以以商品id做分组，求对应的用户行为数求和。可得出每商品的在该段时间内的浏览、收藏、加购、购买次数，同时可求得商品购买转化率。
select item_id,
sum(case when behavior_type=1 then 1 else 0 end) as'pv',
sum(case when behavior_type=4 then 1 else 0 end) as'fav',
sum(case when behavior_type=3 then 1 else 0 end) as'cart',
sum(case when behavior_type=2 then 1 else 0 end) as'buy',
count(distinct case when behavior_type=2 then user_id else null
end)/count(distinct user_id) as buy_rate
from temp_trade
group by item_id
order by buy desc;
查询结果：
image.png
2. 商品品类购买转化率
此方法与上面商品转化率大致相同，分组对象改为品类id即可。
select item_category,
sum(case when behavior_type=1 then 1 else 0 end) as'pv',
sum(case when behavior_type=4 then 1 else 0 end) as'fav',
sum(case when behavior_type=3 then 1 else 0 end) as'cart',
sum(case when behavior_type=2 then 1 else 0 end) as'buy',
count(distinct case when behavior_type=2 then user_id else null
end)/count(distinct user_id) as buy_rate
from temp_trade
group by item_category
order by buy desc;
查询结果：
image.png
用户行为指标
1. 每时段的浏览量及访客量
首先创建hours的新字段，并提取时间；
ALTER TABLE temp_trade ADD COLUMN hours int NOT NULL;
UPDATE temp_trade SET hours = hour(TIME(date_time));
select
hours,
COUNT(DISTINCT user_id) AS 'uv' ,
sum( CASE WHEN behavior_type = 1 THEN 1 ELSE 0 END ) AS 'pv'
from temp_trade
group by hours
ORDER BY hours
查询结果如下：
image.png
2. 用户复购情况；
首先求出各用户的购买次数
SELECT user_id, COUNT(user_id)AS 购买次数
FROM temp_trade WHERE behavior_type=2 GROUP BY user_id ORDER BY 购买次数 DESC
查询结果：
image.png
用case when函数统计出各复购买次数的用户数
SELECT
(CASE WHEN 购买次数 =1 THEN '1次'
WHEN 购买次数 BETWEEN 2 AND 5 THEN '2-5次'
WHEN 购买次数 BETWEEN 6 AND 10 THEN '6-10次'
WHEN 购买次数 BETWEEN 11 AND 15 THEN '11-15次'
WHEN 购买次数 BETWEEN 16 AND 20 THEN '16-20次'
WHEN 购买次数 BETWEEN 21 AND 25 THEN '21-25次'
WHEN 购买次数 BETWEEN 26 AND 30 THEN '26-30次'
ELSE 0 END )AS 复购买次数 , COUNT(user_id) AS 用户数
FROM
(
SELECT user_id, COUNT(user_id)AS 购买次数
FROM temp_trade WHERE behavior_type=2 GROUP BY user_id
)a
GROUP BY 复购买次数
查询结果如下：
image.png
- 用户购买路径；
利用偏移函数倒序列出用户购买一产品的行为路径，并对其进行排序，并筛选倒序时第一行的行为为购买。并创建窗口函数，方便后面的调用。
create view product_user_way as
SELECT
a.*
FROM
(select
user_id,
item_id,
lag ( behavior_type, 4 ) over ( partition by user_id, item_id order by
date_time ) lag_4,
lag ( behavior_type, 3 ) over ( partition by user_id, item_id order by
date_time ) lag_3,
lag ( behavior_type, 2 ) over ( partition by user_id, item_id order by
date_time ) lag_2,
lag ( behavior_type, 1 ) over ( partition by user_id, item_id order by
date_time ) lag_1,
behavior_type,
rank ( ) over ( partition by user_id, item_id order by date_time desc ) as rank_dn #
from
temp_trade
)a
WHERE a.rank_dn=1 AND behavior_type=2
最后将行为串联起来，并对其进行用户数统计；
select
concat(
ifnull( lag_4,'空' ),
"-",
ifnull( lag_3,'空' ),
"-",
ifnull( lag_2,'空' ),
"-",
ifnull( lag_1,'空' ),
"-",
behavior_type
) as user_way,
count( distinct user_id ) as user_count -- 该路径下购买用户数 from
FROM
product_user_way
group by
concat(
ifnull( lag_4,'空' ),
"-",
ifnull( lag_3,'空' ),
"-",
ifnull( lag_2,'空' ),
"-",
ifnull( lag_1,'空' ),
"-",
behavior_type
);
查询结果如下：
image.png
（完结）
网友评论