【面试题】下面是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。
查询结果:
(1)最近一次消费时间间隔(R)
R值:用户最近一次消费距离现在(1月30日)多长时间了。
R值=现在(2020-01-30)- 每个用户最后一次购物日期
每个用户最后一次购物日期,涉及到“每个问题“要想到《猴子 从零学会SQL》里讲过的用分组汇总来实现。
根据“用户编号”分组,然后汇总(最后一次购买日期,就是找到日期的最大值,用汇总函数max)
将该查询结果表示为临时表b。
然后就可以分析出R值=现在(2020-01-30)- 每个用户最后一次购物日期
(2)消费频率(F)
消费频率(F):用户一段时间内(1月份)消费了多少次。
先筛选(where)出30天内所有的购买记录。
然后需要分析出“每位用户的购买次数。“每位用户”按用户编号分组(group by),然后用汇总函数(count)求出购买次数。
(3)消费金额(M)
消费金额(M):用户一段时间内(1月份)的消费金额,对应表中的“消费金额“。
先筛选(where)出30天内所有的购买记录。
然后,分析出“每位用户的总消费金额”,按用户编号分组(group by),用汇总函数(sum)求出总消费金额。
至此,我们已经获得了每一个用户的R、F、M值,将其合并起来设为表c,以便后续查询:
2. 给R、F、M按价值打分
本案例的R、F、M打分规则定义如下:
打分过程及预期打分结果:
为了实现上面的打分,可以使用SQL语句的条件判断(case)对查询条件做条件限制。其语法规则是:
先来看如何用条件判断(case)实现R值打分
用条件判断(case)实现F值打分
用条件判断(case)实现M值打分
将上面的R、F、M值打分sql合并在一起:
将评分结果表示为临时表d。
3. 求出所有用户R、F、M评分的平均值
求平均值使用汇总函数(avg)
4. 将每个用户的R、F、M值与相应指标的平均值对比,确认用户分类
目前我们已经得到了每个用户的R分、F分和M分,也获得了R、F、M这三个指标各自的平均值。
接下来需要将每一个用户的【R分、F分、M分】与【R分平均、F分平均、M分平均值】做比较,就可知道每一个用户在【最近一次消费时间间隔(R)、消费频率(F)、消费金额(M)】三个指标中是否高于平均水平,从而帮助后续对用户进行分类。
先利用每个的R、F、M值与相应指标平均分作比较,将大于平均分的记为高,否则记为低:
5.用户分类
根据上述分值比较情况,结合下面的分类规则对用户分类:
至此,我们就完成了用RFM分析方法、SQL对用户分类。然后根据分析结果,就可以对用户进行精细化运营啦。
三、如何进行精细化运营?
用户分类后,如何精细化运营呢?
对用户分类以后要做什么呢?那就是针对每类用户如何制定运营策略,这个具体公司业务不同,方法也不一样。这里举例说明前4类用户。
1)重要价值用户,RFM三个值都很高,要提供vip服务
2)重要发展用户,消费频率低,但是其他两个值很高,就要想办法提高他的消费频率
3) 重要保持用户,最近消费距离现在时间较远,也就是F值低,但是消费频次和消费金额高。这种用户,是一段时间没来的忠实客户。应该主动和他保持联系,提高复购率
4) 重要挽留客户,最近消费时间距离现在较远、消费频率低,但消费金额高。这种用户,即将流失,要主动联系用户,调查清楚哪里出了问题,并想办法挽回。
这样通过RFM分析方法来分析用户,对用户进行精细化运营。不断将用户转化为重要价值用户。