淘宝案例用户价值分析（推送广告前进行）：

RFM：

-- 用户价值分析

-- 每个用户消费时间间隔、消费频次、消费金额

select

user_id,

max(日期) as 最近一次消费日期,

timestampdiff(day,max(日期),'2014-12-19') as 消费时间间隔,

count(*) as 消费频次,

sum(amount) as 消费金额

from userbehavior_new

where behavior_type='buy'

group by user_id;

重要程度：先看标准，一般是均值（实际工作中很少用，小乙说的平均值陷阱，也就是受极端值影响）要制定评分标准表（确定新量纲）

-- RFM均值，量纲化后求均值作为判断依据

select

avg(R评分) as R均值,

avg(F评分) as F均值,

avg(M评分) as M均值

from

(select

user_id,

case when timestampdiff(day,max(日期),'2014-12-19')<=6 then 5

when timestampdiff(day,max(日期),'2014-12-19')<=12 then 4

when timestampdiff(day,max(日期),'2014-12-19')<=18 then 3

when timestampdiff(day,max(日期),'2014-12-19')<=24 then 2

else 1

end as R评分,

if(count(*)=1,1,if(count(*)=2,2,if(count(*)=3,3,if(count(*)=4,4,5)))) as F评分,

if(sum(amount)<100,1,if(sum(amount)<200,2,if(sum(amount)<300,3,if(sum(amount)<400,4,5)))) as M评分

from userbehavior_new

where behavior_type='buy'

group by user_id) as t;

-- RFM重要程度，给出平均值后做判断，得到高低标签

select

*,

if(R评分>3.5984,'高','低') as R程度,

if(F评分>2.1039,'高','低') as F程度,

if(M评分>2.2051,'高','低') as M程度

from

(select

user_id,

timestampdiff(day,max(日期),'2014-12-19') as R,

count(*) as F,

sum(amount) as M,

case when timestampdiff(day,max(日期),'2014-12-19')<=6 then 5

when timestampdiff(day,max(日期),'2014-12-19')<=12 then 4

when timestampdiff(day,max(日期),'2014-12-19')<=18 then 3

when timestampdiff(day,max(日期),'2014-12-19')<=24 then 2

else 1

end as R评分,

if(count(*)=1,1,if(count(*)=2,2,if(count(*)=3,3,if(count(*)=4,4,5)))) as F评分,

if(sum(amount)<100,1,if(sum(amount)<200,2,if(sum(amount)<300,3,if(sum(amount)<400,4,5)))) as M评分

from userbehavior_new

where behavior_type='buy'

group by user_id) as t;

-- RFM用户价值，做出判断

select

*,

case when R程度='高' and F程度='高' and M程度='高' then '重要价值用户'

when R程度='高' and F程度='低' and M程度='高' then '重要发展用户'

when R程度='低' and F程度='高' and M程度='高' then '重要保持用户'

when R程度='低' and F程度='低' and M程度='高' then '重要挽留用户'

when R程度='高' and F程度='高' and M程度='低' then '一般价值用户'

when R程度='高' and F程度='低' and M程度='低' then '一般发展用户'

when R程度='低' and F程度='高' and M程度='低' then '一般保持用户'

else '一般挽留用户'

end as 用户价值分类

from

(select

*,

if(R评分>3.5984,'高','低') as R程度,

if(F评分>2.1039,'高','低') as F程度,

if(M评分>2.2051,'高','低') as M程度

from

(select

user_id,

timestampdiff(day,max(日期),'2014-12-19') as R,

count(*) as F,

sum(amount) as M,

case when timestampdiff(day,max(日期),'2014-12-19')<=6 then 5

when timestampdiff(day,max(日期),'2014-12-19')<=12 then 4

when timestampdiff(day,max(日期),'2014-12-19')<=18 then 3

when timestampdiff(day,max(日期),'2014-12-19')<=24 then 2

else 1

end as R评分,

if(count(*)=1,1,if(count(*)=2,2,if(count(*)=3,3,if(count(*)=4,4,5)))) as F评分,

if(sum(amount)<100,1,if(sum(amount)<200,2,if(sum(amount)<300,3,if(sum(amount)<400,4,5)))) as M评分

from userbehavior_new

where behavior_type='buy'

group by user_id) as t1) as t2;