请在文末下载附件
1、背景
在论坛中看到朋友在提复购率（重复购买率）等相关问题，今天把结果贴出来。
问题原贴：计算订单中的老顾客复购率
感谢问题原贴主提供脱敏素材文件。
贴一个效果图看看先；
说明：
1、复购率定义：按天去重，即一个客户一天产生多笔交易付款，则算一次购买，除非在统计周期内另外一天也有购买，则算重复购买。
2、结果中，RF和RF%中，列明细汇总大于等于总计，因为有的客户在不同的上下文中都出现了，所以会多次计算。
二、数据源：
1、data
2、参数维度表
3、calendar（日期表）
4、sku
由于数据处理过，sku变成了分类吧，实际应该是sku。
5、客户
客户维度表，在对客户维度如ABC中会用的更多，本案例中其实可以不用。有用到一个点，但数据大的话，还是需要的。
6、建立关系
7、结果图
三、上DAX
1、客户总数
客户总数:=DISTINCTCOUNT(data[客户ID])2、复购客户
复购客户:=
VAR T1 =
FILTER (
ADDCOLUMNS ( '客户', "RQ", CALCULATE ( DISTINCTCOUNT ( DATA[下单日期] ) ) ),
[RQ] > 1
)
RETURN
CALCULATE ( 'DATA'[客户总数], T1 )3、复购率
复购率:=DIVIDE('data'[复购客户],'data'[客户总数])4、近3个月复购率
近3个月复购率:=
VAR D1 =
STARTOFMONTH ( 'CALENDAR'[DATE] )
VAR D2 =
DATEADD ( D1, -2, MONTH )
VAR D3 =
ENDOFMONTH ( 'CALENDAR'[DATE] )
VAR T1 =
DATESBETWEEN ( 'CALENDAR'[DATE], D2, D3 )
VAR TF =
IF (
CALCULATE ( MAX ( 'DATA'[下单日期] ), ALL ( 'CALENDAR' ) ) >= D3,
TRUE (),
FALSE ()
)
RETURN
IF ( TF, CALCULATE ( 'DATA'[复购率], T1 ), BLANK () )5、近12个月复购率
近12个月复购率:=
VAR D1 =
STARTOFMONTH ( 'CALENDAR'[DATE] )
VAR D2 =
DATEADD ( D1, -11, MONTH )
VAR D3 =
ENDOFMONTH ( 'CALENDAR'[DATE] )
VAR T1 =
DATESBETWEEN ( 'CALENDAR'[DATE], D2, D3 )
VAR TF =
IF (
CALCULATE ( MAX ( 'DATA'[下单日期] ), ALL ( 'CALENDAR' ) ) >= D3,
TRUE (),
FALSE ()
)
RETURN
IF ( TF, CALCULATE ( 'DATA'[复购率], T1 ), BLANK () )6、最近12月间隔购物天数
最近12月间隔购物天数:=
VAR D1 =
STARTOFMONTH ( 'CALENDAR'[DATE] )
VAR D2 =
DATEADD ( D1, -11, MONTH )
VAR D3 =
ENDOFMONTH ( 'CALENDAR'[DATE] )
VAR T1 =
DATESBETWEEN ( 'CALENDAR'[DATE], D2, D3 )
VAR T2 =
CALCULATETABLE ( VALUES ( DATA[客户ID] ), T1 )
VAR T3 =
FILTER (
ADDCOLUMNS (
T2,
"COUNT", CALCULATE ( DISTINCTCOUNT ( DATA[下单日期] ), ALL ( 'CALENDAR'[YM] ) ),
"DAY", DATEDIFF (
CALCULATE ( MIN ( DATA[下单日期] ), ALL ( 'CALENDAR'[YM] ) ),
CALCULATE ( MAX ( DATA[下单日期] ), ALL ( 'CALENDAR'[YM] ) ),
DAY
)
),
[DAY] > 0
)
VAR C =
CALCULATE ( 'DATA'[客户总数], T3, ALL ( 'CALENDAR'[YM] ) )
VAR TF =
IF (
CALCULATE ( MAX ( 'DATA'[下单日期] ), ALL ( 'CALENDAR' ) ) >= D3,
TRUE (),
FALSE ()
)
RETURN
IF ( TF, DIVIDE ( SUMX ( T3, DIVIDE ( [DAY], [COUNT] ) ), C ), BLANK () )7、RF%
RF是recency和frequency的缩写，即最近购买时间和购买次数，RF%符合要求的客户数占比总客户数的百分比。
RF%:=
VAR N1=SUM('RECENCY'[RL])
VAR N2=SUM('RECENCY'[RR])
VAR D=MAX('DATA'[下单日期])
VAR D1=DATESINPERIOD('CALENDAR'[DATE],D,-N1,DAY)
VAR D2=DATESINPERIOD('CALENDAR'[DATE],D,-N2,DAY)
VAR T1=EXCEPT(D2,D1)
VAR I=SUM(FREQUENCY[ID])
VAR TF1=IF(ISFILTERED(RECENCY[RECENCY]),TRUE(),FALSE())
VAR TF2=IF(ISFILTERED(FREQUENCY[FREQUENCY]),TRUE(),FALSE())
VAR C=
SWITCH(TRUE(),
TF1&&TF2,
SWITCH(TRUE(),
I=1,CALCULATE('DATA'[客户总数],FILTER(ADDCOLUMNS('客户',"RQ",CALCULATE(DISTINCTCOUNT(DATA[下单日期]))),[RQ]=1),T1),
I=2,CALCULATE('DATA'[客户总数],FILTER(ADDCOLUMNS('客户',"RQ",CALCULATE(DISTINCTCOUNT(DATA[下单日期]))),[RQ]=2),T1),
I=3,CALCULATE('DATA'[客户总数],FILTER(ADDCOLUMNS('客户',"RQ",CALCULATE(DISTINCTCOUNT(DATA[下单日期]))),[RQ]=3),T1),
I=4,CALCULATE('DATA'[客户总数],FILTER(ADDCOLUMNS('客户',"RQ",CALCULATE(DISTINCTCOUNT(DATA[下单日期]))),[RQ]=4),T1),
I>=5,CALCULATE('DATA'[客户总数],FILTER(ADDCOLUMNS('客户',"RQ",CALCULATE(DISTINCTCOUNT(DATA[下单日期]))),[RQ]>=5),T1)),
TF1=FALSE()&&TF2=TRUE(),
SWITCH(TRUE(),
I=1,CALCULATE('DATA'[客户总数],FILTER(ADDCOLUMNS('客户',"RQ",CALCULATE(DISTINCTCOUNT(DATA[下单日期]))),[RQ]=1)),
I=2,CALCULATE('DATA'[客户总数],FILTER(ADDCOLUMNS('客户',"RQ",CALCULATE(DISTINCTCOUNT(DATA[下单日期]))),[RQ]=2)),
I=3,CALCULATE('DATA'[客户总数],FILTER(ADDCOLUMNS('客户',"RQ",CALCULATE(DISTINCTCOUNT(DATA[下单日期]))),[RQ]=3)),
I=4,CALCULATE('DATA'[客户总数],FILTER(ADDCOLUMNS('客户',"RQ",CALCULATE(DISTINCTCOUNT(DATA[下单日期]))),[RQ]=4)),
I>=5,CALCULATE('DATA'[客户总数],FILTER(ADDCOLUMNS('客户',"RQ",CALCULATE(DISTINCTCOUNT(DATA[下单日期]))),[RQ]>=5))),
TF1=TRUE()&&TF2=FALSE(),
CALCULATE('DATA'[客户总数],T1),
TF1=FALSE()&&TF2=FALSE(),
'DATA'[客户总数])
RETURN
DIVIDE(C,'DATA'[客户总数])8、RF
RF是recency和frequency的缩写，即最近购买时间和购买次数，RF符合要求的客户数。
RF:=
VAR N1=SUM('RECENCY'[RL])
VAR N2=SUM('RECENCY'[RR])
VAR D=MAX('DATA'[下单日期])
VAR D1=DATESINPERIOD('CALENDAR'[DATE],D,-N1,DAY)
VAR D2=DATESINPERIOD('CALENDAR'[DATE],D,-N2,DAY)
VAR T1=EXCEPT(D2,D1)
VAR I=SUM(FREQUENCY[ID])
VAR TF1=IF(ISFILTERED(RECENCY[RECENCY]),TRUE(),FALSE())
VAR TF2=IF(ISFILTERED(FREQUENCY[FREQUENCY]),TRUE(),FALSE())
RETURN
SWITCH(TRUE(),
TF1&&TF2,
SWITCH(TRUE(),
I=1,CALCULATE('DATA'[客户总数],FILTER(ADDCOLUMNS('客户',"RQ",CALCULATE(DISTINCTCOUNT(DATA[下单日期]))),[RQ]=1),T1),
I=2,CALCULATE('DATA'[客户总数],FILTER(ADDCOLUMNS('客户',"RQ",CALCULATE(DISTINCTCOUNT(DATA[下单日期]))),[RQ]=2),T1),
I=3,CALCULATE('DATA'[客户总数],FILTER(ADDCOLUMNS('客户',"RQ",CALCULATE(DISTINCTCOUNT(DATA[下单日期]))),[RQ]=3),T1),
I=4,CALCULATE('DATA'[客户总数],FILTER(ADDCOLUMNS('客户',"RQ",CALCULATE(DISTINCTCOUNT(DATA[下单日期]))),[RQ]=4),T1),
I>=5,CALCULATE('DATA'[客户总数],FILTER(ADDCOLUMNS('客户',"RQ",CALCULATE(DISTINCTCOUNT(DATA[下单日期]))),[RQ]>=5),T1)),
TF1=FALSE()&&TF2=TRUE(),
SWITCH(TRUE(),
I=1,CALCULATE('DATA'[客户总数],FILTER(ADDCOLUMNS('客户',"RQ",CALCULATE(DISTINCTCOUNT(DATA[下单日期]))),[RQ]=1)),
I=2,CALCULATE('DATA'[客户总数],FILTER(ADDCOLUMNS('客户',"RQ",CALCULATE(DISTINCTCOUNT(DATA[下单日期]))),[RQ]=2)),
I=3,CALCULATE('DATA'[客户总数],FILTER(ADDCOLUMNS('客户',"RQ",CALCULATE(DISTINCTCOUNT(DATA[下单日期]))),[RQ]=3)),
I=4,CALCULATE('DATA'[客户总数],FILTER(ADDCOLUMNS('客户',"RQ",CALCULATE(DISTINCTCOUNT(DATA[下单日期]))),[RQ]=4)),
I>=5,CALCULATE('DATA'[客户总数],FILTER(ADDCOLUMNS('客户',"RQ",CALCULATE(DISTINCTCOUNT(DATA[下单日期]))),[RQ]>=5))),
TF1=TRUE()&&TF2=FALSE(),
CALCULATE('DATA'[客户总数],T1),
TF1=FALSE()&&TF2=FALSE(),
'DATA'[客户总数])四、总结
1、业务为导向，理解业务，把业务转换成dax上下文；
2、参数表的使用;
3、按天去重的复购率的定义，所在在模型中增加了去掉购买时间的购买日期;
4、参考模型RFM模型，做一个 RF矩阵；
5、附件中放了一个《购买次数5次及以上验证》的sheet，便于验证。
by焦棚子
请点击【立即购买】或者【升级VIP】获得案例附件。
评论