Public Loss Adjusters: Direct Mail

Insurance / Marketing Automation

Public Loss Adjusters: Direct Mail Drip Campaigns

Google Sheets Postalytics API Time-Based Triggers Webhooks

The Mission This firm needed a drip campaign without the overhead of a CRM. I turned Google Sheets into a state machine that tracks where each lead is in a 5-week sequence and triggers physical mailers via Postalytics.

System Architecture

stateDiagram-v2 [*] --> Week1 Week1 --> Week2: 7 Day Delay Week2 --> Week3: 7 Day Delay Week3 --> Week4: 7 Day Delay Week4 --> Week5: 7 Day Delay state "Trigger Check" as TC Week1 --> TC TC --> PostalyticsAPI: Active & Due? PostalyticsAPI --> MailSent MailSent --> UpdateRow: Set Next Trigger

Key Engineering Challenges

State Management without a Database: Google Sheets as a state machine. The script calculates nextTriggerDate based on the current stage and runs an hourly cron job (weeklyTrigger) to check for rows that have matured.
Visual Feedback: A generateProgressBar function updates cells with emoji indicators (🟩⬜) so non-technical staff can see a lead's drip position at a glance without opening a dashboard.

Code Snippet

processLead.js

// From processLead()
// Logic: State machine for drip campaign execution

function processLead(rowIndex, weekStage) {
  // 1. Validate Data Integrity
  if (!hasRequiredFields(row) || row[IS_PAUSED_INDEX]) return;

  // 2. Construct Payload for Direct Mail API
  const payload = buildPayload(row);
  const webhookUrl = props.getProperty(weekStage + '_WEBHOOK');

  // 3. Execute External Action
  const response = sendToPostalytics(webhookUrl, payload, authHeader);

  if (response.getResponseCode() === 200) {
    // 4. Update State Machine
    const nextWeek = new Date(now.getTime() + (7 * 24 * 60 * 60 * 1000));
    const stageNum = parseInt(weekStage.replace('WEEK_', ''));
    
    sheet.getRange(rowIndex, 2).setValue('Week ' + stageNum);
    sheet.getRange(rowIndex, 3).setValue(generateProgressBar(stageNum));
    sheet.getRange(rowIndex, 5).setValue(nextWeek);
  }
}