OPERATIONAL ANALYTICS &
TARGETED QUALITY IMPROVEMENT

A. Executive Snapshot

B. Executive Summary

This project is not a reporting project with a training add-on. It is one chain in two halves: a reporting loop that turns fragmented operational data into the same-morning management picture, and a learning loop that turns the errors visible in that picture into rule-level coaching, a re-test, and a closed gap. The automation is the enabler. The value is that the distance between a signal and a decision collapsed from days of manual collation and generic refresher training to one morning's data and one prepared one-to-one.

The reporting problem was not exotic. Four client feeds, four operating stages, and a daily demand for inflow, TAT/SLA, productivity and error views meant that a single report could require several sources to be pulled and reconciled by hand before a manager could read it. The manual cycle consumed a full analyst day per operating day for report preparation, and a comparable effort for the assessment work that followed it. Everything was assembled by a person, so the picture was only as good as the day it was produced — and the analysis was a by-product of formatting rather than a designed step.

The more interesting problem was underneath. An error report answers "who made mistakes, and in which field?" That is enough to rank people and to schedule a refresher — and that is exactly where most quality programmes stop. But a field is not a rule. Qualification Attained alone contains ten to fifteen distinct rules, and telling a manager that someone is weak in "Qualification Attained" gives that manager nothing to open the coaching session with. So I built a question bank of roughly 200 four-option items directly from the operating rules, generated the daily 10-question assessment from it, and read the answer patterns per performer. A recurring error in a field plus a specific rule missed in the assessment produces a defensible coaching agenda: this performer, this field, this rule, this scenario — and a re-test one to two weeks later shows whether the gap actually closed.

The whole thing runs on Google Apps Script, Google Sheets, Google Forms and the company case feeds, with AI used where it genuinely earns its place: drafting the question bank from the rule set, reading thousands of responses for patterns, and accelerating the code. Every AI output was reviewed by me against the source rules before it reached a performer or a manager. The result I can defend is the operating one: the manual reporting-and-assessment cycle is gone, a management view arrives the same morning with a diagnosis attached to it, and the manual effort that used to sit behind that cycle is estimated at about a full analyst day per operating day. The quality numbers improved, but several initiatives ran in parallel, so the case study states that as reported and shared rather than as a claim for this project alone.

C. Business Context and Problem Definition

C.1 What leadership actually asked for

Four requirements arrived from different directions and were consolidated into one daily reporting suite:

• Daily inflow — how many cases arrived, by client and by stage, so that capacity and the queue can be judged at a glance.
• Client TAT and SLA — how long cases are actually taking against each client's contractual turnaround expectation, per stage.
• Performer productivity and TAT — monthly-to-date case counts per workstream with the corresponding average TAT/SLA, so that output and pace can be assessed together rather than separately.
• Errors and quality — who is producing errors, in which field, at what rate against their own production, and what that means for the client's quality percentage.

A team leader's underlying question was simpler than any of the four: are we on track, and if not, exactly who needs what? Every design decision below is judged against that question, not against the elegance of the output.

C.2 Why the previous way of working did not answer it

C.3 The problem underneath the problem

The reporting requirement could be met with effort. The quality requirement could not, because of a category error in the original error report. A field such as Applicant Name (as per passport), Qualification Attained or Qualification Confirmed Date is a container, not a rule: it holds ten to fifteen independent rules, each with its own condition, and a performer can be reliable on twelve of them and wrong on the thirteenth. Field-level error counts cannot separate that case, so they cannot target it. That is the gap the assessment loop was designed to close, and it is why the two loops in this project are one project: without the reporting layer there is no error signal to diagnose, and without the diagnostic layer the reporting layer only describes the past.

D. Data Discovery and Root Cause Analysis

D.1 What the data actually looked like

No endpoint, credential or internal link is reproduced here; the source names are generic descriptions of the feeds used.

D.2 Root cause analysis of the reporting problem

Each cause below is classified by how it was established. Only causes that the operating record supports are called confirmed.

D.3 The analysis chain used to move from symptom to cause

1. Symptom: recurring errors in a named field, visible in the error views.
2. Pattern: the same field concentrates errors for the same performer while their other fields stay clean — which rules out general carelessness and points to a specific rule.
3. Hypothesis: the performer does not own a particular rule of that field (for example one qualification rule out of fifteen).
4. Test: the rule is converted into a targeted question and placed in that performer's daily form; a wrong answer makes the hypothesis observable within one working day.
5. Action: the finding goes to the manager as a one-to-one agenda item, and the same question returns after one to two weeks to test retention.

D.4 How the project maps to DMAIC

E. Solution Design and AI-Directed Development

E.1 One routine, many outputs

Parts of the reporting capability already existed in the operation. The work described here consolidated the separate feeds into one scheduled routine that does the same five things every day — fetch, normalise, compute, format and distribute — added the views leadership and managers were missing, and connected that output to the assessment loop so that it ends in an action rather than a number. The design decision that mattered most was the separation of collection from delivery: the outputs differ per audience, but the underlying calculation happens once, so a figure can never disagree between the chat summary, the email and the slides.

E.2 Hangouts delivery — the two standing tables

The team chat carries the morning view. Two tables are posted, both small enough to read on a phone: how much came in, and how long the work is taking against each client’s expectation.

Table 1 · Inflow report (posted daily)

Synthetic values. The live table is generated from the case feed and posts automatically each morning. Tables 1–13 in this document reproduce the reporting objects the automation produces; the other tables here are analysis tables (data sources, root causes, controls, risks, tests, results and skills).

Table 2 · Validation stage TAT report (period average per client)

Synthetic values. The average is computed over the completed validation cases of the period; the expectation column is the contractual turnaround used as the comparison reference.

E.3 Daily productivity email

Table 3 · Validation and insufficiency productivity

Synthetic names and values. One row per performer; a paired count-and-time column for each workstream. Insufficiency initiations cover the operation’s first- and second-level queues and are reported as one workstream column, with the average initiation time beside the count.

Table 4 · Error-correction (EC) productivity

EC cases are cases returned from verification or quality because a field was entered incorrectly, corrected, and returned to the stage they came from.

Table 5 · Client TAT overview

Synthetic values. The client view is what leadership reads: it pairs each client’s workload with the time the operation is actually taking.

E.4 Daily error and quality email

The second email separates when an error was received from when the work was done. That distinction is the whole reason the email has three error views plus two client views: a case validated in one month and returned in the next would otherwise distort both months and hide a real trend.

Table 6 · Errors on recently validated cases (last 7 days)

Synthetic values. The error percentage is the performer’s error count as a share of their own production in the same window, so a heavy producer is not mistaken for a careless one.

Table 7 · Errors on this month’s production (validation date in the current month)

Synthetic values. Only cases actually validated inside the month appear here.

Table 8 · Total error volume received this month (all validation dates)

Synthetic values. The gap between Table 8 and Table 7 is the late-returned population — for example, H. Saeed has four errors on cases that were validated in earlier months, which Table 7 correctly excludes.

Table 9 · Monthly inflow and quality overview (counts)

Table 10 · Monthly quality percentage

Synthetic values. Client quality for the same month (1 − (rejected + error EC cases) ÷ cases validated) is: Client A 97.9% · Client B 98.4% · Client C 98.3% · Client D 97.3% — team 98.3% against a 98% target. Two clients sit marginally below target; that is the trigger for targeted coaching, not for a blanket intervention. The validated-production denominator comes from the monthly inflow table in the daily summary.

E.5 Auto-generated daily summary (Google Slides)

A short summary deck is generated and shared each day with the same figures that drive the two emails, so the leadership view and the working view can never drift apart.

Table 11 · Auto-generated daily summary

E.6 The assessment loop

All four workstreams share one rule base: a performer who knows the rules can work any stage, and the same qualification rule is judged the same way in Validation and in Error Correction. That made a single question bank possible instead of four.

• The bank. Roughly 200 four-option questions were drafted with AI directly from the rule set, then reviewed item by item against the source rules by me. I added and reworded items for newly introduced or updated rules, rare scenarios, and recurring edge cases that a performer might forget between occurrences. Every item carries the correct answer, and no item reaches a performer unreviewed.
• Distribution. A daily trigger checks the roster feed — leave, special tasks and off-days — and issues the form only to the performers who are working. Nobody is tested on a day off, and no one is tested while on leave.
• Collection. The form is a Google Form; responses land in a tab of the same sheet that holds the bank, so the question and the answer stay in one place.
• Analysis. After about two weeks a performer has accumulated roughly ten assessments, or around a hundred answers. Across ~80 performers that is ~8,000 responses, which is where AI-assisted reading becomes necessary rather than convenient.
• Control. Items a performer got wrong return in the form one to two weeks later. A repeated miss is now visible, so the gap can be closed or escalated instead of quietly persisting.

Table 12 · Question-bank structure (illustrative rows)

Synthetic questions written in the style of the bank. The live sheet holds the full set of ~200 items with the same column structure.

Table 13 · Assessment result, per performer (illustrative)

Synthetic values. Two performers sit in a consistently strong band; they were surfaced as candidate peer coaches, and the decision to use them remained with the managers.

E.7 AI-directed development, and what I refused to accept

AI was used as a drafting and acceleration layer, in three places: generating candidate questions from the rule documents, generating and iterating the Apps Script that does the fetching, computing and distribution, and reading thousands of assessment responses for patterns. In each case the output was treated as a proposal to be tested, not as a result to be shipped.

F. From Field-Level Error to Rule-Level Coaching

This is the part of the project that changes what a manager does on Monday morning. The worked example below is illustrative and uses synthetic data, but it is the real shape of the finding.

Worked example (illustrative)

The same mechanism works in the opposite direction. Two performers in the illustration score consistently above 90% with no material weakness, which makes them credible peer coaches; the report surfaces them, and the decision to use them stays with the managers. That keeps the identification factual and the judgement where it belongs.

G. Six Sigma Framing and Preventive Controls

The improvement was run as a DMAIC cycle (defined in Section D.4) with a deliberate design intent: prevent the error instead of correcting it after the fact. The reporting side of the project is corrective by nature — it shows what went wrong after a case came back. The assessment side is preventive, and it is preventive because of a small number of controls that sit in the path of the work rather than beside it.

Controls that are implemented

Controls that are proposed, not yet in place

H. Implementation, Ownership, Adoption and Risk

Adoption: how the routine became the operating standard

Three things decided whether this design survived contact with a busy operation, and all three were deliberate. First, the performer cost was capped at about fifteen minutes a day and the forms were suppressed for anyone on leave, a special task or an off-day, so the routine never punished absence. Second, the findings were handed to managers in a form they could use immediately — a named rule, the failing option and the rule text — rather than a dashboard they had to interpret. Third, the re-ask step made a non-action visible: if a rule is missed again after coaching, that gap shows up in the next cycle instead of disappearing into a training log. I did not run a formal change-management programme; the adoption was driven by delivering a usable output to the people who had to act on it, and by keeping the cost to performers lower than the value they got from it.

Risk register

I. Testing and Validation

Two different things were validated in this project, and they deserve to be stated separately: the correctness of the automation, and the validity of the question bank. Neither was validated by a formal test department; both were validated by the owner against explicit acceptance conditions, and the conditions are recorded here rather than left implicit.

Two items are explicitly not validated, and I prefer to name them rather than let a reader assume coverage: the causal effect of the assessment on production quality, and the long-term behaviour of the question bank under continuous rule change. Both are listed in the roadmap.

J. Business Impact and Measurement Limitations

Outcomes, in order of evidential strength

Results and measurement status

KPI framework used by the reporting suite

Before and after

Limitations, stated plainly

• The 8-hour figure is an owner estimate from an observed manual cycle. No time study was performed and it has not been timed by an independent party, so it is published as an estimate of avoidable effort, not as a cash saving, a headcount saving or an audited result.
• Parts of the reporting capability existed in the operation before this work. The contribution claimed here is the consolidation of the feeds into one routine, the management views that were missing, the rule-level diagnostic layer and the coaching loop built on top of them — not the creation of the reporting capability from nothing.
• The quality movement is real as reported but is not attributable to this project in isolation. Three initiatives ran in parallel, and no controlled comparison was performed.
• The link between an assessment miss and a production error is a consistent, directly actionable observation — not a statistically isolated effect. It is presented as a prioritised hypothesis, and the re-test makes it falsifiable over time.
• Every table and figure in this document is synthetic and reproduces the structure of the live output, not its values. No company data, client name, case reference, employee record, endpoint or credential is reproduced.
• Calendar dates for individual releases were not recorded, so implementation is described as relative phases rather than a dated timeline.

K. Skills Demonstrated

L. Lessons Learned and Next Improvements

What I would carry into the next project

• Reporting is not analysis. The moment the report and the analysis share the same step, one of them suffers. Separating them was the difference between a daily picture and a daily insight.
• A category is not a knowledge gap. Most error reports stop at the field. Converting the field into rules, and the rules into questions, is what makes training actionable and closable.
• Prevention is cheaper than correction. A daily 15-minute check is a fraction of the cost of a returned case, a re-work cycle and a quality target at risk.
• Discipline the dates. Separating the date an error was received from the date the work was done was a small design decision that changed every conclusion drawn from the error views.
• State the trade-offs. The 15-to-10 question reduction, the fixed delivery windows and the fixed report set were all choices with costs; naming them is what makes the design defensible to the next owner.
• Protect the performer. A learning routine that feels expensive does not survive a busy week. Fifteen minutes a day, roster-aware, is what made it stick.

What I would do next

• Standardise the baselines: a fixed pre-training and post-training measurement window per performer, so improvement is comparable rather than remembered.
• Log every coaching action with its date, rule and outcome, and link it to the assessment item, so the value of the loop can be measured instead of argued.
• Measure the miss-to-error relationship properly: correlate a performer’s missed rule items with their error categories in the same rule, over a defined period, and report the strength of that relationship.
• Add a control plan to the automation: documented checks, ownership of the script, a fallback when a feed fails, and a change log for the question bank.
• Extend the same loop to the remaining stages and clients, and to a structured onboarding assessment for newly joined performers, where the same rule base already exists.