Customer segmentation in Alteryx

[cheunghm4532]

Hi Alteryx Experts,

I am currently trying to map a list of customer (in Table 1) to the customer segments (in Table 2) based on their RFM Score (Column E). The RFM Score is simply the combination of the Recency Score, Frequency Score, and Monetary Score to create a three digit number. I would like to return the customer segment (in Table 2 Column A) for each of the Customer Master Code.

The main issue is that the mapping table is a range of values instead of just one value for each lookup criteria. I assume I will need to do something like a non-exact match vlookup in Excel.

I am new to Alteryx and this is too difficult for me at the moment. I would appreciate if anyone can help me on this. Thanks!

Table 1

Table 2

Herman

[MarqueeCrew]

I just woke up and will be answering this post in about an hour. I will take table 2 and parse the fields to create min and max values. Then generate rows to create entries for all values in range. Then create the distinct score combinations. This should give me the universe of combined RFM scores to join.

Cheers,

Mark

[ponraj · Accepted solution]

Here is the solution....

[cheunghm4532]

Thanks Ponraj for your prompt reply! It's much appreciated!

However, I don't quite understand the part where you filter the "CountDistinct_Name = 3". Would you mind explaining this? Thanks again!

Herman

[ponraj]

That is where I am checking which all customer segments a customer falls under. ie) Customer (RFM) score has to fall under all the three ranges(namely recency, frequency, monetary) to qualify for a segment. 

[cheunghm4532]

Thanks Ponraj! You are my hero!

[AnadilM]

A little late to this post but a much much simpler and quicker solution would be this