Team Practices Group/CSAT/Process

=Calculating scores for the CSAT=

Prep
Once the survey is closed, copy the responses doc. This will keep the original safe for reference and allows for formatting and movement of data as needed.

The example spreadsheet below shows all the calculations you will need to make: https://docs.google.com/spreadsheets/d/1HOsuRTHMx-bDHKk7Oy8PkeuGEh-kHBp6k0guuU0d0q0/edit#gid=1630881912

Step1
Looking at one section (let’s say delivery), calculate the average score from each question from a single person.

Number range:
 * Strongly Disagree = 1
 * Strongly Agree = 5
 * Not Applicable should not be counted.

Instructions: =if(C2="Strongly Disagree",1,IF(C2="Disagree",2,IF(C2="Neither Disagree nor Agree",3,if(C2="Agree",4,if(C2="Strongly Agree",5,)))))
 * 1) Insert blank columns to the right of columns containing the Likert responses.
 * 2) In google spreadsheets, convert strings to numbers by copying a nested IF statement  into cells of blank columns to the right of Likert responses:
 * 1) NOTE: Copy the formula above, and paste it into one cell, adjusting the column (C) if necessary. Then copy that cell and paste it into the other cells. You can select a range of cells in a column, and a single paste can populate all of them.
 * 2) With each response converted to a number, average all responses for delivery questions from person 4. Repeat this with all respondents. Each person will end up with a single, average score for delivery.

Step 2
Get the average from the numbers acquired in step 1. This is the cumulative Delivery Score.

Then take an average of the cumulative Deliver Score column, being careful to exclude cells with the non-numeric "Not Applicable"

Repeat the whole process for sustainability.

Break down the responses by Detractors, Passives, and Promoters.
Score breakdowns: Copy the column to another sheet (tab), OR pull each value in with a formula like:
 * 0 - 6: Detractors
 * 7 – 8: Passives
 * 9 - 10: Promoters

Count the number of Detractors and Promotors.
This can be done with formulas like:

Convert to percentages
Divide each group count by the total number of survey responses (including Detractors, Passives, and Promoters).

Calculate Net Promotion
Subtract the Detractor percentage from the Promoter Percentage; this is the NPS score.

Note: the final number is not shown as a decimal or percentage. I.e. 0.2 or 20% should be displayed as 20.

Reference: https://www.surveymonkey.com/mp/net-promoter-score-calculation/

Example
Suppose there are five responses: 3, 7, 8, 9, 10. The Detractor count is 1; the Detractor percentage is 20%. The Promoter count is 2; the Promoter percentage is 40%. The NPS is 20.

Processing Open Ended Questions

 * 1) Copy the responses per question into individual tabs
 * 2) Read through responses to identify themes (helps to pair to do this)
 * 3) Create columns for the predominant themes
 * 4) Re-read responses and put a 1 in column of respective theme it ties to
 * 5) Sum up each theme column

Depending on the slide deck format, there may not be a lot of space for quotes.