Calculating Your Overall Net Promoter Score In Dataverse

I wrote about this back in 2019 back when Dataverse was the Common Data Service, and Customer Voice was Forms Pro, AND before Realtime Marketing and Customer Insights Journeys. So time for a bit of a revamp! This post will look at how you can calculate your organisations overall Net Promoter Score in Dataverse. It doesn’t really matter what is updating your customers Net Prompter Score feedback, as long as you use the same field on the Contact record each time. In my example, the NPS is being captured via a Realtime Marketing form. So, if your company uses NPS, this might be one for you. Let’s get started!

First off make sure you have a global choice field called NPS Type. The values should be Promoter, Passive and Detractor. You can add a little emoji to the label if you like, and also colour code each choice so that it shows up in views. All adds to the quick visual impact for users in a model-driven app.

Next we need some fields on the Contact table. NPS is a whole number which will store the actual NPS rating given by them. The next two you don’t need but if you are capturing feedback you might want to capture some text and also the date they gave that feedback.

The NPS Type field is a formula field. This allows us to calculate which value from the global choice field to use based on if the person gives a 6 or below which is a Detractor, a 7 or 8 which is Passive, or a 9 or 10 which is a Promoter.

If(
   NPS <= 6, [@'NPS Type'].Detractor,
   NPS >= 9, [@'NPS Type'].Promoter,
   (NPS = 7) Or (NPS = 8), [@'NPS Type'].Passive,
    Blank()
)

Arrange them on your Contact form so you can see the information. Note that the NPS Type will be locked as the value is always set by the formula above.

The next part is not required, but it is a nice way to see how happy your individual clients are. This allows us to look at the NPS feedback for all Contacts at an Account and understand if they are all happy, or just one or two Contacts are. Gives us better understanding and makes us informed when building on those relationships. For this, we need a few more fields. An individual gives NPS with a value of 0 to 10. Your NPS Score overall is anywhere from negative 100 to positive 100. We will use this on an Account. Hopefully most of the fields below are self explanatory, but we have a mixture. The ones with fx next to them are formula fields.

For the whole number fields, we still need to do rollups to count the number of Contacts that match some filtering. The NPS Contacts is all Contacts related to an Account via the Company Name link, then filtered to only count those that are Active and where the NPS field contains a value. Then our aggregation is the Count of all those Contacts.

Then we have three additional roll up fields, one for Promoters, one for Passives and one for Detractors. We adjust the filter slightly on these so that it only counts the relevant ones based on their response.

For the formula fields, we need the percentage of those that are Promoters, Passives and Detractors. So use the following formula for each of those three fields, just adjusting which Number field you use each time. You need one for each type, Promoters Percentage, Passives Percentage and Detractors Percentage.

'Promoters Number'/'NPS Contacts'*100

Now on the Account we will add in the Net Promoter Score formula. However, because zero can be a legit score, we only want to see a number if there are any Promoters or Detractors, otherwise we will leave this field blank. It could be confusing otherwise. If the score is zero, we know it is intentional rather than because of a bad formula. So here we check to make sure either the Promoters Number field or the Detractors Number field are greater than zero. If they are, we subtract the Detractors Percentage from the Promoters Percentage. If not, the field is left blank.

If('Promoters Number'>0 Or 'Detractors Number'>0,'Promoters Percentage'-'Detractors Percentage', Blank())

This next field is not required, but it allows you to do a nice little visual on the Account record that is colour coded. So add a formula field called NPS Colour, and use something like this. We are checking the Net Promoter Score field. If blank, it will be a light grey colour, then depending on the value we can go from Red, Orange, Light Green and Dark Green. By all means experiment with the colours, but note that some don’t work, and they have to be named specifically (no spaces between Light/Dark and Green for example).

If(
   IsBlank('Net Promoter Score'), "LightGrey",
   'Net Promoter Score' < 0, "Red",
   'Net Promoter Score' <= 29, "Orange",
   'Net Promoter Score' <= 69, "LightGreen",
   'Net Promoter Score' >= 70, "DarkGreen",
   "Grey"
)

Now you can add a nice NPS tab on your Account form. Lay it all out however you see fit. If you want to use the NPS Colour, you can add on a Radial Score Control to your Net Promoter Score field. Then you can use the NPS Colour field to show the colour based on the value.

This is what it looks like on the Account. We can see 2 Contacts have provided feedback, both were Promoters so we have the highest possible NPS value for this company.

Last thing, we need to almost repeat what we did on the Account on to a new table. It used to be (back in 2019 at least!) that we could add all of this on to the Business Unit table and it worked beautifully. That is no longer available to us I guess based on security related changes over the years. So, now I have added in a new table called NPS Summary. In theory you could add in multiple records for this, or create yearly and so on, but in reality I just need to create one record for it. My NPS Summary custom table contains the following fields. Follow the logic above to create the Promoters Percentage, Passives Percentage, Detractors Percentage, NPS Colour and Net Promoter Score fields. All of the others (Promoters Number, Passives Number, Detractors Number, Total Contacts and Total NPS Contacts) we will set using Power Automate.

Your flow ideally will run on a regular basis, daily, weekly etc. We then need 5 different List Rows steps. Each one will go off and get the number of records for each part, so each will have slightly different filtering. Note that the total number of contacts isn’t needed for any calculations, I just thought it might be helpful to see how many out of your entire database exist vs having given NPS feedback. For each List rows step, you will use a different filter in the Fetch XML part, all provided below.

Keep in mind, check the query and replace the fields names that start with mvw_ with the correct field names from your environment.

Total Contacts query.

<fetch aggregate="true">
  <entity name="contact">
    <attribute name="contactid" alias="totalContacts" aggregate="count" />
    <filter type="and">
      <condition attribute="statecode" operator="eq" value="0" />
    </filter>
  </entity>
</fetch>

NPS Contacts query.

<fetch aggregate="true">
  <entity name="contact">
    <attribute name="contactid" alias="totalResponses" aggregate="count" />
    <filter type="and">
      <condition attribute="mvw_nps" operator="not-null" />
      <condition attribute="statecode" operator="eq" value="0" />
    </filter>
  </entity>
</fetch>

NPS Promoters query.

<fetch aggregate="true">
  <entity name="contact">
    <attribute name="contactid" alias="promoters" aggregate="count" />
    <filter type="and">
      <condition attribute="mvw_nps" operator="ge" value="9" />
      <condition attribute="statecode" operator="eq" value="0" />
    </filter>
  </entity>
</fetch>

NPS Passives query.

<fetch aggregate="true">
  <entity name="contact">
    <attribute name="contactid" alias="passives" aggregate="count" />
    <filter type="and">
<condition attribute="mvw_nps" operator="ge" value="7"/><condition attribute="mvw_nps" operator="le" value="8"/>
      <condition attribute="statecode" operator="eq" value="0" />
    </filter>
  </entity>
</fetch>

NPS Detractors query.

<fetch aggregate="true">
  <entity name="contact">
    <attribute name="contactid" alias="detractors" aggregate="count" />
    <filter type="and">
      <condition attribute="mvw_nps" operator="le" value="6" />
      <condition attribute="statecode" operator="eq" value="0" />
    </filter>
  </entity>
</fetch>

Then at the bottom add in an update a row step, and find your NPS Summaries table. I suggest you add a record first, then get the GUID (right at the end of the URL) and add it here. You could of course use an environment variable or other means to find the right record, but in my case it was easier to hard code. For each value we need to update, we have a different expression. Assuming you copied the naming convention I used for each List rows step and you copied the FetchXML queries I gave you, you should be able to copy and paste each expression in to the right spot.

Total Contacts expression.

@{first(body('Total_Contacts')?['value'])?['totalContacts']}

Total NPS Contact expression.

@{first(body('NPS_Contacts')?['value'])?['totalResponses']}

NPS Promoters expression.

@{first(body('NPS_Promoters')?['value'])?['promoters']}

NPS Passives expression.

@{first(body('NPS_Passives')?['value'])?['passives']}

NPS Detractors expression.

@{first(body('NPS_Detractors')?['value'])?['detractors']}

Then you can run your flow (and it will run on a scheduled basis after that). You should then see the values populated on your NPS Summary record. I laid mine out the same way as on the Account form, along with the radial control to show the colour. So we can see an overall NPS of 94, along with the total number of NPS Contacts and broken down by promoters, passives and detractors.

You can also customise the form for your NPS Summary. So here I have added a subgrid with all of the NPS Contacts that have provided feedback along with the score, type, date it was submitted and the feedback.

Then I have a tab for any Accounts that have a score along with the number of NPS Contacts, total number of Contacts linked to the Account and just some information about what kind of Account it is with a Customer Relationship field.

Original Post http://meganvwalker.com/calculating-your-overall-net-promoter-score/