Application positions
1. Dataset Name
application_x_positions_sr
2. Description
Application-level dataset that joins every application to its parent position, enriched with calibration status/percentage, position-aging buckets, and (as of the latest query revision) field-level calibration detail, resolved recruiter/hiring manager display names, calibration milestone timestamps, and candidate diversity fields. Each row represents a single application, with position attributes (recruiter, hiring manager, business unit, location, calibration state, etc.) denormalized onto it. Designed to be the primary source for recruitment funnel, time-to-fill/hire, match-score, and calibration-health analyses.
3. Source Tables
- customer_views.positions
- customer_views.applications
- customer_views.employee (joined twice — once to resolve recruiter display name, once for hiring manager)
- customer_views.user_analytics (joined twice — once for the calibration_80_percent_complete event, once for calibration_100_percent_complete)
4. Grain
One row per position_id × application_id. Since positions is the LEFT JOIN anchor, a position with zero applications still appears — once, with all application-level fields (application_id, application_ts, hired_ts, match_stars, profile_id, etc.) null. A position with multiple applications appears once per application.
5. Dashboards Supported
- Recruitment Effectiveness
- TA Feature – Usage and Outcome
- Calibration Dashboard
- Source Analytics Dashboard
6. Metrics Supported
| Metric | Supported Breakdowns |
| Time to Fill | Business unit, recruiter, location, job function, seniority level, calibration status |
| Time to Hire | Hire-time bucket (<7, 7–30, 31–60, >60 days), recruiter, business unit, location, source category |
| Match Score | Position, source category, recruiter |
| Filled Positions | Business unit, recruiter, location, job function |
| Hired (count/rate) | Source category, internal vs. external, referral, recruiter, business unit, calibration status |
| Recruitment Funnel | source category, internal mobility, referral, business unit |
| Open Positions | Recruiter, business unit, location, position age bucket, calibration status |
| Time to Calibrate Position | Recruiter, business unit — via creation_ts vs. position_calibration_80_complete_ts / position_calibration_100_complete_ts |
| Diversity Breakdown — Applications/Hires | Recruiter, business unit — via ef_race, ef_gender |
7. Known Exclusions / Filters Applied
- Applications with system_id = 'sourcing' are excluded.
- Positions with status = 'Sourcing Pipeline' are excluded.
- A hardcoded list of 5 position IDs is explicitly flagged via is_excluded_position (not dropped, but flaggable for exclusion downstream).
- source_category is derived through a long waterfall of CASE WHEN rules (referral, internal mobility, job boards, social, campus recruiting, etc.) — consumers building source-based breakdowns should treat source_category as the canonical field rather than raw source_type/source_name/utm_source.
- Calibration is considered "complete" only when ≥4 of 5 calibration flags (location, skills, titles, similar candidate, work experience) are true.
- Recruiter and hiring_manager are resolved to display names via a join to customer_views.employee; when no matching employee record is found, they fall back to the raw email (recruiter_email / hiring_manager_email).
- position_calibration_80_complete_ts and position_calibration_100_complete_ts are sourced from customer_views.user_analytics calibration events and will be null for positions where that milestone was never reached, or where event tracking predates this feature's rollout.
- fields_calibrated and the has_calibrated_* flags give field-level detail (which of the 5 calibration attributes are set); calibration_field_bifarcation is unchanged and remains limited to the "exactly 4 of 5" case.
8. Schema
| Column Name | Data Type | Notes |
| application_id | varchar | Primary grain key for this dataset |
| position_id | bigint | Join key to position |
| group_id | varchar | Tenant/customer scoping |
| system_id | varchar | Used to exclude sourcing-only applications ('sourcing' excluded) |
| apps_system_id | varchar | Application's system_id (aliased from apps.system_id); also checked in the sourcing exclusion filter |
| application_ts | datetime | Application submission timestamp |
| hired_ts | datetime | Null unless candidate was hired |
| offer_ts | datetime | Null unless an offer was extended |
| match_stars | double | Powers Match Score metric |
| is_internal | bigint | Flag (0/1); internal candidate applying to a position |
| is_referral | bigint | Flag (0/1); feeds into source_category waterfall |
| is_career_site | bigint | Flag (0/1); application came via career site |
| in_talent_network | bigint | Flag (0/1); applicant was already in talent network |
| application_status | varchar | Raw status of the application (e.g. in review, rejected) |
| position_status | varchar | Raw status of the position (e.g. open, closed) |
| closed_at | datetime | Position close timestamp; null if still open |
| is_open | bigint | Flag (0/1) from source position record |
| creation_ts | datetime | Position creation date; basis for position_age_bucket |
| recruiter_email | varchar | Raw recruiter email from source position record (recruiter is now resolved separately) |
| recruiter | varchar | Recruiter owning the position. Resolved to "First Last (email-prefix)" via a join to customer_views.employee; falls back to recruiter_email if no match |
| job_function | varchar | Position's job function/category |
| location_country | varchar | Position location, country |
| location | varchar | Position location, full string (city, etc.) |
| site | varchar | Derived: location_country + city portion of location, concatenated |
| business_unit | varchar | Position's business unit |
| hiring_manager_email | varchar | Raw hiring manager email from source position record (hiring_manager is now resolved separately) |
| hiring_manager | varchar | Hiring manager for the position. Resolved to display name using the same pattern as recruiter, keyed off hiring_manager_email |
| position_age_bucket | varchar | Derived: <15, 15–30, 30–60, 60–90, >90 days since creation_ts |
| source_category | varchar | Derived: normalized source label (Referral, Internal Mobility, LinkedIn, Indeed, Career Site, etc.) via a CASE WHEN waterfall over source_type/source_name/utm_source |
| has_calibrated_location | bigint | Flag (0/1); Location calibration attribute set |
| has_calibrated_titles | bigint | Flag (0/1); Job Titles calibration attribute set |
| has_calibrated_skills | bigint | Flag (0/1); Skills calibration attribute set |
| has_calibrated_workexp | bigint | Flag (0/1); Work Experience calibration attribute set |
| has_calibrated_similar_cand | bigint | Flag (0/1); Ideal Candidate (similar candidates) calibration attribute set |
| fields_calibrated | varchar | Derived: comma-separated list of calibrated field names (e.g. "Location, Skills"), or 'None' if zero are calibrated |
| is_calibrated | bigint | Derived: 1 if ≥4 of 5 calibration flags (location, skills, titles, similar candidate, work experience) are true |
| is_closed_position | bigint | Derived: 1 if closed_at is not null |
| is_rediscovered_applicant | bigint | Flag (0/1) from source applications record |
| is_open_position | bigint | Derived: 1 if is_open = TRUE |
| is_hired | bigint | Derived: 1 if hired_ts is not null |
| is_excluded_position | bigint | Derived: 1 if position_id is in a hardcoded list of 5 excluded position IDs |
| Time_to_hire | bigint | Derived: DATEDIFF(hired_ts, application_ts), in days |
| hire_time_bucket | varchar | Derived bucket of Time_to_hire: <7, 7–30, 31–60, >60 days |
| calibration_pct | bigint | Derived: (number of true calibration flags / 5) × 20, so 0/20/40/60/80/100 |
| is_offer | bigint | Derived: 1 if offer_ts is not null |
| ats_job_id | varchar | External ATS job identifier for the position |
| seniority_level | varchar | Position's seniority level |
| calibration_type | varchar | Derived: "Calibration Complete" if ≥4/5 flags true, else "Calibration Incomplete" |
| calibration_status | varchar | Raw calibration status field from source position record |
| calibration_field_bifarcation | varchar | Derived: lists which 4 of the 5 calibration fields are set, only populated when exactly 4 are true; null otherwise |
| profile_id | varchar | Candidate identifier; null for positions with no applications |
| source_name | varchar | Raw source detail; for referrals, carries the referring employee identifier |
| position_calibration_80_complete_ts | datetime | Timestamp position first reached the calibration_80_percent_complete event (from customer_views.user_analytics) |
| position_calibration_100_complete_ts | datetime | Timestamp position first reached the calibration_100_percent_complete event (from customer_views.user_analytics) |
| ef_race | varchar | Candidate self-identified race/ethnicity from the applications record; powers Diversity Breakdown metrics. Null where not disclosed/collected. |
| ef_gender | varchar | Candidate self-identified gender from the applications record; powers Diversity Breakdown metrics. Null where not disclosed/collected. |
| archived_at | datetime | Position archive timestamp from the source position record; null if never archived. |
| row_num | bigint | Derived: ROW_NUMBER() partitioned by group_id, position_id, ordered to rank the "primary" application per position (hired first, then offer, then most recent hire/application date). Filter row_num = 1 for position-level analysis to avoid fan-out. |