Skip to content
Faisal Mahmud TusherDigital marketing, SEO & analytics
with Faisal Tusher

Marketing Research › Customer Research

Cleaning and Tabulating Survey Responses

How to clean survey responses and build tables: keep the raw export, remove tests and duplicates, apply rules set in advance, log exclusions and show bases.
Tutorialintermediate13 min readUpdated

What cleaning and tabulating are for

The survey design guide gives cleaning a single sentence: strip out test answers and duplicates, anyone the screening questions should have stopped, and anyone who raced through or ticked one column all the way down, then run the plan. This tutorial is that sentence carried out with a record, together with the job that follows it, which is turning the cleaned file into tables a reader can check for themselves. The customer research section places it beside the other survey tutorials: drawing the sample comes before fieldwork, pretesting before launch, and this page after the survey closes.

Two things make cleaning riskier than it looks. Every exclusion changes who the results describe, so a rule that removes one kind of customer more than another moves the answer even when each removal looks sensible. And a rule decided after the results have been read becomes a way of choosing the answer, because it is hard not to notice which respondents are inconvenient. The defence against both is the same: rules written in advance, applied to everyone, recorded with what they removed, and checked afterwards for what they did to the make-up of the sample.

The pretesting tutorial runs similar checks on a soft launch, while the questionnaire can still change. Here the questionnaire is frozen and the people have answered; the only decision left is which responses count and how to present them. The table shows what each kind of problem does if it stays in the file, and what cleaning cannot repair.

Problem in the exportWhat it does if left inWhat cleaning cannot repair
Test and staff responsesAdds answers from people outside the populationNothing; removing them is the whole fix
Duplicate responsesCounts one person's views twiceA frame that held the same person under two records
Partial responsesBases shift from question to question without anyone noticingA question that loses people, which the completion funnel points to
Answers the screener should have stoppedMixes in people the decision is not aboutA screener that gave away the qualifying answer
Speeding and straight-liningRecords clicks rather than viewsA questionnaire too long or grids too large
Answers outside the routingCounts people who should never have seen a questionThe routing fault itself, found too late to fix

Cleaning also cannot fix a sample that never reached the right people. If the people who answered differ from the people invited, that is Non-Response Bias or a frame problem, and the diagnosis for unrepresentative survey results tells those causes apart. If too few answered at all, the diagnosis for a survey few people complete works through the completion funnel from invitation to finish.

Before you start

Have the analysis plan open. The survey analysis plan template sets out what the decision needs to see, which question feeds which output, the groups for comparison, the rule for small groups and whether to weight. An analysis plan fixed before fieldwork lets those rules be applied without anyone knowing which result they favour. If the plan has no completion rule and no rules for poor-quality answers, write them now, before opening the results, note the date you wrote them, and say in the report that they were written after fieldwork closed. A sceptical reader can fairly ask whether rules written after fieldwork closed were chosen for the answer they give.

Have the questionnaire exactly as it went out, with its routing, and the record from the survey launch checklist if the survey passed through it. The routing tells you which blanks are correct and which are faults, and the launch record tells you what was tested before invitations went.

Get the export in a shape you can work with: one row per response, a response ID, start and finish times, the completion status, every closed answer, the open answers and the fields the results will be grouped by. Where those fields come from customer records rather than from questions, they arrive with the draw, as in the tutorial on sampling from your own customer list. Check what your export holds before relying on a field.

Check what respondents were told. Under the ESOMAR and MRS codes, as the site's research guides summarise them, answers stay confidential unless a respondent agreed to something else, and a report states honestly how the study was done and where it falls short. The tutorial on participant information and consent explains how those promises were set; the cleaning file has to keep them. An export that can be tied to a person through a tracked link or a customer ID is personal data, so keep it where only the people analysing the survey can reach it.

Agree who cleans and who checks. One person applies the rules; a second reads the cleaning log and the tables before anything is reported. The second reader does not need to repeat the work, only to be able to follow it.

The steps

  1. Keep the raw export untouched and work on a copy

    Save the export as it arrived, name it with the survey and the date, and make it read-only. Do every piece of cleaning in a copy, and do it in a way that can be repeated: a list of rules applied in order, a script, or a sheet with a column for each rule, rather than rows deleted by hand. When a reader later challenges a figure, or the next Survey Wave needs the same treatment, the cleaned file can then be rebuilt from the original. In the working copy, replace names and contact details with the response ID wherever the analysis does not need them. The personal data entry explains why free text that identifies someone counts too, so open answers are cleaned of names and account numbers before anyone else reads them.

  2. Remove test, staff and duplicate responses

    Take out the responses given while the survey was being tested, using the test flag or the times before launch, and responses from colleagues who reached the survey through a shared link despite the frame excluding staff. Then find duplicates: the same invitation link or customer ID answering twice, or the same person under two records. Decide in advance which response to keep, such as the first complete one, and apply that choice to every duplicate. Similar answers given at similar times are not proof of a duplicate on their own; without a shared identifier, flag them for the quality check rather than removing them here.

  3. Apply the completion rule written before fieldwork

    Define a complete response before counting anything: reaching the last required question, or answering every question the decision depends on. Then treat partial responses one way throughout. Either set them aside, or keep each answer they gave and report every question on its own base; never keep them in one table and drop them from the next. Before setting partials aside, look at where they stopped. A cluster of people leaving at one question says something about that question, and the response rate diagnosis above shows how to read it. The people who finished are the survivors of the questionnaire, and the survivorship bias framework is a reminder to name the group that dropped out in the report rather than let it vanish.

  4. Check screening answers, quotas and routing

    Find anyone who gave a disqualifying screening answer but reached the main questions, which points to a fault in the survey's logic, and remove them: their answers describe people the decision is not about. Compare the count in each quota group with the plan, using the record fields where you have them rather than what people said about themselves. Then read the routing: answers to questions a person should have skipped, and blanks where everyone should have answered. Decide question by question whether the affected answers are set to blank or the question is reported with a note, and record the decision. Answers that contradict each other, such as a person who says they have never ordered and then rates the delivery, go forward to the next step as a flag rather than an automatic removal.

  5. Flag speeding, straight-lining and empty answers by rules set in advance

    Write each rule against your own survey. For speed, the rule compares each response with how long your own respondents took, using a cut-off the team chooses before looking at any answers. For straight-lining, the rule looks for the same column chosen down an entire grid. For open questions, it looks for answers that say nothing, repeat the question or are plainly unrelated. Treat each as a flag, and remove a response only when it meets the combination the plan named, because a quick reader with clear views is not a careless one. Never look at what someone answered on the substance of the survey when deciding whether to remove them; a rule that happens to remove the critics is not a quality rule.

  6. Back-code 'other' answers and send the open questions to the codeframe

    Read every "other, please specify" answer. Where the written answer plainly belongs to an existing option, move it there and record the move; where it fits no option, leave it as other. When several people wrote the same thing, that is a finding about a missing option, to be noted for the next version of the questionnaire, not a new option added silently to this one. Open questions go to a Codeframe built as the tutorial on coding open-text answers describes, with personal details already removed. If the survey repeats an earlier wave, use the earlier wave's options and codes so the results can be compared, and date anything added.

  7. Log every exclusion and compare who was removed with who was kept

    Keep a cleaning log with one row per rule: the rule as written, the step where it was applied, who applied it, the date and the number of responses it removed. Then set the removed responses beside the kept ones on the fields from the records, such as customer type, tenure or region. If one rule removed a noticeably different kind of customer, the rule changed the result, and the report says so. Change a rule only if it was wrong in principle, never because of what it did to an answer, and log the change. Finally compare the kept respondents with the frame, the last step of the sampling tutorial, so the report can say how the people answering differ from the people invited.

  8. Build each question's table with its base

    For every question, put the exact wording above the table, say who was asked, and show the base: the number of people asked and the number who answered, with a note on which one the shares use. Show counts beside shares. Keep "don't know" and "prefer not to say" visible rather than removing them from the base without saying so. Show every point of a scale before grouping any, and define any grouping of scale points before you see the results. Shares for a question that allowed one answer add up to the whole; shares for a question that allowed several add up to more, and the table says so, as the coding tutorial does for open answers. The base is what limits a figure, not the number invited, and the Sample Size behind the whole survey does not carry over to a question that only some people saw.

  9. Cut the planned comparisons with the small-group rule and any planned weighting

    Cut the results only by the groups the plan named, each column carrying its own base, with shares read within each group so that groups of different sizes can be compared. Apply the plan's small-group rule: below the count it sets, show the group as counts rather than shares, merge it with a neighbour, or hold it back. For the figures the decision rests on, the guide to reading small numbers recommends a range around the figure, such as a Confidence Interval, instead of a single number. Weight only if the plan said so, on the variables it named, and show weighted and unweighted figures side by side where they differ. Then hand the tables to the readout, which the guide to reporting research findings builds around the answer to the decision, and store the cleaned file, the log and the tables beside the untouched export. For a survey that repeats, the customer survey wave SOP logs what each cleaning rule removed and stores the draw and the coded answers where the next wave will look, so that wave can be drawn and coded the same way.

A cleaning log and a table that carries its base

The cleaning log is short when the rules were written in advance. Its columns, with what goes in each, are below; the rows follow the order of the steps, so a reader can see the file shrink and why.

ColumnWhat to writeWhy it matters
StepThe step where the rule was appliedShows the order, which changes what each rule removes
Rule as writtenThe wording from the plan, with the date it was writtenProves the rule came before the results
Applied byThe person who ran itLets the second reader ask the right person
RemovedThe number of responses the rule took outLets the reader rebuild the final base
Who was removedHow the removed responses differ from those kept, on record fieldsShows whether the rule changed who is speaking
NoteAnything decided along the way, such as a routing faultKeeps decisions out of people's memories

A table that a reader can check carries the same elements every time: the exact question wording above it, who was asked, the base at the top of each column, the count beside every share, weighted and unweighted figures side by side where weighting was planned, and a line under the table when shares can add up to more than the whole. A table without its base cannot be read, however clear the chart made from it looks.

A fabric shop cleans and tables its customer survey (illustrative)

Illustrative example — not a real result

Quiddlesk Fabrics cleans and tables its customer survey

Quiddlesk Fabrics, an invented online fabric shop, surveys customers about a planned range of sewing kits. It invites 2,400 customers drawn from its order records; 512 start the survey and 446 reach the last required question.

The analysis plan, written before full fieldwork, counts a response as complete if it reaches the last required question, and removes a respondent only when two signs appear together: a completion time below the cut-off the team set from its own soft-launch timings, and the same answer down both grids. The cleaning log, in the order of the steps, first removes 3 test responses, 2 from staff and 6 duplicates found by invitation link (keeping the first complete response), all of which had reached the last required question. The completion rule then sets aside the 66 partial responses; 41 of them stopped at the question asking customers to rank kit themes, which the report names as a fault in the questionnaire. Of the 435 complete responses left, 5 people who said they had not bought in the past year should have been stopped by the screener, and 9 respondents met both quality signs. 421 responses remain.

Setting the removed beside the kept shows that 7 of the 9 flagged respondents were first-time buyers, against about a third of the kept respondents, so the report notes that first-time buyers are slightly under-represented after cleaning. Of 38 "other" answers about what customers mainly sew, 22 are back-coded into existing options, such as cushion covers into home furnishings, and 16 stay as other.

The table for "Which of these kits would you consider buying?" shows a base of 421, counts beside every share and a line beneath it explaining that the shares exceed 100% since customers could choose several. Cut by what customers mainly sew, the quilting group has a base of 34, so its results appear as counts only, as the plan's small-group rule requires. Repeat buyers are over-represented against the order records, so the planned weighting on order count is applied, and the headline figure for the beginners' kit appears as 31% unweighted and 35% weighted, side by side.

Common mistakes

  • Overwriting the raw export. Once the original is gone, nobody can rebuild the figures or answer a challenge.
  • Writing the rules after reading the results. A rule chosen with the answers in view is a way of choosing the answer.
  • Removing people for what they said. A quality rule that happens to take out the critics or the enthusiasts is not a quality rule.
  • Borrowing a speed cut-off from another survey. How long a careful respondent takes depends on this questionnaire, these devices and these customers.
  • Removing on one weak sign. Speed alone, or one straight-lined grid, also catches quick, decided readers.
  • Dropping partial responses silently. Bases shift between tables and the people who left at a hard question disappear from the story.
  • Shares without bases. A percentage with no count behind it cannot be read or checked.
  • Treating a multi-answer total as a whole. Shares that add up to more than the whole need saying so under the table.
  • Cutting by every available group. A long list of cuts produces groups too small to read and differences found by fishing.
  • Weighting chosen for the answer it gives. The decision to weight, and on what, belongs in the plan before fieldwork.

Frequently asked questions

Should we remove everyone who finished the survey very quickly?

Not on speed alone, and never by a rule invented once the results are in. Write the rule before anyone reads the results, judged against how long your own respondents took rather than a cut-off borrowed from another survey, and look for a second sign, such as an identical answer down every grid or an open answer that says nothing, before removing anyone. Record the rule and the number of people it removed, and check whether the people removed differ from those kept, because a rule that removes one kind of customer changes the result.

Can we use answers from people who stopped partway through?

Yes, if the analysis plan said so before anyone read the results. One defensible choice keeps every answer a person gave and reports each question on its own base; another keeps only people who reached the end. Either works when it is written down in advance and stated in the report. What does not work is deciding after reading the results, or mixing the two so that one table counts partial respondents and the next does not.

Should 'don't know' and 'prefer not to say' answers stay in the base?

Keep them visible in the table rather than dropping them silently. Whether a share is calculated on everyone asked or only on those who gave a substantive answer is a choice for the analysis plan, made in advance and stated under the table, because the same answers produce different shares on different bases. A large block of 'don't know' answers is itself a finding about the question or about what customers know.

Terms used on this page

Frameworks

Sources

  1. ESOMAR — ESOMAR
  2. Market Research Society — MRS
  3. Regulation (EU) 2016/679 (GDPR) — EUR-Lex

Related