Blog · How-to guides · Customer service
Customer effort analytics compares recorded survey scores across journey stages and service outcomes. Calculate response coverage, score distributions and stage comparisons from an export, with Excel formulas and checks that keep missing responses visible.
Customer effort analytics compares numeric survey responses by journey stage and recorded service outcome. It shows where customers report friction and whether enough invited customers answered to support a useful comparison. Keep the score distribution beside the average so a summary cannot hide a group with a difficult experience.
Start with the exact question in the export. In this fictional US business-services example, customers rate the statement, "The company made it easy for me to complete this task," from 1, strongly disagree, to 7, strongly agree. Higher scores therefore mean greater reported ease.
The comparison unit is an invited task episode, not a unique customer. A customer may complete more than one task. Use the invitation ID to prevent a duplicate response from counting twice, and preserve the existing journey-stage and outcome labels rather than interpreting free-text comments.
Select invitations sent during September 2026 and accept responses received through September 30, 2026. Keep that cutoff consistent across stages. Broader customer journey analytics examples explain the sequence of activities; this analysis measures the feedback recorded at those activities.
The source file should have one row per invitation, including unanswered invitations. Use columns A:D for invitation ID, stage, recorded outcome and numeric score. Keep blanks in D when a customer did not answer.
This compact view groups the invitation rows but retains every recorded score, so the calculations can be rebuilt. All recorded scores are valid whole numbers on the stated scale.
| Stage | Recorded outcome | Invitations | Recorded scores |
|---|---|---|---|
| Order | Resolved | 10 | 4, 5, 6, 6, 7, 7 |
| Setup | Resolved | 5 | 4, 5, 5, 6 |
| Setup | Unresolved | 5 | 2, 3, 3, 4 |
| Support | Resolved | 5 | 4, 5, 6 |
| Support | Unresolved | 5 | 1, 2, 3 |
| Total | 30 | 20 responses |
An unresolved outcome is the service team's existing status at the survey point. Do not substitute a later closure status when comparing these responses. That would describe a different moment in the experience.
Expand the invitation records into rows 2:31 in the worksheet. In E2, flag a valid numeric, whole-number response on the stated scale. In F2, retain its score for summation. In G2, flag scores of at least 6, the local definition of an easy experience for this review.
=IF(ISNUMBER(D2),IF(AND(D2>=1,D2<=7,MOD(D2,1)=0),1,0),0)
=IF(E2=1,D2,0)
=IF(E2=1,IF(D2>=6,1,0),0)
Fill down through row 31. The zero in F is a summation helper, not a replacement survey score. Only E determines how many responses enter a score denominator. Inspect any nonblank score that fails validation before proceeding.
Response coverage = Valid responses / Invitations
Mean ease score = Sum of valid scores / Valid responses
Easy-experience share = Valid scores of at least 6 / Valid responses
Put a stage label in J2. SUMIFS adds values that meet specified criteria. These formulas return invitations, valid responses and the score sum for that stage:
=COUNTIF($B$2:$B$31,J2)
=SUMIFS($E$2:$E$31,$B$2:$B$31,J2)
=SUMIFS($F$2:$F$31,$B$2:$B$31,J2)
If those results occupy K2:M2, coverage is =IF(K2>0,L2/K2,"") and mean score is =IF(L2>0,M2/L2,""). The easy-experience share is:
=IF(L2>0,SUMIFS($G$2:$G$31,$B$2:$B$31,J2)/L2,"")
| Stage | Invitations | Responses | Coverage | Score sum | Mean ease | Scores at least 6 | Easy share |
|---|---|---|---|---|---|---|---|
| Order | 10 | 6 | 60.0% | 35 | 5.83 | 4 | 66.7% |
| Setup | 10 | 8 | 80.0% | 32 | 4.00 | 1 | 12.5% |
| Support | 10 | 6 | 60.0% | 21 | 3.50 | 1 | 16.7% |
| Total | 30 | 20 | 66.7% | 88 | 4.40 | 6 | 30.0% |
Percentages are rounded to one decimal place and means to two decimals. The total mean is 88 / 20, calculated from responses, rather than an unweighted average of stage means. This follows the same denominator principle used for customer comparisons.
Count each valid score separately. The distribution below must sum to the same response count and score total:
| Score | 1 | 2 | 3 | 4 | 5 | 6 | 7 |
|---|---|---|---|---|---|---|---|
| Response count | 1 | 2 | 3 | 4 | 4 | 4 | 2 |
Put the scores in B40:H40 and counts in B41:H41. SUMPRODUCT sums products of corresponding ranges, giving an independent mean check:
=SUMPRODUCT(B40:H40,B41:H41)/SUM(B41:H41)
The counts sum to 20 and the weighted score sum is 88, reproducing 4.40. Scores of at least 6 total six responses, reproducing the 30.0% easy share.
From the original grouped rows, resolved episodes have 13 responses totaling 70, or a mean of 5.38. Unresolved episodes have seven responses totaling 18, or 2.57. Those totals reconcile to 20 responses and 88 score points. The difference warrants investigation; it does not prove that resolving a case caused the higher score.
The service owner should review Support's low scores alongside unresolved episodes. Use customer care analytics for operating workload and resolution measures rather than asking this survey to explain every service result.
Covirage is not affiliated with Microsoft.
Bring your invitation and numeric-response export with its existing stage and outcome labels. Covirage maps the columns, and its tools compute and check the comparisons; the external AI model explains the results with every figure traced to its rows. Create a free Covirage account, set up your business and bring a sample of your own data.
See self-service analytics with Covirage for the supporting business context.
It depends on the question and scale direction. A higher agreement score for an ease statement means an easier reported experience. A higher score for a question about effort required may mean the opposite. Keep the exact wording and scale labels beside every comparison.
No. An unanswered survey is missing feedback, not a customer rating. Include the invitation in response-coverage calculations but exclude it from score summaries. Preserve a separate flag for invalid responses so missing feedback and data-quality problems can be investigated separately.
No. A score difference can identify an experience worth investigating, but it does not establish a cause of churn. Review the underlying task, service outcome and account context, then track what happens after a specific operational change.