113_Power Pivot 销售订单之重复购买率及购买间隔天数相关

博客：www.jiaopengzi.com

焦棚子的文章目录

 请点击下载附件

1、背景

在论坛中看到朋友在提复购率（重复购买率）等相关问题，今天把结果贴出来。

问题原贴：计算订单中的老顾客复购率

感谢问题原贴主提供脱敏素材文件。

贴一个效果图看看先；

[video width="1692" height="896" mp4="https://jiaopengzi.com/wp-content/uploads/2020/03/113_1.mp4"][/video]

结果

说明：

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焦棚子

焦棚子的文章目录

巴特西

113_Power Pivot 销售订单之重复购买率及购买间隔天数相关

1、背景

二、数据源：

三、上DAX

四、总结

最新文章

热门文章