Survey Sentiment Insights

A local pipeline that turns open-ended survey answers in Excel into sentiment, topics and an executive report, in English and Spanish, without sending data to any external API.

This README is the case study: how the pipeline's sentiment models were measured against human labels for the first time, what that exposed, and what changed as a result. The full analysis is in notebooks/01_sentiment_benchmark.ipynb. Setup and options are in the usage guide.

Key results

• The ML mode was no better than an off-the-shelf lexicon. Its "trained" lexicon changed macro-F1 by −0.003. It was replaced by TF-IDF + logistic regression, which gained +0.088 macro-F1 at the same speed in a 1.3 MB model.
• The LLM mode had three measurable defects, now fixed. Free-form output was invalid JSON 62% of the time; batching five answers per call attached labels to the wrong answer; and the prompt pushed neutral answers to Negative. Together the fixes took it from 0.532 to 0.600 macro-F1.
• The sentiment bar in the report is now close to the truth. The error in the predicted Positive / Neutral / Negative shares fell from 0.25 to 0.075 (total variation distance).
• A confidence cascade beats the LLM while calling it on two thirds of the answers. The classifier keeps the answers it is sure about and sends the rest to the LLM: 0.616 macro-F1, +0.017 over the LLM alone, at about 1.5 times its speed. The threshold was chosen on a separate dev set.
• On real survey answers, only the transformer holds up. On 299 hand-labeled answers from real surveys, XLM-RoBERTa scores 0.680 macro-F1, the LLM 0.545 and the TF-IDF default 0.478. The benchmark's tie between the LLM and the classifier does not survive, and the cascade thresholds tuned on tweets and reviews do not transfer.

The problem

Open-ended answers are the most informative part of a survey and the slowest to read. The pipeline reads an Excel export and produces, per response, a sentiment label, a score from −1 to +1, topics and a one-line summary. It then rolls those up into per-question and global summaries, a topic taxonomy and a formatted Excel report.

Surveys contain personal data, so everything runs on the user's machine. That rules out large hosted models and leaves two options, both shipped as modes of the same pipeline:

	LLM mode	ML mode
Engine	phi4-mini (3.8B) via Ollama	TF-IDF + logistic regression
Sentiment	Label and score from the LLM	Classifier probabilities
Topics and summaries	Written by the LLM, in English	TF-IDF keywords and templates
Needs	Ollama; a GPU helps	pip install only
Measured throughput	1.6 responses/s on an RTX 5060 Ti	~6,000 responses/s on CPU

Before this work, the only validation compared the two modes with each other. Agreement between two models says nothing about whether either is right. The question was simple: how accurate is each mode against human labels, and what should the pipeline use?

flowchart LR
    A[Excel export] --> B[Detect open-text<br/>question columns]
    B --> C{Mode}
    C -->|LLM| D[phi4-mini, one answer per call<br/>JSON-schema output:<br/>summary, topics, label, score]
    C -->|ML| E[TF-IDF + LR label and score<br/>TF-IDF keyword topics]
    D --> F[Consolidate topics<br/>Build category taxonomy]
    E --> F
    F --> G[Per-question and<br/>global summaries]
    G --> H[Excel report<br/>+ quality sheet]

Loading

How it was evaluated

Data. There is no public labeled corpus of open-ended survey answers in Spanish, so the benchmark uses the official test splits of two public corpora in both languages. These are proxies; section 5 checks the results against hand-labeled real survey answers.

Corpus	Languages	Labels	Test rows
CardiffNLP Tweet Sentiment Multilingual	EN, ES	Human-annotated, 3 classes	870 per language
Multilingual Amazon Reviews	EN, ES	Stars: 1–2 Negative, 3 Neutral, 4–5 Positive	1,000 per language, 200 per star

Texts that also appear in the training data were removed. The 3,740 test texts were never used for training or tuning.

Metric. Macro-F1 weights the three classes equally, so a model cannot score well by ignoring Neutral. The report's headline is a Positive / Neutral / Negative bar, so the benchmark also tracks distribution error: the total variation distance between predicted and true class shares.

Uncertainty. Every score has a 95% interval from 2,000 bootstrap resamples. All models share the same resamples, which makes paired comparisons between them valid. Differences are only called real when the paired interval excludes zero.

Tuning discipline. Prompt variants were compared on a separate dev set of 398 texts from the official validation splits. The chosen prompt was then scored once on the test set.

Baselines. A majority-class predictor, off-the-shelf VADER, and XLM-RoBERTa fine-tuned for sentiment as a strong reference. XLM-R was trained on tweets from the same authors as the tweet test set, so its tweet scores are in-domain and flatter it.

Both pipeline modes were scored through the pipeline's own functions, not re-implementations. For the LLM, each text was presented as the answer to a plausible survey question.

Results

1. The trained lexicon did nothing

The original ML mode was VADER extended with an 11,000-word lexicon induced from labeled data. Against off-the-shelf VADER it scored −0.003 macro-F1, with a 95% interval of −0.021 to 0.015. Its small gain in accuracy came from giving up on the Neutral class, which accuracy rewards and macro-F1 does not.

It was replaced by TF-IDF over word 1–2-grams and character 2–5-grams with logistic regression. The regularization strength was tuned by 5-fold cross-validation on training data only. The new model gains +0.088 macro-F1 (interval 0.068 to 0.107) and still classifies about 6,000 texts per second.

2. Three defects in the LLM mode

Broken JSON. The pipeline asked for a JSON array per batch of answers. Free-form, phi4-mini returned valid JSON on the first attempt only 38% of the time, and the pipeline slept and retried. Passing a JSON schema to Ollama's structured outputs constrains decoding to the expected shape. On the same 60 batches, first-attempt validity rose to 98%.

Misaligned batches. Even with valid JSON, batches of five produced labels and summaries that belonged to a different answer. For example, "Llegó más rápido de lo previsto" ("arrived faster than expected") came back Negative with the summary "disappointed with the broken outer case". An ablation on 398 texts, run twice in opposite orders, compared three setups:

Setup	Macro-F1, run 1	Macro-F1, run 2	Seconds, run 2
5 answers per call	0.521	0.543	190
5 per call, each object quotes its answer first	0.567	0.566	238
1 answer per call	0.570	0.578	182

One answer per call is the most accurate and no slower, because Ollama caches the long shared prompt prefix. On the full test set it gained +0.036 macro-F1 (interval 0.019 to 0.053).

Neutral bias. The LLM then predicted 62% Negative on a test set that is 37% Negative. Four suspects in the prompt were tested one at a time on the dev set:

Prompt variant (dev set)	Macro-F1	Neutral F1	Distribution error
P0: original prompt	0.589	0.278	0.196
P1: drop "use the full score range"	0.593	0.286	0.211
P2: drop the sentiment keyword lists	0.572	0.250	0.247
P3: add an explicit rule for Neutral	0.621	0.359	0.171
P4: write summary and topics before the label	0.599	0.446	0.081
P5: Neutral rule and summary first	0.629	0.479	0.068

The keyword lists and the score-range instruction made no difference beyond the run-to-run noise of about 0.02. What worked was defining Neutral and letting the model describe the answer before committing to a label. On the test set, P5 gained +0.032 macro-F1 (interval 0.016 to 0.050) and cut distribution error from 0.25 to 0.075. The cost is speed: 2.15 to 1.60 texts per second.

A fourth fix came from a single runaway generation that took 407 seconds. Output length is now capped in proportion to the batch.

3. Where the models land

Model	Macro-F1	95% interval	Texts/s	Status
XLM-RoBERTa	0.667	0.651–0.682	29 (CPU)	Reference
LLM mode, final	0.600	0.585–0.615	1.6 (GPU)	In pipeline
ML mode, TF-IDF + LR	0.587	0.571–0.602	6,352 (CPU)	In pipeline
LLM mode, original	0.532	0.517–0.547	2.6 (GPU)	Replaced
ML mode, VADER + lexicon	0.499	0.484–0.514	20,421 (CPU)	Replaced
VADER, off-the-shelf	0.481	0.465–0.496	18,767 (CPU)	Reference
Majority class	0.180			Reference

The final LLM and the TF-IDF model are tied: +0.013, with an interval of −0.005 to 0.033. XLM-RoBERTa beats both by about 0.07, part of which is its in-domain advantage on tweets.

Each supervised model wins on its own training domain. XLM-R leads on tweets and TF-IDF leads on Amazon reviews. The LLM is the most even across the four test sets, from 0.54 to 0.59. Survey answers are a third domain, so that evenness is a point in the LLM's favor, but only labeled survey data can confirm it.

Neutral remains the hard class for every model. About 7% of test texts are ones where the three strongest models agree with each other and disagree with the gold label. Many of these are genuinely ambiguous, such as three-star reviews whose text reads clearly positive or negative. This caps the Neutral F1 any model can reach. After the prompt fix, the direction of the LLM's errors flipped. The original prompt turned neutral texts into polar ones about three times as often as the reverse. The final prompt errs slightly the other way, labeling polar texts as Neutral.

4. More classical models, and a confidence cascade

Every model is now a sentiment engine behind one interface (survey-insights engines). Two more classical models share the TF-IDF features of the default: complement naive Bayes and a linear SVM with calibrated probabilities.

Both first trailed logistic regression by about 0.06 in cross-validation. The cause was class balance. Logistic regression is trained with balanced class weights, and the SVM's probability calibration was fitted on unweighted data, which pushed it back toward the majority classes. With balanced sample weights, which also reach the calibrator, the SVM ties logistic regression on the test set (0.596 vs 0.587) and naive Bayes trails at 0.572.

The cascade keeps the classifier's label when its top probability clears a threshold and routes the rest to a stronger model. The classifier's confidence is well calibrated on the dev set (expected calibration error 0.039) and moderately informative about its errors (AUROC 0.68).

Cascade	Routed on test	Macro-F1	Against the slower model alone	Speed
TF-IDF + LR, unsure answers to the LLM	67%	0.616	+0.017 (0.009 to 0.025)	~1.5x the LLM
TF-IDF + LR, unsure answers to XLM-R	61%	0.665	−0.002 (−0.011 to 0.008)	~1.6x XLM-R

The dev set has 398 texts, so its curve is noisy. For the LLM cascade the dev-chosen threshold routes 67% of answers, while the test curve peaks near 40%. The threshold was fixed before looking at the test curve, and it still beats the LLM alone.

5. Real survey answers

To measure the engines where they are meant to work, 299 answers from real surveys were hand-labeled with the project's labeling tool. They were sampled across 33 open-ended questions in proportion to how many answers each had, without non-answers or duplicates. They were labeled blind to every model's output, following guidelines written before labeling began. The answers stay private; only the aggregate metrics are published.

Engine	Real answers	95% interval	Benchmark
XLM-RoBERTa	0.680	0.621–0.733	0.667
Cascade, unsure answers to XLM-R	0.649	0.589–0.702	0.665
Cascade, unsure answers to the LLM	0.588	0.529–0.645	0.616
LLM (phi4-mini)	0.545	0.485–0.603	0.600
VADER	0.531	0.470–0.592	0.481
TF-IDF + linear SVM	0.507	0.450–0.562	0.596
TF-IDF + logistic regression (default)	0.478	0.421–0.534	0.587
TF-IDF + naive Bayes	0.384	0.329–0.438	0.572

• Only XLM-RoBERTa keeps its benchmark score. It beats the LLM by 0.135 (interval 0.064 to 0.207) and the default classifier by 0.202. The TF-IDF classifiers lose 0.09 to 0.19, as bag-of-words models trained on tweets and reviews should be expected to on a new domain. The LLM loses 0.055.
• The cascade thresholds do not transfer. Tuned on a dev set of tweets and reviews, the XLM-R cascade now trails XLM-R alone by 0.031, an interval that excludes zero. Tuning them for surveys needs a separate dev set of survey answers.
• VADER's gain is an artifact. Half of the real answers are Neutral, and VADER, an English lexicon, labels most Spanish text Neutral. The engine recommender only offers VADER for English-only data.

The engine cards now carry these results, so the MCP server's recommend_engine ranks engines on real answers: without a time limit it recommends XLM-RoBERTa. The pipeline's defaults are unchanged for now.

Limitations

• Domain shift. Tweets and product reviews are not survey answers, and section 5 shows the ranking changes on real answers. Benchmark-only conclusions, such as the cascade thresholds, need checking there.
• A small, single-annotator real set. The real-survey intervals are about ±0.06 wide, four times the benchmark's, and the annotator's self-agreement is not measured yet.
• Topics and summaries are not evaluated. They are the main reason to run the LLM, and they have no measurement yet.
• ML-mode topics are rough. They are overlapping TF-IDF n-grams such as "mobile app quick" and "mobile app", in the answer's own language.
• Run-to-run variance. At temperature 0.1, the LLM's macro-F1 moves by about 0.01–0.02 between identical runs. Smaller differences are not meaningful.
• One machine, one LLM. Throughput was measured once on a single workstation. Only phi4-mini was benchmarked.

Next steps

1. Blind re-label of 10% of the real answers, to measure the annotator's self-agreement.
2. Label a separate dev set of survey answers, to tune the cascade thresholds for this domain.
3. Consider XLM-RoBERTa for sentiment inside LLM mode, keeping the LLM for topics and summaries.
4. Build an evaluation for topic extraction and summaries.

Run it

Python 3.11 or newer. ML mode needs nothing else:

pip install -r requirements.txt
survey-insights sample
survey-insights analyze input_surveys/sample_survey.xlsx --mode ml

The first command writes a synthetic bilingual survey of a fictional product. The results workbook lands in output/.

For LLM mode, install Ollama, pull the model and drop the flag:

ollama pull phi4-mini
survey-insights analyze input_surveys/sample_survey.xlsx

Ollama picks the device on its own and uses a supported GPU automatically.

In ML mode, any engine can label sentiment. The list shows each engine's measured macro-F1 and speed, and whether it can run on this machine:

survey-insights engines
survey-insights analyze input_surveys/sample_survey.xlsx --mode ml --engine cascade_xlmr

XLM-RoBERTa and its cascade need pip install -e .[xlmr], which adds PyTorch.

There is also a local web UI, and an MCP server with a local agent that picks the engine from the measured evidence:

survey-insights serve                                  # then open http://localhost:8000
ollama pull qwen2.5:7b-instruct
survey-insights agent "20,000 answers in Spanish, sentiment only, 5 minutes: which engine?"

Everything runs on this machine. The agent refuses Ollama's cloud models and any OLLAMA_HOST that is not local.

To reproduce the benchmark:

pip install -r requirements-dev.txt
python scripts/download_datasets.py
python evaluation/benchmark.py

Predictions are cached in evaluation/results/, so the notebook re-runs in seconds without Ollama. The usage guide covers every command and flag, the report's sheets, GPU settings, the web UI, the agent and troubleshooting.

Code and tests

The pipeline is an installable package, survey_insights, with one module per stage:

src/survey_insights/
  survey_io.py     reads the Excel export and finds the open-ended questions
  llm.py           LLM mode: the benchmarked prompt, JSON schema, retries, topic vocabulary
  engines.py       sentiment engines behind one interface: VADER, three TF-IDF classifiers,
                   XLM-RoBERTa, the LLM and two confidence cascades, each with a model card
  ml.py            ML mode: sentiment from any engine and TF-IDF keyword topics
  classifier.py    loads the packaged TF-IDF classifiers (models/ holds them and their cards)
  topics.py        cross-question topic consolidation and the category taxonomy
  summaries.py     executive summaries and key findings
  quality.py       label-free reliability checks
  report.py        the results workbook
  pipeline.py      runs the stages end to end, with checkpoints for long LLM runs
  cli.py           the survey-insights command
  labeling.py      private hand-labeling of real answers for the next evaluation
  mcp_server.py    MCP server: engine evidence, a rule-based engine recommendation, analysis tools
  agent.py         local agent (qwen2.5 via Ollama) that uses the MCP server as its client
  validation.py, web/, training.py, sample_survey.py

The per-answer prompt and its JSON schema stay in the Spanish they were benchmarked in; the model's output is mapped to English records at the boundary. A test pins the prompt byte for byte, so changing it means re-running the benchmark.

Tests run without Ollama: LLM mode is exercised against a deterministic fake model that returns schema-valid answers, and the tests cover retries, batch splitting, checkpoint resume, the workbook, the CLI, the web API, the engines, the MCP round trip and the agent loop. GitHub Actions runs lint and the tests on Linux with Python 3.11 and 3.14 and on Windows with Python 3.14.

pip install -r requirements-test.txt
pytest
ruff check src tests evaluation scripts

Repository layout

src/            the survey_insights package
tests/          pytest suite and the fake model
evaluation/     benchmark, ablation and prompt experiments; cached predictions in results/
notebooks/      benchmark analysis
scripts/        downloads the public datasets and test splits
docs/           usage guide and figures
input_surveys/  surveys to analyze; only the synthetic sample is tracked

Survey data, results, downloaded datasets and the domain-context file are git-ignored.

License

Code is released under the MIT License. The datasets keep their own licenses. The Amazon reviews corpus is for non-commercial research use, and the tweet corpus follows its dataset card. Neither is redistributed here: scripts/download_datasets.py fetches them from the Hugging Face Hub, and the notebook's error analysis quotes only a handful of test examples.