DIY NPS KPI’s Part 2 -
Performance Analytics Build Out
Welcome back!
In part one we walked through the steps to configure your system to send out a new survey enabling you to track NPS score. If you haven’t yet configured the new survey I recommend you jump back to that post and start there:
Once that is done, we get to the fun stuff!
Performance Analytics is uniquely suited to track and monitor the trends of your NPS score. Being in-platform, it makes it much more straightforward to analyze the data and also create connections between the survey results and supporting data, such as Assignment Groups or Business Services. Achieving success with Performance Analytics is just like success in other applications, meaning you need to start with a cohesive plan and design rather than just start building things. Performance Analytics content are assembled like building blocks, so it is critical to map out the components and how they will fit together. Let’s walk through that process by starting at the end, with a list of the KPIs/Indicators we wish to see.
The first and most important is NPS Score, which is the % of Detractors minus the % of Promoters. Performance Analytics can create averages/percentages in 2 ways, either creating an average of a value included on records, or by using a formula indicator to create a calculation. For these % we will be using formula indicators with calculations. Here are the indicators necessary to get the info we desire:
Automated Indicators:
Total number of NPS Responses
Number of Promoters
Number of Detractors
Formula Indicator:
% Promoters (Number of Promoters / Total number of NPS Responses)
% Detractors (Number of Detractors / Total number of NPS Responses)
NPS Score (% Promoters - % Detractors)
Those are the bare bones KPIs, but thinking holistically, what else can we do to help make the management of the process more efficient and successful? Here are some additional KPIs / Indicators that should also be added:
Additional Automated Indicators:
Number of NPS Surveys not taken
“Why?” NPS Comment Word Cloud
Number of Passives
Additional Formula Indicators:
Response % for NPS surveys
% Passives (Number of Passives / Total number of NPS Responses)
Indicator Sources:
Now that we know the Indicators we want, we should plan out the Indicator Sources. The Indicator Sources are records which are re-used by Indicators, and reference a specific table and filter to begin to segment the data. In our above examples we need information from Metric Results (the actual scores), but also Assessment Instances (to determine response rates). For both, we only will want record where the Assessment Instance is either Closed or Canceled.
Breakdowns:
Breakdowns are a key feature in Performance Analytics, which allow you to slice and dice the data in order to perform more detailed analysis. Typically Breakdowns are created based on Reference fields or Choice lists on the Indicator Source table. In addition, it is possible to dot walk to related tables and also script Breakdowns to handle complex cases. When thinking about ways to analyze NPS, consider how you already slice and dice data within Incident Management. Here are some proposed Breakdowns:
- By Incident Category
- By Contact type
- By Caller Department
- By Caller Manager
- By Assignment Group
- By Assignee
- By Priority
Fortunately most of these already exist as part of the Incident Management Performance Analytics Content Pack, so it will be a snap to utilize them for the NPS Survey. However there is an important issue that surfaces when we analyze our Breakdowns. The question you need to ask yourself is “Do I have access to the Breakdown fields from my Indicator Source tables?”
At first blush, of course you do, Metric Results reference the Assessment Instances, and Assessment Instances reference the Tasks for which they are generated. However certain fields, such as the Category, don’t exist on the Task table at all but are instead part of the Incident table. To Breakdown on these fields we’ll need to get a little creative.
Widgets:
These are the actual visualizations that you see on screen when you look at a Dashboard. Any Indicator and Breakdown combination can be used to make dozens of different visualization types. The most common used are Scores, Time Series and Breakdown Widgets. Once you have the other objects created, determining the visualization is more a matter of style and preference, so play with these until you get a view that you think provides the most value.
Jobs:
In order to collect Scores, Indicators must be associated with a Job. Jobs are run on a scheduled basis, typically once a day during non-work hours. Instead of adding our Indicators to an existing job, it will make support and debugging easier if we create a job just for our NPS scores. Generally, I tend to create new jobs from groups of created Indicators, rather than continuing to add to the existing out of the box (ootb) jobs.
Dashboard:
To bring it all together, we will need a dashboard visualizing the information. In order to organize data, each Dashboard may also have mutliple tabs. To make dashboards as efficient as possible and reduce loads times, try to limit each dashboard tab to around 6 widgets. For this example I will create 2 dashboard tabs. Tab 1 will be NPS Results and Tab 2 will be Survey response frequency.
Now that we have a rough idea of all the different components to be created, we can get started. The order I like to use when building PA objects is:
- Indicator Source
- Indicator
- Breakdown Source
- Breakdown
- Job
- Dashboard
- Widgets
If you have more detailed questions about the order of assembling the buliding blocks, I recommend you check out this fantastic, detailed post on the Community from Dale Lin:
Save yourself time:
The ServiceNow Share site has loads of great, free apps for you to try out and use. One I always recommend is “PA UI Actions”. If you are building PA content, this will save you hours of clicking back and forth between different records. Just one example – generating sample scores directly from an Indicator – has personally saved me many hours over the course of the last few years! Download this Share app, put it on your Developer instance, test it out, and if you have no issues, I recommend you add it to Production.
Indicator Sources:
There are two important things to consider when creating Indicator Sources: you want to segment the data, and “dates not states”. The first part is apparent if you look at the out of the box Indicator Sources. For example, you can see that instead of just one for Incidents, it is divided up into Incidents.New, Incidents.Open, Incidents.Resolved and Incidents.Closed. This ensures maximum efficiency of jobs and processing. You want the Indicator Sources to be general enough to be re-usable but at the same time focused on a key data segment. “Dates not States” refers to how the filter criteria are created. Your first thought might be that Incidents.Resolved should filter on State=Resolved but that is incorrect. By using a date/time field (Resolved) where possible instead of a State field, the Indicator Source is able to accurately collect historical collection.
Also note that the out of the box conditions often use “on Today” rather than something like “Resolved is not empty”. Scheduled jobs collected each day, so you don’t need to filter to find all records, just the ones that were processed through Resolved on that specific day. If you want to create an Indicator showing “Resolved in the last 30 days” you wouldn’t need to create anything. You could just use the existing “Number of resolved incidents” Indicator and apply the “30d running SUM” time series.
Lastly I almost always recommend using “Daily” as the Valid for frequency. Using Time Series on Widgets, you can make a Daily Indicator show by week, month or quarter, however you cannot make a weekly or monthly indicator show daily. The exception is when you want to show year to year, or any yearly Time Series (e.g. year to date), in which case Weekly or Monthly frequency Indicators must be used.
Here are the Indicator Sources:
Name: NPS.Survey.Result
Valid for frequency: Daily
Facts table: Metric Result [asmt_metric_result]
Conditions: (will need to dot walk to Instance where referenced)
Instance . State is Complete
Metric starts with NPS
Instance . Taken on is Today
Name: NPS.Survey.Instances
Valid for frequency: Daily
Facts table: Assessment Instance [asmt_assessment_instance]
State is one of Complete, Canceled
Updated is Today
Metric type is NPS Survey
* note any field value not specified should be left as default / not changed
** note the “Cancel Expired Assessments” Scheduled Job does not enter in the “Taken on” date on the Metric Result record, so in the filter above Updated is used. Assessments are locked to editing after being taken or canceled so the Updated field will work for our purposes. The only exception to that would be if your system has been customized to allow updates to Assessments after they have been completed.
Automated Indicators:
Automated Indicators are the foundation of Performance Analytics. They are what creates the values on the Widgets you see on Dashboards, and are combined in calculations to create Formula Indicators. With Automated Indicators you can create record counts, sum up values in records, average fields, or create scripts to gather information.
Name: Total number of Completed NPS Responses
Indicator Group: NPS
Direction: Maximize
Unit: #
Indicator Source: NPS.Survey.Instance
Aggregate: Count
Value when nil: 0
Filter:
State is Complete
Collect records: true
Collect breakdown matrix: true
Publish on Scorecards: true
Name: Total number of Cancelled NPS Responses
Indicator Group: NPS
Direction: Minimize
Unit: #
Indicator Source: NPS.Survey.Instance
Aggregate: Count
Value when nil: 0
Filter:
State is Cancelled
Collect records: true
Collect breakdown matrix: true
Publish on Scorecards: true
Name: Number of Promoters
Indicator Group: NPS
Direction: Maximize
Unit: #
Indicator Source: NPS.Survey.Result
Aggregate: Count
Value when nil: 0
Filter:
Metric is NPS
NPS value is Promoter
Collect records: true
Collect breakdown matrix: true
Publish on Scorecards: true
Name: Number of Detractors
Indicator Group: NPS
Direction: Minimize
Unit: #
Indicator Source: NPS.Survey.Result
Aggregate: Count
Value when nil: 0
Collect records: true
Collect breakdown matrix: true
Filter:
Metric is NPS
NPS value is Detractor
Publish on Scorecards: true
Name: Number of Passives
Indicator Group: NPS
Direction: M Minimize
Unit: #
Indicator Source: NPS.Survey.Result
Aggregate: Count
Value when nil: 0
Collect records: true
Collect breakdown matrix: true
Filter:
Metric is NPS
NPS value is Passive
Publish on Scorecards: true
Formula Indicators:
While Automated Indicators typically count or sum value in records, Formula Indicators add, substract, multiply or divide other indicators. In fact, the Formula box in the Indicators is actually a javascript box, and from a certain perspective you can do almost anything you want with them. For advanced users, check out this Community post by Adam Stout:
https://community.servicenow.com/community?id=community_blog&sys_id=1f0d2ea5dbd0dbc01dcaf3231f961947
For NPS we will be creating some simple formula Indicators, using the Automated Indicators created in the previous step.
Name: % Promoters
Indicator Group: NPS
Direction: Maximize
Unit: %
Precision: 2
Formula: ([[Number of Promoters]]/[[Total number of Completed NPS Responses]])*100
Publish on Scorecards: true
Apply time series to result: false
Name: % Detractors
Indicator Group: NPS
Direction: Minimize
Unit: %
Precision: 2
Formula: ([[Number of Detractors]]/[[Total number of Completed NPS Responses]])*100
Publish on Scorecards: true
Apply time series to result: false
Name: % Passives
Indicator Group: NPS
Direction: Maximize
Unit: %
Precision: 2
Formula: ([[Number of Passives]]/[[Total number of Completed NPS Responses]])*100
Publish on Scorecards: true
Apply time series to result: false
Name: NPS Score
Indicator Group: NPS
Direction: Maximize
Unit: #
Precision: 0
Key: true
Formula: [[% Promoters]]-[[% Detractors]]
Publish on Scorecards: true
Apply time series to result: false
Name: Response % for NPS surveys
Indicator Group: NPS
Direction: Maximize
Unit: %
Precision: 0
Formula: ([[Total number of Completed NPS Responses]]/([[Total number of Completed NPS Responses]]+[[Total number of Cancelled NPS Responses]]))*100
Publish on Scorecards: true
Apply time series to result: false
Breakdowns:
If you are familiar with Groupings in Reports, Breakdowns act in a similar way. Other BI tools might call them ‘Dimensions’. The big difference for PA is that Breakdowns are constructed ahead of time and added to Indicators so they can be processed when collection jobs are run. Breakdowns are usually added on Choice Lists or Reference fields and for NPS we’ll use common Breakdowns associated with the underlying Incident records. This will allow someone analyzing NPS values to slice and dice the data to generate insights related to Detractors and Promoters. Once we’ve added the Breakdowns, we’ll apply them to all our Indicators, then create and run a Job so we can see results. Each Breakdown needs a Breakdown Source and Breakdown Mapping, and each element is defined below. Note if you have activated the ITSM Content Packs, then Priority, Category, Assignment Group, Assigned to and Contact type already exist, saving us loads of time.
For the Caller Department we will use the ootb Breakdown source, so we only need to create the Breakdown.
Caller Department
Breakdown (Automated):
Name: Caller Department
Breakdown source: Departments
Breakdown Mapping:
Facts table: Metric Result [asmt_metric_result]
Field: Instance .. Assigned to .. Department
(* note that the .. denotes a dot walk)
Facts table: Assessment Instance [asmt_assessment_instance]
Field: Assigned to .. Department
Indicators: % Detractors
% Passives
% Promoters
NPS Score
Number of Detractors
Number of Passives
Number of Promoters
Response % for NPS Surveys
Total number of Cancelled NPS Responses
Total number of Completed NPS Responses
For Caller Manager, we could use the Users.Active Breakdown Source, but that would not be efficient. It is far better to create a new Breakdown Source that will allow us to zero in on Managers only, which is easily accomplished using Related List conditions.
Caller Manager
Breakdown Source:
Name: Users.Active.Manager
Facts table: User [sys_user]
Field: Sys ID
** I know it seems like sometimes it should not be Sys ID,
but the answer is always Sys ID.
Condition:
Active is true
RELATED LIST CONDITIONS
Great than or Equal to 1
User -> Manager
Breakdown (Automated):
Name: Caller Manager
Breakdown source: Users.Active.Manager
Breakdown Mapping:
Facts table: Metric Result [asmt_metric_result]
Field: Instance .. Assigned to .. Manager
Facts table: Assessment Instance [asmt_assessment_instance]
Field: Assigned to .. Manager
Indicators: % Detractors
% Passives
% Promoters
NPS Score
Number of Detractors
Number of Passives
Number of Promoters
Response % for NPS Surveys
Total number of Cancelled NPS Responses
Total number of Completed NPS Responses
The following Breakdowns exist out of the box and the fields we need exist on the Task table, so no assembly required! The the only thing necessary to use an out of the box Breakdown is to connect them to a table using a Breakdown Mapping, and then add them to the Indicators. Just to reiterate, in this step you are not creating new Breakdown Sources or Breakdowns, instead you will need to access the existing Automated Breakdowns (which exist because you’ve activated the ootb Content Packs, right?), then add Mapping records and Indicators to the Related Lists at the bottom of the records.
Breakdown: Priority (access the one with Breakdown Source = Incident.Priority)
Breakdown Mapping (need to add 2)
Facts table: Metric Result [asmt_metric_result]
Field: Instance .. Task .. Priority
Facts table: Assessment Instance [asmt_assessment_instance]
Field: Task .. Priority
Indicators: % Detractors
% Passives
% Promoters
NPS Score
Number of Detractors
Number of Passives
Number of Promoters
Response % for NPS Surveys
Total number of Cancelled NPS Responses
Total number of Completed NPS Responses
Breakdown: Assignment Group (access the one with Breakdown Source = Groups)
Breakdown Mapping (need to add 2)
Facts table: Metric Result [asmt_metric_result]
Field: Instance .. Task .. Assignment Group
Facts table: Assessment Instance [asmt_assessment_instance]
Field: Task .. Assignment Group
Indicators: % Detractors
% Passives
% Promoters
NPS Score
Number of Detractors
Number of Passives
Number of Promoters
Response % for NPS Surveys
Total number of Cancelled NPS Responses
Total number of Completed NPS Responses
Breakdown: Assigned To
*This should exist already out of the box, you just need to add a mapping
Breakdown Mapping (need to add 2)
Facts table: Metric Result [asmt_metric_result]
Field: Instance .. Task .. Assigned to
Facts table: Assessment Instance [asmt_assessment_instance]
Field: Task .. Assigned to
Indicators: % Detractors
% Passives
% Promoters
NPS Score
Number of Detractors
Number of Passives
Number of Promoters
Response % for NPS Surveys
Total number of Cancelled NPS Responses
Total number of Completed NPS Responses
Breakdown: Contact type
Breakdown Mapping (need to add 2)
Facts table: Metric Result [asmt_metric_result]
Field: Instance .. Task .. Contact type
Facts table: Assessment Instance [asmt_assessment_instance]
Field: Task .. Contact type
Indicators: % Detractors
% Passives
% Promoters
NPS Score
Number of Detractors
Number of Passives
Number of Promoters
Response % for NPS Surveys
Total number of Cancelled NPS Responses
Total number of Completed NPS Responses
I’m going to be vulnerable here, and admit that I am not good at scripting. If I have been able to achieve anything with scripting it is only because I have stood upon the shoulders of giants, AKA patient colleagues who help and support me. Another technique I have used successfully for many years is finding something already in the system similar to what I need, then copying it and making a few minor edits. Most Performance Analytics work does not require scripting however there are some exceptions including hierachical Breakdowns (e.g. with Parent \ Child type records), time duration calculations, and occasionally Breakdown Mappings.
Why do we need to use scripting? In the last step we demonstrated we can dot walk to the Task record, but it is not possible to them dot walk from the Task to the Incident. Although we have Incident number (trigger_id) the manual field selection in the Breakdown Mapping only lets us choose Task fields. That is great for Assignment Group and Priority, which exist on all records at the Task level, but we need to script to pull in Breakdown data for fields that only exist on the Incident record itself like Category. But have no fear, it’s a relatively easy script to create.
First we’ll need to create the scripts. Under the Performance Analytics menu, there is a specific option for ‘Scripts’. First we’ll create a new one to help us get the Category from the Task record. When you ‘script a breakdown’ you basically want the script to return a value that matches your Breakdown Source.
Script Name: get.incident.category.from.task
Facts table: Metric Result [asmt_metric_result]
Fields: Instance .. Trigger ID
Script: (you can literally cut/paste the below section into the script record)
//this defines the function
function incidentCategory(incident_id){
var gr = new GlideRecord("incident");
if (gr.get(incident_id))
return gr.getValue("category");
return '';
}
incidentCategory(current.instance.trigger_id);
Basically this searches the incident table using the ‘incident_id’ from the Task, and then returns the ‘category’. Easy peasy!
Next we need to create a second script, so that the same Breakdown may be applied to Assessment Instance based Indicators.
Script Name: get.incident.category.from.task.assessment
Facts table: Assessment Instance [asmt_assessment_instance]
Fields: Trigger ID
Script:
//this defines the function
function incidentCategory(incident_id){
var gr = new GlideRecord("incident");
if (gr.get(incident_id))
return gr.getValue("category");
return '';
}
incidentCategory(current.trigger_id);
Now we just need to use our script to create a Breakdown Mapping. Access the Breakdown as in the last step, add the scripted Breakdown Mapping
Breakdown: Incident Category
Breakdown Mapping
Facts table: Metric Result [asmt_metric_result]
Scripted: true
Script: get.incident.category.from.task
Facts table: Assessment Instance [asmt_assessment_instance]
Scripted: true
Script: get.incident.category.from.task.assessment
Indicators: % Detractors
% Passives
% Promoters
NPS Score
Number of Detractors
Number of Passives
Number of Promoters
Response % for NPS Surveys
Total number of Cancelled NPS Responses
Total number of Completed NPS Responses
Word Cloud – Text Analytics
In the London version, ServiceNow released a new visualization called a ‘Word Cloud’. This is a great way to represent text from fields such as Short Description or Survey Comments. In this section we’ll walk through setting up a Word Cloud for our NPS Surveys. If you are on a version prior to London, skip these steps.
Text Analytics are configured using already existing Indicators Sources. In order to create a Word Cloud for only NPS Comments and ensure maximum effiency of collection, we’ll configure a new Indicator Source that pulls only NPS Comments.
Indicator Source
Name: NPS.Survey.Result.Wordcloud
Valid for frequency: Daily
Facts table: Metric Result [asmt_metric_result]
Conditions:
Instance .. State is Complete
Metric is NPS Comment
Instance .. Taken on on Today
In your Navigator type in ‘text a’ and it will filter to the proper menu options. Then click on ‘Setup’ and then ‘New’ to create a new Text Analytic.
Indicator source: NPS.Survey.Result.Wordcloud
Fields to analyze: String value
Use system stop words: True (checked)
Next we need to create a simple Indicator that points to our new Indicator Source:
Automated Indicator
Name: NPS Wordcloud
Indicator Group: NPS
Direction: None
Indicator Source: NPS.Survey.Result.Wordcloud
Aggregate: Count
Value when nil: 0
Collect records: true
Filter:
None
Publish on Scorecards: true
Then add Breakdowns to the Indicator:
Assigned To
Category
Priority
NPS Value
Assignment Group
Caller Department
Caller Manager
Contact type
Now go back to Text Analytics > Setup and open the NPS Wordcloud setup record we added previously. At the bottom of the screen, access the Indicator tab and add your NPS Wordcloud Indicator.
And that’s it! We’ll come back to this Indicator and get to see it in action once we create the Dashboard.
Collection Jobs
If you jumped ahead and did a collection just to see what happens, don’t worry, I won’t judge! For everyone else following along, the next step is to create the Jobs and associate them with our Automated Indicators. Formula Indicators are created by Automated Indicators, so you don’t need to add Formula Indicators to Jobs.
Job: [PA NPS] Daily Collection Job
Operator: Relative
Relative start: 1
Relative start interval: days ago
Relative end 1
Relative end internal: days ago
Run as: System Administrator *
(* Always use an admin, or service account with admin to run jobs. The selected user must have access to the underlying data tables or the job won’t collect any scores)
Active: true (checked)
Run: Daily
Collect: Both scores and text index
Time: Hours 00 02 00
Indicators: NPS Wordcloud
Number of Detractors
Number of Passives
Number of Promoters
Total number of Cancelled NPS Responses
Total number of Completed NPS Responses
Job: [PA NPS] Historical Collection Job
Operator: Relative
Relative start: 5
Relative start interval: days ago
Relative end 1
Relative end internal: days ago
Run as: System Administrator *
(* Always use an admin, or service account with admin to run jobs. The selected user must have access to the underlying data tables or the job won’t collect any scores)
Active: false
Run: On Demand
Collect: Both scores and text index
Indicators: NPS Wordcloud
Number of Detractors
Number of Passives
Number of Promoters
Total number of Cancelled NPS Responses
Total number of Completed NPS Responses
You might be thinking at this point “A 5 day historical collections seems pretty useless, I want 6 months of data!” My recommendation is always to collect only a few days initially, then verify the results before you do a full historical collection. If you only configured the NPS survey from Step 1 recently, you won’t have much data regardless. Go ahead, run a quick collection, then look at each Indicator to ensure you have data prior to running a longer collection. If your Indicators or Breakdowns are missing data, recheck the previous steps to ensure nothing was missed. If you are still having an issue please post it as a comment to this post and I’ll do my best to help.
If you like this content and would like to see more, please mark this is Helpful or Comment.
Thanks,
Chris Steinke
p.s. Part 3 is going to be the Dashboard build out! Stay tuned!
12 Comments
