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.

Creating Engaging Classrooms

"Everyone please stop what you're doing, close your computers, and give me your fullest attention. This work we're doing is very important and we all want to do our best. I'm noticing that several of you are off-task now and that's not good. Let's stay mindful of our work and try to remain on task. Thank you."

If you've ever taught, I'm sure you've said this or something similar to students. I know I have.

Let's stop.

The truth is that statements like the one above aren't effective. They distract the students who are on-task without actually addressing the reasons that some are off-task in the first place.

We continue to say things like this because the immediate response is short-term compliance. Students appear to be more engaged after we say something so we feel better about ourselves. This makes us more likely to say things like this in the future.

I'm not suggesting that we stop addressing off-task behavior; I'm actually suggesting that we start.

The next time you're planning produces less engagement than desired, take time to reflect upon why before taking action. Then, take action.

Here are a few ideas, as examples:

1. Involve your students in the planning process.

Form a student academic advisory board in your school or class. Before launching a new project or unit, ask them to convene to tune your ideas. Take their feedback seriously and use it to improve your practice.

Include a broad range of learners. You'll want input from multiple perspectives; asking only the top ten for feedback won't solve your engagement issues.

2. Observe disengaged students and ask them why they're struggling to stay on task.

If your question is sincere, they'll be quite honest. Write their responses down as if you were a researcher collecting data on student behavior. Taking this step will pay off immensely during future planning.

Over time, analyzing this data will reveal patterns of student disengagement. Are the same students always disengaged? Is there a particular activity that disengages more students than others? Are students in some classes or at different times of day more likely to disengage than others?

By analyzing data, you'll be better able plan for and prevent disengagement in the future. To do this, however, you have to first collect the data!

3. Ask an outsider to do the observing for you.

A trusted administrator might be a good place to start. Lacking that (or additionally), ask a colleague to sit in during one of your classes to collect data. Be specific about what data you want them to collect. Help them help you by providing the data collection format and tool.

Do you want to know when or how often a particular group of students disengages? Do you want to know what your proximity in the classroom does to engagement? An outside observer can see things that you never will in your own class.

4. Make peer accountability a celebrated part of your school or classroom culture.

It should not be the teacher's sole responsibility to keep everyone on task. In too many schools, though, we teach our students to be passive bystanders to their peer's disengagement and misbehavior. Make this unacceptable by addressing it explicitly and regularly when it occurs.

When one member of a group does significantly better or worse on an assessment than the the rest, there's a teachable moment that shouldn't be skipped. When one member of the group is distracting the others, what steps can that group take to address that distraction in a non-threatening way?

By taking class time to teach students to hold one another accountable to their work, you'll not only decrease disengagement, but you'll be teaching them an essential 21st-century skill.

5. End every project or unit with a day of student reflection.

Divide the day into three parts.

1. Start with a review of the work they did and ask them to provide feedback on how you could improve upon it the next time around.
2. Ask them to reflect upon how the class functioned as a group. What went well? What didn't? What might they be able to do next time to make the class better.
3. Ask them each to reflect upon how they functioned, individually. What study habits or activities worked particularly well for them? What worked the least? What should they work to improve personally in the future?

Take notes throughout the day and store them with your lesson plans or project resources.

6. End every class with five minutes of honest reflection.

Choose a reflection format, as appropriate, to get student feedback on what they learned, what questions they have, or what needs to be covered more thoroughly the next day.

Whether it be through a journal, an exit slip, a discussion, or a any other format, the point is to increase communication from your students about what they need to learn to be successful.

7. Identify school and district resources for the most disengaged students.

Let's face it, we can't solve every problem ourselves. When we can't, it's important to acknowledge that we can't and to ask for help from someone who can. Regardless whether these students need academic, social, psychological, or behavioral support, remember that no classroom is an island. Use every resource available for this small segment of every school population.

8. Leverage parent relationships to help increase student productivity.

This is especially helpful when students are not engaging in homework. Calls and emails to parents for feedback and support are typically greeted more positively when delivered sincerely and without emotion.

Ask parents what their kids say about your class at home. What work do they see their kids doing at home? How much homework are their kids getting? Is it too much? Are other commitments getting in the way? What do they think could help you engage their kid in learning?

---

By taking time to collect and analyze data before deciding to act, and then acting intentionally to address the actual challenges of student engagement, we will start to realize a world with increased student engagement and achievement.

Moreover, you can stop giving the whole-class lectures on the importance of staying on-task.