I am now revisiting this topic, if I wanted to calculate Returning Customers as individuals who have made at least two purchases over five years, and add a new category as Infrequent Customers as individuals who made only one purchase in the past five years, and New Customer as someone who purchased this year but none in the previous five years. Could that be done using a similar formula?
So I would like to have it so that if the Mail # appears for five consecutive years for them to be considered Returning. If the Mail # appears between 4-5 times in five years to be Considered Infrequent. When the Mail # occurs for the first time for it to show up as New.
The first year a Mail # appears they are considered New, and recieve a 1.
The Second year a Mail # appears within a five year period they are no longer considered New and are now considered Infrequent, so they have a 0 for the New column, and a 1 for the Infrequent column.
The Third year a Mail # appears within a five year period they remain as Infrequent and get a 1 in the Infrequent column.
The Fourth Year a Mail # appears within a five year period they remain as Infrequent and get a 1 in the Infrequent column.
The Fifth Year a Mail # appears within a five year period they are no longer considered Infrequent and are now considered Returning, so they have a 0 for the Infrequent column, a 0 for the New column and a 1 for the Returning column.
If a Mail # appears each year for more than five consecutive years they recieve a 1 in the Returning column for each of the years after year five.