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()
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 emailny2026_reminder.html - Reminder for non-registered userssummer2026_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
