# Metric definitions & reconciliation notes

How each validated metric is defined **as we ingest it**, how that differs from NYC's
official **School Quality Reports (SQR) / Snapshot** presentation, and what is (and
isn't) reconcilable. Written from the validation work in `verify/`
(see `docs/design/05_validation_spec.md`, `docs/design/06_mwg1378_packet.md`). Sources
for the SQR definitions: the per-school SQR PDFs
(`nycenet.edu/OA/SchoolReports/<year>/<DBN>_{SQR_Scoring_Guide|School_Quality_Guide}_<yy>_<EMS|HS>.pdf`)
and the per-year *Educator Guide to the School Quality Reports* (InfoHub).

> Scope note: this records what is **verified** (a quoted definition, or a measured
> result) vs what is **not established**. Unexplained gaps are labeled as such, with
> ways to narrow them — not filled with a plausible mechanism.

## The four validation checks (terms used throughout)
- **(i) Completeness** — are years or schools missing?
- **(ii) Correctness — base case** — does the DB match an independent re-read of the **exact source file the loader ingested**? *(the binding correctness gate)*
- **(iii) Correctness — spot check** — does the DB match NYC's **public-facing per-school report** (the School Snapshot / SQR)? *(a presentation cross-reference — see the caveat below; not an independent oracle)*
- **(iv) Correctness — computed values** — are derived metrics (composites, survey rollups) computed correctly from validated inputs? *(not done yet)*

## Core distinction (applies to every metric)
We ingest the **raw, all-tested / all-enrolled** figures published in the NYCDOE
InfoHub bulk files (and NYC Open Data for graduation). NYC's SQR/Snapshot publishes
figures on an **accountability cohort** — students *attributed to a school by its
October-31 audited register* ("N: the number of students contributing to the metric") —
rounded to whole percents. So our values and the Snapshot's are **different population
cuts of the same underlying data**, not competing measurements, and the two N's can
differ in either direction (see Test proficiency). The operative cut is per-metric:
for the test metrics it is the documented accountability cohort; for chronic
absenteeism it turned out to be the **grade band** (K–8 / 9–12 — see that section).
The **base case (ii)** is binding.
The **spot check (iii)** is the Snapshot — NYC's *own* report (same data family), so it
is **not** an independent ground-truth oracle; a truly independent publisher (NYSED) is
a separate, deferred check.

## Per-metric definitions

### Test proficiency — `ela_grade3/7_proficiency`, `math_grade3/7_proficiency`, `*_all_proficiency`
- **Ours (InfoHub / NYS 3–8 tests):** `% Level 3+4` = (# scoring Level 3 or 4) ÷ (# tested), for **all tested students** at the grade. `*_all_proficiency` is the file's "All Grades" aggregate.
- **(ii) Base case — exact.** Served % = the file's %L3+4 with **0 mismatches** on every metric: g3 ELA 4,664 / Math 4,663; g7 2,765 / 2,759; school-wide `ela_all` 6,626 (6,565 + 61 suppressed) / `math_all` 6,626 (6,449 + 177). The file's "All Grades" Number Tested = sum of its grade 3–8 counts (no aggregation artifact). Reports: `docs/qa_reports/*_proficiency__validation.md`.
- **Snapshot/SQR figure.** `val_prof_pct_*_all` (% Level 3+4), plus "Average Student Proficiency" (a 1–4 mean — the *scored* metric; %L3+4 is weighted 0%). The SQR PDF "% at Level 3 or 4" and the snapshot API `val_prof_pct_ela_all` are the **same figure** (verified 57/57 schools across 2022-23/2023-24/2024-25: identical N; the snapshot rounds the % to an integer).
- **Denominator (documented, stable 2021-22 → 2024-25)** — per each year's EMS Educator Guide: students who **(a) are on the school's October-31 audited register AND (b) took the exam**; attributed by the Oct-31 register.
- **Verified N relationship (SQR-PDF N vs InfoHub "Number Tested"):** 2022-23 SQR N **>** InfoHub (≤ in 6/19; mean ≈ +5); 2023-24 **>** (≤ in 5/19; e.g. 15K136 497 vs 469); 2024-25 mostly **≤** (≤ in 44/50). So the cohort is **not** a subset of test-takers and is **not** bounded by "Number Tested."
- **(iii) Spot check — agreement, stated honestly (two different checks; don't conflate):**
  - *Snapshot-API reconciliation* (committed; all years; 300-school stratified sample; ±1.5pp): **`ela_all` 73% agreement (216/295; 79 mismatch); `math_all` 62% (181/290; 109 mismatch)**. Central tendency is tight (median gap +0.4pp ELA / 0.0pp math), but the distribution has a **wide tail**: in-sample signed gaps run −3 to +11pp (ELA) and −13 to +19pp (math). Reports: `docs/qa_reports/*_all_proficiency__snapshot.md`.
  - *SQR-PDF hand-check* (recent-year-weighted subset): mean |SQR%−InfoHub%| ~0.6–1.3pp; 88% within 1pp in 2024-25. **That "~1pp" is this small recent-year subset — not the overall agreement.**
- **Documented explanation of the N difference + year-flip (attribution rules, not the formula):**
  - *Out-of-district testers — the main driver of the flip.* Per the InfoHub test-file `NOTES`: "Starting in **2025**, students enrolled in a NYC PS school outside of their zoned district are attributed to the school where the exam was administered. For **prior years**, these students were considered Out of District Placement testers and **attributed separately**." So pre-2025 InfoHub **drops** out-of-district testers from a school's count while the SQR (register) **includes** them → SQR > InfoHub; from 2024-25 InfoHub includes them at the exam school → SQR ≤ InfoHub. Matches the observed direction.
  - *NYSED-style exclusions.* Per the SQR Educator Guide, NYSED (and InfoHub, which reports NYS results) excludes IEP K-8 students whose home address is in a different community school district, and students not continuously enrolled BEDS-Day→exam; NYCPS/SQR includes them.
  - *Math double-testing waiver (2024-25, grades 7–8 math).* NYCPS imputes proficiency for Regents-takers and includes them; InfoHub counts only State-test-takers.
  - **Residual — direction explained, tail size NOT established.** The documented rules account for the *direction* of the N difference and the 2024-25 flip, but **not** the size of the largest individual gaps. The big disagreements are **not** just small-N noise: math `16K267` 2023-24 is +18.9pp at **N=89**, math `14K318` 2022-23 is −9.4pp at **N=439**, absenteeism `28Q157` 2018-19 is −12pp at **N=1693**. A small N *can* amplify a gap (one student ≈3pp at N≈34 — e.g. ELA `13K054` 2024-25 +7.8pp at N=34), but that does not explain the large-N cases. Way to narrow: per-school-year decomposition of snapshot N vs InfoHub N by attribution group — not done.
- **Corrections to earlier drafts:** (1) the cohort is **not** a subset of test-takers (it exceeds InfoHub N in 2022-23/2023-24). (2) The `max(tested, 95% of continuously enrolled)` participation floor belongs to NYC's *separate* ESSA/StART *Weighted Academic Achievement Index*, not this Snapshot metric. (3) the "~1pp" headline was the SQR-PDF recent-year subset, **not** the overall snapshot agreement (73% / 62% within ±1.5pp). (4) "valid-score/vintage noise" is withdrawn.

### Chronic absenteeism — `chronic_absenteeism_rate`
- **Ours (InfoHub attendance file):** `% Chronically Absent` = `# Chronically Absent ÷ # Contributing (10+ total days, 1+ present day)`. Chronically absent = absent ≥10% of enrolled days.
- **(ii) Base case — exact.** Served = `#CA ÷ #Contributing` to 2 dp, recomputed from raw counts (10,639 cells, 0 mismatches).
- **(iii) Spot check (`student_chronic_absent`) — largely explained (2026-06-10 exhaustive census, `docs/qa_reports/snapshot/`; replaces the 300-school sample).** All **6,570** comparable district school-year pairs, SY 2018-19 → 2022-23 (snapshot side: the committed bulk fetch `data/quality/snapshot-values.json`): school-wide agreement within ±1.5pp is **0.6534** — the sample's "~67%" confirmed, with the full tail −26.84 to +15.83pp. **Basis established: the Snapshot displays the SAME-YEAR GRADE-BAND rate, not the school-wide rate** — grades **K–8 (K-inclusive)** on EMS reports, grades **9–12** on HS reports. Re-compared on the matching band, agreement rises to **0.8639 pooled / 0.9223 EMS-report** (≥0.94 for EMS report-years 2020–2023), and the entire worst-15 list is band-explained: the old "−12pp at large N" cases were middle schools served the HS report (worst case `28Q157` SY 2021-22, −26.84pp school-wide — the Snapshot's 54% is its grade-9 rate, 19/35, **exactly**) and K-8 schools whose school-wide rate differs by their PK rows. This supersedes the earlier "suspected accountability-cohort denominator" for district schools. **Residual not established:** HS reports in SY 2019-20/2021-22 (band agreement ~0.49 with a one-sided +1.5pp median shift, served above snapshot) and SY 2018-19 (0.80–0.83 both report types).
- **(iii) Report-years 2024/2025 — renamed varnames decoded (ledger A.7, verified).** `val_chronic_absent_{ems,hs}_all` stores the **complement**: `round(100 − same-report-year band rate)` (EMS: K–8 K-inclusive; HS: 9–12) — i.e., the share NOT chronically absent. Decoded agreement at ±1.5pp: **0.9710/0.9740** (ry2024 EMS/HS) and **0.9665/0.9814** (ry2025); every raw-value candidate across 26 tested alignments maxes out at 0.042, which is why the earlier 4-school spot check "matched neither year". The series changed sign convention in 2024, not population or period. What label the rendered page attaches is not established (the page does not visibly display CA for 2023-24+).
- **(iii) Charters (Snapshot) — partially independent (ledger A.19, verified; `docs/qa_reports/snapshot/PROVENANCE.md`).** The Snapshot's charter CA values are **computed, not copied** from NYSED (17.5–19.8% rounding-match vs ~100% expected for republication; values exist where NYSED suppresses; K-inclusive enrollment verified at scale) and track NYSED only loosely (median |diff| 1.79pp matched-band, r 0.73–0.83 vs district r 0.99 — why not established). Reporting outages co-move with the state chain **82/82** (`DATA_REP_FLAG=N` school-years 2021–2025, zero counterexamples): it can catch gross serving errors, cannot catch state-chain reporting failures, cannot arbitrate <2pp.
- **Harness follow-up (NOT done in this PR):** `verify/snapshot.yaml` still compares `student_chronic_absent` against the school-wide served value; it should be re-pointed at the K–8 / 9–12 **band aggregates** (and taught the 2024/2025 complement varnames) so the recurring (iii) check runs on the established basis.
- **SQR *scored* attendance metric is different:** "**Percentage of Students with >90% Attendance**" (documented in the SQR PDF) — a student-level distribution statistic, **not** chronic absenteeism, and **not in the InfoHub bulk file** (which has `% Attendance`, `% Chronically Absent`, day counts only). Not reproducible from what we ingest.
- **Coverage:** the Snapshot *page* displays absenteeism only ~2018-19 → 2022-23; its data *feed* carries 2023-24/2024-25 under the renamed complement varnames above (decoded, usable as a reference); NYSED remains the independent recent-year reference (see Coverage).
- **Expansion (2026-06-09):** the same file now also backs `average_daily_attendance` (`% Attendance`, N = `# Total Days`) and per-grade keys `*_grade_{pk,k,1..12}` for both metrics; (ii) components exist for all (subgroup `ALL`), and the ADA base case is **exact** (10,639 cells, 0 mismatches on the dev replica — regenerate against prod after ingest).
- **(iii-b) Cross-publisher (NYSED Report Card Database) — done.** NYSED publishes school-level CA (grades 1–8 / 9–12, BEDS-keyed) independently of the DOE file; joined via the deterministic BEDS→DBN rule (zero collisions). Result (`docs/qa_reports/nysed/`, expanded 2026-06-10 to all comparable years — 2018-19 and 2020-21 → 2024-25): **ten of twelve (year × grade-band) panels reconcile at the median** (median per-school diff ≤0.4pp) — the strongest external validation of the ingested attendance data to date. The second failure besides 2024-25 EM is **2022-23 HS** (median −0.60pp, only 47% within ±1pp, r 0.779) — the visible edge of a COVID-era HS tail (62/112/129 high schools >5pp below NYSED in 2020-21/2021-22/2022-23 on largely byte-identical denominators; publisher-level; hidden by median-based summaries; cause not established; ledger A.20, `docs/qa_reports/nysed/RECONCILIATION.md` §9). **Exception: 2024-25 grades 1–8 diverges systematically** (DOE falling vs NYSED rising on the same ~1,115 schools; numerator-side; weighted 29.0% vs 30.8%; no precursor — EM medians stay within ±0.4pp in every earlier panel). Document review rules out any documented 2024-25 definition change on either side; NYSED's standing suspension/extended-medical exclusion predicts the opposite sign; one documented candidate remains (SIRS defaults missing/unpaired attendance records to "absent, unexcused"); **cause not established** — NYSED inquiry drafted. Until resolved, treat 2024-25 grades-1–8 CA as **publisher-dependent** (carry source attribution). NYSED coverage limits: D75/D79 have zero rows in the CA tables; Success Academy stopped reporting attendance network-wide in 2023-24 (`DATA_REP_FLAG=N`).

### Graduation — `graduation_rate_4yr`
- **Ours:** 4-year **August** cohort grad rate, from **NYC Open Data `mjm3-8dw8`** (`report_category='School' AND cohort='4 year August'`; `grads_1`). The prototype's stated source is Open Data here — **the InfoHub grad file returns empty bodies**.
- **(ii) Base case — exact** vs the Open Data resource (3,789 cells, 0 mismatches).
- **(iii) Spot check:** not yet wired (HS Snapshot varname unconfirmed).

### Survey — `survey_responses.positive_pct` (and the `survey_*` rollups)
- **Ours (NYC School Survey "Pos & Neg %" sheet):** top-2-box **positive %** per (school, year, question, respondent). Stored as percent (source is a 0–1 fraction → ×100). The positive column is the favorable bucket (`Agree/Strongly agree` side), sometimes the first of the two question columns.
- **(ii) Base case — exact:** 0 mismatches across **826,354** served survey cells, given the column convention. Caveat: column *selection* mirrors the loader's interpretation.
- **(iii) Spot check:** the Snapshot's "School Percent Positive" is a measure-group average on the *same* survey (same publisher); not run as an external check. The named `survey_*` DB metrics are **derived rollups** → check (iv).

## What is NOT reconcilable from the bulk files
- NYC's **accountability cohort** (Oct-31-register-attributed) — the register/attribution flag is derived inside NYC's accountability pipeline and isn't in the InfoHub downloads.
- The SQR's **`% with >90% attendance`** — needs student-level attendance distributions not in the bulk file.
- **Grade-3/7 proficiency** from the Snapshot — the Snapshot API is school-wide only.

## Coverage & data gaps
Distinct from value-correctness (above): what's missing, and whether a source exists to fill it.

- **Years missing in the source itself.** Test results have **no 2019-20 / 2020-21** (NYSED cancelled 2020; 2021 ~21% participation — documented in the InfoHub test-file NOTES). Affects any trend/year analysis. Not fillable.
- **Site missing rows that exist in the source ("source-only").** ~215 test/grad/absenteeism school-years are in the source but not served (ela_g3 8, ela_g7 24, math_g3 8, math_g7 24, absenteeism 148, grad 3; school-wide ela_all/math_all 39 each); survey is much larger (student ~13k, teacher ~80–109k per file — partly the same cause, partly not yet explained). **Cause:** those DBNs aren't in the `schools` directory and every loader skips unknown DBNs (`test-results.ts:132`). **Fix:** refresh the directory loaders (`all-school-directories.ts` / `nyc-od-schools.ts`), then re-ingest.
- **Spot-check reference coverage limits.** Snapshot `student_chronic_absent` ends ~2022-23 (the 2023-24/2024-25 feed values live under the renamed complement varnames — decoded per ledger A.7); NYC Open Data school-level absenteeism (`gqq2-hgxd`) ends **2020-21**; Open Data test datasets (`iebs-5yhr`/`74kb-55u9`) end **2022-23**; the Snapshot API exposes no grade-3/7 breakout.
- **Whole metrics not ingested.** CCR, SSEC bullying incidents, course access — loaders are stubs (0 rows).
- **Survey scope.** Parent/family surveys are **not loaded** (the loader skips them); the files **do exist** on the InfoHub survey archive. The latest *published* survey is **2023-24** (= the "2024" file we have) — there is **no 2024-25 survey released yet**, so we are current, not behind.

### Sources that can fill recent-year gaps
- **Chronic absenteeism spot-check (recent years).** Our absenteeism *data* covers through 2024-25 (base case exact); the gap is a *reference*. **NYSED ESSA Report Card** (`data.nysed.gov/downloads.php`) publishes **per-school chronic absenteeism for 2023-24** — a different publisher (a true independent check). Caveats: BEDS→DBN crosswalk (already loaded), and NYSED's definition differs from NYC's. 2024-25 NYSED likely not released yet.
- **Parent/family survey.** The K-12 Family / Pre-K Family files on the InfoHub survey archive (currently uningested).
- **CCR / SSEC incidents / course access.** Sources listed in `SPECS.md`; needs the stub loaders built (write-path; mwg).

## Practical guidance
- Treat the **base case (ii)** (faithful to the ingested file) as the **binding** correctness gate.
- Treat Snapshot/Open-Data agreement (**spot check (iii)**) as **context** — the central tendency matches (median gap ~0) but a real tail disagrees by up to ~10–19pp at both small and large N. For **chronic absenteeism** that tail is now largely explained (the Snapshot shows grade-band rates — compare against the band, residual pockets noted above); for the **test metrics** the tail's cause is not individually established. Do not "fix" our values toward the Snapshot — the base case (ii) is exact vs the file.
- If matching NYC's *published* number becomes a requirement, ingest the SQR/Snapshot figure as a **second source** (the cohort %), rather than recomputing from the bulk files.