Community

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
brayden_d_ross
New Member

Data Frame containing all Instructor-Student interactions (i.e. Student Interactions Report)

Jump to solution

I'm fairly new to the canvas system, and I am in search of a comprehensive way to see all Student Interaction reports for all teachers by course. I'm not sure if theres a data frame available in the Schema Documentation or if there is an API to pull this data. Any help would be greatly appreciated!

1 Solution

Accepted Solutions
James
Community Champion

 @brayden_d_ross ,

For clarification, are you talking about the data that drives the Communications block of the analytics page, except you would like to get it for all courses and all instructors and all students?

295555_pastedImage_2.png

If you're not familiar with that page, I found it by going by going to a Course, choosing View Course Analytics, and then picking a student.

That information does come from an API call: Get user-in-a-course-level messaging data 

However, it only returns the number of communications by the instructor and student per day and not the actual content of the messages.

295577_pastedImage_4.png

Also note that it only returns conversation data, it does not include submission comments.

With the API approach, you would need to iterate through every combination of course and student to get the information and then save it and make your own report.

If you need comments made on submissions, then you will need to look at the Submissions API and there are several calls there that allow you to fetch a submission. You need to add a query parameter of include[]=submission_comments to get the data.

By the way, getting submission comments is a very lengthy process -- you essentially have to download every submission for every assignment for every course. I've just recently started doing that because of another project where I need to look at submissions and it is by far the most intensive portion of the process. I needed the submission history as well and it duplicates a lot of information. I set up my script to process 25 concurrent API calls and it still took me about 12 minutes to download everything that's just for this semester. 12 minutes may not sound long, but we're a small school, and I'm making requests in parallel. If I made those in series, waiting for one to finish before calling the next, it could take me 5 hours (25*12=300 minutes) to finish.

If you have access to Canvas Data, then there are some tables to look at that will make the process easier, but it will be at least a few days stale because of the way Canvas Data works.

The submission_comment_fact and submission_comment_dim tables contain information about comments left on submissions, including the actual text fo the comment in the submission_comment_dim.comment field. This does not contain a recipient_id, it's been deprecated, so you will have to find out who the users receiving it are from other tables.

The conversation_dim, conversation_message_participant_fact, and conversation_message_dim tables contain information sent through the Inbox. The conversation_message_dim.body field contains the content of the message. I'd start with the conversation_message_participant_fact table as it as the links to the other two tables and the user who it was to. It also includes the number of bytes. The converstation_message_dim has the date, author, and body.

Here's an example MySQL statement that lists when a message was sent, who sent it, and who received it for my statistics course this semester (where the example above came from). It's probably not exactly what you're after, but it may be enough to get you headed in the right direction.

SELECT
  cmd.created_at,
  (SELECT canvas_id FROM user_dim WHERE cmpf.user_id = user_dim.id) AS recipient_id,
  (SELECT sortable_name FROM user_dim WHERE cmpf.user_id = user_dim.id) AS recipient,
  (SELECT canvas_id FROM user_dim WHERE cmd.author_id = user_dim.id) AS author_id,
  (SELECT sortable_name FROM user_dim WHERE cmd.author_id = user_dim.id) AS author,
  cd.canvas_id AS course_id,
  cd.name AS course_name
FROM conversation_message_participant_fact cmpf
JOIN conversation_message_dim cmd ON (cmpf.conversation_message_id = cmd.id)
JOIN course_dim cd ON (cmpf.course_id = cd.id)
WHERE cd.canvas_id = 2335978
  AND cmpf.user_id != cmd.author_id;

The last line is necessary because it seems that users are a recipient to their own messages and I didn't want the duplication.

Also note that when I aggregated that report, the numbers matched the data from the analytics (except for the information where Canvas Data hadn't caught up with live data). Submission comments are something separate and not included in the analytics supplied by Canvas.

View solution in original post

2 Replies
kona
Community Coach
Community Coach

 @brayden_d_ross , sorry that your question has sat here for so long without a response! I can’t think of anything on the front end of Canvas that would give you this information, but yes, the API could probably do it. To help get you some feedback on the API part of this, I’m going to share this with the Canvas Developers‌ group in the Community to see if they can help. You might also consider joining this group so you’ll have access to their resources and previous discussions. 

Kona

James
Community Champion

 @brayden_d_ross ,

For clarification, are you talking about the data that drives the Communications block of the analytics page, except you would like to get it for all courses and all instructors and all students?

295555_pastedImage_2.png

If you're not familiar with that page, I found it by going by going to a Course, choosing View Course Analytics, and then picking a student.

That information does come from an API call: Get user-in-a-course-level messaging data 

However, it only returns the number of communications by the instructor and student per day and not the actual content of the messages.

295577_pastedImage_4.png

Also note that it only returns conversation data, it does not include submission comments.

With the API approach, you would need to iterate through every combination of course and student to get the information and then save it and make your own report.

If you need comments made on submissions, then you will need to look at the Submissions API and there are several calls there that allow you to fetch a submission. You need to add a query parameter of include[]=submission_comments to get the data.

By the way, getting submission comments is a very lengthy process -- you essentially have to download every submission for every assignment for every course. I've just recently started doing that because of another project where I need to look at submissions and it is by far the most intensive portion of the process. I needed the submission history as well and it duplicates a lot of information. I set up my script to process 25 concurrent API calls and it still took me about 12 minutes to download everything that's just for this semester. 12 minutes may not sound long, but we're a small school, and I'm making requests in parallel. If I made those in series, waiting for one to finish before calling the next, it could take me 5 hours (25*12=300 minutes) to finish.

If you have access to Canvas Data, then there are some tables to look at that will make the process easier, but it will be at least a few days stale because of the way Canvas Data works.

The submission_comment_fact and submission_comment_dim tables contain information about comments left on submissions, including the actual text fo the comment in the submission_comment_dim.comment field. This does not contain a recipient_id, it's been deprecated, so you will have to find out who the users receiving it are from other tables.

The conversation_dim, conversation_message_participant_fact, and conversation_message_dim tables contain information sent through the Inbox. The conversation_message_dim.body field contains the content of the message. I'd start with the conversation_message_participant_fact table as it as the links to the other two tables and the user who it was to. It also includes the number of bytes. The converstation_message_dim has the date, author, and body.

Here's an example MySQL statement that lists when a message was sent, who sent it, and who received it for my statistics course this semester (where the example above came from). It's probably not exactly what you're after, but it may be enough to get you headed in the right direction.

SELECT
  cmd.created_at,
  (SELECT canvas_id FROM user_dim WHERE cmpf.user_id = user_dim.id) AS recipient_id,
  (SELECT sortable_name FROM user_dim WHERE cmpf.user_id = user_dim.id) AS recipient,
  (SELECT canvas_id FROM user_dim WHERE cmd.author_id = user_dim.id) AS author_id,
  (SELECT sortable_name FROM user_dim WHERE cmd.author_id = user_dim.id) AS author,
  cd.canvas_id AS course_id,
  cd.name AS course_name
FROM conversation_message_participant_fact cmpf
JOIN conversation_message_dim cmd ON (cmpf.conversation_message_id = cmd.id)
JOIN course_dim cd ON (cmpf.course_id = cd.id)
WHERE cd.canvas_id = 2335978
  AND cmpf.user_id != cmd.author_id;

The last line is necessary because it seems that users are a recipient to their own messages and I didn't want the duplication.

Also note that when I aggregated that report, the numbers matched the data from the analytics (except for the information where Canvas Data hadn't caught up with live data). Submission comments are something separate and not included in the analytics supplied by Canvas.