SQL計(jì)算電商各項(xiàng)指標(biāo)數(shù)據(jù)

本次筆記主要是記錄通過SQL計(jì)算電商各項(xiàng)指標(biāo)數(shù)據(jù),包括AARRR部分指標(biāo)、RFM模型等常用的指標(biāo)數(shù)據(jù)；

平臺(tái)指標(biāo)：

  1. PV、UV、瀏覽深度（PV/UV）；
  2. 跳失率；
  3. 購(gòu)買轉(zhuǎn)化率；

商品指標(biāo)：

  1. 商品購(gòu)買轉(zhuǎn)化率；
  2. 商品品類購(gòu)買轉(zhuǎn)化率；

用戶行為指標(biāo)：

  1. 每時(shí)段的瀏覽量；
  2. 用戶復(fù)購(gòu)情況；
  3. 用戶購(gòu)買路徑；

RFM模型：見之前的文章《SQL建立RFM模型指標(biāo)的兩種方法對(duì)比》

用戶留存率：見之前文章《SQL 查詢用戶留存率（根據(jù)兩種不同定義計(jì)算）》

數(shù)據(jù)清洗

首先我們導(dǎo)入相關(guān)數(shù)據(jù)，并去重?cái)?shù)據(jù)放進(jìn)新表 temp_trade；

由于時(shí)間關(guān)系，以導(dǎo)入如下數(shù)據(jù)，期間利用
SET date_time = STR_TO_DATE(time,'%Y-%m-%d %H');
set dates=date(date_time);
這兩個(gè)函數(shù)對(duì)原表（紅框）日期進(jìn)行處理；


image.png
create table o_retailers_trade_user
(
user_id int (9),
item_id int (9), -- 商品id
behavior_type int (1), -- 用戶行為類型(1-曝光;2-購(gòu)買;3-加入購(gòu)物?;4-加入收藏夾。)
user_geohash varchar (14), 
item_category int (5),  -- 品類ID
time varchar (13) -- 用戶發(fā)生行為的時(shí)間
);

-- 日期時(shí)間數(shù)據(jù)處理 增加新列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;

再檢查一下關(guān)鍵字段有無缺失值

SELECT COUNT(user_id)  ,COUNT(item_id) ,COUNT(item_category) ,COUNT(behavior_type)  ,COUNT(time) 
FROM  o_retailers_trade_user;

查詢后得出并無缺失。

image

查詢結(jié)果無異常值；

檢查用戶行為數(shù)據(jù)有沒有其他類型；

SELECT behavior_type FROM temp_trade

WHERE behavior_type NOT IN (1,2,3,4);

查詢結(jié)果無異常值；

-- 建新表，放進(jìn) 去重后的 數(shù)據(jù)

create table temp_trade like o_retailers_trade_user;
insert into temp_trade select distinct * from o_retailers_trade_user;

平臺(tái)指標(biāo)

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;

查詢結(jié)果：


image.png

2. 跳失率

這里定義跳失率=只有瀏覽行為的用戶數(shù)/總用戶數(shù)

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

查詢結(jié)果


image.png

3. 購(gòu)買轉(zhuǎn)化率

這里的購(gòu)買轉(zhuǎn)化率定義為：某段時(shí)間產(chǎn)生購(gòu)買行為的用戶數(shù)/所有到達(dá)店鋪的訪客人數(shù)

SELECT 
dates,
COUNT(DISTINCT user_id) AS 訪客人數(shù),
COUNT(DISTINCT(if(behavior_type=2,user_id,null))) AS 產(chǎn)生購(gòu)買用戶數(shù),
CONCAT(TRUNCATE(COUNT(DISTINCT(if(behavior_type=2,user_id,null)))/COUNT(DISTINCT user_id) * 100,2) ,"%") AS 轉(zhuǎn)化率
FROM
temp_trade
GROUP BY
dates;

查詢結(jié)果：


image.png

同時(shí)可以通過這算法求得：每天總行為次數(shù)、每天點(diǎn)擊次數(shù)、收藏次數(shù)、加購(gòu)物?次數(shù)、購(gòu)買次數(shù)

SELECT
    dates,
    count( 1 ) AS '每日的總數(shù)',
    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;

查詢結(jié)果如下：


image.png

商品指標(biāo)

1. 商品購(gòu)買轉(zhuǎn)化率

由于轉(zhuǎn)化率的對(duì)象是商品，所以以商品id做分組，求對(duì)應(yīng)的用戶行為數(shù)求和?？傻贸雒可唐返脑谠摱螘r(shí)間內(nèi)的瀏覽、收藏、加購(gòu)、購(gòu)買次數(shù)，同時(shí)可求得商品購(gòu)買轉(zhuǎn)化率。

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;

查詢結(jié)果：


image.png

2. 商品品類購(gòu)買轉(zhuǎn)化率

此方法與上面商品轉(zhuǎn)化率大致相同，分組對(duì)象改為品類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;

查詢結(jié)果：


image.png

用戶行為指標(biāo)

1. 每時(shí)段的瀏覽量及訪客量

首先創(chuàng)建hours的新字段，并提取時(shí)間；

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

查詢結(jié)果如下：


image.png

2. 用戶復(fù)購(gòu)情況；

首先求出各用戶的購(gòu)買次數(shù)

SELECT user_id, COUNT(user_id)AS 購(gòu)買次數(shù)
FROM  temp_trade WHERE behavior_type=2 GROUP BY user_id ORDER BY 購(gòu)買次數(shù) DESC

查詢結(jié)果：


image.png

用case when函數(shù)統(tǒng)計(jì)出各復(fù)購(gòu)買次數(shù)的用戶數(shù)

SELECT
(CASE WHEN 購(gòu)買次數(shù) =1   THEN '1次' 
        WHEN 購(gòu)買次數(shù) BETWEEN 2 AND 5 THEN '2-5次' 
        WHEN 購(gòu)買次數(shù) BETWEEN 6 AND 10 THEN '6-10次' 
        WHEN 購(gòu)買次數(shù) BETWEEN 11 AND 15 THEN '11-15次' 
        WHEN 購(gòu)買次數(shù) BETWEEN 16 AND 20 THEN '16-20次' 
        WHEN 購(gòu)買次數(shù) BETWEEN 21 AND 25 THEN '21-25次' 
        WHEN 購(gòu)買次數(shù) BETWEEN 26 AND 30 THEN '26-30次' 
    ELSE 0 END )AS 復(fù)購(gòu)買次數(shù) , COUNT(user_id) AS 用戶數(shù)
FROM
(
SELECT user_id, COUNT(user_id)AS 購(gòu)買次數(shù)
FROM  temp_trade WHERE behavior_type=2 GROUP BY user_id
)a
GROUP BY 復(fù)購(gòu)買次數(shù)

查詢結(jié)果如下：


image.png
  1. 用戶購(gòu)買路徑；
    利用偏移函數(shù)倒序列出用戶購(gòu)買一產(chǎn)品的行為路徑，并對(duì)其進(jìn)行排序，并篩選倒序時(shí)第一行的行為為購(gòu)買。并創(chuàng)建窗口函數(shù)，方便后面的調(diào)用。
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

最后將行為串聯(lián)起來，并對(duì)其進(jìn)行用戶數(shù)統(tǒng)計(jì)；

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 -- 該路徑下購(gòu)買用戶數(shù) from
FROM 
 product_user_way
group by
  concat(
    ifnull( lag_4,'空' ),
    "-",
    ifnull( lag_3,'空' ),
    "-",
    ifnull( lag_2,'空' ),
    "-",
    ifnull( lag_1,'空' ),
    "-",
  behavior_type
  );

查詢結(jié)果如下：


image.png

（完結(jié)）

最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請(qǐng)結(jié)合常識(shí)與多方信息審慎甄別。
平臺(tái)聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡(jiǎn)書系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。

友情鏈接更多精彩內(nèi)容