Queen of San Diego — Tech Blog

Under the hood at queenofsandiego.com

Funeral Home Outreach System: From Zero Sends to Production—Architecture Audit and Rebuild Plan

We inherited a funeral home prospecting system that looked operational on paper but had never sent a single email in weeks of deployment. This post documents the infrastructure audit, why the system failed silently, and the technical rebuild required to get daily 10-prospect outreach running at scale.

The Problem: Two Broken Implementations

Two separate outreach implementations existed in the codebase, each partially complete and neither functional:

  • Google Apps Script (GAS): /sites/queenofsandiego.com/FuneralOutreach.gs — reads a Google Sheet, sends via AWS SES, has advanced reply/bounce tracking built in. Trigger was never installed.
  • EC2 Cron + Python: /home/ubuntu/repos/tools/send_funeral_blast.sh — shell wrapper around jada_blast.py send --campaign funeral-outreach-2026. Cron was set for a single date (Apr 24) and the send silently failed approval gates.

Result: 25 prospects in a Google Sheet with zero InitialSent, F1Sent, F2Sent, or Replied values. Gmail audit across 180 days showed not a single outreach message to any funeral home domain (greenwoodsd.com, lavistamemorialpark.com, neptunesandiego.com, etc.).

Root Cause Analysis

Implementation #1: GAS Script — Schema Mismatch

The Google Sheet 1ADx_0L6...rg38, tab Contacts has 9 columns:

Name | Email | Phone | City | LastContact | InitialSent | F1Sent | F2Sent | Replied

But the script header declares a 14-column schema including OOO, Bounced, F3Sent, and CampaignID. The setup function funeralOutreachSetup() that would have expanded the sheet and initialized tracking columns was never called. Without those columns, the main function runFuneralOutreach() would crash on write attempts.

Additionally:

  • Trigger was never installed (no Apps Script deployment trigger for Wednesday 9 AM PT).
  • Sender address was admin@queenofsandiego.com, not the standing rule's outreach@burialsatseasandiego.com.
  • Cadence was weekly (1 send per prospect per week), not the standing rule's daily 10 new prospects.

Implementation #2: EC2 Cron — Silent Failure

The shell script at /home/ubuntu/repos/tools/send_funeral_blast.sh:

#!/bin/bash
python3 /home/ubuntu/repos/tools/jada_blast.py send --campaign funeral-outreach-2026 \
  --template tools/templates/funeral-outreach.html \
  --contacts tools/contacts/funeral-homes-sd.csv

Cron entry was hardcoded to a single date: 5 16 24 4 * (Apr 24 at 4:05 PM). The campaign ledger at s3://progress.queenofsandiego.com/blast-campaigns.json contains 2,649 sends across 9 campaigns; funeral-outreach-2026 is absent. No funeral_blast_*.log file exists in tools/logs/. The most likely cause: jada_blast.py enforces an approval gate (task ID must be in the done lane of a Monday.com board), and without that approval, the script exits without logging—a silent failure pattern.

Technical Architecture Decisions

Why GAS Was Chosen (Initially)

Google Apps Script is ideal for:

  • Native Google Sheet integration—no ETL, direct cell writes.
  • SES SigV4 signing can be done in-script, avoiding a separate authentication service.
  • Serverless execution: no EC2 instance to manage.
  • Built-in time-based triggers (Apps Script Deployments API).

The implementation correctly anticipated OOO detection (scanning reply headers for vacation auto-responders) and bounce handling (querying SES suppression lists via boto3-style AWS SDK calls). These are production-grade features for a drip campaign.

Why EC2 Cron Failed

The EC2 approach tried to reuse existing infrastructure (jada_blast.py, the Monday.com approval gate, the campaign ledger). This is sensible for auditing and batching, but:

  • Hardcoding a single cron date instead of a daily recurring rule suggests the setup was never validated.
  • Silent failure on approval gate rejection provided zero feedback; logs were not being written.
  • The prospect CSV at tools/contacts/funeral-homes-sd.csv is static (8 rows, hand-entered). There's no harvester to fetch "10 new funeral homes every day" as the standing rule specifies.

What's Missing: The Prospect Harvester

Neither implementation includes a mechanism to automatically discover and enroll new funeral home prospects. The standing rule calls for "find 10 new emails every day." To do this, you'd need:

  • Google Maps API queries for funeral homes in San Diego County, filtered by review count and rating.
  • Email extraction from business websites (contact page scraping or WHOIS lookups).
  • Deduplication against the existing contacts sheet and suppression lists.
  • Validation (MX record checks, SMTP banner probes) to verify addresses are real.
  • Daily enrollment into the drip campaign.

This is non-trivial and was never built.

Rebuild Plan

To get from zero sends to production:

Phase 1: Fix GAS (Immediate — ~30 minutes)

  • Call funeralOutreachSetup() manually to initialize the 14-column schema in the Google Sheet.
  • Create an Apps Script deployment (via clasp push or the IDE).
  • Install a time-based trigger: ScriptApp.newTrigger('runFuneralOutreach').timeBased().everyDays(1).atHour(9).inTimezone('America/Los_Angeles').create().
  • Update sender from admin@queenofsandiego.com to outreach@burialsatseasandiego.com (requires SES domain verification and DKIM setup for that address).
  • Let it run Wednesday through the existing 25 prospects; collect baseline metrics (open rate, reply rate, bounces).

Phase