Turn Survey Results into Themes and Recommendations

Tier: Core · Hands-on time: ~35 minutes · Tools: ChatGPT, Google Sheets, Claude

Everyone runs surveys; almost nobody analyses the free-text box. The multiple-choice charts get made, and the 200 comments — where the actual reasons live — get skimmed for one juicy quote. This recipe makes the model do the tedious clustering while you enforce the one rule that keeps it honest: every theme carries a count, and every count is checkable.

When to use — and when not to

• Employee or customer surveys with open-ended answersengagement surveys, NPS "why did you give that score?", post-event feedback, exit-interview forms.
• Any pile of short free-text recordssupport ticket subjects, app-store reviews, suggestion-box entries. The recipe does not care that it was not technically a survey.
• Re-analysis of an old surveywhen a decision resurfaces and someone asks "what did people actually say last year?"

And when not to:

• Fewer than ~25 responses. Read them yourself; clustering 15 comments through a model adds process without adding insight.
• Responses containing sensitive personal disclosuresharassment reports, health information, whistleblowing. Those need a human reader with a duty of care, not a clustering pass.
• You have already decided the answer. If the write-up exists to justify a decision already made, no analysis method will save it — and the model will happily oblige your framing. That is the failure this recipe is built to prevent.

The recipe

1. 1.

   Prepare the responses as a numbered list (5 minutes). Export the free-text column from Google Sheets. Give every response a number (=ROW()-1 in a helper column, then copy both columns out). Numbers are the anti-cherry-picking device: they let you trace any quote back to a real row. Strip names, emails, and anything identifying before it leaves the sheet.

2. 2.

   Run the clustering pass with counting rules. Paste the numbered responses with this prompt:

   Copy

   You are a research analyst. Below are numbered open-ended survey
   responses to the question: "[PASTE THE EXACT SURVEY QUESTION]".
   
   Cluster them into 4-7 themes. For each theme give:
   1. A short, neutral name (not a slogan, not a recommendation).
   2. The COUNT of responses assigned to it, and the response numbers.
   3. Two representative quotes, copied VERBATIM, with their numbers.
   4. One sentence on what the theme means in plain terms.
   
   Rules:
   - Every response is assigned to exactly one primary theme. Put
     genuinely unclassifiable ones in a final "Other" bucket and list
     their numbers — do not hide them.
   - Counts must sum to the total number of responses. State the total.
   - Quotes must be exact text from the list. Never paraphrase a quote.
   - Order themes by count, largest first.
   - Do NOT give recommendations yet. Analysis only.
   
   RESPONSES:
   [PASTE NUMBERED RESPONSES]
   

   A good output looks like this:

   Copy

   Total responses: 187
   
   Theme 1 — Meeting load (52 responses: #3, #9, #14, ...)
   "I lose whole days to meetings that could be a doc" (#14)
   "back-to-backs mean my real work starts at 4pm" (#77)
   Meaning: the single largest complaint is time lost to meetings,
   not the meetings' content.
   
   Theme 2 — Unclear priorities (41 responses: #2, #8, ...)
   ...
   Other (6 responses: #19, #44, #101, #118, #150, #171)
   

3. 3.

   Audit the counts and quotes (8 minutes). Spot-check five quotes against your sheet by number — verbatim or the analysis is untrustworthy. Check the counts sum to your total. Skim the "Other" bucket yourself; models sometimes park inconvenient responses there. If a theme feels like two themes wearing a coat, say so: "Split theme 2 into 'unclear priorities' and 'shifting priorities' and redistribute the numbers."

4. 4.

   Now — and only now — ask for recommendations. Same conversation:

   Copy

   Based ONLY on these themes and counts, draft 3-5 recommendations.
   For each: the theme(s) it addresses, the count of people affected,
   the specific action, and who would own it. Flag any recommendation
   that addresses a small theme but would be cheap to do anyway.
   Do not recommend anything no theme supports.
   

   Keeping analysis and recommendations in separate steps is the discipline: it stops the model (and you) from bending the clustering to fit a preferred fix.

5. 5.

   Write the one-page summary yourself. Themes table (name, count, one quote each), then recommendations, then a link to the full response sheet. The table format the renderer gives you is the deliverable; the model's prose is scaffolding.

Make it yours

• Claude for large surveys: 500+ responses fit comfortably in one paste, and Claude is strong at keeping verbatim quotes verbatim. For 2,000+, split into batches, cluster each, then paste the theme lists together and ask it to merge themes and re-sum counts.
• ChatGPT with the spreadsheet attached: upload the export directly and have it number rows itself — then your spot-check happens against the uploaded file. Ask it to output the theme-assignment as a two-column table you can paste back into Sheets next to the raw responses.
• Google Sheets for the follow-through: paste theme assignments back as a column, then a pivot table gives you theme-by-department or theme-by-score cuts the model never saw — often where the real story is ("meeting load is 60% of complaints in engineering").

Quality check

• Counts sum to the total and the total matches your sheet's row count.
• Five random quotes verified verbatim against their claimed row numbers.
• Theme names are neutral descriptions, not editorials — "Meeting load", not "Meeting culture is broken".
• The "Other" bucket was read by a human and genuinely doesn't cluster.
• Every recommendation traces to a named theme and countnothing smuggled in from your own agenda.