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

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

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)

Correct Implementation:
CODEBLOCK4

WRONG Implementation (causes 429 errors):
CODEBLOCK5

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

Common Pitfalls & Solutions

PITFALL 1: Case-Sensitive Enums

PITFALL 2: Invalid Status Values

PITFALL 3: Rate Limiting Too High

PITFALL 4: Missing Organization ID

PITFALL 5: Hardcoded Email in Templates

PITFALL 6: Not Closing DB Connections

Email Template Requirements

Template Structure

Example filenames:

• - ny2026_gift.html - Initial campaign email
• INLINECODE13 - Reminder for non-registered users
• INLINECODE14 - Welcome email

Required Template Variables

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

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