Talkpush Lifecycle and List Intelligence Platform
Production Snowflake pipeline suite that maintains canonical lead lifecycle status in TBLLEADTRACKING_V3 and drives list operations in TBLLIST_MASTER for recruiter execution and performance reporting.
Overview
Processes new, updated, and deleted Talkpush leads, recalculates lifecycle outcomes, and refreshes operational list states used by recruiter and performance-management workflows.
Business Purpose
Provide one governed source for both funnel progression and list-based execution so teams can execute consistent outreach, endorsement, shortlisted, and hired follow-up processes.
Technology Stack
Business Impact
Consolidates stage classification and list disposition logic, reducing manual reconciliation while improving recruiter throughput and trust in conversion reporting.
Execution Cadence
Runs daily through TASK_PROCESSDATA_V3 at 14:15 ET.
Stage Model
Tracks PS, TS, TP, and H statuses with TS_ENTER, TP_ENTER, H_ENTER, and COMPLETION_TIME timestamps.
Reprocess Control
Supports full reprocessing through P_FULL_REPROCESS for logic rollouts and historical backfills.
Hire Locking
Leads auto-lock at HIRED once Domain ID is mapped and TimeQey hours reach the 4-hour threshold.
Impact Metrics
Process Coverage
4 stages
One governed lifecycle model across Prescreening, Talkscore, Talent Pool, and Hiring.
Reprocessing Window
2 days
Recent candidate and folder-log activity is re-evaluated continuously for updated-lead scope.
Hiring Sync Window
7 days
EngageHR hire and offer updates are synchronized on each execution from recent source changes.
Hire Lock Threshold
>= 4 hrs
Leads lock only after TimeQey-backed proof of work to prevent premature hire closure.
Business outcomes: faster funnel reporting, reduced manual reconciliation across Talkpush and EngageHR, and higher trust in final hiring states through lock-protected lifecycle closure.
Pipeline Responsibilities
- Scopes deleted, new, and updated leads, then rebuilds status outcomes only for eligible in-scope records.
- Derives PS_STATUS, TS_STATUS, TP_STATUS, and H_STATUS using folder history, JSON attributes, and reject classifications.
- Synchronizes hires and offers from EngageHR using candidate mapping rules and invitation-date alignment.
- Calculates TQ_HOURS from TimeQey to enforce hiring confirmation and lock behavior.
- Writes execution-level observability data (counts, DML row totals, timings, and query IDs) to DATASTORE.MODELED.TBL_PROCEDURE_RUN_LOG.
Funnel Logic Highlights
- Prescreening and Talkscore logic include date-aware rule changes (2025-01-01 and 2025-07-01) for eligibility and pass criteria.
- Manual campaign override force-sets PASSED and SHORTLISTED outcomes for shortlisted manual campaign candidates.
- Talent Pool updates include TP_ENTER, endorsement detection, and endorsement count in the same update path.
- Hiring stage can transition OFFERED to HIRED, reverse stale hires, and mark post-offer reject outcomes as DECLINED.
- Locked leads are excluded from future status rewrites to preserve terminal hiring outcomes.
List Master Population Engine
TBLLIST_MASTER is refreshed by dedicated procedures that convert folder movement history into recruiter-ready operational queues with controlled episode windows and disposition logic.
- ENDORSED: Tracks client-folder endorsement episodes, extends windows through assessment folders, and assigns final disposition on first non-qualifying exit.
- TALENT POOL: Builds one episode per lead from first Talent Pool entry through final exit, with windowed call counting only during active pool periods.
- SHORTLISTED: Chains shortlisted visits that belong to the same client path and extends windows into hired when disposition ends in Hired.
- HIRED: Captures hired-direct paths that bypass shortlisted, then marks terminal disposition from EngageHR updates.
- LEADCHASERS: Models unresponsive and cold-lead windows, including Passed Talkscore passthrough behavior, to preserve continuous chase episodes.
Key refresh procedures: SP_REFRESH_LIST_MASTER_ENDORSED, SP_REFRESH_LIST_MASTER_TALENTPOOL, SP_REFRESH_LIST_MASTER_SHORTLISTED, and SP_REFRESH_LIST_MASTER_LEADCHASERS.
List Operations Metrics
Queue Coverage
5 queues
ENDORSED, TALENT POOL, SHORTLISTED, HIRED-direct, and LEADCHASERS are maintained as governed recruiter queues.
Population Baseline
2025-09-01
Episode inclusion is standardized from a single historical start date across refresh paths.
Attribution Method
Windowed
Calls are counted only inside active episode windows to avoid off-stage inflation.
Disposition Method
Exit-based
Final state is driven by first valid post-episode exits or EngageHR confirmation, depending on list type.
Core Data Assets
Primary objects: TALKPUSH.MODELED.SP_PROCESSLEADS_V3, TASK_PROCESSDATA_V3, and LIST_MASTER refresh procedures.