Flag columns to capture Repeated Customers information

[stevelogue1980]

Hi,

I am a new bee of Alteryx and need help on creating 2 flag columns (IS_Customer, Customer_TYPE),Alteryx Workflow attached

I have 2 Tables: Customer Interactions, Customer Details and Need a NEW TABLE with the below flags:

Ex:

Customer_ID	IS_Customer	Customer_TYPE
NULL	FALSE	NULL
11	TRUE	NEW
12	TRUE	NEW
13	TRUE	NEW
14	TRUE	EXISTING

IS_Customer: If there is an existing Customer based on Customer_ID

Customer_TYPE: to capture if there is Repeated Customer (based on Encounter_Date) with in the last 24 months of the Interaction (Visited/ bought previously)  based on Interaction Date.

[DavidP]

Hi @stevelogue1980 

Welcome to the Community!

Have a look at the attached workflow to see if it does what you're looking for - let me know if you have any questions.

[stevelogue1980]

Thanks David for a head start.

However, Customer_TYPE is Expected as below based on Encounter_Date with in the last 24 months of the Interaction (Visited/ bought previously) based on Interaction Date.

Customer_ID	IS_Customer	Customer_TYPE
NULL	FALSE	NULL
11	TRUE	NEW
12	TRUE	NEW
13	TRUE	NEW
14	TRUE	EXISTING

your results are all says 'EXISTING'

[grazitti_sapna · Accepted solution]

Hi @stevelogue1980 , this is my output for the problem you shared.

There are multiple interactions for customer_id=11, can you please check the output you shared again.

Thanks.

[DavidP · Accepted solution]

Hi @stevelogue1980 

I've added more description around the logic I used so that you can identify where I'm misunderstanding and change the logic.

Minor thing - I changed a few of the dates in your original data from Feb 30th to Feb 28th.

So Customer 10 doesn't exist in Customer details and therefore Is_Customer is set to False and Customer_TYPE to null().

I ignored Customer NULL.

The highlighted formula tool below is the one we have to look at.

1. I notice some Interaction dates are in the future - should they be ignored?

2. I used "months" as the unit to calculate the difference. For customer 13 you can see there is one example where the difference is 24 months, but actually, it's 24 months and 2 days. We should therefore probably use days as units, i.e. diff <= 2*365 days

3. There is also a question whether it should be <= 24 months or < 24 months. Again, using days would be better.

4. As things stand, each of customers 11,12,13 and 14 have at least 1 interaction within 24 months of Encounter date (although 13 is probably wrong).

The Summarize tool takes the maximum within each customer group, so if 1 or more instances per customer are true the answer is true for that customer and I've take true as being an existing customer. Do you want this to be the other way round, i.e. true = NEW?

Once you've had a chance to work through the steps above, let me know where you think we're going wrong and we can fix it.