SQL计算电商各项指标数据

本次笔记主要是记录通过SQL计算电商各项指标数据,包括AARRR部分指标、RFM模型等常用的指标数据；

平台指标：

  1. PV、UV、浏览深度（PV/UV）；
  2. 跳失率；
  3. 购买转化率；

商品指标：

  1. 商品购买转化率；
  2. 商品品类购买转化率；

用户行为指标：

  1. 每时段的浏览量；
  2. 用户复购情况；
  3. 用户购买路径；

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

（完结）

最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 195,898评论 5赞 462
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 82,401评论 2赞 373
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 143,058评论 0赞 325
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 52,539评论 1赞 267
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 61,382评论 5赞 358
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 46,319评论 1赞 273
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 36,706评论 3赞 386
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 35,370评论 0赞 254
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 39,664评论 1赞 294
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 34,715评论 2赞 312
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 36,476评论 1赞 326
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 32,326评论 3赞 313
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 37,730评论 3赞 299
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,003评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 30,275评论 1赞 251
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 41,683评论 2赞 342
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 40,877评论 2赞 335