from pathlib import Path
import json, re
import openpyxl, xlrd
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter
from openpyxl.worksheet.dimensions import ColumnDimension

BASE=Path('/Users/bot1/Volumes/root_for_ai/AI工作区/良渚文化_月报_2026年5月_20260601_1123')
W=BASE/'work'; D=BASE/'deliverables'; SRC=BASE/'source'
store=json.loads((W/'store_monthly.json').read_text(encoding='utf-8'))
products=json.loads((W/'product_monthly.json').read_text(encoding='utf-8'))
promos=json.loads((W/'promo_plan_rows.json').read_text(encoding='utf-8'))

by={(r['平台'],r['月份']):dict(r) for r in store}
# Supplement JD Apr/May units/refunds from product-detail totals only if store-level tradeSummary did not provide them.
for month in ['2026-04','2026-05']:
    r=by.get(('京东',month))
    rows=[x for x in products if x['平台']=='京东' and x['月份']==month]
    if r and rows:
        if r.get('支付件数') is None:
            r['支付件数']=sum((x.get('支付件数') or 0) for x in rows)
        if r.get('退款金额') is None:
            r['退款金额']=sum((x.get('退款金额') or 0) for x in rows)
            r['实际成交金额']=(r.get('支付金额') or 0)-r['退款金额']
        r['转化率']=r.get('转化率')

def rate(cur, base):
    return None if cur is None or base in [None,0] else (cur-base)/base

def total(rows, month, platform='合计'):
    out={'平台':platform,'月份':month}
    for col in ['访客数','支付金额','退款金额','实际成交金额','支付买家数','支付件数','加购人数','收藏次数']:
        vals=[r.get(col) for r in rows if r.get(col) is not None]
        out[col]=sum(vals) if vals else None
    out['转化率']=out['支付买家数']/out['访客数'] if out.get('支付买家数') and out.get('访客数') else None
    out['客单价']=out['支付金额']/out['支付买家数'] if out.get('支付金额') and out.get('支付买家数') else None
    out['件单价']=out['支付金额']/out['支付件数'] if out.get('支付金额') and out.get('支付件数') else None
    return out

platforms=['天猫','淘宝','京东']
may=[by[(p,'2026-05')] for p in platforms]
apr=[by[(p,'2026-04')] for p in platforms]
may_total=total(may,'2026-05','三渠道合计')
apr_total=total(apr,'2026-04','三渠道合计')
comp_plats=[p for p in platforms if (p,'2025-05') in by and (p,'2026-05') in by]
comp_name='可比渠道合计（' + '+'.join(comp_plats) + '）'
yoy_name='去年同期可比渠道合计（' + '+'.join(comp_plats) + '）'
may_comp=total([by[(p,'2026-05')] for p in comp_plats],'2026-05',comp_name)
yoy_comp=total([by[(p,'2025-05')] for p in comp_plats],'2025-05',yoy_name)

def money(v): return '' if v is None else f'¥{float(v):,.2f}'
def num(v): return '' if v is None else f'{float(v):,.0f}'
def pct(v, nd=1): return '' if v is None else f'{float(v)*100:.{nd}f}%'

# product helpers
prod_may=[r for r in products if r['月份']=='2026-05']
def top_products(platform, n=20):
    return sorted([r for r in prod_may if r['平台']==platform], key=lambda x:x.get('实际成交金额') or 0, reverse=True)[:n]
new_rows=[r for r in prod_may if r.get('新品类别')]
new_summary={}
for r in new_rows:
    for cat in (r.get('新品类别') or '').split('、'):
        if not cat: continue
        cat=cat.replace('礼乐粽子','礼阅粽子')
        key=(cat,r['平台'])
        d=new_summary.setdefault(key, {'新品类别':cat,'平台':r['平台'],'商品数':0,'访客数':0,'支付件数':0,'支付金额':0,'退款金额':0,'实际成交金额':0})
        d['商品数']+=1
        for col in ['访客数','支付件数','支付金额','退款金额','实际成交金额']:
            d[col]+=r.get(col) or 0
new_summary=list(new_summary.values())

# promo helpers
def agg_promo(rows, keys):
    d={}
    for r in rows:
        k=tuple(r.get(x) for x in keys)
        if k not in d:
            d[k]={x:r.get(x) for x in keys}
            for col in ['花费','总成交金额','成交笔数','点击量','展现量']: d[k][col]=0.0
        for col in ['花费','总成交金额','成交笔数','点击量','展现量']:
            d[k][col]+=float(r.get(col) or 0)
    out=list(d.values())
    for r in out:
        r['ROI']=r['总成交金额']/r['花费'] if r['花费'] else None
        r['点击率']=r['点击量']/r['展现量'] if r['展现量'] else None
        r['CPC']=r['花费']/r['点击量'] if r['点击量'] else None
    return out
promo_may_scene=agg_promo([r for r in promos if r['月份']=='2026-05'], ['平台','月份','场景名字'])
promo_may_total=agg_promo([r for r in promos if r['月份']=='2026-05'], ['平台','月份'])
promo_apr_total=agg_promo([r for r in promos if r['月份']=='2026-04'], ['平台','月份'])

# flow parsing for brief source structure
def to_float(v):
    if v is None: return None
    if isinstance(v,(int,float)): return float(v)
    s=str(v).strip().replace(',','').replace('￥','').replace('¥','')
    if s in ['', '-', '--']: return None
    is_pct=s.endswith('%')
    if is_pct: s=s[:-1]
    try:
        x=float(s); return x/100 if is_pct else x
    except Exception:
        return None

def read_xls(path):
    book=xlrd.open_workbook(path)
    sh=book.sheet_by_index(0)
    return [[sh.cell_value(r,c) for c in range(sh.ncols)] for r in range(sh.nrows)]
def parse_flow(path):
    rows=read_xls(path)
    headers=[str(x).strip() for x in rows[5]]
    out=[]
    for row in rows[6:]:
        if all(str(x).strip()=='' for x in row): continue
        d={headers[i]: row[i] if i<len(row) else None for i in range(len(headers)) if headers[i]}
        out.append(d)
    return out
flow_rows=[]
for p in SRC.glob('*无线店铺流量来源-2026-0*.xls'):
    platform='天猫' if '天猫' in p.name else '淘宝'
    m=re.search(r'(2026-\d{2})-', p.name)
    month=m.group(1) if m else ''
    for d in parse_flow(p):
        if str(d.get('流量载体','全店流量')).strip() not in ['','全店流量']: continue
        first=str(d.get('一级来源','')).strip(); second=str(d.get('二级来源','')).strip(); third=str(d.get('三级来源','')).strip(); fourth=str(d.get('四级来源','')).strip()
        if second=='汇总' and third=='汇总' and fourth=='汇总':
            flow_rows.append({'平台':platform,'月份':month,'一级来源':first,'访客数':to_float(d.get('访客数')),'支付金额':to_float(d.get('支付金额')),'支付买家数':to_float(d.get('支付买家数')),'支付转化率':to_float(d.get('支付转化率')),'UV价值':to_float(d.get('UV价值'))})

# workbook styles
wb=Workbook()
ws=wb.active
ws.title='良渚文化5月月报'
ws.sheet_view.showGridLines=False
max_col=18
blue='1F4E78'; dark='17365D'; light='D9EAF7'; pale='EEF5FB'; green='E2F0D9'; orange='FCE4D6'; yellow='FFF2CC'
header_fill=PatternFill('solid', fgColor=blue)
section_fill=PatternFill('solid', fgColor=dark)
analysis_fill=PatternFill('solid', fgColor=pale)
sub_fill=PatternFill('solid', fgColor=light)
thin=Side(style='thin', color='D9E2F3')
border=Border(left=thin,right=thin,top=thin,bottom=thin)
white=Font(color='FFFFFF', bold=True)
section_font=Font(color='FFFFFF', bold=True, size=14)
title_font=Font(color='17365D', bold=True, size=18)
analysis_font=Font(color='1F1F1F', size=11)
row=1

def set_range_style(r1,r2,c1=1,c2=max_col, fill=None, font=None, align=None):
    for rr in range(r1,r2+1):
        for cc in range(c1,c2+1):
            cell=ws.cell(rr,cc)
            cell.border=border
            if fill: cell.fill=fill
            if font: cell.font=font
            if align: cell.alignment=align

def add_title(text):
    global row
    ws.merge_cells(start_row=row,start_column=1,end_row=row,end_column=max_col)
    c=ws.cell(row,1,text)
    c.font=title_font; c.fill=PatternFill('solid', fgColor='FFFFFF'); c.alignment=Alignment(horizontal='center', vertical='center')
    ws.row_dimensions[row].height=32
    row+=2

def add_section(title, analysis):
    global row
    ws.merge_cells(start_row=row,start_column=1,end_row=row,end_column=max_col)
    c=ws.cell(row,1,title)
    c.fill=section_fill; c.font=section_font; c.alignment=Alignment(vertical='center')
    set_range_style(row,row,fill=section_fill,font=section_font)
    ws.row_dimensions[row].height=24
    row+=1
    ws.merge_cells(start_row=row,start_column=1,end_row=row+2,end_column=max_col)
    c=ws.cell(row,1,analysis)
    c.fill=analysis_fill; c.font=analysis_font; c.alignment=Alignment(wrap_text=True, vertical='top')
    set_range_style(row,row+2,fill=analysis_fill,font=analysis_font,align=Alignment(wrap_text=True,vertical='top'))
    ws.row_dimensions[row].height=48
    ws.row_dimensions[row+1].height=18
    ws.row_dimensions[row+2].height=18
    row+=3

def add_subtitle(text):
    global row
    ws.merge_cells(start_row=row,start_column=1,end_row=row,end_column=max_col)
    c=ws.cell(row,1,text)
    c.fill=sub_fill; c.font=Font(bold=True,color='17365D'); c.alignment=Alignment(vertical='center')
    set_range_style(row,row,fill=sub_fill,font=Font(bold=True,color='17365D'))
    row+=1

def add_table(headers, rows, formats=None):
    global row
    for i,h in enumerate(headers,1):
        c=ws.cell(row,i,h); c.fill=header_fill; c.font=white; c.alignment=Alignment(horizontal='center',vertical='center',wrap_text=True); c.border=border
    row+=1
    for r in rows:
        for i,h in enumerate(headers,1):
            v=r.get(h,'') if isinstance(r,dict) else r[i-1]
            c=ws.cell(row,i,v); c.border=border; c.alignment=Alignment(vertical='center',wrap_text=True)
            if formats and h in formats and isinstance(v,(int,float)):
                c.number_format=formats[h]
        row+=1
    row+=1

def ppf(v): return None if v is None else v

add_title('良渚文化｜2026年5月月度运营报告（单页阅读版）')

def change_phrase(v):
    if v is None:
        return '暂无可比数据'
    return ('增长' if v>=0 else '下降') + f'{abs(v)*100:.1f}%'

def channel_row(p):
    cur=by[(p,'2026-05')]; prev=by.get((p,'2026-04')); yr=by.get((p,'2025-05'))
    return {
        '渠道':p,
        '2026年5月实际成交金额':cur.get('实际成交金额'),
        '2026年4月实际成交金额':prev.get('实际成交金额') if prev else None,
        '实际成交环比':rate(cur.get('实际成交金额'), prev.get('实际成交金额') if prev else None),
        '2025年5月实际成交金额':yr.get('实际成交金额') if yr else None,
        '实际成交同比':rate(cur.get('实际成交金额'), yr.get('实际成交金额') if yr else None),
        '支付/成交金额':cur.get('支付金额'),
        '退款金额':cur.get('退款金额'),
        '退款率':cur.get('退款金额')/cur.get('支付金额') if cur.get('退款金额') is not None and cur.get('支付金额') else None,
        '访客数':cur.get('访客数'),
        '支付/成交买家数':cur.get('支付买家数'),
        '支付/成交件数':cur.get('支付件数'),
        '转化率':cur.get('转化率'),
        '客单价':cur.get('客单价'),
        '支付/成交金额同比':rate(cur.get('支付金额'), yr.get('支付金额') if yr else None),
        '访客数同比':rate(cur.get('访客数'), yr.get('访客数') if yr else None),
        '转化率同比':rate(cur.get('转化率'), yr.get('转化率') if yr else None),
        '备注':'京东2025年5月店铺月报无退款字段，去年实际成交暂按成交金额展示' if p=='京东' else ''
    }

summary_yoy=rate(may_comp.get('实际成交金额'),yoy_comp.get('实际成交金额'))
jd_cur=by.get(('京东','2026-05')); jd_yr=by.get(('京东','2025-05'))
if jd_yr:
    yoy_sentence=(f"本次已补充京东2025年5月同口径店铺月报，因此天猫、淘宝、京东三渠道均可计算店铺整体同比：5月三渠道实际成交较去年同期{change_phrase(summary_yoy)}。"
                  f"京东5月成交金额 {money(jd_cur.get('支付金额'))}，较2025年5月 {money(jd_yr.get('支付金额'))} 同比{change_phrase(rate(jd_cur.get('支付金额'),jd_yr.get('支付金额')))}；访客数同比{change_phrase(rate(jd_cur.get('访客数'),jd_yr.get('访客数')))}。")
else:
    yoy_sentence=(f"去年同期同比先按有同口径去年数据的可比渠道（{'+'.join(comp_plats)}）计算：5月可比渠道实际成交较去年同期{change_phrase(summary_yoy)}。"
                  "京东本轮已补齐2026年5月退款/售后口径，但当前源表缺少2025年5月京东店铺同口径数据，因此暂不做京东同比。")
analysis1=(f"5月三渠道合计支付/成交金额 {money(may_total['支付金额'])}，扣除退款后的实际成交金额 {money(may_total['实际成交金额'])}，环比4月{change_phrase(rate(may_total['实际成交金额'],apr_total['实际成交金额']))}；合计访客数 {num(may_total['访客数'])}，环比{change_phrase(rate(may_total['访客数'],apr_total['访客数']))}。"
           + yoy_sentence)
add_section('第一部分｜月度经营情况总览：环比 + 去年同期同比', analysis1)
formats={'实际成交金额':'¥#,##0.00','2026年5月实际成交金额':'¥#,##0.00','2026年4月实际成交金额':'¥#,##0.00','2025年5月实际成交金额':'¥#,##0.00','支付/成交金额':'¥#,##0.00','退款金额':'¥#,##0.00','退款率':'0.00%','访客数':'#,##0','支付/成交买家数':'#,##0','支付/成交件数':'#,##0','转化率':'0.00%','客单价':'¥#,##0.00','实际成交环比':'0.00%','实际成交同比':'0.00%','支付/成交金额同比':'0.00%','访客数同比':'0.00%','转化率同比':'0.00%'}
summary_rows=[
    {'汇总口径':'三渠道合计（2026年5月）',
     '2026年5月实际成交金额':may_total.get('实际成交金额'),
     '2026年4月实际成交金额':apr_total.get('实际成交金额'),
     '实际成交环比':rate(may_total.get('实际成交金额'),apr_total.get('实际成交金额')),
     '2025年5月实际成交金额':yoy_comp.get('实际成交金额'),
     '实际成交同比':rate(may_comp.get('实际成交金额'),yoy_comp.get('实际成交金额')),
     '支付/成交金额':may_total.get('支付金额'),'退款金额':may_total.get('退款金额'),
     '退款率':may_total.get('退款金额')/may_total.get('支付金额'),
     '访客数':may_total.get('访客数'),'支付/成交买家数':may_total.get('支付买家数'),
     '支付/成交件数':may_total.get('支付件数'),'转化率':may_total.get('转化率'),
     '客单价':may_total.get('客单价'),
     '备注':'2025年5月同比基准仅包含有同口径去年数据的可比渠道；京东本轮补齐2026年5月退款/售后，但缺2025年5月京东店铺同口径数据'},
    {'汇总口径':'环比基准（三渠道2026年4月）',
     '2026年5月实际成交金额':None,
     '2026年4月实际成交金额':apr_total.get('实际成交金额'),
     '实际成交环比':None,
     '2025年5月实际成交金额':None,
     '实际成交同比':None,
     '支付/成交金额':apr_total.get('支付金额'),'退款金额':apr_total.get('退款金额'),
     '退款率':apr_total.get('退款金额')/apr_total.get('支付金额') if apr_total.get('退款金额') is not None and apr_total.get('支付金额') else None,
     '访客数':apr_total.get('访客数'),'支付/成交买家数':apr_total.get('支付买家数'),
     '支付/成交件数':apr_total.get('支付件数'),'转化率':apr_total.get('转化率'),
     '客单价':apr_total.get('客单价'),
     '备注':'用于计算环比'},
    {'汇总口径':'同比基准（三渠道2025年5月）',
     '2026年5月实际成交金额':None,
     '2026年4月实际成交金额':None,
     '实际成交环比':None,
     '2025年5月实际成交金额':yoy_comp.get('实际成交金额'),
     '实际成交同比':None,
     '支付/成交金额':yoy_comp.get('支付金额'),'退款金额':yoy_comp.get('退款金额'),
     '退款率':yoy_comp.get('退款金额')/yoy_comp.get('支付金额') if yoy_comp.get('退款金额') is not None and yoy_comp.get('支付金额') else None,
     '访客数':yoy_comp.get('访客数'),'支付/成交买家数':yoy_comp.get('支付买家数'),
     '支付/成交件数':yoy_comp.get('支付件数'),'转化率':yoy_comp.get('转化率'),
     '客单价':yoy_comp.get('客单价'),
     '备注':'用于计算同比；当前为可比渠道同比基准，不含缺少去年同口径的京东'},
]
add_subtitle('1.1 三渠道合计与环比/同比源数据')
summary_headers=['汇总口径','2026年5月实际成交金额','2026年4月实际成交金额','实际成交环比','2025年5月实际成交金额','实际成交同比','支付/成交金额','退款金额','退款率','访客数','支付/成交买家数','支付/成交件数','转化率','客单价','备注']
add_table(summary_headers, summary_rows, formats)
for idx,p in enumerate(['天猫','淘宝','京东'], start=2):
    add_subtitle(f'1.{idx} {p}店铺月度表现（单渠道单独表）')
    add_table(['渠道','2026年5月实际成交金额','2026年4月实际成交金额','实际成交环比','2025年5月实际成交金额','实际成交同比','支付/成交金额','退款金额','退款率','访客数','支付/成交买家数','支付/成交件数','转化率','客单价','支付/成交金额同比','访客数同比','转化率同比','备注'], [channel_row(p)], formats)

# section 2
best_new=max(new_summary, key=lambda x:x['实际成交金额']) if new_summary else None
analysis2=("本月新品单独看玉鸟咕咕毛绒挂件、礼阅粽子、黑陶茶具三类，避免与主力商品TOP混在一起。"
           + (f"其中新品成交最好的是{best_new['平台']}的{best_new['新品类别']}，实际成交 {money(best_new['实际成交金额'])}，支付件数 {num(best_new['支付件数'])}。" if best_new else '')
           + "2.2开始的商品TOP20按渠道分别列示，天猫、京东、淘宝各自带表头，便于直接查看每个渠道的主力成交商品。")
add_section('第二部分｜新品表现 + 商品TOP20：先看新品，再看主力商品', analysis2)
add_subtitle('2.1 本月重点新品表现（新品部分单独呈现）')
for r in new_summary:
    total_actual=by.get((r['平台'],'2026-05'),{}).get('实际成交金额')
    r['平台实际成交占比']=r['实际成交金额']/total_actual if total_actual else None
add_table(['新品类别','平台','商品数','访客数','支付件数','支付金额','退款金额','实际成交金额','平台实际成交占比'], sorted(new_summary, key=lambda x: (x['新品类别'], x['平台'])), {'访客数':'#,##0','支付件数':'#,##0','支付金额':'¥#,##0.00','退款金额':'¥#,##0.00','实际成交金额':'¥#,##0.00','平台实际成交占比':'0.00%'})
add_subtitle('2.2 各渠道商品TOP20（分渠道单独表，按实际成交金额排序）')
top_headers=['排名','商品名称','访客数','支付/成交件数','支付/成交金额','退款金额','实际成交金额','转化率','渠道销售占比']
for subidx,p in enumerate(['天猫','京东','淘宝'], start=1):
    total_actual=by[(p,'2026-05')]['实际成交金额']
    rows=[]
    for i,r in enumerate(top_products(p,20),1):
        rows.append({'排名':i,'商品名称':r['商品名称'],'访客数':r.get('访客数'),'支付/成交件数':r.get('支付件数'),'支付/成交金额':r.get('支付金额'),'退款金额':r.get('退款金额'),'实际成交金额':r.get('实际成交金额'),'转化率':r.get('转化率'),'渠道销售占比':(r.get('实际成交金额') or 0)/total_actual if total_actual else None})
    add_subtitle(f'2.2.{subidx} {p}商品TOP20')
    add_table(top_headers, rows, {'访客数':'#,##0','支付/成交件数':'#,##0','支付/成交金额':'¥#,##0.00','退款金额':'¥#,##0.00','实际成交金额':'¥#,##0.00','转化率':'0.00%','渠道销售占比':'0.00%'})

# section 3
analysis3=("推广总花费以计划报表为准，商品报表、货品全站、营销场景报表只作为维度参考，不重复累加。推广总计可以放在同一张表里看总体效率；流量来源结构则按天猫、淘宝分别列示，避免不同平台来源项混在一起。"
           "下月重点建议围绕主力成交商品稳盘、新品内容化测试、京东转化提升和推广预算分层执行。")
add_section('第三部分｜推广投放、流量来源与下月计划：简要分析后看数据', analysis3)
add_subtitle('3.1 推广投放总计（天猫/淘宝合计表，计划报表口径）')
promo_total_rows=[]
for r in sorted(promo_may_total,key=lambda x:x['平台']):
    st=by[(r['平台'],'2026-05')]
    promo_total_rows.append({'平台':r['平台'],'月份':r['月份'],'花费':r['花费'],'成交金额':r['总成交金额'],'ROI':r['ROI'],'成交笔数':r['成交笔数'],'点击量':r['点击量'],'展现量':r['展现量'],'点击率':r['点击率'],'CPC':r['CPC'],'花费占店铺支付金额':r['花费']/st['支付金额'] if st.get('支付金额') else None,'推广成交占店铺支付金额':r['总成交金额']/st['支付金额'] if st.get('支付金额') else None})
add_table(['平台','月份','花费','成交金额','ROI','成交笔数','点击量','展现量','点击率','CPC','花费占店铺支付金额','推广成交占店铺支付金额'], promo_total_rows, {'花费':'¥#,##0.00','成交金额':'¥#,##0.00','ROI':'0.00','成交笔数':'#,##0','点击量':'#,##0','展现量':'#,##0','点击率':'0.00%','CPC':'¥#,##0.00','花费占店铺支付金额':'0.00%','推广成交占店铺支付金额':'0.00%'})
add_subtitle('3.1.1 推广分场景明细（合并表）')
promo_rows=[]
for r in sorted(promo_may_scene,key=lambda x:(x['平台'],x['场景名字'])):
    st=by[(r['平台'],'2026-05')]
    promo_rows.append({'平台':r['平台'],'推广场景':r['场景名字'],'花费':r['花费'],'成交金额':r['总成交金额'],'ROI':r['ROI'],'成交笔数':r['成交笔数'],'点击量':r['点击量'],'展现量':r['展现量'],'点击率':r['点击率'],'CPC':r['CPC'],'花费占店铺支付金额':r['花费']/st['支付金额'] if st.get('支付金额') else None,'推广成交占店铺支付金额':r['总成交金额']/st['支付金额'] if st.get('支付金额') else None})
add_table(['平台','推广场景','花费','成交金额','ROI','成交笔数','点击量','展现量','点击率','CPC','花费占店铺支付金额','推广成交占店铺支付金额'], promo_rows, {'花费':'¥#,##0.00','成交金额':'¥#,##0.00','ROI':'0.00','成交笔数':'#,##0','点击量':'#,##0','展现量':'#,##0','点击率':'0.00%','CPC':'¥#,##0.00','花费占店铺支付金额':'0.00%','推广成交占店铺支付金额':'0.00%'})
add_subtitle('3.2 流量来源结构（分平台单独表）')
flow_headers=['月份','一级来源','访客数','访客占比','支付金额','支付占比','支付转化率','UV价值']
for subidx,platform in enumerate(['天猫','淘宝'], start=1):
    add_subtitle(f'3.2.{subidx} {platform}流量来源结构')
    flow_table=[]
    for month in ['2026-04','2026-05']:
        rows=[r for r in flow_rows if r['平台']==platform and r['月份']==month]
        total_uv=sum((r.get('访客数') or 0) for r in rows)
        total_pay=sum((r.get('支付金额') or 0) for r in rows)
        for r in rows:
            flow_table.append({'月份':month,'一级来源':r['一级来源'],'访客数':r.get('访客数'),'访客占比':(r.get('访客数') or 0)/total_uv if total_uv else None,'支付金额':r.get('支付金额'),'支付占比':(r.get('支付金额') or 0)/total_pay if total_pay else None,'支付转化率':r.get('支付转化率'),'UV价值':r.get('UV价值')})
    add_table(flow_headers, flow_table, {'访客数':'#,##0','访客占比':'0.00%','支付金额':'¥#,##0.00','支付占比':'0.00%','支付转化率':'0.00%','UV价值':'¥#,##0.00'})
add_subtitle('3.3 下月执行重点')
plan_rows=[
    {'方向':'主力商品稳盘','执行重点':'天猫继续稳住玉琮/摆件、晴雨伞、茶具等成交品；高退款商品重点检查规格说明、材质说明、详情页预期和售后原因。','关注指标':'实际成交金额、退款率、转化率'},
    {'方向':'新品测试','执行重点':'玉鸟咕咕毛绒挂件加强内容曝光和礼品场景承接；礼阅粽子复盘节令礼盒流量与转化；黑陶茶具继续作为高客单礼赠型商品优化主图、详情页和投放承接。','关注指标':'新品访客、支付件数、实际成交、加购收藏'},
    {'方向':'京东转化提升','执行重点':'京东5月流量同比放大但成交金额同比下滑，需围绕TOP10商品做标题关键词、详情页卖点、评价问答、价格权益优化。','关注指标':'京东访客数、成交客户数、转化率、客单价'},
    {'方向':'推广预算分层','执行重点':'保留ROI更好的货品全站推广；天猫关键词推广控制预算，优化词包和承接商品，减少低效消耗。','关注指标':'花费、成交金额、ROI、点击率、CPC'},
    {'方向':'数据机制','执行重点':'下月固定提供三渠道分天店铺整体数据、商品月度明细、推广计划报表、流量来源报表，形成稳定月报趋势。','关注指标':'数据完整性、同口径环比/同比'}
]
add_table(['方向','执行重点','关注指标'], plan_rows, {})

# final footnote
add_subtitle('数据口径说明')
notes=[
    {'项目':'实际成交金额','说明':'天猫/淘宝=支付金额-成功退款金额；京东2026年5月=成交金额-退款金额，退款/售后汇总优先来自交易概况导出（6.har），商品明细用于TOP和件数校验。'},
    {'项目':'环比/同比源数据','说明':'第一部分已把本月实际成交、2026年4月实际成交金额、2025年5月实际成交金额放在同一表中，再计算环比和同比，便于核对计算来源。'},
    {'项目':'同比口径','说明':'店铺整体同比先按有2025年5月同口径数据的可比渠道计算；京东本轮补齐2026年5月退款/售后，但缺少2025年5月京东店铺同口径数据，暂不纳入同比。'},
    {'项目':'商品TOP20','说明':'2.2开始按渠道拆表，天猫、京东、淘宝分别带表头；排序按实际成交金额，若平台无退款字段则按成交金额近似。'},
    {'项目':'新品命名','说明':'“礼阅粽子”的“阅”为阅读的阅，已按该名称呈现。'},
    {'项目':'推广口径','说明':'推广总花费与ROI以计划报表为准，商品报表/货品全站/营销场景报表不重复累加。'},
    {'项目':'每日趋势','说明':'本批次未提供三渠道店铺分天整体数据，因此单页版暂不展开每日趋势；后续补充后可接在同一sheet后段。'}
]
add_table(['项目','说明'], notes, {})

# column widths and freeze
widths={1:16,2:16,3:16,4:14,5:16,6:14,7:14,8:14,9:12,10:12,11:14,12:14,13:12,14:14,15:16,16:16,17:16,18:28}
for col,w in widths.items():
    ws.column_dimensions[get_column_letter(col)].width=w
for r in range(1,row+1):
    ws.row_dimensions[r].height=max(ws.row_dimensions[r].height or 15, 20)
ws.freeze_panes='A4'
# add auto filter not meaningful over multiple tables; skip
out=D/'良渚文化_2026年5月月报_单页版.xlsx'
wb.save(out)
print(out)
print('rows', row, 'sheets', wb.sheetnames)