email-campaign-management

name

email-campaign-management

description

Complete workflow for email marketing campaigns with conversion tracking and trial activation. Use when (1) Creating email campaigns, (2) Sending campaign emails via Resend API, (3) Tracking conversions and clicks, (4) Activating trials for campaign users, (5) Generating campaign reports, (6) Sending reminder emails. CRITICAL - Follow Resend rate limits (2 emails/second), use UPPERCASE for PostgreSQL enums.

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 = "[e-mail]"

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()

---

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