发表评论

A+

所属分类：SQL技巧

SQL计算电商各项指标数据！

作者：ppcc09
链接：https://www.jianshu.com/p/e41f3e11dd8e

本次笔记主要是记录通过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);
这两个函数对原表（红框）日期进行处理；

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;

查询后得出并无缺失。

查询结果无异常值；

检查用户行为数据有没有其他类型；

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;

查询结果：

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

查询结果

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;

查询结果：

同时可以通过这算法求得：每天总行为次数、每天点击次数、收藏次数、加购物⻋次数、购买次数

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;

查询结果如下：

商品指标

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;

查询结果：

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;

查询结果：

用户行为指标

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

查询结果如下：

2. 用户复购情况；

首先求出各用户的购买次数

SELECT user_id, COUNT(user_id)AS 购买次数
FROM  temp_trade WHERE behavior_type=2 GROUP BY user_id ORDER BY 购买次数 DESC

查询结果：

用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 复购买次数

查询结果如下：

用户购买路径；
利用偏移函数倒序列出用户购买一产品的行为路径，并对其进行排序，并筛选倒序时第一行的行为为购买。并创建窗口函数，方便后面的调用。

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