Student Course Metrics
The mart_student_activity_score.student_course_metrics mart aggregates data about a student’s performance and activities in a course on a weekly basis. The mart_student_activity_score_rolling.student_course_metrics mart aggregates this data for a rolling 7-day window.
BQ Prod Dataset Locations
mart_student_activity_score
mart_student_activity_score_rolling
Student Activity Score vs. Student Activity Score Rolling
Both the student_activity_score and student_activity_score_rolling marts aggregate a student's performance and activities in a course in a 7-day time window. The primary difference is whether the 7-day time window is static or moving.
The student_activity_score mart defines static weeks in a term, every week starting on Sunday and ending on Saturday. The weeks are calculated based on the start date of the academic session, term, or course. We rely on BigQuery's DATE_DIFF function to determine a week number, and this BigQuery function has the start of the weeks on Sundays. We perform the date diff anchored against the start date of the academic session, the academic term, or the course in scope. Our logic prefers the academic session or academic term start dates when possible; this creates a more even baseline for week numbers across courses at scale. Each week in the term will have a fixed start and end date based on the session/term/course start date.
The student_activity_score_rolling mart, on the other hand, defines rolling weeks by defining a 7-day time window for each day in a term. The day in a term is calculated using the same logic described above for calculating the week in a term. The time window includes the current day and the previous 6 days. There are some instances where the time window is shorter: if the day in the term is still within 6 days of the first day in the term, since the earliest possible start date is the first day in the term. The rolling 7-day window can be defined on and after the 7th day in the term, until the final day in the term.
In this documentation, any reference to week in the student_activity_score mart is a static week, while week in the student_activity_score_rolling mart is a rolling 7-day window.
Interactive Mart Dependency Diagram
The following visualization shows the construction of this data mart defined in the UDP marts repository. More information on the repository and diagram can be found on this marts page.
Schema
mart/student_activity_score/student_course_metrics
university_id
STRING
The SIS ID of the student.
global_user_id
INTEGER
The UDP ID of the student.
canvas_user_id
STRING
The Canvas ID of the student.
term_name
STRING
The academic term the student is enrolled in, i.e. Fall 2020.
session_name
STRING
The academic session the student is enrolled in.
week_number
INTEGER
The week of the academic session, term, or course.
week_start_date
DATE
The start date of the week of the academic session, term, or course.
week_end_date
DATE
The end date of the week of the academic session, term, or course.
campus_name
STRING
The campus of the student for the given academic term.
academic_program
STRING
The academic program the student is enrolled in for the given academic term.
course_code
STRING
The SIS ID of the course offering.
course_global_id
INTEGER
The UDP ID of the course offering.
course_canvas_id
STRING
The Canvas ID of the course offering.
navigation_time
FLOAT
The amount of time the student spent in Canvas for the given week in the course and the week prior.
num_sessions
INTEGER
The number of Canvas sessions of the student for the given week in the course and the week prior.
assignments_due
INTEGER
The number of assignments due in the course for the given week.
submissions
INTEGER
The number of assignments submitted by the student for the given week in the course.
assignments_due_cumulative
INTEGER
The number of assignments due in the course from the first day of the term to the current week, inclusive.
submissions_cumulative
INTEGER
The number of assignments submitted by the student from the first day of the term to the current week, inclusive.
mart/student_activity_score_rolling/student_course_metrics
The student_activity_score_rolling.student_course_metrics schema is the same as the student_activity_score.student_course_metrics, except that the static week definitions are replaced with rolling week definitions. Additionally, time spent in the course is no longer defined for a two week period, but the 7-day window defined by the week start and end dates.
week_number
INTEGER
The week of the academic term.
week_start_date
DATE
The start date of the rolling 7-day window, inclusive.
week_end_date
DATE
The end date of the rolling 7-day window, inclusive.
navigation_time
FLOAT
The amount of time the student spent in Canvas for the given week in the course.
num_sessions
INTEGER
The number of Canvas sessions of the student for the given week in the course.
Assignments Due
The number of assignments due in a course offering for a given week is defined in the mart_helper.context__student_activity_score(_rolling)__assignments table. The week_number of an assignment is determined by the due date of the assignment and the start and end dates of the defined week (either static or rolling). The student activity score only includes published assignments with due dates and non-zero points possible.
Submissions
The number of assignments submitted by a student in a course offering for a given week is defined in the mart_helper.context__student_activity_score(_rolling)__submissions table. The week_number of an assignment and its corresponding submission is determined by the due date of the assignment and the start and end dates of the defined week (either static or rolling). We only count submissions for published assignments with due dates, non-zero points possible, and allowed submission types of 'on_paper', 'Assignments', 'not_graded', 'none', ' ', or 'external_tool'.
Navigation time and Number of Sessions
The amount of time spent and number of sessions in the learning environment for a student in a course is defined in the mart_helper.event__student_activity_score__navigation_time table. The week_number of an event is defined as the difference in weeks between the event time and the start date of the academic session, academic term, or course. The navigation_time for a given week in a course for a student is the amount of time spent in the learning environment for the prior two weeks. The num_sessions is the number of sessions for the prior two weeks. We define the two week range for a week based on the latest event time for the given week in the course. Periods of inactivity, 25 minutes or more between two consecutive events, are removed from our aggregations.
In student_activity_score_rolling.student_course_metrics, the amount of time spent and number of sessions in the learning environment for a student in a course is defined in the mart_helper.event__student_activity_score_rolling__navigation_time table. The week_number of an event is determined by the date of the event and the start and end dates of the defined week. The navigation_time for a given week in a course for a student is the amount of time spent in the learning environment for the defined week only. The num_sessions is the number of sessions for the defined week only. This differs from the non-rolling version of this mart, which uses a two-week range. Periods of inactivity, 25 minutes or more between two consecutive events, are removed from our aggregations.
Last updated