Email Campaign Management - Complete Workflow

Purpose: Reusable workflow for creating, tracking, and managing email marketing campaigns with conversion tracking, reminder emails, and trial activation.

Scope: Growth marketing, email campaigns, conversion tracking, trial activation, campaign analytics

Campaign Database Schema

campaigns table

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

campaign_clicks table

CREATE TABLE campaign_clicks (
    id UUID PRIMARY KEY,
    campaign_id VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    click_source VARCHAR(100),  -- e.g., "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()
);

Campaign Statistics Query Patterns

Get Campaign Overview

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';

Get Registered vs Non-Registered

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 Integration

Rate Limiting (CRITICAL)

Resend Free Tier: 2 requests per second (NOT 100/minute!)

Correct Implementation:

import time

RATE_LIMIT_DELAY = 0.5  # 2 emails per second

for email in emails:
    send_email(email)
    if i < len(emails):
        time.sleep(RATE_LIMIT_DELAY)  # Wait 0.5 seconds

WRONG Implementation (causes 429 errors):

# DON'T DO THIS - sends too fast
RATE_LIMIT = 100  # emails per minute
for email in emails:
    send_email(email)
    # Only pause every 100 emails - TOO LATE!
    if i % RATE_LIMIT == 0:
        time.sleep(60)

Email Sending Function

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):
    """Send campaign email via Resend."""

    # Replace template variables
    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

Trial Activation Workflow

Activate Trial (CRITICAL: Use UPPERCASE Enums)

def activate_trial(user, campaign_id: str, trial_days: int, plan_name: str):
    """Activate ENTERPRISE trial for user."""

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

    # CRITICAL: PostgreSQL enums are UPPERCASE
    # 'enterprise' → 'ENTERPRISE'
    # 'active' NOT 'trial' (trial status doesn't exist)

    if user['subscription_id']:
        # Update existing subscription
        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:
        # Create new subscription
        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
        ))

    # Mark click as converted
    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()
這個技能的最新版本可以在7w4.net小蔥技能站找到。

Common Pitfalls & Solutions

PITFALL 1: Case-Sensitive Enums

Problem: 'enterprise' fails with "invalid input value for enum" Solution: Always use UPPERCASE: 'ENTERPRISE', 'SCALE', 'FREE'

PITFALL 2: Invalid Status Values

Problem: Using status = 'trial' (doesn't exist) Solution: Use status = 'active' with trial_ends_at field

PITFALL 3: Rate Limiting Too High

Problem: Setting rate limit to 100/minute causes 429 errors Solution: Use 2 per second (0.5s delay between sends)

PITFALL 4: Missing Organization ID

Problem: Some users don't have organization_id, causing NULL constraint errors Solution: Check if organization_id exists before INSERT, use separate query if NULL

PITFALL 5: Hardcoded Email in Templates

Problem: Test email in production template Solution: Use {{email}} template variable, replace at send time

PITFALL 6: Not Closing DB Connections

Problem: "too many clients already" error Solution: Always use try/finally to close connections

Email Template Requirements

Template Structure

All campaign email templates MUST be stored in:

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

Example filenames:

• ny2026_gift.html - Initial campaign email
• ny2026_reminder.html - Reminder for non-registered users
• summer2026_welcome.html - Welcome email

Required Template Variables

<!-- Email parameter for personalized links -->
<a href="https://www.ainative.studio/register?gift={{campaign_id}}&email={{email}}">
    Claim Your Trial
</a>

Styling Standards (Dark Theme)

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

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

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

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

Quick Reference Commands

Check Campaign Stats

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';
"

Send Reminder Campaign

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

End-to-End Campaign Checklist

• [ ] Create campaign record in database
• [ ] Add campaign to backend VALID_CAMPAIGNS config
• [ ] Create email templates (gift + reminder)
• [ ] Test email send to yourself
• [ ] Deploy backend changes to Railway
• [ ] Launch initial campaign
• [ ] Monitor click tracking (first 24h)
• [ ] Check conversion rate (day 3)
• [ ] Send reminder emails (day 7)
• [ ] Activate trials for registered users
• [ ] Generate final campaign report

這個 Skill 質量較好，提供了完整的郵件營銷活動管理方案，包括活動追蹤、郵件傳送和試用啟用等核心功能。亮點在於詳細記錄了常見錯誤和避坑指南，對新手友好。不足之處是文件結構稍顯零散，部分程式碼細節有改進空間。