Customer complaint analytics: find repeat issues and costs

Customer complaint analytics compares issue frequency, resolution time and recorded cost across customer and product groups. Start with the complaint codes your service team already uses, then connect the cases to an activity denominator such as fulfilled orders. Raw complaint counts identify workload; rates identify where complaints are more concentrated.

Choose the complaint cohort and exposure

Write down what qualifies as a complaint before counting it. A request for information may be a service ticket without being a complaint. Keep the dedicated complaint register separate from the broader workload measures in customer care analytics.

For the example, a US equipment supplier selects complaints received in September 2026 and follows their status through September 30, 2026. The product comparison uses fulfilled orders from the same month. This is a period complaint intensity measure: a complaint received in September may concern an earlier order. To measure the incidence on September orders specifically, link complaints to order IDs and use a consistent follow-up window.

Use one case ID per complaint. Retain additional contacts as service activity rather than creating extra complaints for the same unresolved issue. Here, the repeat flag identifies a new case that the service team has already linked to a prior complaint from the same account about the same issue. Preserve that existing label.

Prepare the coded export

The example below is fictional. Put the columns in A:H and the case rows in rows 2:7. All timestamps use the same time zone. An em dash represents an empty closing cell in the spreadsheet, not a typed character.

The costs are recorded handling amounts through the cutoff, including work on the open case. They exclude refunds, lost sales and other economic consequences. Do not call this the complete cost of dissatisfaction.

Calculate resolution time without losing open cases

Elapsed resolution hours = (Closing timestamp − Received timestamp) × 24

In I2, enter the formula below and fill down. Confirm that E and F contain spreadsheet date-time values rather than text. These are elapsed hours, including nights and weekends.

=IF(OR(E2="",F2=""),"",(F2-E2)*24)

There are six complaints, five closed cases and one open case. The mean for closed cases is =SUM(I2:I7)/COUNT(I2:I7), or 128 / 5 = 25.6 hours. Do not divide by all six complaints, because that would assign the unfinished case a zero duration.

Compare product rates and issue costs

The order export gives the exposure column below. Count complaints per product with =COUNTIF($C$2:$C$7,"Pro"), replacing the criterion for Basic. Divide cases by fulfilled orders and multiply by 1,000.

Both products generated the same case count, but Pro's rate is higher because its order base is smaller. The company rate is 6 / 450 × 1,000, calculated from totals. For the denominator principle behind this comparison, see defining the norm from your customer base.

Next, compare the issue codes without mixing closed and open cases:

Means and rates are rounded to one decimal place. AVERAGEIFS calculates an arithmetic mean for cells meeting criteria, while SUMIFS adds values meeting criteria. These formulas return Delivery's mean and recorded cost:

=AVERAGEIFS($I$2:$I$7,$D$2:$D$7,"Delivery")
=SUMIFS($H$2:$H$7,$D$2:$D$7,"Delivery")

Check that issue counts return six, closed counts return five, closed hours return 128 and costs return $600. The repeat share is =SUM(G2:G7)/COUNTA(A2:A7), or 2 / 6 = 33.3%. It describes the supplied flags, not a finding inferred from complaint text.

Assign the investigation

Delivery has the higher closed-case mean and the open case. Ask the operations owner to inspect those case histories before changing staffing or promising faster service. Billing has less elapsed time but includes Harbor's repeat complaint; the billing owner should check whether the underlying issue was corrected.

Carry the account evidence into customer success analytics when service problems affect an ongoing relationship. Keep the case facts visible beside any explanation, as described in customer-service analytics.

Where it goes wrong

• Contacts counted as complaints. Repeated messages can inflate issue frequency without adding distinct cases.
• Closed-only reporting. A lower mean can coexist with an aging unresolved backlog.
• Unmatched exposure. Product complaints divided by company-wide orders hide concentration.
• Changing issue labels. Recoding delivery complaints as general service can create an artificial improvement.

Covirage is not affiliated with Microsoft.

Analyze your own complaint export

Bring your coded complaint register and matching activity totals. 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 and bring a sample of your own data after setting up your business.

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