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 

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

New Old Customer.yxmd