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 export | What it does if left in | What cleaning cannot repair |
|---|---|---|
| Test and staff responses | Adds answers from people outside the population | Nothing; removing them is the whole fix |
| Duplicate responses | Counts one person's views twice | A frame that held the same person under two records |
| Partial responses | Bases shift from question to question without anyone noticing | A question that loses people, which the completion funnel points to |
| Answers the screener should have stopped | Mixes in people the decision is not about | A screener that gave away the qualifying answer |
| Speeding and straight-lining | Records clicks rather than views | A questionnaire too long or grids too large |
| Answers outside the routing | Counts people who should never have seen a question | The 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 BiasThe gap between the people a survey or study invited and those who answered; engaged, satisfied customers tend to reply more, so results lean their way. 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 planA survey's plan, written before the questionnaire: the decision, the figures and comparisons the report needs, which question feeds each, and small-group rules. 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
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 WaveOne round of fieldwork in a repeated survey or tracker, drawn, worded and coded the same way as earlier rounds so the results can be compared over time. 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.
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.
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.
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.
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.
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 CodeframeThe defined set of codes open-text answers are tagged with, built from a sample, applied consistently, checked by a second coder and counted against a base. 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.
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.
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 SizeThe number of users or sessions an experiment needs to detect a given effect reliably. behind the whole survey does not carry over to a question that only some people saw.
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 IntervalA range of values for an effect, such as a lift in conversion rate, that is consistent with the data at a chosen confidence level., 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.
| Column | What to write | Why it matters |
|---|---|---|
| Step | The step where the rule was applied | Shows the order, which changes what each rule removes |
| Rule as written | The wording from the plan, with the date it was written | Proves the rule came before the results |
| Applied by | The person who ran it | Lets the second reader ask the right person |
| Removed | The number of responses the rule took out | Lets the reader rebuild the final base |
| Who was removed | How the removed responses differ from those kept, on record fields | Shows whether the rule changed who is speaking |
| Note | Anything decided along the way, such as a routing fault | Keeps 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)
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.
