Diagnosing and fixing a broken employee-commission match.

Problem / decision addressed: Commission records only carried free-text employee names, while payroll used email-based employee IDs. Reconciliation broke on role tags, typos, middle names, name-order mismatches, and formatting noise. The decision question was whether the commission-to-employee join could be trusted for executive reporting without a manual spreadsheet review every cycle.

Research approach and methodology: This was data and systems usability research. I started with the actual workflow failure, queried unmatched records, categorized root-cause patterns, tested deterministic normalization rules, and designed a confidence waterfall that made every match method visible and auditable.

Key findings and insights: Most failures were not unsolvable identity problems. They were predictable text-pattern failures: parenthetical role tags, inconsistent token order, extra middle names, and a smaller set of true exceptions requiring governed overrides.

Impact on product, design, or business decisions: The fix turned commission reconciliation from an opaque manual process into a queryable warehouse workflow. Finance and operations could see match confidence by row, preserve raw-to-joined totals, review explicit UNMATCHED exceptions, and trust the reporting layer before using it in executive decisions.

Ran the naive join first and inspected rows where join_status = 'UNMATCHED' instead of guessing the failure mode.

Categorized unmatched records into root causes: parenthetical role tags, token-order differences, middle-name noise, and true typos.

Built analytics.dim_employee as the canonical employee dimension with email as the authoritative employee_id.

Created deterministic name_key_full and name_key_firstlast fields using REGEXP_REPLACE and REGEXP_SPLIT_TO_ARRAY.

Added analytics.employee_commission_name_map so reviewed exceptions are governed, documented, and auditable.

Rebuilt analytics.commission_2025_joined_payroll with match_method and join_status diagnostics.

Validated the workflow with raw-to-joined commission total checks and match_method count summaries.

Kept unmatched records visible as a review queue rather than dropping them from reporting outputs.