电商“人货场”指标体系
一、项目背景
电商⾏业近⼏年迅猛发展,电⼦商务从早些年的粗放式经营,逐步转化为精细化运营。随着平台数据量的不断积累,通过数据分析挖掘消费者的潜在需求,消费偏好成为平台运营过程中的重要环节。
本项目目的:
探索⽤户⾏为规律,寻找⾼价值⽤户;
分析商品特征,寻找⾼贡献商品;
分析产品功能,优化产品路径。
二、使用“人货场”拆解方式建立指标体系
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