亚马逊竞品流量对抗分析
核心目标
基于SIF（Sorftime/卖家精灵）多维度数据，对标竞品流量结构，找到竞品流量弱点和我方机会点，生成可直接落地执行的广告打法方案。
数据源识别
用户会上传以下一种或多种SIF导出的xlsx文件。通过文件名和内容特征识别：
1. 流量时光机（Traffic Time Machine）
- 文件特征：文件名含"流量时光机"或"流量占比对比"
- 核心Sheet：
流量时光机sheet：按周的7维流量趋势（自然流量、SP广告、品牌广告、视频广告、AC推荐、其他推荐、全部流量），每个维度下有多个ASIN的数据列流量占比对比sheet：每个关键词下各竞品的流量占比份额，含关键词类型标签（主要流量词/精准流量词/长尾词等）、周搜索量、在售商品数对比sheet：与流量占比类似，但含有订单量估算
- 数据结构：第1行为维度分组header，第2行为ASIN列header，第3行起为数据。流量时光机sheet第1列为日期
2. 广告透视仪（Ad Spy / Ad Keywords）
- 文件特征：文件名含"asinAdKwView"或"广告透视仪"
- 核心字段：广告搜索词、翻译、SP广告流量占比（该词为整个Listing贡献的SP广告流量占比）、SP广告流量份额（该Listing在该词下的SP广告流量份额）、有曝光的广告活动/广告组/变体数、搜索量排名、月搜索量
- 数据结构：第1行为ASIN站点header，第2行为列名，第3行起为数据
3. 关键词广告点击转化率（Keyword Conversion）
- 文件特征：文件名含"keywordConversion"
- 核心字段：关键词、搜索量、点击量、购买量、搜索转化率、点击转化率、建议竞价（最低/推荐/最高）、CPA（最低/推荐/最高）、ACOS（最低/均价/最高）、产品均价、广告盈亏预估、top3产品转化率、ABA Top3集中度
- 数据结构：第1行为站点+关键词+毛利率header，第2行为列名，第3行起为数据
4. 坑位快照（Position Snapshot）
- 文件特征：文件名含"坑位快照"
- 核心字段：页码-页内排名（如P1-1表示第1页第1位）、按小时的ASIN变化
- 数据结构：奇数列为时间点对应的ASIN，偶数列为图片（忽略）
分析流程
按以下顺序执行分析。每一步都要用Python读取数据并计算，不要凭空推测。
Step 1: 数据加载与清洗
import pandas as pd
import numpy as np
# 读取所有上传的xlsx文件
# 对每个文件：跳过标题行（通常第1行是总header，第2行是列名，从第3行开始是数据）
# 用 header=None 读取，然后手动设置列名（取第2行）
关键清洗规则：
- 百分比字符串转float（去掉%号再除100）
- 流量占比对比中的NaN表示该竞品在此词下无流量
- 坑位快照中忽略"图片"列
- 广告透视仪第1行含ASIN信息，提取目标ASIN
Step 2: 竞品流量结构全景分析
从流量时光机sheet提取最近2-4周数据，构建每个竞品的流量结构：
| 维度 | 分析要点 |
|---|---|
| 自然流量 | 各竞品自然流量占比趋势，谁在涨谁在跌 |
| SP广告流量 | 各竞品SP广告投入力度对比 |
| 品牌广告流量 | 是否有竞品在做品牌防御 |
| 视频广告流量 | 视频广告渠道是否存在空白机会 |
| AC推荐流量 | 谁拿到了AC推荐标 |
| 其他推荐流量 | 关联推荐/频繁购买等流量 |
| 全部流量 | 总流量趋势判断竞争态势 |
输出：每个竞品的流量结构饼图数据和趋势变化方向
Step 3: 关键词级别流量对抗分析
合并流量占比对比和关键词转化率数据：
- 识别核心战场词：周搜索量排名前50的高流量词
- 计算各竞品在每个词的流量份额差距
- 标记关键词类型：主要流量词、精准流量词、长尾词、品牌词
- 合并转化率数据：为每个关键词补充点击转化率、CPA等指标
Step 4: CPA推算与广告ROI建模
这是核心决策依据。用以下公式推算每个关键词的广告投入产出：
售价 = 用户提供（如49.99美金）
利润率 = 用户提供（如25%）
单件利润 = 售价 × 利润率
目标CPA = 单件利润（即保本CPA）
实际CPA = 建议竞价(推荐) / 点击转化率
ACOS = 建议竞价(推荐) / (售价 × 点击转化率)
每单广告成本 = CPA
广告盈亏 = 单件利润 - CPA
将关键词分为三档：
- 绿灯词（盈利）：CPA < 目标CPA，即ACOS < 利润率，打了就赚
- 黄灯词（微亏/持平）：CPA接近目标CPA，ACOS在利润率±5%内，需要优化转化率
- 红灯词（亏损）：CPA远超目标CPA，需谨慎或暂缓
Step 5: 竞品弱点挖掘
针对每个竞品，找出以下弱点：
- 广告空白词：竞品自然排名靠前但没投广告的高流量词（流量占比对比中有份额，但广告透视仪中无此词）→ 机会：投SP广告抢该词流量
- 广告低效词：竞品广告占比很高但流量份额不高的词 → 说明竞品在该词转化差或出价过低
- 排名不稳定词：坑位快照中，竞品在该关键词下排名波动大（不同时段出现在不同位置或消失）→ 机会：该词竞品投放不连续
- 流量下滑词：流量时光机中，竞品某维度流量持续下降的词 → 机会：竞品正在放弃该流量入口
- 低集中度词：ABA Top3集中度低的词 → 说明该词市场分散，新玩家更容易分到流量
Step 6: 生成作战方案
最终输出必须是可直接执行的方案，包含：
6.1 关键词攻击清单（按优先级排序）
每个推荐关键词包含：
- 关键词（中英文）
- 周搜索量
- 推荐竞价（低/中/高）
- 预估CPA和ACOS
- 盈亏预估（每单赚/亏多少）
- 攻击理由（哪个竞品的什么弱点）
- 建议广告类型（SP精准/SP广泛/SB品牌/SBV视频）
- 优先级（P0/P1/P2）
6.2 竞品逐个击破策略
针对每个竞品单独出方案：
- 该竞品流量结构特征（依赖自然流量还是广告？品牌词占比？）
- 该竞品最大弱点（3条以内）
- 针对性打法（具体到关键词+出价+广告类型）
6.3 预算分配建议
- 按日预算推荐（保守/正常/激进三档）
- 每个关键词的建议日点击量和对应花费
- 预期产出（日单量、日广告销售额）
输出格式
生成一个完整的分析报告xlsx文件（使用openpyxl），包含以下sheets：
- 竞品流量全景 - 7维流量结构对比表+趋势
- 关键词攻防矩阵 - 核心词在各竞品的流量份额+转化率+CPA
- 关键词攻击清单 - 按优先级排序的可执行广告词表
- 竞品弱点地图 - 每个竞品的弱点汇总
- 预算与ROI预估 - 三档预算方案和预期回报
- 作战总结 - 文字版执行方案摘要
同时在对话中用中文给出精炼的策略总结，重点突出：
- 最大的3个机会点
- 最应该优先攻击的5个关键词
- 每个竞品的一句话弱点描述
- 推荐的日预算和预期ROI
用户参数处理
用户会提供以下参数，如果没提供则主动询问：
- 售价：产品售价（美金）
- 利润率：毛利率百分比
- 我方ASIN：可选，用于标记我方数据
- 目标竞品ASIN：可选，重点分析的竞品
如果用户同时上传了自己的后台广告报告（Amazon Ads后台导出），一并纳入分析，与SIF数据交叉验证。
注意事项
- 所有数值计算必须用Python完成，不得编造数据
- 百分比显示统一用两位小数
- 金额统一用美金，保留两位小数
- 关键词保留英文原文+中文翻译
- 分析结论必须有数据支撑，每个建议都要标明数据来源
- 如果某个数据源缺失，标注"数据不足，建议补充XX数据"而非跳过
- CPA计算中如果点击转化率为0或NaN，标记为"数据不足"而非给出无穷大