#!/usr/bin/env python3 # -*- coding: utf-8 -*- """单一客户供货货号分析（客户清单映射） 口径： - 订单：2026 年 已审核 标准销售订单，剔赠品、剔宝锐/宝泰/横琴内部主体 - 客户：以《客户清单_官方_20260930》为基准做名称归一（精确 → 去后缀模糊） - 单一客户 = 该货号 2026 年只卖给 1 家客户 输出：销售分析/单一客户供货货号分析_2026-10-26.xlsx """ import difflib import json import os import re import sys from collections import defaultdict import openpyxl from openpyxl.styles import Alignment, Font, PatternFill from openpyxl.utils import get_column_letter sys.path.insert(0, '/Users/liuxinyuan/Desktop/Hermes输出-工作类/脚本') import erp_sales_core as core ROOT = '/Users/liuxinyuan/Desktop/Hermes输出-工作类' OFFICIAL = os.path.join(ROOT, '数据', '客户清单_官方_20260930.json') SERIES = os.path.join(ROOT, '销售分析', '产品系列资料归档与缺口清单_2026-10-26.xlsx') OUT = os.path.join(ROOT, '销售分析', '单一客户供货货号分析_2026-10-26.xlsx') INTERNAL = ('宝锐', '宝泰', '横琴') def code_key(sku): x = re.sub(r'\s+', '', sku or '').upper() x = re.sub(r'-\d$', '', x) for t in ['NA', 'DG', 'DB', 'TP', 'FP', '5P', 'O20', 'O25', 'S50', 'S70', 'P01', 'P02', 'P03']: x = re.sub(r'-%s(?=-|$)' % t, '', x) x = re.sub(r'-{2,}', '-', x).strip('-') if re.match(r'^P[A-Z]', x): x = x[1:] m = re.match(r'^([A-Z]+)(\d+)(.*)$', x) if not m: return x L, D = m.group(1), m.group(2) if L.startswith('F') and len(L) > 1: L = L[1:] if L in ('M', 'MD') and len(D) >= 4: D = D[-3:] return L + D def norm_cust(s): s = re.sub(r'（.*?）|\(.*?\)', '', s or '').strip() for t in ('股份有限公司', '有限责任公司', '有限公司', '公司', '集团'): s = s.replace(t, '') return re.sub(r'\s+', '', s) def main(): off = json.load(open(OFFICIAL, encoding='utf-8'))['customers'] canon = {} for x in off: canon.setdefault(norm_cust(x['cust']), x['cust']) names_list = list(canon) def match(c): if c in {x['cust'] for x in off}: return c, '精确' n = norm_cust(c) if n in canon: return canon[n], '归一' m = difflib.get_close_matches(n, names_list, n=1, cutoff=0.88) return (canon[m[0]], '模糊') if m else (c, '未匹配') p = core.find_latest_erp() wb = openpyxl.load_workbook(p, data_only=True, read_only=True) ws = wb.worksheets[0] it = ws.iter_rows(values_only=True) H = [str(x or '').strip() for x in next(it)] ix = {h: i for i, h in enumerate(H)} sku_cust = defaultdict(lambda: defaultdict(float)) sku_name = {} for r in it: sku = str(r[ix['货号#']] or '').strip() cust = str(r[ix['客户']] or '').strip() if not sku or not cust: continue if str(r[ix['单据状态']]) != '已审核' or str(r[ix['单据类型']]) != '标准销售订单': continue if str(r[ix['是否赠品']] or '') in ('是', 'Y', '1'): continue if any(k in cust for k in INTERNAL): continue amt = float(r[ix['价税合计']] or 0) if amt <= 0: continue sku_cust[sku][cust] += amt nm = str(r[ix['物料名称']] or '').strip() if len(nm) > len(sku_name.get(sku, '')): sku_name[sku] = nm # 系列映射 sws = openpyxl.load_workbook(SERIES)['主货号与变体明细'] sku_series, keymap = {}, {} for r in sws.iter_rows(min_row=2, values_only=True): sku_series[r[3]] = (r[0], r[1]) keymap.setdefault(code_key(r[3]), (r[0], r[1])) rows = [] cust_kind = defaultdict(int) for sku, cm in sku_cust.items(): total = sum(cm.values()) custs = sorted(cm.items(), key=lambda x: -x[1]) canon_custs = [] for c, a in custs: cc, how = match(c) cust_kind[how] += 1 canon_custs.append((cc, a)) # 同一客户归一后可能重复，合并 merged = defaultdict(float) for c, a in canon_custs: merged[c] += a cs = sorted(merged.items(), key=lambda x: -x[1]) rep, fam = sku_series.get(sku, ('', '')) if not rep: rep, fam = keymap.get(code_key(sku), ('未归类', '')) rows.append({'sku': sku, 'name': sku_name.get(sku, ''), 'rep': rep, 'fam': fam, 'total': total, 'n': len(cs), 'top': cs[0][0], 'top_amt': cs[0][1], 'top_share': cs[0][1] / total if total else 0, 'sole': len(cs) == 1, 'custs': cs}) # 系列汇总 bys = defaultdict(list) for r in rows: bys[r['rep'] or '未归类'].append(r) srows = [] for rep, rs in bys.items(): tot = sum(r['total'] for r in rs) sole = [r for r in rs if r['sole']] sole_amt = sum(r['total'] for r in sole) top = max(rs, key=lambda r: r['top_amt']) n_cust = len({c for r in rs for c, _ in r['custs']}) srows.append({'rep': rep, 'fam': rs[0]['fam'], 'n_sku': len(rs), 'amt': tot, 'n_cust': n_cust, 'n_sole': len(sole), 'sole_amt': sole_amt, 'sole_share': sole_amt / tot if tot else 0, 'top_cust': top['top'], 'top_share': top['top_amt'] / tot if tot else 0}) srows.sort(key=lambda r: -r['amt']) wb2 = openpyxl.Workbook() Hf = Font(color='FFFFFF', bold=True) Ff = PatternFill('solid', fgColor='1F4E79') ws1 = wb2.active ws1.title = '系列客户集中度' h1 = ['代表货号', '产品系列', '有销货号数', '客户数', '2026销售额', '单一客户货号数', '单一客户销售额', '单一客户占比', 'TOP1客户', 'TOP1占比', '集中度'] ws1.append(h1) for r in srows: lvl = '单一客户' if r['n_cust'] == 1 else ('高度集中' if r['top_share'] >= 0.7 else ('较集中' if r['top_share'] >= 0.4 else '分散')) ws1.append([r['rep'], r['fam'], r['n_sku'], r['n_cust'], round(r['amt'], 2), r['n_sole'], round(r['sole_amt'], 2), round(r['sole_share'] * 100, 1), r['top_cust'], round(r['top_share'] * 100, 1), lvl]) for c in ws1[1]: c.font = Hf; c.fill = Ff; c.alignment = Alignment(horizontal='center', wrap_text=True) for i, w in enumerate([14, 34, 10, 8, 14, 12, 14, 10, 30, 9, 10], 1): ws1.column_dimensions[get_column_letter(i)].width = w ws1.freeze_panes = 'B2' ws2 = wb2.create_sheet('单一客户货号清单') ws2.append(['货号', '物料名称', '代表货号', '产品系列', '唯一客户', '2026销售额', '是否定制特征']) sole_rows = [r for r in rows if r['sole']] for r in sorted(sole_rows, key=lambda x: -x['total']): cust = r['top'] custom = '疑似定制' if (re.search(r'(ZJ|T4|SCS|P\d)', r['sku']) or cust in ('保密客户',) or cust in r['name']) else '' ws2.append([r['sku'], r['name'], r['rep'], r['fam'], cust, round(r['total'], 2), custom]) for c in ws2[1]: c.font = Hf; c.fill = Ff; c.alignment = Alignment(horizontal='center', wrap_text=True) for i, w in enumerate([24, 52, 14, 32, 32, 14, 12], 1): ws2.column_dimensions[get_column_letter(i)].width = w ws2.freeze_panes = 'A2' ws3 = wb2.create_sheet('多客户货号清单') ws3.append(['货号', '物料名称', '代表货号', '产品系列', '客户数', '2026销售额', 'TOP1客户', 'TOP1占比']) for r in sorted([r for r in rows if not r['sole']], key=lambda x: -x['total']): ws3.append([r['sku'], r['name'], r['rep'], r['fam'], r['n'], round(r['total'], 2), r['top'], round(r['top_share'] * 100, 1)]) for c in ws3[1]: c.font = Hf; c.fill = Ff; c.alignment = Alignment(horizontal='center', wrap_text=True) for i, w in enumerate([24, 52, 14, 32, 8, 14, 32, 9], 1): ws3.column_dimensions[get_column_letter(i)].width = w ws3.freeze_panes = 'A2' ws4 = wb2.create_sheet('口径说明') for line in [ ['数据源', 'ERP 2026 已审核标准销售订单；剔除赠品与宝锐/宝泰/横琴内部主体；金额=价税合计'], ['客户口径', '《客户清单_官方_20260930》1635 家；精确匹配 → 去后缀归一 → 模糊(0.88)匹配'], ['单一客户', '该货号 2026 年只有 1 家客户采购（不含赠品、不含内部）'], ['定制特征', '货号含 ZJ/T4/SCS/P 段，或客户为保密客户，或名称含客户名 → 疑似定制款'], ['系列映射', '沿用《产品系列资料归档与缺口清单_2026-10-26》的主货号→系列映射，未归类单列'], ['客户匹配', '精确 %d 条 / 归一 %d 条 / 模糊 %d 条 / 未匹配 %d 条' % ( cust_kind['精确'], cust_kind['归一'], cust_kind['模糊'], cust_kind['未匹配'])], ]: ws4.append(line) ws4.column_dimensions['A'].width = 14 ws4.column_dimensions['B'].width = 110 wb2.save(OUT) tot = sum(r['total'] for r in rows) print('货号 %d ｜销售额 %.0f 万 ｜单一客户货号 %d（%.0f 万，占 %.1f%%）' % ( len(rows), tot / 1e4, len(sole_rows), sum(r['total'] for r in sole_rows) / 1e4, sum(r['total'] for r in sole_rows) / tot * 100)) print('系列 %d ｜客户匹配 %s' % (len(srows), cust_kind)) print('\n单一客户货号 TOP15:') for r in sorted(sole_rows, key=lambda x: -x['total'])[:15]: print(' %-24s %-40s %10.0f %s' % (r['sku'], r['name'][:40], r['total'], r['top'])) print('\n系列集中度 TOP10（按销售额）:') for r in srows[:10]: print(' %-10s 客户%3d 单客货号%2d 单客占比%5.1f%% TOP1 %-18s %5.1f%%' % ( r['rep'], r['n_cust'], r['n_sole'], r['sole_share'] * 100, r['top_cust'][:18], r['top_share'] * 100)) print('✅', OUT) if __name__ == '__main__': main()