Hi,
I am finding the date when a customer is lost and reactivated.
1) Multiple visit scenario:
A) Lost in the middle and reactivated
A customer is considered lost if he/she has not visited for more than 18 months. this is found by finding the difference between any two consecutive visits is more than 18 months ( or 548 days). The lost date is calculated by adding 548 days to he earlier of the two consecutive days. The later of the two days is considered as the reactived date. Example : customer_id 4 in the excel that I have shared.
B)Lost at the end:
The customer had multiple visits. The date difference between any two visists were not more that 548 days so the customer was active for the whole time but since his last visit it has been more than 548 days when compared to today. In this case the customer was lost by adding 548 days to his last visit date. Example : customer_id 1 in the excel that I have shared. This customer will not have a reactived date
2) Single visit scenario:
If the customer visited only once that we compare the visit date to today's date and find if it is more than 548 days . If it is more than 548 days then we add 548 days to the visit date and call it as his lost date. Example : Customer_id 6 in the excel that I have shared
Hope this helps
