Marketing · Advanced
Code open-ended survey responses
640 people answered "What's the main reason for your score?" Nobody is going to read all 640, and a word cloud isn't analysis. Coding them by hand takes days. Coding them with AI takes minutes, and produces numbers that look precise whether or not they are. This guide gets you categories with counts you can stand behind: a codebook you approve, coding you can audit by response ID, and a random spot-check you do yourself.
What AI is for here, and what it isn't
Coding open-ended responses means reading each answer and assigning it one or more categories ("codes") from a defined list, the codebook, so you can count how often each reason comes up. It's how free text becomes a chart.
AI is genuinely useful at both halves: proposing a codebook from a sample of responses, and then applying it consistently to hundreds of answers. It reads the 600th response with the same attention as the first.
It's also fallible in ways that are easy to miss in a large file. It can skip rows, merge two responses, invent a category that isn't in the codebook, or code confidently where a person would hesitate. And its arithmetic isn't reliable. So the method separates the jobs: AI proposes and applies, the spreadsheet counts, and you approve the codebook and check a random sample before any number leaves your desk.
Prepare the export
Start from a CSV with at least two columns: a unique response ID and the free-text answer. Keep any segment columns you'll want later (score, plan, region) in the spreadsheet, not in the AI prompt; you'll join them back by ID.
Then clean it:
- Remove blank and nonsense answers ("n/a", "."), and note how many you removed.
- Mask personal data: replace names and emails with [NAME] and [EMAIL].
- Write down the exact question, as respondents saw it. "Main reason for your score" invites one reason; "What could we improve?" invites lists. The codebook should match the question.
Build the codebook from a sample
Take a random sample of around 100 responses (Step 4 shows how) and ask for a draft codebook.
Propose a codebook for coding responses to this survey question.
QUESTION (as asked): "[exact wording]"
SAMPLE: [n] responses, each with its ID: [paste ID, text]
FOR EACH CODE:
- CODE NAME: short and specific
- DEFINITION: one sentence
- INCLUDE: what belongs here
- EXCLUDE: what looks similar but belongs elsewhere (name that code)
- EXAMPLES: two response IDs from the sample
RULES: 8–12 codes plus "Other" and "Unclear". Codes must be about
what respondents said, not your interpretation of why. A response may
need more than one code; say how often that happens in the sample.
Now edit it yourself. Merge codes that you'd never act on separately, split ones that hide two different problems, and sharpen every exclude line where two codes sit close together. The codebook is the analysis; approve it as carefully as a questionnaire.
Apply it to every response, by ID
Code in batches of 50 to 100, so nothing gets skipped silently.
Code each response below using ONLY the codebook. Return one line per
response, in the same order, as:
ID | code(s), separated by ; | confidence HIGH/LOW | note if LOW
CODEBOOK: [paste approved codebook]
RULES:
- Every ID in the input appears exactly once in the output.
- Use only code names from the codebook. If nothing fits, use Other;
if the meaning is unclear, use Unclear.
- Mark LOW confidence where a person might reasonably choose a
different code, and say why in a few words.
- Do not count or summarize.
RESPONSES:
[paste ID, text for one batch]
Paste each batch's output into your spreadsheet next to the original responses, matched by ID. Then check the mechanics before the meaning:
- Every ID came back exactly once. Count the IDs in and out. Missing or duplicated rows are the most common failure in large batches. Rerun any batch that doesn't match.
- Every code is in the codebook. Filter for anything else.
- No response has the same code twice. A code repeated in both columns would be counted twice and inflate that code's total.
- Count in the spreadsheet, with formulas, not with the AI. Counts by code, and by segment once you join the segment columns back.
Spot-check a random sample yourself
This step is what makes the numbers defensible.
- Draw a random sample of coded responses: at least 50, or about 10% of a large file. In the spreadsheet, add a column with
=RAND(), copy it and paste as values (so it stops changing), sort by it, and take the top rows. Don't hand-pick. - Code the sample yourself, without looking at the AI's codes. Hide that column first.
- Compare, at two levels. Because a response can carry more than one code, "agreement" can mean two different things, and you should report both:
- Response-level agreement: the share of responses where your full set of codes exactly matches the AI's. This is the strict measure.
- Code-level agreement: for each code, the share of responses where you and the AI agree on whether that code applies (both yes, or both no). This shows which codes are reliable and which aren't. A response where you chose two codes and the AI chose one of them counts as a miss at response level, but as agreement on one code and a disagreement on the other.
- Set your threshold before you look. There's no universal standard. The right level depends on how the numbers will be used, so choose and write down a threshold (this chapter's example uses 80% response-level agreement) before you compare. If you're below it, or if one code's agreement is much lower than the rest, sharpen those definitions, recode those codes, and check a fresh sample.
Chance-corrected statistics such as Cohen's kappa are stricter than percent agreement, but they assume a particular setup: two coders each assigning one category per item. They don't apply directly to a scheme with several codes per response. The usual workaround is to treat each code as a separate yes/no decision and calculate kappa per code (the companion workbook does this). If you're publishing results or making a high-stakes decision, ask someone with survey-methods experience which reliability measure fits your design.
Read every LOW-confidence row and every Other and Unclear row as well. If Other is more than about one response in ten, the codebook is missing something.
Worked example: the NPS follow-up question
Fictional example. A customer survey gets 640 usable answers to "What's the main reason for your score?" The analyst masks 23 email addresses and names, samples 100 responses, and edits the proposed codebook down to nine codes plus Other and Unclear.
Coding runs in eight batches of 80. Batch 5 comes back with 77 lines. Three IDs are missing, so the batch is rerun, and the second run returns all 80.
The spot-check: 60 random responses, coded blind by the analyst. At response level, 49 of 60 have exactly matching codes, 81.7%, just above the 80% threshold the team chose beforehand. But the code-level view shows where the trouble is: most codes agree on 57 or more of the 60 responses, while "Ease of use" and "Setup and onboarding" each agree on only 53. Seven of the eleven mismatched responses involve those two codes. The AI codes "It took us a while to figure out" as Ease of use; the analyst reads it as Setup.
Clearing the threshold isn't the point if the disagreements concentrate in one place. The analyst sharpens the definitions: Setup covers getting started, roughly the first month; Ease of use covers day-to-day work after that. Those two codes are recoded across all 640. A fresh 60-response spot-check matches exactly on 55, 91.7%, and neither code is now an outlier at code level.
The spreadsheet then does the counting. Setup and onboarding is the top reason among detractors (scores 0–6), a finding the AI never stated. It came from joining the codes back to the scores by ID.
Record the method
Survey: [name], question: "[exact wording]", fielded [dates]
Responses: [total] received, [n] removed (blank/nonsense), [n] coded
Personal data masked: [n] items
CODEBOOK: v[number], approved by [name], [date] (attached)
CODING
Tool: [name] Batches: [n] of [size] ID check: all IDs returned once ✓
Codes outside codebook: [n] fixed
SPOT-CHECK
Sample: [n] random (RAND, values pasted) Threshold set beforehand: [ %]
Response-level (exact match): [n]/[n] = [ %]
Code-level: lowest codes [code: %], [code: %] Kappa per code (if used): [ ]
Disagreements clustered in: [codes]
Action: [definitions changed; recoded; second check [ %]]
COUNTS: from spreadsheet formulas, file [name], [date]
Reported by: [name]
What stays with you: the final codebook, and the spot-check on a random sample.