淘宝客户服务案例分析「JAVA实战项目」

项目背景

随着电商⾏业的发展度过红利期，新客获取的流量成本⾼居不下，对消费品牌的直接影响则是销售贡献中增量的减少。

为了弥补乏⼒的增⻓，各个品牌越来越重视对存量⼈群进⾏精细化运营，因为与获取新客相⽐，对存量⽼客的运营维护成本更低，反⽽能带来更⾼的转化率。

此次项⽬需求⽅正是来⾃某电商公司的⽤⼾运营部⻔，希望数据分析师基于对⽤⼾⾏为数据的挖掘，形成数据结论后，输出⼀套能提⾼运营效率的策略建议赋能业务增⻓。

数据说明

数据来源

此案例中的⽤⼾⾏为数据集来源如下：

https://tianchi.aliyun.com/dataset/dataDetail?dataId=649

字段说明

此数据集中已有的字段及behavior_type值域如下：

分析思路

项目整体的分析思路是标准的数据分析流程：明确问题-分析原因-落地建议。

在做数据分析时，需要有结构、有主线地进⾏，这样得出的结果才能找到依据。如上学时语⽂⽼师在作⽂课上经

常强调的 “总-分-总”⾏⽂结构，在数据分析⾥，同样也是在遵循这样的原则：

•【总】 - 先进⾏全局的分析，对总体现状有认识；

◦ 这个阶段对应数据分析流程中的【明确问题】：通过对整体数据的分析，透视业务现状，并结合分析框架

明确业务问题；

•【分】 - 基于数据全貌的观察，找到问题点进⾏针对性分析

◦ 这个阶段对应数据分析流程中的【分析原因】：把复杂的业务问题通过多维度分析⽅法拆解成多个⼩问

题，并选择合适的分析⽅法、分析模型逐⼀对问题进⾏分析

•【总】 - 最后汇总并总结各部分的分析，并以业务⽬标为中⼼提建议

◦ 这个阶段对应数据分析流程中的【落地建议】：分析得到的数据结论，需要结合业务场景，转成可落地执

⾏的策略建议，帮助赋能业务增⻓。

数据清洗什么是数据清洗？

数据清洗是指找出数据中的【异常值】并【处理】它们，使数据应⽤层⾯的结论更贴近真实业务。

异常值：

•不规范的数据，如空值、重复数据等

•不符合分析要求的数据，如此案例中，需要分析11⽉25⽇-12⽉3⽇之间的用户行为，在此时间段之外的⾏为

都不应该被纳⼊分析

处理异常值：

•⼀般情况下，对于异常值，直接删除即可，本案例中将采取此⽅式进⾏异常值处理

•对于特殊情况，要根据业务要求按需处理，暂不展开

异常值查找及处理

•查找是否存在重复数据

select

user_id, item_id, time_stamp

from userbehavior

group by

user_id , item_id, time_stamp

having count(*) > 1;

•删除重复值

alter table userbehavior

add id int primary key auto_increment

delete

from userbehavior

where id in

(select id from

(select id, user_id, item_id, time_stamp, row_number() over(partition by user_id, item_i

d, time_stamp) '重复值'

from userbehavior) as temp

where 重复值 >1)

•查找是否存在空值

select * from userbehavior

where user_id is null

or category_id is null

or item_id is null

or behavior_type is null

or time_stamp is null ;

•查找是否存在超出分析时间的记录

dates 字段：行为发⽣的⽇期，如'2017-11-25' 为2017年11⽉25⽇

hours 字段：行为发⽣的⼩时时间，如'21'为晚上9点

datetime 字段：行为发⽣的⽇期时间，如'2017-11-25 21:30:00' 为2017年11⽉25⽇晚上9点30分

weekday 字段：行为发⽣在星期几，如'1' 为星期⼀

因为给定的数据中，时间相关的字段是时间戳类型，⽽时间戳是指格林威治时间1970年01⽉01⽇00时00分00秒

(北京时间1970年01⽉01⽇08时00分00秒)起⾄现在的总秒数，无法直接与日期进行比较，因此，需要对其进行

格式化转换，才能继续分析。

时间戳的处理

此处，为了⽅便后⽂更复杂的⽇期分析，这⾥⽤函数 FROM_UNIXTIME(时间戳，时间格式) 将时间戳转为不同格

式的时间，相关字段说明如下：

alter table userbehavior add dates varchar(255);

update userbehavior

set dates = from_unixtime(time_stamp,'%Y-%m-%d');

alter table userbehavior add hours varchar(255);

update userbehavior set hours = from_unixtime(time_stamp,'%H');

-- 特别注意，此处的时间格式中“⼩时”部分的字符需要⽤⼤写的H，才能转为24⼩时制

alter table userbehavior add datetime varchar(255);

update userbehavior

set datetime = FROM_UNIXTIME(time_stamp,'%Y-%m-%d %H:%i:%s');

/*

- mysql中，可以通过函数 WEEKDAY() 将⽇期转为“星期⼏”，但是索引是从0开始，

即0表⽰星期⼀、1表⽰星期⼆...

- 为了使该字段更符合阅读习惯，可以将其进⾏加1操作，结果即为1对应星期⼀，

2对应星期⼆...

*/

alter table userbehavior add weekday varchar(255);

update userbehavior set weekday = weekday(datetime) 1;

转换结果如图所示：

剔除超出时间范围的数据

有了⽇期字段后，就可以直接将超出指定时间范围的数据剔除掉。

select *

from userbehavior

where dates<'2021-11-25' or dates>'2021-12-03' ;

delete from

userbehavior where dates<'2021-11-25' or dates>'2021-12-03'

⾄此，已完成数据清洗⼯作，剩余的数据便是可以直接⽤来分析得“纯净”数据。

第⼀部分：【总】⸺明确问题

⼀般来说，此部分要求我们对业务有个整体的认识，在本案例中，主旨是进行用户分析，所以要知道总共有多少

会员、他们分布情况如何等。

会员总数

按如下语句，即可统计出现有业务中，共有983名会员在2017年11⽉25⽇-12⽉3⽇期间发⽣了动作。

SELECT COUNT(DISTINCT user_id) '会员数' FROM userbehavior

AIPL漏⽃分析

知道整体有多少会员后，接下来要进⼀步对他们的⾏为进⾏观察，因为分析的是淘宝⾏为，在电商领域⽐较常⽤

的⽅法是阿⾥提出的AIPL营销模型：

•A - Awareness 认知：浏览

•I - Interested 兴趣：收藏、加购

•P - Purchase 购买：购买

•L - Loyalty 忠诚：复购

AIPL营销模型可以很好地将会员⾏为与相应地运营周期关联起来，例如 【AI】部分即为运营中的拉新，即处于AI

阶段 即浏览、收藏、加购的⽤⼾数越多，运营拉新指标就完成得越好，但此部分同样意味着拉新成本的⽀出；⽽

【PL】则对应着运营的收割⾏为，通俗地讲，即是将前期推⼴引进的新客变现，以此回收拉新成本，体现ROI。

这样，我们就可以将会员的浏览、收藏、加购、购买消费划⼊到AIPL的各个流程中，便于我们根据不同的流转情

况，对运营现状有整体的把握。

为了⽅便进⾏复杂分析，需要对表结构进⾏【⾏列互换】，此操作通过【创建视图】来完成：

CREATE VIEW behavior AS

SELECT user_id, datetime, dates, hours,

max(CASE behavior_type when 'pv' then 1 else 0 end) 'view',

max(CASE behavior_type when 'fav' then 1 else 0 end) 'favor',

max(CASE behavior_type when 'cart' then 1 else 0 end) 'cart',

max(CASE behavior_type when 'buy' then 1 else 0 end) 'buy'

FROM

userbehavior

GROUP BY user_id, datetime;

------------------------------------

select * from behavior

AIP计算

因为AIPL中的“L”为会员复购⾏为，需要单独计算。

所以这⾥仅对A、I、P 阶段的⾏为进⾏统计，结果如图：

select sum(pv) 'A',

sum(favor) sum(cart) 'I', sum(buy) 'P'

from behavior

L计算：

对于会员复购⾏为，计算思路是：

•通过【窗⼝函数】统计会员的每⼀次消费动作，即记录第n次消费

•有了每个会员的第n次消费记录字段后，即可将第⼀次消费 列为⾸次消费，⽽第⼆次开始的皆为【复购消费】

•最后统计【复购消费】⾏为，即为“L”即可

第N次消费计算：

如下语句，通过dense_rank() 窗⼝函数对对会员按每次消费时间进⾏排序，即可得到每个会员的第n次消费，将

字段命名为 n_consume；并⽤查询语句新建视图consume

create view consume as

select user_id, datetime, buy,

dense_rank() over(partition by user_id

order by datetime) as 'n_consume'

from behavior where buy=1

order by user_id, datetime;

-------------------------------------------------

select * from consume

统计L行为

从中提取复购的次数，即 n_consume ⼤于1的行为都算进L中，计算结果如图：

select sum(buy) 'L'

from consume where n_consume >1

为了提⾼阅读效率，根据前面计算出来的AIPL数据：

整体分析

整体分析的⽬的是为了找出⽤⼾运营存在的问题或特点，进⽽提出分析⽬标，并以此作为接下来进⼀步分析的⽬

标。

在整体分析到提出⽬标的过程中，需要有【象限思维】，即按轻重缓急列出问题的重要性排序。这样才能有序地

分析，业务才能在有限资源的条件下选择分析结论进⾏落地实践。

⼀般来说，需要应⽤对⽐分析来判断⽤⼾运营结果的好坏，如同⽐、环⽐等，因此案例中缺少了相关数据，故⽆

法使⽤对⽐分析，仅从业务经验出发进⾏判断。

回到此案例中，有了AIPL漏⽃图后，我们便可直观地发现⽤⼾运营的情况：

1. 【拉新环节 A→I】 部分做得较差，可能拉新完成率较低，值得注意。将该问题列为优先级：重要紧急 P0

2. 【收割之客⼾复购 P→L】 部分做得很好好，可以据此总结业务经验或复购规律，进⼀步推⼴应⽤。将该事项

列为优先级：重要不紧急 P1

3. 【收割之客⼾⾸购 I→P 】部分效果还可以，可以进⼀步挖掘⽤⼾购买转化路径。将该事项列为优先级：不重

要不紧急 P2

第⼆部分：【分】⸺分析原因分析思路

此部分，需根据“第⼀部分：【总】” 得出的分析⽬标，指定细分的分析策略：

1. 【P0: A→I 】部分是转化率低的问题，在零售⾏业中，【⼈货场】分析是最常⽤且有效的分析⽅法，故此案

例也将应⽤该⽅法进⾏分析；

2. 【P1: P→L】部分是⽤⼾复购表现很好，所以可以通过【⽤⼾复购分析】，挖掘、总结复购特征；

3. 【P2: I→P】部分⽬的在与进⼀步透析⽤⼾购买⾏为，可以采⽤【AIPL RFM】模型进⾏分析。

【P0: A→I 】

此部分计划通过【⼈货场】分析，解决A认知⽤⼾到I兴趣⽤⼾⾏为转化率低的问题。

【⼈货场】分析

⼈

【⼈】的部分主要在于观察用户行为特点，以总结经验。此案例中，因为数据维度有限，会员⾃⾝⾏为相关的数

据除了商品外就是时间，⽽商品计划在【货】部分进⾏分析，故此部分旨在分析⽤⼾A到I⾼效转化的时间特征。

各时段AI转化分布

如以下语句，结合group by 语句，即可计算各时段AI转化情况。

select A.hours, A浏览⾏为, I兴趣⾏为,

concat(round((I兴趣⾏为/A浏览⾏为),3)*100,'%') as 'A→I转化率'

from

(select hours, count(*) 'A浏览⾏为'

from behavior

where pv=1 group by hours) A

left join

(select hours, count(*) 'I兴趣⾏为'

from behavior

where favor=1 or cart=1 group by hours) I

on A.hours = I.hours

order by A.hours

​

​

计算结果中，有很多不同的值。根据对⽐思维，我们需要【⽐较基准】，才知道这些转化率哪些⾼，哪些低，才

可进⼀步判断。

这⾥，以常⻅的“平均值”作为对⽐的基准，计算语句如下：

select Avg(A浏览⾏为) 'A浏览⾏为 平均值',

Avg(I兴趣⾏为) 'I兴趣⾏为 平均值',

concat(round(AVG(I兴趣⾏为)/AVG(A浏览⾏为),3)*100,'%') as 'A→I转化率 平均值'

from

(select hours, count(*) 'A浏览⾏为'

from behavior

where pv=1 group by hours) A

left join

(select hours, count(*) 'I兴趣⾏为'

from behavior

where favor=1 or cart=1 group by hours) I

on A.hours = I.hours

order by A.hours;

得到结果如图所示：

​

因每个时段的浏览⼈数不同，为确保分析的结果有效，故需选择 浏览值⼤于平均值3735的时段来观察：在这些

时段中， 我们认为转化率⼤于 平均值9.1%的时段，既是浏览⼈数多，⼜是转化率⾼的时段，即11点、15-17

点、19点、22-23点。其中，23点为转化率⼤于10%，为最⼤值，可作为最佳投放时间。

​

货

此部分需要对【⼈货匹配】情况进⾏分析，进⽽可针对性地将货品根据会员⾏为进⾏调整。

总商品数统计

在分析之前，需要先对AI阶段涉及的总商品情况进⾏观察：

SELECT count(DISTINCT item_id) 'A' FROM userbehavior

WHERE behavior_type ='pv'

接下来，计算用户感兴趣的商品：

SELECT count(DISTINCT item_id) 'I' FROM userbehavior

WHERE behavior_type IN ('favor','cart')

接下来，结合【假设验证思维】，基于有效浏览机产⽣兴趣的商品数差异较⼤的情况，提出假设：⽤⼾偏好的商

品主要集中在少数商品，⽽⼤部分⻓尾商品则是被错误地推荐到⽤⼾。

验证思路：

1. 将被浏览最多的前100款商品 以及 ⽤⼾最感兴趣(即加购、收藏)的前100款商品 进⾏交叉查询

2. 如果交叉出的商品数较少，则假设成⽴

3. 反之，则假设不成⽴

按如下语句可提取被浏览最多的前100款商品，类似地，亦可提取⽤⼾最感兴趣的前100款商品

-- 被浏览最多的前100款商品

select item_id, count(*) 'A'

from userbehavior

where behavior_type = 'pv'

group by item_id

order by A desc

limit 100;

-- ⽤⼾最感兴趣的前100款商品

select item_id, count(*) 'I'

from userbehavior

where behavior_type IN ('favor','cart')

group by item_id

order by I desc

limit 100

按验证思路⾥说的，分别A, I 热⻔的前100款单品交叉，查看交叉数量：

select count(*) 'A & I 交叉商品数'

from

(select item_id, count(*) 'A'

from userbehavior

where behavior_type = 'pv'

group by item_id

order by A desc

limit 100) A

inner join

(select item_id, count(*) 'I'

from userbehavior

where behavior_type IN ('favor','cart')

group by item_id

order by I desc

limit 100 ) I

on A.item_id = I.item_id

​

结果显⽰，仅有5款商品既是⾼浏览量，也是⾼收藏/加购的，故假设成⽴：⽤⼾偏好的商品主要集中在少数商

品，⽽⼤部分⻓尾商品则是被错误地推荐到⽤⼾。进⽽可得出【⼈货匹配】效率低的结论。

场

【场】⼴义来说指的是与⽤⼾的触点，例如在线下零售业，则是⻔店; 在线上电商，则是在线店铺、平台、投放

渠道等。

从这个⻆度，分析此段时间淘宝平台的活动情况：

1. 结合业务经验，数据时间段在双⼗⼀之后，双⼗⼆之前，即是两个⼤促的中间节点

2. 查询2017年双⼗⼆的相关信息如下：

•活动：天猫双12年终品牌盛典

•预热时间：2017年12⽉7⽇00:00:00-2017年12⽉11⽇23:59:59；

•上线时间：2017年12⽉12⽇00:00:00-2017年12⽉12⽇23:59:59。

3. 由活动时间可知，分析时间段处于双⼗⼆预热时间前⼣的低潮期，客观上营销效果整体⽐较平淡，进⽽影响AI

转化率。

分析小结

•⼈：为提⾼转化率，可选择在 浏览值⼤于3735 且 转化率⼤于 9.1%的时间段加⼤投放⼒度；其中，23点为转

化率⼤于10%，为最佳投放时间

•货：⼈货匹配效率低，平台⼤部分商品为⻓尾商品，但它们并不能很好地吸引⽤⼾兴趣，需要进⼀步优化商

品信息以提升转化率。

•场：从平台活动周期⻆度来说，分析时间段正处于⼤促活动预热前的低潮期，此客观因素也在⼀定程度上导

致转化率低。

【P1: P→L】

根据此前制定的分析思路，PL部分计划进⾏【⽤⼾复购分析】以挖掘复购特征。

复购周期分析

结合具体业务，复购分析的⽬的是为了优化⽤⼾触达策略，即何时何地向哪些已购⽤⼾推送什么商品可提升复购

率。此案例中，受数据限制及考虑受众，暂不对商品推荐进⾏建模分析。将通过分析⽤⼾【复购周期】以解决业

务中触达时间的问题。

现该部分问题转化为分析⽤⼾复购周期问题，对于此，从业务⻆度主要包括何时触达、触达多久两部分。

(⼀) 何时触达

可通过计算⽤⼾平均回购周期，即可在⽤⼾发⽣购买⾏为后，在平均回购周期内对其进⾏营销触达。

为了计算⽅便，这⾥我们新建⼀个记录⽤⼾第N次购买的视图取名为consume_dates

注意，这⾥与开头计算AIPL的视图consume的区别，在于这⾥的窗⼝函数，是基于dates这个字段，也就是按⽇

期去对⽤⼾购买⾏为进⾏排序

create view consume_dates as

select distinct user_id, dates,

dense_rank() over (partition by user_id

order by dates ) as n_consume

from behavior where buy = 1;

-----------------------------------

select * from consume_dates

计算步骤：

1. 通过以下语句先计算出每个⽤⼾每次消费的回购周期

select a.user_id, a.dates, a.n_consume,

datediff(a.dates, b.dates) '回购周期（天）'

from consume_dates as a,

consume_dates as b

where a.user_id = b.user_id

and b.n_consume = a.n_consume-1

结果说明：

如图，user_id 为100的⽤⼾ n_consume=2，回购周期=2天 即为期第⼆次消费时，与第⼀次消费间隔了2天，也

就是说该⽤⼾在2天前进⾏了第⼀次消费。

2. 基于以上语句，可以将每⼈次的回购周期进⾏平均，取得最终的 平均回购周期 ：

select avg(datediff(a.dates, b.dates)) '平均回购周期（天）'

from consume_dates as a,

consume_dates as b

where a.user_id = b.user_id

and b.n_consume = a.n_consume-1

结论：即在⽤⼾购买⾏为后的2-3天内对其进⾏触达。

(⼆) 触达多久

可通过计算不同时段下购买的客⼾最⻓购买周期，供业务触达时参考。1. 统计每个客⼾“⾸次消费⽇期”、“最后消费⽇期”后，相减即可算得每个客⼾的消费周期，命名为“最⻓消 费间隔”。

select a.user_id,

a.dates '⾸次消费⽇期',

最后消费⽇期,

datediff(最后消费⽇期,a.dates) '最⻓消费间隔'

from

(select user_id, dates from consume_dates where n_consume=1) a

join

(select user_id, MAX(dates) '最后消费⽇期' from consume_dates where n_consume >1

group by user_id) b

on a.user_id = b.user_id

结果如下：

2. 基于1.中语句，按⽇期进⾏分组统计，计算得每天消费的客⼾平均最⻓消费间隔。

SELECT `⾸次消费⽇期`,

WEEKDAY(`⾸次消费⽇期`) 1 '星期⼏',

AVG(`最⻓消费间隔`) '平均最⻓消费间隔'

FROM (

SELECT

a.user_id,

a.dates '⾸次消费⽇期',

`最后消费⽇期`,

DATEDIFF(`最后消费⽇期`,a.dates) '最⻓消费间隔'

FROM

(SELECT user_id, dates FROM consume WHERE n_consume=1) a

LEFT JOIN

(SELECT user_id, MAX(dates) '最后消费⽇期'

FROM consume GROUP BY user_id) b

ON a.user_id = b.user_id

-- 仅针对复购⼈群

WHERE a.user_id IN

(SELECT DISTINCT user_id FROM consume WHERE n_consume>1)

) t

GROUP BY `⾸次消费⽇期`

ORDER BY `⾸次消费⽇期`

结果如图，在11-25 ⾸购的客⼾，最⻓消费间隔最⻓为5.9天。

【P2: I→P】

有此前漏⽃分析可知，平台⽤⼾复购率⾼达63%，也就是说⽤⼾只要突破0到1的消费，接下来就有63%的机会复

购，所以此部分重点应放在如何提升AI→P。

1. 用户首购特征

为了提⾼AI客⼾的购买转化率，可以通过⾼购买率客⼾与低购买率客⼾对⽐，挖掘⾼购买率客⼾特征，进⽽指导

业务运营策略。

先创建视图，以统计每个客⼾的购买率及其他数据。语句如下：

create view user_feature as

select user_id, sum(pv) '浏览', sum(favor) sum(cart) '收藏加购',

sum(buy) '购买', (sum(cart) sum(favor))/sum(pv) '加购率',

sum(buy)/(sum(pv) sum(cart) sum(favor)) '购买率',

dense_rank() over(order by sum(buy)/(sum(pv)

sum(cart) sum(favor)) desc) '购买率排序'

from behavior group by user_id;

------------------------------

select * from user_feature

order by 购买率 desc

购买率⾼ vs 购买率低

计算好每个客⼾的购买率后，需要进⼀步定义，何为⾼购买率、何为低购买率？

结合【⼆⼋原则】，认为前20%为⾼购买率客⼾，后20%为低购买率客⼾。

由此前统计可知，分析数据共有386个客⼾，即20%分位为：

•389*0.2 =77，即前77位客⼾为⾼购买率客⼾;

•389-77 = 312，即312-389位的客⼾为低购买率客⼾。

⾼购买率客⼾ 购买率特征：

select

avg(浏览) '平均浏览数', avg(收藏加购) '平均兴趣数',

avg(加购率) '平均加购率'

from user_feature where 购买率排序<=77

⾼购买率客⼾品类集中度：

select count(distinct category_id) '购买品类集中度'

from userbehavior where behavior_type ='buy'

and user_id in ( select distinct user_id from user_feature where 购买率排序<=77)

低购买率客⼾ 特征：

注意需剔除⽆购买⼈群影响

select

avg(浏览) '平均浏览数', avg(收藏加购) '平均兴趣数',

avg(加购率) '平均加购率'

from user_feature where 购买率排序>=312 and 购买率>0

低购买率客⼾品类集中度：

select count(distinct category_id) '购买品类集中度'

from userbehavior where behavior_type ='buy'

and user_id in ( select distinct user_id from user_feature where 购买率排序>=312 and 购买率>0

)

总结：

1. ⾼购买率客⼾浏览商品数36远⼩于低购买率客⼾浏览商品数258

2. ⾼购买率客⼾平均加购率11% ⼤于 低购买率客⼾平均加购率7%

3. ⾼购买率客⼾购买品类集中度399 远⼤于低购买率客⼾购买品类集中度96综上所述可得⼈群画像：⾼购买率⼈群浏览商品数少，但加购率⾼，⼤概率为决策型顾客，看对了商品就下⼿购

买；⽽低购买率⼈群更多是纠结型顾客，需要反复浏览商品来确认最终想要的。

此外，低购买率⼈群品类集中度⾼，可列出这些品类，有⽬的性地优化品类信息，减少⽤⼾浏览跳失率。

2. AIPL RFM分析

为促进⽤⼾购买，需进⾏精细化⽤⼾运营，即可通过RFM模型实现。

由于数据限制，并⽆⽤⼾购买⾦额数据，故此处可与AIPL模型结合，重新定义R、F、M：

•R：客⼾最近⼀次购买离分析⽇期的距离，⽤以判断购买⽤⼾活跃状态

•F：客⼾收藏、加购⾏为次数

•M：客⼾购买⾏为次数

因此，RFM分群结果及⼈群特征如下：

1. 重要价值⽤⼾（R⾼F⾼M⾼）：⽤⼾处于购买活跃期，感兴趣的商品多，购买次数也多

2. ⼀般价值⽤⼾（R⾼F⾼M低）：⽤⼾处于购买活跃期，感兴趣的商品多，购买次数少

3. 重要发展⽤⼾（R⾼F低M⾼）：⽤⼾处于购买活跃期，感兴趣的商品少，购买次数多

4. ⼀般发展⽤⼾（R⾼F低M低）：⽤⼾处于购买活跃期，感兴趣的商品少，购买次数少

5. 重要唤回⽤⼾（R低F⾼M⾼）：⽤⼾已不活跃，感兴趣的商品多，购买次数也多

6. ⼀般唤回⽤⼾（R低F⾼M低）：⽤⼾已不活跃，感兴趣的商品多，购买次数少

7. 重要挽留⽤⼾（R低F低M⾼）：⽤⼾已不活跃，感兴趣的商品少，购买次数多

8. ⼀般挽留⽤⼾（R低F低M低）：⽤⼾已不活跃，感兴趣的商品少，购买次数少

（1）按以上定义，计算出每个客⼾的R、F、M值：

•计算R值：

select user_id, dates '最后消费⽇期', datediff('2021-12-03', dates) 'R'

from consume_dates a

where n_consume = ( select MAX(n_consume) from

consume_dates b where a.user_id = b.user_id )

•计算F、M值

select user_id,

sum(favor) sum(cart) 'F', sum(buy) 'M'

from behavior group by user_id

•将前两个表合并，写成⼀个语句，并新建视图

create view RFM as

select r.user_id, R, F, M

from

(select user_id, dates '最后消费⽇期', datediff('2021-12-03', dates) 'R'

from consume_dates a

where n_consume = ( select MAX(n_consume) from

consume_dates b where a.user_id = b.user_id )) r

left join

(select user_id,

sum(favor) sum(cart) 'F', sum(buy) 'M'

from behavior group by user_id ) fm

on r.user_id = fm.user_id;

----------------------

select * from RFM

（2）为了划分R、F、M值的⾼ 与低，这⾥采⽤常⽤的平均值法作为阈值，有条件的同学可以通过聚类算法计算

更准确的阈值。语句如下：

select avg(R), avg(F), avg(M) from RFM

（3）根据以上RFM阈值，及RFM定义，将每个客⼾划分到不同的⽤⼾价值族群中。

create view user_rfm as

select user_id, case when R < 2.4501 and F>9.3115 and M>2.8018 then '重要价值⽤⼾'

when R < 2.4501 and F>9.3115 and M<2.8018 then '⼀般价值⽤⼾'

when R < 2.4501 and F<9.3115 and M>2.8018 then '重要发展⽤⼾'

when R < 2.4501 and F<9.3115 and M<2.8018 then '⼀般发展⽤⼾'

when R > 2.4501 and F>9.3115 and M>2.8018 then '重要唤回⽤⼾'

when R > 2.4501 and F>9.3115 and M<2.8018 then '⼀般唤回⽤⼾'

when R > 2.4501 and F<9.3115 and M>2.8018 then '重要挽留⽤⼾'

when R > 2.4501 and F<9.3115 and M<2.8018 then '⼀般挽留⽤⼾'

end as '⽤⼾价值族群' from RFM;

-------------------

select * from user_rfm

结果如图：

（4）统计各个族群的⽤⼾数及⽤⼾占⽐，⽤于对⽤⼾运营现状进⾏分析，并给出运营策略⽅向。

select ⽤⼾价值族群, count(user_id) '⽤⼾数',

concat(round(count(user_id)/983*100,3),'%') '⽤⼾数占⽐'

from user_rfm

group by ⽤⼾价值族群 order by ⽤⼾价值族群

如图为RFM模型分群结果，对⽐分析需要对照组进⾏⽐照分析，因数据限制，此处结合业务经验进⾏判断：

如图为RFM模型分群结果，对⽐分析需要对照组进⾏⽐照分析，因数据限制，此处结合业务经验进⾏判断：

1. “重要”型⽤⼾累计占⽐26.8%（3.1% 3.5% 7.4% 12.8%），符合⼆⼋规律

2. 从【⽤⼾RFM转化路径】⻆度来看：重要价值⽤⼾占⽐较少，⽽主要由⼀般价值⽤⼾、重要发展⽤⼾、重要

唤回⽤⼾转化⽽来

•⼀般价值、重要唤回⽤⼾基数较多，可优先对两个⼈群进⾏“升级”

•其次，重要发展⽤⼾数量少，需要从基数较多的⼀般发展⽤⼾转化，之后再考虑将重要发展转为重要价值

第三部分：【总】⸺落地建议

⾸先，通过AIPL营销模型，对⽤⼾现状有整体的认识，并分析、总结出三个分析⽬标。现对分析结论结合业务场

景总结经验及提出建议：

【P0: A→I】【⼈货场】业务建议

⸺从“认知”到“兴趣”阶段的转化率过低

通过【⼈货场】分析，可得以下业务建议：

•推⼴部⻔：优化投放策略，在A→I转化率⾼的时间点，尤其是23点加⼤投放⼒度，提升AI转化率，进⽽提升

整体转化

•产品部⻔：针对【⼈货匹配】效率低的问题，及时处理低转化率商品，总结⾼转化产品特性，开发新品

•运营部⻔：针对【⼈货匹配】效率低的问题，参考⾼转化商品描述，优化产品标题、内容描述; 针对⼤促前低

潮期，提出吸引性促销政策，提⾼此阶段销售

【P1: P→L】【复购分析】业务建议

⸺从“购买”到“忠诚”阶段的转化率⾼。

通过【⽤⼾复购分析】后，可得以下建议：

⽤⼾部⻔：针对⽤⼾复购周期特性，客⼾消费后2-3天内结合回馈⼿段及时进⾏触达; 此外，对于周六⾸购客⼾，

最⻓消费周期达6天，在此周期内可对消费客⼾连续优惠触达

【P2: I→P】【购买特征 RFM】业务建议

⸺表现较好。

通过以下分析，可得建议如下

(1) 分析【⾼购买率⽤⼾特征】：

•⽤⼾部⻔：

◦ 针对⾼购买率客⼾，即决策型客⼾，可能更多为【⻓尾商品领域】，可配合精准商品推荐模型，触达优质

⻓尾商品，提升购买率;

◦ 针对低购买率客⼾，即纠结型，可能更多为【热⻔商品领域】，触达热⻔商品，提⾼购买率

(2) ⽤【AIPL RFM】模型：

对于⽤⼾部⻔，根据此前结合⽤⼾RFM转化路径分析结论制定提升策略：

•优先级P0:

◦ 重要价值⽤⼾（R⾼F⾼M⾼）维护：⽤⼾处于购买活跃期，感兴趣的商品多，购买次数也多 → 针对兴趣

产品，保持⼀定频率的触达，持续维护

◦ ⼀般价值⽤⼾（R⾼F⾼M低）：⽤⼾处于购买活跃期，感兴趣的商品多，购买次数少 → 针对该部分客

⼾，⽬的就是要让他们购买，所以可以和运营部⻔沟通，降低利润，给出有⼒折扣促进购买，进⽽转为重

要价值⽤⼾。

•优先级P1:

◦ ⼀般发展⽤⼾（R⾼F低M低）：⽤⼾处于购买活跃期，感兴趣的商品少，购买次数少 → 可通过商品组合

推荐，即通过连带提升该⼈群购买频次，进⽽转为重要发展⽤⼾

◦ 重要发展⽤⼾（R⾼F低M⾼）：⽤⼾处于购买活跃期，感兴趣的商品少，购买次数多 → 通过关联算法，

找出与兴趣商品关联⾼的商品进⾏推荐触达，提⾼⽤⼾收藏、加购数，进⽽转为重要价值⽤⼾