| 12345678910111213141516171819202122232425262728 |
- -- 客户意向度评分表（多维意向度评估核心载体）
- CREATE TABLE IF NOT EXISTS lobster_intent_score (
- id BIGINT AUTO_INCREMENT PRIMARY KEY,
- company_id BIGINT NOT NULL COMMENT '企业ID',
- external_user_id VARCHAR(128) NOT NULL COMMENT '客户外部ID(企微external_userid)',
- channel_type VARCHAR(20) NOT NULL DEFAULT 'QW' COMMENT '渠道类型 QW/WX',
- contact_id BIGINT DEFAULT NULL COMMENT '联系人ID',
- company_user_id BIGINT DEFAULT NULL COMMENT '归属销售ID',
- total_score INT DEFAULT 0 COMMENT '总分 0-100',
- communication_score INT DEFAULT 0 COMMENT '沟通活跃度分 0-20',
- course_score INT DEFAULT 0 COMMENT '看课行为分 0-25',
- live_score INT DEFAULT 0 COMMENT '直播互动分 0-15',
- order_score INT DEFAULT 0 COMMENT '订单行为分 0-25',
- profile_score INT DEFAULT 0 COMMENT '画像匹配分 0-15',
- intent_level VARCHAR(20) DEFAULT NULL COMMENT '意向等级 HIGH(>=70)/MEDIUM(>=40)/LOW(<40)',
- ai_reasoning TEXT DEFAULT NULL COMMENT 'AI生成的意向理由',
- scoring_method VARCHAR(20) DEFAULT 'HYBRID' COMMENT '评分方式 RULE/AI/HYBRID',
- create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
- update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
- KEY idx_company_customer (company_id, external_user_id, channel_type),
- KEY idx_intent_level (company_id, intent_level),
- KEY idx_total_score (company_id, total_score),
- KEY idx_company_user (company_id, company_user_id)
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='客户意向度评分表(多维意向度评估)';
|
