Open Enrollment Tracker

A few weeks, the whole company, and a hard deadline. The tracker's job is a truthful completion number and a chase list, and both depend on definitions the delivered view keeps vague.

RuleSnapshot eligibility at window open, track adds and drops as named lines, and split completion into actively reviewed versus rolled forward.Neverlet passive enrollment read as engagement. Rolled-forward elections are complete; they are not reviewed, and the split is the point.

◆ The traps— the moving denominator, the passive split, and the three queues that decide the last week.

Trap	What goes wrong
The denominator moves	Hires and terminations continue through the window, so eligibility on day twelve is not eligibility on day one. Snapshot the population at window open; adds and drops are named lines beneath it, and the completion percentage stops wobbling for reasons nobody can explain.
Passive is not engagement	Passive enrollment rolls last year's elections forward when a worker takes no action. Complete, and untouched. Report actively-reviewed and rolled-forward separately; a high completion built on passive means the plan changes nobody read about will surface as payroll questions in January.
Pending evidence of insurability	Elections above coverage thresholds wait on evidence before the coverage is real. Approved-pending-evidence is its own queue with its own follow-up, not a completion.
Unfinished corrections	A correction started but never submitted leaves the original elections standing. The unfinished-corrections list runs daily in the final week, because after the window closes it becomes a life-event exception process.
Workers on leave	People out during the window still owe elections. The Workers on Leave list is the outreach list, pulled at window open, not discovered at close.

◆ The owned model, and the SQL— one daily status query on the election fact, statuses that sum to the snapshot.

The tracker reads fct_benefit_enrollment event rows for the new plan year against the day-one eligibility snapshot:

-- daily during the window: statuses that sum to the day-one snapshot
SELECT COUNT(*)                                                        AS eligible,
       SUM(CASE WHEN e.election_status = 'Submitted'
             AND e.election_method  = 'Active'      THEN 1 ELSE 0 END) AS completed_active,
       SUM(CASE WHEN e.election_status = 'Submitted'
             AND e.election_method  = 'Passive'     THEN 1 ELSE 0 END) AS rolled_forward,
       SUM(CASE WHEN e.election_status = 'Pending EOI' THEN 1 ELSE 0 END) AS pending_eoi,
       SUM(CASE WHEN e.election_status IS NULL         THEN 1 ELSE 0 END) AS not_started
FROM oe_eligible_snapshot s
LEFT JOIN fct_benefit_enrollment e
       ON s.worker_key = e.worker_key AND e.plan_year = 2027

Sample output, day twelve of the window:

eligible	completed_active	rolled_forward	pending_eoi	not_started
147	96	31	6	14

The statuses sum to the snapshot: 96 plus 31 plus 6 plus 14 is 147. Completion is 86.4%, and the honest number beside it is 65.3% actively reviewed. The 14 not started, grouped by manager, are the chase list; the 6 pending evidence are their own follow-up. After the window closes, the new plans' deduction codes go through the same pre-commit mapping gate as any new pay component, so the plan year's first payroll reconciles instead of surprising. Sample values are illustrative, never client data.

Want enrollment season to run on a list instead of a scramble?

The day-one snapshot, the active-passive split, and the three queues, reconciled against Workday, and you own every line.

Talk to us

Terms on this page

enrollment window: The dates elections are open. The snapshot is taken when it opens.
eligibility snapshot: Who owed elections on day one. The fixed denominator; adds and drops are lines beneath it.
active election: The worker reviewed and submitted. The engagement number.
passive enrollment: Last year's elections rolled forward on no action. Complete, not reviewed.
pending EOI: Awaiting evidence of insurability. A queue, not a completion.
unfinished correction: Started, never submitted; the original elections stand. A daily list in the final week.
chase list: Not-started workers by manager. How the window closes on time.