NPS (net promoter score) based on customer order dates

[Anonymous]

Hi Community  -  

Our Marketing team recently got back some NPS data and now is asking for some analysis of it.  

In particular, they want to see:   NPS score for customers who have not purchased anything in over 365 days.    And the same thing for equal/under 365 days.    For now, I am just using the dates from the Orders table.  (I do not have a date table at this time).  

For some reason, I am getting a blank value returned for the "over one year" measure.   But the other works fine.   

My intention is to be able to have a 2 column table visual, with these two metrics in the left column, and their average NPS score in the right column.   Been trying to figure out how to put all of this into a DAX measure, if indeed that is the best approach.  

NPS Over 1 Year = IF([Days Since Last Purchase] > 365,[Average NPS Score],BLANK())

NPS Equal_Under 1 Year = IF([Days Since Last Purchase] <=365,[Average NPS Score],BLANK())

Days Since Last Purchase = IF(ISBLANK([Customer Last Purchase Date]),Blank(),VALUE([Overall Last Purchase Date] - [Customer Last Purchase Date]))

Average NPS Score = AVERAGEX('Retently-feedback-export-13 10','Retently-feedback-export-13 10'[NPS Score])

[mattww]

HI @Anonymous

Reading between the lines of what you've described, it sounds like you have a Customer table where you have calculated the last order date for that customer. If so, I would suggest a simple calculated column on that table

IF([Days Since Last Purchase] <=365,"Under 1 year","Over 1 year")

You could of course make that IF a bit more complicated if you wanted to take into account those with no last purchase date at all

If that Customer table is then related to your NPS table, then you can just create a normal AVERAGE measure for the NPS and use the calculated column to split out your results in your visual

AVERAGE('Retently-feedback-export-13 10','Retently-feedback-export-13 10'[NPS Score])

That said, an NPS score doesn't really lend it self to being averaged. You would be better categorising each response as Detractor, Passive or Promoter, then calculating the NPS Score as Number of Promoters - Number of Detractors / Total Responses * 100

(measure) Number of Responses = COUNTROWS('Retently-feedback-export-13 10','Retently-feedback-export-13 10')

(calc column) NPS Group = IF([NPS Score] >= 9,"Promoter",IF([NPS Score] >= 7,"Passive","Detractor"))

(measure) Number of Promoters = CALCULATE([Number of Responses],'Retently-feedback-export-13 10','Retently-
feedback-export-13 10'[NPS Group] = "Promoter")

(measure) Number of Passives = CALCULATE([Number of Responses],'Retently-feedback-export-13 10','Retently-feedback-export-13 10'[NPS Group] = "Passive")

(measure) Number of Detractors = CALCULATE([Number of Responses],'Retently-feedback-export-13 10','Retently-feedback-export-13 10'[NPS Group] = "Detractor")

(measure) Aggregate NPS = ([Number of Promoters] - [Number of Detractors]/[Number of Responses]) * 100

[Anonymous]

@mattww   You have read between the lines very well!    Yes, I have all of the Passive, etc...values in place already.    Let me work on implementing the ideas and I'll come back to post/mark as solved in a bit.   But looks promising so thank you for the detail!

[mattww]

Great, good luck with it and let me know if you need anything further

If it all works out, don't forget to pop back and accept the answer

Good luck

[Anonymous]

@mattww   Oh, and yes I do have customers with no purchase dates at all...so any help with contending with that in the IF statement? 

[Anonymous]

Hi @mattww    One thing that initially seems off is the aggregate.    Our score should be about 37 and that is what I get if I average the NPS scores.  This lines up with what Retently has as well.    But using the aggregate measure I get:  

[mattww · Accepted solution]

Hi @Anonymous ,

For the blank dates, I'd just extend your IF formula

IF(ISBLANK([Days Since Last Purchase]),"No purchase",IF([Days Since Last Purchase] <=365,"Under 1 year","Over 1 year"))

The odd NPS score is definitely wrong, it's probably some bad bracketing in my original measure, try this:

Aggregate NPS = (([Number of Promoters] - [Number of Detractors])/[Number of Responses]) * 100

[amitchandak]

@Anonymous , if you are using a measure like [Average NPS Score] in a new column, I doubt that will work fine. You need use column in columns