BSC calculation — per employee, per calendar month

Audience: Engineering / product (dashboard, BigQuery sync, audits)
Code: api/app/services/bsc_employee_compute.py, api/app/services/balance_score_card_calculation.py
Config: Scorecard → Configurations → Balance Scorecard Metrics (balance-score-card-metrics)

This document describes exactly how one user-month (one employee_id + year + month) is calculated today. Scheduled/manual BigQuery sync must produce the same numbers as this logic.

---

1. Grain and identity

Term	Meaning
User-month	One row of analytics for employment_data.employee_id (scorecard employee_id on entries), not Mongo user._id unless they match by design
Period	Calendar month in UTC (e.g. May 2026 = 2026-05-01 00:00 UTC ≤ date < 2026-06-01 00:00 UTC), plus legacy US date strings (MM/DD/YYYY) on the same field
Config snapshot	KPI tree (KPM weights, KPI keys, source, breakdown %) read from Mongo at compute time — not versioned historically yet

---

2. Which entries count in the month?

All queries use ScorecardDateQuery(date_mode="month", month_values=("YYYY-MM",)).

Collection	Date field	Filter
tbl_sbs_entries	task_evaluated_date	employee_id + month
tbl_non_sbs_entries	task_evaluated_date	same
tbl_atw_entries	date_of_occurrence	same
tbl_attendance_issues	date_of_occurrence	same
tbl_productivity_issues	date_of_occurrence	same

Up to 5,000 documents per collection per batch load (operational limit).

Important: SBS/Non-SBS use evaluation date; ATW / attendance / productivity use occurrence date. A single real-world event can appear in different months depending on page.

---

3. Pipeline (one employee, one month)

1. Load all entries for employee_id in month (5 collections)
2. Resolve working_days (schedule or Mon–Fri fallback)
3. For each KPI in config (by source type) → kpi_score 0–100
4. total_score = weighted sum of kpi_scores (round up 2 decimals)

Performance band (Goal / Expectation / Standard) is not part of user-month compute; dashboard applies it only for self view using Total score goals.

---

4. Working days (attendance KPIs only)

resolve_working_days_for_employee(employee_id, year, month)
  → tbl_employee_schedules.working_days if month document exists
  → else weekdays_in_calendar_month(year, month)   # Mon–Fri count

Used only for Tardiness and Absenteeism KPIs (see below).

---

5. KPI formulas (by source)

Each KPI row in config has a source. Built-in keys map in DEFAULT_KPI_SOURCE_BY_KEY if source omitted. custom KPIs are skipped (no score).

All sub-scores use round_bsc_up: round up to 2 decimal places; values ≤ 0 → 0.0.

Code: Productivity KPIs → api/app/services/bsc_productivity_kpis.py. Required SBS/Non-SBS counts → api/app/services/bsc_eval_requirements.py (eval quota tiers + tenure at last day of month).

Productivity KPM (60% of total score)

Four KPIs; weights come from Balance Scorecard Metrics config (defaults: quantity 20%, integrity 25%, accuracy 25%, compliance 30%).

Eval-completion ratio (quantity, integrity, accuracy only)

Month state	Ratio applied?
Current calendar month (rolling dashboard)	No — use the average only
Past calendar months	Yes — multiply average by the ratio below
Finalized month (tbl_bsc_month_reports.status = final)	Yes — same as closed month

Resolved by resolve_apply_eval_ratio_for_month() in bsc_month_report_store.py (finalized or strictly past month vs EST “today”).

Required counts (denominator for quantity / integrity submission ratios):

required_sbs, required_non_sbs = eval_quotas tier lookup(
  total_months_for_tenure(hire_date, last_day_of_target_month)
)

Actual counts = number of SBS / Non-SBS rows in the month for that employee (not “scorable” subset).

sbs_non_sbs_quantity — Quantity of output (BSC 2025 only)

BSC 2025 (quantity_of_output, source sbs_non_sbs_quantity):

1. Base = mean of quantity_score (0–1) across all SBS + Non-SBS entries in the month × 100.
2. If ratio applies: base × min(1, (actual_sbs + actual_non_sbs) / (required_sbs + required_non_sbs)).

task_delivery — Quantity of output (BSC 2026+)

BSC 2026+ maps KPI key quantity_of_output to source task_delivery:

1. Workdays = scheduled task-delivery dates for the employee-month through today (from tbl_employee_schedules + day code is_task_delivery_day; no weekday fallback).
2. Per day: min(100, finished_task / task_count × 100); past missing workday → 0%; today without entry → pending (excluded); task_count = 0 → exclude day from average.
3. KPI score = average of included daily percents. No eval-completion ratio.

Code: task_delivery/kpis.py, task_delivery/workdays.py. Details: Fiscal cycles & locks §5.

sbs_integrity — Integrity (25%)

BSC 2025 — Non-SBS only:

1. Base = mean of quantity_score on Non-SBS entries × 100.
2. If ratio applies: base × min(1, actual_non_sbs / required_non_sbs).

BSC 2026+ — SBS + Non-SBS: same formula as BSC 2025 quantity (combined eval ratio).

sbs_non_sbs_accuracy — Accuracy (25%)

BSC 2025:

1. Base = mean of auditors_score (0–100) on SBS + Non-SBS rows where auditor score is not N/A.
2. If ratio applies: base × min(1, scorable_count / ((required_sbs + required_non_sbs) − na_count)).

BSC 2026+:

1. Base = mean of checklist score (timeliness − integrity, 0–1) on all submitted SBS + Non-SBS entries × 100.
2. If ratio applies: base × min(1, (actual_sbs + actual_non_sbs) / (required_sbs + required_non_sbs)).
3. Non-SBS entries use the same Yes/No checklist and per-cycle rubric as SBS.

sbs_compliance — Compliance (30%)

BSC 2025 — SBS checklist base:

1. Base = mean of SBS checklist score (0–1, timeliness − integrity) × 100 — not sbs_percentage_score.
2. Penalty = productivity issue count × 1.0 (percentage points).
3. KPI score = max(0, base − penalty).

BSC 2026+:

1. Base = 100%.
2. Penalty = productivity issue count × 1.0.
3. KPI score = max(0, 100 − penalty).

Attendance/TD KPM (20% of total score)

Two KPIs from config (defaults: late_ob_not_working 70%, absenteeism 30%).
Code: api/app/services/bsc_attendance_kpis.py

Working days for the employee-month:

resolve_working_days_for_employee(employee_id, year, month)
  → tbl_employee_schedules for that month if present
  → else Mon–Fri count in the calendar month

Entries: tbl_attendance_issues where employee_id matches and date_of_occurrence falls in the month.

No eval-completion ratio (unlike Productivity quantity/accuracy).

attendance_tardiness → KPI key late_ob_not_working (70%)

• violations = count of rows with type_of_violation == "Tardiness" (Late/OB/Not Working/Undertime/Bio log missed, etc.).
• KPI score = max(0, (working_days − violations) / working_days) × 100.
• working_days <= 0 → 0.

attendance_absenteeism → KPI key absenteeism (30%)

• violations = count of rows with type_of_violation == "Absenteeism".
• Same formula as tardiness.

atw_by_impact (Team / Other Departments / Company)

Three separate KPI keys map to impacts via ATW_IMPACT_BY_KPI_KEY:

KPI key	ATW impact value
impact_to_team	Team
impact_to_other_departments	Other Departments
impact_to_company	Company

Per KPI (per month, per employee):

1. points_earned = sum of final_score on ATW entries with that impact.
2. KPI score = min(100, points_earned) — target is 100 points per impact per month.

---

6. Total BSC score (one employee, one month)

From config tree:

• KPM weight_percent sums to 100 across KPMs.
• KPI breakdown_percent sums to 100 within each KPM.
• Effective weight (share of full scorecard):

effective_kpi_weight = ceil₂( kpm.weight_percent × kpi.breakdown_percent / 100 )

• Contribution per KPI:

contribution = ceil₂( effective_kpi_weight × kpi_score / 100 )

• Total:

total_score = ceil₂( Σ contribution over all KPIs with a computed score )

KPIs with custom source or missing from kpi_scores dict contribute nothing (not treated as 0 unless you add a score of 0).

Default config example (weights):

KPM	Weight	KPIs (breakdown % inside KPM)
Productivity	60%	Quantity 20%, Integrity 25%, Accuracy 25%, Compliance 30%
Attendance/TD	20%	Tardiness 70%, Absenteeism 30%
Attitude Towards Work	20%	Team 40%, Other Depts 20%, Company 40%

---

7. Dashboard vs user-month (aggregation)

The API always computes per employee first (compute_kpi_scores_for_employees).

View	What the UI shows
Self (access level 1)	That employee’s kpi_scores and total_score directly
Team / dept / company (levels 2–5)	Average of per-employee KPI scores (average_kpi_scores), then total_score from those averages using current config weights

Caution: Team average is not “pool all entries then calculate once.” Example: Employee A quantity 80%, Employee B 40% → team quantity KPI shows 60%, not necessarily the quantity score of all SBS rows combined.

Trend chart (6 months): same rules per month, then for aggregate views average of each employee’s total_score per month.

---

8. Additional points (discretionary boosts)

Source: tbl_additional_points_entries — scorecard page Additional Points. Filtered by date_of_occurrence in the user-month (same as ATW/attendance).

Managers record percentage points (1 point = 1%) targeting either a KPI or a KPM from that month’s Balance Scorecard Metrics config. Multiple entries in the same month sum before applying.

Target level	Formula
KPI	kpi_score = min(100, base_kpi_score + sum(points)) for that KPI key
KPM	kpm_total = min(100, weighted_kpi_sum_within_kpm + sum(points)) — individual KPI scores are not changed; only the KPM total and Total BSC contribution use the boosted KPM total

Example (KPM): ATW KPM weighted sum = 0%, manager adds +50 on ATW KPM → ATW KPM total = 50%, Total BSC gains 50% × 20% KPM weight = 10 points.

Where applied:

Path	Behavior
Live Mongo compute	compute_kpi_scores_for_employees + aggregate_total_score(..., kpm_boosts=...)
BigQuery sync	Same compute on sync; stored kpi_score, kpm_total_score, total_score reflect boosts
Dashboard UI	KPM hero shows boosted total with + marker; hover explains base → boost → final. KPI-target boosts show + on affected KPI cells. Re-sync BQ after changes if reading from BigQuery.

Soft-deleted additional-points entries are excluded from compute (same as other entry types).

---

9. What BigQuery sync should store

Per BigQuery dashboard, materialize:

Store	Fields
bsc_employee_month_kpi	employee_id, period_date, kpi_key, kpi_source, kpi_score
bsc_employee_month_summary	employee_id, period_date, total_score, working_days

Recompute when:

• Entries in that month change (create/update/delete/restore on scorecard entry types, including additional points and incidents).
• Config weights/sources change (full backfill recommended).
• Employee schedule working_days for that month changes.

---

10. Edge cases checklist

Case	Behavior
No entries in month	Most KPIs → 0; total may be 0
custom KPI	Omitted from kpi_scores; no contribution to total
Multiple entries same KPI source	Means/averages/sums as defined above
Productivity issues	Count toward compliance penalty only
Additional points	KPI boosts change kpi_score; KPM boosts change kpm_total_score / total_score only
Employee not in scope	Excluded before compute (dashboard access rules)
bsc_status off	Still in missing-excess report contexts; dashboard scope uses active BSC employee list from _load_bsc_employee_contexts

---

11. Eligibility (BSC and bonus)

Eligibility is not part of KPI or total score math. It is stored on the user-month summary row (BigQuery) and shown on dashboards for filtering and reporting.

Flag	When true
BSC eligible	regularization_date ≤ first day of the BSC fiscal cycle containing the month (default cycle start: July 1 of cycle id year)
Bonus eligible	regularization_date ≤ first calendar day of that user-month

Both are false when regularization date is missing.

Why two flags: BSC cycle eligibility is fixed at cycle start (July 1). Bonus programs may need employees who regularize mid-cycle to become eligible in later months of the same cycle — bonus eligibility evaluates per calendar month.

Dashboard labels: BSC Eligible / BSC Ineligible, Bonus Eligible / Bonus Ineligible. Monthly performance bonus filter uses the current calendar month eligibility when filtering cycle data.

Code: bsc_eligibility.py (is_bsc_eligible, is_bonus_eligible).

Bonus qualification

Separate from eligibility. Stored as bonus_qualified on the user-month row (BigQuery), computed at sync. All checks below must pass (qualified: true).

Check	Requirement
Pre-gate	bonus_eligible must be true
Goal band	Total BSC score in Goal remark only
Attendance	No attendance issues in that calendar month
Incidents	No incidents in that calendar month (any type)
Red flags	Every SBS + checklist Non-SBS entry: no red flag = No (N/A ignored)
Timely weekly submission	When weekly cadence applies: enough on-time SBS and Non-SBS entries in each week (see below). Level-5 excused entries count as on-time. Otherwise N/A (passes).

Payout: bonus_eligible && bonus_qualified

Dashboards:

• Scope panel badge (⭐ Bonus Qualified / Not Bonus Qualified), filter, export, and hover breakdown on Balanced Scorecard and related pages.
• Dedicated manager matrix: Bonus qualification dashboard (/dashboard/bsc-bonus) — one row per employee with each gate as a column.

Timely weekly submission (lock schedule vs unlock)

This gate is driven by Scorecard → Configurations → Weekly Locking, not by whether a week is currently locked or unlocked.

When it applies

• If effective_from is set → cadence for calendar months on/after that month (even if hard locking is temporarily off).
• If blank → cadence only while Enable weekly locking is on.
• If neither applies → check passes with detail “Weekly submission cadence does not apply for this month.”

What “on-time” means

1. Required monthly SBS / Non-SBS counts come from eval quotas + tenure (same as operational quotas).
2. Counts are split into per-week increments via the configured quota distribution (defaults: Non-SBS total 5 → W1–W4 2,1,1,1; SBS total 3 → 1,1,1,0; total 2 → 0,1,1,0; total 1 → 0,0,1,0).
3. Each entry is placed in a week by its entry date day-of-month (task_evaluated_date).
4. It counts toward that week’s on-time total only if createdAt < scheduled lock datetime for that week (timezone/clock from config; defaults 8:00 PM Asia/Manila on the configured lock day), or the entry is excused by level 5 (required reason).

Independent of hard lock / unlock

Situation	Can managers create?	Counts for timely bonus?
Week locked on schedule	No (unless level 5)	Creates before the scheduled lock: yes
Admin unlocked the week after the deadline	Yes	Creates after the scheduled lock: no
Admin moves lock day later in config	Uses new schedule for create and for next sync’s on-time math	Yes, if createdAt is before the new scheduled instant

Front-loading later weeks does not help: each week’s increment is checked separately. ATW is weekly-locked for operations but excluded from this bonus gate.

Operational lock rules: Scorecard § Weekly locking.

Code: app/domains/bsc/bonus_qualification.py, app/domains/bsc/timely_weekly_submission.py.

---

12. Code index

File	Role
bsc_employee_compute.py	Month filters, per-employee KPI dict
additional_points.py	Parse entries; KPI boosts; KPM boost maps for aggregation
balance_score_card_calculation.py	Formulas + weighted total (incl. kpm_boosts)
balance_score_card_kpi_source.py	Source types + ATW key map
scorecard_config_store.py	Load metrics config
bonus_qualification.py	Six-gate bonus qualification
timely_weekly_submission.py	Weekly on-time SBS/Non-SBS cadence gate
bsc_dashboard.py	Scope + average + band (self)
bonus_qualification.py	Bonus qualification evaluation + breakdown
sync_bsc_to_bigquery.py	ETL (must match §3–6)