Code 1,000 Open-Ended Survey Answers in an Afternoon with Gemini and Python

Every feedback form ends with the same question: “Any suggestions for us?” The multiple-choice questions turn into a neat chart in Google Forms. The open-ended column turns into 600 rows of text that nobody reads properly. Somebody scrolls for ten minutes, remembers three loud complaints, and that becomes “what customers said”.

That’s a waste, because the open-ended answers are usually where the useful stuff is: the delivery problem nobody asked about, the product people keep requesting, the staff member who gets named in 40 compliments.

Researchers have a name for turning those answers into numbers: coding. You build a short list of themes (a codebook), tag every answer with the themes it mentions, then count. Done by hand, it’s slow. At a fast 15 seconds per answer, 1,000 answers is more than four hours of reading and tagging, before you count anything.

In this guide you’ll build one Python script, survey.py, that:

• reads a Google Forms (or any) CSV export and strips emails and phone numbers before anything leaves your computer
• asks Gemini to propose a codebook from a sample of answers, which you then edit
• tags every answer with codes from your codebook only, plus a sentiment, and re-asks for anything the model skipped or got wrong
• builds a summary table: how many people mentioned each theme, with three real quotes pulled from the data (never written by the AI)
• gives you a blind check: you code 50 random answers yourself and the script tells you how often you and the model agree

AI cost for 1,000 short answers: about $0.05–0.10. Build time: about 45 minutes. It’s a time-saver for anyone who runs surveys, and a small service you can sell to people who don’t want to do this themselves.

What you need

Pricing as of October 2026 (check ai.google.dev/pricing before you rely on it; Google changes models and prices often):

• Gemini 3.5 Flash-Lite (paid tier): $0.30 per 1M input tokens, $2.50 per 1M output tokens. Thinking tokens are billed as output.
• Free tier: available for 3.5 Flash-Lite, with rate limits. Fine for testing on your own data. Read the privacy note in the Reality check before you put anyone else’s survey through it.

Why not Gemini 2.5 Flash? It costs the same per token and isn’t deprecated, but Google is now limiting access to the 2.5 models to accounts that have actively used them before, and points new projects to 3.5 Flash-Lite or 3.8 Flash. With a new API key you may not be able to call 2.5 Flash at all. The script defaults to gemini-3.5-flash-lite and still works with gemini-2.5-flash if your account has access (set SURVEY_MODEL). Gemini 3.8 Flash is stronger but costs more: $0.75 input / $3.75 output per 1M tokens through December 31, 2026, doubling from January 1, 2027. For a tagging job like this, Flash-Lite is enough.

Is there a no-code option?

For 100 answers, yes: paste them into ChatGPT, Gemini or Claude and ask for themes. That’s fine for a quick look. It breaks down at a few hundred answers: chat apps tend to summarise instead of tagging every row, the counts can’t be checked, and if you ask twice you get two different sets of themes. The script fixes the codebook first, tags row by row, validates every result, and gives you a file you can audit.

How the cost works out

A typical “suggestions” answer is 10–40 words, roughly 15–60 tokens.

Codebook pass: 250 sampled answers ≈ 10,000 input tokens. The output plus thinking (the script asks for high thinking on this one request) is usually a few thousand tokens, and can reach around 8,000 if the model thinks for a long time. Cost: about $0.01–0.03.

Coding pass: the script sends 40 answers per request. Each request carries the codebook (~600 tokens), instructions (~200) and 40 answers (~1,500), so about 2,300 input tokens. Each answer comes back as roughly 25 tokens of JSON, so about 1,000 output tokens per batch. The coding pass uses minimal thinking, which keeps thinking tokens low but not always at zero. For 1,000 answers that’s 25 requests:

• Input: 25 × 2,300 = 57,500 tokens × $0.30/1M = $0.017
• Output: 25 × 1,000 = 25,000 tokens × $2.50/1M = $0.063

Total: under $0.10 for 1,000 answers, including a retry or two. Long answers (people who write paragraphs) can double that, which is still pocket change. The script prints the real token count and cost at the end of every run, so you don’t have to trust these numbers.

Step 1: Set up the project

mkdir survey && cd survey
python3 -m venv .venv && source .venv/bin/activate   # Windows: .venv\Scripts\activate
pip install -U google-genai pydantic pandas

export GEMINI_API_KEY="paste-your-key-here"           # PowerShell: $env:GEMINI_API_KEY="..."

Put your export in the folder as responses.csv. The download sometimes arrives as a .zip. If it does, unzip it first. If you’ve cleaned up responses in the linked Google Sheet, export from the Sheet instead (File → Download → CSV), because the Forms export won’t include your edits. Google Forms uses the full question text as the column header, so note exactly what it says. You’ll pass it in quotes.

Step 2: The script

Save this as survey.py:

import json, os, re, sys, time
from collections import Counter
from pathlib import Path

import pandas as pd
from google import genai
from google.genai import types
from pydantic import BaseModel

MODEL = os.environ.get("SURVEY_MODEL", "gemini-3.5-flash-lite")
PRICE_IN, PRICE_OUT = 0.30, 2.50   # USD per 1M tokens, gemini-3.5-flash-lite paid tier
BATCH = 40
client = None
usage = Counter()

class Code(BaseModel):
    code: str              # short snake_case id
    label: str
    definition: str        # one sentence a human coder could apply
    example_ids: list[int]

class Codebook(BaseModel):
    codes: list[Code]

class Coded(BaseModel):
    id: int
    codes: list[str]
    sentiment: str         # positive / negative / mixed / neutral

class CodedBatch(BaseModel):
    items: list[Coded]

EMAIL = re.compile(r"[\w.+-]+@[\w-]+\.[\w.]+")
PHONE = re.compile(r"(?:\+?62|0)8[\d\s-]{7,13}\d")

def clean(text: str) -> str:
    return PHONE.sub("[phone]", EMAIL.sub("[email]", text)).strip()

def load(path: str, column: str) -> pd.DataFrame:
    df = pd.read_csv(path, dtype=str, encoding="utf-8-sig")
    if column not in df.columns:
        sys.exit("Column not found. Columns are:\n" + "\n".join(df.columns))
    df["id"] = df.index + 2                      # = row number in the spreadsheet
    df["text"] = df[column].fillna("").map(clean)
    return df[df.text.str.len() > 2][["id", "text"]].reset_index(drop=True)

def ask(prompt: str, schema, think: str = "minimal"):
    global client
    client = client or genai.Client()            # reads GEMINI_API_KEY
    if MODEL.startswith("gemini-2.5"):           # 2.5 uses budgets; 3.x uses levels
        extra = dict(temperature=0, thinking_config=types.ThinkingConfig(
            thinking_budget=0 if think == "minimal" else -1))
    else:                                        # Google: keep 3.x at default temperature
        extra = dict(thinking_config=types.ThinkingConfig(thinking_level=think))
    for attempt in range(4):
        try:
            r = client.models.generate_content(
                model=MODEL, contents=prompt,
                config=types.GenerateContentConfig(
                    response_mime_type="application/json",
                    response_schema=schema, **extra))
            u = r.usage_metadata
            usage["in"] += u.prompt_token_count or 0
            usage["out"] += (u.candidates_token_count or 0) + (u.thoughts_token_count or 0)
            if r.parsed is None:
                raise ValueError("response did not match schema")
            return r.parsed
        except Exception as e:                   # 429 rate limits, timeouts, bad JSON
            wait = 5 * 2 ** attempt
            print(f"  retry in {wait}s: {e}", file=sys.stderr)
            time.sleep(wait)
    sys.exit("Gave up after 4 attempts")

def cmd_codebook(path: str, column: str):
    df = load(path, column)
    sample = df.sample(min(250, len(df)), random_state=1)
    lines = "\n".join(f"[{r.id}] {r.text}" for r in sample.itertuples())
    prompt = f"""These are answers to the survey question: "{column}"
Propose a codebook of 6-15 themes that covers them.
- Each code is one specific theme ("delivery_late", not "service").
- definition: one sentence a human coder could apply consistently.
- example_ids: 2-3 ids of answers that fit. Only use ids shown below.
- Include "other" (fits nothing) and "no_comment" ("-", "nothing", "tidak ada").
- English labels, even if the answers are in another language.
Answers:
{lines}"""
    book = ask(prompt, Codebook, think="high")   # one request, worth thinking about
    Path("codebook.json").write_text(
        json.dumps(book.model_dump(), indent=2, ensure_ascii=False), encoding="utf-8")
    for c in book.codes:                         # show REAL examples, not AI text
        ex = df[df.id.isin(c.example_ids)].text.str[:70].tolist()
        print(f"{c.code:20} {c.definition}\n{'':20} e.g. {ex}\n")

def cmd_code(path: str, column: str):
    df = load(path, column)
    book = json.loads(Path("codebook.json").read_text(encoding="utf-8"))["codes"]
    valid = {c["code"] for c in book}
    defs = "\n".join(f'- {c["code"]}: {c["definition"]}' for c in book)
    texts = dict(zip(df.id, df.text))
    results, todo = {}, df.id.tolist()
    for _ in range(3):                           # re-ask for skipped/invalid rows
        for i in range(0, len(todo), BATCH):
            ids = todo[i:i + BATCH]
            lines = "\n".join(f"[{x}] {texts[x]}" for x in ids)
            prompt = f"""Survey question: "{column}"
Tag each answer with every code that applies (usually 1-2), using ONLY these codes:
{defs}
sentiment: one of positive, negative, mixed, neutral.
Return exactly one item per id. Do not skip ids.
Answers:
{lines}"""
            for it in ask(prompt, CodedBatch).items:
                if it.id in ids and it.codes and set(it.codes) <= valid:
                    results[it.id] = (sorted(set(it.codes)), it.sentiment)
            print(f"  coded {len(results)}/{len(df)}", file=sys.stderr)
        todo = [x for x in df.id if x not in results]
        if not todo:
            break

    df["codes"] = df.id.map(lambda x: ";".join(results.get(x, (["UNCODED"],))[0]))
    df["sentiment"] = df.id.map(lambda x: results.get(x, (None, ""))[1])
    df.to_csv("coded.csv", index=False)

    labels = {c["code"]: c["label"] for c in book}
    rows = []
    for code, g in df.assign(code=df.codes.str.split(";")).explode("code").groupby("code"):
        pool = g[g.text.str.len().between(20, 200)]
        pool = g if pool.empty else pool
        quotes = pool.sample(min(3, len(pool)), random_state=1).text.tolist()
        rows.append({"code": code, "label": labels.get(code, code), "answers": len(g),
                     "pct": round(100 * len(g) / len(df), 1),
                     "negative_pct": round(100 * (g.sentiment == "negative").mean()),
                     **{f"quote_{i+1}": q for i, q in enumerate(quotes)}})
    summary = pd.DataFrame(rows).sort_values("answers", ascending=False)
    summary.to_csv("summary.csv", index=False)
    print(summary[["code", "answers", "pct", "negative_pct"]].to_string(index=False))
    if todo:
        print(f"\n{len(todo)} answers UNCODED - check them by hand in coded.csv")

def cmd_review(n: int = 50):
    df = pd.read_csv("coded.csv")
    s = df.sample(min(int(n), len(df)), random_state=7)[["id", "text"]]
    s["human_codes"] = ""
    s.to_csv("review.csv", index=False)
    print("Fill human_codes in review.csv (codes separated by ;) without opening coded.csv")

def cmd_agree():
    model = pd.read_csv("coded.csv").set_index("id").codes.str.split(";")
    rev = pd.read_csv("review.csv", dtype={"human_codes": str},
                      encoding="utf-8-sig").dropna(subset=["human_codes"])   # Excel adds a BOM
    exact, seen, both = 0, Counter(), Counter()
    for r in rev.itertuples():
        h = {c.strip() for c in r.human_codes.split(";") if c.strip()}
        m = set(model[r.id])
        exact += h == m
        for c in h | m:
            seen[c] += 1
            both[c] += c in h and c in m
    print(f"Exact match: {exact}/{len(rev)} ({exact / len(rev):.0%})")
    for c, n in seen.most_common():
        print(f"  {c:20} agreed {both[c]}/{n}")

if __name__ == "__main__":
    cmds = {"codebook": cmd_codebook, "code": cmd_code, "review": cmd_review, "agree": cmd_agree}
    if len(sys.argv) < 2 or sys.argv[1] not in cmds:
        sys.exit("usage: survey.py codebook|code FILE.csv 'COLUMN' | review | agree")
    cmds[sys.argv[1]](*sys.argv[2:])
    if usage:
        cost = usage["in"] / 1e6 * PRICE_IN + usage["out"] / 1e6 * PRICE_OUT
        print(f"\nTokens in {usage['in']:,} / out {usage['out']:,} = ${cost:.4f}", file=sys.stderr)

Three design choices do most of the safety work:

• The model never writes the quotes. Example answers and summary quotes are pulled from your CSV by id. An AI summary can invent a “customer said…” line; this can’t.
• Codes outside the codebook are rejected. If the model makes up packaging_issue when your codebook says packaging, that row goes back in the queue. After three rounds, anything still failing is marked UNCODED for you to handle.
• Row ids are spreadsheet row numbers. Row 47 in coded.csv is row 47 in your form export, so you can join the codes back to other columns like branch, age group or rating.

Step 3: Build the codebook (and actually edit it)

python survey.py codebook responses.csv "What could we do better?"

You get something like this (from a café feedback form):

wait_time            Mentions slow service or long waits for food or drinks.
                     e.g. ['pesanan lama banget, 25 menit baru datang', 'Waited too long on Saturday night']

price                Comments that items are expensive or about value for money.
                     e.g. ['harga kopi naik terus', 'a bit pricey for the portion']
...

Now open codebook.json and spend 15–20 minutes on it. This is the step where your judgement matters. Common edits:

• Merge overlapping codes. slow_service and wait_time will split the same complaint into two smaller numbers.
• Split vague ones. food_quality might hide two separate problems, “cold food” and “too salty”, that need different fixes.
• Add what you’re asking about. If the owner wants to know whether people mention the new menu, add new_menu even if the sample didn’t surface it.
• Tighten definitions. “Mentions parking” is clearer than “Comments related to the physical accessibility of the location”.

Keep it to roughly 6–15 codes. More than that and both you and the model start disagreeing on the edges.

Step 4: Code everything

python survey.py code responses.csv "What could we do better?"

For 1,000 answers this takes a few minutes on a paid key (25 requests), longer on the free tier because of rate limits; the retry loop waits them out. You get:

• coded.csv: every answer with its codes and sentiment
• summary.csv: one row per theme with count, % of respondents, % negative, and three real quotes

         code  answers   pct  negative_pct
    wait_time      212  23.4            94
        price      148  16.3            88
 staff_praise      131  14.5             0
      parking       97  10.7            96

(Illustrative output. Your numbers will be your survey’s.)

pct is a share of people who wrote a usable answer, not of everyone who filled in the form. Blank and very short answers are left out. Percentages add up to more than 100 because one answer can mention several things. Say so in any report, or someone will ask.

Step 5: Check it before you trust it

python survey.py review

This writes review.csv with 50 random answers and an empty human_codes column. Fill it in without looking at coded.csv, using the same code names separated by ;. It takes 15–20 minutes. Then:

python survey.py agree

Exact match: 37/50 (74%)
  wait_time            agreed 14/15
  price                agreed 9/11
  other                agreed 2/7

“agreed 9/11” means 11 answers got price from you, the model or both, and you both chose it in 9. How to read it:

• High agreement on the big codes: the headline numbers are usable.
• One code keeps disagreeing: its definition is usually the problem, not the model. Rewrite it, re-run code, and check again.
• other is large (over ~10%): your codebook is missing a theme. Read the other rows in coded.csv and add one.

Exact match on multi-code answers is a harsh measure; two humans coding the same answers won’t hit 100% either. What you’re looking for is that the codes that drive decisions are reliable.

Honest cost and time breakdown

Scenario: a 1,000-response customer feedback survey.

Compare that with four-plus hours of tagging by hand before you even start counting. The bigger gain isn’t the hours, though. It’s that the open-ended column actually gets analysed instead of skimmed.

Turning it into money

The people who collect open-ended answers and never analyse them are easy to find: cafés and clinics with feedback forms, event organisers with post-event surveys, schools and courses with end-of-term evaluations, NGOs with programme feedback, small HR teams with engagement surveys.

What to sell: a deliverable, not “AI analysis”. For example: “Send me your form export. In 3 working days you get a one-page summary of the top themes with counts and real customer quotes, plus the coded spreadsheet.” Your real work is the codebook, the check, and writing findings someone can act on.

How to price it: by the deliverable, based on about 2–3 hours of your time per survey plus a bit more for a call to go through results. Use your local freelance rates, not a number from a blog post. Repeat clients (monthly or termly surveys) are where it gets worthwhile, because you reuse the codebook and the second run takes under an hour.

Realistic expectations: your first clients will be people who already know you and already have a survey sitting unread. Running a free analysis on one client’s last survey is a much easier pitch than a cold message.

Reality check: what does NOT work

Skipping the codebook edit. The AI’s first draft is a starting point. Unedited codebooks have overlapping codes, so the same complaint gets split across two themes and looks half as important as it is.

Treating it as statistics. These are counts of what people chose to write, from people who chose to answer. “23% mentioned wait time” doesn’t mean 23% of all customers care about it. Don’t put decimal-point precision on a self-selected sample.

Sentiment on sarcasm and mixed answers. “Great, another 30 minute wait” can come back positive. Sentiment here is a rough sort, not a measurement. Use the theme counts for decisions.

Very short answers. The script drops answers of 2 characters or fewer (“ok”, “-”) before coding. Slightly longer ones like “good”, “mantap” or “nothing” carry almost no information. They’ll mostly land in no_comment or a generic praise code. That’s correct, not a bug.

Expecting identical results every run. Google recommends leaving Gemini 3.x models at their default temperature (lowering it can cause looping), so the script doesn’t set one for them. A fixed codebook keeps most answers stable, but some borderline answers will flip between runs. (On 2.5 Flash the script uses temperature=0, which is more consistent but still not guaranteed identical.) Save coded.csv from the run you report on.

Huge batches to save requests. Sending 200 answers in one request is tempting. In practice, models are more likely to skip ids or drift from the codebook in long lists. 40 is a safe default; the script catches skipped rows either way, but each skip costs a retry.

Putting other people’s data on the free tier. Under Google’s Gemini API terms, content sent on the unpaid tier may be used to improve Google’s products and can be seen by human reviewers. The paid tier isn’t used that way. The script masks emails and Indonesian-style phone numbers, but names and details people type into free text (“Mbak Sari at the Kemang branch was rude”) get through. For any client’s survey, and anything from employees or patients, enable billing; it costs cents. For HR surveys, also check the client’s own confidentiality promises to respondents before data leaves their hands.

Doing a student’s thesis analysis for them. Qualitative coding is assessed work in many degree programmes. Helping someone set up the tool is fine; delivering their analysis as theirs is ghostwriting. Many universities also require AI assistance in analysis to be disclosed.

Hard-coding the model. Google retires and restricts models (see ai.google.dev/gemini-api/docs/deprecations). When you switch, set SURVEY_MODEL, update PRICE_IN/PRICE_OUT, and check which thinking_level values the new model accepts: minimal works on 3.5 Flash-Lite, but on Gemini 3.8 Flash the lowest level is low, so change the default in ask(). Gemini 3.5+ models reject the old thinking_budget setting. Then re-run review and agree on an old survey before you trust the new model.

Final checklist

• Ran on an old survey where you already know roughly what people said
• Codebook edited by hand: no overlaps, 6–15 codes, clear definitions
• other under ~10%, UNCODED checked by hand
• Blind check of 50 answers done, weak codes rewritten
• Billing enabled before any client, employee or patient data goes through
• Report says percentages overlap and come from people who chose to answer
• coded.csv and codebook.json saved with the report

Start with the survey you already have sitting in Google Forms. If the summary tells you something you didn’t know (it usually does), the 45 minutes were worth it.