Offer rejection reasons
1. Dataset Name
offer_rejection_reasons
2. Description
A reason-label-level dataset that captures why offers with a negative outcome — declined by the candidate, revoked by the recruiter, or rejected by an approver — ended that way. Each row represents one reason label cited for one offer. This dataset currently powers Top Offer Declining Reasons on the Offers Dashboard, and is built broader than that single widget so it can support future reporting on revoke and approval-rejection reasons as well.
3. Source Tables
- customer_views.offers_view (aliased off) — primary offer record; base table for the dataset.
- reason_rows CTE (also sourced from customer_views.offers_view) — three UNION ALL branches, each unnesting a different JSON array on offer_lifecycle_json (candidate_decline_reason_labels, revoke_reason_labels, approval_reject_reason_labels) into one row per reason label, tagged with the corresponding reason_type.
- customer_views.applications (aliased apps) — LEFT JOINed on application_id; provides is_internal, used to derive candidate_type.
- customer_views.positions (aliased pos) — LEFT JOINed on position_id; provides job_function, business_unit, recruiter, recruiter_name, and hiring_manager.
4. Grain
One row per (offer_id, reason_label). An offer with multiple reason labels produces multiple rows. An offer with a qualifying status but no recorded reason labels still produces exactly one row, with reason_label = NULL, since reason_rows is LEFT JOINed to the base offer set.
5. Dashboards Supported
- Offers Dashboard
6. Metrics Supported
| Metric | Supported Breakdowns |
| Top Offer Declining Reasons | recruiter_name, business_unit, job_function, hiring_manager |
7. Known Exclusions / Filters Applied
- Only offers with offer_status IN ('Declined', 'Canceled', 'Rejected') are included in this dataset — offers in any other status are excluded entirely.
- reason_type is derived per row: it takes the value from the matched reason-label branch (candidate_decline, revoke, or approval_reject) when one exists, and falls back to a fixed mapping from offer_status (Declined → candidate_decline, Canceled → revoke, Rejected → approval_reject) when the offer has no recorded reason label.
- Offers declined, revoked, or rejected before reason-code tracking was enabled will appear with reason_code = NULL.
- This dataset is intentionally broader than what is currently wired to a widget: only the reason_type = 'candidate_decline' rows (offer_status = 'Declined') feed Top Offer Declining Reasons today. The 'revoke' (Canceled) and 'approval_reject' (Rejected) rows already exist in the dataset, built ahead of future Revoke Reasons and Approval Rejection Reasons widgets.
8. Schema
| Column Name | Data Type | Notes |
| offer_id | varchar | Unique offer identifier. COUNT DISTINCT grain for Top Offer Declining Reasons. |
| reason_type | varchar | Derived: which outcome bucket the reason code belongs to — 'candidate_decline', 'revoke', or 'approval_reject'. |
| reason_label | varchar | Individual reason label cited for the offer's decline, revocation, or rejection. One row per label — an offer with multiple labels has multiple rows. NULL when no reason label was recorded. Grouping column for Top Offer Declining Reasons. |
| offer_status | varchar | Final status of the offer. Restricted to Declined, Canceled, or Rejected at the dataset level. Top Offer Declining Reasons further restricts to Declined. |
| offer_created_at | datetime | Timestamp the offer was created. Complementary field. |
| offer_extended_at | datetime | Timestamp the offer was extended to the candidate. Complementary field. |
| offer_declined_at | datetime | Timestamp the candidate declined the offer. Primary date field for Top Offer Declining Reasons. |
| rejection_reason | varchar | Legacy free-text rejection reason on the offer. Complementary field. |
| job_location | varchar | The job location associated with the offer. |
| position_id | bigint | Position associated with the offer. Joins positions for job_function, business_unit, recruiter, and hiring_manager. Complementary field. |
| profile_id | bigint | Candidate profile identifier. Complementary field. |
| created_by | varchar | User who created the offer. Complementary field. |
| candidate_type | varchar | Derived: 'internal', 'external', or 'unknown' based on the application's internal flag. Complementary field. |
| job_function | varchar | Job function of the position. Breakdown dimension for Top Offer Declining Reasons. |
| business_unit | varchar | Business unit of the position. Breakdown dimension for Top Offer Declining Reasons. |
| recruiter | varchar | Recruiter identifier. Complementary field. |
| recruiter_name | varchar | Recruiter display name. Breakdown dimension for Top Offer Declining Reasons. |
| hiring_manager | varchar | Hiring manager identifier. Breakdown dimension for Top Offer Declining Reasons. |