A. Executive Snapshot

Business problem	Four insufficiency queues — first-level, second-level, applicant responses, and payment checks — were tracked in a sheet assembled by hand from several sources. One compilation took about two hours, twice a day, leaving long visibility gaps under tight, client-specific SLA deadlines.
My role and contribution	I defined the queue rules and SLA clock logic, designed the workbook structure, alerts, analytics, and status controls, and directed AI-assisted development of the Apps Script workflow. I tested, corrected, and maintained the solution.
Solution	A scheduled workflow that filters the company-wide case feed to the four in-scope clients, routes each case to its correct queue, refreshes the workbook every 30 minutes around the clock, posts queue summaries and SLA reminders to the team chat, and reconciles performer statuses against the source feed.
Methods and tools	Google Sheets, Google Apps Script, the company case-management API, a shared payment inbox, team chat (Hangouts/Chat), and AI-assisted coding under my direction.
Project status	Live since January 2026; major upgrade in August 2026 (status reconciliation, first/second-level split, full analytics tab, chat summaries); continuous maintenance since.
Verified outcomes	24/7 30-minute refresh (up to 48 automated runs a day); formula-driven analytics; SLA reminders one hour before each deadline; status reconciliation. Manual effort figures are owner-reported estimates; no audited SLA percentage is claimed.
Limitations	No measured before/after SLA-compliance figure; the strongest outcome signal is observational (absence from the company breach report). Client names are anonymized; mockups use hypothetical example data.

B. Executive Summary

After a case passed initial validation, it often still needed a follow-up action: a missing document, a request returned from verification or a later stage, an applicant upload, or a payment shortfall. Each follow-up type had its own deadline rules, and missing one was operationally costly. Yet the only working view was a spreadsheet that a performer assembled by hand from several data sources — roughly two hours per compilation, twice a day.

The problem became urgent when the clients tightened the rules. First-level insufficiency had to be completed within an overall 12 hours measured from the original validation case start; second-level insufficiency had 24 hours from its own entry; applicant responses had 12 hours for the two revised clients and 24 hours for the others, measured from the applicant upload. With a view that refreshed twice daily, a case could sit unnoticed until it was already late.

I replaced the manual assembly with a scheduled workflow: a Google Apps Script job that reads the company case feed every 30 minutes, keeps only the four in-scope clients, routes cases into the correct queue tab, computes elapsed time from the right clock for each queue, and posts queue summaries and deadline reminders to the team chat. The analytics tab is formula-driven, so it recalculates on its own. A hidden edit timestamp with a 40-minute reconciliation rule protects correct performer input while clearing statuses that no longer match the source. The result is a working view that is always current, with deadlines made visible before they pass — instead of after.

C. Business Context and Problem Definition

The four queues

Insufficiency work split into four distinct queues, and treating them as one was dangerous because each had its own clock:

• First-level insufficiency — a missing document found during validation, for which the team requests the document from the applicant. Under the revised rule, the two revised clients required completion within an overall 12 hours measured from the original case start in validation, not from the insufficiency entry.
• Second-level insufficiency — a case returned from verification or a later stage. Its 24-hour window starts the moment the case enters second-level insufficiency.
• Applicant responses — the applicant uploads a document the team requested. The two revised clients required 12 hours from the upload; the remaining two clients kept 24-hour requirements.
• Payment checks — payment follow-ups such as a missing fee or an underpayment, tracked with a 24-hour window from the payment date.

The manual baseline

A performer assembled the working sheet by pulling case data from several sources, populating the tabs, and organizing the columns. One compilation took about two hours. The sheet was prepared roughly twice a day — about four staff-hours daily — which meant the visible picture was always hours old.

The volume made staleness costly

On a typical day the queues carried roughly 500–1,000 applicant-response cases, 300–600 first- and second-level insufficiency cases, and up to 5–10 payment checks. With a 12-hour overall clock on first-level work, a sheet refreshed twice a day could show a case with only an hour left — or hide it until it was already late. The practical symptom: the company analytics team sent a six-hourly report of cases that had breached their SLA, and the team queues appeared in it regularly.

D. Data Discovery and Root Cause Analysis

The visible symptom was breach-report appearances. Working backward from it, the contributing factors were clear:

Symptom	Contributing factor	How the solution addresses it
Cases appearing in the six-hourly SLA-breach report	Working sheet refreshed only twice a day	Refresh every 30 minutes, 24/7
Hours of assembly work per update	Data pulled and combined by hand from several sources	Script filters and routes cases automatically
Wrong sense of elapsed time	First-level clock started at insufficiency entry instead of the validation case start	Clock corrected to the client definition
Statuses disagreed with the source system	Performer errors had no check against the live feed	Hidden edit timestamp + 40-minute reconciliation rule
Leads could not see where work was building up	No live workload view per client, queue, and performer	Formula-driven analytics tab and chat summaries

The most important finding was the clock-origin error in the first-level logic. An earlier version of the elapsed-time calculation started the timer when the case entered the insufficiency queue, which understated elapsed time against the client overall 12-hour definition. Correcting the clock — starting from the original validation case start — is what made the sheet trustworthy for deadline decisions. The contributing factors above are observed operating problems; the breach-report observation is reported and not independently audited.

E. Solution Design and AI-Directed Development

I translated the operating rules into a precise specification before any code existed:

1. Filter and classify. Keep only the four in-scope clients, then route each case by queue type and level so the same case never appears as multiple queue entries.
2. Apply the right clock. Compute elapsed time per queue from its own start point — validation case start for first level, insufficiency entry for second level, upload time for applicant responses.
3. Populate the workbook. Write barcode, performer, start date, elapsed time, and level into the matching tab; leave status blank for performers to fill in.
4. Refresh and reconcile. Run on a 30-minute trigger and check performer statuses against the source feed.
5. Publish visibility. Let formula-based analytics recalculate and post queue summaries and SLA reminders to the team chat.

I designed this specification myself, then used AI as an implementation partner: I described the required behavior, reviewed the generated Apps Script code, tested it against the live data, identified problems, and directed corrections until the behavior matched the specification. The iterative improvements came out of that loop:

• Correcting the first-level clock. Testing showed the timer was using the insufficiency entry time; I changed the logic to use the validation case start.
• Replacing four payment pulls with one inbox. Payment data previously required four separate source pulls. I noticed that every completed payment arrives at a shared email inbox with the barcode and receipt attached, so I directed the script to check that inbox on every run and add new barcodes to the payment tab — removing four slow dependencies.
• Status reconciliation. I added a hidden column that stamps the exact edit time whenever a status changes. On the next run, if the case is still in the source unfinished list and the edit is older than 40 minutes — the source refreshes every 30 minutes, the run itself takes only a minute or two, and a further 10-minute buffer absorbs occasional script failures or source update delays — the script clears the status back to blank, the state that means not done. The buffer protects work that was genuinely just completed.
• Formula-driven analytics. The analytics tab uses formulas rather than pivot tables, so the summary tables update by themselves whenever the source tabs change.

The operational tab

The analytics tab

F. Project Management and Implementation

The work went live in January 2026 and has run continuously since, with the largest upgrade in August 2026 — adding the status-correction logic, the first/second-level split, the full analytics tab, and the chat summaries.

The key implementation decisions were about timing and safety. The 30-minute cadence matches the source feed refresh rate, so runs never work from data older than one cycle. The 40-minute reconciliation threshold gives the source time to refresh, covers the run itself, and adds a safety buffer for occasional script failures or source update delays, so the control catches stale statuses without deleting correct work. Reminders fire one hour before each queue's own deadline and repeat with the cycle, so an approaching case stays visible until it is handled.

Risks were managed by design: the workflow runs 24/7 because the operation runs 24/7; the payment inbox dependency replaced four slower dependencies; and every rule that could silently mislead — clock origins, grace windows, bucket definitions — was made explicit before automation.

G. Preventive Improvement

The solution builds prevention into the process rather than relying on people to remember:

• Mistake-proofing statuses. The hidden-timestamp reconciliation is a control that detects and reverses a specific human error — a wrong status left on an unfinished case — instead of asking performers to be perfect.
• Standardizing the clock. Centralizing each queue deadline rule in one place removes per-person interpretation of when a timer should start.
• Removing the manual reporting step. Formula-driven analytics mean workload views cannot go stale because someone forgot to refresh a pivot table.
• Making deadlines unmissable. Yellow and red highlighting on barcodes, plus chat reminders carrying the barcode, performer, and elapsed time, turn checking the sheet into the sheet telling you.

These controls are implemented. Recommended future controls — execution and exception logs, and a formal test set for the SLA calculations — are part of the roadmap, not claimed as built.

H. Testing and Validation

Testing was continuous and behavior-based. I verified that cases landed in the correct tab and level, that elapsed time matched each queue's clock after the first-level correction, that the chat summary format and buckets were readable, that the reconciliation rule waited through the 40-minute window before clearing, and that payment barcodes were captured from the inbox. Each of these was checked against the live environment and corrected where it failed.

The strongest available outcome evidence is observational: before the workflow, the team's cases appeared regularly in the company six-hourly SLA-breach report; since the workflow became routine, no case from these queues has appeared in it. This is an observed operational signal reported by the owner, not an audited measurement — no before/after SLA-compliance percentage is claimed.

I. Business Impact and Performance Measurement

Metric	Baseline	Result	Evidence status	Validation method
Refresh cadence	~2 manual compilations/day	Every 30 minutes, 24/7 (up to 48 runs/day)	Implemented	Run history / sheet audit log
Staff time per update	~2 hours per compilation (~4 staff-hours/day)	No manual compilation per run	Reported / Estimated	Time-log comparison (not available)
Projected manual alternative under the revised SLA	~4 updates/day = ~8 staff-hours/day	Replaced by automation	Estimated projection	Not applicable — projection, not measured
Cases in the six-hourly breach report	Regularly before the workflow	None observed after it became routine	Observed / Reported	Company analytics reports (not independently audited)
SLA-compliance percentage	Not measured	Not measured	Not available	Requires approved analytics export
Error reduction from status reconciliation	Not measured	Not measured	Not available	Requires before/after error logs

Validation plan for the missing measurements. To substantiate the outcome further: (1) export breach-report appearances for a defined pre-period and post-period and compute cases-per-week before and after; (2) log every reconciliation event so the number of cleared wrong statuses is measurable; (3) keep run-level execution logs with timestamps and counts so cadence is provable. These are proposals, not evidence of results.

Figure 7 — Manual baseline versus the automated cycle. Manual effort figures are owner-reported recollections; the automated cadence is implemented and running. Projected manual alternative under the revised SLA: 4 updates/day at ~2 hours each = ~8 staff-hours/day (estimate, not a measured historical figure).

The 30-minute queue summary

J. Lessons Learned and Future Roadmap

Three lessons stand out. First, the business definition of a clock must be confirmed before automation — the first-level origin correction showed how easily a technically working timer can be operationally wrong. Second, reconciliation controls need a grace window that respects the source refresh timing, or they will destroy correct work. Third, visibility is only useful when it reaches people in their working rhythm — the 30-minute chat summary turned the sheet from a tool people open into a signal people receive.

The roadmap: keep execution and exception logs, validate the grace period against source timing, build a small test set for the SLA calculations, and compare overdue-case rates before and after using approved company reports. These would convert the current observational evidence into measured evidence.

K. Skills Demonstrated

Competency	My contribution	Supporting evidence	Business or technical value
Business analysis and rules design	Translated queue rules and client SLA variations into filter, classification, and clock logic	SLA rules table; clock-origin correction	Correct deadline decisions
Data and process analysis	Identified staleness, wrong clock origin, and status disagreement as the failure points	Root-cause table; breach-report observation	Targeted the right problems
Workflow automation design	Specified the 30-minute refresh, routing, and inbox-based payment capture	Architecture diagram; implementation timeline	Reliable, current working view
AI-directed development	Defined requirements, directed code generation, reviewed, tested, and corrected iteratively	Iteration list; testing section	Working solution in the available environment
Preventive controls	Designed the hidden-timestamp reconciliation with a 40-minute grace window	Status-correction flow	Statuses stay consistent with the source
Operational visibility	Designed formula-driven analytics, chat summaries, and color-coded urgency	Analytics and chat mockups	Leads act before deadlines, not after

About the visuals in this document: process diagrams are reconstructed from the owner description of the real workflow; sheet and chat mockups are hypothetical examples; no confidential screenshots, employee data, or internal interfaces are included. Client names are anonymized.