cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rina89 Regular Visitor
Regular Visitor

RFM - Recency of latest purchase

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]
	)
    )
)

 

image.png

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.

 

image.png 

 

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: RFM - Recency of latest purchase

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:

 

RecencyFromUntil
0-12012
12-241224
24+24 

 

 

 

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




5 REPLIES 5
rina89 Regular Visitor
Regular Visitor

RFM: Recency of purchase regarding specific year

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]
	)
    )
)

image.png

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.

 

 

image.png

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.

 

Super User
Super User

Re: RFM - Recency of latest purchase

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:

 

RecencyFromUntil
0-12012
12-241224
24+24 

 

 

 

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




rina89 Regular Visitor
Regular Visitor

Re: RFM - Recency of latest purchase

@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!

 

 

 

 

rina89 Regular Visitor
Regular Visitor

Re: RFM - Recency of latest purchase

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

 

Thanks again for your help @ImkeF!

rina89 Regular Visitor
Regular Visitor

Re: RFM - Recency of latest purchase

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