이메일 마케팅 자동화

// newsletter_system.php - 뉴스레터 구독 관리 class NewsletterSystem { private $pdo; public function __construct(PDO $pdo) { $this->pdo = $pdo; $this->initTables(); } private function initTables() { $this->pdo->exec(" CREATE TABLE IF NOT EXISTS subscribers ( id INT AUTO_INCREMENT PRIMARY KEY, email VARCHAR(255) UNIQUE NOT NULL, name VARCHAR(100), status ENUM('active','unsubscribed','bounced') DEFAULT 'active', source VARCHAR(50), tags JSON, confirmed TINYINT DEFAULT 0, confirm_token VARCHAR(64), subscribed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, unsubscribed_at TIMESTAMP NULL, INDEX idx_email (email), INDEX idx_status (status) ); CREATE TABLE IF NOT EXISTS email_campaigns ( id INT AUTO_INCREMENT PRIMARY KEY, subject VARCHAR(200) NOT NULL, content LONGTEXT NOT NULL, segment_filter JSON, status ENUM('draft','scheduled','sent') DEFAULT 'draft', scheduled_at TIMESTAMP NULL, sent_at TIMESTAMP NULL, total_sent INT DEFAULT 0, total_opened INT DEFAULT 0, total_clicked INT DEFAULT 0, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); "); } // Double Opt-In 구독 (스팸 방지 필수!) public function subscribe(string $email, string $name = '', string $source = 'website'): array { // 이메일 유효성 검사 if (!filter_var($email, FILTER_VALIDATE_EMAIL)) { return ['success' => false, 'error' => '유효하지 않은 이메일']; } // 중복 체크 $stmt = $this->pdo->prepare("SELECT id, status FROM subscribers WHERE email = ?"); $stmt->execute([$email]); $existing = $stmt->fetch(); if ($existing && $existing['status'] === 'active') { return ['success' => false, 'error' => '이미 구독 중입니다']; } $token = bin2hex(random_bytes(32)); $stmt = $this->pdo->prepare(" INSERT INTO subscribers (email, name, source, confirm_token) VALUES (?, ?, ?, ?) ON DUPLICATE KEY UPDATE status = 'active', confirm_token = ?, confirmed = 0 "); $stmt->execute([$email, $name, $source, $token, $token]); // 확인 이메일 발송 $this->sendConfirmationEmail($email, $token); return ['success' => true, 'message' => '확인 이메일을 발송했습니다']; } // 이메일 확인 처리 public function confirm(string $token): bool { $stmt = $this->pdo->prepare(" UPDATE subscribers SET confirmed = 1, confirm_token = NULL WHERE confirm_token = ? AND confirmed = 0 "); $stmt->execute([$token]); return $stmt->rowCount() > 0; } // 구독 해지 (원클릭) public function unsubscribe(string $email): bool { $stmt = $this->pdo->prepare(" UPDATE subscribers SET status = 'unsubscribed', unsubscribed_at = NOW() WHERE email = ? "); $stmt->execute([$email]); return $stmt->rowCount() > 0; } private function sendConfirmationEmail(string $email, string $token): void { $confirmUrl = "https://yourapp.com/confirm?token={$token}"; $subject = "구독 확인 - 이메일을 인증해주세요"; $body = "<h2>구독을 확인해주세요</h2> <p>아래 버튼을 클릭하면 구독이 완료됩니다.</p> <a href='{$confirmUrl}' style='background:#667eea;color:white; padding:12px 32px;border-radius:8px; text-decoration:none;'>구독 확인하기</a>"; mail($email, $subject, $body, "Content-Type: text/html; charset=UTF-8"); } }

// email_sequence.php - 이메일 시퀀스 자동화 엔진 class EmailSequenceEngine { private $pdo; public function __construct(PDO $pdo) { $this->pdo = $pdo; $this->initTables(); } private function initTables() { $this->pdo->exec(" CREATE TABLE IF NOT EXISTS email_sequences ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL, trigger_event VARCHAR(50) NOT NULL, is_active TINYINT DEFAULT 1 ); CREATE TABLE IF NOT EXISTS sequence_steps ( id INT AUTO_INCREMENT PRIMARY KEY, sequence_id INT NOT NULL, step_order INT NOT NULL, delay_days INT NOT NULL, subject VARCHAR(200) NOT NULL, template_id INT, content TEXT NOT NULL, condition_field VARCHAR(50), condition_value VARCHAR(100), FOREIGN KEY (sequence_id) REFERENCES email_sequences(id) ); CREATE TABLE IF NOT EXISTS sequence_enrollments ( id INT AUTO_INCREMENT PRIMARY KEY, user_id INT NOT NULL, sequence_id INT NOT NULL, current_step INT DEFAULT 0, status ENUM('active','completed','paused') DEFAULT 'active', enrolled_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, next_send_at TIMESTAMP NULL, UNIQUE KEY unique_enrollment (user_id, sequence_id) ); "); } // 사용자를 시퀀스에 등록 public function enroll(int $userId, int $sequenceId): void { $stmt = $this->pdo->prepare(" INSERT IGNORE INTO sequence_enrollments (user_id, sequence_id, next_send_at) VALUES (?, ?, NOW()) "); $stmt->execute([$userId, $sequenceId]); } // 크론잡으로 매 시간 실행: 보낼 이메일 처리 public function processQueue(): int { $sent = 0; $enrollments = $this->pdo->query(" SELECT e.*, s.name as seq_name FROM sequence_enrollments e JOIN email_sequences s ON e.sequence_id = s.id WHERE e.status = 'active' AND e.next_send_at )->fetchAll(PDO::FETCH_ASSOC); foreach ($enrollments as $enrollment) { $nextStep = $enrollment['current_step'] + 1; // 다음 단계 가져오기 $step = $this->pdo->prepare(" SELECT * FROM sequence_steps WHERE sequence_id = ? AND step_order = ? "); $step->execute([$enrollment['sequence_id'], $nextStep]); $stepData = $step->fetch(PDO::FETCH_ASSOC); if (!$stepData) { // 시퀀스 완료 $this->pdo->prepare(" UPDATE sequence_enrollments SET status = 'completed' WHERE id = ? ")->execute([$enrollment['id']]); continue; } // 이메일 발송 $this->sendEmail($enrollment['user_id'], $stepData); // 다음 스텝 스케줄링 $nextDelay = $stepData['delay_days']; $this->pdo->prepare(" UPDATE sequence_enrollments SET current_step = ?, next_send_at = DATE_ADD(NOW(), INTERVAL ? DAY) WHERE id = ? ")->execute([$nextStep, $nextDelay, $enrollment['id']]); $sent++; } return $sent; } private function sendEmail(int $userId, array $step): void { // 사용자 정보 조회 후 이메일 발송 $user = $this->pdo->prepare("SELECT email, name FROM users WHERE id = ?"); $user->execute([$userId]); $userData = $user->fetch(PDO::FETCH_ASSOC); // 변수 치환 ({{name}} 등) $content = str_replace( ['{{name}}', '{{email}}'], [$userData['name'], $userData['email']], $step['content'] ); mail($userData['email'], $step['subject'], $content, "Content-Type: text/html; charset=UTF-8"); } } // 크론탭 설정: 0 * * * * php /path/to/cron_email.php $engine = new EmailSequenceEngine($pdo); $sent = $engine->processQueue(); echo "발송 완료: {$sent}건";