[ARCHIVED] Emulating calculation of last_activity_at in enrollment_dim with Requests... (for weekly engagement reporting)

[jago_brown]

Hello,

We want to retrospectively calculate the last activity for each week of the 2017 academic year (within defined canvas terms) - so starting back in October 2017.

I'm thinking this might be possible to calculate with the requests data, if we knew the queries that are run to generate the enrollment_dim table? 

• Does anyone know precisely how  "last_activity_at" in "enrollment_dim" is calculated?
• or if there is more documentation available to help? than can be found  here: Canvas Data Portal or Instructure, Inc. · GitHub 

UPDATE:

(being a bit slow waking up today)

So presumably "enrollment_dim" is close to a dump or the Enrollments API (Enrollments - Canvas LMS REST API Documentation ) and how "last_activity_at" is calculated can be seen in the Canvas ruby source code.

So my question is really does anyone have a script to emulate calculating "last_activity_at" from CanvasData i.e drawling through requests?

Kind reagards

Jago

[robotcars]

Depending on the DB engine you are using, if you have MAX, filter and group by:

Course ID

User ID

MAX(timestamp)

This should give you the last activity for each student course enrollment, using the requests table.

-- mssql
-- last student activity for course, using requests
SELECT 
     u.canvas_id AS canvas_user_id,
     u.name AS student_name,
     t.canvas_id AS canvas_term_id,
     t.name AS term_name,
     c.canvas_id AS canvas_course_id,
     c.name     AS course_name,
     MAX(timestamp) last_activity_at
FROM 
     dbo.requests r
     JOIN dbo.user_dim u ON (u.id = r.user_id)
     JOIN dbo.course_dim c ON (c.id = r.course_id)
     JOIN dbo.enrollment_term_dim t ON (t.id = c.enrollment_term_id)
     JOIN dbo.enrollment_dim e ON (e.course_id = c.id)
WHERE
     t.canvas_id = 5507 -- target term
     AND e.type = 'StudentEnrollment' -- students
GROUP BY 
     u.canvas_id,
     u.name,
     t.canvas_id,
     t.name,
     c.canvas_id,
     c.name‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Each JOIN slows down the query, the above took more than 40 minutes and I stopped it before it finished.

While the following only took 60 seconds returning 49,533 rows

SELECT
     r.user_id,
     --u.canvas_id AS canvas_user_id,
     --u.name AS student_name,
     t.canvas_id AS canvas_term_id,
     t.name AS term_name,
     c.canvas_id AS canvas_course_id,
     c.name     AS course_name,
     MAX(timestamp) last_activity_at
FROM 
     dbo.requests r
     --JOIN dbo.user_dim u ON (u.id = r.user_id)
     JOIN dbo.course_dim c ON (c.id = r.course_id)
     JOIN dbo.enrollment_term_dim t ON (t.id = c.enrollment_term_id)
     --JOIN dbo.enrollment_dim e ON (e.course_id = c.id)
WHERE
     t.canvas_id = 5507 -- target term
     --AND e.type = 'StudentEnrollment' -- students
GROUP BY 
     r.user_id,
     t.canvas_id,
     t.name,
     c.canvas_id,
     c.name‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

In contrast, this took 75 seconds.

SELECT 
     u.canvas_id AS canvas_user_id,
     u.name AS student_name,
     a.*
FROM (
     SELECT
          r.user_id,
          t.canvas_id AS canvas_term_id,
          t.name AS term_name,
          r.course_id,
          c.name     AS course_name,
          MAX(timestamp) last_activity_at
     FROM 
          dbo.requests r
          JOIN dbo.course_dim c ON (c.id = r.course_id)
          JOIN dbo.enrollment_term_dim t ON (t.id = c.enrollment_term_id)
     WHERE
          r.user_id IS NOT NULL
          AND t.canvas_id = 5507 -- target term
     GROUP BY 
          r.user_id,
          t.canvas_id,
          t.name,
          r.course_id,
          c.name
) a
JOIN dbo.user_dim u ON (u.id = a.user_id)
JOIN dbo.enrollment_dim e ON (e.course_id = a.course_id AND e.user_id = a.user_id)
--WHERE e.type = 'StudentEnrollment' -- students‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

I'm getting some different row counts between tests, it probably has to do with various teacher as student and test student enrollments. I don't have time to drill into it at the moment, but hopefully this will give you something to work with.

I'd also suspect that some students might come back to their course after the semester, so you might look at filtering requests before enrollment_term_dim.date_end.

These queries happen after I reduce rows, https://community.canvaslms.com/message/41824#comment-42118 

[jago_brown]

Thanks Robert. Very helpful. Its only enrollment_term_dim I would need to load into Postgres so I can test these (but I only need to run against one term so may just adapt your query).  I will look at more closely soon. Thanks again for sharing

Jago