Case Study · Retail

Defacto Norm Staffing: calculating the staff each store should have, from data

A model that takes the store staffing decision away from actual payroll and subjective requests and grounds it in measured workload and an efficient peer in the same segment. It produces a 2025 norm, a 2026 target and an increase / reduce / keep decision for every store × role. It runs entirely on BigQuery and is fully parametric.

Store × role
Decision unit: Frontier
Reference: Gradual
Approach: Increase · reduce · keep
Output

Problem

How many people does a store need? In most retailers the answer is a continuation of the past: however many there were last year, plus or minus a little. The staffing request comes from the store manager, the budget comes down from above, and the two meet somewhere in the middle. Nobody can show with a number which store is genuinely short-staffed and which is running with too many.

Defacto wanted to base the staffing decision on data: the staff each store should have for each role, calculated from the volume of work that store does and from the most efficient peer store in the same segment; and from that result, an increase / reduce / keep decision.

The difficulty lies in defining “same segment” and “efficient” correctly. Comparing a high-revenue mall store with a small high-street store is meaningless; taking the store with the fewest people as the benchmark would reward understaffing.

The effort layer: how many hours is the workload?

The model starts from the store operations effort standard: a unit time for every task item. Sales and returns, goods receipt, unpacking, tagging, transfers, stock counts, till closing, label changes, membership sign-ups, omnichannel tasks (ship-from-store, click and collect), visual merchandising, training, management… Each task’s annual hours = volume × unit time.

Those hours are distributed to stores by driver share: transaction count, inbound waybills, labels printed, till closings, membership sign-ups and so on. Each task is tied to the driver that best represents its volume. Tasks not tied to volume are distributed either equally per store (management, daily meeting, visual merchandising) or by operational share. Every task is split into Sales, Stockroom and Management by role weights; for mixed tasks (unpacking, stock counts, transfers) the weights were set together with store operations.

The effort layer has its own validation: how closely does the model’s effort track actual headcount? This “quality gate” keeps the model from moving to the next step until the effort layer holds up.

Segmentation

Stores are clustered by hierarchical dimensions: revenue › velocity segment › m² › store type › location › floors. The narrowest cluster consists of stores sharing all six dimensions. If a cluster has too few stores, the weakest dimension is dropped and the search moves to a wider cluster. Revenue is never dropped. This way every store gets a peer group large enough to be statistically meaningful.

Frontier: a reference with two gates

Each cluster’s reference store passes two filters:

Performance gate: budget / target attainment must be within the segment’s band. A structurally troubled store, or one that looks “lean” because it is understaffed, cannot be the reference.
Efficiency gate: among those candidates, the store working with the fewest FTE per unit of revenue.

What is taken from the frontier is its efficiency ratio (FTE/revenue), which is then multiplied by each store’s own revenue. Without this scaling, low-revenue stores would be forced to carry a large store’s staffing; scaling only kicks in when the revenue gap widens.

Convergence

Equating every store directly to the frontier is too aggressive. Instead, each store is moved gradually toward its scaled reference: only part of the gap between current staffing and the reference is closed. The result is an achievable 2025 norm per store × role.

Edge cases have their own rules. No norm is proposed for a role the store does not have at all; that work is assumed to be handled centrally. Room-sized stores stay out of the frontier pool. Stores without a full year are annualised by monthly run-rate and flagged for manual review.

2026: the multiplier

There is no re-convergence for 2026. The ratio between “raw effort” and “converged norm” in 2025 is stored as an efficiency multiplier for each store × role; the 2026 workload forecast is converted to effort and multiplied by it. The norm carries the efficiency level; effort carries the year-on-year change in the work. The result is compared store by store with the company’s 2026 hours budget: where the budget is below the reference, “aggressive / understaffing risk”; where above, “room to spare”.

Data decisions

The model’s reliability rests on a few data decisions. Because time-and-attendance data for 2025 had incomplete coverage and excluded support staff, payroll was taken as authoritative for actual hours. Stores showing zero stockroom staff were found to have stockroom work booked under sales in payroll and were corrected using the fleet ratio. 2026 drivers that had undergone process or coverage changes (footfall, put-away, outbound waybills) were left out of the model, with reliable drivers used as proxies instead. For label data, two sources were compared and one chosen.

Parametric structure and version discipline

The whole model was built on BigQuery as step-by-step SQL, driven by parameters from a single config: monthly net hours (the FTE divisor), the convergence coefficient, the performance-band scenario, frontier scaling on/off, the cluster threshold, the segment hierarchy. Improvement requests that came after presentations (a change of divisor, role mappings, removal of calibration, a budget gate on the frontier) were applied as parameter changes, and every version was frozen as a checkpoint. When alternatives such as a “direct frontier” came up, we put two outputs from the same data side by side instead of debating them.

The output package: one master table per store with segment, cluster, frontier, budget attainment, 2025 (actual / model / frontier) and 2026 (norm / budget / proposal) FTE, plus combined, proposal and segment-detail Excel files for management, and methodology and data-dictionary documents.

Status

Phase 1 was presented to management; eight improvements were applied afterwards and Phase 2 is under way. The HR analytics roadmap defines two continuations of the model: an adaptation for international stores, calibrated per country, and a cold-start staffing estimate for stores not yet opened. The norm gives what is “required”; the surplus/shortfall analysis against actual staffing is a separate piece of work.

This project is the backbone of the HR analytics engagement with Defacto.

How the model was built

Objective	Direction	Tension
Required staff (FTE) per store × role	↓	While moving toward the efficient peer, a store that misses its target cannot be the reference; leanness caused by understaffing does not count
Reference efficiency (FTE / revenue)	↓	The narrower the cluster, the fewer the peers; without enough peers the search moves to a wider segment

Constraints

Performance gate: a store whose budget / target attainment is outside the segment's band cannot be the frontier
If a cluster is too small, the hierarchy moves up one level (revenue is the most protected dimension)
The frontier is an efficiency ratio, not a headcount; it is scaled by each store's own revenue
Gradual convergence: part of the gap is closed, so the target stays achievable
No norm is proposed for a role the store does not have (the work is assumed to be handled centrally)
Very small "room" stores stay out of the frontier pool and follow a separate rule
Stores without a full year are annualised by run-rate and flagged for manual review
No re-convergence for 2026; 2025's efficiency level is carried forward with a multiplier

All our work