email-campaign-management

# 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 ```sql 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 ```sql 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 ```sql 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 ```sql 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**: ```python 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): ```python # 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 ```python 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) ```python 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 ```html <!-- 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) ```css /* 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 ```bash 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 ```bash 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 --- > Converted and distributed by [TomeVault](https://tomevault.io/claim/ainative-studio) — claim your Tome and manage your conversions. <!-- tomevault:4.0:skill_md:2026-04-14 -->