Thoughts about "New Customer Analysis"

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:

  1. NEW CUSTOMER
    = the first time of a purchase. So only counted once.

  2. RETURNING CUSTOMER
    = every unique/single purchase occasion by the customer. You will not show up as a RETURNING customer until your second purchase.

  3. 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).

  4. LOST CUSTOMER
    = if no purchase was made withing a 12 month period after the last purchase occasion

  5. 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.

image

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 :slight_smile:

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!

1 Like

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.

I also suggest that you check the forum guideline https://forum.enterprisedna.co/t/how-to-use-the-enterprise-dna-support-forum/3951. Not adhering to it may sometimes cause delay in getting an answer.

@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.

Hi @Tibbie

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 :smiley:

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

Solution - EDNA_Solution_New Customer Analysis.pbix (389.2 KB)

Thanks
Ankit J

1 Like

Hi Ankit,
thanks for taking the time and review the measures!
I will for sure take a look at them this week and come back with the results :slight_smile:

Hello, this is really great and helpful! How would I go about doing this if I only wanted to look at the current year instead of a rolling 12 months.