from pathlib import Path
import json, math, re
import xlrd, openpyxl
from openpyxl import Workbook, load_workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter
BASE=Path('/Users/bot1/Volumes/root_for_ai/AI工作区/良渚文化_月报_2026年5月_20260601_1123')
SRC=BASE/'source'; WORK=BASE/'work'; DEL=BASE/'deliverables'

def loadj(name): return json.loads((WORK/name).read_text(encoding='utf-8'))
store=loadj('store_monthly.json'); products=loadj('product_monthly.json'); promos=loadj('promo_plan_rows.json')

def f(v, nd=2):
    if v is None: return ''
    return round(float(v), nd)
def pct(v, nd=1):
    if v is None: return ''
    return f"{float(v)*100:.{nd}f}%"
def money(v):
    if v is None: return ''
    return f"¥{float(v):,.2f}"
def num(v):
    if v is None: return ''
    return f"{float(v):,.0f}"
def mom(cur, prev):
    if cur is None or prev in [None,0]: return None
    return (cur-prev)/prev

def key_rows(platform=None, month='2026-05'):
    return [r for r in store if r['月份']==month and (platform is None or r['平台']==platform)]
# Supplement JD store units/refunds from product-detail totals only when a store-level trade summary is absent.
# For 2026-05, prefer the `6.har` tradeSummary export parsed in build_analysis.py because it is the store-level refund/after-sales口径.
for r in store:
    if r['平台']=='京东' and r['月份'] in ['2026-04','2026-05']:
        rows=[x for x in products if x['平台']=='京东' and x['月份']==r['月份']]
        if rows:
            if r.get('支付件数') is None:
                r['支付件数']=sum((x.get('支付件数') or 0) for x in rows)
            if r.get('退款金额') is None:
                refund=sum((x.get('退款金额') or 0) for x in rows)
                r['退款金额']=refund
                if r.get('支付金额') is not None: r['实际成交金额']=r['支付金额']-refund
            if r.get('支付金额') and r.get('支付件数'): r['件单价']=r['支付金额']/r['支付件数']
# totals for May/Apr
platforms=['天猫','淘宝','京东']
store_by={(r['平台'],r['月份']):r for r in store}
may=[store_by[(p,'2026-05')] for p in platforms if (p,'2026-05') in store_by]
apr=[store_by[(p,'2026-04')] for p in platforms if (p,'2026-04') in store_by]

def total_store(rows, month):
    s={'平台':'三渠道合计','月份':month}
    for col in ['访客数','浏览量','支付金额','退款金额','实际成交金额','支付买家数','支付件数','加购人数','收藏次数']:
        vals=[r.get(col) for r in rows if r.get(col) is not None]
        s[col]=sum(vals) if vals else None
    s['转化率']=s['支付买家数']/s['访客数'] if s.get('支付买家数') is not None and s.get('访客数') else None
    s['客单价']=s['支付金额']/s['支付买家数'] if s.get('支付金额') is not None and s.get('支付买家数') else None
    s['件单价']=s['支付金额']/s['支付件数'] if s.get('支付金额') is not None and s.get('支付件数') else None
    return s
may_total=total_store(may,'2026-05'); apr_total=total_store(apr,'2026-04')

# aggregate promos by platform/month/scenario and platform/month total
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_apr_scene=agg_promo([r for r in promos if r['月份']=='2026-04'], ['平台','月份','场景名字'])
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'], ['平台','月份'])

# product top and new products
prod_may=[r for r in products if r['月份']=='2026-05']
prod_apr=[r for r in products if r['月份']=='2026-04']
def top_products(platform, n=20, month='2026-05'):
    rows=[r for r in products if r['平台']==platform and r['月份']==month]
    return sorted(rows, key=lambda r:r.get('实际成交金额') or 0, reverse=True)[:n]
# by product ID April lookup for JD环比
prod_lookup={(r['平台'],r['商品ID'],r['月份']):r for r in products}
# cross channel totals and top contribution
top_by_platform={p: top_products(p,20) for p in platforms}
# new products May only
new_rows=[r for r in prod_may if r.get('新品类别')]
new_summary={}
for r in new_rows:
    for cat in r['新品类别'].split('、'):
        if not cat: continue
        new_summary.setdefault((cat,r['平台']), {'新品类别':cat,'平台':r['平台'],'商品数':0,'支付金额':0.0,'实际成交金额':0.0,'支付件数':0.0,'访客数':0.0,'退款金额':0.0})
        d=new_summary[(cat,r['平台'])]
        d['商品数']+=1; d['支付金额']+=r.get('支付金额') or 0; d['实际成交金额']+=r.get('实际成交金额') or 0; d['支付件数']+=r.get('支付件数') or 0; d['访客数']+=r.get('访客数') or 0; d['退款金额']+=r.get('退款金额') or 0
new_summary=list(new_summary.values())

# Flow source parse
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
    pctv=s.endswith('%')
    if pctv: s=s[:-1]
    try:
        x=float(s); return x/100 if pctv else x
    except: 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_file(path):
    rows=read_xls(path)
    headers=[str(x).strip() for x in rows[5]]
    data=[]
    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]}
        data.append(d)
    return data
flow_rows=[]
for p in SRC.glob('*无线店铺流量来源-2026-0*.xls'):
    platform='天猫' if '天猫' in p.name else '淘宝'
    month=re.search(r'(2026-\d{2})-', p.name).group(1)
    data=parse_flow_file(p)
    for d in data:
        # May has 流量载体 column, April tmall maybe not
        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()
        uv=to_float(d.get('访客数'))
        pay=to_float(d.get('支付金额'))
        buyers=to_float(d.get('支付买家数'))
        # summary rows
        if second=='汇总' and third=='汇总' and fourth=='汇总':
            flow_rows.append({'平台':platform,'月份':month,'一级来源':first,'二级来源':second,'访客数':uv,'支付金额':pay,'支付买家数':buyers,'支付转化率':to_float(d.get('支付转化率')),'UV价值':to_float(d.get('UV价值'))})
        # keep level-2 non-summary rows too
        elif second and second!='汇总' and (third==second or third=='汇总') and (fourth==second or fourth=='汇总'):
            flow_rows.append({'平台':platform,'月份':month,'一级来源':first,'二级来源':second,'访客数':uv,'支付金额':pay,'支付买家数':buyers,'支付转化率':to_float(d.get('支付转化率')),'UV价值':to_float(d.get('UV价值'))})
(WORK/'flow_source_summary.json').write_text(json.dumps(flow_rows,ensure_ascii=False,indent=2),encoding='utf-8')

# Create workbook
wb=Workbook()
ws=wb.active; ws.title='01_月度总览'
header_fill=PatternFill('solid', fgColor='1F4E78'); white=Font(color='FFFFFF', bold=True)
light=PatternFill('solid', fgColor='D9EAF7')
thin=Side(style='thin', color='CCCCCC')
border=Border(left=thin,right=thin,top=thin,bottom=thin)
def style_sheet(ws):
    for row in ws.iter_rows():
        for c in row:
            c.border=border; c.alignment=Alignment(vertical='center', wrap_text=True)
    for c in ws[1]:
        c.fill=header_fill; c.font=white; c.alignment=Alignment(horizontal='center', vertical='center')
    for col in ws.columns:
        max_len=0; letter=get_column_letter(col[0].column)
        for c in col:
            val=str(c.value) if c.value is not None else ''
            max_len=max(max_len, min(len(val),60))
        ws.column_dimensions[letter].width=max(12,min(max_len+2,40))

def append_table(ws, headers, rows):
    ws.append(headers)
    for r in rows:
        ws.append([r.get(h,'') for h in headers])

# Overview rows with MoM and contribution
ov=[]
for p in platforms:
    cur=store_by.get((p,'2026-05')); prev=store_by.get((p,'2026-04'))
    if not cur: continue
    row={'渠道':p,'实际成交金额':cur.get('实际成交金额'),'支付/成交金额':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('客单价'),'实际成交占比':cur.get('实际成交金额')/may_total['实际成交金额'] if may_total.get('实际成交金额') else None,'实际成交环比':mom(cur.get('实际成交金额'), prev.get('实际成交金额') if prev else None),'访客数环比':mom(cur.get('访客数'), prev.get('访客数') if prev else None),'转化率环比':mom(cur.get('转化率'), prev.get('转化率') if prev else None),'备注':'京东退款/件数由商品明细汇总补充' if p=='京东' else ''}
    ov.append(row)
# total row
ov.append({'渠道':'三渠道合计','实际成交金额':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('转化率'),'客单价':may_total.get('客单价'),'实际成交占比':1,'实际成交环比':mom(may_total.get('实际成交金额'),apr_total.get('实际成交金额')),'访客数环比':mom(may_total.get('访客数'),apr_total.get('访客数')),'转化率环比':mom(may_total.get('转化率'),apr_total.get('转化率')),'备注':'京东退款来自商品明细；天猫/淘宝来自店铺整体表'})
append_table(ws, list(ov[0].keys()), ov); style_sheet(ws)
for row in ws.iter_rows(min_row=2):
    for i,c in enumerate(row, start=1):
        h=ws.cell(1,i).value
        if h in ['退款率','转化率','实际成交占比','实际成交环比','访客数环比','转化率环比'] and isinstance(c.value,(int,float)): c.number_format='0.00%'
        elif h in ['实际成交金额','支付/成交金额','退款金额','客单价'] and isinstance(c.value,(int,float)): c.number_format='¥#,##0.00'
        elif h in ['访客数','支付/成交买家数','支付/成交件数'] and isinstance(c.value,(int,float)): c.number_format='#,##0'

ws=wb.create_sheet('02_新品表现')
headers=['新品类别','平台','商品数','支付金额','实际成交金额','支付件数','访客数','退款金额','销售占比_按平台实际成交']
rows=[]
for r in new_summary:
    plat_total=store_by.get((r['平台'],'2026-05'),{}).get('实际成交金额')
    rr=dict(r); rr['销售占比_按平台实际成交']=r['实际成交金额']/plat_total if plat_total else None
    rows.append(rr)
append_table(ws,headers, sorted(rows,key=lambda x:(x['新品类别'],x['平台']))); style_sheet(ws)
for row in ws.iter_rows(min_row=2):
    for i,c in enumerate(row, start=1):
        h=ws.cell(1,i).value
        if h in ['支付金额','实际成交金额','退款金额'] and isinstance(c.value,(int,float)): c.number_format='¥#,##0.00'
        if h=='销售占比_按平台实际成交' and isinstance(c.value,(int,float)): c.number_format='0.00%'
# detail rows for new products
ws=wb.create_sheet('02b_新品明细')
headers=['新品类别','平台','商品ID','商品名称','访客数','支付件数','支付金额','退款金额','实际成交金额','转化率']
append_table(ws,headers, sorted(new_rows,key=lambda x:((x.get('新品类别') or ''),x['平台'],-(x.get('实际成交金额') or 0)))); style_sheet(ws)

ws=wb.create_sheet('03_TOP20商品')
headers=['平台','排名','商品ID','商品名称','访客数','支付件数','支付金额','退款金额','实际成交金额','转化率','平台销售占比','支付件数占比']
rows=[]
for p in platforms:
    total_amount=store_by.get((p,'2026-05'),{}).get('实际成交金额')
    total_units=store_by.get((p,'2026-05'),{}).get('支付件数')
    for i,r in enumerate(top_by_platform[p],1):
        rows.append({'平台':p,'排名':i,'商品ID':r['商品ID'],'商品名称':r['商品名称'],'访客数':r.get('访客数'),'支付件数':r.get('支付件数'),'支付金额':r.get('支付金额'),'退款金额':r.get('退款金额'),'实际成交金额':r.get('实际成交金额'),'转化率':r.get('转化率'),'平台销售占比':(r.get('实际成交金额') or 0)/total_amount if total_amount else None,'支付件数占比':(r.get('支付件数') or 0)/total_units if total_units else None})
append_table(ws,headers,rows); style_sheet(ws)
for row in ws.iter_rows(min_row=2):
    for i,c in enumerate(row, start=1):
        h=ws.cell(1,i).value
        if h in ['支付金额','退款金额','实际成交金额'] and isinstance(c.value,(int,float)): c.number_format='¥#,##0.00'
        if h in ['转化率','平台销售占比','支付件数占比'] and isinstance(c.value,(int,float)): c.number_format='0.00%'

ws=wb.create_sheet('04_京东TOP10环比')
headers=['排名','商品ID','商品名称','5月成交金额','4月成交金额','成交金额环比','5月成交件数','4月成交件数','成交件数环比','5月销售占比','备注']
rows=[]; jd_total=store_by[('京东','2026-05')]['实际成交金额']
for i,r in enumerate(top_products('京东',10,'2026-05'),1):
    prev=prod_lookup.get(('京东',r['商品ID'],'2026-04'))
    rows.append({'排名':i,'商品ID':r['商品ID'],'商品名称':r['商品名称'],'5月成交金额':r.get('实际成交金额'),'4月成交金额':prev.get('实际成交金额') if prev else 0,'成交金额环比':mom(r.get('实际成交金额'), prev.get('实际成交金额') if prev else 0),'5月成交件数':r.get('支付件数'),'4月成交件数':prev.get('支付件数') if prev else 0,'成交件数环比':mom(r.get('支付件数'), prev.get('支付件数') if prev else 0),'5月销售占比':(r.get('实际成交金额') or 0)/jd_total if jd_total else None,'备注':'4月无同SPU成交' if not prev or not prev.get('实际成交金额') else ''})
append_table(ws,headers,rows); style_sheet(ws)

ws=wb.create_sheet('05_推广表现')
headers=['平台','月份','场景名字','花费','总成交金额','ROI','成交笔数','点击量','展现量','点击率','CPC','花费占店铺支付金额','推广成交占店铺支付金额']
rows=[]
for r in sorted(promo_may_scene,key=lambda x:(x['平台'],x['场景名字'])):
    st=store_by.get((r['平台'],'2026-05'),{})
    rr=dict(r); rr['花费占店铺支付金额']=r['花费']/st.get('支付金额') if st.get('支付金额') else None; rr['推广成交占店铺支付金额']=r['总成交金额']/st.get('支付金额') if st.get('支付金额') else None
    rows.append(rr)
append_table(ws,headers,rows); style_sheet(ws)

ws=wb.create_sheet('06_流量来源')
headers=['平台','月份','一级来源','二级来源','访客数','支付金额','支付买家数','支付转化率','UV价值','访客占比_本平台来源汇总','支付占比_本平台来源汇总']
rows=[]
for platform in ['天猫','淘宝']:
    for month in ['2026-04','2026-05']:
        first_summaries=[r for r in flow_rows if r['平台']==platform and r['月份']==month and r['二级来源']=='汇总']
        total_uv=sum((r.get('访客数') or 0) for r in first_summaries)
        total_pay=sum((r.get('支付金额') or 0) for r in first_summaries)
        for r in first_summaries:
            rr=dict(r); rr['访客占比_本平台来源汇总']=(r.get('访客数') or 0)/total_uv if total_uv else None; rr['支付占比_本平台来源汇总']=(r.get('支付金额') or 0)/total_pay if total_pay else None
            rows.append(rr)
append_table(ws,headers,rows); style_sheet(ws)

ws=wb.create_sheet('07_月度趋势')
headers=['平台','月份','访客数','支付/成交金额','退款金额','实际成交金额','支付/成交买家数','支付/成交件数','转化率','客单价']
rows=[]
for r in sorted(store,key=lambda x:(x['平台'],x['月份'])):
    rows.append({'平台':r['平台'],'月份':r['月份'],'访客数':r.get('访客数'),'支付/成交金额':r.get('支付金额'),'退款金额':r.get('退款金额'),'实际成交金额':r.get('实际成交金额'),'支付/成交买家数':r.get('支付买家数'),'支付/成交件数':r.get('支付件数'),'转化率':r.get('转化率'),'客单价':r.get('客单价')})
append_table(ws,headers,rows); style_sheet(ws)

ws=wb.create_sheet('08_数据口径')
rows=[
['项目','说明'],
['报告月份','2026年5月，环比主要对比2026年4月。'],
['实际成交金额','天猫/淘宝=支付金额-成功退款金额；京东2026年5月=成交金额-退款金额，退款/售后汇总优先来自交易概况导出（6.har），商品明细用于TOP和件数校验。'],
['推广口径','推广总花费与ROI以“计划报表”为准；商品报表、货品全站推广报表、营销场景报表为同一投放数据的不同维度，未重复累加。'],
['京东商品口径','京东商品明细含月汇总和分天数据，本报告TOP10/TOP20只取月汇总行，避免重复计算。'],
['每日经营趋势','本批次未提供三渠道店铺分天经营总表，因此以分月趋势和推广/流量月度数据为主；后续如补充分天店铺数据，可追加每日经营趋势页。'],
['流量来源','目前仅有天猫/淘宝2026年4月、5月无线店铺流量来源，用于4-5月环比和来源结构分析；京东未提供同口径来源数据。']]
for r in rows: ws.append(r)
style_sheet(ws)
out_xlsx=DEL/'良渚文化_2026年5月月报数据分析附录.xlsx'
wb.save(out_xlsx)

# Narrative markdown
# key numbers
lines=[]
lines.append('# 良渚文化 2026年5月月度运营分析报告\n')
lines.append('## 1. 月度经营情况总览\n')
lines.append(f"2026年5月，良渚文化天猫、淘宝、京东三渠道合计支付/成交金额 {money(may_total['支付金额'])}，扣除退款后的实际成交金额 {money(may_total['实际成交金额'])}，环比4月实际成交增长 {pct(mom(may_total['实际成交金额'],apr_total['实际成交金额']))}；合计访客数 {num(may_total['访客数'])}，环比增长 {pct(mom(may_total['访客数'],apr_total['访客数']))}。\n")
for p in platforms:
    cur=store_by[(p,'2026-05')]; prev=store_by.get((p,'2026-04'))
    lines.append(f"- **{p}**：实际成交 {money(cur['实际成交金额'])}，占三渠道 {pct(cur['实际成交金额']/may_total['实际成交金额'])}；支付/成交金额 {money(cur['支付金额'])}，退款 {money(cur.get('退款金额')) if cur.get('退款金额') is not None else '未提供'}，访客 {num(cur['访客数'])}，转化率 {pct(cur['转化率'],2)}，实际成交环比 {pct(mom(cur['实际成交金额'], prev['实际成交金额'] if prev else None))}。")
lines.append('\n## 2. 各店铺/渠道经营表现\n')
# observations
lines.append('- **天猫**仍是主力成交渠道，5月实际成交占三渠道约 {:.1f}%，访客和成交均较4月提升；但退款金额较高，需要继续关注高退款商品的详情页预期管理和售后原因。'.format(store_by[('天猫','2026-05')]['实际成交金额']/may_total['实际成交金额']*100))
lines.append('- **淘宝**5月实际成交较4月明显增长，转化率高于天猫，说明小盘渠道承接效率较好；可继续用高ROI投放和重点商品承接放大成交。')
lines.append('- **京东**5月成交金额相较4月大幅提升，主要由高客单摆件类商品拉动；但转化率仍低，访客放大后需要优化商品承接和搜索/详情页转化。')
lines.append('\n## 3. 新品表现\n')
if new_summary:
    for r in sorted(new_summary,key=lambda x:-(x['实际成交金额'])):
        lines.append(f"- **{r['新品类别']}｜{r['平台']}**：实际成交 {money(r['实际成交金额'])}，支付件数 {num(r['支付件数'])}，访客 {num(r['访客数'])}。")
else:
    lines.append('本批商品明细未识别到约定新品关键词。')
lines.append('新品结论：玉鸟咕咕毛绒挂件已经在京东形成成交，并具备形象化传播价值；黑陶茶具在天猫侧有成交基础，适合继续做高客单礼赠场景；礼乐粽子需结合节令周期和库存/上架情况判断是否继续作为节点型商品延展。')
lines.append('\n## 4. 商品TOP表现\n')
for p in platforms:
    top=top_products(p,5)
    total=store_by[(p,'2026-05')]['实际成交金额']
    lines.append(f"### {p} TOP5")
    for i,r in enumerate(top,1):
        lines.append(f"{i}. {r['商品名称'][:38]}：实际成交 {money(r['实际成交金额'])}，占本渠道 {pct((r['实际成交金额'] or 0)/total)}，销量 {num(r.get('支付件数'))}。")
lines.append('\n京东TOP10已单独做环比和占比：5月第一名“小二节玉琮/大玉琮王”实际成交占京东约 {:.1f}%，是京东5月放大的主要拉动项；玉鸟咕咕毛绒挂件进入京东TOP10，说明新品已具备可继续测试的成交基础。'.format(top_products('京东',10)[0]['实际成交金额']/store_by[('京东','2026-05')]['实际成交金额']*100))
lines.append('\n## 5. 投放推广表现\n')
for r in sorted(promo_may_total,key=lambda x:x['平台']):
    st=store_by[(r['平台'],'2026-05')]
    prev=next((x for x in promo_apr_total if x['平台']==r['平台']), None)
    lines.append(f"- **{r['平台']}**：5月推广花费 {money(r['花费'])}，推广成交 {money(r['总成交金额'])}，ROI {r['ROI']:.2f}；花费占店铺支付金额 {pct(r['花费']/st['支付金额'])}，推广成交占店铺支付金额 {pct(r['总成交金额']/st['支付金额'])}，ROI环比 {pct(mom(r['ROI'], prev['ROI'] if prev else None)) if prev else '无'}。")
for r in sorted(promo_may_scene,key=lambda x:(x['平台'], -x['ROI'] if x['ROI'] else 0)):
    lines.append(f"  - {r['平台']}｜{r['场景名字']}：花费 {money(r['花费'])}，成交 {money(r['总成交金额'])}，ROI {r['ROI']:.2f}。")
lines.append('推广结论：淘宝货品全站推广ROI高于天猫，天猫货品全站推广效率优于关键词推广；下月推广应优先保留高ROI的货品全站承接，同时对天猫关键词推广做词包、商品承接和预算分层优化。')
lines.append('\n## 6. 流量来源与月度趋势补充\n')
# flow summary
for platform in ['天猫','淘宝']:
    mayf=[r for r in flow_rows if r['平台']==platform and r['月份']=='2026-05' and r['二级来源']=='汇总']
    aprf=[r for r in flow_rows if r['平台']==platform and r['月份']=='2026-04' and r['二级来源']=='汇总']
    total_uv=sum((r.get('访客数') or 0) for r in mayf); total_pay=sum((r.get('支付金额') or 0) for r in mayf)
    parts=[]
    for r in sorted(mayf,key=lambda x:-(x.get('访客数') or 0)):
        parts.append(f"{r['一级来源']}访客占比{pct((r.get('访客数') or 0)/total_uv) if total_uv else ''}")
    lines.append(f"- **{platform}流量来源**：" + '，'.join(parts) + '。')
lines.append('说明：本批未提供三渠道店铺分天经营数据，因此每日经营趋势暂不展开；已在附录中保留分月趋势表，后续补充“分天店铺整体数据”后可追加每日趋势。')
lines.append('\n## 7. 下月运营计划重点\n')
lines.append('1. **主力商品稳盘**：天猫继续稳住玉琮/摆件、晴雨伞、茶具等主力成交品，重点关注高退款品的页面说明、规格预期和售后原因。')
lines.append('2. **新品测试**：玉鸟咕咕毛绒挂件进入京东TOP，建议下月在天猫/淘宝同步加强内容曝光和礼品场景承接；黑陶茶具继续作为高客单礼赠型商品优化主图、详情页和投放承接。')
lines.append('3. **京东转化提升**：京东5月流量和成交明显放大，但转化率低，应围绕TOP10商品做标题关键词、详情页卖点、评价/问答、价格权益和搜索承接优化。')
lines.append('4. **推广预算分层**：保留ROI更好的货品全站推广；天猫关键词推广需要控制预算、优化词包和承接商品，避免低效消耗。')
lines.append('5. **补齐数据机制**：下月建议固定提供三渠道分天店铺整体数据、商品月度明细、推广计划报表、流量来源报表，便于形成稳定月报趋势。')
lines.append('\n## 8. 数据口径说明\n')
lines.append('- 推广总花费以计划报表为准，商品报表/货品全站/营销场景报表不重复累加。')
lines.append('- 京东商品明细包含月汇总与分天数据，商品TOP只取月汇总口径。')
lines.append('- 京东2026年5月退款/售后汇总来自交易概况导出（6.har）；京东商品明细用于TOP20和件数校验。')
lines.append('- 本批未提供店铺分天整体经营表，因此“每日经营趋势”暂以分月趋势/流量来源补充。')
out_md=DEL/'良渚文化_2026年5月月报分析稿.md'
out_md.write_text('\n'.join(lines),encoding='utf-8')
# also write a quick summary JSON for final chat
quick={'may_total':may_total,'apr_total':apr_total,'store_may':may,'promo_may_total':promo_may_total,'new_summary':new_summary,'paths':{'xlsx':str(out_xlsx),'md':str(out_md)}}
(WORK/'final_quick_summary.json').write_text(json.dumps(quick,ensure_ascii=False,indent=2),encoding='utf-8')
print(out_xlsx)
print(out_md)
print('may_total', may_total)