Health Insurance Renewal Census: What Brokers Need and How to Collect It Clean

Renewal season for a benefits broker means asking every employer group for the same thing: an updated employee census. What comes back is the office manager’s own spreadsheet, and it’s rarely ready to quote from.

What goes in a renewal census

Carriers’ forms differ, but most ask for:

• Employee name (or an ID).
• Date of birth. Prices depend on each person’s age.
• Home ZIP code. It decides the rating area, and which doctors need to be in network.
• Coverage: employee only, employee and spouse, employee and children, family, or waived.
• Tobacco use, where the state and carrier use it.
• Hire date and hours or full-time status, for eligibility and waiting periods.
• Sex, on some carriers’ forms, mostly for larger groups.

What small-group prices can depend on

Under the ACA’s rules for the small-group market, premiums can vary only by:

• Age (adults at most 3 to 1 between oldest and youngest),
• Rating area (where people live),
• Tobacco use (at most 1.5 to 1),
• Who is covered (individual or family).

Not health status, not claims, not sex. So age, ZIP code, tobacco and coverage are the fields worth chasing; the rest is eligibility.

What comes back from employers

From the example’s own sample file, typical of what an office manager sends:

• ZIP codes without their leading zero (2134 for 02134), because the file went through Excel. See why Excel does that.
• Coverage written six ways: “EE+SP”, “Family”, “ee+ch”, “Employee Only”, “Waive”, “FAM”.
• A blank coverage cell, a missing birth date, “W” in the sex column.
• 160 hours a week: a month’s hours in the weekly column.
• A part-timer enrolled. Fine if the carrier allows it; for the employer mandate, 30 hours a week counts as full-time.
• A “Total” row at the bottom.

Collecting it clean

The census portal example has a health renewal template. The employer drops in their spreadsheet as it is, and the portal:

• matches their columns (“Hrs/Wk”, “Home Zip”, “Coverage”) to the broker’s fields,
• puts the zeros back on ZIP codes and maps every spelling of the coverage tiers to the broker’s codes,
• leaves out the totals row,
• asks only about what’s missing, with the reason (“Group health prices depend on each person’s age”),
• flags for the broker what the broker decides, like a part-timer on the plan.

It’s the same engine as the example’s 401(k) census and 1099 contractor list: one portal for any data you collect from clients.

Key takeaways

• Age, ZIP code, tobacco and coverage drive small-group prices; chase those first.
• Expect Excel damage (ZIP codes) and many spellings of the same coverage tier; map them, don’t reject them.
• Ask the employer only for what’s missing, and say why.

Try the health renewal template →

A broker, or any business that collects data from its clients? We’ll build a portal around your fields and checks, and because we work with AI-assisted development, it costs far less than you might expect. Let’s talk.