The formula is:
Count of NPS Promoters/ SUM NPS Responses - Count of NPS Detractors /SUM NPS Responses * 100
Best answer by dan_ahrens
View originalCreating a formula in Rules Engine based on NPS data by CSM
I am trying to replicate a formula I have in one of my Salesforce reports to calculate the NPS score total by CSM. The end goal is to be able to look at all NPS scores given this QTR, by CSM and have their cumulative score for all of those NPS responses. Though, I am not sure this type of formula is doable in Gainsight.
The formula is:
Count of NPS Promoters/ SUM NPS Responses - Count of NPS Detractors /SUM NPS Responses * 100
The formula is:
Count of NPS Promoters/ SUM NPS Responses - Count of NPS Detractors /SUM NPS Responses * 100
Userlevel 7
+6
Ky, prepare yourself, some algebra is about to blow your mind (as it did mine):
Check out all the responses to Scott Morris' comment on this post: https://community.gainsight.com/gainsight/topics/true-nps-calculation-and-nps-widget
(You'll likely need to click the "View previous..." link to expand them all.)
In short, you're going to create a basic calculated field on the NPS response object, and then simply do an Average on that field in the Rule.
Check out all the responses to Scott Morris' comment on this post: https://community.gainsight.com/gainsight/topics/true-nps-calculation-and-nps-widget
(You'll likely need to click the "View previous..." link to expand them all.)
In short, you're going to create a basic calculated field on the NPS response object, and then simply do an Average on that field in the Rule.
Thanks Seth, so the issue here is getting the actual accurate NPS Scores requires a specific NPS formula. I can't just take the average, which is why I need to be able to replicate that formula exactly.
Userlevel 7
+6
Hi Ky -- the trick that I discovered through those comments is that the algebra works out identically, if you give the promoters a score of 100, the detractors a score of -100, the passives a score of 0, and average those scores together. You'll get the same NPS value per CSM as if you'd done the calculation shown in your screenshot.
I mean, you could literally put the equations on opposite sides of an equals sign, and do the algebraic reduction until you end up with a formula of "1=1" 🙂
I mean, you could literally put the equations on opposite sides of an equals sign, and do the algebraic reduction until you end up with a formula of "1=1" 🙂
Awesome, I will try this! Thank you Seth!
Userlevel 7
+2
You can write a rule to do this.
Use a pivot task to bucket your responses:
And then a formula field in the transform task using the output fields you created in the pivot task:
Use a pivot task to bucket your responses:
And then a formula field in the transform task using the output fields you created in the pivot task:
Thanks Dan! This is exaclty what I was looking for that I thought I could do. Though, when I am doing my pivot task, it makes me choose a field to group by and then aggregates my show me fields. How can I avoid this?
Also, I have created everything that you had suggested. Though, my end goal is to use this in reporting. So that I look at that final value I created from the formula field by each CSM. Though, my first thoughts were to Load this to a new field in SFDC, but that won't work if I am grouping this by CSM in my rule. Not sure how to get the results from my rule out in the way that I can use it in reporting to look at the Total NPS Score by CSM for all of their NPS's created this QTR.
Also, I have created everything that you had suggested. Though, my end goal is to use this in reporting. So that I look at that final value I created from the formula field by each CSM. Though, my first thoughts were to Load this to a new field in SFDC, but that won't work if I am grouping this by CSM in my rule. Not sure how to get the results from my rule out in the way that I can use it in reporting to look at the Total NPS Score by CSM for all of their NPS's created this QTR.
Userlevel 7
+2
Hi Ky,
If you want to use this to report on NPS by CSM, this is how the pivot would look:
What you'd want to do in order to report on this is to write it to a custom MDA table that would show the NPS score by CSM.
Here's help on creating a new MDA object: https://support.gainsight.com/Product_Documentation/Data_Management_and_Integrations/Managing_Data_I...
If you want to use this to report on NPS by CSM, this is how the pivot would look:
What you'd want to do in order to report on this is to write it to a custom MDA table that would show the NPS score by CSM.
Here's help on creating a new MDA object: https://support.gainsight.com/Product_Documentation/Data_Management_and_Integrations/Managing_Data_I...
Reply
Sign up
If you ever had a profile with us, there's no need to create another one.
Don't worry if your email address has since changed, or you can't remember your login, just let us know at community@gainsight.com and we'll help you get started from where you left.
Else, please continue with the registration below.
Welcome to the Gainsight Community
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.