Customer satisfaction survey data analysis: scores and gaps

Customer satisfaction survey data analysis compares valid numeric answers by customer segment, reporting period, and service team. Define who received the survey, which answers count, and what qualifies as satisfied before calculating a score. Keep response coverage beside the result so missing answers do not disappear.

1. Define the survey population and scoring rule

An illustrative US equipment distributor sends the same post-service question during August and September 2026. The question uses integer answers from one through five. Four and five mean satisfied; one through three do not meet that threshold.

Each invitation is eligible, delivered, and unique. The company keeps at most one accepted submission per invitation, using a written rule for corrections. Period means invitation month, with the same response deadline after invitation in both periods. Segment and service team come from the interaction that triggered the invitation.

Keep wording, scale, and question placement consistent. Pew Research Center explains that measuring survey change requires the same wording and sensitivity to question context. If a question changes, start a separately labeled comparison rather than silently extending the old trend.

This analysis uses structured numeric answers. It does not classify open comments or infer sentiment from text. Purchase behavior belongs in a separate customer transaction data analysis, joined only through a verified business key when that comparison is needed.

2. Retain invitations and validate responses

Use two exports: an invitation register and the accepted submissions. The register preserves customers who never submitted anything. Join submissions to unique invitation IDs without dropping unmatched invitations, and reconcile the invitation count afterward.

The response sheet needs invitation ID, invitation period, segment, team, and raw score. If the raw score is in E2, this Excel helper retains only numeric integers on the approved scale:

=IF(ISNUMBER(E2),IF(AND(E2=INT(E2),E2>=1,E2<=5),E2,""),"")

Put that in F2 and fill down. A text value that looks numeric should be investigated and converted only under an explicit cleaning rule. Preserve the original value. Record separate exclusion reasons for a blank, an out-of-range answer, a duplicate, and an unmatched invitation.

Do not let the helper erase the audit trail. Blank and invalid counts must remain available even though neither contributes a numeric score.

3. Build the score distribution

The accepted submissions below contain no duplicate or unmatched invitations. Blank means the customer submitted the questionnaire without answering this question; invalid means a submitted score outside the allowed integer scale.

There are 80 valid scored answers, eight blanks, and two invalid submissions: 80 + 8 + 2 = 90 accepted submissions. The remaining 160 - 90 = 70 invitations produced no submission by the deadline.

These are different missing-data situations. A questionnaire submission is not necessarily a valid answer to the satisfaction question.

4. Calculate satisfaction, mean score, and coverage

Satisfaction rate = Answers of four or five / Valid scored answers

Mean score = Sum of numeric score points / Valid scored answers

Valid-answer response rate = Valid scored answers / Eligible delivered invitations

Use the distribution table as A1:K6, with the four group rows in rows 2 through 5. In L2, calculate valid answers; in M2, satisfied answers; in N2, mean score; in O2, satisfaction; and in P2, valid-answer response rate:

=SUM(E2:I2)
=SUM(H2:I2)
=IF(L2=0,"",(E2+2*F2+3*G2+4*H2+5*I2)/L2)
=IF(L2=0,"",M2/L2)
=IF(D2=0,"",L2/D2)

Fill down through row 5. The resulting table includes score points so the mean can be rebuilt. Means and percentages are rounded to two decimal places.

For the first row, points are 2 × 1 + 2 × 2 + 4 × 3 + 8 × 4 + 4 × 5 = 70. Therefore mean score is 70 / 20 = 3.50 and satisfaction is (8 + 4) / 20 = 60.00%.

If you summarize validated response rows directly, Microsoft documents COUNTIFS for counting records that meet several conditions. For example, count valid August, Standard, East scores in Responses columns B, C, D, and F:

=COUNTIFS(Responses!B:B,"August",Responses!C:C,"Standard",Responses!D:D,"East",Responses!F:F,">=1",Responses!F:F,"<=5")

Microsoft documents AVERAGEIFS for the corresponding conditional arithmetic mean:

=AVERAGEIFS(Responses!F:F,Responses!B:B,"August",Responses!C:C,"Standard",Responses!D:D,"East",Responses!F:F,">=1",Responses!F:F,"<=5")

These formulas assume one reporting year in the filtered sheet. Use a year-month period key when multiple years are present. Covirage is not affiliated with Microsoft or Google.

5. Reconcile totals and interpret the gaps

The score distribution proves the total: 3 + 6 + 14 + 33 + 24 = 80 valid answers. Points are 3 + 12 + 42 + 132 + 120 = 309, and satisfied answers are 33 + 24 = 57. Thus overall mean is 309 / 80 = 3.86 and satisfaction is 57 / 80 = 71.25%.

Never average displayed group percentages to calculate the total. For a fuller explanation of preserving denominators, see weighted averages in Excel.

August satisfaction is (12 + 12) / (20 + 15) = 68.57%. September is (17 + 16) / (25 + 20) = 73.33%. The difference is 4.76 percentage points, calculated before rounding.

Standard satisfaction rises from 60.00% to 68.00%, while Premium stays at 80.00%. Premium's mean nevertheless moves from 4.20 to 4.15 because the distribution across score categories changes. The mean treats adjacent score steps as equal numerical intervals; show the distribution and threshold rate beside it.

Do not rank East against West from these rows. Team and segment are perfectly aligned, so the comparison cannot separate team differences from segment differences. Equal response rates also do not prove respondents represent every invited customer. Record these limits and use the figures to select records for review. For the subsequent decision workflow, see using data to improve customer experience.

Where it goes wrong

• Counting all submissions as valid scores. This export has 90 submissions but only 80 valid answers. Report both quantities and label the response definition.
• Blank answers become zero. Zero is outside this scale and would lower the mean using an invented observation.
• Duplicate invitations or answers survive. Repeated submissions can give one interaction extra weight. Retain a documented accepted-answer rule.
• Mix changes look like performance changes. Compare the same segments and service contexts before attributing a period movement to better service.
• Rounding happens too early. Calculate totals and gaps from counts and unrounded values, then round the displayed result.

Analyze your own scored survey export in Covirage

Upload your invitation and numeric-response summaries, or bring a Google Sheet with approved scoring and exclusion rules. Covirage maps the columns; its tools compute and check the comparisons, and the external AI model explains the results with every figure traced to its rows. Create a free Covirage account and bring a sample of your own data.

See self-service analytics with Covirage for the supporting context.