Weekday early alert digest for at-risk students in Slack

Every weekday at 7am, scan Blackboard for students who are falling behind and post an early alert digest to our advising team in Slack. Build this on a cron trigger. Blackboard does not send outgoing webhooks for gradebook activity, so the workflow does all of its own reading on each run.

Start with Blackboard Get Courses to list the courses, and use Get Terms to resolve each course's term and its start and end dates. Only evaluate courses whose term is currently running. Skip any course whose term has not started yet, and skip courses whose term has already ended. Note that the courses collection lives on the v3 API path while gradebook columns and attempts are on v2, so use the exact version segment each operation requires.

For each course in scope, call Get Course Memberships to resolve the enrolled students and the course instructor. Keep the instructor's name so I can include it in the alert, and only evaluate members whose course role is student.

For each course, call Get Grade Columns to list the gradebook columns. For each column, use Get Column Grades to read every student's score and Get Column Attempts to see which students have no submitted attempt. Treat an assignment as missing when the student has no submitted attempt and the due date has already passed. Work out each student's running score as the percentage they currently hold across graded work, and ignore columns that are not graded work.

All of these Blackboard list endpoints page with offset and limit and return a paging.nextPage link. Follow nextPage until it is absent so I see every student, not just the first page. Blackboard allows roughly 10,000 requests per day across the whole institution, so trim responses with the fields parameter where you can and keep the scan limited to courses in the current term.

Flag a student when their current score falls below a configurable threshold, defaulting to 70 percent, or when they have two or more missing assignments. Rank the flagged students by severity, weighing how far below the threshold the score sits, how many assignments are missing, and how far into the term the course is. A student sitting 20 points below the line in week 10 is more urgent than one sitting 2 points below in week 2, and the ranking should reflect that.

Before alerting, read the existing tracking log in Google Sheets with Get Values and check whether each student has already been flagged for the same course within the last seven days. If they have, stay quiet unless their situation has gotten worse, meaning their score has dropped further or they have picked up another missing assignment since that last flag. This is what keeps advisors from seeing the same names every morning and tuning the digest out.

Post one Slack message per course into our advising channel so each message is a self contained brief an advisor can act on. Name the student, the course and its instructor, the student's current score, and exactly which assignments are missing rather than just a count. List students in severity order, and add a short line of written context for each one explaining why they are on the list. Use Slack formatting, which means single asterisks for bold. If no student in a course is flagged, do not post a message for that course at all.

Append one row per flagged student to our Google Sheets tracking log with Append Values, including the date of the run, the student's name, the course, the instructor, the current score, the number of missing assignments, the names of the missing assignments, and the reason they were flagged. One row per student per run, so we can look back week over week and see whether an intervention actually moved the score.

Make the score threshold, the missing assignment count, the seven day quiet period, the advising channel, and the tracking spreadsheet easy to change in one place at the top of the workflow. Ask me which Slack channel the advising team uses and which spreadsheet to log to when the workflow is set up.