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.

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:

=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,)))))

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

Calculating NPS
Break down the responses by Detractors, Passives, and Promoters.

Score breakdowns: Copy the column to another sheet and sort the numbers so they are ascending. This helps you break down the groups.
 * 0 – 6: Detractors
 * 7 – 8: Passives
 * 9-10: Promoters

Add up the total responses from each group.

To get the percentage, take the group total and divide it by the total number of survey responses.

Now, subtract the percentage total of Detractors from the percentage total of Promoters—this is your NPS score.

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

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

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.