-- 4.5 RFM用戶價值

create VIEW RFM1 AS

select

*,

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

when R程度='高' and F程度='低' and M程度='高' then '重要發(fā)展用戶' #重要發(fā)掘, 提高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 '一般發(fā)展用戶' #一般發(fā)掘,重要優(yōu)惠營銷對象

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;