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

作者: 猴子数据分析 | 来源:发表于2020-07-12 18:35 被阅读0次

【面试题】下面是2020年1月份某电商平台的用户行为记录表。
电商平台促销日消费情况研究
Flink项目系列1-项目介绍
「名度控股」电商企业要学会描绘用户画像
58-用户运营秋招笔试
电商平台需求分析
电商平台用户行为分析(SQL)
1.企业需求
链家面试题：如何分析留存率？
微服务体系中的服务端SDK的实现

【面试题】下面是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;