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.
