#!/usr/bin/env python3 import json, time, math, html, csv, os from pathlib import Path from datetime import date, timedelta from urllib import request, error from openpyxl import Workbook from openpyxl.styles import Font, PatternFill, Alignment from openpyxl.utils import get_column_letter OUTDIR = Path('/var/www/hermes-artifacts/wb_lyskogor_high_drr_4rk_2026-08-25') OUTDIR.mkdir(parents=True, exist_ok=True) BASE = Path('/var/www/hermes-artifacts/wb_lyskogor_ads_active_2026-08-25') DATA_PHASE1 = BASE/'data_phase1.json' FUNNEL_RAW = BASE/'.wb_work'/'funnel.json' TARGET_IDS = [39202897, 39195530, 39214949, 39386122] BEGIN, END = '2026-08-18', '2026-08-24' ADV = 'https://advert-api.wildberries.ru' ANA = 'https://seller-analytics-api.wildberries.ru' def load_env(): env = dict(os.environ) for p in ['/root/.hermes/.env', '/root/Hermes_doc/tools/marketplaces/api_accounts.env']: pp = Path(p) if pp.exists(): for line in pp.read_text(encoding='utf-8', errors='ignore').splitlines(): s=line.strip() if s and not s.startswith('#') and '=' in s: k,v=s.split('=',1) env.setdefault(k.strip(), v.strip().strip('"').strip("'")) return env def token(): env=load_env() for k in ['WB_TOKEN','WB_ANALYTICS_TOKEN','WB_API_KEY','WB_MAIN_API_KEY']: if env.get(k): return env[k] raise SystemExit('WB token not found') def req(method, host, path, tok, body=None, tries=3): data=json.dumps(body, ensure_ascii=False).encode() if body is not None else None last=None for i in range(tries): r=request.Request(host+path, data=data, method=method) r.add_header('Authorization', tok) if data: r.add_header('Content-Type','application/json') try: with request.urlopen(r, timeout=45) as resp: b=resp.read().decode('utf-8', 'ignore') return json.loads(b) if b else None except error.HTTPError as e: txt=e.read().decode('utf-8','ignore')[:500] last=(e.code, txt) if e.code==429: wait=e.headers.get('X-Ratelimit-Retry') or e.headers.get('Retry-After') or '60' try: wait=int(float(wait)) except Exception: wait=60 time.sleep(max(6,min(wait,90))); continue if e.code>=500: time.sleep(5*(i+1)); continue raise RuntimeError(f'HTTP {e.code} {host}{path}: {txt}') except Exception as e: last=(0,str(e)); time.sleep(3*(i+1)) raise RuntimeError(f'failed {method} {host}{path}: {last}') def div(a,b): return a/b if b else None def fmt_money(x): return f'{x:,.0f} ₽'.replace(',', ' ') def fmt_num(x): return f'{x:,.0f}'.replace(',', ' ') def fmt_pct(x): return '—' if x is None else f'{x:.1f}%' def cls_drr(x): return 'bad' if (x or 0) >= 20 else 'warn' if (x or 0) >= 8 else 'good' def normalize_cluster_rows(resp): # Common shapes: {stats:[{advert_id,nm_id,stats:[{norm_query, spend/sum, orders, clicks...}]}]} or variants. out=[] if not resp: return out stats = resp.get('stats') if isinstance(resp, dict) else resp if isinstance(stats, dict): stats=[stats] if not isinstance(stats, list): return out for block in stats: if not isinstance(block, dict): continue aid = block.get('advert_id') or block.get('advertId') or block.get('advertID') nm = block.get('nm_id') or block.get('nmId') or block.get('nmID') arr = block.get('stats') or block.get('clusters') or block.get('items') or [] if isinstance(arr, dict): arr=[arr] for it in arr: if not isinstance(it, dict): continue q = it.get('norm_query') or it.get('query') or it.get('cluster') or it.get('text') or it.get('keyword') or '' spend = it.get('spend', it.get('sum', it.get('cost', 0))) or 0 orders = it.get('orders', it.get('order_count', it.get('orderCount', 0))) or 0 clicks = it.get('clicks', it.get('click_count', it.get('clickCount', 0))) or 0 views = it.get('views', it.get('shows', it.get('viewCount', 0))) or 0 ctr = it.get('ctr') avg_pos = it.get('avg_pos', it.get('avgPosition', it.get('position'))) out.append({'advertId': int(aid) if aid else None, 'nmId': int(nm) if nm else None, 'query': str(q), 'spend': float(spend), 'orders': int(orders), 'clicks': int(clicks), 'views': int(views), 'ctr': ctr, 'avg_pos': avg_pos, 'raw': it}) return out def main(): tok=token() data=json.load(open(DATA_PHASE1, encoding='utf-8')) funnel_raw=json.load(open(FUNNEL_RAW, encoding='utf-8')) camp_by_id={int(c['advertId']): c for c in data['campaigns']} stats_by_id={int(k): v for k,v in data['rk_stats'].items()} vendor={int(nm): pr.get('product',{}).get('vendorCode') or 'артикул продавца не найден' for nm, pr in funnel_raw.items()} title={int(nm): pr.get('product',{}).get('title') or '' for nm, pr in funnel_raw.items()} stocks={int(nm): pr.get('product',{}).get('stocks',{}) for nm, pr in funnel_raw.items()} funnel={int(nm): pr.get('statistic',{}).get('selected',{}) for nm, pr in funnel_raw.items()} # Fetch cluster stats targeted all_cluster=[]; raw={} for aid in TARGET_IDS: c=camp_by_id[aid] body={'from': BEGIN, 'to': END, 'items':[{'advert_id': aid, 'nm_id': int(nm)} for nm in c.get('nm_ids',[])[:100]]} try: resp=req('POST', ADV, '/adv/v0/normquery/stats', tok, body, tries=4) except Exception as e: resp={'error': str(e)} raw[str(aid)]=resp rows=normalize_cluster_rows(resp) # If API didn't echo ids, fill campaign/nm when single nm for r in rows: r['advertId'] = r['advertId'] or aid if not r['nmId'] and len(c.get('nm_ids',[]))==1: r['nmId']=int(c['nm_ids'][0]) all_cluster.extend(rows) time.sleep(6) # organic for target nms organic={} for nm in sorted({int(nm) for aid in TARGET_IDS for nm in camp_by_id[aid].get('nm_ids',[])}): body={"currentPeriod":{"start":BEGIN,"end":END},"nmIds":[nm],"topOrderBy":"openCard","orderBy":{"field":"openCard","mode":"desc"},"limit":50,"page":1} try: organic[str(nm)] = req('POST', ANA, '/api/v2/search-report/product/search-texts', tok, body, tries=2) except Exception as e: organic[str(nm)]={'error': str(e)} time.sleep(1) (OUTDIR/'normquery_raw.json').write_text(json.dumps(raw, ensure_ascii=False, indent=2), encoding='utf-8') (OUTDIR/'organic_raw.json').write_text(json.dumps(organic, ensure_ascii=False, indent=2), encoding='utf-8') # build campaign summaries summaries=[] for aid in TARGET_IDS: c=camp_by_id[aid]; st=stats_by_id.get(aid,{}) nm=int(c['nm_ids'][0]) if c.get('nm_ids') else None spend=st.get('spend',0) or 0; orders=st.get('orders',0) or 0; revenue=st.get('revenue',0) or 0; clicks=st.get('clicks',0) or 0; views=st.get('views',0) or 0 fu=funnel.get(nm,{}) if nm else {} conv=fu.get('conversions',{}) or {} stock=stocks.get(nm,{}) if nm else {} cl=[r for r in all_cluster if r['advertId']==aid] cl_spend=sum(r['spend'] for r in cl) bad_q=[r for r in cl if r['spend']>=100 and r['orders']==0] bad_spend=sum(r['spend'] for r in bad_q) top_bad=sorted(bad_q, key=lambda x:-x['spend'])[:20] good_q=[r for r in cl if r['orders']>0] good_spend=sum(r['spend'] for r in good_q) # approximate potential saving to hit 20% DRR target=20.0 saving = spend*(1-target/(spend/revenue*100)) if revenue and (spend/revenue*100)>target else 0 diagnosis=[] drr=div(spend,revenue)*100 if revenue else None ctr=div(clicks,views)*100 if views else None cpc=div(spend,clicks); cpo=div(spend,orders) if drr and drr>20: diagnosis.append(f'ДРР {drr:.1f}% выше целевых 20%') if ctr and ctr<2.0: diagnosis.append('CTR низкий') if cpc and cpc>13: diagnosis.append(f'CPC дорогой {cpc:.1f} ₽') if bad_spend>300: diagnosis.append(f'есть расход без заказов по запросам ≈ {bad_spend:.0f} ₽') if conv.get('addToCartPercent',0)<8 and fu.get('openCount',0)>500: diagnosis.append(f'низкий переход→корзина {conv.get("addToCartPercent",0)}%') if conv.get('cartToOrderPercent',0)<25 and fu.get('cartCount',0)>50: diagnosis.append(f'низкий корзина→заказ {conv.get("cartToOrderPercent",0)}%') summaries.append({'advertId':aid,'name':c.get('name',''),'nmId':nm,'vendorCode':vendor.get(nm,'артикул продавца не найден'),'title':title.get(nm,''),'bid':c.get('bid_search'), 'spend':spend,'views':views,'clicks':clicks,'ctr':ctr,'cpc':cpc,'orders':orders,'revenue':revenue,'drr':drr,'cpo':cpo,'roas':div(revenue,spend),'stock_wb':stock.get('wb',0),'stock_mp':stock.get('mp',0),'funnel_open':fu.get('openCount',0),'funnel_cart':fu.get('cartCount',0),'funnel_orders':fu.get('orderCount',0),'atc':conv.get('addToCartPercent',0),'cto':conv.get('cartToOrderPercent',0),'buyout':conv.get('buyoutPercent',0),'cluster_rows':len(cl),'cluster_spend':cl_spend,'bad_query_spend':bad_spend,'bad_queries':top_bad,'good_query_spend':good_spend,'saving_to_20pct':saving,'diagnosis':'; '.join(diagnosis) or 'основная причина — высокий CPM/ставка при недостаточной выручке'}) # Cannibalization by query among target four byq={} for r in all_cluster: q=r['query'].strip().lower() if not q: continue byq.setdefault(q,[]).append(r) cann=[] for q, arr in byq.items(): nms={x['nmId'] for x in arr if x.get('nmId')} if len(nms)>=2 and sum(x['spend'] for x in arr)>100: best=max(arr, key=lambda x:(x['orders'], -x['spend'])) loser_spend=sum(x['spend'] for x in arr if x is not best) cann.append({'query':q,'nms':sorted(nms),'spend':sum(x['spend'] for x in arr),'orders':sum(x['orders'] for x in arr),'best_nm':best['nmId'],'loser_spend':loser_spend,'rows':arr}) cann.sort(key=lambda x:-x['loser_spend']) # xlsx xlsx=OUTDIR/'WB_Лысогор_почему_высокий_ДРР_4РК_18-24_авг_2026.xlsx' wb=Workbook() ws=wb.active; ws.title='Итог по 4 РК' headers=['РК','Название','nmID / vendorCode','Товар','Расход','Заказы','Выручка','ДРР','CPC','CPO','CTR','Ставка','Остаток WB','Остаток FBS/MP','Воронка open','Корзины','Заказы карточки','ATC%','Cart→Order%','Расход запросов 0 заказов','Экономия до ДРР 20%','Диагноз'] ws.append(headers) for s in summaries: ws.append([s['advertId'],s['name'],f"{s['nmId']} / {s['vendorCode']}",s['title'],s['spend'],s['orders'],s['revenue'],s['drr'],s['cpc'],s['cpo'],s['ctr'],s['bid'],s['stock_wb'],s['stock_mp'],s['funnel_open'],s['funnel_cart'],s['funnel_orders'],s['atc'],s['cto'],s['bad_query_spend'],s['saving_to_20pct'],s['diagnosis']]) ws2=wb.create_sheet('Запросы без заказов') ws2.append(['РК','nmID / vendorCode','Запрос','Расход','Клики','Показы','Заказы','CTR','avg_pos']) for s in summaries: for r in s['bad_queries']: ws2.append([s['advertId'], f"{r['nmId']} / {vendor.get(r['nmId'],'артикул продавца не найден')}", r['query'], r['spend'], r['clicks'], r['views'], r['orders'], r['ctr'], r['avg_pos']]) ws3=wb.create_sheet('Все запросы') ws3.append(['РК','nmID / vendorCode','Запрос','Расход','Клики','Показы','Заказы','CPC','CTR','avg_pos']) for r in sorted(all_cluster, key=lambda x: (-x['spend'], x.get('query',''))): ws3.append([r['advertId'], f"{r['nmId']} / {vendor.get(r['nmId'],'артикул продавца не найден')}", r['query'], r['spend'], r['clicks'], r['views'], r['orders'], div(r['spend'],r['clicks']), r['ctr'], r['avg_pos']]) ws4=wb.create_sheet('Каннибализация') ws4.append(['Запрос','SKU в запросе','Расход','Заказы','Лучший SKU','Расход проигрывающих']) for c in cann: ws4.append([c['query'], ', '.join(f"{nm} / {vendor.get(nm,'артикул продавца не найден')}" for nm in c['nms']), c['spend'], c['orders'], f"{c['best_nm']} / {vendor.get(c['best_nm'],'артикул продавца не найден')}", c['loser_spend']]) for w in wb.worksheets: for cell in w[1]: cell.fill=PatternFill('solid', fgColor='1F2937'); cell.font=Font(color='FFFFFF', bold=True) w.freeze_panes='A2'; w.auto_filter.ref=w.dimensions for col in range(1,w.max_column+1): w.column_dimensions[get_column_letter(col)].width=min(48, max(12, len(str(w.cell(1,col).value))+4)) wb.save(xlsx) # HTML def srow(s): return f"{s['advertId']}
{html.escape(s['name'])}{s['nmId']} / {html.escape(str(s['vendorCode']))}
{html.escape(s['title'])}{fmt_money(s['spend'])}{fmt_num(s['orders'])}{fmt_money(s['revenue'])}{fmt_pct(s['drr'])}{fmt_pct(s['ctr'])}
CPC {fmt_money(s['cpc']) if s['cpc'] else '—'}ATC {s['atc']}%
Cart→Order {s['cto']}%{fmt_money(s['bad_query_spend'])}{html.escape(s['diagnosis'])}" def bad_list(s): items=''.join(f"{html.escape(r['query'])}{fmt_money(r['spend'])}{fmt_num(r['clicks'])}{fmt_num(r['orders'])}{fmt_num(r['views'])}" for r in s['bad_queries'][:10]) return f"

{s['advertId']} — {s['nmId']} / {html.escape(str(s['vendorCode']))}

{items or ''}
Запрос без заказовРасходКликиЗаказыПоказы
Крупных запросов без заказов не найдено / API не отдал детализацию.
" htmlp=OUTDIR/'WB_Лысогор_почему_высокий_ДРР_4РК_18-24_авг_2026.html' html_doc=f"""

WB Лысогор — почему высокий ДРР в 4 РК

Период: {BEGIN}—{END}. Все 4 РК — Аукцион cpm/manual, поэтому технически доступны минус-фразы/ставки по кластерам. Отчёт read-only, изменений в кабинете нет.
Важно: рекомендации — аналитические. Ставки, бюджеты, минусы и паузы не менялись.

Итог

{''.join(srow(s) for s in summaries)}
РКТоварРасходЗаказыВыручкаДРРCTR/CPCВоронка карточкиРасход 0 заказовПричина

Запросы/кластеры с расходом без заказов

{''.join(bad_list(s) for s in summaries)}

Каннибализация между этими 4 РК

{''.join(f'' for c in cann[:30]) or ''}
ЗапросSKUРасходЗаказыЛучший SKUРасход проигрывающих
{html.escape(c["query"])}{html.escape(", ".join(str(n)+" / "+vendor.get(n,"артикул продавца не найден") for n in c["nms"]))}{fmt_money(c["spend"])}{fmt_num(c["orders"])}{c["best_nm"]} / {html.escape(vendor.get(c["best_nm"],"артикул продавца не найден"))}{fmt_money(c["loser_spend"])}
Внутри этих 4 РК явной само-конкуренции по одному запросу API не показал.

Excel: {xlsx.name}. Raw: normquery_raw.json, organic_raw.json.

""" htmlp.write_text(html_doc, encoding='utf-8') result={'period':[BEGIN,END],'summaries':summaries,'cannibalization':cann[:30],'files':{'html':str(htmlp),'xlsx':str(xlsx),'normquery_raw':str(OUTDIR/'normquery_raw.json'),'organic_raw':str(OUTDIR/'organic_raw.json')}} (OUTDIR/'summary.json').write_text(json.dumps(result, ensure_ascii=False, indent=2, default=str), encoding='utf-8') print(json.dumps({'summaries':summaries,'cannibalization_count':len(cann),'files':result['files']}, ensure_ascii=False, indent=2, default=str)) if __name__=='__main__': main()