Case study · Federal compliance
$214K in fines prevented,seven years reconciled.
Owning IPEDS submissions end to end, including ACTS: a late-added Department of Education mandate with a short deadline and seven years of historical data, completed accurately and on time to prevent more than $214,635 in potential fines.
- Role
- Data Analyst & BI Developer
- Organization
- Southwestern Adventist University, Institutional Research
- Timeline
- 2024 – 2026
- Stack
- Colleague ODS · PostgreSQL · SQL Server · IPEDS · Git
The problem
What neededto change.
IPEDS submissions are federal reporting: missing or failing one puts the institution at risk of fines and of losing eligibility for federal student aid and loan programs. They are audited externally, so the tolerance for error is zero.
Then the Department of Education added the Admissions and Consumer Transparency Supplement (ACTS) to IPEDS: a complex new requirement with a short deadline, covering seven years of historical admissions data.
The approach
How it was built,step by step.
- 01
Five surveys, one source
Authored Fall Enrollment, Graduation Rates, ACTS, Human Resources and 12-Month Enrollment from the same governed SQL layer the dashboards use.
- 02
Two cohort definitions, one answer
For ACTS, built status-based and date-based applicant cohorts side by side, compared them, and took the date-based definition to production.
- 03
ACTS: seven years, one deadline
Wrote the SQL behind the ACTS submission and resolved the data discrepancies between seven years of past reports, so the history reconciled. Submitted accurately and on time, preventing more than $214,635 in potential fines.
- 04
The final three reports
Worked with a colleague through weeks of long days, including a full deadline day into the evening, to finish the final three reports without error. Each carried a potential $20,000 fine.
- 05
Full-time from credits, not flags
Full- and part-time status is derived from credits rather than the registrar flag, which can go stale, and a CI guard enforces the rule in every query.
The result
What it delivered.In numbers.
ACTS and the final reports went in accurately and on time, preventing more than $214,635 in potential fines and keeping the university eligible for federal student aid. The Vice President for Institutional Research and Effectiveness recognized the work in a campus-wide announcement.
- in potential federal fines prevented
- $214K+
- of historical admissions data reconciled for ACTS
- 7 yrs
- annual IPEDS surveys authored
- 5