基于mysql的电商用户分析

米勒111

已于 2022-03-29 10:48:20 修改

阅读量2.3k

点赞数 1

分类专栏：数据分析文章标签：数据分析

于 2022-03-27 21:42:58 首次发布

本文链接：https://blog.csdn.net/mixiaolemy/article/details/123780528

版权

数据分析专栏收录该内容

37 篇文章 12 订阅

订阅专栏

5.1用户指标体系（UV/PV/留存率）+RFM模型分析

1.项目背景

本项目基于电商平台用户行为数据，在mysql关系型数据库，探索用户行为规律，寻找高价值用户；分析商品特征，寻找高贡献商品；分析产品功能，优化产品路径

1.1分析流程

2.使用“人货场”拆解方式建立指标体系

「人」(用户) 是整个运营的核心。所有举动都围绕着，如何让更多的人有购买行为，让他们买的更多，买的更贵。所以对人的洞察是一切行为的基础。目前平台上的主力消费人群有哪些特征，他们对货品有哪些需求，他们活跃在哪些场，还有哪些有消费力的人目前不在平台上，对这些问题的回答指向了接下来的行动。

「货」就对应供给,涉及到了货品分层，哪些是红海，哪些是蓝海，如何进行动态调整，是要做自营还是平台，以满足消费者的需求。

「场」就是消费者在什么场景下，以什么样的方式接触到了这个商品。早期的导购做的比较简单，目前的场就比较丰富，但也暴露了淘宝和京东在导购方面的一些问题。比如内容营销，目前最好的可能是微信的 KOL 生态和小红书，甚至微博，而不在电商自己的场。如何做一个全域的打通，和消费者进行多触点的接触，比如社交和电商联动，来完成销售转化，这就是腾讯和阿里一直都在讲的「全域营销」。

3.确认问题

本次分析的目的是想通过对用户行为数据进行分析，为以下问题提供解释和改进建议:

1)基于漏斗模型的用户购买流程各环节分析指标，确定各个环节的转换率，便于找到需要改进的环节;
2)商品分析:找出热销商品，研究热销商品特点;

3)基于RFM模型找出核心付费用户群，对这部分用户进行精准营销。

4.准备工作

4.1数据读取（用户行为数据）

表结构

USE test;
CREATE TABLE o_retailers_trade_user
(
user_id INT (9),
item_id INT (9),
behavior_type INT (1),
user_geohash VARCHAR (14),
item_category INT (5),
TIME VARCHAR (13)
);

4.2数据预处理

增加新列date_time(datetime),dates(char,年月日），便于后续时间维度分析；

-- 增加新列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)

重复值处理：创建新表temp_trade，并插入无重复数据

CREATE TABLE TEMP_TRADE LIKE o_retailers_trade_user;
INSERT INTO TEMP_TRADE  SELECT  DISTINCT * FROM o_retailers_trade_user

5.指标体系建设

5.1用户指标体系（UV/PV/留存率）+RFM模型分析

5.1.1 基础指标

uv，pv,留存率（按日）统计

/*
需求：uv,pv,浏览深度（按日）统计
pv:统计behavior——type=1的记录数
uv:统计distinct user_id的数量
浏览深度：pv/uv
*/
WITH t AS(
SELECT a.dates,COUNT(DISTINCT a.user_id) AS remain,
COUNT( DISTINCT IF(DATEDIFF(b.dates,a.dates)=1,b.user_id,NULL)) AS remain1,
COUNT( DISTINCT IF(DATEDIFF(b.dates,a.dates)=2,b.user_id,NULL)) AS remain2,
COUNT( DISTINCT IF(DATEDIFF(b.dates,a.dates)=3,b.user_id,NULL)) AS remain3,
COUNT( DISTINCT IF(DATEDIFF(b.dates,a.dates)=4,b.user_id,NULL)) AS remain4,
COUNT( DISTINCT IF(DATEDIFF(b.dates,a.dates)=5,b.user_id,NULL)) AS remain5,
COUNT( DISTINCT IF(DATEDIFF(b.dates,a.dates)=6,b.user_id,NULL)) AS remain6,
COUNT( DISTINCT IF(DATEDIFF(b.dates,a.dates)=7,b.user_id,NULL)) AS remain7,
COUNT( DISTINCT IF(DATEDIFF(b.dates,a.dates)=15,b.user_id,NULL)) AS remain15,
COUNT( DISTINCT IF(DATEDIFF(b.dates,a.dates)=30,b.user_id,NULL)) AS remain30

 FROM
(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.user_id>=a.user_id
 GROUP BY a.dates)
 SELECT dates,remain,
 CONCAT(CAST((remain1/remain)*100 AS DECIMAL(10,2)),'%') AS day1,
 CONCAT(CAST((remain2/remain)*100 AS DECIMAL(10,2)),'%') AS day2,
 CONCAT(CAST((remain3/remain)*100 AS DECIMAL(10,2)),'%') AS day3,
 CONCAT(CAST((remain4/remain)*100 AS DECIMAL(10,2)),'%') AS day4,
 CONCAT(CAST((remain5/remain)*100 AS DECIMAL(10,2)),'%') AS day5,
 CONCAT(CAST((remain6/remain)*100 AS DECIMAL(10,2)),'%') AS day6,
 CONCAT(CAST((remain7/remain)*100 AS DECIMAL(10,2)),'%') AS day7,
 CONCAT(CAST((remain15/remain)*100 AS DECIMAL(10,2)),'%') AS day15,
 CONCAT(CAST((remain30/remain)*100 AS DECIMAL(10,2)),'%') AS day30
 FROM t

5.1.2RFM模型分析

1.RFM模型：R部分(最近一次消费）

--R视图
 DROP VIEW IF EXISTS user_recency;
 CREATE VIEW user_recency AS
 SELECT user_id,MAX(dates) AS rec_buy_time
 FROM TEMP_TRADE
 WHERE behavior_type='2'
 GROUP BY user_id
 ORDER BY rec_buy_time DESC

 
 --R等级划分

 DROP VIEW IF EXISTS r_level;
CREATE VIEW r_level AS
SELECT user_id,rec_buy_time,DATEDIFF('2019-12-18',rec_buy_time) AS recen_num,(
CASE WHEN DATEDIFF('2019-12-18',rec_buy_time)<=2 THEN 5
WHEN DATEDIFF('2019-12-18',rec_buy_time)<=2 THEN 5
WHEN DATEDIFF('2019-12-18',rec_buy_time)<=4 THEN 4
WHEN DATEDIFF('2019-12-18',rec_buy_time)<=6 THEN 3
WHEN DATEDIFF('2019-12-18',rec_buy_time)<=2 THEN 2
ELSE 1 END) AS r_value
FROM user_rencency;
SELECT * FROM r_level ORDER BY recen_num DESC

2.RFM模型：F部分（一段时间内的消费频率）

--F视图
CREATE VIEW fren_value AS
SELECT user_id,COUNT(user_id) AS buy_frenq
FROM TEMP_TRADE
WHERE behavior_type='2'
GROUP BY user_id;
--F等级划分
CREATE VIEW f_level AS
SELECT user_id,buy_frenq,(
CASE WHEN buy_frenq<=4 THEN 2
WHEN buy_frenq<=6 THEN 3
WHEN buy_frenq<=8 THEN 4
ELSE 5 END) AS 'f_value'
FROM fren_value

3.整合结果

本次数据中通过最近消费（R)和消费频率（F）建立RFM模型

重要高价值客户：最近一次消费较近而且消费频率较高的客户
重要唤回客户：最近一次消费较远且消费频率较高的客户
重要深耕客户：最近一次消费较近且消费频率较低的客户
重要挽留客户：最近一次消费较远且消费频率较低的客户

根据最近一次消费的均值和消费频率的均值定高低界限

--R-平均值
SELECT AVG(r_value) AS 'r_avg' FROM r_level;  -2.7091
--F平均值
SELECT AVG(f_value) AS 'f_avg' FROM f_level;  -2.7455
--用户八大类等级划分，由于该数据没有M值，只建立了4个分类
DROP VIEW IF EXISTS RFM_inall;
CREATE VIEW RFM_inall AS
SELECT a.*,b.f_value,
(CASE WHEN a.r_value>2.7091 AND b.f_value>2.7455 THEN '重要高价值客户'
WHEN a.r_value>2.7091 AND b.f_value<2.7455 THEN '重要深耕客户'
WHEN a.r_value<2.7091 AND b.f_value>2.7455 THEN '重要唤回客户'
WHEN a.r_value<2.7091 AND b.f_value<2.7455 THEN '重要挽留客户'
END) AS user_class
FROM r_level a,f_level b
WHERE a.user_id=b.user_id;
SELECT COUNT(user_id) AS user_v,user_class FROM  RFM_inall GROUP BY user_class

5.2 商品指标体系

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

按照商品进行分组统计


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

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

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

5.3平台指标体系

1.行为指标：

点击次数收藏次数加购物车次数购买次数购买转化

SELECT dates,COUNT(1) AS '每日的总数',
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 dates

行为路径分析

WITH t AS(
SELECT a.* FROM
(
SELECT user_id,item_id,
lag(behavior_type,4) over(PARTITION BY user_id,item_id ORDER BY dates) lag4,
lag(behavior_type,3) over(PARTITION BY user_id,item_id ORDER BY dates) lag3,
lag(behavior_type,2) over(PARTITION BY user_id,item_id ORDER BY dates) lag2,
lag(behavior_type,1) over(PARTITION BY user_id,item_id ORDER BY dates) lag1,
behavior_type,
rank() over(PARTITION BY user_id,item_id ORDER BY dates DESC) 'rank_dn'
FROM TEMP_TRADE) a
WHERE behavior_type=2 AND rank_dn=1)
--查询该路径下有多少购买用户数

SELECT CONCAT(IFNULL(
lag4,'空'),'-',IFNULL(lag3,'空'),'-',IFNULL(lag2,'空'),'-',IFNULL(lag1,'空'),'-',behavior_type) AS user_way,
COUNT(DISTINCT user_id)
FROM t
GROUP BY CONCAT(IFNULL(
lag4,'空'),'-',IFNULL(lag3,'空'),'-',IFNULL(lag2,'空'),'-',IFNULL(lag1,'空'),'-',behavior_type)

6.结论

6.1 用户分析

uv异常分析：每⽇UV数据中，明显异常点为双⼗⼆活动造成，该影响为已知影响。

对于UV周环⽐的分析：⽇常周环⽐数据⼤多⼤于0，说明⽤户程⼀定上升趋势，其中如11⽉26⽇、12⽉ 2⽇、12⽉7⽇等的数据为下降数据，需要结合其他数据做进⼀步的下降原因分析。双⼗⼆活动后⽤户周环⽐会相应下降，为正常原因。

猜测可能的问题有：

内部问题：产品BUG（⽹站bug）、策略问题（周年庆活动结束了）、营销问题（代⾔⼈换了）等；

外部问题：竞品活动问题（其他平台⼤酬宾），政治环境问题（进⼝商品限制），舆情⼝碑问题（平台商品爆出质量问题）等；

6.2⽤户精细化运营

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

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

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

6.3商品分析

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

6.4 产品功能路径分析

以下为主要购买路径。可以发现⽤户多以直接购买为主；添加购物⻋的购买在主要购买路径中数量较少。后续的产品加购功能和产品收藏功能还需要结合更多数据做改进⽅案。

米勒111

关注

1
点赞
踩
5

收藏

觉得还不错? 一键收藏
0
评论
基于mysql的电商用户分析

目录1.项目背景1.1分析流程2.使用“人货场”拆解方式建立指标体系3.确认问题4.准备工作4.1数据读取（用户行为数据）4.2数据预处理5.指标体系建设5.1用户指标体系（UV/PV/留存率）+RFM模型分析5.1.1 基础指标5.1.2RFM模型分析1.项目背景本项目基于电商平台用户行为数据，在mysql关系型数据库，探索用户行为规律，寻找高价值用户；分析商品特征，寻找高贡献商品；分析产品功能，优化产品路径1.1分析流程2...
复制链接

扫一扫