本文的主要内容：

人：留存率分析；RFM模型货：商品各指标分析场：用户行为路径分析

本文将利用MySQL，针对实战项目进行以上三个方面的分析。

在正式开始本文之前，我们先来了解一下什么是“人货场”：

1. 人：「人」是整个运营的核⼼。所有举动都围绕着，如何让更多的⼈有购买⾏为，让他们买的更多，买的更贵。所以对⼈的洞察是⼀切⾏为的基础。⽬前平台上的主⼒消费⼈群有哪些特征，他们对货品有哪些需求， 他们活跃在哪些场，还有哪些有消费⼒的⼈⽬前不在平台上，对这些问题的回答指向了接下来的⾏动。

2. 货：「货」就对应供给，涉及到了货品分层，哪些是红海，哪些是蓝海，如何进⾏动态调整，是要做⾃营还是平台，以满⾜消费者的需求。

3. 场：「场」就是消费者在什么场景下，以什么样的⽅式接触到了这个商品。早期的导购做的⽐较简单，⽬前的场就⽐较丰富，但也暴露了淘宝和京东在导购⽅⾯的⼀些问题。⽐如内容营销，⽬前最好的可能是微信的KOL⽣态和⼩红书，甚⾄微博，⽽不在电商⾃⼰的场。如何做⼀个全域的打通，和消费者进⾏多触点的接触，⽐如社交和电商联动，来完成销售转化，这就是腾讯和阿⾥⼀直都在讲的「全域营销」。

一、项目背景

项目背景：随着电商⾏业近⼏年的迅猛发展，电⼦商务从早些年的粗放式经营，逐步转化为精细化运营。随着平台数据量的不断积累，通过数据分析挖掘消费者的潜在需求，消费偏好成为平台运营过程中的重要环节。

项目目的：

探索⽤户⾏为规律，寻找⾼价值⽤户；

分析商品特征，寻找⾼贡献商品；

分析产品功能，优化产品路径。

二、使⽤“⼈货场”拆解⽅式建⽴指标体系

本文的开头部分，我们已经了解了什么是“人货场”。下面，我们针对本文具体实战项目，直接搭建指标体系，如下：

三、问题确认

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) );

导入数据，以下是数据源：

链接：https://pan.baidu.com/s/1yLfmNDgrrKi4WncpcG5Reg 提取码：8888​pan.baidu.com/s/1yLfmNDgrrKi4WncpcG5Reg

前20条数据如下：

2、数据预处理

增加新列date_time（datetime），dates（char，年⽉⽇），便于后续时间维度分析

-- 增加新列date_time,dates alter table o_retailers_trade_user add column date_time datetime null; update o_retailers_trade_user set date_time =str_to_date(time,%Y-%m-%d %H) ; -- %H可以表示0-23;⽽%h表示0-12 alter table o_retailers_trade_user add column dates char(10) null; update o_retailers_trade_user set dates=date(date_time); desc o_retailers_trade_user; select * from o_retailers_trade_user limit 5;

重复值处理：创建新表a，并插⼊5W条⽆重复数据

-- 创建新表a，并插⼊5W条数据。 create table temp_trade like o_retailers_trade_user; insert into temp_trade select distinct * from o_retailers_trade_user limit 50000;

五、指标体系建设（SQL实现）

1、用户指标体系

1）基础指标：uv、pv、留存率（按⽇）统计

/* 需求：uv、pv、浏览深度（按⽇）统计 pv：统计behavior_type=1的记录数，需要按⽇统计（分组） uv: 统计distinct user_id 的数量，需要按⽇统计（分组）浏览深度：pv/uv */ -- pv 进⾏cout时候，如果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; -- ⽤户留存 with temp_table_trades as (select a.dates ,count(distinct b.user_id) as device_v ,count(distinct if(datediff(b.dates,a.dates)=0,b.user_id,null)) as device_v_remain0 ,count(distinct if(datediff(b.dates,a.dates)=1,b.user_id,null)) as device_v_remain1 ,count(distinct if(datediff(b.dates,a.dates)=2,b.user_id,null)) as device_v_remain2 ,count(distinct if(datediff(b.dates,a.dates)=3,b.user_id,null)) as device_v_remain3 ,count(distinct if(datediff(b.dates,a.dates)=4,b.user_id,null)) as device_v_remain4 ,count(distinct if(datediff(b.dates,a.dates)=5,b.user_id,null)) as device_v_remain5 ,count(distinct if(datediff(b.dates,a.dates)=6,b.user_id,null)) as device_v_remain6 ,count(distinct if(datediff(b.dates,a.dates)=7,b.user_id,null)) as device_v_remain7 ,count(distinct if(datediff(b.dates,a.dates)=15,b.user_id,null)) as device_v_remain15 ,count(distinct if(datediff(b.dates,a.dates)=30,b.user_id,null)) as device_v_remain30 from (select user_id ,dates from temp_trade group by user_id ,dates ) a left join ( select dates,user_id from temp_trade GROUP BY dates,user_id ) b on a.user_id = b.user_id where b.dates >= a.dates group by a.dates) select dates, device_v_remain0, concat(cast((device_v_remain1/device_v_remain0)*100 as DECIMAL(18,2)),%) as day_1%, concat(cast((device_v_remain2/device_v_remain0)*100 as DECIMAL(18,2)),%) as day_2%, concat(cast((device_v_remain3/device_v_remain0)*100 as DECIMAL(18,2)),%) as day_3%, concat(cast((device_v_remain4/device_v_remain0)*100 as DECIMAL(18,2)),%) as day_4%, concat(cast((device_v_remain5/device_v_remain0)*100 as DECIMAL(18,2)),%) as day_5%, concat(cast((device_v_remain6/device_v_remain0)*100 as DECIMAL(18,2)),%) as day_6%, concat(cast((device_v_remain7/device_v_remain0)*100 as DECIMAL(18,2)),%) as day_7%, concat(cast((device_v_remain15/device_v_remain0)*100 as DECIMAL(18,2)),%) as day_15%, concat(cast((device_v_remain30/device_v_remain0)*100 as DECIMAL(18,2)),%) as day_30% from temp_table_trades;

2）RFM模型

RFM模型：R部分

-- RFM模型 -- 1.建⽴r视图，将近期购买时间提取到R临时表中 drop view if EXISTS user_recency; create view user_Recency as select user_id ,max(dates) as rec_buy_time from temp_trade where behavior_type=2 group by user_id order by rec_buy_time desc; -- 2.建⽴R等级划分视图：将客户近期购买时间进⾏等级划分，越接近2019-12-18号R越⼤； drop view if exists r_clevel; create view r_clevel as select user_id,rec_buy_time,datediff(2019-12-18,rec_buy_time)as recen_num, (case when datediff(2019-12-18,rec_buy_time)<=2 then 5 when datediff(2019-12-18,rec_buy_time)<=4 then 4 when datediff(2019-12-18,rec_buy_time)<=6 then 3 when datediff(2019-12-18,rec_buy_time)<=8 then 2 else 1 end) as r_value from user_Recency;

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

-- 1.建⽴F视图 create view frenq_value as select user_id ,count(user_id) as buy_frenq from temp_trade where behavior_type=2 group by user_id; -- 2.建⽴F等级划分 create view f_clevel as select user_id,buy_frenq, (case when buy_frenq<=2 then 1 when buy_frenq<=4 then 2 when buy_frenq<=6 then 3 when buy_frenq<=8 then 4 else 5 end) as f_values from frenq_value; select * from f_clevel;

RFM模型：M部分

因为数据集种没有金额字段，所以无M部分

整合结果：

本次数据中通过最近消费（R）和消费频率（F）建⽴RFM模型

重要⾼价值客户：指最近⼀次消费较近⽽且消费频率较⾼的客户；重要唤回客户：指最近⼀次消费较远且消费频率较⾼的客户；重要深耕客户：指最近⼀次消费较近且消费频率较低的客户；重要挽留客户：指最近⼀次消费较远且消费频率较低的客户；

我们按照最近⼀次消费的均值和消费频率的均值定⾼低界限。

-- 将⽤户整合 -- 1.R平均值 SELECT avg(r_value) as r_avg FROM r_clevel; -- 2.7939 -- 2.F平均值select avg(f_values) as f_avg from f_clevel; -- 2.2606 -- 3.⽤户⼋⼤类等级划分，由于该数据没有M值，故只建⽴了4个分类 drop view if exists RFM_inall; create view RFM_inall as select a.*,b.f_values, (case when a.r_value>2.7939 and b.f_values>2.2606 then 重要⾼价值客户 when a.r_value<2.7939 and b.f_values>2.2606 then 重要唤回客户 when a.r_value>2.7939 and b.f_values<2.2606 then 重要深耕客户 when a.r_value<2.7939 and b.f_values<2.2606 then 重要挽留客户 END ) as user_class from r_clevel a, f_clevel b where a.user_id=b.user_id; SELECT count(user_id) as user_v,user_class from RFM_inall GROUP BY user_class;

2、商品指标体系

1）商品的点击量 收藏量 加购量 购买次数 购买转化（该商品的所有⽤户中有购买转化的⽤户⽐）

按照商品进⾏分组统计

-- 商品的点击量 收藏量 加购量 购买次数 购买转化 select * from temp_trade; select item_id, sum(case when behavior_type=1 then 1 else 0 end) aspv, sum(case when behavior_type=4 then 1 else 0 end) asfav, sum(case when behavior_type=3 then 1 else 0 end) ascart, sum(case when behavior_type=2 then 1 else 0 end) asbuy, 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 order by buy desc;

对应品类的点击量 收藏量 加购量 购买次数 购买转化（该商品品类的所有⽤户中有购买转化的⽤户

⽐）

select item_category, sum(case when behavior_type=1 then 1 else 0 end) aspv, sum(case when behavior_type=4 then 1 else 0 end) asfav, sum(case when behavior_type=3 then 1 else 0 end) ascart, sum(case when behavior_type=2 then 1 else 0 end) asbuy, 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 order by buy desc;

3、平台指标体系

1）行为指标

点击次数 收藏次数 加购物⻋次数 购买次数 购买转化（该平台当⽇的所有⽤户中有购买转化的⽤户⽐）

-- 每⽇的分析(1-4，分别表示点击pv、购买buy、加购物⻋cart、喜欢fav) select dates,count(1) as 每⽇的总数, sum(case when behavior_type=1 then 1 else 0 end) aspv, sum(case when behavior_type=2 then 1 else 0 end) asbuy, sum(case when behavior_type=3 then 1 else 0 end) ascart, sum(case when behavior_type=4 then 1 else 0 end) asfav, 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;

行为路径分析

-- ⾏为路径分析-- ⾏为路径组建基础视图 drop view product_user_way; create view product_user_way 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 ) 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_dn # 倒数第⼏个⾏为 from temp_trade ) a where a.rank_dn = 1 and behavior_type = 2; -- 查询该路径下有多少购买⽤户数 select concat( ifnull( lag_4, 空 ), "-", ifnull( lag_3, 空 ), "-", ifnull( lag_2, 空 ), "-", ifnull( lag_1, 空 ), "-", behavior_type ) as user_way, count( distinct user_id ) as user_count -- 该路径下购买⽤户数 from product_user_way group by concat( ifnull( lag_4, 空 ), "-", ifnull( lag_3, 空 ), "-", ifnull( lag_2, 空 ), "-", ifnull( lag_1, 空 ), "-", behavior_type );

六、结论

1、用户分析

UV异常分析：每⽇UV数据中，明显异常点为双⼗⼆活动造成，该影响为已知影响。对于UV周环⽐的分析：⽇常周环⽐数据⼤多⼤于0，说明⽤户程⼀定上升趋势，其中如11⽉26⽇、12⽉2⽇、12⽉7⽇等的数据为下降数据，需要结合其他数据做进⼀步的下降原因分析。双⼗⼆活动后⽤户周环⽐会相应下降，为正常原因。

猜测可能的问题有：

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

2、用户精细化运营

通过RFM模型中的⽤户最近⼀次购买时间、⽤户消费频次分析，分拆得到以下重要⽤户。

结论：

共挖掘重要客户165人。后续工作重点，需要对高价值客户（44人）做VIP服务设计，增加用户粘性同时通过设计优惠券提升客户消费；对深耕客户（44人）做广告、推送刺激，提升消费频次；对挽留客户（66人）做优惠券、签到送礼策略，增加挽留用户粘性；对唤回客户（11人）做定向广告、短信召回策略，尝试召回用户。

3、商品分析

热销商品品类如下所示。

其中5027、5399品类购买转化率较其余商品品类偏低，需要结合更多数据做进⼀步解读。（可能的原因：品类⾃有特性导致⽤户购买较低，⽐如⾮必需品、奢侈品等等）

4、产品功能路径分析

以下是产品的主要消费转化路径。

可以发现用户以直接购买转化为主。重新购买（第4名、第6名）和加购物车转化（第4、第6名）都有一定的排名。在所有的消费路径中，58%的用户有加入购物车的行为；但仅有0.6%的用户在加入购物车之后发生购买；购物车相关产品的后续改进会与产品经理沟通进行。

本篇文章为大家提供了测试数据，出于安全考虑，百度网盘链接的有效期为30天，如果过期，可以在评论区评论哦，看到后会回复大家~

写文章不容易！写好的文章更不容易！希望大家多多点赞收藏评论喜欢！

MySQL技能篇：

数据分析方法篇：

数据分析案例篇：