内容投放分析 + 热帖洞察报告生成

一、项目概述

1.1 产出物

一份 HTML 报告，内含：

内容投放分析、热帖洞察
交互式图表（ECharts，CDN 引入）
KPI 卡片、数据表格、洞察文案
无外部依赖，可直接浏览器打开或分享

1.2 核心指标

| 指标 | 公式 | 口径 | | ------ | --------- | --------- | | CPC | 总费用 ÷ 总阅读 | 先聚合后相除 | | CPE | 总费用 ÷ 总互动 | 先聚合后相除 | | 互动率 | 总互动 ÷ 总阅读 | 分子分母同口径 | | CTR | 总阅读 ÷ 总曝光 | 仅有完整数据的帖子 |

脱敏说明：原流程中团队用固定代码（如 SD/XJ/LL/XM）、产品用固定代码（如 wb/qc）标识。本技能统一替换为通用占位符：

团队：team A / team B / team C / team D（团队为空归入 其他）

产品：product A / product B（按需映射实际业务产品）

执行前，先与实际业务建立「代码 ↔ 真实团队/产品」映射表，再套用本流程。

二、输入材料

| 材料 | 说明 | 必须 | | -------- | -------------------- | ------ | | 源表 Excel | 已筛选的 {周期}发布记录.xlsx | ✅ | | 日期范围 | 如 MMDD-MMDD | ✅ | | 上期 HTML | 作为模板参考格式与样式 | ✅ | | 热帖数据 | 新增爆款帖子的投放数据 + 截图等 | 仅新增时 |

三、完整工作流

┌─────────────────────────────────────────────────────────┐
│  Phase 1: 数据准备                                       │
│  读取Excel → 筛选日期 → 清洗字段 → 重算指标 → 去极值     │
└────────────────────────────┬────────────────────────────┘
                             │
┌────────────────────────────▼────────────────────────────┐
│  Phase 2: 聚合计算                                       │
│  全局KPI → 按团队 → 按产品 → 产品×团队 → 粉丝量级       │
└────────────────────────────┬────────────────────────────┘
                             │
┌────────────────────────────▼────────────────────────────┐
│  Phase 3: 可视化                                         │
│  堆叠柱状图(按团队) → 气泡图(去极值+含极值) → ECharts    │
└────────────────────────────┬────────────────────────────┘
                             │
┌────────────────────────────▼────────────────────────────┐
│  Phase 4: HTML组装                                       │
│  Tab1 内容投放分析 → Tab2 热帖洞察                       │
└────────────────────────────┬────────────────────────────┘
                             │
┌────────────────────────────▼────────────────────────────┐
│  Phase 5: 交付                                           │
│  预览 → 验证 → 输出文件                                  │
└─────────────────────────────────────────────────────────┘

Phase 1: 数据准备

目标：从原始 Excel 得到干净的结构化记录列表。

读取 Excel
- 使用 openpyxl 读取（data_only=True）
- 若为总表，需向下填充（ffill）产品/项目列的空值
- 解析粉丝数文本格式（如 "17.8w" → 178000）
筛选
- 按实际发布日期过滤本周期范围
- 排除达人名称为空的行
字段标准化
- 产品：统一为内部编码（如 product A / product B）
- 团队：统一命名格式（如 team A）
- 团队为空 → 归入 其他
- 数值清洗：去 \xa0、逗号、空格
重算计算字段
- 总费用 = 投流费用 + 合作费用
- 每条记录的 CPE = 总费用 / 总互动（用于极值判断）
去极值
- 仅对有效记录（费用>0 且互动>0）
- CPE 排序后去掉双侧各 2.5%
- 记录哪些是极值点（后续气泡图标注用）

Phase 2: 聚合计算

目标：生成报告所需的各维度统计数据。

| 维度 | 分组键 | 输出指标 | | ------ | ----------------- | -------------------------------------- | | 全局 | — | 笔记数、达人数、费用、曝光、CPC、CPE、CPM、CTR、互动率、篇均曝光 | | 按团队 | team A/team B/team C/team D | 同上 | | 按产品 | product A/product B | 同上 | | 产品×团队 | product A team A, product B team B... | 同上，按费用倒序 | | 粉丝量级 | 6档 | 篇数、平均曝光、阅读率、互动率、CPC、CPE、CPM、CTR | | 每日发布 | 日期×团队 | 各团队当日笔记数 |

Phase 3: 可视化

目标：生成报告中的图表，使用 ECharts（CDN）在 HTML 中渲染。

堆叠柱状图（每日发布量）

维度：按团队堆叠（非产品）
值为 0 时隐藏 label

气泡图（投放效果散点）

坐标：X=总费用, Y=总互动
气泡大小：按阅读量缩放
标注：每个气泡显示达人名称
拟合曲线：红色 2 阶多项式（数据 <4 点时降级为线性）
排除：费用=0 的记录不参与
两个版本：
- 主图 = 去极值后
- 折叠区 = 含极值，极值点用橙色圈标注

Phase 4: HTML 组装

目标：将数据和图表拼装为完整 HTML。

各 Tab 内部模块

| Tab | 模块顺序 | | ------- | -------------------------------------------------- | | 内容投放分析 | KPI卡片 → 堆叠图+团队表 → 气泡图 → 产品拆分表 → 产品×团队表+洞察 → 粉丝量级分析 | | 热帖洞察 | 数据统计表 → 核心洞察 → 热帖详情卡片 |

热帖洞察框架

爆款公式：情绪痛点 × 场景代入 × 工具赋能
四种 Hook 类型：身份共鸣 / 反常识好奇 / 焦虑解决 / 清单工具
达人梯队：10万+(破圈) / 2-10万(转化) / 2万以下(长尾SEO)

Phase 5: 交付

写入文件
浏览器预览确认
交付给用户

四、规范速查

数字格式

| 情况 | 格式 | 示例 | | ----------- | ------ | ------------- | | 数值 ≥ 10000 | X.X万 | 77,561 → 7.8万 | | 数值 < 10000 | 千位逗号 | 8,052 | | CPE/CPC | ¥X.XX | ¥1.63 | | 百分比 | X.X% | 55.0% | | 粉丝数 | X.X万粉 | 11.8万粉 |

洞察文案

每条带具体数字（篇数 + CPE + 互动率）
小样本（<10篇）加限定语

五、关键注意事项

互动率口径：分子分母必须同口径，无阅读数据的帖子其互动不计入分子
费用=0 排除气泡图：这些点不画、不参与拟合
不依赖 numpy：用 ECharts 渲染图表，Python 侧只做数据聚合
源表计算字段不可信：CPC/CPE/CTR 全部用原始数据重新算
气泡图必须标注达人名：用白色描边增加可读性
f-string 与 JS 花括号冲突：Python 生成时 JS 的 {} 要写成 {{}}

六、快速启动

对 AI 说：

请用 {源表}.xlsx 作为源表，生成 {MMDD-MMDD} 周期的周报 HTML。参考上期报告格式，热帖洞察沿用上期。

或调用本技能：content-placement-report