#!/usr/bin/env python3 # -*- coding: utf-8 -*- """生命科学：客户分层 + 广东/浙江成长型课题组清单（农村包围城市口径） 分层（2026 年 1–10 月 ERP，标准单/非赠品/已审核） 头部 ：2026 ≥ 30 万 腰部 ：5–30 万 外围/成长：< 5 万，或 2026 新开（2025=0） 成长型筛选：在外围/成长层里，满足 ① 2026 有采购 ② 同比 ≥ +30% 或新客 ③ 近 6 个月有活动 输出：数据/2027战略_生命科学_成长型课题组.json """ import io import json import re import warnings from collections import defaultdict warnings.filterwarnings('ignore') import openpyxl # noqa: E402 import xlrd # noqa: E402 W = '/Users/liuxinyuan/Desktop/Hermes输出-工作类' D = W + '/数据/' BASE = '/Users/liuxinyuan/Desktop/hermas输入-工作台数据库' XLS = ('/Users/liuxinyuan/.hermes/profiles/zone-b/cache/documents/' 'doc_ec9c274f3021_诊断原料&生命科学客户清单20260930.xls') LO, HI = '2026-04-17', '2026-10-17' BAORUI = ('宝锐', '宝泰', '横琴') # ── 官方生命科学清单（含区域）──────────────────────────────────── wb = xlrd.open_workbook(XLS) sh = wb.sheet_by_name('生命科学销售部') LS = [] for r in range(2, sh.nrows): cust = str(sh.cell_value(r, 1)).strip() if not cust: continue LS.append({'cust': cust, 'sp': str(sh.cell_value(r, 2)).strip(), 'region': str(sh.cell_value(r, 3)).strip()}) print('█ 官方生命科学客户 %d 家（广东 %d / 浙江 %d）' % (len(LS), sum(1 for x in LS if '广东' in x['region']), sum(1 for x in LS if '浙江' in x['region']))) NAMES = {x['cust']: x for x in LS} SORTED = sorted(NAMES, key=len, reverse=True) def match(raw): c = str(raw or '').strip() c = re.sub(r'[-－](广东|浙江|上海|北京|江苏|华南|华北|华中|华西)$', '', c) for n in SORTED: if n in c or (len(c) >= 5 and c in n): return n return '' # ── ERP 2025 / 2026 1–10 月 ────────────────────────────────────── def load(path, sheet, y): wb = openpyxl.load_workbook(path, data_only=True, read_only=True) ws = wb[sheet] if sheet else wb[wb.sheetnames[0]] hdr, ix = None, {} out = defaultdict(float) for row in ws.iter_rows(min_row=1, values_only=True): if hdr is None: hdr = [str(x or '').strip() for x in row] ix = {k: hdr.index(k) for k in hdr if k in ('客户', '价税合计', '是否赠品', '单据类型', '单据状态', '合同月份', '合同日期')} continue g = {k: (row[v] if v < len(row) else None) for k, v in ix.items()} if '标准' not in str(g.get('单据类型') or ''): continue if str(g.get('是否赠品') or '').strip() == '是': continue if '单据状态' in g and str(g.get('单据状态') or '').strip() not in ('已审核', ''): continue c = str(g.get('客户') or '').strip() if not c or any(k in c for k in BAORUI): continue mon = str(g.get('合同月份') or g.get('合同日期') or '')[:7] if not (mon in {'%d-%02d' % (y, m) for m in range(1, 11)}): continue try: out[c] += float(g.get('价税合计') or 0) except Exception: pass return out a25 = load(BASE + '/2024和2025历史数据/2025全年订单.xlsx', 'Sheet1', 2025) a26 = load(BASE + '/月度销售额数据/销售订单_20260101-20261015.xlsx', None, 2026) def remap(d): out = defaultdict(float) for k, v in d.items(): n = match(k) if n: out[n] += v return out a25, a26 = remap(a25), remap(a26) print('█ ERP 对齐官方清单后：2025 有流水 %d 家 ｜ 2026 有流水 %d 家 ｜ 2026 合计 %.1f 万' % (sum(1 for v in a25.values() if v > 0), sum(1 for v in a26.values() if v > 0), sum(a26.values()) / 1e4)) # ── 近 6 个月活动计数 ──────────────────────────────────────────── def match(raw): c = str(raw or '').strip() c = re.sub(r'[-－](广东|浙江|上海|北京|江苏|华南|华北|华中|华西)$', '', c) for n in SORTED: if n in c or (len(c) >= 5 and c in n): return n return '' act_n, act_txt = defaultdict(int), defaultdict(str) fs = json.load(io.open(D + 'activities_feishu.json', encoding='utf-8'))['items'] for x in fs: if str(x.get('dept_raw')) not in ('广东', '浙江'): continue d = str(x.get('date') or '')[:10] if not (LO <= d <= HI): continue c = match(x.get('customer')) if not c: continue act_n[c] += 1 t = re.sub(r'<[^>]+>|!\[.*?\)|\s+', ' ', str(x.get('text') or '')) if len(t) > len(act_txt[c]): act_txt[c] = t mp = json.load(io.open(D + 'daily_activities_mcp.json', encoding='utf-8'))['items'] for x in mp: v = x.get('field_5ed7ab') if isinstance(v, str): m = re.search(r"'label':\s*'([^']+)'", v); v = m.group(1) if m else v elif isinstance(v, dict): v = v.get('label', '') if v not in ('广东', '浙江'): continue d = str(x.get('start_time') or '')[:10] if not (LO <= d <= HI): continue cu = x.get('field_5f20fc') if isinstance(cu, str): m = re.search(r"'label':\s*'([^']+)'", cu); cu = m.group(1) if m else cu elif isinstance(cu, dict): cu = cu.get('label', '') c = match(cu) if not c: continue act_n[c] += 1 t = re.sub(r'<[^>]+>|!\[.*?\)|\s+', ' ', str(x.get('field_b99055') or '')) if len(t) > len(act_txt[c]): act_txt[c] = t # ── 分层 ──────────────────────────────────────────────────────── rows = [] for x in LS: c = x['cust'] v25, v26 = a25.get(c, 0.0), a26.get(c, 0.0) if v26 >= 300000: tier = '头部' elif v26 >= 50000: tier = '腰部' else: tier = '外围/成长' growth = None if v25 <= 0 else (v26 - v25) / v25 * 100 rows.append({'cust': c, 'sp': x['sp'], 'region': x['region'], 'tier': tier, 'a25': round(v25), 'a26': round(v26), 'growth': (round(growth, 1) if growth is not None else None), 'acts': act_n.get(c, 0), 'sample': act_txt.get(c, '')[:120]}) agg = defaultdict(lambda: {'n': 0, 'a26': 0.0}) for r in rows: a = agg[r['tier']] a['n'] += 1 a['a26'] += r['a26'] tot = sum(x['a26'] for x in rows) or 1 print('\n█ 客户分层（2026 年 1–10 月）') for t in ('头部', '腰部', '外围/成长'): a = agg[t] print(' %-8s %3d 家 ｜ %8.1f 万 ｜ 占比 %4.1f%%' % (t, a['n'], a['a26'] / 1e4, a['a26'] / tot * 100)) # 成长型：外围/成长层 + 有活动 + （新客 或 同比 ≥30%） cand = [r for r in rows if r['tier'] == '外围/成长' and r['acts'] > 0 and (r['a25'] == 0 or (r['growth'] is not None and r['growth'] >= 30))] cand.sort(key=lambda r: (-r['acts'], -r['a26'])) print('\n█ 成长型课题组候选 %d 家（有活动 + 新客或同比≥30%%）' % len(cand)) for reg in ('广东', '浙江'): sub = [r for r in cand if reg in r['region']] print('\n ▸ %s（%d 家）' % (reg, len(sub))) for r in sub[:14]: g = ('新客' if r['a25'] == 0 else '%+.0f%%' % r['growth']) print(' %-30s %-5s 2026 %6.1f 万 %-6s 活动 %2d ｜ %s' % (r['cust'][:28], r['sp'], r['a26'] / 1e4, g, r['acts'], r['sample'][:60])) json.dump({'generated': '2026-10-17', 'window': [LO, HI], 'tiers': {t: {'n': agg[t]['n'], 'amt26': round(agg[t]['a26'])} for t in agg}, 'customers': rows, 'growth_candidates': cand}, io.open(D + '2027战略_生命科学_成长型课题组.json', 'w', encoding='utf-8'), ensure_ascii=False, indent=1) print('\n✅ 数据/2027战略_生命科学_成长型课题组.json')