REMEMBER TO SAVE YOUR QUERY WHEN EDITING!
1. When you're inside Explore, you can click 'Queries' on the left, and then choose 'New Query'
2. Select dataset you want to create the query from, click 'New Query'.
3. Next we need to build the first calculated metric. Click on the calculator icon on the right side of the screen and select 'Standard calculated metric'.
4. Give your metric a name. For Example "Surveypal App #ALL NPS tickets" and in the formula copy and paste this code:
IF ([Changes - Field name] = "NPS score")
IF ([Changes - New value] < 11)
THEN [Ticket ID]
“NPS score” is the name of the field where your NPS score located. Click 'Save'.
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.
5. Click on the 'Add' under Metrics, search for the metric we created by name.
6. Click on the pen icon on our metric, and then click 'Options' on the top right corner and choose 'Edit aggregators'.
7. In the Aggregators-menu make D_COUNT the default choice, click 'Save'.
8. Next we need to add the Metric, click 'Add', search for your metric and choose it, click 'Apply'. Make sure the Metric is D_COUNT and not SUM, you might need to delete and add it if it's wrong.
9. Give your query a suitable name and save it.
In order to create the next two metrics, we can do this faster by duplicating the one we just created.
10. To duplicate a calculated metric, click to edit it, then under 'Options', click on 'Duplicate' like this:
11. Choose where you want to save the copy and click 'Ok'.
12. Click 'Add' under Metrics, search for your metric and click on the pen icon on the copy.
13. Change the name of the copy to include Detractors, and the code from 11 to 7. Click 'Save'.
14. Add both metrics to report.
Finally, you're going to need to duplicate the metric one last time to measure the NPS promoters.
15. Create a copy of the Detractors-metric.
16. Edit the new copy. Change name to Promoters and change "<7" in the code to ">8". Click 'Save' on the bottom of the metric.
17. After adding all three metrics, you should see something like this:
Next you have to add Columns to add time as slicer.
18. Choose 'Add' under Columns. Find 'Time - Ticket update' and choose 'Update - Year' and 'Update - Month' active. NOTE: You need to choose the Year first, it is important that 'Year' is above 'Month' in Columns.
Now your report will be broken down by months and years you've been collecting your scores from in a line chart like this.
19. You can modify time by clicking 'Year' and then defining what year you want to see results for.
Now we can create the query for calculating NPS score.
20. Create a new Standard calculation
21. Give this New metric name for example 'Surveypal App: NPS Calculation'. Write/copy the following code to the formula field. Click 'Save' at the bottom.
((D_COUNT(Surveypal App # All NPS Tickets Promotors) / D_COUNT(Surveypal App # All NPS Tickets)) *100)-
((D_COUNT(Surveypal App # All NPS Tickets Detractors) / D_COUNT(Surveypal App # All NPS Tickets))*100)
This will calculate NPS score for you (Promoters% - Detractror %)
22. Edit the 'NPS Calculation' metric and change the Aggregators to D_COUNT as default.
23. When you have 'NPS Calculation' under Metrics, and 'Year' + 'Month' under Columns, you're ready!