淘寶案例用戶價值分析（推送廣告前進(jìn)行）：

RFM：

-- 用戶價值分析

-- 每個用戶消費(fèi)時間間隔、消費(fèi)頻次、消費(fèi)金額

select

user_id,

max(日期) as 最近一次消費(fèi)日期,

timestampdiff(day,max(日期),'2014-12-19') as 消費(fèi)時間間隔,

count(*) as 消費(fèi)頻次,

sum(amount) as 消費(fèi)金額

from userbehavior_new

where behavior_type='buy'

group by user_id;

重要程度：先看標(biāo)準(zhǔn)，一般是均值（實(shí)際工作中很少用，小乙說的平均值陷阱，也就是受極端值影響）要制定評分標(biāo)準(zhǔn)表（確定新量綱）

-- RFM均值，量綱化后求均值作為判斷依據(jù)

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重要程度，給出平均值后做判斷，得到高低標(biāo)簽

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 '重要發(fā)展用戶'

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 '一般發(fā)展用戶'

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;