*RFM_R
MAX(DATEDIFF('day',{max([Order Date])}, [Order Date]))
*RFM_F
countd([Basket Id])
*RFM_M
sum([Sales Value])/countd([Basket Id])
*RFM_R_Score
IF {Fixed [Household Key]: [*RFM_R]} <=
{Fixed : Percentile(
{Fixed [Household Key]: [*RFM_R]}, 0.25)}
THEN 1
ELSEIF {Fixed [Household Key]: [*RFM_R]} <=
{Fixed : Percentile(
{Fixed [Household Key]: [*RFM_R]}, 0.50)}
THEN 2
ELSEIF {Fixed [Household Key]: [*RFM_R]} <=
{Fixed : Percentile(
{Fixed [Household Key]: [*RFM_R]}, 0.75)}
THEN 3
ELSE 4
END
*RFM_F_Score
IF {Fixed [Household Key]: [*RFM_F]} <=
{Fixed : Percentile(
{Fixed [Household Key]: [*RFM_F]}, 0.25)}
THEN 1
ELSEIF {Fixed [Household Key]: [*RFM_F]} <=
{Fixed : Percentile(
{Fixed [Household Key]: [*RFM_F]}, 0.50)}
THEN 2
ELSEIF {Fixed [Household Key]: [*RFM_F]} <=
{Fixed : Percentile(
{Fixed [Household Key]: [*RFM_F]}, 0.75)}
THEN 3
ELSE 4
END
*RFM_M_Score
IF {Fixed [Household Key]: [*RFM_M]} <=
{Fixed : Percentile(
{Fixed [Household Key]: [*RFM_M]}, 0.25)}
THEN 1
ELSEIF {Fixed [Household Key]: [*RFM_M]} <=
{Fixed : Percentile(
{Fixed [Household Key]: [*RFM_M]}, 0.50)}
THEN 2
ELSEIF {Fixed [Household Key]: [*RFM_M]} <=
{Fixed : Percentile(
{Fixed [Household Key]: [*RFM_M]}, 0.75)}
THEN 3
ELSE 4
END
*RFM_code
[*RFM_R_Score]*100 +
[*RFM_F_Score]*10 +
[*RFM_M_Score]*1
*RFM_Segement
IF REGEXP_MATCH(STR([*RFM code]),'444') THEN "Best Customers"
ELSEIF [*RFM code]=244 THEN "Almost Lost"
ELSEIF [*RFM code]=144 THEN "Lost Customers"
ELSEIF [*RFM code]=111 OR REGEXP_MATCH(STR([*RFM code]),'1[1-2][1-2]')
THEN "Lost Cheap Customers"
ELSEIF REGEXP_MATCH(STR([*RFM code]),'4[1-4][1-4]') THEN "Recent Customers"
ELSEIF REGEXP_MATCH(STR([*RFM code]),'[1-4]4[1-4]') THEN "Loyal Customers"
ELSEIF REGEXP_MATCH(STR([*RFM code]),'[1-4][1-4]4') THEN "Big Spenders"
ELSE "Others"
END
*No_Orders_per_Consumer
{Fixed [Household Key]: countd([Basket Id])}
*Recency_Per_Consumer
{FIXED [Household Key]: -[*RFM_R]}