# Monthly salary benchmark refresh for your open roles

> Every month, pull fresh advertised pay ranges for every open role in your hiring sheet and email the People team what changed.

- Workflow type: code
- Services: JobsPipe, Google Sheets, Gmail
- Categories: HR & People, Operations
- Published: 2026-08-04

## What it does

- Reads every open role from your hiring spreadsheet on the first of each month, including the job title, country and seniority level you track for each one.
- Looks up fresh advertised pay data for each role and writes the low, midpoint and high figures back into the matching row, along with the date it was refreshed.
- Marks a role as "insufficient data" when there are too few live postings to produce a reliable range, instead of leaving a blank cell that reads like a zero.
- Emails your People team a short recap naming the roles whose pay moved most since the previous run.

## What you'll need

- A Google account with edit access to the spreadsheet where you track open roles.
- A JobsPipe account for the salary data. The free plan is enough to try this out, though a paid plan suits teams tracking a longer list of roles.
- A spreadsheet laid out with one row per open role and columns for job title, country and seniority level.
- Optionally, a standard occupation code per role if your team already classifies jobs that way. Rows without one still work.
- A Gmail account to send the monthly recap from, plus the People team address that should receive it.

## Prompt

On the first day of every month, refresh the advertised salary benchmarks for all of our open roles so that we never take a role to market with a stale band. Run this on a monthly cron trigger set to the 1st.

Start by reading our open roles from Google Sheets with Get Values over the tracked range. Each row holds a job title, a country, a seniority level, and optionally an ISCO occupation code. Keep track of which sheet row each record came from so that every result can be written back to the row it belongs to.

Before overwriting anything, capture the midpoint (p50) figure already sitting in each row from the previous run. That is what we compare against to work out movement.

For every row, call JobsPipe Get Salary Benchmark using the job title, country and seniority level from that row. Where a row also supplies an occupation code, additionally call JobsPipe Get Occupation Compensation Percentiles for that code. Note that JobsPipe matches 4-digit ISCO-08 codes exactly and treats 1-3 digit codes as hierarchy prefixes, so a shorter code returns a broader occupation family rather than an error.

Collect the p10, p50 and p90 advertised salary figures plus the date of this refresh, and write them back into the matching row using Google Sheets Batch Update Values. Batch every row update into a single write rather than issuing one write per row.

JobsPipe suppresses a result when a group has fewer than 30 postings, returning suppressed: true with null percentiles. When that happens, write "insufficient data" into the p10, p50 and p90 cells for that row instead of leaving them blank. A blank cell is too easily misread as a zero or as a row that failed to refresh, and either reading is misleading.

Make the sheet header row state clearly that JobsPipe compensation percentiles are always reported in USD regardless of the country filter applied, so that a country-filtered row is never mistaken for local currency.

Finish by sending the People team a short summary with Gmail Send a Message. List the roles whose midpoint moved most since the previous run, showing the old figure, the new figure and the direction of the change, and call out any roles that came back as insufficient data this month.

Two practical constraints to respect. Every JobsPipe request costs one credit against the monthly quota, including requests that return zero results, so keep the tracked sheet to a sensible number of roles and do not retry blindly. Pair any retry with an Idempotency-Key header so that it cannot duplicate billed work. Also expect genuine gaps in coverage, because only about a quarter of postings state pay at all.

## How to customize

- Change the timing. Monthly on the 1st is the default, but quarterly works well if your bands tend to move slowly.
- Change who receives the recap, or point it at a shared People team inbox instead of named individuals.
- Adjust which figures get written back, for example keeping only the midpoint if a full range is more detail than your hiring managers need.

## FAQ

### Why are the salary figures in US dollars when I track roles in other countries?

JobsPipe always reports its compensation percentiles in US dollars, even when the lookup is filtered to a specific country. The country filter changes which postings are counted, not the currency they are reported in. This workflow puts a note in your sheet header so nobody mistakes a UK or German row for local currency, and you can add your own conversion column alongside if your team prefers to review bands in local terms.

### What does "insufficient data" mean when it shows up in my sheet?

It means there were fewer than 30 live postings behind that particular combination of title, country and seniority, so the source withholds a figure rather than publishing an unreliable one. The workflow writes those words into the row on purpose. A blank cell is easy to misread as a zero or as a row that simply failed to update, and either mistake could send a role to market with the wrong band.

### How many open roles can I track in one sheet?

Every lookup counts against your monthly JobsPipe allowance, including lookups that come back empty, so it is worth keeping the sheet to the roles you are genuinely hiring for rather than every job description you have ever written. A few dozen roles is comfortable on a paid plan. If your list is long, consider running the refresh quarterly instead of monthly.

### Will this overwrite notes or columns I have added myself?

No. It only writes to the specific benchmark columns and the refresh date column in each role's row. Any other columns you keep, such as hiring manager, req number or internal band, are left untouched, and rows stay in place so your own formulas and filters keep working.

### What if a role has no pay data behind it at all?

Only around a quarter of job postings state pay publicly, so some niche or senior roles will always have thinner data than a common role in a large market. Those roles will show as insufficient data, which is itself useful signal that you are pricing into a thin market and may want a manual comparison rather than an automated band.

Use this prompt in General Input: https://www.generalinput.com/prompts/monthly-salary-benchmark-refresh-for-your-open-roles