​

Attribution Delta Root Cause Analysis

​

September 2025: Why Repo B Pays $1,925.71 More to Agents Than Repo A

​

Executive Summary

• Canyon Plumbing LLC: Repo A pays $553.84 total, Repo B pays$2,408.00 total
• Agent2_PEPM authoritative rates: KEN YOUNG = $10, KENNY YOUNG =$4
• Repo B calculation: ($10 × 12/12) × 172 rows =$1,720 (KEN) + ($4 × 12/12) × 172 rows =$688 (KENNY) = $2,408 ✅
• Repo A calculation: $395.60 (KEN) +$158.24 (KENNY) = $553.84 ❌ (uses ~$2.30 and $0.92 per row instead of$10 and $4)

​

Detailed Analysis

​

1. Business-Level Attribution Deltas (Top 5)

​

2. Deep Dive: Canyon Plumbing LLC

​

Agent2_PEPM Authoritative Rates

KEN YOUNG:     PEPM = $10.00
KENNY YOUNG:   PEPM = $4.00

​

Repo A Stage3 Breakdown

Business: CANYON PLUMBING LLC
├─ KEN YOUNG:     $395.60 (absorbed_count=172, PEPM_rate_clean=NULL)
├─ KENNY YOUNG:   $158.24 (absorbed_count=172, PEPM_rate_clean=NULL)
└─ Total:         $553.84

Calculation Implied:
  KEN YOUNG:     $395.60 / 172 = $2.30 per row
  KENNY YOUNG:   $158.24 / 172 = $0.92 per row

If normalized_pepm = $2.30, then PEPM = ($2.30 × 12) / 12 = $2.30
But agent2_pepm says PEPM = $10.00 ❌ MISMATCH

​

Repo B Stage3 Breakdown

Business: CANYON PLUMBING LLC
├─ KEN YOUNG:     $1,720.00 (row_count=172, employee_count=52)
├─ KENNY YOUNG:   $688.00   (row_count=172, employee_count=52)
├─ Richard Ballard: $69.59  (owner residual)
└─ Total Agent:   $2,408.00

Calculation:
  KEN YOUNG:     normalized_pepm = ($10 × 12) / 12 = $10.00
                 agent_total = $10.00 × 172 rows = $1,720.00 ✅
  
  KENNY YOUNG:   normalized_pepm = ($4 × 12) / 12 = $4.00
                 agent_total = $4.00 × 172 rows = $688.00 ✅

​

Root Cause: PEPM Rate Mismatch

• pepm_rate_clean is NULL in stage3_snapshots for Canyon Plumbing
• Stored agent_total values ($395.60,$158.24) imply PEPM rates of $2.30** and **$0.92
• These rates are incorrect (should be $10 and$4 per agent2_pepm)

• Uses PEPM rates from agent2_pepm.csv directly
• Calculates normalized_pepm = (pepm_rate × 12) / pay_periods
• Calculates agent_total = normalized_pepm × row_count
• Matches authoritative agent2_pepm rates ✅

​

3. Row-Count Basis Check

• Repo A: agent_total = tpa_per_pay_amount × absorbed_count (where absorbed_count = surviving credit rows)
• Repo B: agent_total = normalized_pepm × row_count (where row_count = transaction rows)

• Repo A: Reports 344 employees total (172 per agent)
• Repo B: Reports 104 employees (distinct member_ids), 344 rows
• Employee count is for reporting only; commission uses row_count ✅

​

4. Agent Eligibility Rules

• KEN YOUNG ✅
• KENNY YOUNG ✅

• HAWAIIAN HUT businesses: Repo B includes GREG DEAN (not in Repo A)
• This accounts for $66 total delta ($36 + $20 +$10)

​

5. Reconciliation Against Dashboard Truth

• Agent Payout Net: $10,534.29
• Owner Net: $18,065.41

• Agent Payout Net: $12,460.00
• Owner Net: $16,139.70

• Repo B uses authoritative PEPM rates from agent2_pepm.csv
• Repo A uses incorrect/stale PEPM rates (NULL or wrong values in stage3_snapshots)
• Repo B is correct; Repo A is underpaying agents

​

Root Cause Explanation

​

Why Does Repo B Pay $1,925.71 More to Agents?

1. Authoritative PEPM rates (agent2_pepm.csv):
   • KEN YOUNG: $10.00
   • KENNY YOUNG: $4.00
2. Repo B calculation (correct):
   • KEN YOUNG: ($10 × 12/12) × 172 rows = **$1,720.00**
   • KENNY YOUNG: ($4 × 12/12) × 172 rows = **$688.00**
   • Total: $2,408.00
3. Repo A calculation (incorrect):
   • KEN YOUNG: **$395.60** (implies PEPM ≈$2.30)
   • KENNY YOUNG: **$158.24** (implies PEPM ≈$0.92)
   • Total: $553.84
4. Delta: $2,408.00 -$553.84 = **$1,854.16** (accounts for 96$1,925.71 delta)

• pepm_rate_clean is NULL in stage3_snapshots for Canyon Plumbing
• Stored agent_total values suggest PEPM rates of $2.30 and$0.92 (incorrect)
• Possible causes:
  1. Stale data (stage3_snapshots loaded before PEPM rates were updated)
  2. PEPM rate lookup failure during Repo A processing
  3. Manual override or data corruption in stage3_snapshots

​

Decision Recommendation

​

Option A: Adjust Repo B Logic to Match Repo A ❌ NOT RECOMMENDED

• Repo A is using incorrect PEPM rates
• Matching Repo A would underpay agents by $1,925.71
• Violates audit-grade requirement to use authoritative PEPM rates
• Would require Repo B to ignore agent2_pepm.csv and use stale/wrong rates

​

Option B: Declare Repo A Underpaying Agents ✅ RECOMMENDED

• Repo B uses authoritative PEPM rates from agent2_pepm.csv
• Repo A uses incorrect/stale PEPM rates (NULL or wrong values)
• Repo B calculation matches agent2_pepm rates exactly
• Repo A calculation implies PEPM rates that don’t match agent2_pepm

• Canyon Plumbing: Repo A uses ~$2.30 and$0.92 PEPM; agent2_pepm says $10 and$4
• Repo B calculation: ($10 × 12/12) × 172 =$1,720 ✅ matches agent2_pepm
• Repo A calculation: $395.60 / 172 =$2.30 per row ❌ doesn’t match agent2_pepm

1. Fix Repo A to use authoritative PEPM rates from agent2_pepm.csv
2. Regenerate Repo A stage3_snapshots for September 2025 with correct PEPM rates
3. Re-run Repo A payroll for September to correct agent payouts
4. Keep Repo B unchanged (it’s already correct)

​

Regression Guardrail

​

Target: September 2025 Parity

• Agent Delta: $1,925.71 (Repo B > Repo A)
• Owner Delta: -$1,925.71 (Repo B < Repo A)
• Invariant: ✅ gross = agent + owner + chargebacks (preserved)

• Agent Delta: ≤ $0.01 ✅
• Owner Delta: ≤ $0.01 ✅
• Invariant: ✅ gross = agent + owner + chargebacks

def validate_september_2025_parity(repo_a_agent, repo_b_agent, tolerance=Decimal("0.01")):
    """
    Validate that Repo A and Repo B agent payouts match within tolerance.
    
    After Repo A is fixed to use authoritative PEPM rates, this should PASS.
    """
    delta = abs(repo_b_agent - repo_a_agent)
    assert delta <= tolerance, f"Agent delta ${delta:,.2f} exceeds tolerance ${tolerance:,.2f}"
    return True

​

Files Modified

​

Next Steps

1. Fix Repo A to use authoritative PEPM rates from agent2_pepm.csv
2. Regenerate Repo A stage3_snapshots for September 2025
3. Re-run parity validation to confirm delta ≤ $0.01
4. Document the fix in Repo A codebase
5. Add regression test to prevent PEPM rate mismatches in the future

​

Conclusion