Spreadsheet: 859838d1708080038-assigning-customers-to-a-segment-over-rolling-time-periods-segmentation-movement-logic-and-formulas-1-
Sheet: Raw data

 | KEY
 |  | Active | Individual has purchased at least once in the last 36 months - either as their first ever purchase, or following no periods of being lapsed (i.e. they make regular purchases)
 |  | Lapsed | Individual at one point had purchased in a 36 month time period, but then they made no further purchases
 |  | Never purchased | Have never purchased to date
 |  | Reactivated | Individual had been active, then they lapsed and upon making another purchase they become classed as "Reactivated" - they will remain as ‘Reactivated’ for the next 35 months, after which point if they make another purchase then they will remain as "Reactivated", otherwise they will become "Lapsed" again
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Counts |  |  |  |  | Counts |  |  |  | Counts |  |  |  | Counts
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | STATUS at the end of December 2022 |  |  |  |  | STATUS at the end of December 2021 |  |  |  | STATUS at the end of December 2020 |  |  |  | STATUS at the end of December 2019
 |  | <01/09/2017 | 2017 | 2018 | 2018 | 2018 | 2018 | 2018 | 2018 | 2018 | 2018 | 2018 | 2018 | 2019 | 2019 | 2019 | 2019 | 2019 | 2019 | 2019 | 2019 | 2019 | 2019 | 2019 | 2019 | 2020 | 2020 | 2020 | 2020 | 2020 | 2020 | 2020 | 2020 | 2020 | 2020 | 2020 | 2020 | 2021 | 2021 | 2021 | 2021 | 2021 | 2021 | 2021 | 2021 | 2021 | 2021 | 2021 | 2021 | 2022 | 2022 | 2022 | 2022 | 2022 | 2022 | 2022 | 2022 | 2022 | 2022 | 2022 | 2022
Date customer joined our database | Person ID |  | Sep | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec | Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec | Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec | Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec | Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec | Active | Lapsed | Never purchased | Reactivated | Comments | Active | Lapsed | Never purchased | Reactivated | Active | Lapsed | Never purchased | Reactivated | Active | Lapsed | Never purchased | Reactivated
2022-09-15 00:00:00 | 1 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 1 |  |  |  | Active | N | N | N | Has made at least one purchase in the last 3 years and has not lapsed | N | N | Never purchased | N | N | N | Never purchased | N | N | N | Never purchased | N
2013-05-10 00:00:00 | 2 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 1 |  |  |  |  | 1 |  |  | Active | N | N | N | Has made at least one purchase in the last 3 years and has not lapsed | N | N | Never purchased | N | N | N | Never purchased | N | N | N | Never purchased | N
2018-05-31 00:00:00 | 3 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | N | N | Never purchased | N | This person has never made a purchase | N | N | Never purchased | N | N | N | Never purchased | N | N | N | Never purchased | N
2014-12-14 00:00:00 | 4 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 1 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | N | Lapsed | N | N | This person was active and remained as active for 36 months, after which they did not purchase again and so became lapsed | Active | N | N | N | Active | N | N | N | Active | N | N | N
2011-06-26 00:00:00 | 5 |  |  |  |  |  |  |  |  |  |  |  |  | 1 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 1 |  |  |  |  |  |  | N | N | N | Reactivated | This person was active between Jan 2019 - Dec 2021, after this 36 month period, they lapsed (as they didn't make a purchase) and remained as lapsed until they purchased again in June 2022, when they became "Reactivated".. They will remain classed as "Reactivated" for 36 months (inclusive of June 2022) | Active | N | N | N | Active | N | N | N | Active | N | N | N
2015-09-24 00:00:00 | 6 |  |  |  |  |  | 1 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 1 |  |  |  |  |  | N | N | N | Reactivated | Similar to above - the person was active for a time, then became lapsed. They then purchased again and as such became "Reactivated". | N | Lapsed | N | N | Active | N | N | N | Active | N | N | N
2015-04-11 00:00:00 | 7 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 1 |  |  |  |  |  |  |  |  |  |  |  |  | 2 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Active | N | N | N | This person has made at least 1 purchase in the last 3 years. | Active | N | N | N | Active | N | N | N | N | N | Never purchased | N
2012-10-16 00:00:00 | 8 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 1 |  |  |  |  |  |  |  |  | 1 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Active | N | N | N | This person has made at least 1 purchase in the last 3 years. | Active | N | N | N | Active | N | N | N | Active | N | N | N
2013-07-18 00:00:00 | 9 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Active | N | N | N | This person has made at least 1 purchase in the last 3 years. | Active | N | N | N | Active | N | N | N | N | N | Never purchased | N
2005-08-30 00:00:00 | 10 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 1 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 1 |  |  |  |  |  |  | Active | N | N | N | This person has made at least 1 purchase in the last 3 years. | Active | N | N | N | Active | N | N | N | Active | N | N | N
2017-08-22 00:00:00 | 11 |  |  |  |  |  | 2 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | N | Lapsed | N | N | This person was active and remained as active for 36 months, after which they did not purchase again and so became lapsed | N | Lapsed | N | N | Active | N | N | N | Active | N | N | N