---
name: brand-ad-analysis
description: "品广高潜商户TGI分析工具。对品广档期和商户名单进行去重、特征分层、TGI计算、易售卖商户识别和可售卖档期匹配。Use when the user explicitly asks to run brand advertising analysis, 品广分析, 高潜分析, TGI分析, or generate the brand-ad analysis HTML tool."
---

# 品广"高潜"分析工具

根据品广档期查询和商户名单两份Excel数据，自动完成TGI特征分析，识别易售卖商户，匹配可售卖档期。

## 触发条件

用户明确要求执行品广分析、高潜分析、TGI分析，或要求生成品广分析HTML工具时触发。

## 输入文件要求

### 文件1：品广档期查询
必须包含列：`城市等级`、`城市名称`、`行政区`、`行业`、`类目`、`月份`、`占期周期`

### 文件2：品广商户名单
必须包含列：`商户ID`、`商户名称`、`行业`、`类目`、`城市名称`、`效果广告收入`、`年费收入`、`品广收入`、`是否售卖品广`、`流量`

## 分析流程

### Step 1: 数据预处理

```python
import pandas as pd
import numpy as np

df_schedule = pd.read_excel('品广档期查询.xlsx')
df_merchant = pd.read_excel('品广商户名单.xlsx')

# 流量空值填0
df_merchant['流量'] = df_merchant['流量'].fillna(0)

# 去重：按商户ID+城市名称+效果广告收入+年费收入+流量
df_merchant = df_merchant.drop_duplicates(subset=['商户ID', '城市名称', '效果广告收入', '年费收入', '流量'])

# 标记品广买家
df_merchant['is_buyer'] = (df_merchant['品广收入'] > 0).astype(int)
```

### Step 2: 特征分层

默认阈值（支持用户自定义）：

| 维度 | 阈值 | 标签 |
|------|------|------|
| 效果广告收入 | 0 / 2000 / 10000 / 50000 | 无效果收入 / 效果_低 / 效果_中 / 效果_高 / 效果_超高 |
| 年费收入 | 0 / 1000 / 3000 / 6000 | 无年费 / 年费_低 / 年费_中 / 年费_高 / 年费_超高 |
| 流量 | 0 / 50000 / 200000 / 500000 | 无流量 / 流量_低 / 流量_中 / 流量_高 / 流量_超高 |

```python
def layer_ad(x, th=[0, 2000, 10000, 50000]):
    if x <= th[0]: return '无效果收入'
    elif x <= th[1]: return '效果_低'
    elif x <= th[2]: return '效果_中'
    elif x <= th[3]: return '效果_高'
    else: return '效果_超高'

# 同理 layer_fee, layer_traffic
```

### Step 3: TGI计算

```
TGI = (目标群体中某特征占比 / 总体中该特征占比) × 100
```

- 目标群体 = 品广买家（品广收入 > 0）
- TGI > 100 表示该特征在买家中过度表达
- 计算两个维度：整体TGI + 分类目TGI

```python
def calc_tgi(df, feature_col):
    buyers = df[df['is_buyer'] == 1]
    total_n, buyer_n = len(df), len(buyers)
    results = []
    for layer in df[feature_col].unique():
        total_in_layer = len(df[df[feature_col] == layer])
        buyer_in_layer = len(buyers[buyers[feature_col] == layer])
        pct_total = total_in_layer / total_n * 100
        pct_buyer = buyer_in_layer / buyer_n * 100 if buyer_n > 0 else 0
        tgi = (pct_buyer / pct_total * 100) if pct_total > 0 else 0
        results.append({'分层': layer, '总体占比': pct_total, '买家占比': pct_buyer, 'TGI': round(tgi, 1)})
    return results
```

### Step 4: 商户评分

每个商户的综合TGI得分 = 三个维度TGI均值（优先使用类目级TGI，fallback到整体TGI）

```python
score = (效果TGI + 年费TGI + 流量TGI) / 3
```

售卖难度标签（默认阈值）：
- 综合得分 >= 200 → 高潜力
- 综合得分 >= 150 → 较容易
- 综合得分 >= 100 → 一般
- 综合得分 < 100 → 较难

### Step 5: 匹配可售卖档期

1. 筛选出"易售卖商户"：未购买品广 + 售卖难度为"高潜力"或"较容易"
2. 筛选"可售卖档期"：占期周期 = "可售卖"
3. 按城市名称 + 类目做inner join匹配

### Step 6: 输出

输出字段：`城市等级`、`城市名称`、`行政区`、`行业`、`类目`、`月份`、`占期周期`、`高潜商户数`、`商户名单`

## 输出方式

### 方式A：Python直出Excel

生成Excel文件，包含以下Sheet：
1. **整体TGI分析** - 三维度各分层的TGI值
2. **分类目TGI分析** - 按类目细分的TGI
3. **可售卖档期-高潜商户** - 最终匹配结果

高潜力行用绿色高亮，较容易行用黄色高亮。

### 方式B：生成HTML前端工具

生成单个HTML文件，包含：
- 两个文件上传区（带列名校验）
- 可折叠参数配置区（阈值可调）
- 分析按钮
- 结果展示（统计卡片 + Tab切换 + 表格 + Chart.js图表）
- Excel下载按钮

技术要求：
- 纯原生JS，不依赖React/Babel
- CDN依赖：SheetJS (xlsx.full.min.js) + Chart.js (chart.umd.min.js)
- 所有计算在浏览器端完成

HTML模板参考 [html-template.md](html-template.md)

## 注意事项

- 分层阈值应根据数据分布调整，上述为默认值
- 类目样本 < 10 或无买家时，fallback到整体TGI
- 流量字段为null时统一视为0
- 去重逻辑为完全匹配，非模糊匹配