To start , for those that don't know what NPS is see this quick writeup:
Promoters (score 9-10) are loyal enthusiasts who will keep buying and refer others, fueling growth.
Passives (score 7-8) are satisfied but unenthusiastic customers who are vulnerable to competitive offerings.
Detractors (score 0-6) are unhappy customers who can damage your brand and impede growth through negative word-of-mouth.
To calculate your company’s Net Promoter Score (NPS®), take the percentage of customers who are Promoters and subtract the percentage who are Detractors.
So I'm trying to write this in postgres sql..the solution I came up with is:
SELECT
DATE_TRUNC('Month', a.createddate)
,a.on_deck_sales_agent__c
,(case
when a.net_promoter__c in (9, 10) then 1
when a.net_promoter__c in (7, 8) then 0
when a.net_promoter__c in (0, 1, 2, 3, 4, 5, 6) then -1
end) as net_promoter_score
FROM
sf_survey__c a
WHERE
a.createddate > '2015-01-01'
AND a.net_promoter__c IS NOT NULL
GROUP BY 1,2,3
Is there an easier way to do this? It seems like what i'm doing isn't correct. My ultimate goal is to have the query show me all net promoter scores by sales rep, broken down by date.
Hope this is helpful..and please let me know input/suggestions. The bolded portion is all screwed up as you can see. Thoughts?\
As a secondary question, how would I use the partiion over function to calculate the NPS by sales rep.
This post was edited by noflexzone on Mar 18 2015 05:26pm