Analyse feedback at scale

The problem

You've got hundreds or thousands of free-text responses. Too many to paste into ChatGPT, too many to read manually. You need to categorise them systematically, maybe run each one through multiple checks (theme, sentiment, urgency), and do this in a way you can repeat next time.

The solution

You'll use Python to process each response through an AI API (OpenAI or Anthropic). This lets you handle any volume, run multi-step analysis (categorise first, then assess sentiment, then flag urgent items), and build up a dataset you can filter and analyse. It costs a few pounds per thousand responses and takes minutes to run. IMPORTANT: Before sending beneficiary feedback through any AI API, ensure you have appropriate data processing agreements in place and consider whether the data should be anonymised first.

What you get

A spreadsheet with each response categorised by theme, sentiment, and any other dimensions you care about. You can filter, pivot, and chart the results. The categorisation is consistent because every response goes through the same process. You can re-run it on new data with one command.

Before you start

• Your feedback exported as a CSV (one response per row)
• An OpenAI or Anthropic API key (costs ~£0.001-0.01 per response depending on model)
• Basic comfort with Python, or willingness to adapt code with AI help
• A Google account for Colab

When to use this

• You've got more than 100 responses and the simple paste approach won't work
• You need to process feedback regularly and want a repeatable system
• You want multi-step analysis (theme + sentiment + urgency flags)
• You need consistent categorisation you can aggregate and compare over time

When not to use this

• You've got under 100 responses. Just paste them into Claude or ChatGPT directly
• This is a one-off and you'll never do it again. The setup time isn't worth it
• The feedback contains identifiable beneficiary data and you don't have a data processing agreement with the API provider
• Your organisation's data protection policy prohibits sending personal data to third-party AI services
• The feedback includes safeguarding disclosures or other highly sensitive information

Steps

1. 1

   Check data protection requirements

   Before processing any feedback through AI APIs, check with your data protection lead. Key questions: Does the feedback contain personal data? Do you have a data processing agreement with your chosen API provider (OpenAI or Anthropic)? Should you anonymise the data first? Consider removing names, locations, and any identifiable details. Both OpenAI and Anthropic offer enterprise agreements with stronger data protections - check if your organisation needs these.

2. 2

   Define your categories upfront

   Before you write any code, decide what you want to categorise. What themes are you looking for? (You can let the AI suggest these from a sample first.) What sentiment scale? (Positive/negative/neutral, or 1-5?) Any special flags (urgent, safeguarding, complaint)? Write these down clearly.

3. 3

   Test your categories on a sample

   Take 20-30 responses and test your categorisation in ChatGPT or Claude. Paste them in with your category definitions and see if the results make sense. Refine your categories based on what you learn. It's much easier to adjust now than after processing thousands.

4. 4

   Get your API key

   Sign up at platform.openai.com or console.anthropic.com. Add some credit (£5-10 is plenty to start). Create an API key and keep it safe. In Google Colab, set your API key by running: `import os; os.environ['OPENAI_API_KEY'] = 'your-key-here'` in a code cell before the main script. For better security, use Colab's Secrets feature (key icon in left sidebar) to store the key, then access it with `from google.colab import userdata; os.environ['OPENAI_API_KEY'] = userdata.get('OPENAI_API_KEY')`. Don't share your key or commit it to a public repository.

5. 5

   Set up the processing script

   Open Google Colab and use the example code below. Upload your CSV. The script processes each response through the API, categorises it according to your rules, and saves the results. Start with a small batch (50 rows) to check it's working before running the full dataset.

6. 6

   Add multi-step processing if needed

   For complex analysis, run each response through multiple prompts. First pass: assign a theme. Second pass: assess sentiment for that theme. Third pass: flag if urgent. Each step can use the results of previous steps. This costs more but gives richer output.

7. 7

   Review and refine

   Check a random sample of categorised responses. Are they accurate? If the AI is consistently miscategorising something, update your prompt with clearer instructions or examples. Then re-run on the problem cases.

8. 8

   Analyse your categorised data

   Now you've got structured data, analyse it however you like. Pivot tables in Excel, charts in Google Sheets, or more Python analysis. Compare themes over time, break down by demographic, identify what drives negative sentiment.

Example code

import pandas as pd
from openai import OpenAI
import time

# Set up the client (paste your API key or set as environment variable)
client = OpenAI()  # Uses OPENAI_API_KEY environment variable

# Load your feedback
df = pd.read_csv('feedback.csv')

# Define your categories
themes = [
    'service quality',
    'staff and volunteers',
    'accessibility',
    'communication',
    'waiting times',
    'facilities',
    'impact and outcomes',
    'other'
]

def categorise_response(text):
    """Categorise a single response by theme and sentiment."""
    if not text or pd.isna(text) or len(str(text).strip()) < 3:
        return {'theme': 'empty', 'sentiment': 'neutral', 'summary': ''}

    prompt = f"""Analyse this feedback and return a JSON object with:
- theme: one of {themes}
- sentiment: positive, negative, or mixed
- summary: a 5-10 word summary of the main point

Feedback: {text}

Return only valid JSON, no other text."""

    response = client.chat.completions.create(
        model="gpt-4o-mini",  # Fast and cheap
        messages=[{"role": "user", "content": prompt}],
        response_format={"type": "json_object"}
    )

    import json
    return json.loads(response.choices[0].message.content)

# Process all responses (with rate limiting)
results = []
for idx, row in df.iterrows():
    if idx % 50 == 0:
        print(f"Processing {idx}/{len(df)}...")

    result = categorise_response(row['feedback_text'])
    result['original'] = row['feedback_text']
    results.append(result)

    time.sleep(0.1)  # Avoid rate limits

# Save results
results_df = pd.DataFrame(results)
results_df.to_csv('feedback_categorised.csv', index=False)

# Quick summary
print("\nTheme breakdown:")
print(results_df['theme'].value_counts())
print("\nSentiment breakdown:")
print(results_df['sentiment'].value_counts())

def deep_analyse_negative(row):
    """For negative responses, identify specific issues and urgency."""
    if row['sentiment'] != 'negative':
        return {'issues': [], 'urgency': 'normal', 'action_needed': ''}

    prompt = f"""This feedback was categorised as negative, theme: {row['theme']}.

Original feedback: {row['original']}

Analyse further and return JSON with:
- issues: list of specific issues mentioned
- urgency: "urgent" if needs immediate attention, "normal" otherwise
- action_needed: one sentence on what action might help

Return only valid JSON."""

    response = client.chat.completions.create(
        model="gpt-4o-mini",
        messages=[{"role": "user", "content": prompt}],
        response_format={"type": "json_object"}
    )

    import json
    return json.loads(response.choices[0].message.content)

# Apply to negative responses only
negative_df = results_df[results_df['sentiment'] == 'negative'].copy()
deep_analysis = negative_df.apply(deep_analyse_negative, axis=1, result_type='expand')
negative_df = pd.concat([negative_df, deep_analysis], axis=1)

# Flag urgent items
urgent = negative_df[negative_df['urgency'] == 'urgent']
print(f"\n{len(urgent)} urgent items need attention:")
print(urgent[['original', 'issues', 'action_needed']])

Tools

Resources