伊菲丹官旗素材数据分析技能

> 用于解析巨量千川导出的素材数据 Excel，按剪辑/编导/分组维度聚合统计消耗、净成交ROI、点击率、转化率、3s播放率、完播率、CPM 等指标，支持环比对比，生成 HTML 报告。

一、技能元信息

name: yifeidan-material-analysis
description: "伊菲丹官旗投流素材数据半月度分析技能。用于解析巨量千川导出的素材数据Excel，按剪辑/编导/分组维度聚合统计消耗、净成交ROI、点击率、转化率、3s播放率、完播率、CPM等指标，支持环比对比，生成HTML报告。当用户提供伊菲丹素材数据Excel并要求按剪辑/编导统计各项投流指标时触发此技能。"
agent_created: true

二、用途与触发条件

用途：解析巨量千川平台导出的伊菲丹官旗素材数据 Excel，按剪辑、编导、分组维度聚合统计投流核心指标，支持单期和环比两期对比，生成含表格与文字洞察的 HTML 报告。

触发条件：当用户提供伊菲丹素材数据 Excel 文件，并要求按剪辑/编导/分组统计消耗、ROI、点击率、转化率、3s播放率、完播率、CPM 等维度时触发。

数据文件说明：
- 数据来源：巨量千川平台导出的素材数据 Excel
- 文件命名：如 伊菲丹官旗6.16~6.30素材数据.xlsx、伊菲丹官旗7.1~7.14素材数据.xlsx
- 列结构：固定列索引映射（见第三节），但部分导出文件数值列可能为字符串格式（带逗号、百分号），需用 parse_num() 统一解析

三、列索引映射

以下为伊菲丹官旗素材数据的标准列映射。首次使用新文件时需验证列名是否匹配。

关键注意点：

1. 整体成交订单数（col16）vs 净成交订单数（col24）：
   - 转化率公式使用 整体成交订单数（col16）÷ 点击次数
   - 净成交ROI 使用 净成交金额（col23）÷ 消耗
   - 报告中"净成交订单数"展示的是 col24 的值
2. 数值格式兼容：部分导出文件数值为正常 float 类型；部分导出文件数值为字符串，带逗号（如 "1,234,567"）或百分号（如 "3.45%"）；parse_num() 统一处理。
3. 百分比处理：原始百分比值可能是小数形式（如 0.0345）或百分比字符串（如 "3.45%"）；parse_num() 检测到 % 符号时自动除以 100。

四、素材名称解析规则

剪辑提取

素材名称以 【亚瑟】 开头，紧跟的文本（到第一个 - 为止）即为剪辑名称。
- 示例：【亚瑟】潘沛璋-谢咏-... → 剪辑 = 潘沛璋

编导提取

编导名称在素材名称中部，模式为 谢 或 瑶 开头的姓名（如谢咏、谢喵、谢咏喵、瑶传等）。
正则：(A|B|一组|二组)?(谢[^\-]+|瑶[^\-]+)-

分组提取

编导名称前如有 A/一组 标记 → 一组；B/二组 标记 → 二组；无标记 → 未分组（分组前的历史数据）。
- 关键：B 后面接的是编导名称（文字），B 后面接的数字只是素材编号，不是编导。
- 示例：【亚瑟】刘宇-B谢喵-1-... → 编导=谢喵，分组=二组

解析示例

五、指标计算公式（严格按以下口径）

> 注意：转化率用的是"整体成交订单数"（非"净成交订单数"）除以点击次数。这是用户确认的口径。

六、执行流程

1. 确认参数

向用户确认以下信息（若已在对话中提供则直接使用）：
- 本期 Excel 文件路径
- 上期 Excel 文件路径（如需环比）
- 本期"新产出"判定截止日期（如 2026-07-01，即创建时间 ≥ 该日期算本期新产出）
- 上期"新产出"判定截止日期（如 2026-06-16）
- 两期的时间标签（如"7月上半月"和"6月下半月"）

2. 运行分析脚本

使用 material_analysis.py 一次性完成数据解析、聚合、环比计算和 HTML 报告生成：

python material_analysis.py \
  --new-file "本期文件路径" \
  --old-file "上期文件路径" \
  --new-cutoff "2026-07-01" \
  --old-cutoff "2026-06-16" \
  --new-label "7月上半月" \
  --old-label "6月下半月" \
  --output "输出HTML路径"

> 如仅需单期分析（无环比），省略 --old-file、--old-cutoff、--old-label 参数即可。

脚本依赖 pandas 和 openpyxl，安装方式：pip install pandas openpyxl

3. 审阅并汇报

脚本运行后：
- 控制台输出各维度汇总数据用于快速校验
- 生成 HTML 报告文件（含两张主表 + 分组汇总 + 综合洞察 + 公式说明）
- 在回复中用文字汇总核心结论（剪辑维度要点 + 编导维度要点 + 分组维度要点 + 关键建议）

七、报告输出规范

• 最多 2 张主表：表一剪辑维度、表二编导维度（含分组标记）
• 分组汇总表：附加在文字洞察区
• 环比标注：消耗占比变化(pp)、排名变化(↑N/↓N)、各比率指标涨跌(↑X.XXpp 或 ↑X.X%)
• 颜色规范：红涨绿跌（中国股市惯例），红色 #e8533e，绿色 #2bb673
• 公式说明栏：报告底部附指标公式说明
• 综合洞察：整体趋势 → 剪辑要点 → 编导要点 → 分组要点 → 关键建议

八、注意事项

1. 数据类型兼容：不同批次导出的 Excel 数值格式可能不同（数值型 vs 字符串带逗号/百分号），parse_num() 统一处理
2. 列索引验证：首次使用新文件时，先检查列名是否与列映射一致，如有偏移需调整 COL_* 常量
3. 环比口径一致：两期数据必须使用相同的列映射和公式口径
4. 编导分组归属：同一编导可能跨组出现（如部分在未分组、部分在一组），取消耗占比最大的组作为该编导的主分组

九、完整脚本代码

保存为 material_analysis.py：

#!/usr/bin/env python3
"""
伊菲丹官旗素材数据分析脚本
支持单期分析和环比两期对比，生成HTML报告。

用法：
  # 环比对比
  python material_analysis.py \
    --new-file "本期.xlsx" \
    --old-file "上期.xlsx" \
    --new-cutoff "2026-07-01" \
    --old-cutoff "2026-06-16" \
    --new-label "7月上半月" \
    --old-label "6月下半月" \
    --output "报告.html"

  # 单期分析
  python material_analysis.py \
    --new-file "本期.xlsx" \
    --new-cutoff "2026-07-01" \
    --new-label "7月上半月" \
    --output "报告.html"
"""

import argparse
import json
import os
import re
import sys
import pandas as pd


# ============ 列索引映射 ============
COL_NAME = 0
COL_CREATE_TIME = 4
COL_CONSUMPTION = 7
COL_CLICK_RATE = 11
COL_CONVERSION_RATE = 12
COL_CLICKS = 13
COL_IMPRESSIONS = 14
COL_TOTAL_ORDERS = 16
COL_COMPLETION_RATE = 17
COL_3S_PLAY_RATE = 20
COL_NET_SALES = 23
COL_NET_ORDERS = 24


def parse_num(val):
    """解析数值：处理带逗号的字符串、带%的百分比字符串、普通数值"""
    if pd.isna(val):
        return 0.0
    if isinstance(val, (int, float)):
        return float(val)
    s = str(val).strip()
    if not s or s == '-':
        return 0.0
    is_percent = '%' in s
    s = s.replace(',', '').replace('%', '').replace(' ', '').strip()
    try:
        v = float(s)
    except (ValueError, TypeError):
        return 0.0
    if is_percent:
        v = v / 100.0
    return v


def extract_editor(name):
    """从素材名称提取剪辑"""
    m = re.match(r'【亚瑟】([^-]+)', str(name))
    if m:
        return m.group(1).strip()
    return '未识别'


def extract_director_and_group(name):
    """从素材名称提取编导和分组"""
    name = str(name)
    m = re.search(r'(A|B|一组|二组)?(谢[^\-]+|瑶[^\-]+)-', name)
    if m:
        grp_raw = m.group(1) or ''
        director = m.group(2)
        if grp_raw in ('A', '一组'):
            group = '一组'
        elif grp_raw in ('B', '二组'):
            group = '二组'
        else:
            group = '未分组'
        return director, group
    return None, '未分组'


def process_file(filepath, cutoff_date):
    """读取Excel并解析为记录列表"""
    df = pd.read_excel(filepath, engine='openpyxl')
    records = []
    for _, row in df.iterrows():
        name = str(row.iloc[COL_NAME])
        editor = extract_editor(name)
        director, group = extract_director_and_group(name)

        create_time = row.iloc[COL_CREATE_TIME]
        try:
            create_time = pd.to_datetime(create_time)
        except Exception:
            create_time = None

        consumption = parse_num(row.iloc[COL_CONSUMPTION])
        clicks = parse_num(row.iloc[COL_CLICKS])
        impressions = parse_num(row.iloc[COL_IMPRESSIONS])
        total_orders = parse_num(row.iloc[COL_TOTAL_ORDERS])
        net_sales = parse_num(row.iloc[COL_NET_SALES])
        net_orders = parse_num(row.iloc[COL_NET_ORDERS])
        completion_rate = parse_num(row.iloc[COL_COMPLETION_RATE])
        play3s_rate = parse_num(row.iloc[COL_3S_PLAY_RATE])

        is_new = False
        if create_time is not None and not pd.isna(create_time):
            if create_time >= pd.Timestamp(cutoff_date):
                is_new = True

        records.append({
            'editor': editor,
            'director': director if director else '未识别',
            'group': group,
            'consumption': consumption,
            'clicks': clicks,
            'impressions': impressions,
            'total_orders': total_orders,
            'net_sales': net_sales,
            'net_orders': net_orders,
            'completion_rate': completion_rate,
            'play3s_rate': play3s_rate,
            'is_new': is_new,
        })
    return records


def aggregate(records, key_field):
    """按指定字段聚合统计"""
    groups = {}
    for r in records:
        key = r[key_field]
        if key not in groups:
            groups[key] = {
                'consumption': 0, 'clicks': 0, 'impressions': 0,
                'total_orders': 0, 'net_sales': 0, 'net_orders': 0,
                'completion_rate_sum': 0, 'play3s_rate_sum': 0,
                'count': 0, 'new_count': 0,
            }
        g = groups[key]
        g['consumption'] += r['consumption']
        g['clicks'] += r['clicks']
        g['impressions'] += r['impressions']
        g['total_orders'] += r['total_orders']
        g['net_sales'] += r['net_sales']
        g['net_orders'] += r['net_orders']
        g['completion_rate_sum'] += r['completion_rate'] * r['impressions']
        g['play3s_rate_sum'] += r['play3s_rate'] * r['impressions']
        g['count'] += 1
        if r['is_new']:
            g['new_count'] += 1

    total_consumption = sum(g['consumption'] for g in groups.values())
    results = []
    for key, g in groups.items():
        roi = g['net_sales'] / g['consumption'] if g['consumption'] > 0 else 0
        cpm = g['consumption'] / g['impressions'] * 1000 if g['impressions'] > 0 else 0
        avg_consumption = g['consumption'] / g['count'] if g['count'] > 0 else 0
        ctr = g['clicks'] / g['impressions'] if g['impressions'] > 0 else 0
        cvr = g['total_orders'] / g['clicks'] if g['clicks'] > 0 else 0
        play3s = g['play3s_rate_sum'] / g['impressions'] if g['impressions'] > 0 else 0
        completion = g['completion_rate_sum'] / g['impressions'] if g['impressions'] > 0 else 0
        share = g['consumption'] / total_consumption if total_consumption > 0 else 0

        results.append({
            'name': key,
            'consumption': g['consumption'],
            'consumption_share': share,
            'net_roi': roi,
            'net_orders': int(g['net_orders']),
            'avg_consumption': avg_consumption,
            'ctr': ctr,
            'cvr': cvr,
            'play3s_rate': play3s,
            'completion_rate': completion,
            'cpm': cpm,
            'new_count': g['new_count'],
            'total_count': g['count'],
        })

    results.sort(key=lambda x: x['consumption'], reverse=True)
    for i, r in enumerate(results):
        r['rank'] = i + 1
    return results, total_consumption


def aggregate_by_director_with_group(records):
    """按编导聚合，并确定每个编导的主分组"""
    dir_group_map = {}
    for r in records:
        d = r['director']
        if d not in dir_group_map:
            dir_group_map[d] = {}
        g = r['group']
        if g not in dir_group_map[d]:
            dir_group_map[d][g] = 0
        dir_group_map[d][g] += r['consumption']

    results, total = aggregate(records, 'director')
    for r in results:
        d = r['name']
        if d in dir_group_map:
            best_group = max(dir_group_map[d].items(), key=lambda x: x[1])
            r['group'] = best_group[0]
        else:
            r['group'] = '未分组'
    return results, total


# ============ HTML 生成 ============

def fmt_pct(v, decimals=2):
    return f'{v*100:.{decimals}f}%'

def fmt_num(v):
    if v == 0:
        return '0'
    return f'{v:,.0f}'

def fmt_money(v):
    return f'{v:,.0f}'

def fmt_roi(v):
    return f'{v:.2f}'

def fmt_cpm(v):
    return f'{v:.0f}'

def change_arrow(curr, prev, is_rate=False, higher_better=True):
    if prev is None or prev == 0:
        if curr == 0:
            return '<span style="color:#999">—</span>'
        return '<span style="color:#999">新增</span>'
    if curr == 0 and prev > 0:
        return '<span style="color:#999">退出</span>'
    if is_rate:
        diff_pp = (curr - prev) * 100
        arrow = '↑' if diff_pp > 0 else ('↓' if diff_pp < 0 else '→')
        color = '#e8533e' if (diff_pp > 0) == higher_better else '#2bb673' if (diff_pp < 0) == higher_better else '#999'
        if diff_pp == 0:
            color = '#999'
        return f'<span style="color:{color}">{arrow}{abs(diff_pp):.2f}pp</span>'
    else:
        diff = curr - prev
        pct_change = (diff / prev) * 100 if prev != 0 else 0
        arrow = '↑' if diff > 0 else ('↓' if diff < 0 else '→')
        color = '#e8533e' if (diff > 0) == higher_better else '#2bb673' if (diff < 0) == higher_better else '#999'
        if diff == 0:
            color = '#999'
        return f'<span style="color:{color}">{arrow}{abs(pct_change):.1f}%</span>'

def rank_change(curr_rank, prev_rank):
    if prev_rank is None:
        return '<span style="color:#999">新增</span>'
    diff = prev_rank - curr_rank
    if diff > 0:
        return f'<span style="color:#e8533e">↑{diff}</span>'
    elif diff < 0:
        return f'<span style="color:#2bb673">↓{abs(diff)}</span>'
    else:
        return '<span style="color:#999">→</span>'

def share_change(curr_share, prev_share):
    if prev_share is None:
        return '<span style="color:#999">新增</span>'
    diff = (curr_share - prev_share) * 100
    if diff > 0:
        return f'<span style="color:#e8533e">↑{diff:.1f}pp</span>'
    elif diff < 0:
        return f'<span style="color:#2bb673">↓{abs(diff):.1f}pp</span>'
    else:
        return '<span style="color:#999">→</span>'

def generate_table_header(has_compare, has_group=False):
    """生成表格表头"""
    group_col = '<th rowspan="2">分组</th>' if has_group else ''
    compare_cols = ''
    roi_cols = ''
    if has_compare:
        compare_cols = '''
                    <th colspan="3" style="border-bottom:1px solid #e8e8e8">消耗环比</th>
                    <th colspan="2" style="border-bottom:1px solid #e8e8e8">净成交ROI</th>'''
        roi_cols = '<th>本期</th><th style="background:#f0f1f5">环比</th>'
    else:
        roi_cols = '<th>本期</th>'

    rate_pairs = ''
    rate_subheads = ''
    for label in ['点击率', '转化率', '3s播放率', '完播率']:
        if has_compare:
            rate_pairs += f'<th colspan="2" style="border-bottom:1px solid #e8e8e8">{label}</th>'
            rate_subheads += f'<th>本期</th><th style="background:#f0f1f5">环比</th>'
        else:
            rate_pairs += f'<th>{label}</th>'

    header = f'''<tr>
                    <th rowspan="2">排名</th>
                    <th rowspan="2">{"编导" if has_group else "剪辑"}</th>
                    {group_col}
                    <th rowspan="2">消耗(¥)</th>
                    {compare_cols}
                    {roi_cols}
                    <th rowspan="2">净成交<br>订单数</th>
                    <th rowspan="2">条均<br>消耗(¥)</th>
                    <th rowspan="2">CPM<br>(¥)</th>
                    {rate_pairs}
                    <th rowspan="2">周期<br>产出</th>
                    <th rowspan="2">在投<br>总数</th>
                </tr>
                <tr>
                    {"<th style='background:#f0f1f5'>占比</th><th style='background:#f0f1f5'>占比变化</th><th style='background:#f0f1f5'>排名变化</th>" if has_compare else ""}
                    {"<th style='background:#f0f1f5'>环比</th>" if has_compare else ""}
                    {rate_subheads}
                </tr>'''
    return header


def generate_editor_rows(new_editors, old_ed_map, has_compare):
    rows = []
    for e in new_editors:
        name = e['name']
        old_e = old_ed_map.get(name) if old_ed_map else None

        if has_compare:
            prev_share = old_e['consumption_share'] if old_e else None
            prev_rank = old_e['rank'] if old_e else None
            prev_roi = old_e['net_roi'] if old_e else None
            prev_ctr = old_e['ctr'] if old_e else None
            prev_cvr = old_e['cvr'] if old_e else None
            prev_3s = old_e['play3s_rate'] if old_e else None
            prev_comp = old_e['completion_rate'] if old_e else None

            share_cell = share_change(e['consumption_share'], prev_share)
            rank_cell = rank_change(e['rank'], prev_rank)
            roi_change = change_arrow(e['net_roi'], prev_roi, False, True) if prev_roi is not None else '<span style="color:#999">新增</span>'
            ctr_change = change_arrow(e['ctr'], prev_ctr, True, True) if prev_ctr is not None else '<span style="color:#999">新增</span>'
            cvr_change = change_arrow(e['cvr'], prev_cvr, True, True) if prev_cvr is not None else '<span style="color:#999">新增</span>'
            s3_change = change_arrow(e['play3s_rate'], prev_3s, True, True) if prev_3s is not None else '<span style="color:#999">新增</span>'
            comp_change = change_arrow(e['completion_rate'], prev_comp, True, True) if prev_comp is not None else '<span style="color:#999">新增</span>'

            compare_cells = f'''<td style="text-align:center">{fmt_pct(e['consumption_share'],1)}</td>
        <td style="text-align:center">{share_cell}</td>
        <td style="text-align:center">{rank_cell}</td>'''
            rate_cells = f'''<td style="text-align:center">{fmt_pct(e['ctr'])}</td><td style="text-align:center">{ctr_change}</td>
        <td style="text-align:center">{fmt_pct(e['cvr'])}</td><td style="text-align:center">{cvr_change}</td>
        <td style="text-align:center">{fmt_pct(e['play3s_rate'])}</td><td style="text-align:center">{s3_change}</td>
        <td style="text-align:center">{fmt_pct(e['completion_rate'])}</td><td style="text-align:center">{comp_change}</td>'''
        else:
            compare_cells = f'<td style="text-align:center">{fmt_pct(e['consumption_share'],1)}</td>'
            rate_cells = f'''<td style="text-align:center">{fmt_pct(e['ctr'])}</td>
        <td style="text-align:center">{fmt_pct(e['cvr'])}</td>
        <td style="text-align:center">{fmt_pct(e['play3s_rate'])}</td>
        <td style="text-align:center">{fmt_pct(e['completion_rate'])}</td>'''

        row = f'''<tr>
        <td style="text-align:center;font-weight:600">{e['rank']}</td>
        <td style="font-weight:600">{name}</td>
        <td style="text-align:right">{fmt_money(e['consumption'])}</td>
        {compare_cells}
        <td style="text-align:center">{fmt_roi(e['net_roi'])}</td>
        {"<td style='text-align:center'>" + roi_change + "</td>" if has_compare else ""}
        <td style="text-align:right">{e['net_orders']}</td>
        <td style="text-align:right">{fmt_money(e['avg_consumption'])}</td>
        <td style="text-align:center">{fmt_cpm(e['cpm'])}</td>
        {rate_cells}
        <td style="text-align:center;font-weight:600;color:#e8533e">{e['new_count']}</td>
        <td style="text-align:center;color:#666">{e['total_count']}</td>
    </tr>'''
        rows.append(row)
    return ''.join(rows)


def generate_director_rows(new_directors, old_dir_map, has_compare):
    rows = []
    for d in new_directors:
        name = d['name']
        old_d = old_dir_map.get(name) if old_dir_map else None
        group_label = d.get('group', '未分组')
        group_color = {'一组': '#4e7cff', '二组': '#9b59b6', '未分组': '#999'}.get(group_label, '#999')

        if has_compare:
            prev_share = old_d['consumption_share'] if old_d else None
            prev_rank = old_d['rank'] if old_d else None
            prev_roi = old_d['net_roi'] if old_d else None
            prev_ctr = old_d['ctr'] if old_d else None
            prev_cvr = old_d['cvr'] if old_d else None
            prev_3s = old_d['play3s_rate'] if old_d else None
            prev_comp = old_d['completion_rate'] if old_d else None

            share_cell = share_change(d['consumption_share'], prev_share)
            rank_cell = rank_change(d['rank'], prev_rank)
            roi_change = change_arrow(d['net_roi'], prev_roi, False, True) if prev_roi is not None else '<span style="color:#999">新增</span>'
            ctr_change = change_arrow(d['ctr'], prev_ctr, True, True) if prev_ctr is not None else '<span style="color:#999">新增</span>'
            cvr_change = change_arrow(d['cvr'], prev_cvr, True, True) if prev_cvr is not None else '<span style="color:#999">新增</span>'
            s3_change = change_arrow(d['play3s_rate'], prev_3s, True, True) if prev_3s is not None else '<span style="color:#999">新增</span>'
            comp_change = change_arrow(d['completion_rate'], prev_comp, True, True) if prev_comp is not None else '<span style="color:#999">新增</span>'

            compare_cells = f'''<td style="text-align:center">{fmt_pct(d['consumption_share'],1)}</td>
        <td style="text-align:center">{share_cell}</td>
        <td style="text-align:center">{rank_cell}</td>'''
            rate_cells = f'''<td style="text-align:center">{fmt_pct(d['ctr'])}</td><td style="text-align:center">{ctr_change}</td>
        <td style="text-align:center">{fmt_pct(d['cvr'])}</td><td style="text-align:center">{cvr_change}</td>
        <td style="text-align:center">{fmt_pct(d['play3s_rate'])}</td><td style="text-align:center">{s3_change}</td>
        <td style="text-align:center">{fmt_pct(d['completion_rate'])}</td><td style="text-align:center">{comp_change}</td>'''
        else:
            compare_cells = f'<td style="text-align:center">{fmt_pct(d['consumption_share'],1)}</td>'
            rate_cells = f'''<td style="text-align:center">{fmt_pct(d['ctr'])}</td>
        <td style="text-align:center">{fmt_pct(d['cvr'])}</td>
        <td style="text-align:center">{fmt_pct(d['play3s_rate'])}</td>
        <td style="text-align:center">{fmt_pct(d['completion_rate'])}</td>'''

        row = f'''<tr>
        <td style="text-align:center;font-weight:600">{d['rank']}</td>
        <td style="font-weight:600">{name}</td>
        <td style="text-align:center"><span style="background:{group_color};color:#fff;padding:1px 6px;border-radius:3px;font-size:11px">{group_label}</span></td>
        <td style="text-align:right">{fmt_money(d['consumption'])}</td>
        {compare_cells}
        <td style="text-align:center">{fmt_roi(d['net_roi'])}</td>
        {"<td style='text-align:center'>" + roi_change + "</td>" if has_compare else ""}
        <td style="text-align:right">{d['net_orders']}</td>
        <td style="text-align:right">{fmt_money(d['avg_consumption'])}</td>
        <td style="text-align:center">{fmt_cpm(d['cpm'])}</td>
        {rate_cells}
        <td style="text-align:center;font-weight:600;color:#e8533e">{d['new_count']}</td>
        <td style="text-align:center;color:#666">{d['total_count']}</td>
    </tr>'''
        rows.append(row)
    return ''.join(rows)


def generate_group_rows(new_groups, old_grp_map, has_compare):
    rows = []
    all_groups = ['未分组', '一组', '二组']
    for gname in all_groups:
        ng = None
        for g in new_groups:
            if g['name'] == gname:
                ng = g
                break
        if ng is None:
            continue

        og = old_grp_map.get(gname) if old_grp_map else None
        group_color = {'一组': '#4e7cff', '二组': '#9b59b6', '未分组': '#999'}.get(gname, '#999')

        if has_compare:
            prev_share = og['consumption_share'] if og else None
            prev_roi = og['net_roi'] if og else None
            prev_ctr = og['ctr'] if og else None
            prev_cvr = og['cvr'] if og else None
            prev_3s = og['play3s_rate'] if og else None
            prev_comp = og['completion_rate'] if og else None

            share_cell = share_change(ng['consumption_share'], prev_share)
            roi_change = change_arrow(ng['net_roi'], prev_roi, False, True) if prev_roi is not None else '<span style="color:#999">新增</span>'
            ctr_change = change_arrow(ng['ctr'], prev_ctr, True, True) if prev_ctr is not None else '<span style="color:#999">新增</span>'
            cvr_change = change_arrow(ng['cvr'], prev_cvr, True, True) if prev_cvr is not None else '<span style="color:#999">新增</span>'
            s3_change = change_arrow(ng['play3s_rate'], prev_3s, True, True) if prev_3s is not None else '<span style="color:#999">新增</span>'
            comp_change = change_arrow(ng['completion_rate'], prev_comp, True, True) if prev_comp is not None else '<span style="color:#999">新增</span>'
        else:
            share_cell = '—'
            roi_change = ctr_change = cvr_change = s3_change = comp_change = '—'

        rows.append(f'''<tr>
        <td style="text-align:center"><span style="background:{group_color};color:#fff;padding:2px 8px;border-radius:3px;font-size:12px">{gname}</span></td>
        <td style="text-align:right">{fmt_money(ng['consumption'])}</td>
        <td style="text-align:center">{fmt_pct(ng['consumption_share'],1)}</td>
        <td style="text-align:center">{share_cell}</td>
        <td style="text-align:center">{fmt_roi(ng['net_roi'])}</td>
        <td style="text-align:center">{roi_change}</td>
        <td style="text-align:center">{fmt_pct(ng['ctr'])}</td>
        <td style="text-align:center">{ctr_change}</td>
        <td style="text-align:center">{fmt_pct(ng['cvr'])}</td>
        <td style="text-align:center">{cvr_change}</td>
        <td style="text-align:center">{fmt_pct(ng['play3s_rate'])}</td>
        <td style="text-align:center">{s3_change}</td>
        <td style="text-align:center">{fmt_pct(ng['completion_rate'])}</td>
        <td style="text-align:center">{comp_change}</td>
    </tr>''')
    return ''.join(rows)


def generate_html(args, new_editors, new_directors, new_groups, new_total,
                  old_editors, old_directors, old_groups, old_total):
    has_compare = old_editors is not None

    old_ed_map = {e['name']: e for e in old_editors} if old_editors else None
    old_dir_map = {d['name']: d for d in old_directors} if old_directors else None
    old_grp_map = {g['name']: g for g in old_groups} if old_groups else None

    editor_rows = generate_editor_rows(new_editors, old_ed_map, has_compare)
    director_rows = generate_director_rows(new_directors, old_dir_map, has_compare)
    group_rows = generate_group_rows(new_groups, old_grp_map, has_compare)

    if has_compare:
        overall_old = {
            'net_roi': sum(e['net_roi'] * e['consumption'] for e in old_editors) / old_total if old_total > 0 else 0,
            'ctr': sum(e['ctr'] * e['consumption'] for e in old_editors) / old_total if old_total > 0 else 0,
            'cvr': sum(e['cvr'] * e['consumption'] for e in old_editors) / old_total if old_total > 0 else 0,
        }
        overall_new = {
            'net_roi': sum(e['net_roi'] * e['consumption'] for e in new_editors) / new_total if new_total > 0 else 0,
            'ctr': sum(e['ctr'] * e['consumption'] for e in new_editors) / new_total if new_total > 0 else 0,
            'cvr': sum(e['cvr'] * e['consumption'] for e in new_editors) / new_total if new_total > 0 else 0,
        }
        subtitle = f'{args.new_label} vs {args.old_label} 环比分析'
        summary_cards = f'''<div class="summary-cards">
    <div class="summary-card"><div class="label">本期总消耗</div><div class="value" style="color:#e8533e">¥{fmt_money(new_total)}</div><div class="change" style="color:{'#e8533e' if new_total > old_total else '#2bb673'}">环比 {'↑' if new_total > old_total else '↓'}{abs((new_total-old_total)/old_total*100):.1f}%</div></div>
    <div class="summary-card"><div class="label">上期总消耗</div><div class="value" style="color:#999">¥{fmt_money(old_total)}</div><div class="change" style="color:#999">基准期</div></div>
    <div class="summary-card"><div class="label">本期整体ROI</div><div class="value" style="color:#e8533e">{fmt_roi(overall_new['net_roi'])}</div><div class="change">{change_arrow(overall_new['net_roi'], overall_old['net_roi'], False, True)}</div></div>
    <div class="summary-card"><div class="label">本期素材条数</div><div class="value">{sum(e['total_count'] for e in new_editors)}</div><div class="change" style="color:#999">上期{sum(e['total_count'] for e in old_editors)}条</div></div>
    <div class="summary-card"><div class="label">本期新产出</div><div class="value" style="color:#e8533e">{sum(e['new_count'] for e in new_editors)}条</div><div class="change" style="color:#999">上期{sum(e['new_count'] for e in old_editors)}条</div></div>
</div>'''
        legend = '<div class="legend"><span><span class="color-red">↑</span> 上升/改善</span><span><span class="color-green">↓</span> 下降/回落</span><span style="color:#999">→ 持平</span><span style="color:#999">注：红涨绿跌</span></div>'
    else:
        overall_new = {
            'net_roi': sum(e['net_roi'] * e['consumption'] for e in new_editors) / new_total if new_total > 0 else 0,
            'ctr': sum(e['ctr'] * e['consumption'] for e in new_editors) / new_total if new_total > 0 else 0,
            'cvr': sum(e['cvr'] * e['consumption'] for e in new_editors) / new_total if new_total > 0 else 0,
        }
        subtitle = args.new_label
        summary_cards = f'''<div class="summary-cards">
    <div class="summary-card"><div class="label">总消耗</div><div class="value" style="color:#e8533e">¥{fmt_money(new_total)}</div></div>
    <div class="summary-card"><div class="label">整体ROI</div><div class="value" style="color:#e8533e">{fmt_roi(overall_new['net_roi'])}</div></div>
    <div class="summary-card"><div class="label">素材条数</div><div class="value">{sum(e['total_count'] for e in new_editors)}</div></div>
    <div class="summary-card"><div class="label">新产出</div><div class="value" style="color:#e8533e">{sum(e['new_count'] for e in new_editors)}条</div></div>
</div>'''
        legend = ''

    editor_header = generate_table_header(has_compare, has_group=False)
    director_header = generate_table_header(has_compare, has_group=True)

    cutoff_display = args.new_cutoff
    period_info = f'周期内新产出判定：素材创建时间 ≥ {cutoff_display}'

    html = f'''<!DOCTYPE html>
<html lang="zh-CN">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>伊菲丹官旗素材数据报告 - {args.new_label}</title>
<style>
* {{ margin: 0; padding: 0; box-sizing: border-box; }}
body {{ font-family: -apple-system, "Microsoft YaHei", "Segoe UI", sans-serif; background: #f5f6fa; color: #333; line-height: 1.6; padding: 20px; }}
.container {{ max-width: 1600px; margin: 0 auto; }}
.header {{ background: linear-gradient(135deg, #667eea 0%, #764ba2 100%); color: #fff; padding: 28px 32px; border-radius: 12px 12px 0 0; }}
.header h1 {{ font-size: 22px; font-weight: 700; }}
.header .subtitle {{ font-size: 14px; opacity: 0.9; margin-top: 6px; }}
.header .period {{ font-size: 13px; opacity: 0.8; margin-top: 4px; }}
.summary-cards {{ display: flex; gap: 16px; padding: 20px 32px; background: #fff; border-bottom: 1px solid #e8e8e8; }}
.summary-card {{ flex: 1; text-align: center; }}
.summary-card .label {{ font-size: 12px; color: #999; }}
.summary-card .value {{ font-size: 20px; font-weight: 700; margin-top: 4px; }}
.summary-card .change {{ font-size: 12px; margin-top: 2px; }}
.section {{ background: #fff; border-radius: 0 0 12px 12px; margin-bottom: 24px; overflow: hidden; box-shadow: 0 2px 8px rgba(0,0,0,0.06); }}
.section-title {{ padding: 16px 24px; font-size: 16px; font-weight: 700; color: #333; border-bottom: 2px solid #f0f0f0; display: flex; align-items: center; gap: 8px; }}
.section-title .icon {{ width: 4px; height: 18px; border-radius: 2px; background: #667eea; }}
.table-wrapper {{ overflow-x: auto; }}
table {{ width: 100%; border-collapse: collapse; font-size: 12px; white-space: nowrap; }}
thead th {{ background: #f8f9fc; padding: 10px 8px; text-align: center; font-weight: 600; color: #666; border-bottom: 2px solid #e8e8e8; }}
tbody td {{ padding: 8px; border-bottom: 1px solid #f0f0f0; }}
tbody tr:hover {{ background: #fafbfc; }}
.text-section {{ background: #fff; border-radius: 12px; padding: 24px 28px; margin-bottom: 24px; box-shadow: 0 2px 8px rgba(0,0,0,0.06); }}
.text-section h2 {{ font-size: 16px; font-weight: 700; margin-bottom: 12px; color: #333; }}
.text-section p {{ font-size: 13px; color: #555; margin-bottom: 8px; }}
.text-section .highlight {{ color: #e8533e; font-weight: 600; }}
.formula-box {{ background: #f8f9fc; border-left: 3px solid #667eea; padding: 12px 16px; margin: 12px 0; font-size: 12px; color: #666; }}
.formula-box code {{ background: #eef; padding: 1px 4px; border-radius: 2px; font-size: 11px; }}
.legend {{ display: flex; gap: 16px; padding: 8px 24px 16px; font-size: 11px; color: #999; }}
.legend span {{ display: flex; align-items: center; gap: 4px; }}
.color-red {{ color: #e8533e; }}
.color-green {{ color: #2bb673; }}
</style>
</head>
<body>
<div class="container">

<div class="header">
    <h1>伊菲丹官旗素材数据报告</h1>
    <div class="subtitle">{subtitle}</div>
    <div class="period">{period_info}</div>
</div>

{summary_cards}

<div class="section">
    <div class="section-title"><div class="icon"></div>表一：剪辑维度数据汇总</div>
    {legend}
    <div class="table-wrapper">
        <table>
            <thead>{editor_header}</thead>
            <tbody>{editor_rows}</tbody>
        </table>
    </div>
</div>

<div class="section">
    <div class="section-title"><div class="icon" style="background:#9b59b6"></div>表二：编导维度数据汇总（含分组）</div>
    {legend}
    <div class="table-wrapper">
        <table>
            <thead>{director_header}</thead>
            <tbody>{director_rows}</tbody>
        </table>
    </div>
</div>

<div class="text-section">
    <h2>分组数据汇总</h2>
    <div class="table-wrapper" style="margin-bottom:16px">
        <table>
            <thead><tr>
                <th>分组</th><th>消耗(¥)</th><th>消耗占比</th><th>占比变化</th>
                <th>净成交ROI</th><th>ROI环比</th>
                <th>点击率</th><th>点击率环比</th>
                <th>转化率</th><th>转化率环比</th>
                <th>3s播放率</th><th>3s环比</th>
                <th>完播率</th><th>完播环比</th>
            </tr></thead>
            <tbody>{group_rows}</tbody>
        </table>
    </div>
</div>

<div class="text-section">
    <h2>综合洞察</h2>
    <p><strong>整体趋势：</strong>本期总消耗 ¥{fmt_money(new_total)}，{'环比上期 ¥' + fmt_money(old_total) + '，' if has_compare else ''}整体净成交ROI {fmt_roi(overall_new['net_roi'])}，整体点击率 {fmt_pct(overall_new['ctr'])}，整体转化率 {fmt_pct(overall_new['cvr'])}。详见上方表格数据。</p>
    <p style="margin-top:8px"><strong>剪辑维度要点和编导维度要点请根据表格数据自行提炼，重点关注：</strong></p>
    <ul style="margin-left:20px;font-size:13px;color:#555">
        <li>消耗占比变化最大的人员（上升/下降）</li>
        <li>ROI 改善/恶化的关键人员</li>
        <li>新产出条数与产出效率</li>
        <li>高3s播放率/完播率但低转化率的"内容力强但带货弱"人员</li>
        <li>无新产出的老素材在持续消耗的情况</li>
    </ul>

    <div class="formula-box" style="margin-top:16px">
        <strong>指标公式说明：</strong><br>
        <code>净成交ROI = 净成交金额 / 消耗</code> | <code>CPM = 消耗 / 展现次数 × 1000</code> | <code>点击率 = 点击次数 / 展现次数</code><br>
        <code>转化率 = 整体成交订单数 / 点击次数</code> | <code>3s播放率 = Σ(各素材3s播放率 × 展现次数) / 展现次数</code><br>
        <code>完播率 = Σ(各素材完播率 × 展现次数) / 展现次数</code> | <code>条均消耗 = 消耗 / 在投素材总条数</code><br>
        <code>周期内产出 = 素材创建时间 ≥ {args.new_cutoff} 的条数</code>
    </div>
</div>

</div>
</body>
</html>'''
    return html


def main():
    parser = argparse.ArgumentParser(description='伊菲丹官旗素材数据分析')
    parser.add_argument('--new-file', required=True, help='本期Excel文件路径')
    parser.add_argument('--old-file', help='上期Excel文件路径（环比对比，可选）')
    parser.add_argument('--new-cutoff', required=True, help='本期新产出截止日期，如 2026-07-01')
    parser.add_argument('--old-cutoff', help='上期新产出截止日期')
    parser.add_argument('--new-label', default='本期', help='本期标签，如"7月上半月"')
    parser.add_argument('--old-label', default='上期', help='上期标签')
    parser.add_argument('--output', default='素材数据报告.html', help='输出HTML文件路径')
    args = parser.parse_args()

    print(f'读取本期文件: {args.new_file}')
    new_records = process_file(args.new_file, args.new_cutoff)
    new_editors, new_total = aggregate(new_records, 'editor')
    new_directors, _ = aggregate_by_director_with_group(new_records)
    new_groups, _ = aggregate(new_records, 'group')
    print(f'本期: {len(new_records)} 条记录, 总消耗 ¥{new_total:,.0f}')

    old_editors = old_directors = old_groups = None
    old_total = 0
    if args.old_file:
        print(f'读取上期文件: {args.old_file}')
        old_records = process_file(args.old_file, args.old_cutoff or args.new_cutoff)
        old_editors, old_total = aggregate(old_records, 'editor')
        old_directors, _ = aggregate_by_director_with_group(old_records)
        old_groups, _ = aggregate(old_records, 'group')
        print(f'上期: {len(old_records)} 条记录, 总消耗 ¥{old_total:,.0f}')

    print(f'\n=== {args.new_label} 剪辑维度 ===')
    for e in new_editors:
        print(f"  {e['name']}: 排名={e['rank']}, 消耗={e['consumption']:,.0f}, 占比={e['consumption_share']:.1%}, ROI={e['net_roi']:.2f}, CTR={e['ctr']:.2%}, CVR={e['cvr']:.2%}, 3s={e['play3s_rate']:.2%}, 完播={e['completion_rate']:.2%}, 新产出={e['new_count']}")

    print(f'\n=== {args.new_label} 编导维度 ===')
    for d in new_directors:
        print(f"  {d['name']}({d.get('group','未分组')}): 排名={d['rank']}, 消耗={d['consumption']:,.0f}, 占比={d['consumption_share']:.1%}, ROI={d['net_roi']:.2f}, CTR={d['ctr']:.2%}, CVR={d['cvr']:.2%}, 3s={d['play3s_rate']:.2%}, 完播={d['completion_rate']:.2%}, 新产出={d['new_count']}")

    print(f'\n=== {args.new_label} 分组维度 ===')
    for g in new_groups:
        print(f"  {g['name']}: 消耗={g['consumption']:,.0f}, 占比={g['consumption_share']:.1%}, ROI={g['net_roi']:.2f}, CTR={g['ctr']:.2%}, CVR={g['cvr']:.2%}")

    html = generate_html(args, new_editors, new_directors, new_groups, new_total,
                         old_editors, old_directors, old_groups, old_total)

    output_path = os.path.abspath(args.output)
    with open(output_path, 'w', encoding='utf-8') as f:
        f.write(html)
    print(f'\nHTML报告已生成: {output_path}')
    print(f'文件大小: {os.path.getsize(output_path) / 1024:.1f} KB')


if __name__ == '__main__':
    main()