Skip to main content

Customer Health Metrics

Core Metrics​

All metrics are tracked per company and updated according to the schedules below.

1. Number of Employees (numberofemployees)​

Description: Total number of active (non-archived) employees in the company.

Purpose: Track company size and growth/contraction trends.

Calculation:

Count of Employees where archived = false

Update Frequency: Real-time (instant)

Triggers:

  • When an employee is hired (created)
  • When an employee is archived
  • When an employee is unarchived

Firebase Path: Employees/{companyId}/

Alert Thresholds:

  • 🔴 Decreased by >20% in 30 days (potential churn signal)
  • 🟡 Decreased by 10-20% in 30 days (watch closely)
  • 🟢 Stable or growing

Dashboard Display:

  • Current employee count
  • 30-day trend (↑ +5, ↓ -2, → 0)
  • Sparkline chart showing last 90 days

2. Last Master Account Login (last_login_date_master_account)​

Description: Date and time of the most recent login by the company's master account (admin email).

Purpose: Identify inactive accounts that may need re-engagement.

Calculation:

Latest timestamp from PresenceStatus table
  where userId = master account userId

Update Frequency: Real-time (instant)

Triggers:

  • When master account user logs into Pivot (web or mobile)
  • Tracked via PresenceStatus table (not legacy lastSeen field)

Firebase Path: PresenceStatus/{userId}/lastActive

Alert Thresholds:

  • 🔴 No login in >14 days (high risk)
  • 🟡 No login in 7-14 days (moderate risk)
  • 🟢 Login within last 7 days

Dashboard Display:

  • "Last seen: 3 days ago"
  • Exact timestamp on hover
  • Color-coded status indicator

Important Notes:

  • Uses PresenceStatus table (not Employees/{employeeId}/lastSeen)
  • Only tracks master account email, not all admin users
  • Master account email found in company profile

3. Open Shifts >10% (quarts_de_travail_ouverts_10_)​

Description: Whether the company has more than 10% of shifts unfilled in DRAFT schedules over the last 14 consecutive days.

Purpose: Identify scheduling inefficiencies and potential understaffing issues.

Calculation:

For each of the last 14 days:
  - Count regular shifts (after "Generate Schedule" is clicked)
  - Count open shifts (unassigned)
  - Daily % = (open shifts / regular shifts) × 100

Average all 14 daily percentages

Result:
  - "Yes" if average > 10%
  - "No" if average ≤ 10%
  - "None" if no schedules exist

Update Frequency: Daily at 6:00 AM Toronto time (cron job)

Scope:

  • Only DRAFT schedules (before publishing)
  • Requires "Generate Schedule" to be clicked (otherwise regular shifts = 0)
  • Rolling 14-day window

Firebase Path: Schedules/{companyId}/{weekId}/shifts/

Alert Thresholds:

  • 🔴 Average >20% (critical staffing issue)
  • 🟡 Average 10-20% (monitor closely)
  • 🟢 Average <10%

Dashboard Display:

  • "Open Shifts: 15.3% (Last 14 days)"
  • Trend chart showing daily percentages
  • Drill-down to see which days/departments affected

Testing Requirements:

Create 3 shifts × 14 consecutive days = 42 shifts:
  - 1 shift assigned to available employee
  - 1 shift assigned to employee on unavailable day
  - 1 shift left open (unassigned)

Expected: numberOfRegularShifts = 2, numberOfOpenShifts = 1
Result: 50% > 10% threshold → "Yes"

4. Shifts Assigned Outside Availability >10% (quarts_de_travail_attribues_hors_dispo_10)​

Description: Whether more than 10% of shifts are assigned to employees on their UNAVAILABLE days in PUBLISHED schedules over the last 14 consecutive days.

Purpose: Detect poor scheduling practices that can lead to employee dissatisfaction and conflicts.

Calculation:

For each of the last 14 days:
  - Count total published shifts
  - Count shifts assigned when employee availability is empty
  - Daily % = (unavailable shifts / total published shifts) × 100

Average all 14 daily percentages

Result:
  - "Yes" if average > 10%
  - "No" if average ≤ 10%
  - "None" if no published schedules

Update Frequency: Daily at 6:00 AM Toronto time (cron job)

Scope:

  • Only PUBLISHED schedules (after publishing)
  • Only MANUALLY assigned shifts (not automatically scheduled)
  • Conflict = shift assigned when employee availability is completely empty for that day
  • Rolling 14-day window

Firebase Path:

  • Schedules/{companyId}/{weekId}/shifts/
  • Availability/{employeeId}/

Alert Thresholds:

  • 🔴 Average >20% (serious scheduling problems)
  • 🟡 Average 10-20% (needs attention)
  • 🟢 Average <10%

Dashboard Display:

  • "Unavailable Assignments: 12.5% (Last 14 days)"
  • List of affected employees
  • Heatmap showing which days had conflicts

Important Notes:

  • Automatic scheduling respects availability (max 30min early, strict end time)
  • This metric catches manual overrides only
  • Employee names highlighted in YELLOW in Pivot UI indicate these conflicts

5. Conflicting Clock-Ins >50% (quarts_problematiques_50)​

Description: Whether more than 50% of clock-in records have conflicts in the payroll module over the last 14 consecutive days.

Purpose: Identify data quality issues with time tracking that affect payroll accuracy.

Calculation:

For each of the last 14 days:
  - Count total clock-ins (timecards)
  - Count clock-ins with conflicts
  - Daily % = (conflicting clock-ins / total clock-ins) × 100

Average all 14 daily percentages

Result:
  - "Yes" if average > 50%
  - "No" if average ≤ 50%
  - "None" if no timecards exist

Update Frequency: Daily at 6:00 AM Toronto time (cron job)

What is a "Conflicting Clock-In"?

  • Timecard that doesn't match the scheduled shift
  • Missing clock-in or clock-out
  • Clock-in time significantly different from shift start/end
  • Overlapping shifts for same employee

Firebase Path: Timecards/{companyId}/

Alert Thresholds:

  • 🔴 Average >70% (payroll data integrity crisis)
  • 🟡 Average 50-70% (significant issues)
  • 🟢 Average <50%

Dashboard Display:

  • "Conflicting Timecards: 55.2% (Last 14 days)"
  • Breakdown by conflict type
  • Most affected employees/departments

Threshold Rationale:

  • 0% conflicts is ideal but unrealistic due to last-minute changes
  • 50% chosen to avoid alert fatigue
  • Threshold can be adjusted based on observed patterns
  • Initial testing may suggest lowering to 30%

Cancelled Metrics​

❌ Timecards in Last 14 Days (punches_last_14_days)​

Status: Cancelled Reason: Replaced by more specific "Conflicting Clock-Ins" metric

❌ Schedule Published in Last 14 Days (schedule_published_last_14_days)​

Status: Cancelled Reason: Too broad; replaced by "Open Shifts" metric which is more actionable

Company Matching Logic​

To identify which companies to track, the system matches Pivot companies using:

Matching Criteria (Both Required)​

  1. Company Name - Exact match (case-sensitive)
  2. Master Account Email (courriel_compte_maitre) - Exact match

Matching Process​

1. When new company created in Pivot → Check Firebase for company data
2. Match company.name AND company.email against Firebase records
3. If both match → Link company for metric tracking
4. If no match or duplicate matches → Log to #support-logs Slack channel
5. Create link in Firebase: Companies/{companyId}/csmDashboardLinked = true

Firebase Paths​

  • Company name: Companies/{companyId}/name
  • Master account email: Companies/{companyId}/email

Edge Cases​

  • No match: Company not tracked (manual review)
  • Multiple matches: Alert sent to Slack, manual resolution required
  • Email change: Update tracked in real-time, link maintained

Metric Calculation Architecture​

Real-time Metrics​

  • numberofemployees
  • last_login_date_master_account

Implementation: Firebase Cloud Functions triggered on data changes

onEmployeeCreate → Update company employee count
onEmployeeArchive → Update company employee count
onPresenceStatusUpdate → Update last login timestamp

Batch Metrics (Cron Job)​

  • quarts_de_travail_ouverts_10_
  • quarts_de_travail_attribues_hors_dispo_10
  • quarts_problematiques_50

Implementation: Scheduled Cloud Function runs daily at 6:00 AM Toronto time

calculateDailyMetrics() {
  for each company:
    - Fetch last 14 days of schedules/timecards
    - Calculate daily percentages
    - Compute 14-day average
    - Update metric value (Yes/No/None)
    - Store historical data for trending
}

Metric Storage​

All calculated metrics stored in Firebase:

CustomerHealthMetrics/
  {companyId}/
    numberofemployees: 127
    last_login_date_master_account: "2025-11-23T14:30:00Z"
    quarts_de_travail_ouverts_10_: "No"
    quarts_de_travail_attribues_hors_dispo_10: "Yes"
    quarts_problematiques_50: "No"
    lastUpdated: "2025-11-24T06:00:00Z"
    history/
      2025-11-24/
        numberofemployees: 127
        ... (daily snapshot)

Alert Rules​

Critical (Red) - Immediate Action Required​

  • No login in >14 days
  • Employee count decreased >20% in 30 days
  • Open shifts >20%
  • Unavailable assignments >20%
  • Conflicting timecards >70%

Action: Slack notification to #customer-health + Assigned to CSM

Warning (Yellow) - Monitor Closely​

  • No login in 7-14 days
  • Employee count decreased 10-20% in 30 days
  • Open shifts 10-20%
  • Unavailable assignments 10-20%
  • Conflicting timecards 50-70%

Action: Added to CSM daily review list

Healthy (Green) - No Action​

  • All metrics within healthy thresholds

Action: None