mysql电商人货场指标体系

一、项目背景

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

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

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