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.

Calculating NPS
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: ='Form Responses 1'!ab4
 * 0 – 6: Detractors
 * 7 – 8: Passives
 * 9-10: Promoters

Add up the total responses from each group. This can be done with formulas like:
 * =countif(A:A, "<7")
 * =countifs(A:A, ">6", A:A, "<9")
 * =countifs(A:A, ">8", A:A, "<11")

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 decimal or percentage. I.e. 0.2 or 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.