Forum Discussion
Tracking Customers Year Over Year
Hi,
You have to go to Modelling > New Table and then write that formula in the formula bar.
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.
Does this make more sense?
Great thank you so much, the only thing is am I not able to get a count of returning, new, or infrequent by year?
If I change the Mail # to a count it looks like it turns the whole display chart blank. I would love to be able to take the data and get a count of each of those customer types by year...
ThanksHi,
I have done it for new customer only. Please apply the same concept for the others yourself. You may download my PBI file from here.
Hope this helps.
I am trying to do the same thing. How would you account for any duplicate data points that appear?
Hi,
I do not understand your question. Share some data and show the expected result.
In the example you provided you only did New Customers, I just attempted to apply those steps to all customers but my totals do not come out? I had assumed it had to do with duplicated data but maybe I misunderstood your example. Are you able to provide a file with the steps applied to Returning and Infrequent as well?
Hi,
Share some data and show the expected result.
I want to use the same logic the previous poster used. We use "Reservation Number" in place of Mail #. I tried to follow the sample that you provided but you only provided the steps for "New". Can you explain how you apply the logic to Infrequent and Returning?
