Assigning segments based on revenue

[Himanshu123]

 Hi All

I have a pretty simple problem. I want to assign packages to my customers based on the revenue and the size. I am attaching here the sample of problem. The sample of data on revenue is in the tab 'Revenue' and the data on how to decide the package is in the tab 'Package Decision'.

Given the size of the customer, I want to look in the tab of 'Package decision', match the size of the customer with the size given in the 'package decision' and then look up for the revenue, and then assign the package. 

Size	Max Revenue	Package
1	250	Basic
1	750	Standard
1	1500	Gold
1	2000	Platinum

So if the customer is in size 1 and has a revenue of 300, then he should be assigned 'Standard' Package and not any other package. If the revenue is 2200 then he should be assigned Platinum. If it is 200 then 'Basic'.

It should be pretty simple, but I think I am unable to find out how to do it.

Thanks in Advance

[CarlDi]

Hi Himanshu,

A conditional statement in the Formula tool should do the trick. See my attached example.

[Himanshu123]

Hi Carl

Thanks for the reply. However, I was looking for a more dynamic solution as I have around 500 such sizes and around 25 such packages for each size with varying ranges of revenues. Can you help me so that the workflow which is more dynamic in its working.

Himanshu

[steven-barsalou · Accepted solution]

Here is a dynamic solution that only requires your customer input and packages input.  This will only output customers that met the criteria for a least one package.  If you want to include those customer that did not meet those criteria, you can add another join after the second join tool and connect the customers input to the L input and the output of the second join to the new join R input.  Then use a union tool to take the L and J outputs from the new (third) join.

See the attached workflow for working example.

[Himanshu123]

Hi

Thanks for responding to the query. I have implemented something similar to it in my workflow. I forgot to upload it here after the problem was done. However, I think that instead of using max in summarize by, we might need to use the minimum value because we want to assign it to the package which has the value closest to the revenue from the customer. If we use max, all the customers will be assigned the same package.

However, thanks again for the response.