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

2020年3月1日21:20:14 评论 653 5220字阅读17分24秒

焦棚子的文章目录

请在文末下载附件

 

1、背景

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

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

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

贴一个效果图看看先;

结果

说明:

1、复购率定义:按天去重,即一个客户一天产生多笔交易付款,则算一次购买,除非在统计周期内另外一天也有购买,则算重复购买。

2、结果中,RF和RF%中,列明细汇总大于等于总计,因为有的客户在不同的上下文中都出现了,所以会多次计算。

二、数据源:

1、data

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

2、参数维度表

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

3、calendar(日期表)

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

4、sku

由于数据处理过,sku变成了分类吧,实际应该是sku。

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

5、客户

客户维度表,在对客户维度如ABC中会用的更多,本案例中其实可以不用。有用到一个点,但数据大的话,还是需要的。

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

6、建立关系

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

7、结果图

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

三、上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】获得案例附件。

隐藏内容需要支付:¥10
立即购买 升级VIP
焦棚子
  • 本文由 发表于 2020年3月1日21:20:14
  • 除非特殊声明,本站文章均为焦棚子原创,转载请务必保留本文链接
140_Power BI&Power Pivot之降维展示同类型比较 Power BI

140_Power BI&Power Pivot之降维展示同类型比较

焦棚子的文章目录 请在文末下载附件 一、背景 最近一段时间比较忙,几乎没有时间更新网站内容,今天刚好周末,更新一个简单的需求。 上效果图: 在我们日常做对比分析的时候,经常几个相同维度(比如省份、区域...
138_Power BI&Power Pivot特殊半累加度量 Power BI

138_Power BI&Power Pivot特殊半累加度量

焦棚子的文章目录 请在文末下载附件 一、背景 半累加度量(semi-additive measure),在DAX建模分析的时候经常遇见;应用场景诸如银行存款、库存等。 接下来看一个比较有意思的案例(结...