cancel
Showing results for
Did you mean:
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]
)
)
)```

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

R.

1 ACCEPTED SOLUTION

Accepted Solutions
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] ))*12RETURN 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

Proud to be a Datanaut!

Imke Feldmann

5 REPLIES 5
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]
)
)
)```

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

R.

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] ))*12RETURN 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

Proud to be a Datanaut!

Imke Feldmann

Regular Visitor

Re: RFM - Recency of latest purchase

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?

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!

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```