一、项目背景

电商⾏业近⼏年迅猛发展，电⼦商务从早些年的粗放式经营，逐步转化为精细化运营。随着平台数据量的不断积累，通过数据分析挖掘消费者的潜在需求，消费偏好成为平台运营过程中的重要环节。
本项目目的：
探索⽤户⾏为规律，寻找⾼价值⽤户；
分析商品特征，寻找⾼贡献商品；
分析产品功能，优化产品路径。

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

1.分析流程

2.使⽤“⼈货场”拆解方式建⽴指标体系
「⼈」（⽤户) 是整个运营的核⼼。所有举动都围绕着，如何让更多的⼈有购买⾏为，让他们买的更多，买的更贵。
「货」就对应供给,涉及到了货品分层，哪些是红海（销量高、利润少、竞争多），哪些是蓝海（销量少、利润高、竞争少），如何进⾏动态调整，是要做⾃营还是平台，以满⾜消费者的需求。
「场」就是消费者在什么场景下，以什么样的⽅式接触到了这个商品。

三、问题确认

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

四、数据准备

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)
);
select * from o_retailers_trade_user limit 5;

2 数据预处理

-- 增加date_time、dates字段
alter table o_retailers_trade_user add column date_time datetime null;
-- date_time 字段数据来自于基础数据中的time字段
update o_retailers_trade_user set date_time=str_to_date(time,'%Y-%m-%d %H');
  -- h代表24进制 h 代表12进制

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;
-- 插入数据
insert into temp_trade select distinct * from o_retailers_trade_user;

五、sql实现

1 用户指标体系

基础指标体系（UV/PV/留存率）+ RFM模型分析

1.1 基础指标

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计数
某日  活跃用户数 1日后留存率 2... 7 15 30
*/

-- 自关联的方式
select user_id,dates from temp_trade group by user_id, dates;

create view  user_remain_view as
select 
a.dates,count(distinct b.user_id) user_count,
count(if(datediff(b.dates,a.dates)=1,b.user_id,null)) as remain1, -- 1日留存数计算
count(if(datediff(b.dates,a.dates)=2,b.user_id,null)) as remain2, -- 2日留存数计算
count(if(datediff(b.dates,a.dates)=3,b.user_id,null)) as remain3, -- 3日留存数计算
count(if(datediff(b.dates,a.dates)=4,b.user_id,null)) as remain4, -- 4日留存数计算
count(if(datediff(b.dates,a.dates)=5,b.user_id,null)) as remain5, -- 5日留存数计算
count(if(datediff(b.dates,a.dates)=6,b.user_id,null)) as remain6, -- 6日留存数计算
count(if(datediff(b.dates,a.dates)=7,b.user_id,null)) as remain7, -- 7日留存数计算
count(if(datediff(b.dates,a.dates)=15,b.user_id,null)) as remain15, -- 15日留存数计算
count(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.dates>=a.dates
group by a.dates;

drop view if exists  user_remain_view
select *from user_remain_view
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'
from user_remain_view

留存率结果如下：

1.2 RFM模型分析

1.2.1RFM模型：R部分

-- R指标分析：根据每个用户最近一次购买时间，给出相应的分数
-- 获取每个用户最近购买时间
drop view if exists user_recency_view
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相差几天，根据相差的天数给与一定的分数
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值
from user_recency_view

1.2.2RFM模型：F部分（⼀定时间内的消费频率）

-- F指标计算，需要求出每个用户消费次数（购买次数），拿到具体消费次数之后对消费	情况评分
-- 求出每个用户消费次数（购买次数）
create view user_buy_fre_view as
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分
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
from user_buy_fre_view

1.2.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 -- 没有其他就别else
)
from r_level r ,f_level f 
where r.user_id = f.user_id

结果如下：

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;

-- 对应品类的点击量 收藏量 加购量 购买次数 购买转化（该商品品类的所有用户中有购买转化的用户比
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;

结果如下：

3 平台指标体系

3.1行为指标

-- 点击次数 收藏次数 加购物车次数 购买次数 购买转化（该平台当日的所有用户中有购买转化的用户比）
-- 每日的分析(1-4，分别表示点击pv、购买buy、加购物车cart、喜欢fav)
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

3.2行为路径分析

/*-- 行为路径分析
核心：拼接行为路径
user_id  item_id  behavior_id  1-3-4-1-2(把多个行为并列摆放，才能使用concat函数进行拼接）
zhangsan   a        1
					3
					4
					1
	1	3	4	1   2(偏移分析函数 lag over lead over -- 窗口函数，分组（用户+商品），排序（升序））
*/
-- 用户行为拼接准备
create view path_base_view as
select * from
(select 
user_id,
item_id,
behavior_type,
lag(behavior_type,4) over(partition by user_id,item_id order by date_time) lag_4, -- 1
lag(behavior_type,3) over(partition by user_id,item_id order by date_time) lag_3, -- 1
lag(behavior_type,2) over(partition by user_id,item_id order by date_time) lag_2, -- 1
lag(behavior_type,1) over(partition by user_id,item_id order by date_time) lag_1,-- 1
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 用户分析

UV异常分析：每日UV数据中，明显异常点为双十二活动造成，该影响为已知影响。
用户成上升趋势，其中11.26，12.3等数据下降还需结合其它数据再做判断。
双十二活动后用户周 环比会相应下降，为正常原因。

猜测可能的问题有:
内部问题：产品BUG(网站bug)、策略问题(周年庆活动结束了)、营销问题(代言人换了)等;
外部问题：竞品活动问题(其他平台大酬宾)，政治环境问题(进口商品限制)，舆情口碑问题(平台商品爆出质量问题)等;

2 用户精细化运营

通过RFM模型中的⽤户最近⼀次购买时间、⽤户消费频次分析，分拆得到以下重要⽤户。
可以在后续精细化运营场景中直接使⽤细分⽤户，做差异化运营：
对⾼价值客户做VIP服务设计，增加⽤户粘性同时通过设计优惠券提升客户消费；
对深耕客户做⼴告、推送刺激，提升消费频次；
对挽留客户做优惠券、签到送礼策略，增加挽留⽤户粘性；
对唤回客户做定向⼴告、短信召回策略，尝试召回⽤户。

3 商品分析

找到热销商品；
其中’5027‘、’5399‘品类购买转化率较其余商品品类偏低，需要结合更多数据做进一步解读。可能的原因：品类自有特性导致用户购买较低，比如非必需品、奢侈品等等。

4 产品功能路径分析

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