Page 1 of 2 1 2 LastLast
Results 1 to 10 of 13

Thread: New, Returning & Win back customers

  1. #1
    Seeker sidvix's Avatar
    Join Date
    Apr 2021
    Location
    Ontario, Canada
    Posts
    6
    Articles
    0
    Excel Version
    365

    New, Returning & Win back customers



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.
    Attached Files Attached Files

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,940
    Articles
    0
    Excel Version
    365
    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:
    Click image for larger version. 

Name:	2021-04-22_230737.png 
Views:	14 
Size:	14.8 KB 
ID:	10450
    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/thread...omers.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

  3. #3
    Seeker sidvix's Avatar
    Join Date
    Apr 2021
    Location
    Ontario, Canada
    Posts
    6
    Articles
    0
    Excel Version
    365
    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

  4. #4
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,940
    Articles
    0
    Excel Version
    365
    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.
    Attached Files Attached Files

  5. #5
    Seeker sidvix's Avatar
    Join Date
    Apr 2021
    Location
    Ontario, Canada
    Posts
    6
    Articles
    0
    Excel Version
    365
    Thanks Pascal,

    However i think you are using Power Query here.

    Is it not possible by using a formula?

  6. #6
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,940
    Articles
    0
    Excel Version
    365
    Quote Originally Posted by sidvix View Post
    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. #7
    Seeker sidvix's Avatar
    Join Date
    Apr 2021
    Location
    Ontario, Canada
    Posts
    6
    Articles
    0
    Excel Version
    365
    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.
    Attached Files Attached Files

  8. #8
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,654
    Articles
    0
    Excel Version
    Office 365 Subscription
    What do you have against PowerQuery?
    Ali
    Enthusiastic self-taught user of MS Excel!

  9. #9
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,940
    Articles
    0
    Excel Version
    365
    Could you answer these please?:
    Quote Originally Posted by p45cal View Post
    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. #10
    Seeker sidvix's Avatar
    Join Date
    Apr 2021
    Location
    Ontario, Canada
    Posts
    6
    Articles
    0
    Excel Version
    365
    Yes that would be fine. I have those functions available.

Page 1 of 2 1 2 LastLast

Posting Permissions

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