New, Returning & Win back customers

[FONT=&quot]Hi Guys,[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]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.[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]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.[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]=IF(COUNTIFS($H:$H,"<"&H2-456,$G:$G,G2),"Returning","New")[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]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)[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]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".[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]Any help would be appreciated.[/FONT]

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:
https://www.mrexcel.com/board/threads/new-returning-winback-customers.1168934/
sidvix, for your information, you should always provide links to your cross posts.
This is a requirement, not just a request.
If you have cross posted at other places, please add links to them too.
Why? Have a read of http://www.excelguru.ca/content.php?184

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"

That's about it.

Thanks

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.

Thanks Pascal,

However i think you are using Power Query here.

Is it not possible by using a formula?

sidvix said:

Is it not possible by using a formula?

Click to expand...

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.

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.

What do you have against PowerQuery?

Could you answer these please?:

p45cal said:

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?

Click to expand...

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

i have nothing against it. I just have never used it. And I don't like submitting something I don't understand completely myself. I have time constraints and need to deliver.

I have managed to creat a UDF in the attached, however, its calculation is slow.
In the attached, in column I of Sheet2, only a few cells (164, that's 3 customers) have the function in, just to compare with the Power Query version; so far so good.
I've saved the file with autocalculation turned off (I don't know if this will carry through to when you open it) because for only these 164 cells to recalculate, it takes about 10 seconds, so for your entire range of 16,000 cells it's going to take 15 to 20 minutes (the Power Query takes less than 3 seconds for the whole range). So you may have to set calculation to manual and recalculate manually, or turn autocalculation back on. Also, be aware that unless you switch off recalculating-before-saving, it could take a long time to save the file while you wait for it to recalculate.

Since you have time constraints, there is no way I'm going to be able to give you a formula (and it would be a long formula) in a reasonable time.
I think the most practical solution is the Power Query one, which you already have.

Neither the PQ solution, nor the UDF require any special sort order of the original data.

I contacted Peter Bartholomew (https://chandoo.org/forum/members/peter-bartholomew.45701/ if you're at Chandoo) who's quite good at LET formulae and he came up with a formula solution involving a helper column.
You can see his formulae in columns E & F of the LET and PQ sheet of the linked-to file below. There is still the Power Query result on that sheet at column I to compare results.
In case you don't have the LET function available to you, I've converted his formulae to more standard formula on the Traditional sheet in that file, along with a plain values copy of the Power Query table, again for comparison/verification of results.

The Power Query solution is still the fastest, but the other two solutions aren't far behind.

The file: https://app.box.com/s/uz0c5gy0fagi73hsd83auckvidkcdrij

Delete as appropriate:

• Thanks p45cal, that worked well
• Those suggestions are all a waste of time