How to send notification to users who has not logged in for over a week

[ian10]

Hi

I have been asked if there is a way of sending a notification email to students and/or the student support officer that they have not logged in for a period of time?

I know I could run the Last User Access report and extract the users details who have not logged in for, say over a week.  But this could end up being run on a weekly basis.

So is there an easier way?

[jerry_nguyen]

@ian10 

The easiest solution I can think of is to use Canvas Data 2. By using workflow automation tools like n8n, you can schedule a report to be sent out on a weekly basis (or send a reminder email to each student). You can check out my article here on setting it up here: 📔Automate Canvas with n8n 

The SQL query (PostgreSQL) you would use to get the data is as follows:

select canvas.users.name, canvas.pseudonyms.unique_id, canvas.pseudonyms.last_login_at from canvas.pseudonyms 
Join canvas.users on canvas.users.id = canvas.pseudonyms.user_id
WHERE canvas.pseudonyms.last_login_at < current_date - interval '7 days'

[ian10]

Hi Jerry

Off to have a look at this, thanks for giving me a direction to look at.

[James · Accepted solution]

@ian10 

You mentioned wanting to know when students had last logged in. That could be taken literally -- which is what Jerry ( @jerry_nguyen ) did with his Canvas Data 2 solution. It could also be taken in the broader sense -- I want to know when students aren't doing anything in Canvas.

Canvas has both login information and activity information. You'll sometimes get very different results. Login dates are almost always older than activity dates. I say almost always because Canvas doesn't log activity unless it goes on for more than a couple minutes. A student could login but not do anything, and the login would be more recent than the activity. The activity is also specific to a course -- this allows you to tell that Johnnie (not his real name) hasn't been active in his trigonometry class while he has still been working on psychology, sociology, and speech.

I would discourage using the last login as your way of determining whether someone has been active in Canvas. For people using the mobile app, it doesn't register a new login every time they open the app. If someone leaves the browser open with themselves logged into Canvas, it may not show a new login (this may depend on how you have them set up). When available, login only shows you that they were opening Canvas, not that they were doing anything once they got there.

You really should be looking at the enrollment activity or actually submitting assignments. Canvas Data 2 has those submissions and it also has the last_activity_at and total_activity_time available in the canvas__enrollments table. You'll need to add more logic, of course, to make sure the student is currently enrolled, but Canvas Data 2 makes that possible (provided you know SQL).

A couple of months ago, I had some bad experiences with Canvas Data 2 and so my trust level isn't where it should be. The pseudonyms table was missing some logins -- including mine. It had my user account, but it wasn't linked. It's not like I'm new, my Canvas account was created in 2012. There were submission data that was missing as well. It seems that most of the missing data tended to be old -- like 2014 or before. That doesn't apply in your case, but my confidence was shaken.

In the end, I ended up going back to the API and fetching the data from there. You can ask it to generate a report through the API, but I chose to iterate through all of the enrollments. We're a small college and I used to have a process that downloaded the last activity and total time nightly for all enrollments. The whole process took less than 15 minutes. It also allowed me to do tracking so that I could see when a student was doing something.

Another option is to use Canvas Data Services (Live Events). I set up an Amazon SQS queue and have Canvas notify me when students do certain tasks. I included clicking on navigation links, viewing content, submitting assignments, taking quizzes, responding to discussions, etc. There is a lot of data there, and Live Events makes a best attempt to send the notification so it's not 100% reliable, but it is another option for getting information as it happens. As a small school, the cost is negligible (I usually pay less than 1 USD per month)

I download those notifications and process them, storing the results in a database than I can use for answering questions. Mostly my questions involve a statistics project where my students try to figure out what they need to do to be successful in the class based on their own information from the term (yes, it suffers from survivor bias). But I've also used it to track down other issues.

[ian10]

Hi James

Yeah think your right, they want to know if the students are actually doing something constructive while they are logged in.

It looks like I need to be investigating the Canvas Data 2 as a way for extracting the data, as to the SQL, we have some people here who know a whole lot more about it than I do.

Thanks for taking the time to reply, it's appreciated.