APPLICANT REMINDER AUTOMATION

A. Executive Snapshot

B. Executive Summary

A client in education-document verification went live without the feature our other clients have. In the standard workflow an applicant receives an insufficiency email when we need a document they have not sent, and if nothing arrives about three days later they receive a reminder. For this client that follow-up did not exist, so every reminder was written and sent by hand.

The hard part was never the wording. It was the assembly. To send one correct reminder I needed three things that lived in three separate exports: who the applicant is and where to reach them, which of their cases is still waiting for a document, and the exact requirement that was communicated to them. The case barcode was the only thing those records had in common. Every reminder therefore started as a manual search across three files, and every one of them was an opportunity to reach the right person with the wrong requirement.

I built a workflow around that join. A Google Apps Script retrieves the three feeds into a Google Sheet, and a barcode typed into the main working sheet pulls the applicant's name, email address and requirement into place by formula — no copying, no re-typing. A second tab, the email sender tab, lists the cases to contact and carries a "Send Emails" button. Pressing it sends the whole list as one batch, writes "Done" against every row it sent, and records the reminder date back on the working sheet. A typical run covers 250–300 applicants and takes about five minutes; the work it replaces took close to a full working day of preparation and individual sending.

One part of the design I deliberately did not automate. Deciding whether a reminder is still required stayed a human decision, because a reminder that is no longer needed is a needless contact with an applicant. The script decides nothing about eligibility: I read the queue, check the last-reminder date on each case, queue the barcodes that are still due, review the list, and then press the button. Three days is the rhythm I work to, not a rule the script enforces.

The workflow was built in August 2025 and has run continuously since. It is used by me and by two performers who work with me. What it changed is the effort and the consistency of the contact, not the size of the queue — and I can demonstrate the first, while the second I can only state as an expectation until delivery and response data are captured.

C. Business Context and Problem Definition

The process that had to be worked around

Our operation verifies education documents for institutional clients. When a document is missing, the applicant is told what is needed through an insufficiency email. On the clients whose systems carry the follow-up feature, the system then watches for the upload and, if nothing arrives, sends the reminder automatically about three days later. The applicant receives two contacts and the case moves forward without anyone chasing it.

This client went live without that feature. The insufficiency email still went out, because that is part of our own operation, but nothing followed it. The reminders that the client system would have sent became our job, and the only way to send them was one at a time, by hand.

The manual baseline

Preparing and sending one cycle of applicant reminders took close to a full employee-day. The work consisted of pulling the three exports, matching them case by case on the barcode, reading the requirement that had been communicated to the applicant, composing the message around that requirement, and sending it individually — then repeating the whole sequence for the next case. I am reporting that as an estimate based on my own recollection; it was not measured with a time study, and I would not present it as an audited figure.

Two things made it worse than a simple time cost. First, the three sources were joined by eye, so a requirement could be attached to the wrong applicant. Second, because each contact was sent individually, the effort scaled directly with the number of pending cases — and the pending queue for this client routinely sits between roughly 150 and 500 cases.

Who was affected

• The applicants, who were waiting on a case while the only contact they received was the one that asked for the document in the first place.
• The team preparing the reminders, who spent the cycle on message preparation instead of case work.
• The cases in the queue, which aged while the reminders were being written one by one.

Constraints and the decision to build in parallel

The client system and the internal API belonged to the company and were out of scope: I could not add a follow-up feature to the client system, and changing the API was not on the table. The solution had to work inside the Google Workspace environment the operation already used, and it had to read the data those systems already exposed rather than replace them. Building the workflow alongside the platform, rather than waiting for the platform to change, was the practical decision — with the trade-off that the automation lives in a spreadsheet rather than inside the system of record, and therefore depends on the data it reads being current. Figure 1 sets the two cycles side by side.

D. Data Discovery and Root Cause Analysis

Before designing anything I compared what the three exports held and how a single case appears across them. That comparison produced the causal chain the whole project rests on.

The root cause

The root cause was structural, not behavioural: the reminder content lived in three disconnected exports, and the case barcode was the only key they shared. That is what made the manual work heavy and what made it risky — not the speed of the team, and not the behaviour of applicants. The client system could not carry the follow-up step for this client, so the join had to be made and the send had to be performed on our side.

Two explanations that might be expected here are worth ruling out explicitly, because they are not the cause. The team was not slow: the effort per case was seconds of work repeated hundreds of times, which is a scaling problem rather than a performance problem. And applicants are not unresponsive by nature: the workflow changes the frequency and consistency of contact, but whether a given applicant responds is outside what this project can claim.

One explanation was considered and deliberately not adopted: a fully scheduled sender, which would remove the human step entirely. I rejected it for a stated reason rather than by default — an automated scheduler cannot tell whether a reminder is still required, and an unnecessary reminder is an unnecessary contact with an applicant. That trade-off shaped the design more than any other decision in the project.

E. Solution Design and AI-Directed Development

I wrote the specification before any code existed, because the specification was the part I owned and the code was the part I could produce quickly with AI. The specification had six elements:

1. Inputs — three client feeds: applicant details (barcode, name, email address), cases still awaiting a document upload, and the insufficiency comment that states the requirement.
2. Join key — the case barcode, entered once, with every other field resolved by formula from the same case record.
3. Working structures — a main working sheet that holds the case record, and an email sender tab that holds what is to be sent and what has been sent.
4. Template variables — applicant name, requirement text, and the barcode in the subject line, so every message carries the case it belongs to.
5. Validation rules — a row with a missing name, address or requirement is skipped rather than sent.
6. State write-backs — a Done flag on the sender tab and the reminder date on the working sheet, so a second run cannot contact the same case again.

How the workflow is put together

The script retrieves the three feeds into the sheet. A barcode typed into the working sheet triggers a formula lookup that returns the applicant's name, email address and requirement from the same case record. The email sender tab pulls the name and address onto each row and carries a "Send Emails" button. Pressing it walks the list, substitutes the variables into the template, sends each message, marks the row Done and writes today's date into the reminder column on the working sheet. Because every field is resolved by barcode, the same case cannot pick up another case's requirement. Figure 2 traces that path from the three feeds to the sent message and back to the state written on the sheet.

The sheet and the template

The working sheet is deliberately plain. The main tab carries the case record — barcode, start date, requirement, number of reminders, date of last reminder, applicant name and applicant email — and the email sender tab carries what is queued and what has been sent. Only the barcode is entered by hand; the applicant name, address and requirement resolve by formula from the same case, and the sender tab pulls the name and address across by barcode. The Send Emails button sits on the right of the sheet.

Figure 3 shows the two tabs as they work, and Figure 4 shows the message it produces. The message is a fixed template with three variables. The name opens the greeting, the requirement states what is still needed, and the barcode in the subject line ties the message to the case without anyone having to retype it. The body stays constant so no message can accidentally omit the request.

How I used AI to build it

I used AI as the implementation partner for the Apps Script, and I kept the judgement that the script could not make. The sequence was consistent throughout the project: I wrote the requirements, the join logic, the tab structure, the template variables and the validation rules; I asked for the script that implemented them; I read the generated code against my specification rather than accepting it; I tested the behaviour against live data; and I directed the revisions until the script did what I had specified. The specification was the part I owned, and the specification was where the real decisions sat — the join key, the three-day rhythm, the point where a person stays in the loop, and the rule that an incomplete record is skipped rather than sent.

That division is what made the workflow maintainable. Because the rules live in a written specification and a sheet, a change in behaviour is a change in the specification first, and the script follows it.

Two engineering decisions worth naming

Pacing the send. The script sends one message per second, so a 250–300 message run takes about five minutes. That is deliberate: sending faster risks being throttled or blocked by the sending channel, and a run that fails halfway is worse than a run that takes five minutes. The pacing is the reason a full batch is safe rather than merely fast.

Skipping rather than sending. A row with a missing name, address or requirement is skipped and no message goes out. The alternative — sending a partially populated message — would put an incomplete request in front of an applicant and waste the contact. The skip is silent today, which is a weakness of the design and is listed as a next step in section J.

F. Project Management and Implementation

Scope decisions

• Build beside the platform, not inside it. The client system and the internal API were left untouched, which kept the work inside the environment the team already used and left nothing to undo in the client system.
• No change to the message the applicant receives. The message structure matches the insufficiency email the applicant already received, so the follow-up is recognisable rather than a second, different instruction.
• The eligibility decision stays human. Automation was scoped to assembly and sending, not to deciding who deserves a reminder.
• No delivery infrastructure. The script sends and records what it sent; it does not manage bounces or track replies, which would need a delivery log the current send does not produce.

Risks and how they were handled

Adoption and continuity

The sheet and the reminder bot are used by me and by two performers who work with me. The people preparing the reminders are the same people who can see the queue, and working from one sheet kept the operating routine common. The workflow has run without interruption since August 2025, and the maintenance load is low because the rules are visible in the sheet and the script follows a written specification.

G. Preventive Improvement

Most of the value in this project is preventive. It is not in how fast a message goes out; it is in the errors that can no longer happen, and in the one error that was designed out on purpose. Figure 7 sets out every control in the workflow, and separates the six that are built from the four that are not.

The control that mattered most

Automating eligibility was the obvious next move, and I did not take it. The reason is worth stating in the company's own terms: a reminder that is not required is not a neutral message — it is an unnecessary contact with an applicant who is already waiting on a decision about their own documents. The cost of that error falls on the applicant, and it cannot be detected by a script that has no view of whether the request is still live. So the design keeps the assembly and the sending automatic, and keeps the judgement with the person who can see the case.

The controls below that judgement rather than replace it. The Done flag and the reminder date make a run safe to repeat; the required-field check makes an incomplete record impossible to send; the barcode join makes a mismatched message improbable; and the pacing makes a full run dependable.

Where the design does not yet protect, it should be said plainly. The workflow prevents unnecessary contact, mismatched content and duplicate sends. It does not currently detect a stale row, a silent skip, or a failed delivery — three of the four remaining risks in the matrix above — and it does not measure whether the contact actually changes the outcome for the applicant.

H. Testing and Validation

Validation here was practical rather than formal: the workflow was exercised on the live pending queue and its behaviour was checked against the written specification, cycle after cycle, over roughly fourteen months. There was no test suite, no regression set, and no formal sign-off record, and I am not going to present continuous use as a substitute for one.

The test set I would build next

These are proposed tests, not executed ones, and they are small enough to run on a copy of the sheet:

I. Business Impact and Performance Measurement

What the business case still needs

The effort comparison is the part of this project I can defend, and it is enough to justify the work on its own. The stronger claim — that continuous, correctly timed contact gets documents to applicants faster — is not yet supported by anything I can point to, because the workflow does not record whether a message arrived or whether the applicant responded. Three measurements would close that gap, and each has a simple source:

Until those exist, the correct statement of the outcome is the one I make in the summary: the workflow removed close to a full employee-day of repetitive preparation and sending per cycle and made the contact consistent, and the effect on case turnaround is an expectation rather than a measured result.

J. Lessons Learned and Future Roadmap

What I learned

• The join is the work. The email was never the hard part. Assembling one applicant's details from three exports, case by case, was the whole cost — and the whole opportunity.
• Knowing where to stop is part of the design. Automating the send was straightforward; automating the decision to send was a choice with a cost attached, and keeping it human was the right one. The script is only defensible because it does not decide who deserves a reminder.
• State written back is what makes a tool safe to re-run. The Done flag and the reminder date are what turn a batch job into something a person can run every three days without fear of contacting the same applicant twice.
• Reliability is often a pace, not a speed. Sending at one message per second looks unhurried until it is compared with the manual alternative, and it is the reason a 300-message run completes rather than being blocked half-way.
• A control you can see beats a control you trust. The Done flag and the reminder date are visible on the sheet, so the state of the work is legible to anyone working from it — including the two performers who use it.
• The measurement gap is my weakest point, and I know where it is. I can defend the effort saved. I cannot yet defend the effect on case turnaround, because nothing in the workflow records whether a message arrived or what happened next. I would rather name that than let a reader assume it.

What I would build next

• A skip log. One column on the sender tab recording why a row was not sent, so no case disappears silently. This is the cheapest meaningful improvement to the workflow.
• A stale-row check. A timestamp on the retrieved data would let the script flag any row queued before the last refresh, removing the risk of sending from an outdated record.
• A suggested-due list. A read-only list of cases whose reminder date is more than three days old, which I review and queue. It keeps the human decision exactly where it is while removing the risk of overlooking a case.
• A delivery check. Capturing the send result per row, so an undeliverable address is visible instead of inferred from silence.
• A small regression sheet. A handful of representative cases with their expected fields and expected behaviour, checked whenever the template or the rules change.
• Three cycle metrics. Application rate, cycle adherence and response within the cycle, so the business effect can be measured rather than assumed.

None of these is built today. They are the design I would take to the next version, and each one is deliberately small: the workflow earns trust by being predictable, and that is worth protecting.

K. Skills Demonstrated

How the competencies combine

The project is not a collection of skills applied in sequence; it is one chain. Business analysis produced the specification, the specification made the AI-directed development possible, the development produced controls that made the workflow safe to run every three days, and the operating routine is what turned a script into something with fourteen months of continuous use. The root cause analysis is the hinge: it is the reason the work was a data join rather than a mail merge, and the reason the control set is shaped around what a script cannot know — namely, whether a reminder is still required.