followup-generator.js
1 /** 2 * Follow-up Sequence Generator 3 * 4 * Creates multi-touch follow-up messages for sites that have been contacted 5 * but haven't replied. 8-touch cadence over 42 days: 6 * 7 * Touch 1 (Day 0): Initial outreach — already exists 8 * Touch 2 (Day 3): Different weakness + social proof 9 * Touch 3 (Day 7): ROI framing ("costing you $X/month") 10 * Touch 4 (Day 14): Case study + sample report link 11 * Touch 5 (Day 21): Quick win offer (free fix) 12 * Touch 6 (Day 28): Ad waste angle (if running ads) / competitor gap 13 * Touch 7 (Day 35): Authority / testimonial 14 * Touch 8 (Day 42): Breakup email ("closing the file") 15 * 16 * Each follow-up uses a different value angle to avoid repetition. 17 * SMS follow-ups are shorter than email follow-ups. 18 * 19 * Edge cases handled: 20 * - Don't follow up sites that received an inbound reply 21 * - Don't follow up opted-out contacts 22 * - Respect 3-day per-site cooldown between any sends 23 * - Don't generate follow-ups for sites already at max sequence step 24 */ 25 26 import { run, getOne, getAll } from '../utils/db.js'; 27 import { readFileSync, existsSync } from 'fs'; 28 import { join, dirname } from 'path'; 29 import { fileURLToPath } from 'url'; 30 import Logger from '../utils/logger.js'; 31 import { spin } from '../utils/spintax.js'; 32 import { computeGrade } from '../score.js'; 33 import { getCountryByCode } from '../config/countries.js'; 34 import { getScoreDataWithFallback } from '../utils/score-storage.js'; 35 import { getContactsDataWithFallback } from '../utils/contacts-storage.js'; 36 37 const __filename = fileURLToPath(import.meta.url); 38 const __dirname = dirname(__filename); 39 const projectRoot = join(__dirname, '../..'); 40 41 const logger = new Logger('FollowupGenerator'); 42 43 // Touch cadence: days after initial outreach for each follow-up 44 const TOUCH_CADENCE = { 45 2: 3, // Touch 2: 3 days after initial 46 3: 7, // Touch 3: 7 days after initial 47 4: 14, // Touch 4: 14 days after initial 48 5: 21, // Touch 5: 21 days — quick win offer 49 6: 28, // Touch 6: 28 days — ad waste / competitor gap 50 7: 35, // Touch 7: 35 days — authority / testimonial 51 8: 42, // Touch 8: 42 days — breakup 52 }; 53 54 const MAX_SEQUENCE_STEP = 8; 55 56 /** 57 * Get sites eligible for follow-up generation. 58 * 59 * Criteria: 60 * - status = 'outreach_sent' 61 * - next_followup_at <= now (or next_followup_at IS NULL for first follow-up scheduling) 62 * - No inbound reply received 63 * - Not opted out 64 * - Current max sequence_step < MAX_SEQUENCE_STEP 65 * - conversation_status not in closed states 66 * 67 * @param {number} limit - Max sites to process 68 * @returns {Promise<Array>} Sites eligible for follow-up 69 */ 70 export async function getFollowupEligibleSites(limit = 10) { 71 const closedStatuses = ['not_interested', 'closed', 'unsubscribed', 'paid', 'report_delivered']; 72 73 const sites = await getAll( 74 `SELECT 75 s.id, 76 s.domain, 77 s.landing_page_url, 78 s.keyword, 79 s.score, 80 s.grade, 81 s.country_code, 82 s.city, 83 s.state, 84 s.last_outreach_at, 85 s.next_followup_at, 86 -- Get the max sequence step already generated for this site 87 (SELECT MAX(m.sequence_step) FROM messages m 88 WHERE m.site_id = s.id AND m.direction = 'outbound' 89 AND m.sequence_step IS NOT NULL) as max_step, 90 -- Get the last sent outbound message timestamp 91 (SELECT MAX(m.sent_at) FROM messages m 92 WHERE m.site_id = s.id AND m.direction = 'outbound' 93 AND m.delivery_status IN ('sent', 'delivered')) as last_sent_at 94 FROM sites s 95 WHERE s.status = 'outreach_sent' 96 -- Due for follow-up: next_followup_at has passed 97 AND s.next_followup_at IS NOT NULL 98 AND s.next_followup_at <= NOW() 99 -- No inbound reply received (no messages with direction='inbound') 100 AND NOT EXISTS ( 101 SELECT 1 FROM messages m 102 WHERE m.site_id = s.id 103 AND m.direction = 'inbound' 104 ) 105 -- Not opted out (check both opt_outs and unsubscribed_emails) 106 AND NOT EXISTS ( 107 SELECT 1 FROM opt_outs o 108 JOIN messages m ON ( 109 (o.phone = m.contact_uri AND o.method = 'sms') 110 OR (o.email = m.contact_uri AND o.method = 'email') 111 ) 112 WHERE m.site_id = s.id AND m.direction = 'outbound' 113 ) 114 -- Not in a closed conversation state 115 AND (s.conversation_status IS NULL 116 OR s.conversation_status != ALL($1::text[])) 117 -- Haven't already generated all 8 touches 118 AND (SELECT COALESCE(MAX(m2.sequence_step), 1) FROM messages m2 119 WHERE m2.site_id = s.id AND m2.direction = 'outbound' 120 AND m2.sequence_step IS NOT NULL) < $2 121 -- Respect 3-day cooldown since last actual send 122 AND (s.last_outreach_at IS NULL 123 OR s.last_outreach_at < NOW() - INTERVAL '3 days') 124 -- Must have at least one successfully delivered email/sms to follow up on 125 AND EXISTS ( 126 SELECT 1 FROM messages m3 127 WHERE m3.site_id = s.id AND m3.direction = 'outbound' 128 AND m3.delivery_status IN ('sent', 'delivered') 129 AND m3.contact_method IN ('email', 'sms') 130 ) 131 ORDER BY s.next_followup_at ASC 132 LIMIT $3`, 133 [closedStatuses, MAX_SEQUENCE_STEP, limit] 134 ); 135 136 return sites; 137 } 138 139 /** 140 * Load follow-up templates for a country/channel combination. 141 * Returns null if country-specific templates don't exist (no AU fallback — 142 * each country must have its own templates with correct spelling/tone). 143 * 144 * @param {string} countryCode - ISO country code 145 * @param {string} channel - 'email' or 'sms' 146 * @returns {Object|null} Templates object with followup fields 147 */ 148 function loadFollowupTemplates(countryCode, channel) { 149 const templatesDir = join(projectRoot, 'data', 'templates'); 150 const paths = [ 151 join(templatesDir, countryCode, `followup-${channel}.json`), 152 ]; 153 154 for (const p of paths) { 155 if (existsSync(p)) { 156 try { 157 return JSON.parse(readFileSync(p, 'utf8')); 158 } catch (e) { 159 logger.warn(`Failed to parse template ${p}: ${e.message}`); 160 } 161 } 162 } 163 return null; 164 } 165 166 /** 167 * Extract weaknesses from score_json for follow-up messaging. 168 * Returns weaknesses NOT already used in the initial outreach (different angle). 169 * 170 * @param {Object} scoreJson - Parsed score_json 171 * @param {number} skipCount - Number of top weaknesses to skip (already used) 172 * @returns {Array} Array of {factor, score, reasoning} 173 */ 174 function extractWeaknesses(scoreJson, skipCount = 1) { 175 if (!scoreJson?.factor_scores) return []; 176 return Object.entries(scoreJson.factor_scores) 177 .filter(([, f]) => f.score < 5) 178 .sort((a, b) => a[1].score - b[1].score) 179 .slice(skipCount) // Skip the ones already used in initial outreach 180 .map(([name, f]) => ({ 181 factor: name.replace(/_/g, ' '), 182 score: f.score, 183 reasoning: f.reasoning, 184 })) 185 .slice(0, 3); 186 } 187 188 /** 189 * Generate follow-up messages for a single site. 190 * 191 * @param {Object} site - Site record from getFollowupEligibleSites 192 * @returns {Promise<Object>} Result with generated messages count 193 */ 194 export async function generateFollowupsForSite(site) { 195 const nextStep = (site.max_step || 1) + 1; 196 if (nextStep > MAX_SEQUENCE_STEP) { 197 return { generated: 0, reason: 'max_step_reached' }; 198 } 199 200 const scoreJson = getScoreDataWithFallback(site.id, site) ?? {}; 201 const contactsJson = getContactsDataWithFallback(site.id, site) ?? {}; 202 const businessName = contactsJson.business_name || site.domain; 203 const grade = site.grade || computeGrade(site.score); 204 const country = getCountryByCode(site.country_code); 205 const pricing = country 206 ? `${country.currencySymbol}${Math.round(country.priceUsd / 100)}` 207 : '$297'; 208 209 // Get existing outbound contact methods for this site (follow up on same channels) 210 const existingChannels = await getAll( 211 `SELECT DISTINCT contact_method, contact_uri 212 FROM messages 213 WHERE site_id = $1 AND direction = 'outbound' 214 AND delivery_status IN ('sent', 'delivered') 215 AND contact_method IN ('email', 'sms') 216 ORDER BY sent_at DESC`, 217 [site.id] 218 ); 219 220 if (existingChannels.length === 0) { 221 return { generated: 0, reason: 'no_sent_channels' }; 222 } 223 224 let generated = 0; 225 226 for (const { contact_method: channel, contact_uri: contactUri } of existingChannels) { 227 // Check if this follow-up step already exists for this channel 228 const exists = await getOne( 229 `SELECT 1 FROM messages 230 WHERE site_id = $1 AND direction = 'outbound' 231 AND contact_method = $2 AND contact_uri = $3 232 AND sequence_step = $4`, 233 [site.id, channel, contactUri, nextStep] 234 ); 235 236 if (exists) continue; 237 238 // Check opt-out for this specific contact 239 const optedOut = await getOne( 240 `SELECT 1 FROM opt_outs 241 WHERE (phone = $1 AND method = 'sms') OR (email = $1 AND method = 'email')`, 242 [contactUri] 243 ); 244 245 if (optedOut) continue; 246 247 // Check unsubscribed emails 248 if (channel === 'email') { 249 const unsub = await getOne( 250 `SELECT 1 FROM unsubscribed_emails WHERE email = $1`, 251 [contactUri] 252 ); 253 if (unsub) continue; 254 } 255 256 // Generate the follow-up message 257 const message = buildFollowupMessage({ 258 step: nextStep, 259 channel, 260 site, 261 scoreJson, 262 businessName, 263 grade, 264 pricing, 265 contactsJson, 266 }); 267 268 if (!message) { 269 logger.warn(`No template for step ${nextStep} ${channel} — site ${site.id}`); 270 continue; 271 } 272 273 // Determine message_type for this step 274 const messageType = `followup${nextStep}`; 275 276 // GDPR check for approval status 277 let approvalStatus = 'pending'; 278 if (channel === 'email' && country?.requiresGDPRCheck) { 279 approvalStatus = 'gdpr_blocked'; 280 } 281 282 // Insert the follow-up message 283 try { 284 await run( 285 `INSERT INTO messages ( 286 site_id, direction, contact_method, contact_uri, 287 message_body, subject_line, approval_status, 288 message_type, sequence_step 289 ) VALUES ($1, 'outbound', $2, $3, $4, $5, $6, $7, $8)`, 290 [ 291 site.id, 292 channel, 293 contactUri, 294 message.body, 295 message.subject || null, 296 approvalStatus, 297 messageType, 298 nextStep, 299 ] 300 ); 301 generated++; 302 } catch (e) { 303 // Handle dedup constraint violations gracefully 304 if (e.message.includes('unique') || e.message.includes('duplicate')) { 305 logger.info(`Dedup: follow-up step ${nextStep} already exists for site ${site.id} ${channel}`); 306 } else { 307 logger.error(`Failed to insert follow-up for site ${site.id}: ${e.message}`); 308 } 309 } 310 } 311 312 // Update next_followup_at for the next touch 313 if (generated > 0) { 314 const nextNextStep = nextStep + 1; 315 if (nextNextStep <= MAX_SEQUENCE_STEP) { 316 const daysUntilNext = TOUCH_CADENCE[nextNextStep] - TOUCH_CADENCE[nextStep]; 317 await run( 318 `UPDATE sites 319 SET next_followup_at = NOW() + INTERVAL '${daysUntilNext} days', 320 updated_at = CURRENT_TIMESTAMP 321 WHERE id = $1`, 322 [site.id] 323 ); 324 } else { 325 // All follow-ups generated — clear next_followup_at 326 await run( 327 `UPDATE sites 328 SET next_followup_at = NULL, 329 updated_at = CURRENT_TIMESTAMP 330 WHERE id = $1`, 331 [site.id] 332 ); 333 } 334 } 335 336 return { generated, step: nextStep }; 337 } 338 339 /** 340 * Build a follow-up message using templates and spintax. 341 * 342 * @param {Object} params - Message parameters 343 * @returns {Object|null} {body, subject} or null if no template 344 */ 345 function buildFollowupMessage({ step, channel, site, scoreJson, businessName, grade, pricing, contactsJson }) { 346 // Try loading templates 347 const templates = loadFollowupTemplates(site.country_code, channel); 348 349 // Get the first contact name if available 350 const contacts = contactsJson?.contacts || []; 351 const contactName = contacts.find(c => c.name)?.name || null; 352 const firstname = contactName ? contactName.split(' ')[0] : null; 353 354 // Get weaknesses for different angles 355 const allWeaknesses = scoreJson?.factor_scores 356 ? Object.entries(scoreJson.factor_scores) 357 .filter(([, f]) => f.score < 5) 358 .sort((a, b) => a[1].score - b[1].score) 359 .map(([name, f]) => ({ 360 factor: name.replace(/_/g, ' '), 361 score: f.score, 362 reasoning: f.reasoning, 363 })) 364 : []; 365 366 const industry = scoreJson?.industry_classification || scoreJson?.overall_calculation?.industry_classification || 'business'; 367 const city = site.city || contactsJson?.city || ''; 368 369 if (!templates?.templates) { 370 logger.error(`No followup templates for ${site.country_code} ${channel} — skipping site ${site.id}`); 371 return null; 372 } 373 374 const stepTemplates = templates.templates.filter(t => t.step === step); 375 if (stepTemplates.length === 0) { 376 logger.error(`No followup templates for ${site.country_code} ${channel} step ${step} — skipping site ${site.id}`); 377 return null; 378 } 379 380 const template = stepTemplates[Math.floor(Math.random() * stepTemplates.length)]; 381 const body = resolveTemplate(template.body_spintax || template.body, { 382 firstname, 383 domain: site.domain, 384 grade, 385 score: Math.round(site.score), 386 industry, 387 city, 388 businessName, 389 pricing, 390 weakness: allWeaknesses[0]?.factor || 'homepage conversion', 391 weakness2: allWeaknesses[1]?.factor || 'call-to-action clarity', 392 reasoning: allWeaknesses[0]?.reasoning || '', 393 }); 394 const subject = template.subject_spintax 395 ? resolveTemplate(template.subject_spintax, { firstname, domain: site.domain, industry, businessName }) 396 : null; 397 return { body, subject }; 398 } 399 400 /** 401 * Resolve template variables and spintax. 402 */ 403 function resolveTemplate(template, vars) { 404 let text = template; 405 // Replace [var] placeholders 406 for (const [key, value] of Object.entries(vars)) { 407 if (value !== null && value !== undefined) { 408 text = text.replace(new RegExp(`\\[${key}\\]`, 'gi'), String(value)); 409 } 410 } 411 // Resolve [var|fallback] with fallback 412 text = text.replace(/\[(\w+)\|([^\]]+)\]/g, (match, key, fallback) => { 413 return vars[key] || fallback; 414 }); 415 // Resolve spintax 416 return spin(text); 417 } 418 419 /** 420 * Set next_followup_at on a site after the initial outreach is sent. 421 * Called from the outreach stage when a message is successfully delivered. 422 * 423 * @param {number} siteId - Site ID 424 * @returns {Promise<void>} 425 */ 426 export async function scheduleFirstFollowup(siteId) { 427 try { 428 // Only schedule if not already scheduled and site is in outreach_sent status 429 const site = await getOne( 430 `SELECT next_followup_at, status FROM sites WHERE id = $1`, 431 [siteId] 432 ); 433 434 if (!site) return; 435 436 // Schedule first follow-up (touch 2) for 3 days from now 437 // Only if next_followup_at is not already set 438 if (!site.next_followup_at) { 439 await run( 440 `UPDATE sites 441 SET next_followup_at = NOW() + INTERVAL '3 days', 442 updated_at = CURRENT_TIMESTAMP 443 WHERE id = $1 AND next_followup_at IS NULL`, 444 [siteId] 445 ); 446 } 447 } catch (e) { 448 logger.error(`Failed to schedule follow-up for site ${siteId}: ${e.message}`); 449 } 450 } 451 452 /** 453 * Run the follow-up generation stage. 454 * Finds sites due for follow-up and generates the next touch. 455 * 456 * @param {Object} options - Stage options 457 * @param {number} options.limit - Max sites to process per run 458 * @returns {Promise<Object>} Stage results 459 */ 460 export async function runFollowupGenerationStage(options = {}) { 461 const startTime = Date.now(); 462 const limit = options.limit || 20; 463 464 logger.info(`Starting Follow-up Generation (limit=${limit})...`); 465 466 const sites = await getFollowupEligibleSites(limit); 467 468 if (sites.length === 0) { 469 logger.info('No sites due for follow-up'); 470 return { 471 processed: 0, 472 generated: 0, 473 skipped: 0, 474 duration: Date.now() - startTime, 475 }; 476 } 477 478 logger.info(`Found ${sites.length} sites due for follow-up`); 479 480 let totalGenerated = 0; 481 let totalSkipped = 0; 482 483 for (const site of sites) { 484 try { 485 const result = await generateFollowupsForSite(site); 486 totalGenerated += result.generated; 487 if (result.generated === 0) totalSkipped++; 488 logger.info( 489 `Site ${site.id} (${site.domain}): generated ${result.generated} follow-up(s) ` + 490 `at step ${result.step || 'n/a'}${result.reason ? ` — ${result.reason}` : ''}` 491 ); 492 } catch (e) { 493 logger.error(`Failed to generate follow-up for site ${site.id}: ${e.message}`); 494 totalSkipped++; 495 } 496 } 497 498 const duration = Date.now() - startTime; 499 logger.info( 500 `Follow-up generation complete: ${totalGenerated} generated, ` + 501 `${totalSkipped} skipped in ${Math.round(duration / 1000)}s` 502 ); 503 504 return { 505 processed: sites.length, 506 generated: totalGenerated, 507 skipped: totalSkipped, 508 duration, 509 }; 510 }