案例"/>
数据分析——MySQL电商案例
目录
模块一：用户数据分析
计算 UV PV 留存率 + RFM模型
1.数据预处理
2 基础指标计算
2.1 计算PV UV
2.2 计算留存率
3 RFM模型
3.1 R指标计算
3.2 F指标计算
3.3 为用户打上标签
模块二：商品数据
1 商品、品类、平台相关
2 行为路径分析
模块三：分析
1 UV
2 精细化运营
3 路径分析
模块一：用户数据分析
计算 UV PV 留存率 + RFM模型
1.数据预处理
-- 1 创建用户行为数据表
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 )
);-- 2 导入“案例数据.xlsx”中的数据
导入后的数据长这样：
time中的数据格式不太好，是“日期+数字”的字符串，无法进行datediff计算，其实最后的数字表示的是时间（24h），所以我们要把格式改为“日期+时间”。另外在某些指标计算时不需要精度很高的，所以再单独创建“日期”一列。
-- 3 添加两列
-- 增加date_time、dates字段
ALTER TABLE o_retailers_trade_user ADD COLUMN date_time datetime NULL; -- date_time是日期格式
UPDATE o_retailers_trade_user -- H 代表24进制，h代表12进制
SET date_time = STR_TO_DATE( time, '%Y-%m-%D %H' ); -- date_time 字段来自于基础数据中的time字符串ALTER TABLE o_retailers_trade_user ADD COLUMN dates CHAR ( 10 ) NULL;-- 将年月日时变为年月日字符串
UPDATE o_retailers_trade_user
SET dates = date( date_time ); -- dates 只保留date_time的年月日得到的数据如下图：
得到数据表后，我们要去掉重复数据，然后创建临时表，对其进行操作，这样操作后不影响原始数据。
-- 4 去重预处理
-- 创建临时表，便于后续操作
create table temp_trade like o_retailers_trade_user;
INSERT into temp_trade select distinct * from o_retailers_trade_user;2 基础指标计算
先看下待计算指标是什么？
数据表的结构如下：
2.1 计算PV UV
PV UV都是某一天内的数据，所以要进行分组计数(group by, count)
/*5.需求： uv、 pv、浏览深度（按日）统计pv 页面浏览量：统计behavior_type=1的记录数，需要按日统计（分组）uv 独立访客数（一人一日多次访问只计1）: 统计distinct user_id 的数量，需要按日统计（分组）浏览深度 单位访客数浏览的页面： pv/uv
*/
-- 统计uv时，不能重复计数--独立
-- 统计pv时，仅当behavior_type=1才计数
SELECTdates,count( DISTINCT user_id ) AS 'uv',count( IF ( behavior_type = 1, user_id, NULL ) ) AS 'pv',count( IF ( behavior_type = 1, user_id, NULL ) ) / count( DISTINCT user_id ) AS 'pv/uv'
FROMtemp_trade
GROUP BY # 统计的是某一天用户的访问情况，按日分组dates;
得到数据如下：
2.2 计算留存率
留存率：选定某一天的用户数作为基数，往后看，这个用户数每天还剩下多少
思路：
第一步，要计算某一天活跃用户数，即不管某用户在某天买了几件商品，我们都只计1
第二步，对上表进行自关联，然后可以根据日期的差值来计算n日留存率，由转化为分组条件计数（以A天为基准，当B天距离A天N天时，不重复的用户数）
-- 自关联+去重
SELECTuser_id,dates
FROMtemp_trade
GROUP BYuser_id,dates -- 某用户某天只返回一条数据得到去重数据：
-- 表A和自己关联,每日活跃用户数（按日分组+统计）
CREATE VIEW user_remain_view AS # 创建视图，便于之后调用
SELECTa.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日留存数
FROM( SELECT user_id, dates FROM temp_trade GROUP BY user_id, dates ) aLEFT JOIN ( SELECT user_id, dates FROM temp_trade GROUP BY user_id, dates ) b ON a.user_id = b.user_id
WHEREb.dates >= a.dates
GROUP BYa.dates # 要分组就要查出来
-- 查看数据
SELECT*
FROMuser_remain_view
-- 计算留存率（转为小数+拼接百分号）
SELECTdates,user_count,concat( cast( ( remain1 / user_count ) * 100 AS DECIMAL ( 10, 2 ) ), '%' ) AS day1,-- 1日留存率 cast转换函数 decimalconcat( cast( ( remain2 / user_count ) * 100 AS DECIMAL ( 10, 2 ) ), '%' ) AS day2,-- 2日留存率 cast转换函数 decimalconcat( cast( ( remain3 / user_count ) * 100 AS DECIMAL ( 10, 2 ) ), '%' ) AS day3,-- 3日留存率 cast转换函数 decimalconcat( cast( ( remain4 / user_count ) * 100 AS DECIMAL ( 10, 2 ) ), '%' ) AS day4,-- 4日留存率 cast转换函数 decimalconcat( cast( ( remain5 / user_count ) * 100 AS DECIMAL ( 10, 2 ) ), '%' ) AS day5,-- 5日留存率 cast转换函数 decimalconcat( cast( ( remain6 / user_count ) * 100 AS DECIMAL ( 10, 2 ) ), '%' ) AS day6,-- 6日留存率 cast转换函数 decimalconcat( cast( ( remain7 / user_count ) * 100 AS DECIMAL ( 10, 2 ) ), '%' ) AS day7,-- 7日留存率 cast转换函数 decimalconcat( cast( ( remain15 / user_count ) * 100 AS DECIMAL ( 10, 2 ) ), '%' ) AS day15,-- 15日留存率 cast转换函数decimalconcat( cast( ( remain30 / user_count ) * 100 AS DECIMAL ( 10, 2 ) ), '%' ) AS day30 -- 30日留存率 cast转换函数decimal
FROMuser_remain_view3 RFM模型
RFM模型
R最近购物的时间间隔（今天是2019-12-18则2019-12-17到今天就是1天）
F一定时间段内购物的频率
M一定时间段内消费的金额
3.1 R指标计算
求出每个用户最近购买时间（分组极值），该时间和参照日期相减，根据差值的大小进行打分
DROP VIEW
IFEXISTS user_recent_buy;-- 将用户最近的购买时间提取到R临时表中，然后给出相应的分数
-- 每个用户最近一次的购买时间：多个用户，每一个用户有多次购买记录————分组统计。最近，数值上最大。
CREATE VIEW user_recent_buy AS SELECTuser_id,max( dates ) AS recent_buy_time
FROMtemp_trade -- 购买行为
WHEREbehavior_type = 2 -- 购买行为
GROUP BYuser_id; -- 统计各个用户最近购买时间如图：
-- 计算每个用户最近购买时间距离2019-12-18(所有数据在此之前)相差几天，根据差值给出分数
CREATE VIEW r_level AS SELECT
user_id,
recent_buy_time,
DATEDIFF( '2019-12-18', recent_buy_time ),
(CASE -- <=2 5分 <=4 4分 <=6 3分 <=8 2分 else 1分WHEN DATEDIFF( '2019-12-18', recent_buy_time ) <= 2 THEN5 WHEN DATEDIFF( '2019-12-18', recent_buy_time ) <= 4 THEN4 WHEN DATEDIFF( '2019-12-18', recent_buy_time ) <= 6 THEN3 WHEN DATEDIFF( '2019-12-18', recent_buy_time ) <= 8 THEN2 ELSE 1 END ) AS r_value -- 判断某个人的r分值 FROMuser_recent_buy3.2 F指标计算
-- F指标计算：求出每个用户购物次数，然后根据次数给用户进行评分
-- 求出每个用户的消费次数（不去重）
DROP VIEW
IFEXISTS user_buy_fre_view;
CREATE VIEW user_buy_fre_view AS SELECT
user_id,
count( user_id ) AS buy_freq -- 各个用户购买频率
FROMtemp_trade
WHEREbehavior_type = 2 -- 2表示购买
GROUP BYuser_id;-- 评分 购买次数<=2 1 <=4 2 <=6 3 <=8 4 其他5分
CREATE VIEW f_level AS SELECT
user_id,
buy_freq,
(CASEWHEN buy_freq <= 2 THEN1 WHEN buy_freq <= 4 THEN2 WHEN buy_freq <= 6 THEN3 WHEN buy_freq <= 8 THEN4 ELSE 5 END ) AS f_value FROMuser_buy_fre_view3.3 为用户打上标签
分别计算均值
-- 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-- 每个人的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
)from r_level r,f_level f where r.user_id=f.user_id模块二：商品数据
1 商品、品类、平台相关
-- 商品的点击量 收藏量 加购量 购买次数 购买转化（该商品的所有⽤户中有购买转化的用户比）
/*思路：对商品进行分组统计
*/SELECTitem_id,count( IF ( behavior_type = 1, user_id, NULL ) ) AS 'pv0',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' -- 购买转化率
FROMtemp_trade
GROUP BYitem_id;-- 对应品类的点击量 收藏量 加购量 购买次数 购买转化（该商品品类的所有用户中有购买转化的用户）
SELECTitem_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
FROMtemp_trade
GROUP BYitem_category
ORDER BYbuy DESC;-- 平台指标分析
-- 整个平台按日分析(1-4，分别表示点击pv、购买buy、加购物⻋cart、喜欢fav)
SELECTdates, -- 将商品名替换成类目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
FROMtemp_trade
GROUP BYdates
ORDER BYbuy DESC;2 行为路径分析
购买行为往前推4个行为
user_id item_id behavior_id 1-1-3-1-2
xiaoming a 1
1
3
1
1 1 3 1 2
- 偏移分析函数：在2的位置，用lag向上找4得到1，找3得到1
- 分组：某用户购买某商品时产生的某种行为
- 排序：按日期升序（从上到下，从小到大）
- 得到行为路径后进行count统计
1 利用偏移函数得到基础数据
SELECTuser_id,item_id,lag ( behavior_type, 4 ) over ( PARTITION BY user_id, item_id ORDER BY date_time ) lag_4,-- 1lag ( behavior_type, 3 ) over ( PARTITION BY user_id, item_id ORDER BY date_time ) lag_3,-- 1lag ( behavior_type, 2 ) over ( PARTITION BY user_id, item_id ORDER BY date_time ) lag_2,-- 3lag ( behavior_type, 1 ) over ( PARTITION BY user_id, item_id ORDER BY date_time ) lag_1,-- 1behavior_type,-- 2rank ( ) over ( PARTITION BY user_id, item_id ORDER BY date_time DESC ) AS rank_number -- 倒序排序，第一条的数据是最新的FROMtemp_trade2 筛选出“最近一天+购买了商品”的用户数据
-- 用户行为拼接基础准备
DROP VIEW path_base_view;
CREATE VIEW path_base_view AS SELECT
a.* -- 同一个分组下最后一条记录是2，把它前面的4条数据拼接起来
FROM(SELECTuser_id,item_id,lag ( behavior_type, 4 ) over ( PARTITION BY user_id, item_id ORDER BY date_time ) lag_4,-- 1lag ( behavior_type, 3 ) over ( PARTITION BY user_id, item_id ORDER BY date_time ) lag_3,-- 1lag ( behavior_type, 2 ) over ( PARTITION BY user_id, item_id ORDER BY date_time ) lag_2,-- 3lag ( behavior_type, 1 ) over ( PARTITION BY user_id, item_id ORDER BY date_time ) lag_1,-- 1behavior_type,-- 2rank ( ) over ( PARTITION BY user_id, item_id ORDER BY date_time DESC ) AS rank_number -- 倒序排序，第一条的数据是最新的FROMtemp_trade) a
WHEREa.rank_number = 1 AND a.behavior_type = 2;-- 取最近一次行为是购买的数据3 拼接
此处需进行判断
-- 拼接
SELECTconcat(IFNULL( lag_4, '空' ),'-',IFNULL( lag_3, '空' ),'-',IFNULL( lag_2, '空' ),'-',IFNULL( lag_1, '空' ),'-',behavior_type ),count( DISTINCT user_id ) AS user_count
FROMpath_base_view
GROUP BYconcat(IFNULL( lag_4, '空' ),'-',IFNULL( lag_3, '空' ),'-',IFNULL( lag_2, '空' ),'-',IFNULL( lag_1, '空' ),'-',behavior_type )
模块三：分析
1 UV
不难看出，UV的突变是12/12导致的
猜测可能的问题有：
内部问题：产品BUG（⽹站bug）、策略问题（周年庆活动结束了）、营销问题（代⾔⼈换了）等；
外部问题：竞品活动问题（其他平台促销力度大），政治环境问题（进⼝商品限制），舆情⼝碑问题（平台商品爆出质量问题）等
2 精细化运营
对⾼价值客户做VIP服务设计，增加⽤户粘性同时通过设计优惠券提升客户消费；
对深耕客户做⼴告、推送刺激，提升消费频次；
对挽留客户做优惠券、签到送礼策略，增加挽留⽤户粘性；
对唤回客户做定向⼴告、短信召回策略，尝试召回⽤户。
3 路径分析
发现⽤户多以直接购买为主；添加购物⻋的购买在主要购买路径中数量较少。后续的产品加购功能和产品收藏功能还需要结合更多数据做改进⽅案。
更多推荐
数据分析——MySQL电商案例
发布评论