Download an admin report of all bouncing email addresses

[stelpstra]

This idea has been developed and deployed to Canvas

Admins who are allowed to view notifications can access the Bounced Emails tab in Admin Tools. The Bounced Emails tab displays bounced email notifications for all users in an account. Please refer to  Admin Tools: Bounced Emails Tab (2021-01-16 Releas...  and   How do I view bounced email notifications for user...  for instructions on viewing bounced email notifications and downloading the csv report.

---

Situation

Frequently email addresses used in Canvas bounce emails while admin are completely unaware this is happening.

Possible causes:

• people start using other email addresses and close the previous one
• people change email addresses because their name changed because of marriage
• people change jobs, the old address is terminated and a new one created
• the university disables the university student email account for inactive students
• new mail is rejected because the mailbox completely full
• etc.

Currently Canvas does not (yet) offer a way to proactively identify these problems, while teachers assume all their students receive notifications of e.g. announcements, assignments, etc. as configured in the notification settings.

Suggested solution

Let Canvas keep a record of every bounce email returned when sending notification emails to users, containing the data:

• user id
• email address
• primary address or not
• date and time of the bounce

Add a report in https://institution.instructure.com/accounts/1/settings#tab-reports containing this data from one year max in CSV format.

[James]

 @stelpstra ‌,

Your solutions didn't come out, just 4 bullet points, so I don't know if this is one of your solutions or not, but here's something quick I threw together using Canvas Data‌ that might help.

EDIT: The SQL was deleted because it returned the wrong list.

[stelpstra]

 @James ‌, thanks for pointing this out! The text somehow disappeared but I added the missing part again.

I like your suggestion, although if I understand correctly we would need to keep a record store to be able to query this (currently we don't have this). Is the workflow state retired set in case when bounces happen?

[stelpstra]

Please also vote for https://community.canvaslms.com/ideas/8404-add-state-bouncing-to-the-api-communication-workflowstate... 

[James]

 @stelpstra ,

I showed it as SQL because we keep our Canvas Data in a MySQL database. If you have it as flat files, there would be more work involved. The users and communication_channels files should be a couple of the smaller ones, so they should fit inside an Excel spreadsheet.

I'm not sure what retired means, I was in a rush between class and lunch. If you look at the communication_channel_dim documentation on the Canvas Data Portal, it just shows 'unconfirmed' and 'active', but when I looked at the table, it had retired in there as well. It appears that the retired is for a different purpose.

When I look more into it, it seems that we should use the communication_channel_fact.bounce_count instead. It says

Number of permanent bounces since the channel was last reset. If it's greater than 0, then no email is sent to the channel, until it is either reset by a siteadmin or it is removed and re-added by a user.

So, here is a revised SQL

SELECT u.canvas_id, u.name, c.address 
FROM communication_channel_dim c 
JOIN communication_channel_fact f ON (f.communication_channel_id = c.id) 
JOIN user_dim u ON (c.user_id = u.id) 
WHERE c.type='email' 
  AND f.bounce_count > 0 
  AND u.workflow_state = 'registered';
‍‍‍‍‍‍‍

I'll edit the original reply's SQL to reflect that it doesn't work.

This gives me 4084 addresses for our college. The other only gave 255. Looking at the addresses, a bunch of them are a "noreply" type that we give once the students are no longer students.

[Steve_25]

We had the same problem. Ended up creating a node.js script to masquerade as each user, look at the html of their notification page and see if the little warning triangle was there. It runs every night and then sends me an email of the users who have bouncing channels  

Not exactly elegant. Wasn't actually aware of the Canvas Data solution haha!