생성형 AI 마케팅 메시지 자동 생성

테이블 (7)

campaign_products (캠페인-상품 매핑)

  • mapping_id — BIGINT — 기본 키
  • product_id — BIGINT — 외래 키 → products.product_id
  • campaign_id — BIGINT — 외래 키 → campaigns.campaign_id

campaigns (캠페인)

  • campaign_id — BIGINT — 기본 키
  • created_by — BIGINT — 외래 키 → users.user_id
  • name — VARCHAR(100) — NOT NULL
  • type — VARCHAR(50)
  • description — TEXT
  • start_date — DATE
  • end_date — DATE
  • status — VARCHAR(20)
  • created_at — DATETIME — NOT NULL

customer_segments (고객 세그먼트)

  • segment_id — BIGINT — 기본 키
  • name — VARCHAR(100) — NOT NULL
  • age_min — INT
  • age_max — INT
  • gender — VARCHAR(10)
  • region — VARCHAR(50)
  • membership_level — VARCHAR(20)
  • recency_max_days — INT
  • created_at — DATETIME — NOT NULL

customers (고객)

  • customer_id — BIGINT — 기본 키
  • name — VARCHAR(50) — NOT NULL
  • phone — VARCHAR(30) — NOT NULL
  • age — INT
  • gender — VARCHAR(10)
  • region — VARCHAR(50)
  • membership_level — VARCHAR(20)
  • current_plan — VARCHAR(100)
  • current_device — VARCHAR(100)
  • contract_end_date — DATE
  • avg_data_usage_gb — DECIMAL(10,2)
  • join_date — DATETIME
  • last_purchase_date — DATETIME
  • created_at — DATETIME — NOT NULL

messages (AI 생성 메시지)

  • message_id — BIGINT — 기본 키
  • created_by — BIGINT — 외래 키 → users.user_id
  • campaign_id — BIGINT — 외래 키 → campaigns.campaign_id
  • segment_id — BIGINT — 외래 키 → customer_segments.segment_id
  • customer_id — BIGINT — 외래 키 → customers.customer_id
  • message_content — TEXT — NOT NULL
  • message_version — INT — NOT NULL
  • message_type — VARCHAR(20) — NOT NULL
  • character_count — INT
  • ai_model_used — VARCHAR(50)
  • generation_prompt — TEXT
  • tone — VARCHAR(20)
  • created_at — DATETIME — NOT NULL

products (상품)

  • product_id — BIGINT — 기본 키
  • name — VARCHAR(100) — NOT NULL
  • category — VARCHAR(50)
  • price — DECIMAL(12,2)
  • discount_rate — DECIMAL(5,2)
  • benefits — TEXT
  • conditions — TEXT
  • stock_status — VARCHAR(20)
  • created_at — DATETIME — NOT NULL

users (사용자)

  • user_id — BIGINT — 기본 키
  • username — VARCHAR(50) — NOT NULL
  • password — VARCHAR(255) — NOT NULL
  • email — VARCHAR(100) — NOT NULL
  • name — VARCHAR(50) — NOT NULL
  • phone — VARCHAR(30)
  • department — VARCHAR(100)
  • role — VARCHAR(20) — NOT NULL
  • created_at — DATETIME — NOT NULL