RFM Analysis in Alteryx

Hello friends!! today we’ll be learning to do RFM analysis in Alteryx. I’ve attached the Alteryx workflow for download and reuse. The workflow is reusable to calculate the RFM or tweak the workflow logic in RFM algorithm.

RFM (recency, frequency, monetary) analysis is a marketing technique used to determine quantitatively which customers are the best ones by examining how recently a customer has purchased (recency), how often they purchase (frequency), and how much the customer spends (monetary) read more

So idea behind RFM is to segment the customers into different groups by assigning a score based on RFM (recency, frequency, monetary) . So we’ll assign a score from 1(lowest) to 5(highest). Scores can be assigned of any range for simplicity I’m taking here 1 to 5.

Here I’ve created segment of customers based on the last purchase(R) date, avg days between two purchase(F) and total sales(M) separately. So lets start with the workflow and the structure.

Sample Data

cust_id	txn_id	txn_date	qnty	net_sales
CC5796943AA	45259579	1/10/2018	1	112.151
CC5796943AA	45259579	1/10/2018	1	72.564
CC5796943AA	45259579	1/10/2018	1	97.619
CC5796943AA	45259579	1/10/2018	1	115.364
CC5796943AA	43515229	11/29/2017	1	191.376
CC5796943AA	40942655	8/1/2017	1	39.058
CC5796943AA	40942655	8/1/2017	1	106.128
CC5796943AA	43515229	11/29/2017	1	89.070

Steps to achieve the final output:

1. Get the input data and calculate sum of sales(for Monetary), first purchase, last purchase date(for frequency) and total transactions (at customer level) after removing the negative values
2. Get the last transaction date present in the data and append to the original data as shown in the workflow(image below). It will be used to calculate the Recency
3. get the number of days between last purchase date(at customer level) and last transaction date present in the data
4. a) Now select the cust_id and total sales and arrange in descending order b) cust_id and days since last purchase in ascending order and c) cust_id and avg days between two purchase in ascending order
5. Now calculate the share the of each record for RFM and calculate running total tool
6. Set the cut off 0.1, 0.2, 0.3…(10%,20%..) for running total of % of product using Tile tool
7. Once you have the Tile_Num then assign scores for RFM separately as Tile_Num:1 to 5, 2 to 4.5, 3 to 4 and so on…
8. Concatenate R,F,M and that’s done.

Download the Alteryx workflow here

Read more
1. RFM analysis for Customer Segmentation

Keep visiting Analytics Tuts for more tutorials.

Thanks for reading! Comment your suggestions and queries