We would like to count total customers each month who had shopped at some point in the previous years (they have placed at least one order on the website) but then they have not shopped in the 12 months prior to the reporting month. Each month we want to show what this count of customers had been at that point in time, but the issue we encounter is that if one of these customers returns to shop now or anytime during the period we are reporting on, they will be taken out of the historical count of 12 months inactive customers in the month reported previously. As an example, please see below:
Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec |
2015 | No | No | No | No | No | No | No | No | No | No | YES | No1 |
Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec |
2016 | No2 | No3 | No4 | YES | No1 | No2 | No3 | No4 | No5 | No6 | No7 | No8 |
Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec |
2017 | No9 | No10 | No11 | No12 | No13 | YES | No1 | No2 | No3 | No4 | No5 | No6 |
Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec |
2018 | No7 | No8 | No9 | No10 | No11 | No12 | No13 | No14 | YES | No1 | No2 | No3 |
- A customer purchased in Apr 2016.
- They had 13 months of No purchases.
- In May 2017, this customer has to be counted in the total count of 12 months inactive customers for May 2017
- However, because we are not looking at this in May 2017 but today and trying to pull historical data, the customer has purchased again in Jun 2017 and Apr-16 is no longer their last order date. They are active again and we can’t see that they had lapsed previously. This behaviour reduces the total count of 12 month inactive customers each month and is preventing us from understanding behaviours relating to customers re-engaging with our brand.
I've attached an example how we calculate the numbers.
We've tried below formula. but this works for today's date not for the historical data.
=count ({< [MaxOrderDate]={"<=$(=MonthEnd(AddMonths(EndDate,-26)))"}>} distinct [EMSCustomerNo])
=count ({< [MaxOrderDate]={"<=$(=MonthEnd(AddMonths(EndDate,-25)))"}>} distinct [EMSCustomerNo])
=count ({< [MaxOrderDate]={"<=$(=MonthEnd(AddMonths(EndDate,-24)))"}>} distinct [EMSCustomerNo])
=count ({< [MaxOrderDate]={"<=$(=MonthEnd(AddMonths(EndDate,-23)))"}>} distinct [EMSCustomerNo])
count ({< [MaxOrderDate]={"<=$(=MonthEnd(AddMonths(EndDate,-22)))"}>} distinct [EMSCustomerNo])
=count ({< [MaxOrderDate]={"<=$(=MonthEnd(AddMonths(EndDate,-21)))"}>} distinct [EMSCustomerNo])
.
.
.
.
=count ({< [MaxOrderDate]={"<=$(=MonthEnd(AddMonths(EndDate,-14)))"}>} distinct [EMSCustomerNo])
EndDate =date(floor(monthend(Today())), 'DD/MM/YYYY')
Attached the table we want to create.
Do you have any suggestions how we can solve this problem?
