Your location join is copying salaries across cities

You expand a job into city rows, and every office suddenly appears to share a salary range. The dashboard looks consistent. That's the uncomfortable part.

A posting's selected salary range isn't a salary observation for every listed location.

The Actor chooses the range matching the primary location when applicable. Otherwise, it uses the posting's typical/base range. Flattening the location list copies that selection without adding evidence about another office.

Why that salary stays with the posting

Employers can publish several geographic pay bands inside a single job description. The Actor exposes a selected range through scalar compensation fields, alongside the sentence it came from.

That gives you something useful for a posting record. It doesn't give you a location-to-salary mapping.

Start by fetching details, because listing-only records don't provide the location and description enrichment this workflow needs.

Example input for a careers site, followed by retrieval:

import { ApifyClient } from 'apify-client'; const client = new ApifyClient({ token: process.env.APIFY_TOKEN, }); const run_input = { mode: 'tenant', tenant: 'workday', site: 'Workday', scrapeDetails: true, maxItems: 50, }; const run = await client .actor('nimbryxo-studio/workday-careers-site-job-listings-extractor') .call(run_input); if (run.status !== 'SUCCEEDED') { throw new Error(`Run ended with ${run.status}`); } const { items } = await client .dataset(run.defaultDatasetId) .listItems(); 

The transformation below processes those retrieved items. Retrieval completeness remains a separate concern.

Underneath, the Actor pages through search results and removes duplicates across pages and URLs. It splits capped searches by job family, then a small secondary facet when needed.

Those strategies address collection coverage. They don't change what the selected salary represents.

Keep availability and compensation apart

Your location bridge answers: “Where can someone take this role?”

Your compensation observation answers: “Which published range was selected for this posting?”

Those questions need different records. Keep the boundary visible:

posting ├── location availability → location labels └── selected compensation → range, source sentence, primary context 

For sales territory planning, the availability rows can help identify hiring activity near an account's offices.

For a city compensation comparison, those same rows are insufficient. Joining them to the selected range creates geographic attribution through SQL syntax. Geography rarely accepts syntax as evidence.

Keep currency and compensation frequency attached, too. A range without its unit can't support a meaningful comparison.

Validate without inventing geography

Validate the scalar fields you intend to calculate with. Preserve unfamiliar location structures rather than guessing their nested keys.

This schema accepts optional compensation fields and retains the rest of the record:

import { z } from 'zod'; const jobSchema = z.object({ jobPostingId: z.string().optional(), requisitionId: z.string().optional(), externalPath: z.string().optional(), url: z.string().optional(), location: z.string().optional(), primaryLocation: z.unknown().optional(), locations: z.array(z.unknown()).optional(), additionalLocations: z.array(z.unknown()).optional(), compensationRangeMin: z.number().finite().optional(), compensationRangeMax: z.number().finite().optional(), compensationCurrency: z.string().optional(), compensationFrequency: z.string().optional(), compensationText: z.string().optional(), compensationType: z.string().optional(), }).passthrough(); 

Missing fields are omitted by the Actor. Your consumer should preserve that absence rather than converting it into zero pay.

The location mapper below handles string labels from location and additionalLocations. Structured entries stay in the original payload and trigger a mapping flag.

That flag describes your mapper's limits. It doesn't certify that the source listed every possible workplace.

Split the record before you split the rows

Create location availability independently from the selected compensation.

The attribution policy below is a consumer decision. It deliberately makes no claim that the Actor supplied a verified geographic attribution flag.

This transformation also rejects duplicate consumer keys within the batch:

const seenKeys = new Set<string>(); const postings = items.map((item) => { const job = jobSchema.parse(item); const identity = [ ['jobPostingId', job.jobPostingId], ['requisitionId', job.requisitionId], ['externalPath', job.externalPath], ['url', job.url], ].find(([, value]) => Boolean(value)); if (!identity) { throw new Error('Posting has no usable identity'); } const posting_key = JSON.stringify([ run_input.tenant, run_input.site, ...identity, ]); if (seenKeys.has(posting_key)) { throw new Error(`Duplicate consumer key: ${posting_key}`); } seenKeys.add(posting_key); const primaryLabel = job.location?.trim(); const additional = job.additionalLocations ?? []; const additionalLabels = additional .filter((value): value is string => typeof value === 'string') .map((value) => value.trim()) .filter(Boolean); const selectedPay = Object.fromEntries( Object.entries({ compensationRangeMin: job.compensationRangeMin, compensationRangeMax: job.compensationRangeMax, compensationCurrency: job.compensationCurrency, compensationFrequency: job.compensationFrequency, compensationText: job.compensationText, compensationType: job.compensationType, }).filter(([, value]) => value !== undefined), ); return { posting_key, raw: item, primary_location_context: job.primaryLocation ?? null, location_labels: [...new Set([ ...(primaryLabel ? [primaryLabel] : []), ...additionalLabels, ])], needs_location_mapping: !primaryLabel || additional.some( (value) => typeof value !== 'string' || !value.trim(), ), selected_pay: Object.keys(selectedPay).length ? selectedPay : null, attribution_policy: 'posting_selected_unverified_geography', }; }); 

Exact label deduplication prevents repeated labels within a posting. It doesn't resolve spelling variants or prove that matching labels describe the same place across employers.

Identity fallbacks deserve similar care. If the preferred identifier becomes available later, this key can change. Keep the identity policy stable for a source, or reconcile that transition explicitly.

For geographic pay attribution, retain compensationText and inspect its wording against the primary-location context. A populated primary location alone can't distinguish a geographic match from the base-range fallback.

We'd leave the geographic claim unresolved until that evidence is checked.

Replace snapshots in a batch

A small ingestion service can store each transformed snapshot as JSONB and expose separate views for availability and compensation.

Replacing the snapshot also replaces its derived locations and pay. That avoids stale child rows when a later record omits a previously published range.

This creates the storage boundary and writes the whole batch with a single upsert:

import pg from 'pg'; const pool = new pg.Pool(); try { await pool.query(` CREATE TABLE IF NOT EXISTS postings ( posting_key text PRIMARY KEY, body jsonb NOT NULL ); CREATE OR REPLACE VIEW posting_locations AS SELECT p.posting_key, l.location_label FROM postings AS p CROSS JOIN LATERAL jsonb_array_elements_text( p.body->'location_labels' ) AS l(location_label); CREATE OR REPLACE VIEW compensation_observations AS SELECT posting_key, body->'primary_location_context' AS primary_location_context, body->>'attribution_policy' AS attribution_policy, body->'selected_pay'->>'compensationText' AS source_text, body->'selected_pay'->>'compensationCurrency' AS currency, body->'selected_pay'->>'compensationFrequency' AS frequency, (body->'selected_pay'->>'compensationRangeMin')::numeric AS range_min, (body->'selected_pay'->>'compensationRangeMax')::numeric AS range_max FROM postings WHERE body->'selected_pay' <> 'null'::jsonb; `); await pool.query(` INSERT INTO postings (posting_key, body) SELECT value->>'posting_key', value FROM jsonb_array_elements($1::jsonb) ON CONFLICT (posting_key) DO UPDATE SET body = EXCLUDED.body `, [JSON.stringify(postings)]); } finally { await pool.end(); } 

This stores current snapshots, not salary history. Serialize ingestion for each source if an older run could otherwise overwrite a newer snapshot.

It also doesn't delete postings absent from the batch. Absence from a capped or partial collection is insufficient evidence that a role closed.

In n8n, keep these derived structures separate before any item-splitting node. Once compensation travels inside each location item, downstream workflows can mistake copied values for independent observations.

Which average are you actually calculating?

The following queries make the difference visible. Both require complete, ordered bounds and explicit currency and frequency.

The first assigns selected ranges to availability labels. The second preserves posting grain:

-- Availability-weighted selected pay, grouped by location label. SELECT l.location_label, c.currency, c.frequency, count(*) AS joined_rows, avg(c.range_min) AS mean_selected_minimum FROM posting_locations AS l JOIN compensation_observations AS c USING (posting_key) WHERE c.range_min IS NOT NULL AND c.range_max IS NOT NULL AND c.range_min <= c.range_max AND c.currency IS NOT NULL AND c.frequency IS NOT NULL GROUP BY l.location_label, c.currency, c.frequency; -- Posting-weighted selected pay across the ingested portfolio. SELECT currency, frequency, count(*) AS postings_with_ranges, avg(range_min) AS mean_selected_minimum FROM compensation_observations WHERE range_min IS NOT NULL AND range_max IS NOT NULL AND range_min <= range_max AND currency IS NOT NULL AND frequency IS NOT NULL GROUP BY currency, frequency; 

The first can describe selected pay attached to jobs available in a location. Labeling it “local salary” would overstate the evidence.

The second describes published selected minima across postings. It still isn't average employee pay, an offered salary, or a representative market estimate.

If you aggregate the joined rows across locations, postings with more location labels receive more weight. Even a company-wide chart can therefore change because of location fanout.

What broke was the attribution

The worked example here's Workday Careers Site Job Listings Extractor. The reusable pattern applies whenever a record contains a repeated dimension beside a selected scalar measurement.

The failure is a consumer assumption crossing a boundary:

There's another coverage limit: a search slice can remain capped after splitting. Narrowing the search can help reach those roles, but a successful run status alone doesn't establish completeness.

Your geographic attribution policy and your collection-coverage policy answer different questions. Neither should quietly substitute for the other.

Before the dashboard refreshes

• Check unresolved location mappings before using office counts.
• Keep the selected range, source sentence, units, and primary context together.
• Require a separate evidence check before publishing geographic pay claims.
• Name each metric's grain and denominator in the dashboard.
• Compare coverage by employer before interpreting differences.

An office can be hiring without having a salary observation.