Offers table
1. Dataset Name
offers_table
2. Description
An offer-level dataset that tracks each offer's full lifecycle — creation, approval, extension, acceptance or decline, and signing — along with position, candidate, and recruiter context. Each row represents one offer. This dataset powers offer outcome and turnaround-time reporting (acceptance time, extend time, approval time, sign time, acceptance rate), offer status distribution, internal-vs-external candidate splits, and signing-channel distribution on the Offers Dashboard.
3. Source Tables
- customer_views.offers_view (aliased off) — primary offer record; base table for the dataset.
- 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.
- offer_signed CTE (sourced from customer_views.user_analytics, aliased ua) — filters to event = 'offer_candidate_signed' and takes MAX(timestamp) per offer (entity_id), then LEFT JOINed on id to attach offer_signed_at.
4. Grain
One row per offer (id). All joins (applications, positions, offer_signed) are LEFT JOINs resolved to at most one match per offer, so there is no fan-out risk — offers with no match in a joined table simply carry NULLs for that table's fields rather than being dropped.
5. Dashboards Supported
- Offers Dashboard
6. Metrics Supported
| Metric | Supported Breakdowns |
| Avg Offer Acceptance Time | recruiter_name, job_function, job_location, hiring_manager |
| Avg Time to Extend an Offer | recruiter_name, job_function, job_location, hiring_manager |
| Offers Rolled Out | recruiter_name, job_function, job_location, hiring_manager |
| Offers by Status | job_location, recruiter_name, job_function, hiring_manager |
| Avg Offer Approval Time | job_function, recruiter_name, job_location, hiring_manager |
| Offers: Internal vs External | recruiter_name, job_function, job_location, hiring_manager |
| Offer Acceptance Rate (new) | job_location, recruiter_name, job_function, hiring_manager |
| Avg Sign Time | recruiter_name, job_function, hiring_manager, job_location |
| Offer Rollout Distribution by Signing Channel | recruiter_name, job_function, job_location, hiring_manager |
7. Known Exclusions / Filters Applied
- offer_signed_at reflects only the most recent signing event recorded for the offer; earlier signing attempts (e.g. re-signs) are not retained.
- offer_signature_type normalizes the raw 'docusign' value to 'esign'.
- candidate_type defaults to 'unknown' when the offer has no matching application record to determine internal/external status.
8. Schema
| Column Name | Data Type | Notes |
| group_id | varchar | Customer/organization identifier. |
| id | varchar | Unique offer identifier. COUNT DISTINCT grain for all metrics. |
| position_id | bigint | Position associated with the offer. |
| profile_id | bigint | Candidate profile identifier. Complementary field. |
| application_id | varchar | Application associated with the offer. Joins to applications to derive candidate_type. |
| offer_status | varchar | Current status of the offer (e.g. Draft, Extended, Accepted, Declined, Rejected, Cancelled, Expired). |
| created_by | varchar | User who created the offer. Complementary field. |
| tentative_joining_ts | datetime | Candidate's tentative joining date. Complementary field. |
| offer_created_at | datetime | Timestamp the offer was created. Date field for Offers by Status. |
| offer_extended_at | datetime | Timestamp the offer was extended to the candidate. |
| offer_accepted_at | datetime | Timestamp the candidate accepted the offer. |
| offer_declined_at | datetime | Timestamp the candidate declined the offer. Complementary field. |
| candidate_name | varchar | Candidate's name. Complementary field. |
| application_source | varchar | Raw source of the candidate's application. Complementary field. |
| application_ts | datetime | Timestamp of the underlying application. Complementary field. |
| analytics_t_update | datetime | ETL update timestamp. Complementary field. |
| approvers_json | varchar | Raw JSON of offer approvers. Complementary field. |
| application_source_name | varchar | Display name of the application source. Complementary field. |
| rejection_reason | varchar | Legacy free-text rejection reason on the offer. Complementary field. |
| offer_approved_at | datetime | Timestamp the offer was approved internally. |
| sent_for_approval_at | datetime | Timestamp the offer was sent for approval. |
| visa_type | varchar | Visa type associated with the offer. Complementary field. |
| workplace_type | varchar | Workplace type (e.g. remote, hybrid, onsite). Complementary field. |
| job_location | varchar | Location of the job. |
| is_people_manager_role | boolean | Whether the position is a people-manager role. Complementary field. |
| is_rehire | boolean | Whether the candidate is a rehire. Complementary field. |
| custom_fields_json | varchar | Raw JSON of customer-specific custom fields. Complementary field. |
| offer_lifecycle_json | varchar | Raw JSON capturing offer lifecycle events; source for offer_signature_type. Complementary field. |
| analytics_loaded_at | datetime | ETL load timestamp. Complementary field. |
| is_internal | boolean | Raw internal/external flag from the application. Source for candidate_type. Complementary field. |
| candidate_type | varchar | Derived: 'internal', 'external', or 'unknown' based on is_internal. |
| job_function | varchar | Job function of the position. Breakdown dimension across most offer metrics. |
| business_unit | varchar | Business unit of the position. |
| recruiter | varchar | Recruiter email associated with the offer. |
| recruiter_name | varchar | Recruiter name associated with the offer. |
| hiring_manager | varchar | Hiring manager for the offer. |
| offer_signed_at | datetime | Derived: timestamp of the candidate's most recent signing event. |
| offer_signature_type | varchar | Derived: signing channel used — typed_signature, esign, or wet_signature. |