This repo contains the Project KIDS templates used for processing school district data. Project KIDS has developed a novel methodology that effectively drills all education expenditures down to the student level. These student spending resource profiles are also linked to enrollment, demographic, and performance data that allows for unique, multi-faceted inquiries into the relationship between spending and performance in Utah education. These scripts are adapted for each individual school district.
This script cleans payroll data received directly from school districts. If the payroll data does not already include CACTUS (Comprehensive Administration of Credentials for Teachers in Utah Schools) IDs, the Payroll without CACTUS script is used to fuzzy match teacher names to their credentials. (Otherwise, the Payroll with CACTUS script is used and fuzzy matching is used to check for any additional matches.) Once teachers are matched to their CACTUS IDs, they can be matched to the course data and thus their students. Finally, all payroll transactions are classified as either instructional or non-instructional using account codes and descriptions. The exported files include (1) a list of all teachers in the course data with each teacher’s total non/instructional pay and benefits and (2) the cleaned transaction-by-transaction payroll data with flags for non/instructional compensation.
This script makes district-specific adjustments to the student-level course data and matches teachers’ instructional compensation with the students in their classes. Classes are verified via checks and remedies for outliers. Teachers’ instructional compensation is matched to the individual students in their courses and allocated across their students. The exported files include (1) the finalized transaction-by-transaction payroll data with flags for non/instructional compensation used in the Expense Allocation code and (2) the finalized student course data with each students’ demographic information, test performance, and instructional compensation used in the Student Allocation code.
This script cleans district expenditure data and categorizes every transaction into a subcode, or spending category. LEA expenditure data is queried from the Transparent Utah database and cleaned payroll data from the Class Adjustments process supplements this information. Default subcode assignments are made based on the Utah State Board of Education's standard chart of accounts which are mapped into Project KIDS subcodes. Manual adjustments are made to ensure the most detailed specification of spending categories possible. The exported file includes spending totals aggregated at the year/school/subcode level.
This script allocates all compensation and expenses to individual students. Each teacher’s instructional compensation from the Class Adjustments process is allocated to their students through the classes they teach in the course data. All other expenses and non-instructional compensation from the Expense Allocation process are allocated to the students who benefit from the spending category, which is identified via student information such as the school the student attends, their unique demographics, and their grade level. The exported file includes detailed student data with student-level totals for each spending category.
This multi-stage process leads to the final product of a database that combines student demographic, enrollment, and cost information. Though data is cleaned at the district level, the database allows for detailed inquiries of education trends across the state. To learn more about Project KIDS and how this data is used, please visit the website or read the technical manual.