The problem
Lead handoffs between the ISA team and agents were manual, inconsistent, and easy to drop. There was no structured way to confirm a lead had been accepted, no visibility into whether the agent had contacted the lead before the appointment, and no escalation path when things went quiet. Leadership couldn't tell where a lead was in the process without asking someone directly. The handoff itself was a black box.
How I built it
The system lives in Google Sheets and is built around a status-driven trigger model — every time the ISA sets or changes a status tag, Apps Script fires the right automated email to the right person. No manual sends, no forgotten follow-ups.
The handoff itself starts with the ISA filling out the lead tracker left to right: lead info, agent assignment, appointment details. Setting the status to ISA-Handoff is the last step — and it's what fires the Day 0 email to the agent with full lead details and a one-click Accept link. Leadership is CC'd automatically.
When the agent clicks Accept, Apps Script updates the status, sends a confirmation to the ISA, and fires an outreach reminder to the agent. From there the system tracks through agent confirmation, appointment day reminders, Day +1 outcome requests, and final outcome logging — each stage with its own automated email chain.
If an agent goes quiet past a deadline, the Needs-Followup status fires an escalation email to leadership automatically. I also wrote full SOPs for each scenario so the ISA team can manage edge cases without guesswork.
What was built
01
Status-triggered email automation: Apps Script fires the correct email sequence based on status changes — handoff, acceptance, confirmation, reminders, outcome, and escalation.
02
One-click agent acceptance: agents receive a Day 0 email with an embedded Accept link — clicking it auto-updates the tracker status and fires confirmation emails to ISA and leadership.
03
Automated escalation logic: Needs-Followup status fires escalation emails to leadership when agents don't respond past defined deadlines — no manual monitoring needed.
04
7-zone lead tracker: color-coded Google Sheets layout guides ISA through lead info, agent assignment, appointment details, status, auto-fields, post-appointment outcomes, and admin tracking.
05
Full SOP documentation: step-by-step guides for each workflow scenario — standard handoff, Cannot Take, Needs-Followup — so the team can manage without depending on me.
06
CC rules and leadership visibility: Team Lead, Admin Lead, Team Manager automatically CC'd at the right stages so leadership always has context without being buried in every email.
8-step handoff workflow
01
ISA Confirms
ISA confirms appointment with lead by phone
02
Assignment
ISA gets agent assignment from team lead
03
Tracker Fill
ISA fills tracker left to right: lead → agent → appointment
04
ISA-Handoff
Status set — Day 0 email fires to agent with Accept link. Leadership CC'd.
05
Agent Accepts
Agent clicks Accept — status auto-updates, ISA confirmation + outreach email fires
06
Agent Confirms
Agent contacts lead, ISA sets Agent-Confirmed — appt reminder fires
07
Appt Day
Day +1 outcome request fires. Team Manager and leadership CC'd from here.
08
Outcome Logged
ISA logs outcome, date, next step. Status = Completed or Dead.
Status tags and what triggers them
ISA-Handoff — ISA sets manually
Lead-Accepted — Apps Script auto
Agent-Confirmed — ISA sets manually
Appt-Completed — ISA sets manually
Needs-Followup — escalation fires
Dead — ISA closes row