This Week in Technology [WK 12]

One foot in front of the other babe
One breath leads to another yeah
Just keep moving, oh
Look within for the strength today
Listen out for the voice to say
Just keep moving, oh

Flames by David Guetta, featuring Sia

We embrace the PBIS process at Streetsboro City Schools, but collecting data can be a bit of a chore sometimes, and often involves double data entry (once on paper, another in a spreadsheet), and people involved in the process often miss out on feedback or status updates. So, naturally, we’re working to fix that…

The Past

Paper forms, naturally. Lots and lots of paper that’s hard to log, analyze, sort, and disseminate. Each building collected a bit differently, and keeping track centrally has always been a difficult task when using paper – regardless of the data you’re trying to collect and use.

And, turns out, building an accurate picture of a student’s behavior is always a challenge when multiple data points (may) exist, but not in the same portal, space, or dashboard.

The Present

I met with Joe Petrarca, our Director of Student Services, to discuss what we could do differently, and what the requirements where for the project.

In any project, collecting the requirements, and accurately creating the scope of work to be done is key to later project success. I like to start at the end, and use questions like “What are we going to do with the data?”, or “Who will need access, and what is the preferred format?” to help guide my design thinking.

“What are we going to do with the data?”

Once we had a good idea of what we needed to collect, in what form, and how and where the data needed to go, I began creating a Google Form to serve as the single point intake and reporting tool for the data.

I call the form the PBDF, or PBIS Behavior Documentation Form.

I try to create easy to say, and easy to find, names for forms and documents in Google Drive. I often use Greek, Roman, and Norse gods and other deities to name important work, because it makes for easy retrieval in a search.

For example, my budget spreadsheet is called TYR, after the son of Odin. But more on that later… (also perhaps not easy to say… for you… 😉 He’s the guy missing a hand to the Fenris wolf…

The Future

The PBDF form collects all the information we need, at present time, and tries to collect it in a natural, and logical order, and uses a few sections to accurately grab the data we need.

For example, we wanted to collect data for both minor and major infractions, but only create office referrals from the major issues. However, creating two forms seemed overkill, and one form can collect both with the use of form sections.

In the first section we collect student data, staff member reporting data, as well as time and date of the incident. The form already timestamps the submission (BONUS – don’t forget this – handy when creating other workflows later, as is the =TODAY() formula is Sheets, that can indicate current date automatically, and roll forward, triggering actions.), so we use that our advantage (see below).

PBDF Section 1

At the end of section 1, we ask the user to select whether or not they are filing a minor or major issue for behavior, and we help them by adding a graphic that covers the various behaviors. The end of section 1 then sends them to the respective next section, based on the form logic, to supply the behavior data. Neat!

PBDF Form Logic

The rest of the form is easy – using check boxes to collect what kind of behavior is being reported, with options for additional comments as well for each section.

In the minor issues section, we ask for staff actions as well, as in “What did you do?” to assist the student – be it a phone call home, student conference, etc. If the matter is a major behavior issue, we trigger an office referral to the building principal and guidance department teams (see below).

PBDF Major Section

On the spreadsheet front, we run a few workflows using FormMule and CopyDown – both helpful utilities to get data to the right people, in the right format.

FormMule Mail Template Screen

We run three notifications using FormMule – the first one for the reporter, as a feedback loop, to make sure they get the data they submitted back via email.

We could to this by enabling the automatic, and mandated, Form response, but generating an email is cleaner, I think, and it helps to have standardized subjects for emails that make searching easy… See how that came back full circle?

Using [ PBDF ] in the subject line for the generated emails enables the end user to make simple, and effective, filters in their inbox! Nice!

PBDF Referral Mail Template

The second flow triggers on a major behavior issues, where an office referral is made. The PRINCIPAL tag above is a converted data cell where I ran an IF statement to simply produce the correct email address from the selected building. I use this quite often, and it’s easy to setup:

PBDF Email Conversion from Building

Speaking of conversions, I mentioned the time stamp earlier. It’s super helpful to have an accurate idea of when the Form was submitted, but using that timestamp in a FormMule workflow creates a problem, depending on how you want it formatted, especially if you want to include the time (which in this case, we do!).

Here’s how to fix that – simply run another column that converts the timestamp (a DATE formatted cell) to TEXT format:

=TEXT(A2,"dddd mmmm dd, yyyy at hh:mm:ss")

PBDF Timestamp Conversion

The above results in a nicely formatted date & time stamp, and one you can then easily use in the FormMule workflow email notification to let folks know when the Form was submitted.

And yes, why don’t you spend some time figuring out how to exploit the =TEXT function, especially for DATE related issues. For example, how would you convert 03/25/2018 to 20180325? <– all the time for the SAT roster upload, for example! 🙂 Or, convert it to Mon Mar 25, 2018…

And the best part is that it converts it to text, and using the CopyDown tool, this will run and paste as text for all submissions.

Lastly, we run a data logger flow, that grabs all the information from the Form submission, and shoots it to separate email for data review. But, you could easily run that later, or export the data for analysis in spreadsheet format, if you wanted to.

We hope the PBDF workflow will work better, collect data faster, and ultimately get help and assistance for the kids that need it sooner than with the paper workflow.

We will implement it before the end of the year.

Follow Up

What ways are you automating behavior data collection in your district? What ideas do you have for process improvement? Let us know in the comment section below.

Now go collect some data!


Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.