# Thread: New, Returning & Win back customers

1. ## New, Returning & Win back customers

Hi Guys,

I need your guy's help in obtaining a formula that will determine if the customer is new, returning, or a win back based on criteria.

So I have attached an image where I have already determined a new customer and returning customer. Now a new customer will continue to be new until the full 5 quarters (456 days) and then it will change to returning.

=IF(COUNTIFS(\$H:\$H,"<"&H2-456,\$G:\$G,G2),"Returning","New")

What I am having a problem with is how can I change a customer from returning to win back if they haven't bought for a period of 24 months. (Like in the attached customer: ABC1)

Moreover, if possible not important; what if the customer account has been given to a new salesperson, can it switch to "new" or maybe "switch".

Any help would be appreciated.

2. In the picture (click on it if it's too small to see), is the Cat.1 column correct?
I've added/changed a row or two for testing:

The numbers in column O are the number of days since the first date after the previous >2 year gap.

One more thing:
cross posted (as near as counts) without links:
This is a requirement, not just a request.
Why? Have a read of http://www.excelguru.ca/content.php?184

3. Hey Pascal,

Thank you for the note on providing the links.

Seems right, however just want to confirm.

In the beginning all customers will be "New" and then after 5 quarters of regular transactions will be considered "Returning" onwards.

In the case where the customer stops buying for a period of 24 months and then returns and starts purchasing will be considered "Winback" for a period of 5 quarters once again and afterwards "Returning"

Thanks

4. In the attached at cell K1 of Sheet2 there's a table which uses the table on the same sheet at cell G2 as input.
I think I have managed to get the logic right, but you can confirm by adding/changing test data to the left table then in the right table, right-click and choose Refresh to update.

5. Thanks Pascal,

However i think you are using Power Query here.

Is it not possible by using a formula?

6. Originally Posted by sidvix
Is it not possible by using a formula?
It would take me a long time to devise such a formula, it'd be a long one, and I still don't know how I'd do it!
Would a user-defined function (a macro based function which would be used like a formula in a cell) do?
Do you have the SEQUENCE and SORT and FILTER worksheet functions available to you?

It was a Power Query solution, but could you nevertheless test it to see if I've got the logic right, then I'll have something to test against; ideally, if you could devise some devilish data to really test it, with the results you'd expect, and upload it here it would help a lot.

7. Hey Pascal,

I have attached the updated file with all customer #'s and transaction dates.

And yes, the logic is correct.

Thank you for helping me on this.

8. What do you have against PowerQuery?

Originally Posted by p45cal
Would a user-defined function (a macro based function which would be used like a formula in a cell) do?
Do you have the SEQUENCE and SORT and FILTER worksheet functions available to you?

10. Yes that would be fine. I have those functions available.

Page 1 of 2 1 2 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•