Hi all,
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.
Definitions:
NEW CUSTOMER
= the first time of a purchase. So only counted once.
RETURNING CUSTOMER
= every unique/single purchase occasion by the customer. You will not show up as a RETURNING customer until your second purchase.
ACTIVE CUSTOMER
= 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).
LOST CUSTOMER
= if no purchase was made withing a 12 month period after the last purchase occasion
RESURRECTED CUSTOMER
= 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
Hi @Tibbie, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.
Use the forum search to discover if your query has been asked before by another member.
When posting a topic with formula make sure that it is correctly formatted to preformated text </>.
Use the proper category that best describes your topic
Provide as much context to a question as possible.
Include demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.
@Tibbie It looks like you have a measure in your Key Measures table that is throwing an error (“Purshase Freq”). I didn’t see it was used anywhere, and when I deleted it the model seemed to run a touch faster - but that could be my imagination. Is the speed issue coming up when you use the slicer or at some other time?
The model is super fast when I only have the test model that contains five customers, but when I load the whole table that contains 5 million rows it becomes slow.
The measures are working, but they are not optimized in anyway unfortunately
Thanks for posting your question @Tibbie, To receive a resolution in a timely manner please make sure that you provide all the necessary details on this thread.
Here is a potential list of additional information to include in this thread; demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.
Including all of the above will likely enable a quick solution to your question.
I have gone through your calculations and they seems correct. I have tried a different approach for 2 measures, getting same values. However, don’t see much difference in return time b/w yours and mine measures.
You can test them out with your 5 million records and check if getting any difference. If helpful, you can Recreate for other measures or let me know and I will create.
If nothing much, atleast you will get a new approach to perform the analysis
New Customers_Ankit =
VAR minDate =
MIN ( Dates[Date] )
VAR Newcust =
COUNTROWS (
FILTER (
VALUES ( Sales[CustomerNumber] ),
COUNTROWS ( CALCULATETABLE ( Sales, Dates[Date] < minDate ) ) = 0
)
)
RETURN
Newcust
Active Customers_Ankit =
VAR ChurnTime = [Churn Time Frame Value]
VAR maxDate = MIN ( Dates[Date] )
VAR maxDateBeforeChurnTime = DATE( YEAR(maxDate), MONTH(maxDate) - ChurnTime, DAY(maxDate) )
VAR ActiveCust =
COUNTROWS (
FILTER (
VALUES ( Sales[CustomerNumber] ),
COUNTROWS ( CALCULATETABLE ( Sales, Dates[Date] < maxDate && Dates[Date] >= maxDateBeforeChurnTime) ) > 0
)
)
RETURN
ActiveCust
