SaaS 邮件营销自动化实战：45% 打开率背后的 7 个 Drip 工作流与数据库 Schema

-- Table 1: Email campaigns
CREATE TABLE email_campaigns (
  id TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  description TEXT,
  campaign_type TEXT NOT NULL CHECK (
    campaign_type IN (
      'welcome', 'drip', 'behavioural', 'transactional', 'promotional', 'newsletter'
    )
  ),
  status TEXT NOT NULL DEFAULT 'draft' CHECK (
    status IN ('draft', 'scheduled', 'active', 'paused', 'completed', 'archived')
  ),
  trigger_event TEXT, -- For behavioral: 'user_signup', 'trial_expiring', 'payment_failed', etc.
  trigger_delay_hours INTEGER DEFAULT 0,
  segment_filter JSON, -- SQL-like conditions stored as JSON
  from_name TEXT NOT NULL DEFAULT 'TanStack Ship Team',
  from_email TEXT NOT NULL DEFAULT 'hello@tanstack-ship.com',
  reply_to TEXT,
  created_at INTEGER NOT NULL DEFAULT (unixepoch()),
  updated_at INTEGER NOT NULL DEFAULT (unixepoch())
);

-- Table 2: Campaign emails (individual emails within a campaign)
CREATE TABLE campaign_emails (
  id TEXT PRIMARY KEY,
  campaign_id TEXT NOT NULL,
  subject TEXT NOT NULL,
  preheader TEXT, -- The preview text shown after subject in email clients
  template_name TEXT NOT NULL, -- References a template key
  template_data JSON DEFAULT '{}', -- Dynamic data for template rendering
  send_order INTEGER NOT NULL DEFAULT 0, -- Order within the campaign
  delay_hours INTEGER DEFAULT 0, -- Delay from campaign trigger or previous email
  is_ab_test INTEGER DEFAULT 0,
  ab_test_winner_id TEXT,
  created_at INTEGER NOT NULL DEFAULT (unixepoch()),
  FOREIGN KEY (campaign_id) REFERENCES email_campaigns(id)
);

-- Table 3: Email sending log (for tracking and analytics)
CREATE TABLE email_log (
  id TEXT PRIMARY KEY,
  campaign_email_id TEXT NOT NULL,
  user_id TEXT NOT NULL,
  recipient_email TEXT NOT NULL,
  subject TEXT NOT NULL,
  status TEXT NOT NULL CHECK (
    status IN ('queued', 'sent', 'delivered', 'opened', 'clicked', 'bounced', 'complained', 'failed')
  ),
  opens_count INTEGER NOT NULL DEFAULT 0,
  clicks_count INTEGER NOT NULL DEFAULT 0,
  last_opened_at INTEGER,
  last_clicked_at INTEGER,
  error_message TEXT,
  sent_at INTEGER,
  delivered_at INTEGER,
  created_at INTEGER NOT NULL DEFAULT (unixepoch()),
  FOREIGN KEY (campaign_email_id) REFERENCES campaign_emails(id),
  FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE INDEX idx_email_log_user ON email_log(user_id);
CREATE INDEX idx_email_log_status ON email_log(status);
CREATE INDEX idx_email_log_campaign ON email_log(campaign_email_id);

-- Table 4: Email templates (stored in database with MJML or HTML)
CREATE TABLE email_templates (
  id TEXT PRIMARY KEY,
  name TEXT NOT NULL UNIQUE,
  description TEXT,
  subject_template TEXT NOT NULL, -- With variables like {{product_name}}
  html_body TEXT NOT NULL, -- MJML or HTML
  plain_text_body TEXT,
  created_at INTEGER NOT NULL DEFAULT (unixepoch()),
  updated_at INTEGER NOT NULL DEFAULT (unixepoch())
);

// src/lib/email/templates/WelcomeEmail.tsx
import {
  Body,
  Button,
  Container,
  Head,
  Heading,
  Hr,
  Html,
  Img,
  Preview,
  Section,
  Text,
} from "react-email"

interface WelcomeEmailProps {
  username: string
  productName: string
  dashboardUrl: string
  tutorialUrl: string
  supportEmail: string
}

export function WelcomeEmail({
  username,
  productName,
  dashboardUrl,
  tutorialUrl,
  supportEmail,
}: WelcomeEmailProps) {
  return (
    <Html>
      <Head />
      <Preview>Welcome to {productName} — Your First Project Awaits</Preview>
      <Body style={{ fontFamily: "sans-serif", padding: "20px" }}>
        <Container>
          <Img
            src="https://tanstackship.com/logo.png"
            alt={productName}
            width="48"
            height="48"
          />
          <Heading>Welcome, {username}! 👋</Heading>
          <Text>
            We are excited to have you onboard. Here is your personalized
            getting-started checklist:
          </Text>

          <Section style={{ padding: "20px", background: "#f9f9f9", borderRadius: "8px" }}>
            <Text>✅ Set up your first project</Text>
            <Text>✅ Configure your domain</Text>
            <Text>✅ Connect your payment provider</Text>
            <Text>✅ Invite your team members</Text>
          </Section>

          <Section style={{ textAlign: "center", margin: "20px 0" }}>
            <Button
              href={dashboardUrl}
              style={{
                background: "#2563eb",
                color: "white",
                padding: "12px 24px",
                borderRadius: "8px",
                textDecoration: "none",
              }}
            >
              Go to Dashboard
            </Button>
          </Section>

          <Text>
            New to {productName}?{" "}
            <a href={tutorialUrl}>Watch the 5-minute getting started tutorial →</a>
          </Text>

          <Hr />
          <Text style={{ color: "#666", fontSize: "12px" }}>
            Need help? Reply to this email or contact {supportEmail}
          </Text>
        </Container>
      </Body>
    </Html>
  )
}

// src/lib/email/render.ts
import { render } from "react-email/components"
import { WelcomeEmail } from "./templates/WelcomeEmail"

interface RenderEmailOptions {
  templateName: string
  data: Record<string, unknown>
}

export async function renderEmail({
  templateName,
  data,
}: RenderEmailOptions): Promise<{ html: string; subject: string }> {
  switch (templateName) {
    case "welcome": {
      const props = data as WelcomeEmailProps
      const html = await render(<WelcomeEmail {...props} />)
      const subject = `Welcome to ${props.productName}!`
      return { html, subject }
    }
    // 按需增加更多模板分支
    default:
      throw new Error(`Unknown template: ${templateName}`)
  }
}

// src/lib/email/send.ts
import { createServerFn } from "@tanstack/react-start"

interface SendEmailParams {
  to: string
  subject: string
  html: string
  text?: string
  headers?: Record<string, string>
}

// 使用 Cloudflare Workers 内置的邮件发送（需要 SendGrid 或类似服务）

export async function sendEmail(params: SendEmailParams) {
  const { to, subject, html, text, headers } = params

  // 方式 1：SendGrid API
  const response = await fetch("https://api.sendgrid.com/v3/mail/send", {
    method: "POST",
    headers: {
      Authorization: `Bearer ${process.env.SENDGRID_API_KEY}`,
      "Content-Type": "application/json",
    },
    body: JSON.stringify({
      personalizations: [{ to: [{ email: to }] }],
      from: { email: "hello@tanstack-ship.com", name: "TanStack Ship Team" },
      subject,
      content: [
        { type: "text/html", value: html },
        ...(text ? [{ type: "text/plain", value: text }] : []),
      ],
      headers: {
        "X-Campaign-Id": headers?.["X-Campaign-Id"] ?? "",
        ...headers,
      },
      tracking_settings: {
        open_tracking: { enable: true },
        click_tracking: { enable: true, enable_text: true },
      },
    }),
  })

  if (!response.ok) {
    const error = await response.text()
    console.error("Email send failed:", error)
    return { success: false, error }
  }

  return { success: true, messageId: response.headers.get("X-Message-Id") }
}

// src/lib/email/engine.ts
import { createServerFn } from "@tanstack/react-start"

// 由 Cron 触发器驱动（Cloudflare Workers Cron）
export const processEmailQueue = createServerFn({ method: "POST" }).handler(
  async () => {
    // 第 1 步：根据触发器找出需要收邮件的用户
    const pendingUsers = await findPendingUsers()
    const results = []

    for (const user of pendingUsers) {
      try {
        const result = await processUserCampaign(user)
        results.push(result)
      } catch (error) {
        console.error(`Failed to process campaign for user ${user.id}:`, error)
      }
    }

    return { processed: results.length }
  }
)

async function findPendingUsers(): Promise<UserRecord[]> {
  // 示例：找 24 小时前注册但尚未完成新手引导的用户
  return env.DB.prepare(`
    SELECT u.* FROM users u
    WHERE u.created_at <= unixepoch() - 86400
    AND u.id NOT IN (
      SELECT user_id FROM activation_events
      WHERE event_type = 'completed_onboarding'
    )
    AND u.id NOT IN (
      SELECT user_id FROM email_log el
      JOIN campaign_emails ce ON ce.id = el.campaign_email_id
      JOIN email_campaigns ec ON ec.id = ce.campaign_id
      WHERE ec.trigger_event = 'onboarding_incomplete'
      AND el.created_at > unixepoch() - 604800 -- 最近 7 天
    )
    LIMIT 100
  `).all() as Promise<unknown> as Promise<UserRecord[]>
}

async function processUserCampaign(user: UserRecord) {
  // 获取活动及其邮件
  const campaign = await env.DB.prepare(`
    SELECT * FROM email_campaigns
    WHERE trigger_event = 'onboarding_incomplete'
    AND status = 'active'
    LIMIT 1
  `).first() as EmailCampaign | null

  if (!campaign) return null

  const emails = await env.DB.prepare(`
    SELECT * FROM campaign_emails
    WHERE campaign_id = ?
    ORDER BY send_order ASC
  `).bind(campaign.id).all() as { results: CampaignEmail[] }

  // 找出用户进行到哪一封
  const lastSent = await env.DB.prepare(`
    SELECT ce.send_order
    FROM email_log el
    JOIN campaign_emails ce ON ce.id = el.campaign_email_id
    WHERE el.user_id = ? AND el.status IN ('sent', 'delivered', 'opened', 'clicked')
    ORDER BY el.created_at DESC
    LIMIT 1
  `).bind(user.id).first() as { send_order: number } | null

  const nextOrder = (lastSent?.send_order ?? -1) + 1
  const nextEmail = emails.results.find((e) => e.send_order === nextOrder)

  if (!nextEmail) return null

  // 渲染并发送
  const { html, subject } = await renderEmail({
    templateName: nextEmail.template_name,
    data: {
      username: user.name ?? user.email,
      productName: "TanStack Ship",
      dashboardUrl: `https://tanstackship.com/dashboard?utm_source=email&utm_medium=email&utm_campaign=${campaign.name}`,
      tutorialUrl: "https://tanstackship.com/blog/tanstack-start-end-to-end-tutorial",
      supportEmail: "support@tanstack-ship.com",
    },
  })

  const sendResult = await sendEmail({
    to: user.email,
    subject,
    html,
    headers: {
      "X-Campaign-Id": campaign.id,
      "X-Campaign-Email-Id": nextEmail.id,
    },
  })

  // 记录发送日志
  await env.DB.prepare(
    `INSERT INTO email_log (id, campaign_email_id, user_id, recipient_email, subject, status, sent_at)
     VALUES (?, ?, ?, ?, ?, 'sent', unixepoch())`
  ).bind(
    crypto.randomUUID(),
    nextEmail.id,
    user.id,
    user.email,
    subject
  ).run()

  return { userId: user.id, emailId: nextEmail.id, sent: sendResult.success }
}

export const checkTrialExpiring = createServerFn({ method: "POST" }).handler(
  async () => {
    // 找出 3 天后试用到期的用户
    const expiringUsers = await env.DB.prepare(`
      SELECT u.* FROM users u
      WHERE u.created_at >= unixepoch() - (14 * 86400) - (3 * 86400)
      AND u.created_at <= unixepoch() - (14 * 86400) - (2 * 86400)
      AND u.id NOT IN (SELECT user_id FROM subscriptions WHERE status = 'active')
      AND u.id NOT IN (
        SELECT user_id FROM email_log
        WHERE subject LIKE '%trial ending%'
        AND created_at > unixepoch() - 604800
      )
    `).all()

    for (const user of expiringUsers) {
      await sendEmail({
        to: user.email,
        subject: "Your trial ends in 3 days — here is a special offer",
        html: await renderTrialExpiryEmail(user),
      })
    }

    return { notified: expiringUsers.length }
  }
)

export const handleFailedPayment = createServerFn({ method: "POST" }).handler(
  async ({ subscriptionId }: { subscriptionId: string }) => {
    const subscription = await env.DB.prepare(`
      SELECT s.*, u.email, u.name FROM subscriptions s
      JOIN users u ON u.id = s.user_id
      WHERE s.id = ?
    `).bind(subscriptionId).first() as Subscription & { email: string; name: string }

    // 统计扣款失败次数
    const failureCount = await env.DB.prepare(`
      SELECT COUNT(*) as count FROM email_log
      WHERE user_id = ? AND subject LIKE '%payment%failed%'
    `).bind(subscription.user_id).first() as { count: number }

    const templates = [
      { delay: 0, subject: "Your payment failed — update your billing info" },
      { delay: 48, subject: "Second attempt failed — your access will be paused" },
      { delay: 96, subject: "Last chance: your account will be downgraded" },
    ]

    const template = templates[failureCount.count] ?? templates[templates.length - 1]

    await sendEmail({
      to: subscription.email,
      subject: template.subject,
      html: await renderPaymentFailedEmail(subscription, failureCount.count),
    })
  }
)

// src/server/email-stats.ts
export const getEmailAnalytics = createServerFn({ method: "GET" }).handler(
  async () => {
    // 整体活动表现
    const campaignStats = await env.DB.prepare(`
      SELECT
        ec.name,
        ec.campaign_type,
        COUNT(DISTINCT el.user_id) as recipients,
        COUNT(DISTINCT CASE WHEN el.status = 'opened' THEN el.user_id END) as opens,
        COUNT(DISTINCT CASE WHEN el.status = 'clicked' THEN el.user_id END) as clicks,
        COUNT(DISTINCT CASE WHEN el.status = 'bounced' THEN el.user_id END) as bounces,
        ROUND(100.0 * COUNT(DISTINCT CASE WHEN el.status = 'opened' THEN el.user_id END) /
          NULLIF(COUNT(DISTINCT el.user_id), 0), 1) as open_rate,
        ROUND(100.0 * COUNT(DISTINCT CASE WHEN el.status = 'clicked' THEN el.user_id END) /
          NULLIF(COUNT(DISTINCT CASE WHEN el.status IN ('opened', 'clicked', 'delivered') THEN el.user_id END), 0), 1) as click_rate
      FROM email_campaigns ec
      JOIN campaign_emails ce ON ce.campaign_id = ec.id
      JOIN email_log el ON el.campaign_email_id = ce.id
      GROUP BY ec.id
      ORDER BY ec.created_at DESC
    `).all()

    // 转化追踪：邮件 → 付费
    const emailToRevenue = await env.DB.prepare(`
      SELECT
        ec.name as campaign_name,
        COUNT(DISTINCT el.user_id) as emailed_users,
        COUNT(DISTINCT CASE WHEN s.status = 'active' THEN el.user_id END) as converted_to_paid,
        ROUND(100.0 * COUNT(DISTINCT CASE WHEN s.status = 'active' THEN el.user_id END) /
          NULLIF(COUNT(DISTINCT el.user_id), 0), 2) as conversion_rate
      FROM email_campaigns ec
      JOIN campaign_emails ce ON ce.campaign_id = ec.id
      JOIN email_log el ON el.campaign_email_id = ce.id
      LEFT JOIN subscriptions s ON s.user_id = el.user_id AND s.status = 'active'
      GROUP BY ec.id
    `).all()

    return { campaignStats, emailToRevenue }
  }
)

# wrangler.jsonc — 邮件送达率所需的 DNS 记录
{
  "dns": {
    "records": [
      { "type": "TXT", "name": "@", "value": "v=spf1 include:sendgrid.net ~all" },
      { "type": "TXT", "name": "s1._domainkey", "value": "DKIM_KEY_HERE" },
      { "type": "TXT", "name": "_dmarc", "value": "v=DMARC1; p=quarantine; rua=mailto:dmarc@tanstack-ship.com" },
    ]
  }
}