MySQL-实例

兔了

I'm the King. U R my Queen.

案例数据.xlsx

·
百度网盘

本案例对电商平台的用户行为数据使用SQL做一些分析，通过“人、货、场”拆解方式建立指标体系，也就是通过用户、商品、平台三个方向来具体分析。

1.涉及指标介绍：

PV，Page View，是页面的浏览量。
UV，Unique Visitor，独立访客数，是一定时间内访问网页的人数
PV/UV，浏览深度，流量质量，平均一个访客浏览多少次

2.本案例想要确认的问题：

1）基于漏⽃模型的⽤户购买流程各环节分析指标，确定各个环节的转换率，便于找到需要改进的环节；
2）商品分析：找出热销商品，研究热销商品特点；
3）基于RFM模型找出核⼼付费⽤户群，对这部分⽤户进⾏精准营销。

3.数据准备：

-- 建表并导入数据（案例数据在附件中）
CREATE TABLE o_retailers_trade_user (
	user_id INT ( 9 ),-- 用户ID
	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 ) -- 用户行为发生的时间
-- 查看一下数据
SELECT * FROM o_retailers_trade_user LIMIT 5;

这是我们的原始数据，需要注意的是：数据的时间格式为2019-12-06 02 年-⽉-⽇⼩时（24进制），这种格式不方便我们用于分析，所以我们需要给表添加两个新的列。

-- 数据预处理，增加新列date_time（datetime）,dates（char，年月日），便于后续时间维度分析
-- 增加date_time列
ALTER TABLE o_retailers_trade_user ADD COLUMN date_time datetime NULL;-- 默认为空
-- date_time字段来自于基础数据中的time字段，str_to_date字符串转日期，%H代表24进制，%h代表12进制
UPDATE o_retailers_trade_user SET date_time=str_to_date(time,'%Y-%m-%d %H');
-- 增加dates列
ALTER TABLE o_retailers_trade_user ADD COLUMN dates CHAR(10) NULL;
-- dates字段来自于date_time字段
UPDATE o_retailers_trade_user SET dates=DATE(date_time);

因为数据里会有一些重复的数据，接下来做一下去重，并存到一个新表中

-- 创建新的临时表，因为结构相同可以直接复制源表结构
CREATE TABLE temp_trade LIKE o_retailers_trade_user;
-- 用distinct 取o表数据（插入去重后的数据）
INSERT INTO temp_trade SELECT DISTINCT * FROM o_retailers_trade_user;

5.指标体系建设

根据人、货、场的拆解方式，我们分别从用户、商品、平台来建设指标体系。

5.1 用户指标体系

对于用户指标我们再分为两个维度来看，一个是基础的指标体系，比如UV/PV/留存率等指标，另一个就是RFM模型分析。

5.1.1 基础指标

基础指标就是按照日来看每天的浏览量（PV），访客数（UV），流量质量|浏览深度（PV/UV），还有留存率

-- PV：Page View 页面浏览 - 统计behavior_type=1的记录数，按日统计（分组）
-- UV：unique visitor 独立访客数 - 统计distinct user_id的数量，按日统计（分组）
-- 流量质量(浏览深度) - pv/uv
SELECT
	dates,
        COUNT(IF(behavior_type=1,user_id,NULL)) 'pv',-- 对pv进行count的时候，如果behavior_type=1进行计算，否则不进行计算
	COUNT( DISTINCT user_id ) 'uv',
	COUNT(IF(behavior_type=1,user_id,NULL))




    
/COUNT( DISTINCT user_id ) 'pv/uv'
	temp_trade 
GROUP BY
	dates;

运行结果示例：

接下来计算留存率，即按日统计活跃用户的留存率。

留存：基准日活跃的用户在之后的某一天活跃的人数，比如一个在基准日活跃的用户，在第二天仍然活跃，那我们就把他算在第二天的留存数里。

留存率：留存/基准日当天的活跃数*100%

所以我们要得到每一个基准日当天的活跃数，第二天的留存数，第三天的留存数。。等，那就要找到基准日以及它之后的数据，基准日之后的某一天如果有数据，那么那一天他就是活跃的。这两个日期相减为几，就是几日后的留存。

-- 因为只涉及用户和活跃日期，所以将两个字段取出
SELECT user_id,dates FROM temp_trade GROUP BY user_id,dates;
-- 上边语句查出的就是每个用户以及他的活跃日期
-- 将上表做自关联，留下右表时间大于左表的记录，然后按照左表日期分组，分别取出针对左表每个日期的活跃用户数（记得去重），每日留存（留存也用count，日期间隔为几，就计算到几日留存里）
-- 存为视图
CREATE VIEW user_remain_view AS(
SELECT
	a.dates,
	COUNT( DISTINCT b.user_id ) AS user_count,
	COUNT(DISTINCT IF(DATEDIFF(b.dates,a.dates)=1,b.user_id,NULL)) AS remain1,-- 1日留存数计算
	COUNT(DISTINCT IF(DATEDIFF(b.dates,a.dates)=2,b.user_id,NULL)) AS remain2,-- 2日留存数计算
	COUNT(DISTINCT IF(DATEDIFF(b.dates,a.dates)=3,b.user_id,NULL))AS remain3,-- 3日留存数计算
	COUNT(DISTINCT IF(DATEDIFF(b.dates,a.dates)=4,b.user_id,NULL))AS remain4,-- 4日留存数计算
	COUNT(DISTINCT IF(DATEDIFF(b.dates,a.dates)=5,b.user_id,NULL))AS remain5,-- 5日留存数计算
	COUNT(DISTINCT IF(DATEDIFF(b.dates,a.dates)=6,b.user_id,NULL))AS remain6,-- 6日留存数计算
	COUNT(DISTINCT IF(DATEDIFF(b.dates,a.dates)=7,b.user_id,NULL))AS remain7,-- 7日留存数计算
	COUNT(DISTINCT IF(DATEDIFF(b.dates,a.dates)=15,b.user_id,NULL))AS remain15,-- 15日留存数计算
	COUNT(DISTINCT IF(DATEDIFF(b.dates,a.dates)=30,b.user_id,NULL))AS remain30-- 30日留存数计算
	( SELECT user_id, dates FROM temp_trade GROUP BY user_id, dates ) a
	LEFT JOIN ( SELECT user_id, dates FROM temp_trade GROUP BY user_id, dates ) b ON a.user_id = b.user_id 
WHERE
	b.dates > a.dates 
GROUP BY
	a.dates);

视图结果如下，分别求出了对应每个基准日的活跃用户数，以及某日的留存数。

-- 基于视图查询留存率
SELECT
	dates,
	user_count,
	-- 1日留存率， cast 转换函数，类型 decimal(长度，小数位)，保留两位小数然后通过连接函数展示为百分数的形式
	concat(cast((remain1 / user_count)*100 AS DECIMAL(10,2)),'%')  AS 'day_1',
	concat(cast((remain2 / user_count)*100 AS DECIMAL(10,2)),'%')  AS 'day_2',
	concat(cast((remain3 / user_count)*100 AS DECIMAL(10,2)),'%')  AS 'day_3',
	concat(cast((remain4 / user_count)*100 AS DECIMAL(10,2)),'%')  AS 'day_4',
	concat(cast((remain5 / user_count)*100 AS DECIMAL(10,2)),'%')  AS 'day_5',
	concat(cast((remain6 / user_count)*100 AS DECIMAL(10,2)),'%')  AS 'day_6',
	concat(cast((remain7 / user_count)*100 AS DECIMAL(10,2)),'%')  AS 'day_7',
	concat(cast((remain15 / user_count)*100 AS DECIMAL(10,2)),'%')  AS 'day_15',
	concat(cast((remain30 / user_count)*100 AS DECIMAL(10,2)),'%')  AS 'day_30'
	user_remain_view;

5.1.2 RFM模型分析

本案例不涉及M，我们只看R和F，所以将用户划分为4中：

重要高价值客户：R近F高
需要唤回客户：R远F高
重要深耕客户：R近F低
重要挽留客户：R远F低

R和F的远近高低用各自平均值划分判断。

1）R指标等级划分

-- R指标分析：根绝每个用户的最近购买时间，给出相应分数
-- 获取每个用户的最近购买时间，创建视图
DROP VIEW IF EXISTS user_recency_view;
CREATE VIEW user_recency_view AS
SELECT
	user_id,
	MAX( dates ) recent_buy_time
	temp_trade 
WHERE
	behavior_type = 2 -- 购买行为
GROUP BY
	user_id;
-- 计算每个用户的最近购买时间距离2019-12-18的天数，根据相差天数给予一定的分数,建立视图
CREATE VIEW r_level AS
SELECT
	user_id,
	recent_buy_time,
	DATEDIFF( '2019-12-18', recent_buy_time ), 
	-- <=2 5分，<=4 4分， <=6 3分， <=8 2分 其他1分
	(case
	WHEN DATEDIFF( '2019-12-18', recent_buy_time )<=2 THEN 5
	WHEN DATEDIFF( '2019-12-18', recent_buy_time )<=4 THEN 4
	WHEN DATEDIFF( '2019-12-18', recent_buy_time )<=6 THEN 3
	WHEN DATEDIFF( '2019-12-18', recent_buy_time )<=8 THEN 2
	ELSE 1 END
	) AS r_value-- 判断每个人的r分值
	user_recency_view;

2）F指标等级划分

-- F指标计算：求出每个用户的消费次数（购买），对消费情况评分
-- 求每个用户消费次数，创建视图
CREATE VIEW user_buy_fre_view AS
SELECT
	user_id,
	count( user_id ) buy_frequency -- 各个用户购买次数
	temp_trade 
WHERE
	behavior_type = 2 
GROUP BY
	user_id;
	-- 评分，购买次数<=2 1分，<=4 2分，<=6 3分，<=8 4分，其他 5分,建立视图
CREATE VIEW f_level AS
SELECT
	user_id,
	buy_frequency,
	(CASE 
	WHEN buy_frequency<=2 THEN 1
	WHEN buy_frequency<=4 THEN 2
	WHEN buy_frequency<=6 THEN 3
	WHEN buy_frequency<=8 THEN 4
	ELSE 5 END	
	) AS f_value
	user_buy_fre_view;

然后我们计算出R指标和F指标的均值：

-- r均值
SELECT AVG(r_value) AS r_avg FROM r_level;-- 2.7939
-- f均值
SELECT AVG(f_value) AS f_avg FROM f_level;-- 2.2606

3）整合

按照R和F指标的均值来定高低界限。按照之前的分类来划分用户。

-- 两表关联r_level,f_level），与均值对比
SELECT r.user_id,
r.r_value,
f.f_value,
(CASE
WHEN r.r_value>2.7939 AND f.f_value>2.2606 THEN '重要高价值客户'
WHEN r.r_value<2.7939 AND f.f_value>2.2606 THEN '需要唤回客户'
WHEN r.r_value>2.7939 AND f.f_value<2.2606 THEN '重要深耕客户'
WHEN r.r_value<2.7939 AND f.f_value<2.2606 THEN '重要挽留客户'
END-- 没有其他就不用else
) AS type
FROM r_level r,f_level f WHERE r.user_id=f.user_id;

这样就得到了每个用户的R，F评分和所属的用户等级：

5.2 商品指标体系

分别从商品，品类两个角度分析。

1）商品角度

商品的点击量收藏量加购量购买次数购买转化（该商品的所有用户中有购买转化的用户比）

-- 按照商品(item_id)进行分组统计，根据behavior_type分类
-- 用户行为类型（1-曝光；2-购买；3-加入购物车；4-加入收藏夹。）
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
	temp_trade 
GROUP BY
	item_id
ORDER BY buy DESC;

将结果按照购买次数降序排列一下：

对于品类分析思路跟商品维度是一样的，不同的是要对品类进行分组：

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
	temp_trade 
GROUP BY
	item_category
ORDER BY buy DESC;

5.3 平台指标体系

同样有两个维度，一是同商品分析思路一样，只不过从平台按照每日的角度来看行为指标。

二是行为路径分析。

-- 2.3.1 从平台角度看行为
-- 点击次数 收藏次数 加购次数 购买次数 购买转化（该平台当日的所有用户中有购买转化的用户比）
-- 按日统计
SELECT
	dates,
	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
	temp_trade 
GROUP BY
	dates
ORDER BY dates;
-- 2.3.2行为路径分析
-- 拼接行为路径,需要把多个行为并列排放（偏移分析函数-窗口函数，按用户——商品分组，升序排序），才能使用concat函数进行拼接,1-3-4-1-2,以2为基准，偏移量分别为4,3,2,1,自己
-- (这里为什么偏移了5次呢，理解是看业务需要，觉得几次合适就偏移几次)
-- 用户行为拼接准备
CREATE VIEW path_base_view 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_number
	temp_trade)a
	WHERE a.rank_number=1 AND behavior_type=2;
-- 拼接行为路径
SELECT
	CONCAT(ifnull( lag_4, '空' ),'-',ifnull( lag_3, '空' ),'-',ifnull( lag_2, '空' ),'-',ifnull( lag_1, '空' ),behavior_type 
	path_base_view;
-- 针对行为路径进行统计
SELECT 
	CONCAT(ifnull( lag_4, '空' ),'-',ifnull( lag_3, '空' ),'-',ifnull( lag_2, '空' ),'-',ifnull( lag_1, '空' ),'-',behavior_type ) AS path,
	COUNT(DISTINCT user_id) AS user_count
	path_base_view
GROUP BY path
ORDER BY user_count DESC;

行为路径的拼接有点复杂，可以多理解几遍，最终得到的按照路径统计的结果为：

6.结论

根据以上我们得到的一些数据，导出后利用excel作图分析。

1）用户分析

-- 根据之前的分析语句，我们取出每日的UV数据
CREATE VIEW UV AS
SELECT
	dates,
	COUNT( DISTINCT user_id ) 'uv'
	temp_trade 
GROUP BY
	dates;
-- 导出用Excel分析

用Excel画出每日UV的曲线图，并计算出UV数据的周环比数据，并作图。

可以看到每日UV数据中，明显异常点为双⼗⼆活动造成，该影响为已知影响。

对于周环比，⽇常周环⽐数据⼤多⼤于0，说明⽤户程⼀定上升趋势。

其中如11/26、12/2、12/7等数据下降，则需要结合其他数据做进⼀步的下降原因分析。

双十二之后的周环比下降为正常原因。

猜测可能的问题：

内部：产品BUG（⽹站bug）、策略问题（周年庆活动结束了）、营销问题（代⾔⼈换了）等；
外部：竞品活动问题（其他平台⼤酬宾），政治环境问题（进⼝商品限制），舆情⼝碑问题（平台商品爆出质量问题）等；

2）用户精细化运营

-- 用户精细化运营
CREATE VIEW type AS(
SELECT 
a.type,
COUNT(a.user_id) '用户数',
avg(a.date_diff) '平均最近购买间隔',
avg(a.buy_frequency) '平均购买次数'
SELECT r.user_id,
r.date_diff,
f.buy_frequency,
(CASE
WHEN r.r_value>2.7939 AND f.f_value>2.2606 THEN '重要高价值客户'
WHEN r.r_value<2.7939 AND f.f_value>2.2606 THEN '需要唤回客户'
WHEN r.r_value>2.7939 AND f.f_value<2.2606 THEN '重要深耕客户'
WHEN r.r_value<2.7939 AND f.f_value<2.2606 THEN '重要挽留客户'
) AS type
FROM r_level r,f_level f WHERE r.user_id=f.user_id)a
GROUP BY a.type);
-- 导出到Excel

通过RFM模型中的⽤户最近⼀次购买时间、⽤户消费频次分析，分拆得到以下重要⽤户。

可以在后续的精细化运营中直接使用细分用户，做差异化运营：

对⾼价值客户做VIP服务设计，增加⽤户粘性同时通过设计优惠券提升客户消费；
对深耕客户做⼴告、推送刺激，提升消费频次；
对挽留客户做优惠券、签到送礼策略，增加挽留⽤户粘性；
对唤回客户做定向⼴告、短信召回策略，尝试召回⽤户；

3）商品分析

-- 商品分析
CREATE VIEW category AS(
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
	temp_trade 
GROUP BY
	item_category
ORDER BY buy DESC);
-- 导出到Excel

我们把数据按照购买次数降序排列，展示前10的数据：

可以看到，其中’5027‘、’5399‘品类购买转化率较其余商品品类偏低（PV并不少），需要结合更多数据做进⼀步解读。可能原因：品类⾃有特性导致⽤户购买较低，⽐如⾮必需品、奢侈品等等。

4）产品路径分析

-- 产品功能路径分析
CREATE VIEW path AS(
SELECT 
	CONCAT(ifnull( lag_4, '空' ),'-',ifnull( lag_3, '空' ),'-',ifnull( lag_2, '空' ),'-',ifnull( lag_1, '空' ),'-',behavior_type ) AS path,