How to categorise monthly customers on a 12 month rolling basis?

[sparshpankaj]

I have the order details of customers of my small business. I want to track the effectiveness of one of my products, let's say it's Item A, which was launched as a response to Covid. Item A was launched in Apr 2020 and I'm tracking order behaviour of customers till June 2021. I have to categorise my customers as old and new for every month. Meaning if a customer buys Item A in May 2020, he will be new if he has no order for any of my products in the last 1 year (1st May 2020 to 30th April 2021) and old if he does have an order for any of my products in the last 1 year. I have to find old and new customers for each month from Apr 2020 to Jun 2021 based on this logic. Please help. My data has Phone Numbers (unique for each customer), date of order, item purchased, order amounts and a few other things. Can somebody please help?

[KarolinaRoza]

Hi @sparshpankaj,

I think you can achieve this using Alteryx. You can either create sorted list of orders for each Customer or you can create time difference between orders.

I can help you with that, but firstly please include sample worksheet with the input. It would be easier for me to start working on this.

Regards,

Karolina

[sparshpankaj]

Hi Karolina this is the sample data

There are also phone numbers in this dataset, which are used as unique ID for each customer. Problem Statement: Find New/Old customers on a rolling basis. A NEW customer (who has a specific Item A as a purchase irrespective of the date of purchase) should not have a purchase for any item in the last 365 days from his/her most recent order.

[apathetichell]

Hey,

I think I've seen a project kind of like this before - but what you posted isn't data - it's just a screengrab of your workflow. you'll probably want to do something like convert dates to alteryx dates. Clean your phone numbers.  group by phone number. sort by date. sample tool to select the two most recent dates and then do a comparison to see if the gap is 365 days...

[sparshpankaj]

I have already cleaned the phone numbers, and have Alteryx accepted dates. The problem is with the logic and understanding which tools to use. Can you please help with this?

[KarolinaRoza · Accepted solution]

hi @sparshpankaj 

Even you did not include data set.. I created the logic for you which I hope solves your problem.

As @apathetichell  mentioned you can:

- Sort orders by Phone number (unique ID) and Date of Order (ascending)

- Summarize data by Phone Number and Date of Order -> your list will me limited to list of Phone numbers and dates when they made orders (in case they have several orders at the same date you will have only one record for those orders)  

- Multi - Row Formula: you can either apply 

if 
DateTimeDiff([Date of order],[Row-1:Date of order],"days")>0 then 
DateTimeDiff([Date of order],[Row-1:Date of order],"days") else 0
endif

or 

DateTimeDiff([Date of order],[Row-1:Date of order],"days")

grouped by Phone Numbers ,

to calculate diff in days between orders

- then Formula tool to assign comments , if day diff  > 365 (and null or =0) then NEW, else OLD.

Let me know if it is sth you look for.

Karolina

[apathetichell]

@KarolinaRozaawesome workflow!