Bringing Data Together

As mentioned in a previous post, I am in the process of updating the mathematical model used by my school to determine when students are ready to take college-level courses. This model is important to us because we send over a third of our juniors and half of our seniors to college each year and we don’t want to mistakenly send students to college before they are ready. Using this model, my team has gotten pretty good at determining readiness; last year our students passed 97% of the college courses they attempted.

Before the model can be applied, it must first be brought together into a single database or spreadsheet. Depending on your systems, this can be a quick or timely endeavor. For me, bringing together all of the data we have on students took a little over six hours. Here’s what I did:

Google Sheets

Because it is shareable and applies edits in real-time, I do all of my modeling in a single Google Sheet. For anyone who is an Excel devotee, this may sound crazy. It is. But, for me, the benefits outweigh the costs.

For this year’s update, I created a new Google Sheet called “Master Data File” where I pasted an export from our Student Information System (SIS) containing each student’s name, ID, DOB, sex, graduation year, and commutative GPA. Because our SIS contains the most up-to-date information regarding student enrollments, I always start there and then use that data as reference for gathering the rest. No need to gather data on a student no longer enrolled.

Microsoft Excel

So far, there is only one function I need that is not easily done in Google Sheets: consolidating data. At one time, I would spend hours manually inputting data from one system’s export file to another. Excel can consolidate data from two spreadsheets in minutes.

The Consolidate function is in the "Data" ribbon on Microsoft Excel.

For example, data downloaded from the College Board website looks different than data taken from our SIS. The College Board data includes some students who have left my school, is missing data for students who are newly enrolled, and may have other formatting differences that would make a simple copy/paste impossible to do.

As long as I have a single column that uniquely identifies individual student (student ID, “Last Name, First Name” combinations, etc.), Excel can consolidate the data from both sources into a single row to be included in the master file.

Data Brought Together

Here’s the data I consolidated into the single Google Sheet for each student organized by source:

Student information System

• Demographic Information used for sorting and aggregated data analysis
• High School Grade Point Average: used as a primary indicator of future college success. This topic will be expanded upon further in a later post.

College Board

• PSAT 8/9, 10, and 11: We give the PSAT to all students every year in grades 8 through 11. While we do not yet use this data in our model, I decided to pull it in hopes of future analysis and reporting.
• SAT: In Michigan, all 11 graders are required to take the new SAT. Our community college partner accepts SAT scores for determining college course placement, so we use these scores as part of our readiness model.
• Accuplacer: While this is technically a College Board product, we get this data from our college partner. Our students take this college placement assessment each year until they place into college-level coursework beginning in the 9th grade.

ACT

• ACT: Now that the state of Michigan has moved from ACT to the SAT for it’s college readiness assessment, we only have a few students each year who take this assessment. For those who do, though, I need to consider their scores when determining readiness.
• Compass: Until this year, our college partner used the ACT’s Compass assessment for determining college placement. This assessment was replaced by Accuplacer but we still consider Compass data in determining students’ college readiness.

Other

• Agency Score: Each year, we ask our teachers to rate each student’s skill at exercising agency on a scale of 0-5. Agency, for those not familiar with the concept is one’s ability to be an “agent” of his or her own learning. It consists of two components, both a part of our instructional model: 1.) ability to complete tasks to specification and on time, and 2.) growing from challenging work and setbacks. I simply ask teachers to rate each student and take the average of their input. More on this measure of college readiness later.

When recording assessment data, I like to separate it by the year it was taken relative to the student. I like to know what each student’s score was each year they took it. This allows me to see growth or stagnation in student performance, and makes analysis and reporting of data much easier to do.

Next up: what I do with this data once I have it all in one location.

Modeling Future Student Success

Over the next few weeks, I will be updating the mathematical model I created to predict students' future success in college. That model, which my school has been using and revising for the past four years, looks for patterns in academic and behavioral data to help predict individual student's likelihood of earning passing scores in college coursework.

I created the model in response to learning that standardized test scores alone left far too many edge cases to accurately predict future academic success. Too many students had previously scored well on tests yet did poorly in college classes. Similarly, some students we thought could handle college coursework did not score well on traditional measures of college "readiness."

Using this model, my school sends a third of its juniors and half of its seniors to college. Last year, these students passed 97% of the courses attempted. Ninety-three percent passed with a C or better.

To learn more about my school and why we send so many students to college while still in high school, I recommend reading my post from June titled Early College For All.

There is nothing magical about the model. It simply applies what is already known about past students' success to predict how well current students might do in college coursework.

The model uses three primary sources of data:

1. Standardized college placement or college readiness scores: I have used data from different assessments over the years with relatively similar results (Compass, Accuplacer, ACT, and SAT).
2. High school grade point average: in my school, the strongest predictor of future academic success is past student success.
3. Teachers' subjective assessment of student "agency:" Each winter, I ask my faculty to evaluate each student on how well they are perceived to grow through challenging work and complete work on time.

Each year, the weight applied to each of these data sources has changed to reflect what we've learned about past student success. Last year, high school GPA and test scores were weighted about evenly. Agency, while found to be an accurate predictor, was weighted very little (approximately 10%) due to its subjective nature and the potential for perceived bias.

Over the coming weeks, as I update the model, I hope to share more of the details that go into its creation and revision. I see great value in having more schools analyzing data in this way and think it's a simple enough process that can be replicated with bit of time and effort.

Disclaimer: I am not a mathematician and do not claim to be an expert in inferential statistics. I am simply a practitioner with a good memory of his Statistics 101 class. I welcome any feedback from readers with stronger mathematical grounding.

If you have questions about this model that you would like me to expand upon or would simply like to learn more, feel free to leave a comment or reach out by email.

A New Metric for High School Success

This morning, I was drawn to a Michigan news report that East Lansing and Okemos High Schools are among the "top ten" in the state, according to U.S. News & World Report rankings. Curious, I wondered how these rankings were determined.

According to their website, U.S. News selected schools for their list using a three step process (emphasis added):

The first two steps ensured that the schools serve all of their students well, using performance on state proficiency tests as the benchmarks. For those schools that made it past the first two steps, a third step assessed the degree to which schools prepare students for college-level work.

Essentially, they are ranking schools by:

1. First, comparing each school's standardized assessment scores in math and reading to state averages (with an unspecified nod to schools with higher levels of economically disadvantaged students).
2. Then, they compare each school's standardized assessment scores in math and reading for black, Hispanic, and low-income students to state averages for the same groups.
3. Of the schools that perform better than state averages in both of the areas specified above, U.S. News ranks them "using Advanced Placement or International Baccalaureate test data" as the benchmark for college-readiness.

   This third step measured which schools produced the best cllege-level achievement for the highest percentages of their students. This was done by computing a "college readiness index" (CRI) based on the school's AP or IB participation rate (the number of 12th-grade students in the 2010-2011 academic year who took at least one AP or IB test before or during their senior year, divided by the number of 12th-graders) and how well the students did on those tests.

---

My district is in its first of a three-year initiative to transform our traditional high school into an Early College. From 2007 through 2010, 74% of our students chose to attend a 2- or 4- year postsecondary institution. Of that group, only 44% successfully completed their first year; thirty percent have yet to earn their first 24 credits. We think we can improve upon this and are working with the New Tech Network and the state of Michigan to re-imagine everything we do to get there. Our goal for the class of 2016 and beyond is a first-year postsecondary success rate of at least eighty percent.

To achieve this goal, we will decrease the number Advanced Placement courses we offer, and we have no plans to start an International Baccalaureate program. In fact, we may stop offering any advanced courses at our high school altogether.

We're serious.

Our vision of a future in which all students are prepared for postsecondary education involves:

1. letting any student who is prepared to go to college dual enroll as early as their junior year of high school.
2. increasing our focus on the students who are not yet prepared through changes in curriculum, instruction, and interventions.
3. offering all students the opportunity to "stick with us" for a fifth year so we can offer support and funding for their first year of college.

As students in our Early College, all young adults in our community have an opportunity to earn up to an associates degree from our area community college after five years of high school. The credits earned toward this degree (or non-degree program) are fully transferrable, earned on the college campus (we plan offer shuttle service to and from our building), and are completely free to the student. Students who elect to "stick around" for that fifth year can attend all classes at the community college and never have to step foot back into the high school if they don't want to.

Our early estimate, based on data collected from the class of 2016 during their freshmen year, is that 60-70% of our students will opt to let us pay for their first year of college. Approximately 20%-30% tell us that they currently want to leave immediately after high school to attend a 4-year university. Around 10% of our students have alternative plans involving the military, a trade, or an alternative career.

We are in talks with other institutions of postsecondary education interested in partnering to offer a wider range of opportunities to our diverse student body. For us and our community, success is not defined only by college entrance and success.

---

Let's assume that my teachers and I are able to achieve our goal and, in fact, over 80% of our students complete their first year of postsecondary education successfully. Let's assume that this success occurs evenly amongst our diverse population.

I am sure the growth from 44% to 80% would be noted by someone. The community and the school would all be pleased and as proud of our young people as ever. The Board of Education might recognize our efforts. The news might even pick up on the story of our success.

But ….

Due of the narrowness of criteria used by organizations like U.S. News to define college readiness for their rankings, we will be listed below (far below) the larger schools in our state that offer Advanced Placement and International Baccalaureate courses.

---

In the end, I don't care to make it onto these lists; that's not my point. Rather, my hope is that by telling my school's story, and by sharing the methods behind how these lists are compiled, we might start a conversation about the metrics being used to evaluate the success of our schools.

After all, should schools be assessed by the number of students whom are successful at scoring well on complex tests? Or, should they be assessed by the number of students whom are successful at actually completing their postsecondary goals?

Isn't it time that we find a new metric for high school success?