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

admin • 2022年6月6日 21:08 • 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
  );

查询结果如下：

（完结）

学习资料见知识星球。

以上就是今天要分享的技巧，你学会了吗？若有什么问题，欢迎在下方留言。

快来试试吧，小琥 my21ke007。获取 1000个免费 Excel模板福利！

更多技巧， www.excelbook.cn

欢迎加入 零售创新 知识星球，知识星球主要以数据分析、报告分享、数据工具讨论为主；

你将获得：

1、价值上万元的专业的PPT报告模板。

2、专业案例分析和解读笔记。

3、实用的Excel、Word、PPT技巧。

4、VIP讨论群，共享资源。

5、优惠的会员商品。

6、一次付费只需99元，即可下载本站文章涉及的文件和软件。

文章版权声明 1、本网站名称：Excelbook
2、本站永久网址：http://www.excelbook.cn
3、本网站的文章部分内容可能来源于网络，仅供大家学习与参考，如有侵权，请联系站长王小琥进行删除处理。
4、本站一切资源不代表本站立场，并不代表本站赞同其观点和对其真实性负责。
5、本站一律禁止以任何方式发布或转载任何违法的相关信息，访客发现请向站长举报。
6、本站资源大多存储在云盘，如发现链接失效，请联系我们我们会第一时间更新。

THE END

二维码

Excel技巧，在Excel中利用MEDIAN函数批量修订超过最大值和最小值的数据！

< <上一篇

Excel技巧，Excel表格中如何快速输入特殊符号√对号有哪些最快捷的方式！

下一篇>>

搜索内容

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

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

数据清洗

平台指标

1. PV、UV、浏览深度（PV/UV）

2. 跳失率

3. 购买转化率

商品指标

1. 商品购买转化率

2. 商品品类购买转化率

用户行为指标

1. 每时段的浏览量及访客量

2. 用户复购情况；

取消回复

共有 0 条评论