Make a working copy of the survey

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

Calculate NPS

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

Score breakdowns:

0 - 6: Detractors
7 – 8: Passives
9 - 10: Promoters

Copy the column to another sheet (tab), OR pull each value in with a formula like: ='Form Responses 1'!ab4

Count the number of Detractors and Promotors.

This can be done with formulas like:

=countif(A:A, "<7")
=countifs(A:A, ">8", A:A, "<11")

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.

Identify themes from Open Ended Answers

Themes can be used to suggest areas to formulate open-ended questions in future CSATs
Make a new tab in the spreadsheet for Themes.
Copy the questions, which are column headings in the Form Responses tab, to the Themes tab as row headings.
For each question, read all of the answers and identify one to three common ideas in the answers.
Paraphrase the ideas as themes in the themes tab. Include some quantitative indication of how common the themes are, such as "more than half of respondents felt" or "One respondant said".
Identify any themes across multiple questions.

Pull out Representative Quotations

Make a new tab in the spreadsheet for quotations
For each of the key themes, find one to two quotations that illustrate the key theme and add them to the Quotations tab.
Trim quotations to be as short as possible without changing the spirit or meaning of the original.
Indicate which theme each quotation illustrates in the second column.

Wrap it up!

Let the rest of TPG know that the results are in and calculated by sending an email to tpg@wikimedia.org
Include high-level results (NPS, other interesting results) and links to relevant materials