A parcel-damage workbook should behave like an operating control, not a folder of screenshots. Every row must answer what moved, whether it belongs in the reporting period, which physical parcel was damaged, what evidence exists, what corrective shipment followed, and how the financial case ended. This guide explains how to run that control with the downloadable workbook, using synthetic examples and explicit acceptance tests rather than unverified industry averages.
Download the current workbook and sample rows
Download the English XLSX workbook v3 and the UTF-8 BOM CSV sample v2. Both were verified on October 13, 2026. The spreadsheet contains nine sheets: Instructions, Shipments, Damage Taxonomy, Photos/Evidence, Claims, Rates, Scenario, QA, and Methodology. The CSV is an import-ready example of the shipment-level fields and deliberately includes a duplicate row and a replacement row so a team can test its controls.
All rows are synthetic fixtures, not customer records or performance benchmarks. Keep the file language, version, and verification date with any internal copy. Before using a carrier rule, confirm the current account, service, country, commodity, packaging, notice deadline, and filing deadline. Public carrier pages explain general processes but do not replace the applicable transport contract.
Define the record grain before entering a shipment
The Shipments sheet uses one row per recorded shipment event. That grain is deliberately narrower than an order and broader than a scan. A corporate gift order may create an original parcel, a replacement parcel, and several carrier scans, yet only two shipment rows belong in this table. Scan history belongs in evidence, while the replacement remains a distinct shipment linked to the original.
Use Shipment ID as the stable unique key. Do not recycle it after cancellation, edit it to describe a later replacement, or append a second row with the same key. Record Type distinguishes Original from Replacement. Original Shipment ID is blank for an original and points back to the first parcel for a replacement. This simple relationship prevents the common mistake of treating a service recovery as a second damage incident.
The workbook exposes four helper decisions for every row: In period, Unique shipment, Unique parcel count, and Unique damage incident. They are visible so reviewers can challenge the logic instead of trusting a hidden summary. In period is true only when the ship date falls inclusively between Period Start and Period End. Unique shipment is true only for the first occurrence of a Shipment ID. Unique parcel count is true for a unique shipment in period. Unique damage incident is true for a unique, in-period original shipment marked damaged; a replacement does not become a new incident merely because it costs money.
When importing from an order platform, warehouse system, or carrier export, stage the file first. Normalize dates to one timezone, preserve leading zeros in identifiers, map empty values deliberately, and reject rows without a stable key. Compare the staged row count with the accepted unique-shipment count and keep both numbers. A difference may be a valid duplicate caught by the control; it must not disappear without explanation.
Use the nine sheets as one closed control loop
Instructions states the file version, scope, and safe-use rules. Shipments is the operational ledger. Damage Taxonomy gives controlled categories so free text does not fragment the data. Photos/Evidence stores references and checks, not image binaries. Claims joins carrier cases to valid shipment keys. Rates applies the reporting window and publishes denominators. Scenario contains fixed tests for edge conditions. QA turns structural expectations into pass or fail results. Methodology preserves definitions, ownership, privacy, and source-review dates.
The order matters. A team first agrees definitions in Methodology, then records shipments and evidence, opens a claim when appropriate, and reviews Rates only after Scenario and QA pass. Rates is not an independent dashboard. If a source field, relationship, or test fails, the summary is provisional even when its arithmetic looks plausible.
Assign sheet owners rather than one vague file owner. Fulfillment operations owns Shipment ID, parcel facts, packaging result, and first inspection. Customer support owns the recipient report timestamp and service-recovery decision. Claims operations owns evidence completeness, carrier case number, and deadlines. Finance owns recovery, credit, write-off, and closure. Analytics or quality owns the reporting window, deduplication tests, and version release. One person may fill several roles in a small team, but each acceptance decision should still show which role made it.
Enter only fields that support a decision
The shipment ledger should not become a shadow customer database. Use a pseudonymous recipient reference, campaign or program reference, ship date, origin and destination region, carrier/service, tracking reference, package count, Record Type, Original Shipment ID, damage flag, damage category, report time, replacement decision, and operational owner. Store the minimum address or contact data needed by the authorized claim team elsewhere under appropriate access controls.
Record timestamps with timezone. A photograph taken at 09:10 in Toronto, a support ticket opened at 09:30 Eastern Time, and a carrier notice filed at 15:00 UTC can be ordered only if the zones survive. Do not overwrite the first report time when additional photos arrive. Add a new evidence row and preserve the original event.
For monetary values, keep amount, currency, and meaning separate. Product replacement value, outbound freight, replacement freight, carrier recovery, platform credit, and internal write-off are different measures. Converting them into one reporting currency is an analysis step that requires an exchange-rate date and method; it should not erase the original amounts.
Use notes for exceptions, not for primary facts that belong in structured columns. A sentence such as “recipient said the box looked bad” cannot replace a damage category, report time, evidence status, and disposition. Structured fields make cases comparable; concise notes preserve context that the taxonomy cannot express.
Apply a compact damage and evidence taxonomy
The workbook separates what failed from how it was proven. That distinction lets packaging owners aggregate crushed-corner events while claims staff independently check whether the required files exist.
| Controlled field | Use it for | Required evidence | Acceptance rule |
|---|---|---|---|
| External crush or puncture | Visible compression, tear, or penetration of the shipping carton | All carton faces, label, damaged area, internal protection | Images identify the same parcel and show context as well as detail |
| Moisture or leakage | Wet carton, internal spill, or liquid migration | Outer and inner packaging, source item, affected contents, handling notes | Potential safety risk is isolated before routine handling continues |
| Product breakage | Crack, fracture, deformation, or loss of function | Whole product, close-up, model reference, packaging position | Record distinguishes cosmetic from unusable condition |
| Missing contents | Quantity shortfall or open package | Seal, carton interior, packing record, weight evidence | Expected and received quantities are explicit |
| Evidence completeness | Claim-readiness independent of damage type | Invoice, tracking, photos, packaging, report time, claim reference | Each required item is present, not merely requested |
Do not infer cause from appearance alone. “External crush” describes observed condition; “carrier mishandling” is a causal conclusion that may need investigation. Likewise, a delivery scan does not prove that the contents were intact at handoff. Keep observation, proposed cause, decision, and external determination in separate fields or notes.
Build an evidence register without turning it into a file dump
The Photos/Evidence sheet records Evidence ID, Shipment ID, evidence type, capture time, custodian, immutable storage reference, access restriction, and review result. The binary stays in the approved repository. A URL pasted into the sheet is not sufficient if it expires, permits unrestricted access, or cannot be tied to the captured file. Use a durable reference and, where policy permits, record a file hash or repository version.
Photograph the unopened parcel when safe, each exterior face, the shipping label, seals, internal protection, damaged area in context, damaged item in detail, and any measured dimensions or weight that matter. Preserve original files. Edited copies can help annotation, but the original should remain identifiable. If the recipient supplied evidence, record that provenance and avoid claiming the warehouse took the image.
The evidence reviewer checks four things: identity, chronology, completeness, and privacy. Identity means the tracking or shipment reference matches. Chronology means capture and report times make sense. Completeness means both context and close-up views exist, along with required commercial documents. Privacy means the case exposes no more personal data than the authorized process needs.
UPS describes supporting documents and inspection-related evidence for claims. FedEx explains that declared value is not shipping insurance. DHL describes shipment-value protection at a general level. These sources were verified on October 13, 2026. They are linked at their first meaningful mention here; teams must still verify the live terms for the booked service and jurisdiction.
Reconcile claims, replacements, credits, and write-offs separately
The Claims sheet permits a claim only when Shipment ID exists in Shipments. That referential-integrity test prevents an orphan carrier case from entering the recovery total. Record carrier, case number, filing date, claimed amount and currency, current status, next action, decision date, approved amount, received amount, and closure reason. If the carrier requests more evidence, do not mark the claim denied; record the request, owner, due date, and evidence supplied.
Keep recipient recovery apart from carrier recovery. A replacement shipment is a service action linked through Original Shipment ID. A refund or goodwill credit is another service action. A carrier payment is a financial recovery. An inventory salvage credit and a supplier reimbursement are different recoveries. The case can therefore be operationally resolved for the recipient while financially open, or financially denied while the recipient outcome is already complete.
Set an explicit closure rule. For example: recipient outcome completed or declined with reason; all evidence decisions recorded; carrier case paid, denied, withdrawn, or closed; all credits and write-offs posted; no overdue next action; owner and closure date present. Closing because “nothing happened recently” hides work and distorts recovery timing.
If one carrier case covers several parcels, either record a case-allocation table outside this simple workbook or split approved and received amounts using a documented method. Never repeat the full payment on every shipment row. Reconciliation should prove that allocated recoveries sum to the actual remittance.
Calculate a defensible period damage rate
The rate in this workbook is intentionally simple: unique damaged original shipments in the selected period divided by unique parcels shipped in the same period. The start and end dates are inclusive. When the denominator is zero, the output remains blank instead of presenting a misleading zero-percent result.
“Unique” is a data-control word, not a formatting choice. A duplicated original row with the same Shipment ID contributes once. A replacement contributes to shipment workload and cost analysis but does not create a second original damage incident. A second, independently damaged replacement can be investigated, but the team must decide whether its metric is replacement-quality failure, total damaged parcels, or original-order incident rate. Do not silently mix those questions.
Publish the numerator, denominator, date window, geography, service scope, and inclusion rules beside any percentage. A value of two damaged originals out of three unique parcels in a synthetic scenario is a control fixture, not a claimed real-world rate. The workbook contains no customer result and no industry benchmark.
For trend review, freeze monthly extracts or versioned outputs rather than changing historical definitions in place. If the taxonomy or grain changes, state the effective date and either restate prior periods transparently or show a break in series. A smooth chart produced by incompatible definitions is less useful than a visible discontinuity.
Run the scenario sheet before trusting the summary
The Scenario sheet contains boundary cases that should survive every import change. Test one confirms that two unique damaged original shipments produce an incident count of two. Test two repeats Shipment ID S-1001 and expects a unique count of one. Test three links S-1001-R1 to S-1001 as a replacement and expects zero new damage incidents from that replacement. Test four moves the reporting window to a future period with no shipments and expects a blank rate. Test five confirms that every claim Shipment ID exists in the shipment ledger.
These are executable acceptance tests, not documentation examples. A reviewer should see the input, expected result, actual result, and localized PASS or FAIL. If a test fails, stop distribution of the new workbook version. Correct the source mapping or formula, recalculate, reopen the file, and rerun all tests; do not edit the displayed result to PASS.
Add local cases when the operation has special rules. A multi-parcel order may need a parcel-sequence key. A consolidation center may introduce a second handoff. A prepaid return may create a reverse-logistics row. Each extension needs a defined grain, relationship to the original, and a test that prevents double counting.
Use the QA sheet as a release checklist
The QA sheet checks required sheet names, unique keys, valid Record Type values, replacement links, claim references, reporting dates, scenario outcomes, formula-error scans, and method notes. Pair those mechanical checks with a visual review of every sheet. Confirm that headers remain visible, wrapped text is readable, dates and currencies use the intended format, CJK fonts render when applicable, formulas display calculated values, and no meaningful field is clipped off-screen or pushed into an unprintable area.
A release owner should also inspect the file outside the authoring process. Open the saved binary, not only an in-memory preview. Recalculate it, search for spreadsheet errors, test filters, inspect hidden rows or columns, and verify that CSV import preserves the BOM and identifiers. Record filename, locale, version, bytes, cryptographic hash, media type, storage receipt, and verification date.
When a workbook fails QA, issue a new version rather than replacing evidence for the old version. Keep the prior receipt archived, mark it superseded, and bind public links only after the new binary is transported and downloaded independently. That version chain is what allows an analyst to explain which formulas produced a published number.
Hypothetical case one: a duplicated damage import
Assume a fulfillment analyst exports September shipments from a warehouse system. Shipment S-1001 appears twice because the second export includes an updated damage note. Both rows have the same ship date, tracking reference, and original record type. A naïve row count produces two shipped parcels and two damage incidents. The workbook’s helper columns mark the first occurrence as unique and the second as duplicate, producing one parcel and one incident.
The owner has three alternatives. They can delete the later row and risk losing the richer note; merge source fields into one governed record; or retain both staged rows while accepting only the first unique shipment into the metric. The chosen method here is to preserve staging evidence, merge the authoritative fields, and document the duplicate rule. The quality owner compares staged rows, accepted unique rows, and rejected duplicates.
Failure evidence would be a scenario result greater than one for S-1001, two conflicting shipment facts with no resolution, or a metric whose denominator changes when export order changes. Recovery means pausing the report, selecting a deterministic source-of-truth rule, correcting the mapping, recalculating, and rerunning all scenarios. Acceptance requires one unique parcel, one unique incident, preserved change history, and reviewer sign-off. This is a hypothetical control example, not a customer outcome.
Hypothetical case two: replacement before claim recovery
Assume original shipment S-1001 is damaged and recipient support authorizes replacement S-1001-R1 before the carrier decides the claim. Operations records the new shipment as Record Type Replacement and links Original Shipment ID to S-1001. The replacement has its own tracking and cost but contributes zero to unique original damage incidents. The Claims sheet opens case C-2001 against S-1001, while finance records replacement freight separately.
The service owner could wait for a claim decision, refund instead of replace, source locally, or send the replacement immediately. The hypothetical team chooses immediate replacement because the gift is needed for a dated recognition event. That decision improves timeliness but leaves the company temporarily exposed to the replacement cost and any denied recovery. The reason and approver belong in the record.
If the carrier later pays part of the accepted value, finance records approved and received amounts without subtracting them from the parcel denominator. If the carrier denies the claim, the recipient outcome can still be accepted while the financial case closes as a write-off. Failure evidence includes a replacement counted as a second damage incident, a claim linked to S-1001-R1 without explanation, or a full carrier payment repeated across multiple rows. Recovery is to restore the original relationship, reverse duplicated entries, reconcile the remittance, and rerun QA. Acceptance requires one original incident, one linked replacement, one valid claim key, balanced recovery allocation, and separate operational and financial closure.
Operate a weekly and monthly review cadence
Daily or weekly triage should focus on open evidence, recipient action, carrier deadlines, and safety issues. Sort by next action date rather than by carrier name. Escalate missing original packaging, expiring notice windows, incomplete commercial documents, unclear shipper-of-record status, and high-value cases with no named decision owner.
The monthly quality review uses the Rates sheet only after Scenario and QA pass. Break results down by a defensible operational dimension such as packing method, fulfillment site, carrier service, product family, or destination region, but suppress segments too small to interpret safely. Look for repeatable failure modes that packaging tests or supplier corrections can address. Do not turn a handful of incidents into a broad vendor ranking.
Quarterly governance should review access, retention, taxonomy changes, version history, and unresolved financial cases. Remove recipient data that no longer has an approved purpose while preserving de-identified operational evidence where policy permits. The global gifting operations hub helps place this control in the larger operating model, while the recipient data operations hub covers data-minimization and access questions.
Conclusion: make every percentage traceable to a case
A useful parcel-damage program does not begin with a target percentage. It begins with a stable shipment key, a declared record grain, a controlled taxonomy, verifiable evidence references, separate service and financial outcomes, an inclusive reporting window, and tests that expose duplicates, replacements, empty periods, and orphan claims. When those controls pass, the rate can support packaging changes and accountability. When they fail, the correct output is a blocked report with a repair owner, not a polished number.
For teams coordinating corporate gifting across several fulfillment partners, Giftpack can provide commercial workflow context alongside this independent QA workbook. Giftpack does not replace carrier terms, legal or tax advice, privacy decisions, payroll decisions, or an employer’s approval responsibilities; those remain with the relevant owners and advisers.