생성형 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