Monthly salary benchmark refresh for your open roles

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.