Sail JADA — Engineering Log

Technical work on sailjada.com and the JADA booking systems

Debugging a Dormant Email Outreach Pipeline: Funeral Home Drip Campaign Audit

During a recent development session, we discovered that a multi-week funeral home outreach campaign—designed to generate leads for burial-at-sea services—had never actually sent a single email. What should have been a steady drip of 10 messages per day was sitting completely cold. This post walks through the audit methodology, root causes identified, and the architectural decisions we're now weighing to get the pipeline operational.

What We Found: Zero Sends in 180 Days

The funeral home outreach system exists across three interconnected layers:

  • Data layer: A Google Sheet (`Contacts` tab) with 25 qualified funeral home prospects
  • Execution layer: A Google Apps Script (`FuneralOutreach.gs`) housed in `sites/queenofsandiego.com/`
  • Delivery layer: AWS SES (Simple Email Service) for outbound SMTP

A Gmail audit covering the past 180 days revealed the harsh truth: zero outbound messages to any of the 25 funeral home addresses, zero inbound replies, and no execution logs indicating the drip had ever fired.

Root Cause Analysis: Multiple Failures in Sequence

Trigger Misconfiguration

The Apps Script trigger was configured to fire once weekly on Wednesday mornings at 9 AM PT, not the standing rule's mandate of daily at 10 AM PT with a 10-message batch limit. This alone doesn't explain zero sends—a weekly trigger should have fired at least 25 times over five months—but it was symptomatic of incomplete setup.

Sender Address Mismatch

The script's sender address was hardcoded as admin@queenofsandiego.com, while the standing rule requires all funeral outreach to originate from outreach@burialsatseasandiego.com. This domain-specific sender is critical for:

  • SPF/DKIM alignment (the SES-verified domain for outbound email)
  • Reply routing (prospects reply to the burial-at-sea brand, not a generic sailboat charter domain)
  • Compliance tracking (separate email logs for this vertical)

Using the wrong sender would either fail SES validation outright or create unverified domain scenarios that bounce the mail.

Schema Drift in the Spreadsheet

The script header documents a 14-column schema for prospect tracking, including:

  • Contact name, funeral home, email
  • InitialSent, F1Sent, F2Sent, F3Sent (send timestamps)
  • Replied, Bounced, OOO (response tracking)
  • Effectiveness metrics (click-through, conversion flags)

The actual sheet has only 9 columns. The function funeralOutreachSetup()—responsible for initializing the extended schema—was never run. When the main execution function, runFuneralOutreach(), attempted to write send timestamps to column indices that didn't exist, it likely failed silently or threw unhandled exceptions.

No Execution Logs

Crucially, Apps Script execution logs showed no records of runFuneralOutreach ever firing. This rules out the trigger being active and the function failing partway through. The trigger itself was either not deployed or not bound to the correct function.

Technical Audit Methodology

Here's the command sequence we followed to surface these issues:

# Search for funeral-related files in the project structure
grep -r "funeral" sites/ --include="*.gs" --include="*.js"

# Locate handoff documentation and standing rules
find . -name "*funeral*" -type f
find . -path "*docs*" -name "*handoff*" -o -name "*standing*rule*"

# Audit the Apps Script file
cat sites/queenofsandiego.com/FuneralOutreach.gs

# Check Google Sheet structure and data
# (via Apps Script: SpreadsheetApp.openById('sheet-id').getSheetByName('Contacts'))

# Validate SES configuration
# (via AWS CLI: list verified identities, check sender domain)

# Search Gmail for actual sends
# Scope: jadasailing@gmail.com, last 180 days
# Queries: to:(funeralHome1@domain.com), from:(outreach@burialsatseasandiego.com)

Infrastructure State

The outreach system relies on these resources:

  • Google Sheet: ID prefix `1ADx_0L6...` (truncated), shared with service account
  • Google Apps Script: Project bound to the Sheet, deployed as web app (though the trigger binding may be stale)
  • AWS SES: Domain burialsatseasandiego.com should be verified; sending limits are 1 message per second, up to 14 per day by default (would need to request production access for higher volumes)
  • Gmail: Outbound audit trail, `jadasailing@gmail.com` (shared inbox for monitoring replies)

Key Decisions Going Forward

We're now deciding between two paths:

Option A: Minimal Intervention (Risk: Unclean Data)

  • Install the Wednesday 9 AM trigger immediately
  • Let it fire from admin@queenofsandiego.com
  • Start generating send records and measure effectiveness
  • Pro: Fastest path to data; minimal setup overhead
  • Con: Violates the standing rule (wrong sender, wrong cadence); emails may fail SES verification; replies go to the wrong domain; we can't trust the effectiveness metrics later

Option B: Full Alignment (Risk: 20-Minute Setup Delay)

  • Verify outreach@burialsatseasandiego.com in AWS SES (if not already done)
  • Update the script's sender address to use the correct domain
  • Extend the Google Sheet schema to 14 columns and run funeralOutreachSetup()
  • Change the trigger to fire daily at 10 AM PT with a 10-message-per-execution batch
  • Validate the trigger is correctly bound to runFuneralOutreach
  • Pro: Meets standing rule; clean SPF/DKIM alignment; compliant reply routing; trustworthy effectiveness metrics from day one
  • Con: Takes 20 additional minutes; delays first send by that window

We're leaning toward Option B. Starting a multi-week campaign with architectural debt baked in means we'll either have to re-run it later (wasting outreach attempts) or accept compromised data. Given that this is a lead-generation pipeline tied to revenue, the 20-minute setup cost is worth the integrity.

What's Next

Next session will involve:

  • Confirming SES domain verification status for outreach@burialsatseasand