RFM - Recency of latest purchase

[Anonymous]

Hello everyone,

I'm currently trying to make an analysis that shows what "segmentation" our customers are in regarding the recency of their last purchase.

The goal is to make it possible to know precisely how many customers are in each "segmentation" for a specific year. This specific year will be chosen using a parameter table that has no connection with the other tables and that starts in year 2010.

For the moment, I have this calculated column in the customer table that gives such results : Each segmentation has X number of customers but the analysis is only possible for the latest "purchase date", which means it is not dynamic and only work when the information about the segmentation is given in a "From today" point of view. Hope this explanation is understandable enough.

recency of last customer's purchase =
IF(
    DATEDIFF(
        dim_customer[date last purchase(sales)];
        MAX(data_sales[purchase_date]);   // I think the solution might be in this area 
        MONTH                             // => adding a filter that calculates for the selected year
    ) <= 12
    && ISBLANK(dim_customer[date last purchase(sales)]) = FALSE() ;
    "01-12 mois";
    IF(
        DATEDIFF(
            dim_customer[date last purchase(sales)];
            MAX(data_sales[purchase_date])
            ; MONTH
        ) <= 24
        && ISBLANK(dim_customer[date last purchase(sales)]) = FALSE() ;
        "12-24 mois";
        IF(
	    DATEDIFF(
		dim_customer[date last purchase(sales)];
		MAX(data_sales[purchase_date]);
		MONTH
	    ) >= 25
	    && ISBLANK(dim_customer[date last purchase(sales)]) = FALSE() ;
	    "25+ mois"
	)
    )
)

Then, I have a measure that gives the number of distinct customers in each category :

nb customers = 
IF(
    [Valeur year last purchase] > YEAR(TODAY());
    BLANK();
    CALCULATE(
        DISTINCTCOUNT(dim_customer[account_id]);
	FILTER(dim_customer;
	    dim_customer[year last purchase] <= [Valeur year last purchase]
	)
    )
)

Example of results from 2015. It clearly shows that whenever the date is over 12 months, etc. the "nb customers" is not calculated because DATEDIFF between the last purchase date in the sales table (which is obviously today or yesterday) and the last purchase of each specific customer is longer than 12 months.

 

Example of results for year 2017

I add the simple distinctcount formula for the total customers :

Total customers = 
CALCULATE(
    DISTINCTCOUNT(data_sales[account_id_FK])
)

Thanks in advance for your help that will be much appreciated.

R.

[ImkeF · Accepted solution]

pls check the dynamic segmentation pattern

I would add a column in the customer table indicating the "AgeOfToday" like this:

AgeOfToday = YEARFRAC(TODAY(),CALCULATE(MAX(data_sales[purchase_date]), RELATEDTABLE(data_sales)))*12

then calculate an offset to the selected year like this:

VAR Offset =
    (YEAR ( TODAY () ) - MAX ( PickYear[CalendarYear] ))*12
RETURN IF(ISFILTERED(Recency[Recency]),
    CALCULATE (
        COUNT( DimCustomer[AgeOfToday] ),
        FILTER (
            VALUES(DimCustomer[AgeOfToday]),
            DimCustomer[AgeOfToday]
                <= MAX ( Recency[Until] )
                    + Offset
                && DimCustomer[AgeOfToday]
                    > MAX ( Recency[From] )
                        + Offset
                ||DimCustomer[AgeOfToday]
                    > MAX ( Recency[From] ) + Offset && MIN( Recency[Until] ) = BLANK ()
        )
    ),COUNT(DimCustomer[AgeOfToday])
)

You need a table "Recency" whose first column you drag into the report pane and "From" and "Until" hold the ranges' border like so:

Recency	From	Until
0-12	0	12
12-24	12	24
24+	24

[Anonymous]

@ImkeFThanks for your answer and the blog post you mentioned.

 
Sorry for the various answer I made, but I cannot find the right answer after several trials.

I still have question about the "PickYear[CalendarYear]": where does it come from? Is it from the Calendar table or so?

Thanks for your help!

[Anonymous]

By using the Year of last Purchase value of my parameter table, it works like a charm.

Thanks again for your help @ImkeF!

[Anonymous]

I've also added a point in the AgeOfToday because the analysis needs to be done at the end of every year.

AgeOfEndOfYear = 
YEARFRAC(
	ENDOFYEAR(Calendar[Date]);
	CALCULATE(
		MAX(data_sales[purchase_date]);
		RELATEDTABLE(data_sales)
	)
) * 12

[Anonymous]

Hello everyone,

I'm currently trying to make an analysis that shows what "segmentation" our customers are in regarding the recency of their last purchase.

The goal is to make it possible to know precisely how many customers are in each "segmentation" for a specific year. This specific year will be chosen using a parameter table that has no connection with the other tables and that starts in year 2010.

For the moment, I have this calculated column in the customer table that gives such results : Each segmentation has X number of customers but the analysis is only possible for the latest "purchase date", which means it is not dynamic and only work when the information about the segmentation is given in a "From today" point of view. Hope this explanation is understandable enough.

recency of last customer's purchase =
IF(
    DATEDIFF(
        dim_customer[date last purchase(sales)];
        MAX(data_sales[purchase_date]);   // I think the solution might be in this area 
        MONTH                             // => adding a filter that calculates for the selected year
    ) <= 12
    && ISBLANK(dim_customer[date last purchase(sales)]) = FALSE() ;
    "01-12 mois";
    IF(
        DATEDIFF(
            dim_customer[date last purchase(sales)];
            MAX(data_sales[purchase_date])
            ; MONTH
        ) <= 24
        && ISBLANK(dim_customer[date last purchase(sales)]) = FALSE() ;
        "12-24 mois";
        IF(
	    DATEDIFF(
		dim_customer[date last purchase(sales)];
		MAX(data_sales[purchase_date]);
		MONTH
	    ) >= 25
	    && ISBLANK(dim_customer[date last purchase(sales)]) = FALSE() ;
	    "25+ mois"
	)
    )
)

Then, I have a measure that gives the number of distinct customers in each category :

nb customers = 
IF(
    [Valeur year last purchase] > YEAR(TODAY());
    BLANK();
    CALCULATE(
        DISTINCTCOUNT(dim_customer[account_id]);
	FILTER(dim_customer;
	    dim_customer[year last purchase] <= [Valeur year last purchase]
	)
    )
)

Example of results from 2015. It clearly shows that whenever the date is over 12 months, etc. the "nb customers" is not calculated because DATEDIFF between the last purchase date in the sales table (which is obviously today or yesterday) and the last purchase of each specific customer is longer than 12 months.

Example of results for year 2017

I add the simple distinctcount formula for the total customers :

Total customers = 
CALCULATE(
    DISTINCTCOUNT(data_sales[account_id_FK])
)

Thanks in advance for your help that will be much appreciated.

R.