How to calculate Recency Frequency and Monetary by Customer ID

[Ale6]

I would like to calculate R F M variable to proceed with building the model however I have zero clue how to calculate them as I'm new to using SAS miner.

This is the sample dataset

What I would like to do sound simple but literally no clue

Except for selecting the variable I wanted by rejecting the one that I don't want.

The one I don't know how is : Create an aggregated variable named Amount, by multiplying Quantity with Price, which gives the total amount of money spent per product / item in each transaction.

Please help me with that

This is my current node. I just imported the file in and use the imported node not sure if I should save the data using the save node or even make it as data source.

Third step I want to do is to Separate the variable InvoiceDate into two variables Date and Time.

Please teach me that too

Fourth is to Filter out any transactions that do not have customerID and the customer that has no united kingdom with it

Lastly is to calculate Recency, Frequency and Monetary sorted by CustomerID this is probably the hardest.

This is the link to obtain the dataset if you're interested:

https://archive.ics.uci.edu/ml/datasets/online+retail

As for the step I mentioned I referred it from a study from Chen:

https://link.springer.com/article/10.1057/dbm.2012.17

Thanks in advance, please help me out

[sbxkoenk]

Hello,

Here are some answers to your questions.

I want to emphasize this is NOT a SAS Enterprise Miner question.

Everything you want, should be done upfront with base programming and the resulting table with all calculated variables should then be put in Enterprise Miner as ABT (Analytical Base Table). That's by far the easiest and most robust way of handling.

How do you define RECENCY, FREQUENCY and MONETARY VALUE? You need to come up with a definition for RFM-variables, otherwise we cannot give any input. I think you can find many SAS-programs on that topic with code if you need inspiration.

Apart from the RFM, here are the other things you want (I haven't downloaded the data but made up two sample observations):

data have;
 invoiceDate='01DEC2010:08:26:00'dt; Quantity=6;  UnitPrice=2.55; CustomerID='17850'; Country='United Kingdom'; output;
 invoiceDate='01DEC2010:08:45:00'dt; Quantity=24; UnitPrice=3.75; CustomerID='12583'; Country='France';         output;
run;

data want;
 set have;
 where      CustomerID is not missing;
 where also Country = 'United Kingdom';
 Amount = Quantity * UnitPrice;
 Date=datepart(invoiceDate);
 Time=timepart(invoiceDate);
 format date date9.
        time time8.;
run;
/* end of program */

RFM is easy to do as well.

The SAS base programming language is extremely powerful and compact (if you are experienced). Although a less compact program is easier to grasp for new users. It's worthwhile investing some time in learning base SAS programming. You will profit from it until you retire. 😉

Kind regards,

Koen

[sbxkoenk]

Hello,

one more of your requirements is sorting.

This is how you sort by customerID and within the same customerID by descending DateTime-stamp:

PROC SORT data=want out=want_sorted;
 by CustomerID descending date descending time;
run;

Cheers,

Koen

[Rynn_2]

Hi! Try to watch videos on YouTube about setting up RFM because I definitely won't be able to tell you how to set it up correctly. Although I started my business about three years ago, inspired by this article https://peertopeermarketing.co/rfm-model/, I tried to make an RPM Model for my business myself. I tried to make it for a very long time, but thanks to the fact that I read a lot, I still created it, and now my business is thriving because I decided to take such a step. However, I haven't programmed in SQL for about two years, and therefore, I don't remember exactly how I set it up, sorry 🙂