Flag columns to capture Repeated Customers information

Question

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.

wf_Repeated Customer with Flags.yxmd

grazitti_sapna · Accepted Answer

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 Answer

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.