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.
JobsPipe
Google Sheets
GmailHR & PeopleOperationsData SyncEmail AutomationOn 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.
Additional information
What does this prompt do?
- 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 do I need to use this?
- 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.
How can I customize it?
- 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.
FAQs
Why are the salary figures in US dollars when I track roles in other countries?
What does "insufficient data" mean when it shows up in my sheet?
How many open roles can I track in one sheet?
Will this overwrite notes or columns I have added myself?
What if a role has no pay data behind it at all?
Related templates
Every weekday, find every unpaid JobNimbus invoice, email each customer one reminder that gets firmer as it ages, and post a receivables summary to Slack.
Every weekday at 7am, your active courses are checked for missing work, silent logins, and slipping grades, with a ranked list sent to your advising channel.
Every Monday at 8am, see exactly which assignments have work waiting, sorted worst first, posted to Slack and logged to a spreadsheet.
Every weekday morning your advising team gets a ranked list of students slipping behind in each course, plus a tracking log that shows whether outreach worked.
Every evening we find the jobs you just finished, email each customer a personal review request, and log it on the job so nothing slips.
Every weekday morning, review your unpaid ERPNext invoices, email each customer a reminder that gets firmer as the debt ages, and brief finance in Slack.
Stop taking roles to market with stale salary bands.
Set this up once and your compensation benchmarks refresh themselves on the first of every month, with the People team told what moved.