send-scan-email-sequence.js
1 #!/usr/bin/env node 2 3 /** 4 * Send Scan Email Sequence 5 * 6 * Runs every 10 minutes. Fires post-scan nurture emails to opted-in 7 * non-purchasers. 7-email sequence over 14 days across 3 score segments. 8 * 9 * Enrolment happens in poll-free-scans.js (via enrollScanEmailSequence) 10 * when a free_scans row is archived with marketing_optin = 1. 11 * 12 * Exit conditions (any stops the sequence): 13 * - Purchase of any product detected in purchases table 14 * - Unsubscribe (status = 'unsubscribed') 15 * - Hard bounce (status = 'bounced') 16 * 17 * Send window: Mon-Fri, 9am-6pm local time per country_code. 18 * Sends outside the window are queued to next available morning slot. 19 */ 20 21 import crypto from 'crypto'; 22 import { getOne, getAll, run } from './../utils/db.js'; 23 import { Resend } from 'resend'; 24 import { getEmailTemplate } from '../reports/scan-email-templates.js'; 25 import Logger from '../utils/logger.js'; 26 import '../utils/load-env.js'; 27 28 const logger = new Logger('ScanEmailSequence'); 29 30 // ── Localised pricing ────────────────────────────────────────────────────── 31 32 const PRICING = { 33 AU: { quick_fixes: 'A$97', full_audit: 'A$337', audit_fix: 'A$625' }, 34 GB: { quick_fixes: '£47', full_audit: '£159', audit_fix: '£350' }, 35 US: { quick_fixes: '$67', full_audit: '$297', audit_fix: '$497' }, 36 }; 37 38 function getPriceTokens(countryCode) { 39 const cc = (countryCode || 'US').toUpperCase(); 40 const p = PRICING[cc] || PRICING.US; 41 return { 42 price_quickfixes: p.quick_fixes, 43 price_fullaudit: p.full_audit, 44 price_auditfix: p.audit_fix, 45 }; 46 } 47 48 // ── Timezone / send-window ───────────────────────────────────────────────── 49 50 const COUNTRY_TIMEZONES = { 51 AU: 'Australia/Sydney', 52 GB: 'Europe/London', 53 US: 'America/New_York', 54 CA: 'America/Toronto', 55 NZ: 'Pacific/Auckland', 56 IE: 'Europe/Dublin', 57 }; 58 59 /** 60 * Return the local Date object for a given UTC datetime and country_code. 61 */ 62 function toLocalDate(utcDateStr, countryCode) { 63 const tz = COUNTRY_TIMEZONES[(countryCode || 'US').toUpperCase()] || 'America/New_York'; 64 const utcMs = utcDateStr ? new Date(utcDateStr).getTime() : Date.now(); 65 const formatter = new Intl.DateTimeFormat('en-CA', { 66 timeZone: tz, 67 year: 'numeric', month: '2-digit', day: '2-digit', 68 hour: '2-digit', minute: '2-digit', hour12: false, 69 }); 70 const parts = Object.fromEntries(formatter.formatToParts(new Date(utcMs)).map(p => [p.type, p.value])); 71 return { 72 dayOfWeek: new Date(utcMs).toLocaleDateString('en-US', { timeZone: tz, weekday: 'long' }), 73 hour: parseInt(parts.hour, 10), 74 }; 75 } 76 77 /** 78 * Is now within the Mon-Fri 9am-6pm local window for this country? 79 */ 80 function isInSendWindow(countryCode) { 81 const { dayOfWeek, hour } = toLocalDate(null, countryCode); 82 const isWeekday = !['Saturday', 'Sunday'].includes(dayOfWeek); 83 return isWeekday && hour >= 9 && hour < 18; 84 } 85 86 /** 87 * Return the UTC datetime string for the next Mon-Fri 9am local morning. 88 */ 89 function nextMorningUTC(countryCode) { 90 const now = new Date(); 91 92 // Walk forward hour by hour until we land in the send window 93 const candidate = new Date(now); 94 candidate.setMinutes(0, 0, 0); 95 candidate.setHours(candidate.getHours() + 1); 96 97 for (let i = 0; i < 200; i++) { 98 const { dayOfWeek, hour } = toLocalDate(candidate.toISOString(), countryCode); 99 const isWeekday = !['Saturday', 'Sunday'].includes(dayOfWeek); 100 if (isWeekday && hour >= 9 && hour < 18) { 101 return candidate.toISOString(); 102 } 103 candidate.setHours(candidate.getHours() + 1); 104 } 105 106 // Fallback: 24h from now 107 return new Date(Date.now() + 24 * 3600 * 1000).toISOString(); 108 } 109 110 // ── Send delays between emails (in hours) ──────────────────────────────── 111 112 const SEND_DELAYS_HOURS = [ 113 0, // Email 1: immediate (handled at enrolment) 114 18, // Email 2: +18h after email 1 115 48, // Email 3: +48h after email 2 (day 3) 116 48, // Email 4: +48h after email 3 (day 5) 117 48, // Email 5: +48h after email 4 (day 7) 118 72, // Email 6: +72h after email 5 (day 10) 119 96, // Email 7: +96h after email 6 (day 14) 120 ]; 121 122 // ── HMAC token for unsubscribe ──────────────────────────────────────────── 123 124 function generateUnsubToken(seqId, email) { 125 const secret = process.env.UNSUBSCRIBE_SECRET || 'change-me-in-production'; 126 return crypto.createHmac('sha256', secret).update(`seq:${seqId}:${email}`).digest('hex').substring(0, 24); 127 } 128 129 function unsubscribeUrl(seqId, email) { 130 const base = process.env.UNSUBSCRIBE_BASE_URL || 'https://auditandfix.com/unsubscribe'; 131 const token = generateUnsubToken(seqId, email); 132 return `${base}?seq_id=${seqId}&token=${token}`; 133 } 134 135 // ── Score factor labels ─────────────────────────────────────────────────── 136 137 const FACTOR_LABELS = { 138 headline_quality: 'Headline & Value Prop', 139 call_to_action: 'Call to Action', 140 trust_signals: 'Trust Signals', 141 urgency_messaging: 'Urgency & Availability', 142 value_proposition: 'Value Proposition', 143 hook_engagement: 'Page Hook & First Impression', 144 mobile_experience: 'Mobile Experience', 145 contact_accessibility:'Contact Accessibility', 146 social_proof: 'Social Proof', 147 visual_hierarchy: 'Visual Hierarchy', 148 }; 149 150 /** 151 * Parse score_json and return sorted factor array + worst/second worst. 152 * score_json shape: { headline_quality: 7.5, call_to_action: 4.2, ... } 153 */ 154 function parseScoreFactors(scoreJson) { 155 let factors = {}; 156 try { 157 factors = typeof scoreJson === 'string' ? JSON.parse(scoreJson) : (scoreJson || {}); 158 } catch { 159 return { factors: [], worst_factor_key: null, worst_factor_label: null, worst_factor_score: null, second_worst_label: null }; 160 } 161 162 const sorted = Object.entries(factors) 163 .map(([key, score]) => ({ key, score: parseFloat(score) || 0, label: FACTOR_LABELS[key] || key })) 164 .sort((a, b) => a.score - b.score); 165 166 return { 167 factors: sorted, 168 worst_factor_key: sorted[0]?.key || null, 169 worst_factor_label: sorted[0]?.label || null, 170 worst_factor_score: sorted[0]?.score ?? null, 171 second_worst_label: sorted[1]?.label || null, 172 }; 173 } 174 175 // ── Purchase detection ───────────────────────────────────────────────────── 176 177 async function hasPurchased(email) { 178 const row = await getOne( 179 `SELECT id FROM purchases WHERE email = $1 AND status NOT IN ('failed','refunded') LIMIT 1`, 180 [email] 181 ); 182 return !!row; 183 } 184 185 // ── Segment from score ───────────────────────────────────────────────────── 186 187 function scoreToSegment(score) { 188 if (score <= 59) return 'A'; 189 if (score <= 76) return 'B'; 190 return 'C'; 191 } 192 193 // ── Enrolment ───────────────────────────────────────────────────────────── 194 195 /** 196 * Enrol a free scan into the nurture sequence. 197 * Called from poll-free-scans.js after archiving a row with marketing_optin = 1. 198 * 199 * @param {object} scan - free_scans row 200 * @returns {Promise<{ enrolled: boolean, reason?: string }>} 201 */ 202 export async function enrollScanEmailSequence(scan) { 203 if (!scan.email || !scan.marketing_optin) { 204 return { enrolled: false, reason: 'no_email_or_no_optin' }; 205 } 206 207 // Already enrolled? 208 const existing = await getOne( 209 'SELECT id FROM scan_email_sequence WHERE scan_id = $1', 210 [scan.scan_id] 211 ); 212 if (existing) { 213 return { enrolled: false, reason: 'already_enrolled' }; 214 } 215 216 // Already purchased? 217 if (await hasPurchased(scan.email)) { 218 return { enrolled: false, reason: 'already_purchased' }; 219 } 220 221 const score = parseFloat(scan.score) || 0; 222 const segment = scoreToSegment(score); 223 224 // Email 1 is sent immediately — next_send_at = now 225 const result = await run( 226 `INSERT INTO scan_email_sequence 227 (scan_id, email, segment, country_code, score, grade, domain, score_json, 228 next_email_num, next_send_at, status) 229 VALUES ($1, $2, $3, $4, $5, $6, $7, $8, 1, $9, 'active') 230 ON CONFLICT DO NOTHING`, 231 [ 232 scan.scan_id, 233 scan.email, 234 segment, 235 scan.country_code || 'US', 236 score, 237 scan.grade || null, 238 scan.domain, 239 scan.score_json || null, 240 new Date().toISOString(), 241 ] 242 ); 243 244 if (result.changes === 0) { 245 return { enrolled: false, reason: 'insert_ignored' }; 246 } 247 248 const newId = result.lastInsertRowid; 249 const token = generateUnsubToken(newId, scan.email); 250 await run('UPDATE scan_email_sequence SET unsubscribe_token = $1 WHERE id = $2', [token, newId]); 251 252 logger.info(`Enrolled scan ${scan.scan_id} (${scan.email}) into sequence — segment ${segment}`); 253 return { enrolled: true, seqId: newId, segment }; 254 } 255 256 // ── Send one email ───────────────────────────────────────────────────────── 257 258 async function sendSequenceEmail(resend, seq, emailNum) { 259 const senderEmail = process.env.AUDITANDFIX_SENDER_EMAIL || 'marcus@auditandfix.com'; 260 const senderName = 'Marcus @ Audit&Fix'; 261 const baseUrl = process.env.AUDITANDFIX_BASE_URL || 'https://www.auditandfix.com'; 262 263 const { factors, worst_factor_key, worst_factor_label, worst_factor_score, second_worst_label } = 264 parseScoreFactors(seq.score_json); 265 266 const priceTokens = getPriceTokens(seq.country_code); 267 const domain = seq.domain || 'your website'; 268 269 const tokens = { 270 domain, 271 score: Math.round(seq.score || 0), 272 grade: seq.grade || 'N/A', 273 worst_factor_key, 274 worst_factor_label: worst_factor_label || 'your lowest-scoring factor', 275 worst_factor_score: worst_factor_score ?? 0, 276 second_worst_label: second_worst_label || 'your second-lowest factor', 277 factors, 278 ...priceTokens, 279 order_url_qf: `${baseUrl}/?domain=${encodeURIComponent(domain)}&product=quick_fixes#order`, 280 order_url_fa: `${baseUrl}/?domain=${encodeURIComponent(domain)}&product=full_audit#order`, 281 scan_url: `${baseUrl}/scan?url=${encodeURIComponent(`https://${domain}`)}&ref=email`, 282 unsubscribe_url: unsubscribeUrl(seq.id, seq.email), 283 country_code: seq.country_code || 'US', 284 }; 285 286 const template = getEmailTemplate(emailNum, seq.segment, tokens); 287 288 const result = await resend.emails.send({ 289 from: `${senderName} <${senderEmail}>`, 290 to: seq.email, 291 subject: template.subject, 292 html: template.html, 293 text: template.text, 294 tags: [ 295 { name: 'sequence', value: 'post_scan' }, 296 { name: 'email_num', value: String(emailNum) }, 297 { name: 'segment', value: seq.segment }, 298 ], 299 }); 300 301 if (result.error) { 302 throw new Error(`Resend error: ${result.error.message} (${result.error.name})`); 303 } 304 305 logger.success(`Sent email ${emailNum}/7 to ${seq.email} (seq #${seq.id}, segment ${seq.segment})`); 306 return result.id; 307 } 308 309 // ── Schedule next send ───────────────────────────────────────────────────── 310 311 async function scheduleNext(seqId, currentEmailNum, countryCode) { 312 const nextNum = currentEmailNum + 1; 313 314 if (nextNum > 7) { 315 await run( 316 `UPDATE scan_email_sequence SET status = 'completed', next_email_num = 8, next_send_at = NULL WHERE id = $1`, 317 [seqId] 318 ); 319 return; 320 } 321 322 const delayHours = SEND_DELAYS_HOURS[nextNum - 1] || 24; 323 const candidateUTC = new Date(Date.now() + delayHours * 3600 * 1000).toISOString(); 324 325 // Check if candidate falls in the send window; if not, push to next morning 326 const { dayOfWeek, hour } = toLocalDate(candidateUTC, countryCode); 327 const isWeekday = !['Saturday', 'Sunday'].includes(dayOfWeek); 328 const inWindow = isWeekday && hour >= 9 && hour < 18; 329 330 const nextSendAt = inWindow ? candidateUTC : nextMorningUTC(countryCode); 331 332 await run( 333 `UPDATE scan_email_sequence 334 SET next_email_num = $1, next_send_at = $2, last_sent_at = NOW(), updated_at = NOW() 335 WHERE id = $3`, 336 [nextNum, nextSendAt, seqId] 337 ); 338 } 339 340 // ── Main ─────────────────────────────────────────────────────────────────── 341 342 /** 343 * Process all due sequence emails. 344 * @returns {{ checked: number, sent: number, skipped: number, failed: number }} 345 */ 346 export async function sendScanEmailSequence() { 347 const apiKey = process.env.RESEND_API_KEY; 348 if (!apiKey) { 349 logger.warn('RESEND_API_KEY not configured, skipping'); 350 return { checked: 0, sent: 0, skipped: 0, failed: 0 }; 351 } 352 353 const resend = new Resend(apiKey); 354 355 let checked = 0; 356 let sent = 0; 357 let skipped = 0; 358 let failed = 0; 359 360 const due = await getAll(` 361 SELECT * FROM scan_email_sequence 362 WHERE status = 'active' 363 AND next_send_at IS NOT NULL 364 AND next_send_at <= NOW() 365 ORDER BY next_send_at ASC 366 LIMIT 50 367 `); 368 369 if (due.length === 0) { 370 logger.info('No emails due'); 371 return { checked: 0, sent: 0, skipped: 0, failed: 0 }; 372 } 373 374 logger.info(`Processing ${due.length} due sequence email(s)`); 375 376 for (const seq of due) { 377 checked++; 378 379 // Exit: purchased since enrolment 380 if (await hasPurchased(seq.email)) { 381 await run( 382 `UPDATE scan_email_sequence 383 SET status = 'purchased', purchase_detected_at = NOW(), next_send_at = NULL 384 WHERE id = $1`, 385 [seq.id] 386 ); 387 logger.info(`Stopped sequence for ${seq.email} — purchase detected`); 388 skipped++; 389 continue; 390 } 391 392 // Send window check — if not in window, reschedule to next morning and continue 393 if (!isInSendWindow(seq.country_code)) { 394 const newTime = nextMorningUTC(seq.country_code); 395 await run( 396 `UPDATE scan_email_sequence SET next_send_at = $1 WHERE id = $2`, 397 [newTime, seq.id] 398 ); 399 logger.info(`Outside send window for ${seq.email} (${seq.country_code}), rescheduled to ${newTime}`); 400 skipped++; 401 continue; 402 } 403 404 try { 405 await sendSequenceEmail(resend, seq, seq.next_email_num); 406 await scheduleNext(seq.id, seq.next_email_num, seq.country_code); 407 sent++; 408 } catch (err) { 409 logger.error(`Failed to send email ${seq.next_email_num} to ${seq.email}`, err); 410 411 // Hard bounce → stop sequence 412 const errMsg = err.message || ''; 413 if (errMsg.includes('bounce') || errMsg.includes('invalid_to')) { 414 await run( 415 `UPDATE scan_email_sequence SET status = 'bounced', next_send_at = NULL WHERE id = $1`, 416 [seq.id] 417 ); 418 logger.warn(`Marked ${seq.email} as bounced, stopping sequence`); 419 } 420 // Soft failure — leave as active, will retry next run (same next_send_at) 421 422 failed++; 423 } 424 } 425 426 logger.success(`Sequence run complete: ${sent} sent, ${skipped} skipped, ${failed} failed`); 427 return { checked, sent, skipped, failed }; 428 } 429 430 // ── CLI ──────────────────────────────────────────────────────────────────── 431 432 if (import.meta.url === `file://${process.argv[1]}`) { 433 sendScanEmailSequence() 434 .then(result => { 435 console.log('Result:', result); 436 process.exit(result.failed > 0 ? 1 : 0); 437 }) 438 .catch(err => { 439 logger.error('Fatal error:', err); 440 process.exit(1); 441 }); 442 }