Building reports over in Explore to measure your survey data starts with creating a new query, and then within that query, we need to create a few calculated metrics that you can use in another query that will ultimately measure your NPS over time.
Query 1: NPS Breakdown by month/year (line chart)
Query 2: NPS Calculated Score by month/year (bar chart)
*requires metrics that you build in query 1.
So in the first query, we'll be creating the following 3 calculated metrics that we'll need before we can create the second query that actually measures the NPS score:
1. #ALL tickets with an NPS rating
2. #NPS Detractors
3. #NPS Promotors
The reason we need these metrics is that the formula for NPS is:
Source: Zendesk: Analyzing-your-Net-Promoter-Score-survey-results
| Note: It has come to our attention that depending on how much data you're looking to deal with, Zendesk Explore may not have the capacity to be able to report on NPS based on the actual ticket event that the NPS was recorded on. This is due to Zendesk Explore limiting SQL results to 50,000. While this is a disappointing limitation of Explore, we have created a video outlining alternate ways to build NPS reports at the bottom of this article. |
Query 1: NPS Breakdown by month/year
To create a new query over in Explore click on the queries icon on the left and then click on "New Query" on the right like this:
Then you'll need to select a dataset to create the query from. Since our reports will need to be measuring scores from the actual point at which the ticket was updated with the latest score, you'll need to select "Support: Ticket updates" like this:
Now we need to build our first calculated metric. To do this click on the calculator icon on the right of the screen and select 'Standard calculated metrics' like this:
Now give your metric a name like "Survey App #ALL NPS tickets" and in the formula copy and paste this code:
IF ([Changes - Field name] = "Organization score (0-10)")
THEN
IF ([Changes - New value] < 11)
THEN [Ticket ID]
ENDIF
ENDIF
Your metric should now look like this:
Finally, at the bottom click on "Save".
Now, before adding it to the query, you'll need to edit this metric so that whenever you add it, by default, the aggregator is always set to D_COUNT rather than the SUM. The reason why we set it to D_COUNT is that we only want to measure the DIFFERENT unique values. ie. We don't want to measure an NPS score twice on the same ticket.
To set the default aggregation, click "Add" from the metrics section, then under 'Calculated metrics' click to edit "Survey App #ALL NPS tickets" like this:
To set it so this is set by default when using it on any query, click to edit it, then over under options click 'edit aggregators' like this:
Now select "D_COUNT" and click "Save" like this:
Great! This now means that whenever you use this metric in future it will default to the correct aggregator. To add the metric to the query now click on "Add", "Calculated metrics" and then on the new metric like this:
Ok sweet! You've now finished your first calculated metric and should be seeing a number in your query once you let it finish calculating (it can take a while).
At this point, you should now save your query by giving the query the name "NPS Breakdown" and click 'save' at the top right like this so that you don't lose your work.
In order to create the next two metrics, we can do this faster by duplicating the one we just created. To duplicate a calculated metric, click to edit it, then under 'options', click on 'duplicate' like this:
...and then click 'OK' for it to be connected to "Ticket updates" like this:
Ok now if you click to add a new metric to your query you'll see the duplicated metric which you can then click to edit (pencil icon) like this:
Now rename the metric to "Survey App: #NPS Detractors" and change the formula to look for only tickets with a score less than 7 like this:
If you'd prefer to copy and paste the formula, here it is:
IF ([Changes - Field name] = "Organization score (0-10)")
THEN
IF ([Changes - New value] < 7)
THEN [Ticket ID]
ENDIF
ENDIF
Now you can add this metric to your query as well. Your query should now look something like this:
Finally, you're going to need to duplicate the metric one last time to measure the NPS promotors (ie. ratings above 8). Call it "Survey App: #NPS Promotors" and set it to look for scores > 8 like this.
Once again, here's the code if you want to copy and paste instead:
IF ([Changes - Field name] = "Organization score (0-10)")
THEN
IF ([Changes - New value] > 8)
THEN [Ticket ID]
ENDIF
ENDIF
You can then add the final calculated metric to your query like this:
However, you may note that the report doesn't look like it should. This is because we now need to slice the data based on the time the NPS scores were added to the ticket. To do this in columns click on 'add', and under 'Time - Ticket update' select both 'Update - Year' and 'Update - Month' like this:
Now your report will be broken down by months and years you've been collecting your scores from in a line chart like this (once again, note this can take a while to calculate due to the slowness of Explore so be patient).
It's important to note here the order in which you add the columns. They should be ordered 'Update - Year' above 'Update - Month'. If you have it around the other way, then all the same months will be clumped together (eg. January 2017, 18 & 19 will appear next to each other etc).
You should be looking for a chart that looks something like this:
You can then filter your results to only show you the last year. To do this click on "Update - Year", then on the years you want to see, like this:
At last, you have a report showing you your breakdown of NPS Promotors, Detractors and ALL ratings over the last X months (depending on your filter) like this:
IMPORTANT: MAKE SURE YOU SAVE YOUR QUERY! If you don't save your query and move away from it, then all of your custom calculated metrics will be lost.
Query 2: NPS Score by month/year
Note: you can not build this query without first building the calculated metrics in query 1.
Ok now that you've completed the first query above. The calculated metrics that you created as part of it can then be used in this next query to generate an NPS score report.
To create your next query at the top left click in the queries icon and at the top right click on 'New Query' like this:
Once again we'll need to make sure this query is using the right dataset, so select "Support: Ticket updates" like this:
Now on the right click to create a new standard calculated metric like this:
Call it "Survey App: NPS Score".
The metric you will be creating will look something like this:
However, since the metric requires you to reference the calculated metrics you've already created in Query 1 above, these need to be referenced specifically by searching for them in the 'Field selector' at the bottom. The video below shows precisely how to do this to create the required metric and resultant query:
Note: Depending on the amount of data you need to report on, building the report based on the above method may result in Zendesk Explore giving you an error message saying that results are limited to 50,000. While this is a disappointing limitation of Explore, we have created a video outlining alternate ways to build NPS reports below:
Comments
9 comments