Compensation Benchmarking Template: How to Build One From Real Wage Data

The Friday Deadline That Exposes Every Weak Spreadsheet

Your CFO wants a number by Friday. A long-tenured operations coordinator just asked for a raise, citing a job posting from a company three towns over that listed a range $15,000 higher than what you're paying. You don't have a dedicated compensation analyst — you have a spreadsheet someone built two years ago, a few screenshots from a crowd-sourced salary site, and a sinking feeling that none of it would hold up if your CFO, or a state labor investigator, asked where the number actually came from.

This is the moment a real benchmarking process earns its keep: not as a nice-to-have HR artifact, but as the documented, defensible answer to "how did you arrive at this pay rate?" The problem is that most templates people inherit or download are just formatted spreadsheets with empty columns waiting for someone to type in a number from memory or a job board. A template built on published government wage tables is a different thing entirely — every figure in it traces back to a named source and a dated vintage, which means it survives scrutiny from a CFO, a board member, or a pay-transparency auditor. Here's how to build one, row by row, from the two public wage datasets that actually hold up: the U.S. Bureau of Labor Statistics' Occupational Employment and Wage Statistics (OEWS) program and Statistics Canada's National Occupational Classification (NOC) wage tables.

What a Compensation Benchmarking Template Actually Needs to Contain

A compensation benchmarking template is not a list of job titles with a single salary number next to each one. At minimum, each row needs: the job title as your organization uses it, the matched occupation code (SOC for U.S. roles, NOC for Canadian roles), the data source, the vintage or as-of date of that data, the geographic scope the figures apply to (national, state/provincial, or metro), and the wage distribution itself — not just a median, but the 10th, 25th, 50th, 75th, and 90th percentiles where available. A notes column matters more than most people expect: this is where you record why a role was matched to a particular code when the title didn't line up cleanly, which is common.

The vintage column is not decoration. BLS OEWS is published annually with a May reference date, and Statistics Canada's Employee Wages by Occupation dataset is also updated annually under the Open Government Licence – Canada. A benchmarking template that doesn't record which year's data populated each row will quietly go stale, and nobody reviewing it a year later will know whether the numbers are current or two refresh cycles old.

Where to Source Defensible Wage Data: BLS OEWS and Statistics Canada NOC

For U.S. roles, BLS OEWS covers approximately 830 detailed occupations, drawn from a rolling sample of about 1.1 million establishments over a three-year period. It classifies occupations using 22 of the 23 major groups in the 2018 Standard Occupational Classification system (military occupations, major group 55, are excluded), and it reports wages at the national level, for every state and D.C., for U.S. territories, and for roughly 530 metropolitan and nonmetropolitan areas. For the six combined semiannual panels behind the most recent release, BLS reports a national response rate of 65.7% by establishment and 65.9% by weighted employment, covering roughly 55% of total national employment — worth knowing if anyone asks how representative the underlying survey actually is. For a full walkthrough of how to pull and interpret these tables, see how to read BLS OEWS data.

For Canadian roles, the NOC system is considerably larger in scope: it comprises more than 40,000 job titles organized into 516 unit groups across six TEER (Training, Education, Experience, Responsibilities) categories. Canada's Job Bank determines annual wages for each of the 516 NOC occupations at national, provincial, territorial, and economic-region levels, drawing primarily on Statistics Canada's Labour Force Survey. Both datasets share one structural advantage worth building your template around: both BLS OEWS and Statistics Canada NOC data are public-domain or Open Government Licence releases, meaning there is no licensing cost to pull and use them.

Matching Your Role to the Right Occupation Code

The step people skip, and the one that breaks a benchmarking template fastest, is forcing a job title to match a code based on wording alone. "Customer Success Manager" does not automatically belong under the same SOC code as "Customer Service Representative" even though the titles sound related — the actual duties, decision-making scope, and reporting structure determine the match, not the label on the org chart. Take the time to read the code's full occupational definition before assigning it to a role, and record that reasoning in your notes column so a future reviewer isn't left guessing.

A few concrete SOC codes illustrate how differently similarly-named roles can land: Network and Computer Systems Administrators (SOC 15-1244) carry a median annual wage of $99,130 (BLS, May 2025); Human Resources Managers (SOC 11-3121) carry a median of $149,280 (BLS, May 2025); Software Developers (SOC 15-1252) carry a median of $132,270 (BLS, May 2023); and Customer Service Representatives (SOC 43-4051) carry a median of $39,680 (BLS, May 2023). Four different codes, four very different wage profiles — which is exactly why the code match, not the job title, has to drive the benchmark.

Building the Percentile Columns: A Worked Example

A single median figure tells you almost nothing about where to actually set a pay rate. The percentile distribution is what turns a benchmark into something usable. Take Customer Service Representatives (SOC 43-4051), per BLS, May 2023: the 10th percentile is $29,560, the 25th percentile is $34,780, the median (50th) is $39,680, the 75th percentile is $48,480, and the 90th percentile is $61,250.

Notice the shape: the gap between the 10th and 25th percentile ($5,220) is much narrower than the gap between the 75th and 90th percentile ($12,770). Wage distributions are rarely symmetrical, and a benchmarking template that only records the median hides that entirely. If you're deciding where a newly-hired representative with no prior experience should land versus a representative who has handled escalations for six years, the percentile columns — not the median alone — are what let you make that call defensibly. For a deeper walkthrough of what each percentile actually represents statistically, see percentile wages explained.

From Benchmark to Range: Turning One Data Point Into Three

A compensation benchmarking template gives you the external data point. A usable salary range is a separate step: taking that benchmark and applying a spread to produce a floor, a midpoint, and a ceiling. As a purely illustrative worked example — not a real wage claim — imagine anchoring a range to the Customer Service Representative median of $39,680 (BLS, May 2023) with a ±15% spread: that would produce a floor around $33,728 and a ceiling around $45,632, with $39,680 sitting at the midpoint. The actual spread percentage you choose should depend on the job family, how much room you want for tenure- and performance-based progression within the band, and how your organization handles promotions versus lateral moves — range spread by job family walks through how those defaults typically differ across functions.

Once a range exists, you can track where an actual employee sits within it using a compa-ratio: current salary divided by the range midpoint, multiplied by 100. A compa-ratio of 100 means the employee is paid exactly at the midpoint; below 100 means they're paid below it. For the full process of turning a benchmark into a complete, documented range — not just the arithmetic — see how to build a salary range.

Common Mistakes That Make a Benchmarking Spreadsheet Fall Apart

A few patterns show up repeatedly in benchmarking spreadsheets that don't survive a second look. The first is blending vintages — pulling a 2023 figure for one role and a 2025 figure for another without noting the difference, which makes the whole sheet internally inconsistent. The HR Managers (SOC 11-3121) median is a useful cautionary example here: it was $140,030 in May 2024 and $149,280 in May 2025, a real year-over-year shift that a template without vintage tracking would simply erase.

The second mistake is ignoring geography — applying a national median to a role based in a high-cost metro area, or vice versa, without noting that the figure is a national rather than a local estimate. The third is treating the median as the whole picture, which the percentile discussion above already addresses. The fourth, and probably the most common, is simply not recording the source at all — a number with no citation is a number nobody can defend six months later when someone asks where it came from.

Keeping the Template Current as Data Refreshes

Because BLS OEWS is published annually with a May reference date, and Statistics Canada's wage tables are likewise updated annually, a compensation benchmarking template is not a document you build once and file away. It needs a refresh cadence — realistically, an annual pull at minimum, timed to whenever the new OEWS or NOC release lands, with the old vintage archived rather than overwritten so you can show how a range moved and why.

If building and re-populating that structure by hand each year isn't where you want to spend your time, the Compensation Benchmarking Spreadsheet is a downloadable, structured template built around exactly the columns described above — source, vintage, geography, and full percentile distribution — designed to pair with a SalaryRange account rather than replace the manual pull entirely. If you're also building ranges across multiple roles, the SalaryRange app separately seeds occupation search directly from local BLS OEWS tables and Statistics Canada NOC tables, so the underlying wage data stays attached to the role rather than living in a separate spreadsheet you have to reconcile by hand; details on tiers are on the pricing page.