Assigning customers to a segment, over rolling time periods

Hello,

I'm wondering if anyone can help give me any direction / pointers or advice on my issue.


We have data on the purchasing activity of our customers over the past number of years. What we are trying to achieve is the following:

- At the end of each year, we need to count the number of customers who fall into one of the pre-defined segments (as defined in the sample data workbook and below).

- Ideally, we'd like to not only do these counts at the end of each year but also at the end of every month - so that we can look at the rolling figures.



The issues I'm having is in setting up the formulas to accurately assign a segment label to everyone;

- Highlighting individuals who have "Never purchased" (i.e. we have their contact details but they have never made a purchase) is easy - the sum of their purchases = 0.

- Highlighting those who are "Active" (i.e. have made a purchase within the last 3 years - we have long sales cycles) is easy IF they have never lapsed (see below). If someone made a purchase a long time ago and then stopped purchasing, they would fall into the "Lapsed" segment, and if they then make a purchase again, they then become classed as "Reactivated" (not "Active").

- Highlighting those who have "Lapsed" I can sometimes get to work with various IF, AND statements, but I haven't been able to get a formula that consistently counts everyone accurately.

- Highlighting those who are "Reactivated" is proving the most challenging, because to be in this segment, you have to have first been active, then been lapsed and then made a purchase.



In the attached, I have shown some made up figures and highlighted what anyone's segment is at a given point in time. Then, in columns BP - BS, I've shown what their status should be at the end of 2022. And for columns BU onwards, I've done a similar thing, for each prior year.

I've spent so many hours trying out various formulas (mainly of the type where I sum the number of purchases over a given 36 month time period and try and use an AND statement to see if they've made purchases prior to this time period) without any success on this.


Thanks

Did you upload your correct sheet?

I don't see any data and expected results in your sheet.

Hi HansDouwe, not sure what happened sorry, but I've tried to reupload it again. Hopefully this works now?

I am not able to see anything below row 36 in the file.

Post #1 indicates that there are conditional formatting rules which could be applied to cells C39:BJ49. Please tell us what those rules are, or better yet apply them to another copy of the file, so that we don't have to reinvent the wheel.