【面试题】下面是2020年1月份某电商平台的用户行为记录表。

【面试题】下面是2020年1月份某电商平台的用户行为记录表。

表中的数据如下：

业务问题：店铺在对用户进行盘点时发现，用户运营过于粗放，没能做到用户分类运营。老板想在下一个月对不同的用户进行有针对性的营销，达到降低成本提高收入，精细化运营的效果。怎么办？

【分析思路】

要想对用户进行精细化运营，就要对用户分类，然后对不同的用户采取不同的运营策略。

涉及到用户分类，可以用RFM分析方法。

一.什么是RFM分析方法？

RFM分析方法是根据用户的最近一次消费时间间隔(R)、消费频率(F)、消费金额(M)来对用户进行打分，进而将用户按分数划分为不同的类型，然后对不同的用户使用不同的运营策略，从而实现辅助精准运营。

RFM分析方法将用户分为以下8类：

本文重点是介绍如何用SQL实现RFM的案例应用，所以在看下面内容之前，确保你已经学会了《RFM分析法原理：如何对用户按价值分类？》

二.如何用SQL实现RFM分析方法？

1.定义指标R、F、M

将指标定义中的“一段时间“定义为1月份（30天）。

最近一次消费时间间隔(R)：用户最近一次消费距离现在（1月30日）多长时间了。

消费频率(F)：用户一段时间内（1月份）消费了多少次。

消费金额(M)：用户一段时间内（1月份）的消费金额，对应表中的“消费金额“。

2.计算R、F、M值

根据RFM分析方法指标的定义，只需要“用户行为类型”中的“购买”值，可以先筛选(where)出用户的所有“购买”记录（下图红框的地方），做为临时表a。

select * from 用户行为记录表 where 用户行为类型=’购买’;

查询结果：

（1）最近一次消费时间间隔(R)

R值：用户最近一次消费距离现在（1月30日）多长时间了。

R值=现在（2020-01-30）- 每个用户最后一次购物日期

每个用户最后一次购物日期，涉及到“每个问题“要想到《猴子 从零学会SQL》里讲过的用分组汇总来实现。

根据“用户编号”分组，然后汇总（最后一次购买日期，就是找到日期的最大值，用汇总函数max）

select用户编号,max(用户活跃日期)as最后一次购物日期fromagroupbya.用户编号;

将该查询结果表示为临时表b。

然后就可以分析出R值=现在（2020-01-30）- 每个用户最后一次购物日期

select 用户编号,('2020-01-30'-b.最后一次购物日期) as 最近一次消费时间间隔Rfrom b;

（2）消费频率(F)

消费频率(F)：用户一段时间内（1月份）消费了多少次。

先筛选（where）出30天内所有的购买记录。

select用户编号fromawheredatediff('2020-01-30',用户活跃日期)<=30;

然后需要分析出“每位用户的购买次数。“每位用户”按用户编号分组（group by），然后用汇总函数（count）求出购买次数。

select 用户编号,count(*) as 消费频率Ffrom a where datediff('2020-01-30',用户活跃时间)<=30group by 用户编号;

（3）消费金额(M)

消费金额(M)：用户一段时间内（1月份）的消费金额，对应表中的“消费金额“。

先筛选（where）出30天内所有的购买记录。

select用户编号fromawheredatediff('2020-01-30',用户活跃日期)<=30;

然后，分析出“每位用户的总消费金额”，按用户编号分组（group by），用汇总函数（sum）求出总消费金额。

select用户编号,sum(消费金额)as总消费金额Mfromawheredatediff('2020-01-30',用户活跃日期)<=30groupby用户编号;

至此，我们已经获得了每一个用户的R、F、M值，将其合并起来设为表c，以便后续查询：

2. 给R、F、M按价值打分

本案例的R、F、M打分规则定义如下：

打分过程及预期打分结果：

为了实现上面的打分，可以使用SQL语句的条件判断（case）对查询条件做条件限制。其语法规则是:

先来看如何用条件判断（case）实现R值打分

selectc.用户编号,(casewhenc.R>15then1,whenc.R>10andc.R<=15then2,whenc.R>5andc.R<=10then3,whenc.R>3andc.R<=5then4,whenc.R>0andc.R<=3then5end)asR分fromc;

用条件判断（case）实现F值打分

selectc.用户编号,(casewhenc.F<=1then1,whenc.F>1andc.F<=3then2,whenc.F>3andc.F<=5then3,whenc.F>5andc.F<=7then4,whenc.F>7then5end)asF分fromc;

用条件判断（case）实现M值打分

selectc.用户编号,(casewhenc.M<=500then1,whenc.M>500andc.M<=1000then2,whenc.M>1000andc.M<=1500then3,whenc.M>1500andc.M<=2000then4,whenc.M>2000then5end)asM分fromc;

将上面的R、F、M值打分sql合并在一起：

selectc.用户编号,(casewhenc.R>15then1,whenc.R>10andc.R<=15then2,whenc.R>5andc.R<=10then3,whenc.R>3andc.R<=5then4,whenc.R>0andc.R<=3then5end)asR分,(casewhenc.F<=1then1,whenc.F>1andc.F<=3then2,whenc.F>3andc.F<=5then3,whenc.F>5andc.F<=7then4,whenc.F>7then5end)asF分,(casewhenc.M<=500then1,whenc.M>500andc.M<=1000then2,whenc.M>1000andc.M<=1500then3,whenc.M>1500andc.M<=2000then4,whenc.M>2000then5end)asM分fromc;

将评分结果表示为临时表d。

3. 求出所有用户R、F、M评分的平均值

求平均值使用汇总函数（avg）

selectavg(R分)asR分平均,avg(F分)asF分平均,avg(M分)asM分平均fromd;

4. 将每个用户的R、F、M值与相应指标的平均值对比，确认用户分类

目前我们已经得到了每个用户的R分、F分和M分，也获得了R、F、M这三个指标各自的平均值。

接下来需要将每一个用户的【R分、F分、M分】与【R分平均、F分平均、M分平均值】做比较，就可知道每一个用户在【最近一次消费时间间隔(R)、消费频率(F)、消费金额(M)】三个指标中是否高于平均水平，从而帮助后续对用户进行分类。

先利用每个的R、F、M值与相应指标平均分作比较，将大于平均分的记为高，否则记为低：

selectd.用户编号,(casewhenR分>R分平均then'高'else'低'end)asR分类,(casewhenF分>F分平均then'高'else'低'end)asF分类,(casewhenM分>M分平均then'高'else'低'end)asM分类fromdinnerjoin(selectavg(R分)asR平均,avg(F分) asF平均,avg(M分)asM平均fromd)ase;

5.用户分类

根据上述分值比较情况，结合下面的分类规则对用户分类：

selecte.用户编号,(casewhenR分类='高'andF分类='高'andM分类='高'then'重要价值用户',whenR分类='高'andF分类=then'高'andM分类='高'then'重要发展用户',whenR分类='低'andF分类='高'andM分类='高'then'重要保持用户',whenR分类='低'andF分类='低'andM分类='高'then'重要挽留用户',whenR分类='高'andF分类='高'andM分类='低'then'一般价值用户',whenR分类='高'andF分类='低'andM分类='低'then'一般发展用户',whenR分类='低'andF分类='高'andM分类='低'then'一般保持用户',whenR分类='低'andF分类='低'andM分类='低'then'一般挽留用户'end)as用户分类from(selectd.用户编号,(casewhenR分>R分平均then'高'else'低'end)asR分类,(casewhenF分>F分平均then'高'else'低'end)asF分类,(casewhenM分>M分平均then'高'else'低'end)asM分类fromdinnerjoin(selectavg(R分)asR平均,avg(F分) asF平均,avg(M分)asM平均fromd))ase;

至此，我们就完成了用RFM分析方法、SQL对用户分类。然后根据分析结果，就可以对用户进行精细化运营啦。

三、如何进行精细化运营？

用户分类后，如何精细化运营呢？

对用户分类以后要做什么呢？那就是针对每类用户如何制定运营策略，这个具体公司业务不同，方法也不一样。这里举例说明前4类用户。

1）重要价值用户，RFM三个值都很高，要提供vip服务

2）重要发展用户，消费频率低，但是其他两个值很高，就要想办法提高他的消费频率

3） 重要保持用户，最近消费距离现在时间较远，也就是F值低，但是消费频次和消费金额高。这种用户，是一段时间没来的忠实客户。应该主动和他保持联系，提高复购率

4） 重要挽留客户，最近消费时间距离现在较远、消费频率低，但消费金额高。这种用户，即将流失，要主动联系用户，调查清楚哪里出了问题，并想办法挽回。

这样通过RFM分析方法来分析用户，对用户进行精细化运营。不断将用户转化为重要价值用户。

推荐：如何从零学会sql？