che小洁数据分析学习笔记MySQL(一)电商案例

唐一刀

基于MySQL的电商用户、商品、平台价值分析

1.项目背景

通过数据分析，挖掘消费者潜能，消费偏好成为平台运营过程中的重要环节。

探索用户行为规律，寻找高价值用户
分析商品特征，寻找高贡献商品
分析产品功能，优化产品路径

2.分析流程




’人货场‘ 指标流程图

• 数据清洗 （脏数据清洗）空字段/重复数据
  
• 数据预处理  （添加/删除字段数据）
  
• 指标体系  指标体系的构建（你要统计分析哪些指标，提前进行）电商平台指标构建基于'人货场'理论
  

使用''人货场''拆解方式建立指标体系——（最终结果：评价'用户、商品、平台'三者质量）

• 用户"人"： UV计算(一定时间内访问网页的人数)、PV(页面浏览量)、留存计算、购买行为计算——近期购买时间、近期购买次数、复购用户标记——RFM模型用户标记——目标用户标记
  
• 商品"货"： 点击量、收藏量、加购量、购买、购买转化率——热门商品——重要商品维护
  
• 平台"场"： PV计算、收藏计算、加购率计算、购买计算——功能路线分析——优化产品功能路线
  

3.确认问题

目的：通过对用户行为数据分析，提供解释和改进建议

漏斗模型——确定各环节转化率——找到需要改进的环节
商品分析——找到热销商品——研究热销商品特点
RFM模型——核心付费用户群——进行精准营销

4.数据准备



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)
);数据示例：


数据预处理（time列标准化处理、重复值处理）
time列标准化处理
/*增加date_time、dates字段*/
alter table o_retailers_trade_user add column date_time datetime null;
alter table o_retailers_trade_user add column dates char(10) null;
/*date_time 字段数据来自于基础数据中的time字段,dates 字段来自于date_time字段*/
/*H 代表24进制，h代表12进制*/
update o_retailers_trade_user set date_time=STR_TO_DATE(time,'%Y-%m-%d %H');
update o_retailers_trade_user set dates=date(date_time);重复值预处理（1.建新表 2.将旧表数据distinct去重再插入新表）
create table temp_trade like o_retailers_trade_user;
insert into temp_trade select distinct * from o_retailers_trade_user;数据示例：


5.指标体系建设（人  货  场体系）

（一）人

用户指标体系：基础指标体系（uv/pv/留存率）+ RFM模型分析
基础指标体系（uv/pv/留存率）
/*需求：UV、PV、浏览深度(按日)统计
pv：统计behavior_type=1的记录数，需要按日统计（分组）
uv：统计distinct user_id 的数量，需要按日统计（分组）
浏览深度：pv/uv
*/
/*pv进行count时候，如果behavior_type=1进行计算，如果不是，不进行计算*/
SELECT dates,
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'
FROM temp_trade
GROUP BY dates;数据示例：


/*留存率(按日)分析
  没有注册用户信息，按活跃用户留存率计算
分析
(某天100个用户活跃，过了3天后剩下50个人活跃)
                          活跃用户      过了1天      过了2天
         2019-12-28         100         90 (90%)     80 (80%)
       
   1)  获取到类似于上面的一个结果集
   2） 基础数据中所有的日都应该进行如下的计算
       
     计算到
     xxx用户      2019-12-28      (在数据集中找到2019-12-28日之后的数据)
        |            dates          dates_1
     xxx用户      2019-12-28      2019-12-29  (有29日这条记录，那么该用户是活跃的)
     xxx用户      2019-12-28      2019-12-30
        |
     xxx用户      2019-12-28   （dates_1-dates)相差几天-----相差1天应该在过了1天的统计中+1
                                                                   或者进行count计数
                                                            相差2天应该在过了2天的统计中+1
                                                                   或者进行count计数
*/            
   /*找出需要数据信息，其他信息不用显示*/
select user_id,dates from temp_trade group by user_id,dates;

/*自关联的方式
   1）先自连接，并按日期分组和按用户分组，筛选右表日期大于左表的,
                再按日期分组——此时数distinct用户ID即为device.
   2）日期相减，distinct去重，count为留存数
   3）留存率计算，留存数/基准日活跃用户数
*/
    /*自连接*/
   select * 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;数据示例：


/*某日     基准日活跃用户数     1日后留存数 2....7 15 30*/
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日留存数计算*/
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;数据示例：


/*某日     基准日活跃用户数     1日后留存率 2....7 15 30*/
/*concat 拼接函数 将多个字符串连接成一个字符串
  cast((a/b)*100 as decimal(10,2))  长度为10，保留2位小数
*/
select dates,user_count,
   concat(cast((remain1/user_count)*100 as decimal(10,2)),'%') as 'day_1',   /*1日留存率*/
   concat(cast((remain2/user_count)*100 as decimal(10,2)),'%') as 'day_2',   /*2日留存率*/
   concat(cast((remain3/user_count)*100 as decimal(10,2)),'%') as 'day_3',   /*3日留存率*/
   concat(cast((remain4/user_count)*100 as decimal(10,2)),'%') as 'day_4',   /*4日留存率*/
   
   concat(cast((remain5/user_count)*100 as decimal(10,2)),'%') as 'day_5',   /*5日留存率*/
   concat(cast((remain6/user_count)*100 as decimal(10,2)),'%') as 'day_6',   /*6日留存率*/
   concat(cast((remain7/user_count)*100 as decimal(10,2)),'%') as 'day_7',   /*7日留存率*/
   concat(cast((remain15/user_count)*100 as decimal(10,2)),'%') as 'day_15', /*15日留存率*/
   concat(cast((remain30/user_count)*100 as decimal(10,2)),'%') as 'day_30'  /*30日留存率*/
from user_remain_view;数据示例：


RFM模型分析
最近消费（R）消费频率（F）本次模型中无（M）

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

R 指标分析:根据每个用户最近一次购买时间，给出相应的分数
/*获取每个用户的最近购买时间*/
  /*建立试图，将数据放入临时表中*/
create view user_recency_view as
select
user_id,max(dates) as recent_buy_time  /*最近购买日期*/
from temp_trade
where behavior_type=2    /*购买行为*/
group by user_id;
/*计算每个用户最近购买时间距离(2019-12-18)指定日期相差几天，根据相差天数给予一定的分数*/
  /* <=2  5分  <=4  4分   <=6  3分   <=8  2分  其他1分*/
select user_id,
recent_buy_time,/*最近购买时间*/
DATEDIFF('2019-12-18',recent_buy_time),/*与指定日期相差天数*/
(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分值*/
from user_recency_view;数据示例：


F 指标分析:求出每个用户消费次数（购买次数)，拿到具体消费次数之后对消费情况评分
/*求出每个用户消费次数（购买次数）*/
select user_id,count(user_id) as buy_frequency/*各用户购买次数*/
from temp_trade
where behavior_type=2
group by user_id;
/*评分，购买次数<=2 1  <=4 2  <=6 3  <=8 4  其他5分*/
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'
from
user_buy_fre_view;
数据示例：


R 和 F 用户指标整合
/*r均值*/
select avg(r_value) as 'r_level' from r_level;  /*2.7939*/
/*f均值*/
select avg(f_value) as 'f_level' 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       /*没有其他就不用else*/
)
from r_level r,f_level f where r.user_id=f.user_id;数据示例：


（二）货

商品指标体系：
1）商品的点击量 收藏量 加购量 购买次数 购买转化（该商品的所有用户中有购买转化的用户比）
按照商品进行分组统计
-- count 不行 因为不管是零还是一都会被数进去，因为count是对于行数的相加
-- count(case when behavior_type = 1 then 1 else 0 end) as 'pv'
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;数据示例：


2)  对应品类的点击量 收藏量 加购量 购买次数 购买转化（该商品品类的所有用户中有购买转化的用户比）
按照商品品类进行分组统计
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;数据示例：


（三）场

平台指标体系
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' /*购买转化*/
from
temp_trade
group by dates;数据示例：


2)  用户行为路径分析
/*核心：拼接行为路径
user_id    item_id  behavior_type  1-3-4-1-2 (把多个行为并列摆放，才能使用concat函数进行拼接)
zhangsan      a          1
                         3
                         4                                                                       
                         1
                 1-3-4-1-2       
（偏移分函数 lag over  lead over ---窗口函数，分组（用户+商品），排序（升序））       
*/       
--用户行为拼接准备
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
from temp_trade;                         数据示例：


---将偏移数据进行倒序排列，取距离最近的（即为最后一次）behavior_type=2的购买路径
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
    from temp_trade
        ) a where a.rank_number = 1 and a.behavior_type = 2;数据示例：


---拼接行为路径
select
   concat(
                ifnull( lag_4, '空' ),'-',
                ifnull( lag_3, '空' ),'-',
                ifnull( lag_2, '空' ),'-',
                ifnull( lag_1, '空' ),'-',
                behavior_type
)
from path_base_view;数据示例：


---针对行为路径进行分组count统计
select concat(ifnull( lag_4, '空' ),'-',ifnull( lag_3, '空' ),'-',ifnull( lag_2, '空' ),'-',
              ifnull( lag_1, '空' ),'-',behavior_type ) ,count(distinct user_id)
from path_base_view
     group by concat(ifnull( lag_4, '空' ),'-',ifnull( lag_3, '空' ),'-',
                     ifnull( lag_2, '空' ),'-',ifnull( lag_1, '空' ),'-',behavior_type ) ;数据示例：


结论：

通过数据间联系，根据可视化图表可以直观发现问题并且进行对问题进行分析
1.用户分析
猜测可能的问题有：

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

用户精细化运营（RFM模型）
通过RFM模型中的用户最近购买时间、用户消费频次分析，分拆得到以下重要用户，可以在后续精细化运营场景中直接使用细分用户，做差异化运营。

• 高价值客户：做VIP服务设计，增加用户粘性同时通过设计优惠劵提升客户消费
  
• 深耕客户：做广告，推送刺激，提升消费频次
  
• 挽留客户：做优惠劵，签到送礼策略，增加挽留用户粘性
  
• 唤回客户：做定向广告，短信召回策略，尝试召回用户
  

2.商品分析
品类自有特性导致用户购买较低，比如非必需品、奢侈品等。
3.产品功能路径分析
用户多以直接购买为主，添加购物车的购买在主要购买路径中数量较少，后续的产品加购功能和产品收藏功能还需要结合更多数据做改进方案。

让理想飞

支持，赞一个