There are so many good threads around New Customer Analysis here on the forum so I dont want to create a new one that repeats the same questions.
I’ve made a New Customer Analysis that maybe is a bit different from the others and its working good (for my purpopse) so I just wanted to share it if anyone else could find it useful.
= the first time of a purchase. So only counted once.
= every unique/single purchase occasion by the customer. You will not show up as a RETURNING customer until your second purchase.
= the amount of customer that has made a purchase within rolling 12 period from the given month (i.e. all the returning customers minus resurrected).
= if no purchase was made withing a 12 month period after the last purchase occasion
= the first purchase after the transition from ACTIVE to LOST
Right or wrong, I reasoned that in case you are a NEW or RESURRECTED you are not yet a ACTIVE customer, but that could of course be change depending on your own thoughts.
The picture describes the current overall situation for my five test (real data) customers that i picked out to test and verify all the measures.
The only downside about this model is that its a bit slow, so if anyone finds some improvements on the measures I would gladly take that help, but its not necessary
New Customer Analysis EDNA.pbix (383.7 KB)
**Example of my:** > New Customers = > VAR ChurnTime = [Churn Time Frame Value] > VAR CurrentList = VALUES ( Sales[CustomerNumber] ) > VAR minDate = MIN ( Dates[Date] ) > VAR PriorList = > CALCULATETABLE ( > VALUES ( Sales[CustomerNumber] ), > FILTER ( > ALL ( Dates ), > Dates[Date] < minDate > ) > ) > VAR NewCustomers = EXCEPT ( CurrentList, PriorList ) > RETURN > COUNTROWS ( NewCustomers ) **Example of my:** > Resurrected Customers = > VAR ChurnTime = [Churn Time Frame Value] > VAR maxDate = MIN ( Dates[Date] ) > VAR maxDateBeforeChurnTime = DATE(YEAR(maxDate), MONTH(maxDate)-ChurnTime, DAY(maxDate)) > VAR CurrentCustomers = VALUES ( Sales[CustomerNumber] ) > VAR PriorCustomers = > CALCULATETABLE ( > VALUES ( Sales[CustomerNumber] ), > FILTER ( > ALL ( Dates ), > Dates[MonthStarting] > maxDateBeforeChurnTime && > Dates[MonthStarting] < maxDate > ) > ) > VAR BeforePriorCustomers = > CALCULATETABLE ( > VALUES ( Sales[CustomerNumber] ), > FILTER ( > ALL ( Dates ), > Dates[MonthStarting] <= maxDateBeforeChurnTime > ) > ) > > VAR LostCustomers = EXCEPT( BeforePriorCustomers, PriorCustomers ) > VAR ResurrectedCustomers = INTERSECT( LostCustomers, CurrentCustomers ) > RETURN > COUNTROWS ( ResurrectedCustomers )
Enjoy and have a nice weekend!