1. Dataset Name

ai_interview_funnel

2. Description

A stage-level funnel dataset that follows each candidate's journey through the AI Interviewer process, from invite through to hire. Each row represents one candidate at one funnel stage for a given interview session, including the timestamp of that stage, the next stage reached, and the time spent transitioning between them. The funnel covers eight stages: Invite Sent, Call Started, Interview Completed, Feedback Generated, Feedback Reviewed, Requested Human Assistance, Offer Extended, and Hired. This dataset powers the AI Recruitment Funnel visualization on the AI Interviewer dashboard.

3. Source Tables

• customer_views.user_analytics (event log — source of the first six funnel stages)
• customer_views.interview_session (joined to resolve interview_type per session)
• customer_views.applications (source of the Offer Extended and Hired stages, via offer_ts and hired_ts)

4. Grain

One row per entity_id (interview session) per funnel stage reached. A candidate who has progressed through five stages will have five rows in this dataset, one per stage, each carrying its own stage_ts and the next stage's name/timestamp.

5. Dashboards Supported

• AI Interviewer (AII) Dashboard

6. Metrics Supported

7. Known Exclusions / Filters Applied

• Only the six interview_session_* events listed in the WHERE clause are pivoted from user_analytics; any other event types on the same entity_id are ignored.
• A row is only included in the final output when stage_order = 1 (the first stage), OR the previous stage in sequence was actually reached (prev_stage_order = stage_order - 1), OR stage_order = 7 (Offer Extended is always included regardless of sequence gaps).
• This sequencing rule means a candidate cannot appear at, say, Feedback Generated without a recorded Interview Completed immediately before it — it prevents out-of-order or orphaned stage entries from appearing in the funnel, ensuring strict step-by-step progression.
• Offer Extended and Hired stages are joined from applications via matching profile_id and position_id to the candidate's interview session — only applications with a non-null offer_ts or hired_ts contribute these stages.
• invited_at is computed as a window function (MAX over entity_id) so every stage row for a given candidate carries the same invitation timestamp, regardless of which stage that row represents.
• interview_type_label is derived through a CASE waterfall over interview_type (profile_screening → Screening, coding_interview → Coding, behavioural_interview → Functional/Behavioural, modular_interview → Modular); any unmapped value falls to 'Other'.
• minutes_in_stage is only meaningful when next_stage_ts is populated; for a candidate's most recent (current) stage, this value will be null since there is no next stage yet.

8. Schema