A Snapshot in Time

When I weigh myself, I look at the scale and write down the weight that I see at that moment. Student assessments are similar. An assessment is a snapshot of the student's performance at that place and time. And then behaviors are the same. What I don't do is think of all the different weights in my life and write down the range of weights that I have weighed in my life.

When I was thinking about how to better analyze data we collect from our Student Support Data Collection form, I looked at the "Approximate time of day concerning behavior occurs" field. I have a lot of data in that field that looks like this: 

[Morning Class time 8:30 - 10:50, Morning Class time 10:50 - 12:00, Mid day class, Lunch - Lunch recess, Afternoon classes].

Data is always easier to work with when it is singular. One cell, one piece of data. I set up this form to try to make it easier for teachers to report on students, but by setting up the time field in this way, I have muddled the data set and have made analyzing data much harder. 

Next year, I am planning on reintroducing the data form in August and talking more about why data is collected in this way and how it works.

How We Collect Student Data

At OYIS, we use a Google Form to collect data about students with concerns. This helps admin, coordinators, learning support, and teachers to see, as a community, which students are coming up on our collective radar. It shows us frequency of submission and why the submission was made. This can inform how we talk with teachers during our collaboration meetings.

On the form we have teachers fill out, we have a field asking for more details on the concern. This field includes the following, and this was recommended by our Head of Inclusion when she came to our school.

This allows teachers to quickly choose one by clicking a button. If there is a need to add more context, the teacher can add a comment at the bottom.

I experimented a little last year with making the categories a little more dynamic. The idea was to analyze the data every month and then put the most frequent responses in the list for the next month. 

That hasn't happened yet, but as I write this, I wonder if that is the best approach. 

Making a Student Lookup System

Over the Winter Break, I was possessed. I spent too much time analyzing the data for our Inclusion [Support] Team. I did one thing, then thought of another, and xxx hours later, I had an awesome thing. It was exactly what I needed. 

Then I broke it.

Now I am trying to recreate what I did and I cannot remember how I set up the formulas. I am going to try to keep notes here for the future.

In the Student Lookup, you would choose the section, or programme (PYP, MYP, DP), then that would filter the Grades in each section. The grade would then filter the student names in that specific grade. 

Below are the steps I took to make it on the dashboard page. 

1. Get a UNIQUE list of students
2. Find their Grades
3. Count the number of academic vs behavioral concerns
4. Find the student section - =INDEX(FILTER('sheeetname!$C$2:$C, 'Form Responses 1'!$E$2:$E = B5), 1)
5. Find the concerns  - =SPLIT(TEXTJOIN(",", TRUE, UNIQUE('sheetname'!J2:J)), ",")
6. Flatten the row =TRANSPOSE(UNIQUE(FILTER('sheetname'!J2:J, 'sheetname!J2:J<>"")))

This is the backend. 

I made a column with the sections, PYP, MYP, DP. I then used this formula in the next column, the middle column above, 

=SORT(UNIQUE(FILTER(C3:C, SEARCH('sheetname'!C25, B3:B) > 0)), 1, TRUE)

And then the third row:

=IFERROR(SORT(FILTER(D3:D, C3:C='sheetname'!D25)), {})

When I looked at it, I thought maybe individual students is not as good as showing a whole class. This could be useful for specialist teachers as well as subs, so I adjusted the above formula to stop at the grade level and I got the below output:

Creating a Student Lookup in Google Sheets

The idea of a student lookup system has been rattling around in my head for a few years. I started this in Google Data Studio, but Data Studio started changing and I couldn't keep up. Data Studio became Looker and now four years later, it looks completely different than it did.

I have been playing with Google Sheets for a few years, and when I came to our Sheet with PYP student data, I could see that some of the data, like reading levels, was something that was outdated for most students on one sheet, but on another was kept up-to-date (EAL).

The original sheet

I started with the reading data and found that I could use VLOOKUP to connect the above sheet to the EAL sheet. The formula I used pulled the reading data from the EAL sheet and keeping our sheet updated. I also started looking for other pieces of information I could keep updated with a formula. 

This got the ideas flowing and I put my homeroom teacher hat on. I wondered what I would want to see as a homeroom teacher for a student. 

I started with a student name that would bring up strategies and goals for a student.

This was good, but then I wanted to expand it to filter between PYP, MYP, and DP. Then I wanted to know other information about the student like the number of submissions they had, reading level, language phase for MYP/DP, and a link to their support plan. I came up with this:

The updated sheet

I can see that I am not done with this - I would like to add the current number of absences, EAL goals and strategies, and something else that slipped my mind, but it is a start. 

Visualizing Student Support Data with Google Sheets

 In the beginning

I joined the [formerly-called] SEN team last year. The team was using anecdotal records for their data collection. The issue was that it was kind of a black box -immediately I wondered how I would know if the strategies I was using with students in my care were helping them reach their goals.

Last year, I helped the team reimagine some ways to collect data on student behavior through the use of rubrics, checklists, and goal trackers. We also had teachers write anecdotal notes about students while observing their behavior.

This year, the school created a new position, inclusion support coordinator. The first thing she did when she came in was to ask how we could collect data from teachers more easily. I was elated to hear this and suggested we use a Google Form that could be easily bookmarked to the browser, so that's what we did. She pushed teachers to use it when they suspected academic or behavioral issues with students. We also did away with the former ways of collecting data to try to save teachers' time.

The form responses before visualization.

With the data collected from the Form, we started by filtering the responses into the different programs; PYP, MYP, and DP. This was good, but after a month, we could see that it was hard to read. I also started asking my coordinator and principals what kind of data they look at when viewing it, and I started thinking about how to take their feedback, what I knew about what I would want to see as a homeroom teacher, and I started thinking of how. I could make this all visual. 

In my head, we would see the current weeks' totals along with the top five students. I also had this idea of a student lookup in my head.

The idea I had brewing in my head was a Google Form that could be easily bookmarked. In my head, we would collate the responses, and these would become the updated form question responses.

I had this other idea that we would have a lookup of student with the goals and the support plan. 

My demo of a student lookup 

Over winter break, as I spent the days at my in-laws house, I got to work making a dashboard. 

For this dashboard, I wanted a few things, data visualization, simple, more data, and better visualizations - right now, there's a lot of text.

Going forward, this exercise should show that data needs to be consolidated and accessible. 

Here's what it looks like.

Then I broke it. I committed the cardinal sin of editing the response sheet. After editing the response sheet, it no longer connected with the dashboard I made. 

The good thing is that I have a better idea of what I want to do next year.

Using Data for Student Support

While in an SEN team meeting, I was a little confused about the data we were looking at in the system. We were looking at data from an online reading app, but the data was in ranges. All it showed us was the average from certain ranges, but we didn't know when interventions happened and what the intervention or strategy was so we could see if it worked.

My idea was to collect baseline data before a strategy was implemented. Then once that was collected, we would start tracking the progress.

In the example below, there are four reading skills we are tracking. At the top is a dropdown that will show the skills. When a skill is chosen, the average appears.

Then I thought of a scenario where you would want to see scores in a range. I don't know how to do that off the top of my head, but I knew I could do it with checkboxes. So the second way is to use checkboxes to find the average of the scores.