​

Agent View Schema Documentation

​

Overview

​

Data Source

​

Schema

CREATE TABLE stage3_snapshot_history (
  period_label STRING,        -- Month identifier (e.g., '2025-01')
  business_label STRING,      -- Business name
  agent_name STRING,          -- Agent name
  tpa_applied BOOLEAN,        -- Whether TPA commission was applied
  agent_total FLOAT64,        -- Agent commission amount
  employee_count INT64,       -- Employee count for this agent/business
  trunc2_applied BOOLEAN,     -- Whether trunc2 rounding was applied
  created_at TIMESTAMP        -- Record creation timestamp
)
PARTITION BY DATE(created_at)
CLUSTER BY period_label, agent_name, business_label;

​

Key Differences from CEO View

​

Agent View Metrics

​

1. Agent Monthly Commission

• month - Period label
• agent_name - Agent identifier
• business_name - Business name
• total_employees - Employee count
• total_commission - Agent commission
• has_tpa_commission - Whether TPA was applied

​

2. Agent Business Churn (MoM)

• month - Current month
• agent_name - Agent identifier
• business_name - Business name
• previous_month_employees - Employee count in prior month
• current_month_employees - Employee count in current month
• employee_change - Net change in employees
• previous_month_commission - Commission in prior month
• current_month_commission - Commission in current month
• commission_change - Net change in commission
• change_category - Classification: New Business, Lost Business, Growth, Decline, Stable
• employee_change_percent - Percentage change in employees

​

3. Agent Attrition Events

• Same as agent_business_churn_mom, filtered to:
  • change_category = 'Decline'
  • employee_change_percent <= -15.0
  • current_month_employees > 0

​

4. Agent Performance Summary

• agent_name - Agent identifier
• total_businesses - Count of unique businesses
• months_active - Number of months with activity
• total_employees - Sum of all employee counts
• total_commission - Sum of all commissions
• businesses_gained - Count of new businesses
• businesses_lost - Count of lost businesses
• net_business_change - Gained - Lost
• businesses_with_attrition - Count with ≥15% employee loss
• avg_commission_per_employee - Average commission rate

​

Implementation Roadmap

​

Phase 1: Data Pipeline (Not Yet Implemented)

• Create ETL job to load Stage3 snapshots to BigQuery
• Populate stage3_snapshot_history table
• Validate data quality and completeness
• Set up incremental loading for new months

​

Phase 2: SQL Views (Scaffolding Complete ✓)

• Create agent_monthly_commission view
• Create agent_business_churn_mom view
• Create agent_attrition_events view
• Create agent_performance_summary view

​

Phase 3: API Integration

• Add get_agent_churn_data(agent_name, start_month, end_month) to api/bigquery/queries.py
• Add get_agent_performance_summary() endpoint
• Add get_agent_attrition_alerts(agent_name) endpoint
• Create API route in api/routes/agent_dashboard.py

​

Phase 4: Dashboard UI

• Create dashboard/src/pages/agent/index.tsx - Agent dashboard page
• Create dashboard/src/components/AgentChurnTable.tsx - Churn events table
• Create dashboard/src/components/AgentPerformanceCard.tsx - Summary metrics
• Add agent filter dropdown to select specific agent
• Add date range picker for custom period selection

​

Phase 5: Reporting

• Generate Excel export for agent-level churn reports
• Add agent churn tab to existing executive reports
• Create automated alerts for agents with high attrition

​

Dashboard Mockup

​

Agent Dashboard Page Structure

┌─────────────────────────────────────────────────────────┐
│  Agent Dashboard                          [Agent: John] │
├─────────────────────────────────────────────────────────┤
│                                                          │
│  Performance Summary (Jan-Aug 2025)                     │
│  ┌──────────┬──────────┬──────────┬──────────┐         │
│  │ Total    │ Total    │ Avg Comm │ Net Biz  │         │
│  │ Biz: 25  │ Comm:    │ per Emp: │ Change:  │         │
│  │          │ $50,000  │ $125     │ +3       │         │
│  └──────────┴──────────┴──────────┴──────────┘         │
│                                                          │
│  Business Churn (Month-over-Month)                      │
│  ┌────────────────────────────────────────────┐         │
│  │ Month    │ Business    │ Employees │ Chg%  │         │
│  ├──────────┼─────────────┼───────────┼───────┤         │
│  │ Feb 2025 │ ABC Corp    │ 50 → 40   │ -20%  │         │
│  │ Mar 2025 │ XYZ Ltd     │ 30 → 0    │ -100% │         │
│  │ Apr 2025 │ DEF Inc     │ 0 → 25    │ +100% │         │
│  └────────────────────────────────────────────┘         │
│                                                          │
│  Attrition Alerts (≥15% Employee Loss)                  │
│  ┌────────────────────────────────────────────┐         │
│  │ ⚠️ 3 businesses need attention this month  │         │
│  │ → ABC Corp: -20% employees                 │         │
│  │ → GHI LLC: -18% employees                  │         │
│  │ → JKL Co: -15% employees                   │         │
│  └────────────────────────────────────────────┘         │
└─────────────────────────────────────────────────────────┘

​

Integration with Existing Systems

​

CEO View vs Agent View

• Purpose: Executive-level visibility into overall business health
• Data: Stage1 snapshots (raw employee data)
• Metrics: Total employees, total commission, business-level churn
• Audience: CEO, CFO, Executive team

• Purpose: Agent-level performance tracking and client relationship management
• Data: Stage3 snapshots (post-TPA agent commissions)
• Metrics: Agent-specific book of business, client gains/losses, attrition alerts
• Audience: Agents, Sales managers, Operations team

​

Data Flow

┌──────────────┐
│  Stage1      │  Raw payroll data
│  Snapshot    │  → CEO View (business-level)
└──────────────┘

        ↓ TPA normalization + agent commission allocation

┌──────────────┐
│  Stage3      │  Agent commission breakdown
│  Snapshot    │  → Agent View (agent × business-level)
└──────────────┘

​

API Endpoint Specifications (Future)

​

1. Get Agent Churn Data

GET /api/agent/churn?agent_name={name}&start={start_month}&end={end_month}

Response:
{
  "agent_name": "John Doe",
  "period": "2025-01 to 2025-08",
  "churn_events": [
    {
      "month": "2025-02",
      "business": "ABC Corp",
      "change_category": "Decline",
      "employee_change": -10,
      "employee_change_percent": -20.0,
      "commission_change": -500.00
    },
    ...
  ]
}

​

2. Get Agent Performance Summary

GET /api/agent/performance?agent_name={name}

Response:
{
  "agent_name": "John Doe",
  "total_businesses": 25,
  "total_commission": 50000.00,
  "businesses_gained": 5,
  "businesses_lost": 2,
  "net_business_change": 3,
  "businesses_with_attrition": 3,
  "avg_commission_per_employee": 125.00
}

​

3. Get Agent Attrition Alerts

GET /api/agent/alerts?agent_name={name}&threshold=15

Response:
{
  "agent_name": "John Doe",
  "alert_count": 3,
  "alerts": [
    {
      "business": "ABC Corp",
      "month": "2025-08",
      "employee_change_percent": -20.0,
      "severity": "high"
    },
    ...
  ]
}

​

Testing Checklist

​

Data Validation

• Verify Stage3 data loads correctly to BigQuery
• Confirm employee counts match between Stage1 and Stage3 aggregates
• Validate commission totals against existing reports
• Check for duplicate records

​

View Accuracy

• Compare agent_business_churn_mom results with manual calculations
• Verify attrition threshold (≥15%) is applied correctly
• Confirm change_category logic matches business rules
• Validate agent performance summary totals

​

API Testing

• Test all API endpoints with valid agent names
• Test error handling for invalid agent names
• Verify date range filtering works correctly
• Check response time for large datasets

​

UI Testing

• Verify agent dropdown populates with all agents
• Test churn table sorting and filtering
• Confirm performance cards display correct metrics
• Validate responsive design on mobile devices

​

Notes

• Stage3 data is already available in data/archive/{month}/stage3_snapshot.csv
• BigQuery upload needs to be implemented (see integration/bigquery/scripts/etl_loader.py)
• SQL views are ready and can be deployed once Stage3 data is in BigQuery
• Dashboard scaffolding (placeholder tab) is included in CEO churn workbook
• No breaking changes to existing CEO view or reports

​

Next Steps

1. Review and approve SQL view logic
2. Implement Stage3 data ETL to BigQuery
3. Deploy SQL views to BigQuery
4. Build API endpoints for agent queries
5. Create React dashboard components
6. User acceptance testing with agent team
7. Production deployment