Position calibration field dataset
1. Dataset Name
position_x_calibration_fields
2. Description
Position-level dataset that unpivots each position's five calibration attributes (Location, Job Titles, Skills, Work Experience, Ideal Candidate) into one row per position per calibration field, so each position appears exactly five times. Each row carries the position's core attributes (recruiter, hiring manager, business unit, location, seniority, job function, status) plus a single calibration_field label and its corresponding is_field_calibrated flag. Recruiter and hiring manager emails are enriched with display names via a join to the employee directory. Designed to power field-level calibration adoption analysis — i.e. answering not just "is this position calibrated" but "which specific calibration criteria are being completed vs. skipped."
3. Source Tables
- customer_views.positions
- customer_views.employee (joined twice — once to resolve recruiter display name, once for hiring manager)
4. Grain
One row per position_id × calibration_field. Since calibration_field is introduced via a CROSS JOIN against a constant 5-row list (Location, Job Titles, Skills, Work Experience, Ideal Candidate), every position that survives the base filters appears in exactly five rows — one per calibration criterion — with is_field_calibrated indicating whether that specific criterion is met. Consumers computing position counts must use COUNT(DISTINCT position_id), not COUNT(*), or filter to a single calibration_field first.
5. Dashboards Supported
- Calibration Dashboard
6. Metrics Supported
| Metric | Supported Breakdowns |
| Calibrated Positions % by Calibration Criteria | Job function, hiring manager, business unit, recruiter, location country — plus native breakdown by calibration_field (Location, Job Titles, Skills, Work Experience, Ideal Candidate). Scoped to open positions only (is_open_position = 1). |
7. Known Exclusions / Filters Applied
- Positions with system_id = 'sourcing' are excluded, unless system_id is null.
- Positions with status = 'Sourcing Pipeline' are excluded, unless status is null.
- Recruiter and hiring manager fields fall back to the raw email address when no matching employee record is found in customer_views.employee (i.e. the LEFT JOIN doesn't resolve).
- calibration_field is a fixed, hardcoded list of exactly 5 values (Location, Job Titles, Skills, Work Experience, Ideal Candidate) — any future calibration criteria added upstream will not appear here until this CROSS JOIN list is updated.
- Because this dataset is one-row-per-position-per-field, it is not the right source for position-level metrics that don't need a field breakdown (e.g. overall Calibrated Positions %) — use application_x_positions_sr's is_calibrated / calibration_pct fields for that instead, to avoid the 5x row inflation.
8. Schema
| Column Name | Data Type | Notes |
| position_id | bigint | Grain key — one row per position_id per calibration_field (5 rows per position). |
| group_id | varchar | Tenant/customer scoping. |
| position_status | varchar | Raw position status, taken from the source position record. |
| is_open_position | bigint | Flag (0/1); derived from pos.is_open = TRUE. |
| creation_ts | datetime | Position creation timestamp. |
| closed_at | datetime | Position close timestamp; null if still open. |
| recruiter_email | varchar | Raw recruiter email from the position record. |
| recruiter | varchar | Derived: "First Last (email-prefix)" when the email matches an employee record; falls back to recruiter_email if no match. |
| hiring_manager_email | varchar | Raw hiring manager email from the position record. |
| hiring_manager | varchar | Derived: same name-resolution pattern as recruiter, keyed off hiring_manager_email. |
| job_function | varchar | Position's job function/category. |
| business_unit | varchar | Position's business unit. |
| location_country | varchar | Position location, country. |
| location | varchar | Position location, full string (city, etc.). |
| site | varchar | Derived: location_country + city portion of location, concatenated. |
| seniority_level | varchar | Position's seniority level. |
| ats_job_id | varchar | External ATS job identifier for the position. |
| calibration_status | varchar | Raw calibration status field from the source position record. |
| calibration_field | varchar | Derived: one of 5 unpivoted values — Location, Job Titles, Skills, Work Experience, Ideal Candidate — introduced via a CROSS JOIN against a 5-row constant list. |
| is_field_calibrated | bigint | Derived: 0/1 flag for whether the specific calibration_field on this row is marked complete for the position (mapped from has_calibrated_location / _titles / _skills / _workexp / _similar_cand). |