# Census Translation Rules — Canonical Spec v1.1

**This document is the single source of truth for every LMG/NCAM census tool.**
The Office Script, the CensaScrub Python tools, and the offline HTML cleaner
should each implement *these* rules so they produce identical output and don't
drift apart. When a rule changes, change it **here first**, bump the version,
then update each implementation.

| Implementation | Repo / file | Runtime | Audience |
|----------------|-------------|---------|----------|
| LMG/NCAM Office Script | `lmg-ncam-engine/LMG-NCAM-Census-Engine.ts` | Excel Automate tab (M365) | Excel + LMG uploaders, zero-install |
| Python CLI/GUI | `CensaScrub/censascrub_v001.py`, `censascrub_gui.py` | Windows desktop | Local power users |
| Offline HTML cleaner | `CensaScrub/lmg_census_cleaner_offline.html` | Any browser, offline | **Universal fallback — any user, no account** |
| ~~Web app~~ (archived 2026-06-29) | `_archive/census-data-refiner/` — dead, never deployed; not a sync target | — | — |

Column source of truth: `CensaScrub/LMG_Column_Dictionary.xlsx` (LMG platform
field names + alias variants).

---

## 1. Workbook / output structure

Seven outputs, in this order. **LMG must be worksheet #1** (LaunchMyGroup uploads
the first sheet only).

1. `LMG` — full enrollment upload (employees, spouses, domestic partners, children). Tab #1, colored `#5C9A1B`.
2. `NCAM` — employees-only quoting sheet derived from LMG. Tab #2, colored `#5C9A1B`.
3. `SUMMARY` — counts by relationship and by plan tier.
4. `VALIDATION ISSUES` — ERROR / WARNING / INFO with row, member, column, value, issue, action.
5. `PROCESSING LOG` — audit trail of every transformation.
6. `RAW DATA` — untouched copy of the original census. Never modified.
7. `Other Columns` — every column **not** in the LMG upload, each with a reason: entirely-blank LMG fields (removed because blank columns break the upload), unrecognized source headers, and duplicate/unused source columns. Two columns: COLUMN, WHY IT IS NOT IN THE LMG UPLOAD. Tab colored Plum `#AF0061`. All other secondary tabs are colored `#003057`.

**Blank-column removal:** after processing, any LMG output column with no data in
any row is dropped from the `LMG` sheet and its name listed on `Other Columns`.
**Exception — REQUIRED columns are never dropped** (Relationship, Member Last
Name, Member First Name, Gender, Date of Birth, State); if a required column is
entirely blank it is kept and flagged ERROR. NCAM and SUMMARY read from the full
(unfiltered) records, so they are unaffected.

**Validation highlighting:** each issue's offending cell is highlighted on the
`LMG` tab by severity — ERROR `#FFC7CE`, WARNING `#FFEB9C`, INFO `#BDD7EE`
(most-severe color wins per cell; duplicate flags highlight the whole row). The
SEVERITY column on `VALIDATION ISSUES` is shaded the same colors.

A tool may read its source from `RAW DATA` if present (idempotent re-runs),
else from the active/first data sheet.

## 2. LMG output columns (canonical order)

Exact LMG platform names and order (40 columns):

Relationship, Social Security Number, Status Field, Member Last Name, First Name,
Middle Initial, Gender, Date of Birth, Date of Hire, Member Street Address,
City, State, Zip Code, Phone Number, Email Address, Working Location,
Billing Division, Disabled?, Salary, Hours Worked per week, Occupation,
Dental Plan Selection, Dental Coverage Type, DHMO Provider Name,
Dental Prior Carrier Name, Dental Prior Carrier Effective Date,
Dental Prior Carrier Term Date, Dental Prior Carrier Ortho, Vision Plan Selection,
Vision Coverage Type, Basic Life Election, Primary Life Beneficiary,
Dependent Basic Life, Employee Voluntary Life, Spousal Voluntary Life,
Dependent Voluntary Life, STD, STD Class, LTD, LTD Class.

Notes: COV/COVERAGE → Coverage Type (tier EE/ES/EC/FAM/W); Plan/Select/Elect →
Plan Selection. Basic Life Election / Dependent Basic Life / STD / LTD read
**Enroll / Waive**. SSN is kept on the employee only. `Member Last Name` becomes
`Last Name` on the NCAM tab. Voluntary Life columns are omitted when blank/0/Waive
for everyone (§6).

## 3. Header normalization

- Remove parenthetical text: `RELATIONSHIP (EE, SP, CH)` → `RELATIONSHIP`.
- Unbalanced parens: if a stray `)` remains after stripping (e.g. `Status
  (Active, COBRA, Disabled ) If COBRA, provide Loss of Event Date and Event
  Type)`), everything from the first `(` onward is instruction text — keep only
  the text before the first `(` (→ `STATUS`).
- Uppercase, strip punctuation to spaces, collapse whitespace.
- Map normalized header to the LMG field. Priority: **user-confirmed HEADER MAP
  entry > exact alias > high-confidence fuzzy match (≥85% word+character
  similarity, auto-applied with a verify flag) > suggestion (50–85%, listed but
  not applied) > unrecognized**. Exact matches never get flagged.
- **HEADER MAP tab:** every non-exact column is listed with a suggested field +
  confidence and an editable `Map To` cell. Fill `Map To` with an LMG field and
  re-run to include the column. Entries persist across runs (the tool learns).
- **No column leaves the LMG upload silently.** Any source column the tool does
  not recognize — or recognizes but can't use because an earlier column already
  filled that LMG field (e.g. both `Home Phone` and `Cell`) — is **flagged
  WARNING on VALIDATION ISSUES** and **listed on Other Columns**, while its raw
  values are always preserved on `RAW DATA`. The user decides; the tool never
  drops a column without surfacing it.

## 4. Field rules

| Field | Rule |
|-------|------|
| Relationship | → `Employee` / `Spouse` / `Domestic Partner` / `Child`. Unknown (e.g. `Retiree`) = flag, keep original. |
| Last / First name | UPPERCASE. Missing = ERROR. |
| Middle Initial | First character only, UPPERCASE. |
| Gender | `Male`→`M`, `Female`→`F`. Else flag (blank/unknown = ERROR for enrollees). |
| Date of Birth | → `MM/DD/YYYY`. Accept slashes/dashes/ISO/2-digit-year/Excel serial. Unparseable or missing = ERROR. |
| Date of Hire | Employees only (blanked on dependents). → `MM/DD/YYYY`. |
| Prior carrier eff/term dates | → `MM/DD/YYYY`. |
| SSN | **Kept on the EMPLOYEE only** — removed from spouse / domestic partner / child. Digits only. If fewer than 9 digits, **pad leading zeros to 9** (INFO spot-check). More than 9 digits = ERROR. Employee missing SSN = ERROR. |
| Member Street Address | Merge Address 1/2/3 (and Addr/Street variants) with single spaces, UPPERCASE. |
| Member City | UPPERCASE. |
| State | Output header is **`State`** (not "Member State"). Full name → USPS 2-letter (`Texas`→`TX`). Invalid = flag; employee missing state = ERROR. **Required — never dropped.** |
| Member Zip Code | First 5 digits; restore dropped leading zero. |
| Phone Number | Digits only, 10 digits; strip leading country `1`. Invalid = flag + leave blank. **Multiple phone columns coalesce per row by priority: personal/home/generic → cell/mobile → work/office.** |
| Email Address | **Multiple email columns coalesce per row by priority: personal/home/generic → work/office.** |
| Annual Salary | Strip `$` and commas. Hourly → `rate × Hours Worked Per Week × 52`, using the member's **actual** weekly hours (e.g. 20 → `rate × 20 × 52`), default 40 only when hours blank/invalid (INFO flag). **Hourly vs annual is decided by a Compensation Type / Salary Type column when present** (Hourly/Annual), else by detecting "/hr" in the salary cell. Salary < $1,000 = flag as possible hourly. Non-numeric = flag. |
| Hours Worked Per Week | Use the value as given. Blank/invalid employee → `40` (fallback only). |
| Occupation | UPPERCASE; apply abbreviation table; max 25 chars (drop non-leading vowels, then truncate + flag). |
| Disabled? | Blank → `No`. Y/N variants → `Yes`/`No`. |
| Combined columns | A single `Name` / `City, State ZIP` / full-address column is split into the separate fields per §11c **before** the field rules above apply. |

### Abbreviation table (occupation)
SENIOR→SR, JUNIOR→JR, INFORMATION TECHNOLOGY→IT, SYSTEM(S)→SYS,
ADMINISTRATOR/ADMINISTRATION→ADMIN, SUPPORT→SUP, TECHNICIAN→TECH,
REPRESENTATIVE→REP, BUSINESS→BIZ, OPERATIONS→OPS, MANAGER→MGR, MANAGEMENT→MGMT,
ASSISTANT→ASST, DIRECTOR→DIR, ENGINEER→ENGR, SUPERVISOR→SUPV, DEVELOPER→DEV,
COORDINATOR→COORD, SPECIALIST→SPEC, GENERAL→GEN.

### Coverage vs Plan columns
- A column containing **COV / COVERAGE** (e.g. `Dental Coverage`, `Vision Cov
  Level`) is the **Coverage Tier** — the election EE/ES/EC/FAM/W.
- A column containing **Plan / Select / Elect** (e.g. `Dental Plan`, `Vision
  Select`, `Dental Election`) is the **Plan Elected** — which plan to enroll on.
- A Dental/Vision **Plan** column that holds only **one distinct value** is left
  off the upload (the system auto-selects the single plan); it's noted on Other
  Columns.

## 5. Coverage normalization

Normalize any tier value to one of: `EE`, `ES`, `EC`, `FAM`, `W`.

- Employee only / Self → `EE`.
- Employee + Spouse **or** Domestic Partner → `ES`.
- Employee + Child(ren) → `EC`.
- Employee + Spouse/DP + Child(ren) → `FAM`.
- Waive / Waived / Decline / None / Opt Out → `W`.
- Unrecognized → flag, keep original.

### LMG-specific
- Dental/Vision tier of `W` → output text `Waive with other coverage`.
- Blank **employee** dental/vision tier, **when the group carries that product**,
  → `Waive with other coverage` (LMG would otherwise treat blank as EE).

### NCAM-specific (reverse)
- `Waive with other coverage` → `W`.
- Blank employee dental/vision → `W` (NCAM defaults blanks to EE).

## 6. Life rules

- Primary Beneficiary: if an employee has Basic Life (election, coverage type, or
  class present and not waived) and/or real Voluntary Life (employee/spousal/
  dependent), and beneficiary is blank → `ON FILE`. Employee rows only.
- Employee & Spousal Voluntary Life: blank or waive → `0`. Employee rows only.
- **Dependent Voluntary Life: blank, `0`, or waive → `Waive`** (not `0`).
- A Voluntary Life column (Employee / Spousal / Dependent) is **dropped from the
  LMG output entirely if it is blank or `0` for every row** — it only appears when
  there are real elections. (Dropped columns are listed on Other Columns.)
- Spousal Voluntary Life found on a dependent row → flag INFO (roll-up to
  employee is a future version; handle manually for now).

## 7. NCAM derivation

From LMG, employees only. `Member Last Name` → `Last Name`.
Columns: Last Name, Gender, Date of Birth, Dental Coverage Tier,
Vision Coverage Tier. **If STD or LTD coverage exists**, also include:
Annual Salary, Occupation, STD Election, STD Class, LTD Election, LTD Class.
Counts must reconcile to LMG.

## 8. Duplicates

Key = Last Name + First Name + Date of Birth + Gender. Duplicates are
**flagged** as WARNING in VALIDATION ISSUES — **never silently merged or
deleted**. An upload file must never silently drop an enrollee. The user
reviews and removes. (Family-unit-aware merge is a planned future version.)

## 8b. Special characters & extra validation flags

- **Special characters** anywhere in the census are replaced with a space; `#` →
  ` UNIT `. Exempt: date fields, Email Address, and numeric fields (SSN, Phone,
  Zip, Salary, Hours, Voluntary Life).
- **Age 65+**: any member (employee or dependent) 65 or older is flagged.
- **Voluntary life not in even thousands** (e.g. 62,500) is flagged.
- **Family consistency** (flag only, never auto-fixed): a dependent listed but not
  covered by any tier → flag; a tier that implies a dependent not listed (ES w/o
  spouse, EC w/o child, FAM missing either) → flag (row-listing censuses only).

## 9. Validation severities

- **ERROR** — missing/invalid required data (no SSN, bad/missing DOB, missing gender). Fix before upload.
- **WARNING** — likely wrong but not blocking (odd salary, unknown relationship/tier, possible duplicate, over-long occupation, bad phone).
- **INFO** — FYI (e.g. spousal vol-life on a dependent row).

## 10. Guiding principle for variable carrier formats

Carriers, payroll vendors, and TPAs each format census files differently; for the
most part their columns match the alias logic above, but it varies. The tool
**auto-handles the normal patterns and FLAGS anything out of the norm for the end
user to decide — it never guesses silently.** When a structural detection isn't
confident, it does not transform; it flags the rows and passes them through. This
extends the no-silent-loss rule from values to structure.

## 11. Pivot pre-pass (IMPLEMENTED)

Runs BEFORE normalization, reshaping stacked/sideways layouts into one clean
enrollee row per person, then hands off to the existing pipeline. Transforms only
when confident; flags anything out of the norm.

- **Stage 0 — product-coded routing.** When a row has a generic value column
  (Coverage Tier / Coverage Level / Coverage Type, Plan, Election, Class) plus a
  product column (Plan Type / Product = Dental / Vision / Basic Life / STD / LTD),
  the generic value is moved into the product-specific LMG field for that row's
  product *before* the collapse: TIER→Dental/Vision Coverage Tier, PLAN→Dental/
  Vision Plan Elected, ELECTION→Basic Life/STD/LTD Election, CLASS→Basic Life/STD/
  LTD Class. Unknown products are left alone (flagged). Output stays
  ancillary-focused — Medical/Medicare generic values are not routed/output.
- **Stage 1 — collapse stacked product rows.** Gated on a Product column
  (Product / Coverage / Coverage Type / Plan Type / Benefit / Line of Coverage).
  Groups rows by person (SSN + last + first + DOB), merges each person's non-blank
  cells into one row (so the Dental row's tier, the Vision row's tier, etc. land on
  one line). Conflicting non-blank values for the same person/column are flagged
  WARNING, not silently overwritten. The existing duplicate check is the post-pivot
  fail-safe.
- **Stage 2 — explode dependent columns.** Detects `Dependent N <field>` groups
  (Name/SSN/DOB/Gender/Relationship, split or combined name; index N to any depth).
  Each populated dependent becomes its own row under the employee, inheriting the
  employee's address/city/state/zip/location to tie the family. Missing dependent
  relationship → defaulted to Child + INFO flag. A comma-less combined name is split
  "First Last" with an INFO flag to verify. (The dependent combined-name split
  shares the same name parser as §11c — identical suffix and middle-initial rules.)
- If a file uses a single generic value column coded only by Product (no per-product
  columns), it is left for manual handling rather than guessed.

## 11b. Family roll-up (IMPLEMENTED)

After the rows are built, families are grouped by **adjacency** — an Employee row
plus the Spouse/Domestic Partner/Child rows beneath it, until the next Employee.
Onto the employee row the engine rolls: spouse's voluntary life → Spousal
Voluntary Life; child's → Dependent Voluntary Life; dependent basic life →
Dependent Basic Life (those are blanked on the dependent rows). The employee's
Dental/Vision Coverage Type is **derived per product from who is enrolled in that
product** (a dependent who waived that product does NOT count) — spouse+child →
FAM, spouse → ES, child → EC. Upgrade-only: never lowers a stated tier.
Demographics stay on the dependent rows. Every roll-up is flagged INFO to verify.

**Only employee-line data is uploaded.** After the roll-up, spouse / domestic
partner / child rows have all election, employment, AND address/contact fields
blanked (coverage types, plans, life, disability, beneficiary, salary, occupation,
hire, location, street/city/state/zip, phone, email). Dependents keep only
Relationship, name, Middle Initial, gender, DOB, and Disabled?.

## 11c. Combined-column split — "data extrapolation" (IMPLEMENTED)

Some carriers consolidate several fields into ONE column. This split runs as the
**first stage of the pre-pass** (before §11 Stage 0 product routing, collapse,
and dependent explode, so person-keying sees the split First/Last), detects
combined columns by header + value shape, and extrapolates them into the
separate canonical fields. Per §10: transform only when confident; otherwise
flag and pass through untouched.

### Shapes

| # | Shape | Example values | Split into |
|---|-------|----------------|------------|
| 1 | Combined name | `Smith, John A` / `Jane Doe` | Member Last Name, First Name, Middle Initial |
| 2 | City/State/Zip | `Austin, TX 78701` / `Round Rock TX 78664-1234` | City, State, Zip Code |
| 3 | Full address | `123 Main St, Apt 4, Austin, TX 78701` | Member Street Address, City, State, Zip Code |

### Detection (header candidates + value-shape confidence)

Confidence = fraction of **non-blank** cells in the column that parse (full
scan). Thresholds: **60%** for a named combined header (`SPLIT_CONFIRM`),
**90%** for a value-shape-only trigger on a plainly-named column
(`SPLIT_STRONG`), **70%** for full address, which is always value-gated
(`SPLIT_ADDR`). Columns with fewer than 5 non-blank values require **100%**
parse for value-shape-only triggers. Between **30%** (`SPLIT_HINT`) and the
threshold → WARNING flag, **no transform** (the column passes through as
today). Below 30% → nothing.

- **Shape 1 — combined name.** Header candidates: `NAME`, `FULL NAME`,
  `EMPLOYEE NAME`, `EMP NAME`, `MEMBER NAME`, `ENROLLEE NAME`,
  `SUBSCRIBER NAME`, `PARTICIPANT NAME`, and other `* NAME` headers except
  FIRST/LAST/MIDDLE/CARRIER/PLAN/PROVIDER/BENEFICIARY/PCP/OFFICE/FACILITY
  names. **Guard:** if separate First AND Last Name columns exist, the combined
  column is left alone (INFO note) — the explicit columns win.
- **Shape 2 — City/State/Zip.** Header candidates: `CITY STATE ZIP`,
  `CITY ST ZIP`, `CSZ`, `CITY STATE`, `CITY AND STATE` (zip optional when the
  header lacks ZIP). Also triggers on a plain `CITY`/`TOWN` header when ≥90% of
  values are full `City, ST 12345` shapes (zip + valid state required).
- **Shape 3 — full address.** Candidate columns: `ADDRESS`, `FULL ADDRESS`,
  `HOME ADDRESS`, `MAILING ADDRESS` and Address-column variants. Header alone
  is never sufficient (an `Address` column is normally street-only): requires
  ≥70% of values to parse as street + city + valid state + zip with a
  comma-delimited city. **Guard:** skipped entirely when separate City AND
  State AND Zip columns exist; only the first qualifying column splits (other
  address columns keep merging into the street per §4).

### Parsing rules

- **Name:** a trailing suffix (JR/SR/II/III/IV/V, with or without periods) is
  detached and re-appended to the last name. Comma present → `Last, First
  [Middle]` (reliable). No comma → guessed: 2 tokens = `First Last`; 3 tokens
  with a 1-character middle = `First MI Last`; 3+ tokens otherwise = first word
  is First Name, the rest is Last Name (protects `Mary Van Buren`); 1 token =
  Last Name only + WARNING.
- **City/State/Zip and full address parse right-to-left:** trailing zip
  (`12345` or `12345-6789`), then a trailing valid USPS 2-letter code or full
  state name (longest match first — `DISTRICT OF COLUMBIA`), remainder = city
  (full address: last comma segment = city, the rest = street). Values are
  stored raw; the normal §4 field rules (UPPERCASE, USPS conversion, zip-5)
  apply downstream.

### No silent loss

- The consumed combined column is removed from mapping but its raw values stay
  on `RAW DATA` (Office Script) / the source file (siblings), and every
  executed split is logged and flagged INFO with a parsed-row count.
- In a confidently-split column, a row that fails to parse keeps its **whole
  original value** in the primary target field (City for shape 2, Member
  Street Address for shape 3, Member Last Name for a one-word name) + WARNING.
- Comma-less name rows get ONE column-level INFO with a count ("split as
  'First [MI] Last' — verify"), not a flag per row.
- Low-confidence columns (30%–threshold) are flagged WARNING and passed
  through unchanged for the user to decide.

## 12. Roadmap (not yet implemented)

- **v2+** — duplicate merge (currently flagged), per-product dependent enrollment
  for tier derivation, family linking by subscriber-SSN / sequence-number (today:
  adjacency), beneficiary refinements.
- **v3** — richer NCAM column selection + reconciliation guarantees.
- **v4** — expanded SUMMARY (per-plan, per-tier, enrolled/waived/eligible).

---

### Changelog
- **v1.1** (2026-07-06) — added §11c combined-column split ("data
  extrapolation"): combined Name, City/State/Zip, and full-address columns are
  split into separate canonical fields; §4 combined-columns row; dependent
  name split now shares the §11c name parser.
- **v1.0** (2026-06-29) — initial canonical spec, extracted from the LMG/NCAM
  Office Script and the Copilot requirements transcript.