비즈니스 시나리오 제작 가이드 템플릿

https://docs.google.com/spreadsheets/d/1av4E8U1LTal1mhJdFLWahCA9jQoKMc4n/edit?usp=sharing&ouid=107351716570025173091&rtpof=true&sd=true

1. Basic Calc

Number of Order

COUNTD([Order ID])

Max Order Date of the Year

{FIXED YEAR([Order Date]): MAX([Order Date])}

2. RFM

Recency (최근성)

DATEDIFF('day',{ FIXED [Customer Name],YEAR([Order Date]): 
MAX([Order Date])},[Max Order Date of the Year])+1

Frequency (구매 빈도)

{ FIXED [Customer Name],YEAR([Order Date]):[Number of Order]}

Monetary (구매 금액)

{ FIXED [Customer Name],YEAR([Order Date]): SUM([Sales])}

3. Score

Recency Score

IF [Recency] >= { FIXED YEAR([Order Date]): PERCENTILE([Recency],0.75)} then 1
ELSEIF [Recency] >= { FIXED YEAR([Order Date]): PERCENTILE([Recency],0.5)} then 2
ELSEIF [Recency] >= { FIXED YEAR([Order Date]): PERCENTILE([Recency],0.25)} then 3
else 4
end