How to Spot Patterns in Complaints, Returns, and Defects With AI

Put your complaint, return and defect logs into one table, agree a short list of cause codes, and have AI code every record against it. Then count codes by month, product, client and team, rank them by cost rather than volume, and test the top two with a structured "five whys". AI finds clusters; people confirm causes.

The step most people skip is the fixed code list. Ask an AI to "find themes" in 200 complaints and it will produce a tidy summary that sounds right and changes every time you ask. Themes you can count, compare month to month and act on only come from codes that are defined once, applied consistently and checked by a person.

Follow me on Instagram@sagnikteaches

Three logs, one table

Most small firms keep complaints in email, returns in a spreadsheet or the returns portal, and defects in project notes or a quality system, if at all. Separately, each looks like a string of one-offs. Together they often point at the same weak spot.

Connect on LinkedInSagnik Bhattacharya

The running example is an illustrative 35-person engineering consultancy that designs building services and also supplies and commissions energy-monitoring equipment for clients. Over 12 months it logged 71 client complaints, 58 device returns and 85 design defects (errors found at internal checking or raised by contractors on site): 214 records in three formats.

Subscribe on YouTube@codingliquids

Before coding anything, map every record into the same columns:

Here is how three raw entries look before and after the mapping (illustrative):

• Email from a client: "Third time the riser drawings have gone out without the latest architect's layout. We need this sorted." Becomes: complaint, Project 2207, client C-031, design team 2, cost 6 hours.
• Returns sheet: "B-series sensor, no reading after 5 months, rooftop plant." Becomes: return, Sensor model B, client C-044, site team, cost $240 replacement.
• Checker's note: "Duct sizes on level 3 don't match revised architect's model rev F." Becomes: defect, Project 2211, design team 2, cost 9 hours.

Replace client names with codes and remove personal details from the description before uploading. Complaint text often contains names, phone numbers and occasionally personal circumstances, none of which the analysis needs. Anonymising client data before it goes into AI covers a quick way to do this.

Write the code list before AI codes anything

A codebook is a list of cause codes, each with a one-line definition and an example. Let AI draft it, then edit it yourself. Give it a random sample of 60 records and ask:

Here are 60 anonymised records from our complaint, return and
defect logs. Propose 8-12 cause codes that would cover at least
90% of them. For each code give: a short name, a one-sentence
definition, one example record ID, and what it should NOT
include (to separate it from the nearest other code).
Include "OTHER - describe" as a final code.
Codes should describe the cause, not the customer's feeling.

The last line matters. Without it, you get codes like "frustration" and "poor experience", which describe how the client felt, not what went wrong. After editing, the consultancy's codebook read like this:

1. COORD: our design didn't match another party's latest information (architect, structural engineer). Not: our own calculation errors.
2. CALC: error in our calculation, sizing or specification.
3. LATE: deliverable issued after the agreed date.
4. COMMS: client not told about a change, delay or decision, or slow to get a reply.
5. SCOPE: disagreement about what we were asked to do.
6. FEE: dispute about an invoice or fee.
7. DOA: device faulty on arrival or at commissioning.
8. FIELD: device failed in service within 12 months.
9. INSTALL: fault caused by our installation or commissioning.
10. NFF: returned device, no fault found.
11. OTHER: describe in a few words.

Add a second, smaller dimension if you can: where the cause sits (our people, our process, our tools, a supplier, client input). It's the dimension that turns a count into a fix.

Coding every record: the tools and the prompt

Three practical routes, depending on where your data lives:

• Upload to ChatGPT or Claude on a business plan and ask it to code the whole file and return it as a spreadsheet. Best for a one-off batch of a few hundred rows.
• Google Sheets has an AI function, written AI("prompt", range), that you can fill down a column. Google's help page on the AI function in Sheets notes that only the first 350 selected cells generate at once, so code in batches, and that results are fixed once inserted rather than recalculating.
• Excel: the COPILOT worksheet function was withdrawn on 14 September 2026, according to Microsoft's support page. Classification now happens through the Copilot pane instead, which works but is less convenient for coding row by row.

Whichever you use, the coding prompt should carry the full codebook and demand a reason:

Code each record using ONLY the codebook below. For each record
return: record_id, code, cause_area, reason (max 15 words,
quoting the record where possible).
If a record fits two codes, choose the one closest to the root
cause and put the other in second_code.
If nothing fits, use OTHER and describe it.
Do not summarise or merge records.

[paste codebook with definitions and "not" rules]

An illustrative slice of the output:

The quoted reason is what makes the output checkable. When a person disagrees with a code, they can see exactly which words the AI relied on.

Check the coding before you count anything

Take 30 records at random and code them yourself without looking at the AI's answer, then compare. In the consultancy's first pass, the partner agreed with 26 of 30 (87%). All four disagreements were between COORD and SCOPE: the AI coded "you didn't include the new plant room" as a coordination error, when the plant room had been added after the fee was agreed, which makes it a scope question.

The fix was one line in the codebook: "If the other party's change came after our appointment and wasn't in our brief, it's SCOPE, not COORD." After recoding, agreement on a fresh sample of 30 rose to 29. Aim for 90% or better before trusting counts; below that, the definitions are too fuzzy and the pattern you find may be an artefact of the coding.

Finding the pattern: rank by cost, then cut the data

Ask the AI (in code, not in prose) for three views of the coded data:

1. A Pareto table: each code's count and cost, sorted, with a running percentage. In the example, COORD (48 records, 22%), FIELD (37, 17%) and LATE (29, 14%) made up 53% of all records. By cost, COORD accounted for 310 hours of rework and FIELD for $14,800 in replacement devices and site visits.
2. A monthly trend for each of the top five codes. LATE ran at one or two a month for nine months, then five, four and six in the last quarter.
3. Cross-tabs of the top codes against product, project, team and client. This is where patterns become causes.

The cross-tabs in the example showed two clear clusters:

• 29 of the 37 FIELD failures were one sensor model, and 22 of those were installed in rooftop plant areas. That points away from "bad batch" and towards "product not suited to exposed locations", which is a specification decision, not a supplier problem.
• 34 of the 48 COORD records came from projects where the architect issued model revisions after the engineering model was fixed for issue. The consultancy had no step for checking incoming revisions before issuing drawings.

The LATE rise had no cluster in the data. The partner supplied the context the data couldn't: a senior designer had left in the spring and the team was a person short. AI can show you when something changed; it usually can't tell you why.

From pattern to root cause: five whys with AI as the interviewer

A cluster is a symptom. The "five whys" technique asks why a problem happened, then why that happened, until you reach something you can change. AI is useful here as a disciplined interviewer that won't accept vague answers:

Act as a facilitator running a five-whys session with me on this
problem: [cluster description with numbers]. Ask ONE "why"
question at a time and wait for my answer. If my answer blames a
person or is vague ("communication", "human error"), ask what in
the process allowed it. Stop when we reach a cause we can change
with a process, tool or specification. Then summarise the chain
and suggest two ways to test whether it's the real cause.

An illustrative chain for the COORD cluster, after the session:

1. Why did drawings not match the architect's layout? Because they were issued from a model that didn't include the architect's latest revision.
2. Why? Because the revision arrived after the model was fixed for issue.
3. Why wasn't it picked up? Because incoming revisions go to the project lead's inbox, and nobody else checks them before issue.
4. Why? Because there's no issue checklist step for "latest information from other parties received and reviewed".
5. Cause to change: add a pre-issue check of all incoming revisions since the model freeze, owned by the checker, not the project lead.

The suggested test: apply the new check on the next ten issues and count COORD defects raised on site over the following two months. If they don't fall, the chain was wrong, and you go back to step three.

The FIELD cluster ran differently, because the chain ended outside the firm. Why did B-series sensors fail on rooftops? Moisture got into the housing. Why? The model's enclosure rating suited indoor plant rooms, and nobody had checked it against exposed locations. So the fix was a specification rule (exposed locations get a different model or a rated enclosure) plus a question to the supplier about the 7 failures that weren't on rooftops. The AI's job in that session was mainly to stop the first answer, "the supplier sent bad units", being accepted without the data behind it.

How the same method reads in other firms

An insurance broker's complaints. An illustrative broker with 1,900 commercial clients logs around 40 complaints a year. Coded by the stage of the client relationship (quote, placement, mid-term change, claim, renewal), the pattern that usually matters is where in the year complaints land and which insurer or product line they involve. If eight of eleven claim-stage complaints involve one insurer's claims process, that's a conversation with the insurer, not a staff training issue. Brokers and other regulated firms usually have their own rules on recording and handling complaints; AI coding sits alongside that process, never instead of it.

A recruitment agency's "returns". For an agency, the equivalent of a returned product is a placement that falls through within the rebate period, when part of the fee is refunded. Code each fall-off by reason (counter-offer accepted, role not as described, candidate performance, client restructure) and cross-tab by consultant and client. A cluster of "role not as described" with two clients says the job brief taken at the start is the weak point, which is fixable at intake.

A physical-goods seller's returns. The reason codes customers pick in a returns portal are unreliable: "changed my mind" often hides "the size guide was wrong". Code the free-text comment, not the drop-down, and compare the two. The gap between them is itself a finding.

Keeping the analysis running every month

One analysis finds this year's problems. A monthly routine catches next year's early. The lightweight version:

• New records are appended to the same table as they're logged, with the same columns.
• Once a month, the AI codes the new records using the same codebook, and a person spot-checks ten of them.
• An alert rule flags any code that is more than 1.5 times its three-month average and has at least five records this month. The second condition stops two records instead of one from setting off alarms.
• Any fix you make gets a date in the table, so you can see whether the code falls afterwards.

Tag complaints as they arrive if you can; tagging and routing support requests with AI uses the same idea at the front door. For survey comments, which need a slightly different codebook, see analysing customer feedback surveys with AI.

How complaint and defect patterns mislead

• Small numbers. Three records rising to six is usually noise. With under about 50 records a year, look at 12-month totals, not monthly swings.
• Only formal complaints get logged. Grumbles in site meetings and phone calls never make it into the table, so "communication" is always under-counted. Ask project leads to log verbal complaints in one line.
• Code drift. Rewording the codebook mid-year changes the counts. Keep a version date on the codebook and recode old records when a definition changes.
• A growing OTHER. If OTHER passes about 10% of records, a new code is hiding in it. Ask the AI to group the OTHER descriptions and propose one.
• Counting instead of costing. Twenty minor complaints about invoice formatting cost less than two design errors that needed site rework. Rank by cost wherever you can estimate it.
• The confident summary. A paragraph saying "clients are frustrated by communication" isn't a finding. If the AI's conclusion doesn't cite codes, counts and record IDs, send it back.

The one page to produce each quarter

If there's one output to aim for, it's a single page each quarter: the top three codes by cost, the cluster behind each, the fix in place and whether the numbers moved. Here is the consultancy's page two quarters after the analysis (illustrative):

That page takes the AI a minute to draft from the coded table and a person ten minutes to correct. It is what turns a complaints log from a record of bad days into a list of things you've fixed.

Further reads

• Can AI Handle Customer Complaints Without Making Them Worse? — Reply to the complaints themselves without making them worse.
• How to Handle Warranty Claims Faster With AI Triage — Speed up the claims that feed your returns log.
• How to Automate Returns and Refunds With Clear AI Rules — Set return rules so the data you log is consistent.
• Why Customers Cancel: How to Run a Churn Analysis With AI — When complaints turn into cancellations, find out why.
• AI Quality Control for Small Manufacturers: Where to Start — Catch defects before they leave the building.
• How to Review AI Call Transcripts for Quality and Compliance — Find the complaints raised on calls that never get logged.
• How to Reply to Negative Reviews With AI: Examples and Pitfalls — Public complaints in reviews need a reply as well as a code.
• What Are Diners Really Saying? Using AI to Analyse Your Reviews — A method for turning months of restaurant reviews into a few counted, checked themes, with a codebook, a tagging prompt and a worked nine-month example.
• How Small Amazon Sellers Use AI for Listings and Reviews — How a small Amazon seller can use AI for listings under the 2026 title rules, analyse reviews for fixes, and grow reviews without breaking policy.
• AI Workflow Automation Examples: 20 Processes to Automate First — Twenty processes ranked by a 25-point score, from inbox sorting to credit packs, each with a worked example, rough costs and what to watch.
• AI Tools and AI Development: The Complete 2026 Guide — the AI hub, including every tutorial in the AI-for-business series.

Sources: Google Docs Editors Help (AI function in Google Sheets, syntax and 350-cell generation limit); Microsoft Support (COPILOT function retired from 14 September 2026, Copilot pane as the alternative).