Analysing survey data

In depth

Preparing the data

Partial responses. Fifty-six of Ana’s questionnaires were partials, which under her rule in Survey error answered three to five of the six questions (people who answered fewer are break-offs, counted with the refusals). Survey error showed how AAPOR’s response rates count these partials; for analysis the question is which of their answers to use. One rule analyses completes only, so every question has the same base. The other keeps each partial’s answers to the questions it reached, so each question has its own base and every table needs its base printed. Ana chose completes only before fieldwork (fictional), because her questionnaire is short and one base keeps her tables comparable; whichever rule you choose, fix it before you see the results, so that it can’t be picked to suit them.

Speeders and straight-liners. A speeder finishes far faster than anyone could read the questions; a respondent who is straight-lining gives the same answer to every item in a grid of rating questions. Both are signs of satisficing, answering with too little effort to be accurate, and Zhang and Conrad (2014) found that web respondents who sped also straight-lined more grid questions. Flag them by rules set in advance, such as a completion time under a third of the median, or identical answers across a grid that contains reversed items, which no attentive person would give. Thresholds differ between studies, so state yours, remove a case only when the evidence is strong, and report how many you removed. Lantern’s six questions contain no grid, so the challenging example uses a survey that does.

Missing answers. A blank can mean that a question wasn’t asked (routing skipped it), that the respondent skipped it (item non-response), or that the answer was “don’t know” or “prefer not to say”. Give each a distinct code and never record any of them as 0, because 0 is a real answer on a 0–10 scale. If “don’t know” is stored as 99, =AVERAGE(C2:C517) averages the 99s with the real scores, while =AVERAGEIFS(C2:C517,C2:C517,"<99") leaves them out. Decide for each figure whether “don’t know” belongs in the base: on a purchase-intent question it is a meaningful answer, best shown as a row of its own. Ana’s completes have an answer to every closed question, so the problem doesn’t arise in her export, but it does in most.

Coding open answers. Answers to an open question become numbers through a code frame: a list of themes, each with a definition and examples, drafted from a sample of the answers and then applied to all of them. Qualitative research builds codebooks the same way and checks inter-coder agreement. An answer can be single-coded, to its main theme, or multi-coded, to every theme it mentions; with multi-coding the percentages add to more than 100%, and the table must say so. Ana single-coded the 516 answers into seven themes, one of which, “Nothing, or left blank”, joins two different things: people with nothing to suggest and people who skipped the box.

Percentages are rounded, so they may not add to exactly 100.

Both bases are honest if they are labelled; they answer different questions. Price leads with 84 suggestions, a quarter of them, ahead of range (76) and delivery speed (58). A count of mentions measures how common a theme is, not how strongly people feel or how much it would change their buying, so read the answers behind each code before acting on it. Crossed with segment, delivery speed was raised by 18.0% of subscribers and 8.1% of newsletter-only respondents, which fits the fact that subscribers are the ones waiting for deliveries.

Frequency tables and cross-tabs in Excel

With Ana’s export in A1:H517 (segment in B, the 0–10 score in C, the Cold Brew answer in D), a frequency table is one COUNTIF per answer. With the five answers in J2:J6, =COUNTIF($D$2:$D$517,J2) filled down counts them, and =K2/SUM($K$2:$K$6) turns the counts into percentages of the base. A cross-tab is a grid of COUNTIFS with mixed references, as in B03: with segments down J3:J6 and answers across K2:O2, =COUNTIFS($B$2:$B$517,$J3,$D$2:$D$517,K$2) in K3 fills across and down, and =K3/SUM($K3:$O3) gives row percentages.

A PivotTable builds the same table with segment in Rows, the Cold Brew answer in Columns and a count in Values, shown as % of Row Total. Formulas suit a fixed report layout that must update by itself; a PivotTable suits trying many crosses quickly. With formulas, check that the counts add to the base: a total of 515 points to an answer spelled differently from the five labels, one with a trailing space perhaps. A PivotTable counts every record, so there the same problem shows up as an extra row or column; either way it is a cleaning job.

Rounded percentages needn’t add to exactly 100: Q2’s five shares add to 100.1%. Don’t force them; a note that percentages may not add to 100 because of rounding is the convention. Give percentages to whole numbers or one decimal place, and never to more places than the base supports: with 27 respondents each person is 3.7 points, so 51.9% suggests a precision the data don’t have.

When neither variable explains the other, as with age group and how people first heard of Lantern, either direction is legitimate, but the table’s title should say which question it answers. Column percentages are sometimes exactly what is wanted: a sales planner deciding where to send free samples needs to know where the likely buyers are, and that is the column view. The error is to calculate one and read it as the other.

Top-box scores and the mean of a scale

Q2’s answers are an ordinal variable: “probably” lies between “definitely” and “might or might not”, but nothing says the steps are equal. Box shares respect that. A mean of the codes 1 to 5 treats the steps as equal, an assumption the question can’t support, so if you report one, report it beside the box shares, not instead of them. The bottom-two-box score, the share who probably or definitely would not buy, is 26.2%. Turning intent into a forecast needs a conversion assumption for each box, which Market sizing covers.

The Net Promoter Score and its margin of error

Give each answer a value of +100 for a promoter, 0 for a passive and −100 for a detractor: the NPS is the mean of those values. Its standard error follows from their variance, which depends only on the shares of promoters and detractors (the formula cards). For Ana, $100\sqrt{(0.459 + 0.184 - 0.275^2) \div 516}$ $\approx 3.32$, and the 95% margin is $1.96 \times 3.32 \approx 6.5$ points. That is half as wide again as the margin for the promoters’ share alone, ±4.3 points, because the NPS is the difference of two shares that move against each other.

Rocks (2016) compared interval methods for the NPS and found that this simple (Wald) interval is too narrow, covering the true score less often than 95% of the time even at 300 answers (he also found an iterative score interval performs well). He recommends an adjusted version that adds 3/4 to the promoter and detractor counts and 3/2 to the passive count before applying the same formula. At Ana’s 516 answers the adjustment hardly matters (+27.4 ± 6.5); for the 27 workshop participants it moves the interval from +11.2 to +70.2 to +8.6 to +64.7. On bases that small, use the adjusted interval, or better, don’t report the score at all (see Small bases).

Two independent groups’ scores are compared through the standard error of their difference, $\sqrt{\text{SE}_1^2 + \text{SE}_2^2}$. Subscribers (+61.8, SE 7.20) and newsletter-only respondents (+7.1, SE 4.28) differ by 54.7 points with a standard error of 8.37, so z ≈ 6.5: far beyond chance. Two waves of a tracking survey are independent samples too, so the same calculation shows whether a change between waves is larger than chance; In practice shows why a few points usually aren’t.

Reichheld argued that this one question usually predicted growth better than others, and that in most of the industries he studied the share of enthusiastic customers tracked growth relative to competitors. Keiningham and colleagues (2007), with longitudinal data from 21 Norwegian firms, failed to replicate its claimed superiority over other measures of customer satisfaction and loyalty. The score also throws information away: a 0 and a 6 are both detractors, and half promoters with half detractors scores 0, the same as everyone a passive. Use it as one tracked indicator, with its margin of error, beside the distribution of scores and the reasons people give. Net Promoter Score is a service mark of Bain & Company, NICE Systems and Fred Reichheld.

Weighted results in Excel

A weighted share is $\sum w_i y_i \div \sum w_i$, where $y_i$ is 1 for a respondent who counts (a promoter, say) and 0 otherwise, and $w_i$ is the respondent’s weight: a SUMPRODUCT divided by a SUM. With each respondent’s weight in column I, the weighted top-two-box score is =SUMPRODUCT(I2:I517,(D2:D517="Definitely would buy")+(D2:D517="Probably would buy"))/SUM(I2:I517), and the weighted NPS is =100*(SUMPRODUCT(I2:I517*(C2:C517>=9))-SUMPRODUCT(I2:I517*(C2:C517<=6)))/SUM(I2:I517). Every respondent in a segment carries the same weight, so a four-row table gives the same answers: $\sum N_h \hat{p}_h \div N$, the segments’ shares weighted by their list sizes.

Report a weighted percentage with its unweighted base, the number of people it rests on, and a note: “Weighted to the mailing list’s segment sizes; unweighted base 516.” Unequal weights reduce the effective sample size: Kish’s effective n is 469 (Survey error), which gives a quick margin for the weighted NPS of +20.4 of about ±6.8 points. The stratified standard error of Sampling methods, which also credits the stratification, gives ±6.3: use Kish’s figure as a quick check and the design’s own standard error when it matters. Comparisons between segments don’t need weights, because within a segment every respondent carries the same weight.

Comparing groups

Differences between segments are tested with B04’s methods. Subscribers’ top-two-box score of 61.8% against newsletter-only respondents’ 31.0% gives a two-proportion z test statistic of 5.25 (p < 0.0001) and a 95% interval for the gap of 19.4 to 42.2 points. Subscribers against other shop customers, 61.8% against 55.3%, gives z = 0.91 and p = 0.37, with an interval from −7.5 to +20.4 points: on these bases the survey can’t tell the two apart.

To test all four segments at once, use the chi-square test of independence. The full 4 × 5 table fails its condition: the expected count of workshop participants who “definitely would not” buy is 2.56, below 5. Merging the answers into the top two boxes against the rest leaves a 4 × 2 table whose smallest expected count is 11.4, and gives χ² = 37.7 with 3 degrees of freedom, p < 0.0001 and Cramér’s V = 0.27: intent clearly differs by segment.

Three cautions. B04’s tests assume simple random samples; each segment is a random sample of its stratum, so comparisons between segments stand, but an interval for a weighted all-respondent figure needs the stratified standard error from Sampling methods. The tests speak only about the people who replied: if keen newsletter readers replied more readily than indifferent ones, the gap the test confirms may be partly a gap in who answered. And twenty cross-tabs tested at the 5% level will produce about one “significant” difference by chance, the multiple testing problem, so choose the few comparisons that matter before looking.

The mean, the median and skewed counts

Q3 asked how many cups of coffee each person bought away from home last week. The answers run from 0 to 20 with a long right tail: 60 people (11.6%) bought none, the median is 3 and the mean 3.9, pulled up by a few heavy buyers (the standard deviation is 3.4). In Excel, =MEDIAN(G2:G517) and =AVERAGE(G2:G517) give the two. For one segment, =AVERAGEIFS(G2:G517,B2:B517,J2) gives the mean, and =MEDIAN(IF(B2:B517=J2,G2:G517)) or =MEDIAN(FILTER(G2:G517,B2:B517=J2)) the median, since Excel has no MEDIANIFS. In Excel 2019 and earlier, enter =MEDIAN(IF(…)) with Ctrl+Shift+Enter; FILTER needs Excel 2021, Excel 2024 or Microsoft 365.

All four segments have a median of 3 cups, while their means run from 3.7 to 4.0: the differences between the means come from a few heavy buyers, not from the typical respondent. Report the median for a typical customer and the mean when you need a total, because the mean times the number of people gives the cups bought. A recall question about last week also carries measurement error, as Survey error explained, so neither figure deserves more than one decimal place.

Small bases

Workshop participants returned 27 completes. Their NPS is +40.7 with a margin of ±29.5 points, from about +11 to +70, and their top-two-box score is 51.9% with a margin of ±18.8 points. Figures like these invite over-reading: “workshop participants are less enthusiastic than subscribers” isn’t supported when the two intervals overlap this much. Set a minimum base before the analysis and apply it to every table. One published standard, from the US National Center for Health Statistics, withholds among other things any proportion whose sample size (or effective sample size) is under 30, or whose 95% Clopper–Pearson confidence interval is 30 percentage points wide or more (Parker et al., 2017); Ana adopts the first rule (fictional), so workshop participants count in every all-respondent figure but get no figure of their own.