-- 4.5 RFM用户价值

create VIEW RFM1 AS

select

*,

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

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

when R程度='低' and F程度='高' and M程度='高' then '重要保持用户' #重要召回, 提高R

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;

SELECT * FROM RFM1;