1. Dataset Name

recruitment_funnel

2. Description

A stage-level dataset that tracks each candidate's progress through the hiring pipeline, matching application stage events to standardized stage groups and adding hire status, match score, and source category. Each row represents one application at one stage, showing how long it spent there before moving to the next. This dataset powers recruitment funnel, stage conversion, time-in-stage, and source effectiveness reporting.

3. Source Tables

• customer_views.application_stage (aliased ast — primary stage event records per application)
• customer_views.stage_mapping (DISTINCT dedup CTE — maps customer stage names to standardized stage_group_index and ef_stage_group labels)
• customer_views.applications (aliased apps in application_hire_info CTE — provides hired_ts, match_stars, application_ts, and source_category derivation; also used in position_hire_flag CTE to flag positions with any hire)
• customer_views.positions (aliased pos — LEFT JOIN on position_id for position attributes, calibration flags, open/closed status, and creation_ts)

4. Grain

One row per application-stage combination. An application progressing through 5 stages will have 5 rows. The LEAD window function provides next_stage_ts and next_stage_group on each row, enabling days_in_stage_group to be computed inline without a self-join.

5. Dashboards Supported

• Recruitment Effectiveness Dashboard

6. Metrics Supported

7. Known Exclusions / Filters Applied

• stage_mapping_dedup uses DISTINCT to remove duplicate stage mappings — multiple rows for the same (group_id, stage_group) combination are collapsed to one, preventing fan-out in the INNER JOIN to application_stage.
• The JOIN between application_stage and stage_mapping_dedup is an INNER JOIN — applications whose stage_group has no matching entry in stage_mapping are excluded from the dataset entirely.
• position_hire_flag flags any position_id that has at least one hired application (hired_ts IS NOT NULL) via a separate DISTINCT subquery, enabling is_hired_position without joining back to the full applications table.
• days_in_stage_group uses hired_ts (from applications) as the end timestamp for the Hired stage, and next_stage_ts (from the LEAD window) for all other stages — identified via LOWER(stage_group_display_name) LIKE '%hired%' OR LOWER(ef_stage_group) LIKE '%hired%'.
• is_calibrated = 1 when >= 4 of 5 position calibration flags are TRUE (has_calibrated_location, has_calibrated_skills, has_calibrated_titles, has_calibrated_similar_cand, has_calibrated_workexp). The match_stars column in Stage Time and Match Score only averages over calibrated positions.
• source_category is derived in application_hire_info via a priority-based CASE WHEN waterfall over source_type, source_name, and utm_source covering Internal Mobility, Referral, Recruiter, Career Site, Social Media, Job Boards (LinkedIn, Indeed, Google, Glassdoor, Naukri, Monster, Ziprecruiter, Seek, Built In), Eightfold alerts, and others.
• Date range filtering maps to positions.creation_ts via the override_filters date_range configuration.
• pass_through_rate in the Recruitment Funnel uses FIRST_VALUE(COUNT(DISTINCT application_id)) OVER (ORDER BY stage_group_index ASC) as the denominator — this anchors the denominator to stage_group_index = 1 (New Applications) for all stages.

8. Schema