Customer care analytics: find service demand and friction

Customer care analytics finds service demand and friction by comparing tickets, repeated contacts and outcomes for each customer. Start with a coded ticket export, choose a common cutoff, and summarize the same records by customer and reason. A team total can look stable while particular customers need repeated help.

The workflow below answers where follow-up work collects. For the broader measurement set, use KPIs for B2B customer service teams; those definitions should remain consistent with the operating scorecard.

Define the issue, contact and observation window

Request one row per ticket with ticket ID, customer ID, primary reason, contact count, resolved flag and elapsed hours to resolution. Keep opened and resolved timestamps in the source file so elapsed time can be checked. Remove duplicate snapshots of the same ticket before summing.

Decide what counts as a contact. In this example, a contact is a substantive customer interaction, including a reply from the service team. Automated acknowledgments and internal notes are excluded. Contact count is therefore a workload measure, not a count of customer complaints.

A repeated-contact ticket has more than one included interaction. That is a local descriptive measure. It differs from a repeat issue opened after closure, which requires an issue-matching rule and a follow-up window. Do not silently substitute either measure for first contact resolution.

The example is an illustrative US equipment supplier. It covers tickets opened September 1 through September 7, 2026, observed through September 14, 2026. The fixed opening cohort keeps newly opened tickets from continuously entering the denominator. Unresolved tickets remain in the outcome denominator but have no completed resolution duration.

Begin with the coded ticket rows

Put the following headers in A1:F1 and the six tickets in rows 2:7. A resolved flag of 1 means resolved at the cutoff; 0 means still open. Leave F6 genuinely empty, rather than typing the word "blank."

There are six tickets and ten contacts. Three tickets had repeated contacts: T01, T03 and T05. Additional contacts beyond the first total 10 − 6 = 4. This separates the number of affected issues from the amount of follow-up work.

Summarize customer demand with exact formulas

List the four distinct customer IDs in H2:H5. Put ticket count in I, contacts in J, repeated-contact tickets in K and resolved tickets in L.

In I2, count tickets matching the customer. Microsoft documents COUNTIFS as counting rows where all supplied criteria match:

=COUNTIFS($B$2:$B$7,H2)

In J2, add the customer's recorded contacts:

=SUMIFS($D$2:$D$7,$B$2:$B$7,H2)

In K2 and L2, apply the additional conditions, then fill all formulas down through row 5:

=COUNTIFS($B$2:$B$7,H2,$D$2:$D$7,">1")
=COUNTIFS($B$2:$B$7,H2,$E$2:$E$7,1)

C01 generates the most contacts, but C03 has an unresolved issue. Those facts support different actions: review the sequence of C01's delivery interactions, and check who owns C03's open access ticket.

The repeat-contact ticket share is 3 / 6 = 50.0%. The resolved share at cutoff is 5 / 6 = 83.3%, rounded to one decimal place. Calculate them from the totals:

=SUM(K2:K5)/SUM(I2:I5)
=SUM(L2:L5)/SUM(I2:I5)

These are contacted-customer results. Measuring demand per active customer requires an active-customer roster that also includes customers with no tickets. Defining the norm from your own customer base explains why that denominator matters.

Find the reasons behind repeated work

Create a second summary by primary reason using the same formulas, replacing the customer range B2:B7 with the reason range C2:C7. List Delivery, Billing and Access in N2:N4.

For average completed resolution hours, use this formula alongside each reason. AVERAGEIFS averages only cells meeting the criteria, so the resolved flag explicitly excludes the open ticket:

=AVERAGEIFS($F$2:$F$7,$C$2:$C$7,N2,$E$2:$E$7,1)

Covirage is not affiliated with Microsoft.

The total duration is (48 + 4 + 24 + 2 + 6) / 5 = 16.8 hours. It is also (36 × 2 + 5 × 2 + 2 × 1) / 5. Never average the three reason averages without their resolved-ticket weights.

Delivery accounts for five contacts from two tickets, while access still has an open ticket. Access's completed average therefore cannot establish that all access issues are handled quickly. Inspect unresolved age separately before setting priorities.

Reconcile and assign the next action

Customer and reason summaries must each total six tickets, ten contacts and five resolved tickets. This contact check must return zero:

=SUM(J2:J5)-SUM(D2:D7)

The service operations lead owns the ticket definitions and reconciliation. A delivery owner should review the repeated-contact cases for avoidable handoffs or missing information; the access owner should inspect the open issue. The customer service leadership questions provide the wider review agenda.

Use customer journey analytics examples for delays between service and other milestones. A ticket reason alone does not establish what happened elsewhere in the relationship.

Where it goes wrong

• Snapshots counted as new tickets. Retain one record per ticket at the stated cutoff.
• Reason codes counted several times. Choose one primary reason for additive totals; show secondary tags separately.
• Open tickets assigned zero duration. Leave completed duration blank and report unresolved outcomes separately.
• Recent tickets compared with mature cohorts. Use consistent opening periods and follow-up windows.
• Volume treated as blame. Customer complexity, issue severity and channel practices can change contact counts. Review the underlying cases before judging a team or customer.

Do this on your own file in Covirage

Bring a coded ticket export with customer IDs, contact counts and outcomes at a common cutoff. Covirage maps the columns, and its tools compute and check the customer and reason comparisons; the external AI model explains the results with every figure traced to its rows. Create a free Covirage account, then set up your business and bring a sample of your own data.

See Covirage for customer service teams for the supporting business context.