承运商管理Carrier Management

如何构建承运商绩效计分卡How to Build a Carrier Performance Scorecard

大多数履约运营跑的都不是一家承运商，而是一个组合：全国性大承运商扛货量，区域网络拼速度，预算型尾程网络吃便宜线路，有时还要一家专门处理危险品或超大件。对同时服务多个客户的 3PL 和自己发货的 DTC 品牌来说，同时跑三到八家承运商是常态。而多数运营者选承运商的方式，仍然是披着复盘外衣的拍脑袋：请客吃饭最勤快的销售拿走份额，门户最好登的承运商拿到更多货量。没人能回答每个季度都会被问到的那个问题：到底哪些承运商真正配得上它们的份额？Most fulfillment operations run not one carrier but a mix: a national heavyweight for volume, a regional player for speed, a budget last-mile network for the cheap lanes, sometimes a specialist for hazmat or oversized freight. Three to eight carriers is normal for both a 3PL serving multiple clients and a DTC brand shipping its own orders. And for most of those operators, carrier selection is still a gut call dressed up as a review. The sales rep with the best lunch wins a share. The carrier whose portal is easiest to log into gets more volume. Nobody can answer the question every quarter inevitably asks: which carriers are actually earning their share?

承运商绩效计分卡用数字回答这个问题。它把模糊的感觉（「承运商 C 最近好像变慢了」）变成可辩护的数字（「承运商 C 在这条线路上准时率 85%，比下一名低 8 个点，附加费占了账单的 18%」）。这篇文章教你如何构建一套：两条轨道共享同一份数据底座，每日轻量得分驱动每单路由，季度完整得分驱动合同复盘与份额分配。你会拿到指标定义、一个三家承运商 30 天的完整算分案例，以及可直接复制的 Excel 公式，一周之内就能跑起来。A carrier performance scorecard answers that question with numbers. It turns a vague feeling ("Carrier C feels slow lately") into a defensible figure ("Carrier C is 85% on time on this lane, 8 points below the next carrier, and its surcharges are 18% of its invoice"). This article shows you how to build one, with two tracks that share one data foundation: a lightweight daily score that drives per-order routing, and a full quarterly score that drives contract reviews and share allocation. You get the metric definitions, a complete worked example with three carriers over 30 days, and the Excel formulas to copy so it runs before the end of the week.

物流控制塔仪表盘插画：四家承运商的计分卡按时效、成本、破损、跟踪数据四个维度以彩色条形打分 — 计分卡把「感觉」变成可辩护的数字，这是所有承运商决策的前提A scorecard turns feelings into defensible numbers, and that is the precondition for every carrier decision

为什么你的承运商组合需要计分卡Why Your Carrier Mix Needs a Scorecard

一旦你同时跑多家承运商，每一单都面临选择问题：这一票给谁？默认答案全都错，而且错误会叠加。按牌价费率选，忽略了附加费、燃油调整和住宅派送费落到发票上之后承运商实际收的钱；按习惯选，忽略了承运商在变：线路重组、枢纽拥堵、区域承运商被收购后服务质量漂移；按投诉量选，忽略了投诉晚到好几周，那时钱已经花了，顾客已经留了一星差评。The moment you run more than one carrier, you have a selection problem on every single order. Which one gets this shipment? The default answers are all wrong in ways that compound. Picking by sticker rate ignores what the carrier actually charges once surcharges, fuel adjustments, and residential delivery fees land on the invoice. Picking by habit ignores that carriers change: routes get reorganized, hubs get congested, regional players get acquired and their service quality drifts. Picking by complaint volume ignores that complaints arrive weeks late, after the money is already spent and the customer already left a one-star review.

便宜承运商的真实成本在发票之外。预算型承运商每件 5.50 美元，看起来比全国性承运商的 7.00 美元划算得多，直到你把附加费、破损理赔、触发平台 chargeback 的晚到件，以及回答「我的包裹在哪」客服工单花掉的时间全加上。本文案例里，线路上最便宜的承运商恰恰是时效最差、破损最差、跟踪数据也最差的一家。牌价把这些全藏起来了。The real cost of the cheap carrier shows up off the invoice. The budget carrier at $5.50 a parcel looks like a steal next to $7.00 from the national player, until you add the surcharges, the claims for damaged goods, the late deliveries that trigger chargebacks from your marketplace, and the support hours spent answering "where is my package" tickets. On the worked example in this article, the cheapest carrier on the lane is also the worst on time, the worst on damage, and the worst on tracking data. The sticker price hides all of it.

计分卡不存在的第二个、也更隐蔽的原因是数据分散。准时扫描在承运商 A 的门户里，发票在承运商 B 的，理赔在第三家，而实际付了多少钱的真相躺在财务系统里。大多数组织里没有任何一个人同时打开这四个视图，所以没人看得见这笔权衡。计分卡把数据逼进同一张表，这是任何值得做的决策的前提。There is a second, less obvious reason scorecards fail to exist: the data is scattered. On-time scans live in one carrier portal, invoices in another, claims in a third, and the truth about what you actually paid sits in the finance system. No single person in most organizations has all four views open at once, so nobody can see the trade-off. The scorecard forces the data into one table, which is the precondition for any decision worth making.

这一切都不需要昂贵的软件。承运商通过 API 发来的数据和 TMS 事件就能喂满一张源数据表，一张搭得好的电子表格就能完成打分。难的不是工具，而是决定衡量什么、分数意味着什么。这正是本文其余部分要讲的。None of this requires expensive software. The data your carriers already send you through APIs and your TMS events can feed a single source table, and a well-built spreadsheet can do the scoring. The hard part is not the tooling, it is deciding what to measure and what the score means. That is what the rest of this article covers.

四个值得衡量的维度The Four Dimensions That Matter

值得运行的承运商计分卡都衡量同样的四件事，因为你能追踪的其他 KPI 都是这四者的症状。时效：有没有按承诺到达？成本：实际花了多少？破损：包裹有没有完好抵达？跟踪：你和你的顾客能不能看到发生了什么？Every carrier scorecard worth running measures the same four things, because every other KPI you could track is a symptom of one of them. Time: did it arrive when promised? Cost: what did it actually cost? Damage: did the parcel survive? Tracking: could you and your customer see what was happening?

维度Dimension	权重Weight	核心 KPICore KPIs
时效表现On-time performance	30%	OTD（相对承诺时效的准时派送率）、平均转运时长 vs 承诺时效OTD (on-time delivery rate vs promised transit), average transit time vs promised transit
成本Cost	30%	含附加费的实际单件成本、账单准确率、理赔成本占比Actual per-parcel cost including surcharges, invoice accuracy, claims cost ratio
破损Damage	20%	每千件破损数、按票数计的理赔率Damaged parcels per 1,000, claims rate by parcel count
跟踪数据质量Tracking data quality	20%	扫描完整度、首扫延迟、异常事件准确率Scan completeness, first-scan delay, exception event accuracy

时效表现是面向顾客的维度。OTD 是按承诺日期派送的比例，而且必须相对承诺时效衡量，不能只算实际时效。一家承诺 5 天、4 天就到的承运商 OTD 很好看，但你本来可以跟顾客承诺 3 天。单独追踪平均转运时长，让计分卡奖励诚实而紧凑的承诺，而不是注水的承诺。On-time performance is the customer-facing dimension. OTD is the share of parcels delivered by the promised date, and it must be measured against the promised transit time, not just the actual one. A carrier that quotes 5 days and delivers in 4 looks great on OTD, but you could have promised your customer 3. Track average transit time separately so the score rewards honest, tight promises rather than padded ones.

成本是揭示牌价真假的维度。实际单件成本等于开票总额除以货量，附加费算在内。账单准确率问的是账单跟报价一致的概率，一家用说不清的附加费注水的承运商，即使基础费率很低也该扣分。理赔成本占比捕捉破损和丢失造成的财务出血。Cost is the dimension that reveals whether the sticker price is real. Actual per-parcel cost is total invoiced amount divided by volume, surcharges included. Invoice accuracy asks how often the bill matches the quote, and a carrier that pads invoices with unexplained fees should lose points even when the base rate is low. Claims cost ratio captures the financial bleed from damage and loss.

破损和跟踪数据质量是保护你品牌和客服团队的两个维度。每千件破损数不看货量也能跨承运商比较。常被忽略的跟踪质量恰恰是顾客真实体验的东西：一件准时到达但三天没有任何扫描记录的包裹，无论最终结果如何，都会制造工单和焦虑。Damage and tracking data quality are the two dimensions that protect your brand and your support team. Damage per 1,000 parcels is easy to compare across carriers regardless of volume. Tracking quality, often ignored, is what your customers actually experience: a parcel that arrives on time but shows no scans for three days generates tickets and anxiety regardless of the final outcome.

30/30/20/20 的权重是合理的起点，不是法律。卖易碎品的礼品公司可以把破损推到 30%，生鲜配送可以把时效提到 40%。每个季度结合你损失的真实成本结构重新校准权重，并把权重写下来，这样下个季度的分数和这个季度含义相同。Weights of 30/30/20/20 are a sane starting point, not a law. A gift company shipping fragile goods might push damage to 30%. A grocery delivery operation might make on-time 40%. Recalibrate the weights every quarter with the actual cost structure of your losses, and write the weights down so the score means the same thing next quarter that it means this one.

双轨制，一套系统Two Tracks, One System

一个常见错误是只建一张计分卡，逼它同时干两件不同的活。每日路由需要快速、反应式的东西：这家承运商现在、在这条线路上，还达不达标？季度复盘需要完整、深思熟虑的东西：整个季度下来，这家承运商配得上它的份额吗？下季度我们要谈什么？一张得分卡两个活都干不好，双轨制干净地解决这个问题，只要两条轨道共享同一份数据。A common mistake is building one scorecard and forcing it to serve two different jobs. Daily routing needs something fast and reactive: does this carrier still meet the bar on this lane, right now? Quarterly review needs something complete and deliberate: across the full quarter, did this carrier earn its share, and what do we negotiate next quarter? One score does both jobs poorly. Two tracks solve it cleanly, as long as they share the same data.

双轨数据流图：承运商 API 与 TMS/WMS 事件汇入源数据表，再分别流向每日轻量得分（驱动每单路由）与季度完整得分（驱动合同复盘与货量分配） — 两条轨道共享同一份源数据：每日得分管路由，季度得分管合同Two tracks, one source table: the daily score drives routing, the quarterly score drives contracts

Flow diagram of the two-track system: carrier APIs and TMS/WMS events feed one source data table, which feeds both the daily lightweight score (routing decisions) and the quarterly full score (contract review and share allocation) — 两条轨道共享同一份源数据：每日得分管路由，季度得分管合同Two tracks, one source table: the daily score drives routing, the quarterly score drives contracts

轻量轨道是完整轨道的一个投影，不是第二套系统。两者都从承运商 API 和 TMS 事件构建的同一张源数据表读取。每日得分用滚动 30 天窗口和少数几个最影响决策的指标，便宜、频繁地重算。季度得分用同一张源表，维度更多、权重更规范、做完整的 1-5 分档。当两者打架时，确实会发生，每日轨道管路由，季度轨道管合同，差异在月度轻复盘里对账。The lightweight track is a projection of the full one, not a second system. Both read from the same source data table built from carrier APIs and TMS events. The daily score uses a rolling 30-day window and a handful of the most decision-relevant metrics, recomputed cheaply and often. The quarterly score uses the same source table with more dimensions, proper weighting, and a full 1-5 banding exercise. When the two disagree, which happens, the daily track wins for routing and the quarterly track wins for contracts, and you reconcile the difference in the monthly light review.

双轨设计的好处是你永远不用在速度与严谨之间二选一。路由引擎每天拿到新鲜数字，合同复盘每季度拿到一份可辩护的加权分析。而且因为两者坐在同一张源表上，一家在 7 月悄悄变差的承运商，每日得分当天就看得见，而不是 10 月季度报告跑出来才发现。The payoff of the two-track design is that you never have to choose between speed and rigor. The routing engine gets fresh numbers every day. The contract review gets a defensible, weighted analysis every quarter. And because both sit on one source table, a carrier that quietly degrades in July is visible in the daily score immediately, not discovered in October when the quarterly report runs.

构建每日轻量计分卡Building the Lightweight Daily Scorecard

每日得分只为回答每单的一个问题：这家承运商今天还配得上这条线路吗？它必须能由已经在流动的数据计算出来，按承运商乘线路的粒度，用滚动窗口抹平单日噪声而不掩盖真实趋势。The daily score exists to answer one question per order: does this carrier still qualify for this lane today? It needs to be computable from data that is already flowing in, at carrier-by-lane granularity, on a rolling window that smooths out single-day noise without hiding a real trend.

从一小撮实时指标开始，六个就够：OTD、平均转运时长、附加费占成本比、每千件破损、首扫延迟、异常率。每个都对应源数据表里的一列，所以每日得分是定时重算，不是手工操作。按季度计分卡同一套分档标准把每个指标打到 1-5，再用同样的权重合成；唯一不同的是窗口和粒度。Start with a short list of real-time indicators, six is enough: OTD, average transit time, surcharge share of cost, damage per 1,000, first-scan delay, and exception rate. Each one maps to a column in the source table, so the daily score is a scheduled recompute, not a manual exercise. Score each indicator 1-5 against the same banding you use in the quarterly scorecard, then combine them with the same weights; the only difference is the window and the granularity.

粒度是大多数每日计分卡翻车的地方。承运商绩效按线路差异巨大。一家在西海岸走廊表现出色的承运商，跨州干线可能很平庸，因为不同线路跑的是不同的枢纽和区域伙伴。按承运商乘线路打分，不要按承运商整体打分，否则你会把订单路由给一家在别的线路上挣来分数的承运商。滚动 30 天窗口让样本保持新鲜：如果一家承运商在某条线路每天只发 10 件，30 天也有几百个数据点，够分数有意义了。Granularity is where most daily scorecards go wrong. Carrier performance varies enormously by lane. A carrier that is excellent on the West Coast corridor can be mediocre on cross-country freight, because the lanes run through different hubs and regional partners. Score at carrier-by-lane granularity, not carrier overall, or you will route orders onto a carrier that earned its score on a different route. The rolling 30-day window keeps the sample fresh: if a carrier only ships 10 parcels a day on a lane, 30 days gives you a few hundred data points, enough for the score to mean something.

从每日得分里出来的路由规则是一道门槛，不是选美：在得分过了这条线路门槛的承运商里，路由给实际成本最低的那家。门槛是你拒绝卖给顾客的质量底线；达标者之间的成本比较才是省钱的来源。低于门槛的承运商在该线路进入暂停，直到滚动得分恢复，暂停对每个做路由的人都可见，这正是意义所在。在下面的案例里，线路上最便宜的承运商过不了门槛、丢掉了货量，赢家是第二便宜而不是最便宜的那家。这就是计分卡在干活。The routing rule that comes out of the daily score is a gate, not a beauty contest: among carriers whose score clears the threshold on this lane, route to the lowest actual cost. The threshold is the quality floor you refuse to sell to your customers; the cost comparison among qualifiers is where you harvest the savings. A carrier below the threshold goes on hold for that lane until its rolling score recovers, and the hold is visible to whoever does the routing, which is the whole point. In the worked example below, the cheapest carrier on the lane fails the gate and loses the traffic, and the winner is the second-cheapest, not the cheapest. That is the scorecard doing its job.

构建季度完整计分卡Building the Full Quarterly Scorecard

季度计分卡回答更大的问题：过去三个月，每家承运商配得上它持有的份额吗？我们要谈什么改变？每日轨道是反应式的，这一轨是深思熟虑的，它是你带进承运商复盘会议的文件。The quarterly scorecard answers the bigger question: over the last three months, did each carrier earn the share it holds, and what changes do we negotiate? Where the daily track is reactive, this one is deliberate, and it is the document you bring to the carrier review.

先把每个 KPI 归一化到 1-5 分档：可以对照内部目标（OTD 达到 95% 及以上打 5 分），也可以对照同行集合（线路上最好的承运商打 5 分，其余按相对比例缩放）。对照目标分档让分数绝对化、跨季度稳定；对照同行分档让分数相对化、自动调整。多数运营希望时效和破损对照目标，成本对照同行，因为成本只有在跟替代选项比较时才有意义。Start by normalizing every KPI to a 1-5 band, either against an internal target (OTD of 95% and above is a 5) or against the peer set (the best carrier on the lane is a 5, everyone else is scaled relative to them). Banding against targets keeps the score absolute and stable across quarters; banding against peers makes the score relative and self-adjusting. Most operations want targets for on-time and damage, and peer-relative scoring for cost, because cost only means something compared to the alternatives.

然后按每日轨道完全相同的权重合成四个维度：时效 30%、成本 30%、破损 20%、跟踪 20%，或者你季度校准后定下的其他权重。综合分 = 时效 × 0.30 + 成本 × 0.30 + 破损 × 0.20 + 跟踪 × 0.20。按层级汇总细节：先承运商乘线路，再承运商整体，再跟上一季度对比，这样你看到的是趋势而不是孤立的水平。一家 3.8 分、从 3.1 爬上来的承运商，跟一家从 4.5 跌下来的，是完全不同的两场对话。Then weight the four dimensions exactly as the daily track does: on-time 30%, cost 30%, damage 20%, tracking 20%, or whatever your quarterly recalibration settled on. The composite score is on-time × 0.30 + cost × 0.30 + damage × 0.20 + tracking × 0.20. Roll the detail up in layers: carrier-by-lane scores first, then carrier overall, then a comparison against the previous quarter so you can see the trend, not just the level. A carrier at 3.8 that has climbed from 3.1 is a different conversation from one that fell from 4.5.

季度计分卡的产出是一组决策，不是一份报告。份额分配：把货量向综合分与成本都配得上的承运商倾斜。费率谈判：带着分数走进谈判，因为想要回货量的承运商，得先在分数上挣到它。绩效改进计划：对低于底线的承运商，约定具体、可衡量、有期限的整改，下次复盘重新打分。复盘会议的议程应该是：过一遍趋势，让每家承运商对数据提出异议（它们会发现真实的错误，这是好事），敲定改变，把结论写进下季度合同条款，这样计分卡才有牙齿。The output of the quarterly scorecard is a set of decisions, not a report. Share allocation: shift volume toward carriers whose composite score and cost justify more. Rate conversation: come to the negotiation with the score in hand, because a carrier that wants volume back needs to earn it in the score first. Performance improvement plan: for carriers below the floor, agree on specific, measurable fixes with a deadline, then re-score at the next review. The agenda for the review meeting should be: walk the trends, let each carrier dispute the data (they will find genuine errors, which is good), agree on the changes, and write the conclusions into the next quarter's contract terms so the scorecard has teeth.

算例：三家承运商、30 天、一个决策Worked Example: 3 Carriers, 30 Days, One Decision

这是全文一直引用的完整案例。线路：USPS Priority 的替代方案，从你的西海岸仓库发往东海岸地区。三家承运商，30 天数据。承运商 A 是全国性大承运商，承运商 B 是区域玩家，承运商 C 是预算型尾程网络。Here is the full example this article has been referencing. Lane: USPS Priority alternative, from your West Coast warehouse to the East Coast region. Three carriers, 30 days of data. Carrier A is the national heavyweight, Carrier B is a regional player, Carrier C is the budget last-mile network.

原始数据，30 天，一条线路：Raw data, 30 days, one lane:

承运商Carrier	货量Volume	准时件On-time	OTDOTD	平均转运Avg transit	承诺Promised	总成本Total cost	附加费Surcharges	破损Damaged	理赔Claims	首扫First scan
承运商 ACarrier A	3,000	2,850	95.0%	3.1 天days	3.0 天days	$21,000	$1,800	21	15	0.4 天days
承运商 BCarrier B	1,800	1,638	91.0%	3.6 天days	3.5 天days	$10,800	$600	12	9	1.2 天days
承运商 CCarrier C	2,400	2,040	85.0%	4.5 天days	4.0 天days	$13,200	$2,400	33	27	2.0 天days

衍生数字和原始数字一样重要。实际单件成本：A 是 7.00 美元（21,000 ÷ 3,000），B 是 6.00 美元，C 是 5.50 美元。附加费占比：A 8.6%，B 5.6%，C 18.2%。每千件破损：A 7.0，B 6.7，C 13.8。理赔率：A 0.5%，B 0.5%，C 1.1%。承运商 C 是牌价上最便宜的，也是其他每一栏里最贵的。The derived numbers matter as much as the raw ones. Actual per-parcel cost: A is $7.00 (21,000 ÷ 3,000), B is $6.00, C is $5.50. Surcharge share: A 8.6%, B 5.6%, C 18.2%. Damage per 1,000: A 7.0, B 6.7, C 13.8. Claims rate: A 0.5%, B 0.5%, C 1.1%. Carrier C is the cheapest on the sticker and the most expensive in every other column.

各维度分档得分（1-5）：Banded scores (1-5) per dimension:

承运商Carrier	时效On-time	成本Cost	破损Damage	跟踪Tracking	综合Composite
承运商 ACarrier A	4.5	3.5	4.0	5.0	4.20
承运商 BCarrier B	3.5	4.5	4.0	3.0	3.80
承运商 CCarrier C	2.0	2.0	2.0	1.0	1.80

综合分 = 时效 × 0.30 + 成本 × 0.30 + 破损 × 0.20 + 跟踪 × 0.20。承运商 A 以 4.20 的质量分领先，承运商 B 以 3.80 拿到最佳成本分，承运商 C 崩到 1.80，因为它唯一的优势，牌价，在成本维度里被附加费和理赔抹平，而它其他每一项都垫底。Composite = on-time × 0.30 + cost × 0.30 + damage × 0.20 + tracking × 0.20. Carrier A leads at 4.20 on quality, Carrier B sits at 3.80 with the best cost score, Carrier C collapses to 1.80 because its only advantage, the sticker price, is wiped out by surcharges and claims in the cost dimension and it is worst everywhere else.

三家承运商各维度得分对比柱状图（1-5 分）：A 时效 4.5、成本 3.5、破损 4、跟踪 5；B 时效 3.5、成本 4.5、破损 4、跟踪 3；C 时效 2、成本 2、破损 2、跟踪 1 — 承运商 C 唯一的优势，牌价，被附加费和理赔抹平了Carrier C's only advantage, the sticker price, is wiped out by surcharges and claims

Grouped bar chart of dimension scores (1-5) by carrier: Carrier A on-time 4.5, cost 3.5, damage 4, tracking 5; Carrier B 3.5, 4.5, 4, 3; Carrier C 2, 2, 2, 1 — 承运商 C 唯一的优势，牌价，被附加费和理赔抹平了Carrier C's only advantage, the sticker price, is wiped out by surcharges and claims

现在用同一份数据跑两条轨道。每日路由，达标门槛 3.5：承运商 A 和 B 过关，C 不过关，进入该线路暂停。达标者里路由给实际成本最低的：承运商 B 以 6.00 美元赢得每日货量。组合里牌价最便宜的 C 一票都拿不到。季度复盘用同一组数字：A 的 4.20 综合分说明它配得上为最需要 SLA 的客户提供溢价服务，B 是货量主力，C 进入绩效改进计划，带具体目标（OTD 高于 90%、附加费占比低于 10%、首扫低于 1 天），下季度末重新打分之后才谈恢复份额。同一份源数据，两个决策，一套系统。Now run both tracks on the same data. Daily routing, with a qualification threshold of 3.5: Carrier A and B clear it, Carrier C does not and goes on hold for this lane. Among the qualifiers, route to lowest actual cost: Carrier B at $6.00 wins the daily traffic. The cheapest sticker price in the group, Carrier C, gets nothing. Quarterly review with the same numbers: Carrier A's 4.20 composite says it earns premium consideration for customers who need the tightest SLA, Carrier B is the volume workhorse, and Carrier C enters a performance improvement plan with specific targets (OTD above 90%, surcharge share below 10%, first scan under 1 day) and a re-score at the end of next quarter before any share is restored. Same source data, two decisions, one system.

Excel 模板：可以直接复制的公式The Excel Template: Formulas You Can Copy

三个 sheet，一张源表，零手工打分。下面的布局跟算例完全对齐，你可以先敲进样本数字看分数落位，再换成自己的数据。Three sheets, one source table, no manual scoring. The layout below matches the worked example, so you can type in the sample numbers and watch the scores land before you swap in your own data.

Sheet 1：原始数据。每个承运商每天一行，列：日期、承运商、线路、货量、准时件、转运天数、承诺天数、成本、附加费、破损、理赔、首扫延迟（天）。工作簿里其他一切从这张表读取，纪律就是：原始行灌进来，计分卡自己跑。Sheet 1: Raw data. One row per carrier per day, with columns: Date, Carrier, Lane, Volume, On-time, Transit days, Promised days, Cost, Surcharges, Damaged, Claims, First scan delay (days). Everything else in the workbook reads from this sheet, so the discipline is: get the raw rows in, and the scorecard runs itself.

Sheet 2：KPI。每个承运商乘线路一行，滚动 30 天窗口。公式用 SUMIFS 和 AVERAGEIFS，新增一天的原始数据会自动更新 KPI：Sheet 2: KPI. One row per carrier × lane, 30-day rolling window. Formulas use SUMIFS and AVERAGEIFS so a new day of raw data updates the KPI automatically:

KPIKPI	公式（承运商 A，线路 L1）Formula (Carrier A, Lane L1)
OTDOTD	=SUMIFS(准时件, 承运商,"A", 线路,"L1") / SUMIFS(货量, 承运商,"A", 线路,"L1")
实际单件成本Actual cost per parcel	=SUMIFS(成本, 承运商,"A", 线路,"L1") / SUMIFS(货量, 承运商,"A", 线路,"L1")
附加费占比Surcharge share	=SUMIFS(附加费, 承运商,"A", 线路,"L1") / SUMIFS(成本, 承运商,"A", 线路,"L1")
每千件破损Damage per 1,000	=SUMIFS(破损, 承运商,"A", 线路,"L1") / SUMIFS(货量, 承运商,"A", 线路,"L1") * 1000
理赔率Claims rate	=SUMIFS(理赔, 承运商,"A", 线路,"L1") / SUMIFS(货量, 承运商,"A", 线路,"L1")
平均转运天数Avg transit days	=AVERAGEIFS(转运天数, 承运商,"A", 线路,"L1", 日期,">="&TODAY()-30)
首扫延迟First scan delay	=AVERAGEIFS(首扫延迟, 承运商,"A", 线路,"L1", 日期,">="&TODAY()-30)

把引号里的列名换成你的实际列引用；如果原始表跨度超过 30 天，给 SUMIFS 公式也加上同样的日期筛选。Replace the column names in quotes with your actual column references, and add the same Date filter to the SUMIFS formulas if your raw sheet spans more than 30 days.

Sheet 3：计分卡。把每个 KPI 分档到 1-5，再给维度加权。用内部目标做 OTD 分档长这样：=IF(B2>=0.95,5,IF(B2>=0.92,4,IF(B2>=0.88,3,IF(B2>=0.85,2,1))))。每个 KPI 建一条这样的公式，套你自己的目标档位。综合分：=G2*0.3+H2*0.3+I2*0.2+J2*0.2，其中 G、H、I、J 分别是时效、成本、破损、跟踪四维得分。排名：=RANK(G2, $G$2:$G$4)。每日门槛：=IF(G2>=3.5,"达标","暂停")。所有这些都能精确复现算例，所以搭对了你会看到承运商 A 4.20、B 3.80、C 1.80，只有 C 被暂停。Sheet 3: Scorecard. Band each KPI to 1-5, then weight the dimensions. The OTD banding with internal targets looks like this: =IF(B2>=0.95,5,IF(B2>=0.92,4,IF(B2>=0.88,3,IF(B2>=0.85,2,1)))). Build one such formula per KPI with your own target bands. Composite score: =G2*0.3+H2*0.3+I2*0.2+J2*0.2, where G, H, I, J are the on-time, cost, damage, and tracking dimension scores. Ranking: =RANK(G2, $G$2:$G$4). Daily gate: =IF(G2>=3.5,"eligible","hold"). All of these reproduce the worked example exactly, so a correct setup shows Carrier A at 4.20, Carrier B at 3.80, Carrier C at 1.80, and only C on hold.

会杀死计分卡的四个陷阱Pitfalls That Kill Scorecards

每张计分卡都以四种可预测的方式之一失败，而它们都可以预防。第一是刷指标。如果你只打 OTD，承运商的回应就是拉长承诺时效，OTD 看起来漂亮，你的顾客却拿到更慢的服务。这就是为什么计分卡永远把 OTD 和平均转运时长 vs 承诺配成一对，放松承诺会在第二个指标上扣分。任何单指标设计里都要警惕同样的动态。Every scorecard fails in one of four predictable ways, and they are all preventable. The first is gaming the metrics. If you score only OTD, the carrier's response is to pad its promised transit time, which makes OTD look great while your customers get slower service. This is why the scorecard always pairs OTD with average transit time versus promised, so loosening the promise hurts the score on the second metric. Watch for the same dynamic in any single-metric design.

第二是小样本和线路结构。一条每月只有 50 件的线路会季度间剧烈摇摆，一家 10 月看起来很糟的承运商可能只是在一条细线路上赶上一周坏运气。用置信下限处理：线路货量低于最低线时，要么加宽窗口，要么压制该线路得分、改用承运商整体数字路由。第三是权重过期。适合旺季的 30/30/20/20 不适合淡季：节假日里时效和破损主导客户投诉，淡季里成本更重。每季度重新校准权重，并记录改了什么、为什么改，这样下季度的比较才诚实。The second is small samples and lane structure. A lane with 50 parcels a month will swing wildly from quarter to quarter, and a carrier that looks terrible in October may just have had a bad week on a thin lane. Handle it with confidence floors: below a minimum volume on a lane, either widen the window or suppress the score and route on carrier-overall numbers instead. The third is stale weights. The 30/30/20/20 that fits peak season does not fit the quiet quarter: during the holidays, on-time and damage dominate customer complaints, while cost matters more in the off-season. Recalibrate weights quarterly, and record what you changed and why, so next quarter's comparison stays honest.

第四个陷阱是计分卡变成一张没人打开的电子表格。如果数据要手工录入，如果每日得分要一个人去跑，如果季度复盘不在日历上，计分卡就会死于忽视。补救措施无聊但必须：自动化数据流让源表自己填满；每月开 15 分钟轻复盘，赶在问题变成灾难之前抓住趋势；每季度开完整复盘会，承运商在场；把谈定的份额和 PIP 目标写进合同，让计分卡有真实后果。没有牙齿的计分卡是一份报告。有牙齿的计分卡是一个路由引擎和一件谈判武器，那才是值得建的东西。The fourth pitfall is the scorecard that becomes a spreadsheet nobody opens. If the data has to be re-entered by hand, if the daily score needs a person to run it, if the quarterly review is not on the calendar, the scorecard dies of neglect. The fixes are boring and mandatory: automate the data flow so the source table fills itself, run a 15-minute light review monthly to catch trends before they become problems, run the full review quarterly with the carriers in the room, and write the agreed share and PIP targets into the contract so the scorecard has real consequences. A scorecard with no teeth is a report. A scorecard with teeth is a routing engine and a negotiation weapon, and that is the one worth building.

要点清单Key Takeaways

计分卡衡量四件事：时效、成本、破损、跟踪数据质量，权重 30/30/20/20 起步，每季度重新校准。A scorecard measures four things: on-time, cost, damage, and tracking data quality, starting at 30/30/20/20 and recalibrated quarterly.
建两条轨道共享一张源表：每日轻量得分管每单路由，季度完整得分管合同复盘与份额分配。Build two tracks on one source table: a daily lightweight score for per-order routing, a full quarterly score for contract reviews and share allocation.
按承运商乘线路打分，不按承运商整体打分；用滚动 30 天窗口和置信下限对付小样本。Score at carrier-by-lane granularity, not carrier overall; use a rolling 30-day window and confidence floors for thin lanes.
路由规则是一道门槛：达标者里选实际成本最低的，最便宜的牌价不一定拿到货量。The routing rule is a gate: among qualifiers, route to the lowest actual cost; the cheapest sticker price does not always win the traffic.
把 OTD 和平均转运时长 vs 承诺配对，防止承运商刷指标；把结论写进合同，计分卡才有牙齿。Pair OTD with average transit time versus promised so carriers cannot game the metrics, and write conclusions into the contract so the scorecard has teeth.
三个 sheet 的 Excel 就能跑：原始数据灌进来，SUMIFS/AVERAGEIFS 自动算 KPI，IF 分档加权重出综合分。A three-sheet Excel workbook is enough: feed the raw data in, let SUMIFS/AVERAGEIFS compute the KPIs, and band with IF formulas before weighting the composite.

常见问题FAQ

一张计分卡很难同时干好两件不同的活。每日路由需要快速、反应式的数字：这家承运商今天在这条线路上还达不达标；季度复盘需要完整、深思熟虑的加权分析。两套系统共享同一张源数据表，只是窗口、粒度和维度深度不同，所以数据一致、成本很低。One scorecard struggles to serve two different jobs well. Daily routing needs a fast, reactive number: does this carrier still qualify on this lane today. Quarterly review needs a complete, deliberate weighted analysis. Both tracks read the same source table, differing only in window, granularity, and depth, so they stay consistent at low cost.

能。数据已经存在，只是分散在承运商门户、发票和财务系统里。先手工灌进一张源表也能跑起来，但要把自动化当成目标，否则每日得分会死于手工维护。先把计分卡跑起来，再逐步自动化数据流，比等完美数据源强得多。Yes. The data already exists, it is just scattered across carrier portals, invoices, and finance systems. You can start by keying it into one source table by hand, but make automation the goal, or the daily score will die of manual maintenance. Getting the scorecard running first, then automating the data flow, beats waiting for a perfect data source.

单指标计分卡必然被刷。最常见的玩法是拉长承诺时效让 OTD 好看，代价是顾客拿到更慢的服务。对策是永远把 OTD 和平均转运时长 vs 承诺配对打分，放松承诺会在第二个指标上扣分。季度复盘时让承运商对数据提异议，也能暴露刷法。Single-metric scorecards always get gamed. The classic move is padding promised transit time to make OTD look great while customers get slower service. The counter is to always pair OTD with average transit time versus promised, so loosening the promise hurts the score on the second metric. Letting carriers dispute the data at the quarterly review also surfaces gaming.

用小样本置信下限。线路货量低于最低线时，要么加宽统计窗口，要么压制该线路得分、改用承运商整体数字做路由。细线路上一两周的坏运气不构成趋势，别让统计噪声把一家好承运商误判成差承运商。Use a confidence floor for small samples. When a lane falls below a minimum volume, either widen the window or suppress the lane score and route on carrier-overall numbers instead. A bad week on a thin lane is not a trend; do not let statistical noise misjudge a good carrier.

每个季度一次，结合你损失的真实成本结构。适合旺季的 30/30/20/20 不适合淡季：节假日里时效和破损主导客户投诉，淡季里成本更重。把改了什么、为什么改记下来，这样跨季度比较依然诚实。Quarterly, using the actual cost structure of your losses. The 30/30/20/20 that fits peak season does not fit the quiet quarter: on-time and damage dominate complaints in the holidays, while cost matters more off-season. Record what you changed and why, so quarter-over-quarter comparisons stay honest.