Building NPS Reports in EXPLORE with Survey App data

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:

nps_score_graphic2.png
Source: Zendesk: Analyzing-your-Net-Promoter-Score-survey-results

 


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:

Zendesk_Explore.png

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:

Zendesk_Explore.png

 

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:

Zendesk_Explore.png

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: 

Zendesk_Explore.png

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:

Zendesk_Explore.png

 

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:

Zendesk_Explore.png

Now select "D_COUNT" and click "Save" like this: 

Banners_and_Alerts_and_Zendesk_Explore.png

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:

Zendesk_Explore.png

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).

Zendesk_Explore.png

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.

Zendesk_Explore.png

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:

Zendesk_Explore.png

...and then click 'OK' for it to be connected to "Ticket updates" like this:

Zendesk_Explore.png

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:

Zendesk_Explore.png

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:

Zendesk_Explore.png

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:

Zendesk_Explore.png

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.

Zendesk_Explore.png

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:

Zendesk_Explore.png

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:

Zendesk_Explore.png

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:

Zendesk_Explore.png

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:

Zendesk_Explore.png

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:

Zendesk_Explore.png

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.

Zendesk_Explore.png

 

 


 

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:

Zendesk_Explore.png

Once again we'll need to make sure this query is using the right dataset, so select "Support: Ticket updates" like this:

Zendesk_Explore.png

Now on the right click to create a new standard calculated metric like this:

Zendesk_Explore.png

Call it "Survey App: NPS Score".

The metric you will be creating will look something like this:

Zendesk_Explore.png

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: 

 

Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.
Powered by Zendesk