邮件营销活动管理——完整工作流

目的：可复用的工作流，用于创建、追踪和管理邮件营销活动，支持转化追踪、提醒邮件发送及试用期激活。

适用范围：增长营销、邮件活动、转化追踪、试用期激活、活动数据分析

活动数据库结构

campaigns 表

CREATE TABLE campaigns (
    id UUID PRIMARY KEY,
    campaign_id VARCHAR(255) UNIQUE NOT NULL,  -- 例如 "ny2026", "summer2026"
    name VARCHAR(255) NOT NULL,
    description TEXT,
    trial_days INTEGER,
    tier VARCHAR(50),  -- ENTERPRISE, SCALE 等
    plan_name TEXT,
    offer_expires_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

campaign_clicks 表

CREATE TABLE campaign_clicks (
    id UUID PRIMARY KEY,
    campaign_id VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    click_source VARCHAR(100),  -- 例如 "cta_button", "events_click"
    utm_source VARCHAR(100),
    utm_medium VARCHAR(100),
    utm_campaign VARCHAR(100),
    converted BOOLEAN DEFAULT FALSE,
    converted_at TIMESTAMP,
    user_id UUID,
    created_at TIMESTAMP DEFAULT NOW()
);

活动统计查询模式

获取活动概览

SELECT
    COUNT(*) as total_clicks,
    COUNT(DISTINCT email) as unique_users,
    COUNT(CASE WHEN converted = TRUE THEN 1 END) as converted_count,
    COUNT(CASE WHEN converted = FALSE THEN 1 END) as pending_count,
    ROUND(100.0 * COUNT(CASE WHEN converted = TRUE THEN 1 END) / COUNT(*), 2) as conversion_rate
FROM campaign_clicks
WHERE campaign_id = 'campaign-id-here';

已注册用户 vs 未注册用户

SELECT
    COUNT(CASE WHEN u.id IS NOT NULL THEN 1 END) as registered_users,
    COUNT(CASE WHEN u.id IS NULL THEN 1 END) as non_registered_users,
    COUNT(CASE WHEN s.tier != 'free' AND s.status = 'active' THEN 1 END) as already_paid_users
FROM campaign_clicks cc
LEFT JOIN users u ON u.email = cc.email
LEFT JOIN subscriptions s ON s.user_id = u.id
WHERE cc.campaign_id = 'campaign-id-here'
  AND cc.converted = FALSE;

Resend API 集成

速率限制（关键）

Resend 免费版限制：每秒 2 次请求（注意：不是每分钟 100 次！）

正确实现方式：

import time

RATE_LIMIT_DELAY = 0.5  # 每秒发送 2 封邮件

for email in emails:
    send_email(email)
    if i < len(emails):
        time.sleep(RATE_LIMIT_DELAY)  # 等待 0.5 秒

错误实现方式（将导致 429 错误）：

# 切勿如此操作 —— 发送速度过快
RATE_LIMIT = 100  # 每分钟 100 封邮件
for email in emails:
    send_email(email)
    # 每 100 封才暂停一次 —— 为时已晚！
    if i % RATE_LIMIT == 0:
        time.sleep(60)

邮件发送函数

import os
import requests

RESEND_API_KEY = os.getenv("RESEND_API_KEY")
RESEND_API_URL = "https://api.resend.com/emails"
FROM_EMAIL = "no-reply@ainative.studio"

def send_campaign_email(email: str, campaign_id: str, template_html: str):
    """通过 Resend 发送活动邮件。"""

    # 替换模板变量
    html = template_html.replace("{{email}}", email)
    html = html.replace("{{campaign_id}}", campaign_id)

    payload = {
        "from": FROM_EMAIL,
        "to": [email],
        "subject": "Your campaign subject here",
        "html": html,
        "tags": [
            {"name": "campaign", "value": campaign_id},
            {"name": "type", "value": "reminder"}
        ]
    }

    response = requests.post(
        RESEND_API_URL,
        headers={
            "Authorization": f"Bearer {RESEND_API_KEY}",
            "Content-Type": "application/json"
        },
        json=payload,
        timeout=10
    )

    if response.status_code in [200, 201]:
        return True, response.json().get("id")
    else:
        return False, response.text

试用期激活工作流

激活试用期（关键：使用大写枚举值）

def activate_trial(user, campaign_id: str, trial_days: int, plan_name: str):
    """为用户激活 ENTERPRISE 试用期。"""

    trial_end = datetime.utcnow() + timedelta(days=trial_days)

    # 关键：PostgreSQL 枚举值为大写
    # 'enterprise' → 'ENTERPRISE'
    # 'active' 而非 'trial'（不存在 trial 状态）

    if user['subscription_id']:
        # 更新已有订阅
        cur.execute("""
            UPDATE subscriptions
            SET tier = 'ENTERPRISE',
                status = 'active',
                trial_ends_at = %s,
                plan_name = %s,
                plan_price = 0,
                current_period_end = %s,
                updated_at = NOW()
            WHERE id = %s
        """, (trial_end, plan_name, trial_end, user['subscription_id']))
    else:
        # 创建新订阅
        cur.execute("""
            INSERT INTO subscriptions (
                user_id, tier, status,
                trial_ends_at, plan_name, plan_price,
                billing_email, start_date,
                current_period_start, current_period_end,
                max_users, max_projects, monthly_token_limit,
                max_ai_requests_per_day, max_models,
                created_at, updated_at
            ) VALUES (
                %s, 'ENTERPRISE', 'active',
                %s, %s, 0,
                %s, NOW(),
                NOW(), %s,
                10, 15, 1000000,
                10000, 100,
                NOW(), NOW()
            )
        """, (
            str(user['user_id']),
            trial_end,
            plan_name,
            user['email'],
            trial_end
        ))

    # 标记点击记录为已转化
    cur.execute("""
        UPDATE campaign_clicks
        SET converted = TRUE,
            converted_at = NOW(),
            user_id = %s
        WHERE id = %s
    """, (str(user['user_id']), user['click_id']))

    conn.commit()

常见陷阱与解决方案

陷阱 1：枚举值大小写敏感

问题：'enterprise' 报错 “invalid input value for enum” 解决方案：始终使用大写形式：'ENTERPRISE'、'SCALE'、'FREE'

陷阱 2：无效的状态值

问题：使用 status = 'trial'（该状态不存在） 解决方案：使用 status = 'active' 并配合 trial_ends_at 字段

陷阱 3：速率限制设置过高

问题：将速率限制设为每分钟 100 次，导致 429 错误 解决方案：采用每秒 2 次（两次发送之间延迟 0.5 秒）

陷阱 4：缺失组织 ID

问题：部分用户无 organization_id，引发 NULL 约束错误 解决方案：INSERT 前检查 organization_id 是否存在；若为 NULL，则单独执行查询

陷阱 5：模板中硬编码邮箱地址

问题：生产环境模板中残留测试邮箱 解决方案：使用 {{email}} 模板变量，并在发送时动态替换

陷阱 6：未关闭数据库连接

问题：“too many clients already” 错误 解决方案：始终使用 try/finally 确保连接被关闭

邮件模板要求

模板结构

所有活动邮件模板必须存放在：

src/backend/app/services/templates/{campaign_id}_{type}.html

示例文件名：

• ny2026_gift.html —— 初始活动邮件
• ny2026_reminder.html —— 面向未注册用户的提醒邮件
• summer2026_welcome.html —— 欢迎邮件

必需的模板变量

    Claim Your Trial

样式规范（深色主题）

/* 基础配色 */
body {
    font-family: 'Poppins', -apple-system, BlinkMacSystemFont, 'Segoe UI', Roboto, sans-serif;
    background-color: #131726;
}

.email-container {
    background-color: #22263c;
    border-radius: 16px;
}

/* 头部渐变 */
.header {
    background: linear-gradient(135deg, #4B6FED 0%, #5867EF 100%);
    padding: 50px 40px;
}

/* 主要操作按钮（CTA） */
.cta-button {
    background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);
    color: #ffffff;
    padding: 18px 48px;
    border-radius: 12px;
}

速查命令

查看活动统计数据

railway run -s "AINative- Core -Production" psql -c "
SELECT
    COUNT(*) as total_clicks,
    COUNT(DISTINCT email) as unique_users,
    COUNT(CASE WHEN converted THEN 1 END) as converted
FROM campaign_clicks
WHERE campaign_id = 'your-campaign-id';
"

发送提醒邮件活动

railway run -s "AINative- Core -Production" 
  python3 scripts/send_{campaign_id}_reminder.py --yes

端到端活动上线核对清单

• 在数据库中创建活动记录
• 将活动添加至后端 VALID_CAMPAIGNS 配置项
• 创建邮件模板（赠礼邮件 + 提醒邮件）
• 向自己测试邮件发送流程
• 将后端变更部署至 Railway
• 启动初始活动
• 监控点击追踪数据（前 24 小时）
• 检查转化率（第 3 天）
• 发送提醒邮件（第 7 天）
• 为已注册用户激活试用期
• 生成最终活动报告