# Level 1 Aggregated * [Taskforce vs. Taskforce Rolling](#taskforce-vs.-taskforce-rolling) * [Interactive Mart Dependency Diagram](#taskforcemartlevel1aggregated-interactivemartdependencydiagram) * [Schema](#taskforcemartlevel1aggregated-schema) * [Field Structure and Subcategories](#taskforcemartlevel1aggregated-fieldstructureandsubcategories) * [Assignment Weight Groupings](#taskforcemartlevel1aggregated-assignmentweightgroupings) * [Week\_in\_term Calculation](#taskforcemartlevel1aggregated-week_in_termcalculation) * [Assignments](#taskforcemartlevel1aggregated-assignments) * [Number of Submissions](#taskforcemartlevel1aggregated-numberofsubmissions) * [Number of Assignments](#taskforcemartlevel1aggregated-numberofassignments) * [Number of Missing Submissions](#taskforcemartlevel1aggregated-numberofmissingsubmissions) * [Number of Late Submissions](#taskforcemartlevel1aggregated-numberoflatesubmissions) * [Average Time Buffer in Hours](#taskforcemartlevel1aggregated-averagetimebufferinhours) * [Average Published Score Percentage](#taskforcemartlevel1aggregated-averagepublishedscorepercentage) * [Discussions](#taskforcemartlevel1aggregated-discussions) * [Discussion Entry Counts](#taskforcemartlevel1aggregated-discussionentrycounts) * [Discussion Counts](#taskforcemartlevel1aggregated-discussioncounts) * [Total Discussion Counts](#taskforcemartlevel1aggregated-totaldiscussioncounts) * [Average Discussion Entry Length](#taskforcemartlevel1aggregated-averagediscussionentrylength) * [Learning Environment Activity](#taskforcemartlevel1aggregated-learningenvironmentactivity) * [View Days](#taskforcemartlevel1aggregated-viewdays) * [Sessions with 10, 20, and 30 Minute Cutoffs](#taskforcemartlevel1aggregated-sessionswith10-20-and30minutecutoffs) * [Tool Launches](#taskforcemartlevel1aggregated-toollaunches) * [File Views](#taskforcemartlevel1aggregated-fileviews) The *mart\_taskforce.level1\_aggregated* mart aggregates data about a student’s performance and activities in a course on a weekly basis. The *mart\_taskforce\_rolling.level1\_aggregated* mart aggregates this data for a rolling 7-day window. ### **BQ Prod Dataset Locations** * mart\_taskforce * mart\_taskforce\_rolling ## Taskforce vs. Taskforce Rolling Both the *taskforce* and *taskforce\_rolling* mart compare a student's performance and activities in a course to the average student for a 7-day time window. The primary difference is whether the 7-day time window is static or moving. The *taskforce* 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. (see [Week Calculation](#taskforcemartlevel1aggregated-week_in_termcalculation)) Each week in the term will have a fixed start and end date based on the session/term/course start date. The *taskforce\_rolling* mart, on the other hand, defines rolling weeks by defining a 7-day time window for each day in a term. The time window includes the day in the term and the 6 previous days. The only instances when the time window is shorter is when 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. It won't be until the 7th day in the term that there is a rolling 7-day window that can be defined. From that day on, all of the days will have a rolling 7-day window defined, until the final day in the term. As the *taskforce\_rolling* mart has more records per enrollment than the *taskforce* mart, we have decided to include data from fewer terms in the *taskforce\_rolling* mart. The *taskforce\_rolling* mart includes Spring 2026 data onwards, while the *taskforce* mart starts in Spring 2021. In this documentation, any reference to week will refer to the static week for the *taskforce* mart and the rolling 7-day time window for the *taskforce\_rolling* mart. ## Interactive Mart Dependency Diagram The following visualization shows the construction of this data mart defined in the [UDP marts](https://gitlab.com/unizin-community/unizin-data-platform/marts) repository. More information on the repository and diagram can be found on [this marts page.](/products/data-and-analytics/unizin-data-platform/data-stores/data-marts.md) {% hint style="info" %} [Click here to open the interactive chart.](https://assets.public.unizin.org/udp-marts/mart/mart__taskforce__level1_aggregated.svg) {% endhint %}

mart_taskforce.level1_aggregated

## Schema ### **mart/taskforce/level1\_aggregated**
FieldData TypeDescription
udp_person_idINTEGERThe UDP ID of the student.
lms_person_idSTRINGThe LMS ID of the student.
udp_course_offering_idINTEGERThe UDP ID of the course offering.
lms_course_offering_idSTRINGThe LMS ID of the course offering.
week_in_termINTEGERThe week of the academic term.
week_start_dateDATEThe start date of the week of the academic term.
week_end_dateDATEThe end date of the week of the academic term.
num_tiny_submissionsINTEGERThe number of submissions classified as ‘tiny’, or making up a tiny percentage of the student’s final grade.
num_small_submissionsINTEGERThe number of submissions classified as ‘small’, or making up a small percentage of the student’s final grade.
num_medium_submissionsINTEGERThe number of submissions classified as ‘medium’, or making up a medium percentage of the student’s final grade.
num_large_submissionsINTEGERThe number of submissions classified as ‘large’, or making up a large percentage of the student’s final grade.
num_major_submissionsINTEGERThe number of submissions classified as ‘major’, or making up a major percentage of the student’s final grade.
num_unweighted_submissionsINTEGERThe number of unweighted submissions.
num_weighted_submissionsINTEGERThe number of weighted submissions.
num_submissions_without_due_dateINTEGERThe number of submissions without due dates.
num_submissions_with_due_dateINTEGERThe number of submissions with due dates.
num_submissionsINTEGERThe total number of submissions.
num_tiny_assignmentsINTEGERThe number of assignments classified as ‘tiny’, or making up a tiny percentage of the student’s final grade.
num_small_assignmentsINTEGERThe number of assignments classified as ‘small’, or making up a small percentage of the student’s final grade.
num_medium_assignmentsINTEGERThe number of assignments classified as ‘medium’, or making up a medium percentage of the student’s final grade.
num_large_assignmentsINTEGERThe number of assignments classified as ‘large’, or making up a large percentage of the student’s final grade.
num_major_assignmentsINTEGERThe number of assignments classified as ‘major’, or making up a major percentage of the student’s final grade.
num_unweighted_assignmentsINTEGERThe number of unweighted assignments.
num_weighted_assignmentsINTEGERThe number of weighted assignments.
num_assignments_without_due_dateINTEGERThe number of assignments without due dates.
num_assignments_with_due_dateINTEGERThe number of assignments with due dates.
num_assignmentsINTEGERThe total number of assignments.
num_tiny_missing_submissionsINTEGERThe number of missing submissions classified as ‘tiny’, or making up a tiny percentage of the student’s final grade.
num_small_missing_submissionsINTEGERThe number of missing submissions classified as ‘small’, or making up a small percentage of the student’s final grade.
num_medium_missing_submissionsINTEGERThe number of missing submissions classified as ‘medium’, or making up a medium percentage of the student’s final grade.
num_large_missing_submissionsINTEGERThe number of missing submissions classified as ‘large’, or making up a large percentage of the student’s final grade.
num_major_missing_submissionsINTEGERThe number of missing submissions classified as ‘major’, or making up a major percentage of the student’s final grade.
num_unweighted_missing_submissionsINTEGERThe number of unweighted missing submissions.
num_weighted_missing_submissionsINTEGERThe number of weighted missing submissions.
num_missing_submissionsINTEGERThe total number of missing submissions.
num_tiny_late_submissionsINTEGERThe number of late submissions classified as ‘tiny’, or making up a tiny percentage of the student’s final grade.
num_small_late_submissionsINTEGERThe number of late submissions classified as ‘small’, or making up a small percentage of the student’s final grade.
num_medium_late_submissionsINTEGERThe number of late submissions classified as ‘medium’, or making up a medium percentage of the student’s final grade.
num_large_late_submissionsINTEGERThe number of late submissions classified as ‘large’, or making up a large percentage of the student’s final grade.
num_major_late_submissionsINTEGERThe number of late submissions classified as ‘major’, or making up a major percentage of the student’s final grade.
num_unweighted_late_submissionsINTEGERThe number of unweighted late submissions.
num_weighted_late_submissionsINTEGERThe number of weighted late submissions.
num_late_submissionsINTEGERThe total number of late submissions.
avg_time_buffer_hrs_tinyFLOATThe average submission time buffer in hours for submissions classified as ‘tiny’, or making up a tiny percentage of the student’s final grade.
avg_time_buffer_hrs_smallFLOATThe average submission time buffer in hours for submissions classified as ‘small’, or making up a small percentage of the student’s final grade.
avg_time_buffer_hrs_mediumFLOATThe average submission time buffer in hours for submissions classified as ‘medium’, or making up a medium percentage of the student’s final grade.
avg_time_buffer_hrs_largeFLOATThe average submission time buffer in hours for submissions classified as ‘large’, or making up a large percentage of the student’s final grade.
avg_time_buffer_hrs_majorFLOATThe average submission time buffer in hours for submissions classified as ‘major’, or making up a major percentage of the student’s final grade.
avg_time_buffer_hrs_unweightedFLOATThe average submission time buffer in hours for unweighted submissions.
avg_time_bufer_hrs_weightedFLOATThe average submission time buffer in hours for weighted submissions.
avg_time_buffer_hrsFLOATThe average submission time buffer in hours for all submissions.
avg_published_score_pct_tinyNUMERICThe average published score percentage for submissions classified as ‘tiny’, or making up a tiny percentage of the student’s final grade.
avg_published_score_pct_smallNUMERICThe average published score percentage for submissions classified as ‘small’, or making up a small percentage of the student’s final grade.
avg_published_score_pct_mediumNUMERICThe average published score percentage for submissions classified as ‘medium’, or making up a medium percentage of the student’s final grade.
avg_published_score_pct_largeNUMERICThe average published score percentage for submissions classified as ‘large’, or making up a large percentage of the student’s final grade.
avg_published_score_pct_majorNUMERICThe average published score percentage for submissions classified as ‘major’, or making up a major percentage of the student’s final grade.
avg_score_pct_unweightedNUMERICThe average score percentage for unweighted submissions.
avg_published_score_pct_weightedNUMERICThe average published score percentage for weighted submissions.
avg_published_score_pct_without_due_dateNUMERICThe average published score percentage for submissions without due dates.
avg_published_score_pct_with_due_dateNUMERICThe average published score percentage for submissions with due dates
avg_published_scoreNUMERICThe average published score percentage for all submissions.
avg_published_score_pct_tiny_cumulativeNUMERICThe weekly cumulative average published score percentage for all tiny submissions
avg_published_score_pct_small_cumulativeNUMERICThe weekly cumulative average published score percentage for all small submissions
avg_published_score_pct_medium_cumulativeNUMERICThe weekly cumulative average published score percentage for all medium submissions
avg_published_score_pct_large_cumulativeNUMERICThe weekly cumulative average published score percentage for all large submissions
avg_published_score_pct_major_cumulativeNUMERICThe weekly cumulative average published score percentage for all major submissions
avg_published_score_pct_unweighted_cumulativeNUMERICThe weekly cumulative average published score percentage for all unweighted submissions
avg_published_score_pct_weighted_cumulativeNUMERICThe weekly cumulative average published score percentage for all weighted submissions
avg_published_score_pct_without_due_date_cumulativeNUMERICThe weekly cumulative average published score percentage for all submissions without a due date
avg_published_score_pct_with_due_date_cumulativeNUMERICThe weekly cumulative average published score percentage for all submissions with a due date .
avg_published_score_cumulativeNUMERICThe weekly cumulative average published score percentage for all submissions
discussion_entry_countINTEGERThe number of discussion entries posted by the student.
discussion_post_countINTEGERThe number of discussion posts posted by the student.
discussion_reply_countINTEGERThe number of discussion replies posted by the student.
discussion_countINTEGERThe total number of discussions interacted with by the student.
assignment_discussion_countINTEGERThe total number of discussions associated with an assignment interacted with by the student.
threaded_discussion_countINTEGERThe total number of threaded discussions interacted with by the student.
side_comment_discussion_countINTEGERThe total number of side comment discussions interacted with by the student.
total_discussion_countINTEGERThe total number of discussions available in the course offering.
total_assignment_discussion_countINTEGERThe total number of discussions associated with assignments available in the course offering.
total_threaded_discussion_countINTEGERThe total number of threaded discussions available in the course offering.
total_side_comment_discussion_countINTEGERThe total number of side comment discussions available in the course offering.
avg_discussion_entry_lengthFLOATThe average length of the student’s discussion entries.
avg_discussion_post_lengthFLOATThe average length of the student’s discussion posts.
avg_discussion_reply_lengthFLOATThe average length of the student’s discussion replies.
view_daysINTEGERThe number of view days in the LMS.
num_sessions_10minINTEGERThe number of sessions in the LMS, with a 10 minute cutoff when calculating sessions.
total_time_seconds_10minINTEGERThe total time spent in the LMS in seconds, with a 10 minute cutoff when calculating sessions.
total_actions_10minINTEGERThe total number of actions in the LMS, with a 10 minute cutoff when calculating sessions.
avg_time_seconds_10minFLOATThe average amount of time spent in the LMS in seconds, with a 10 minute cutoff when calculating sessions.
avg_actions_10minFLOATThe average number of actions in the LMS, with a 10 minute cutoff when calculating sessions.
num_sessions_20minINTEGERThe number of sessions in the LMS, with a 20 minute cutoff when calculating sessions.
total_time_seconds_20minINTEGERThe total time spent in the LMS in seconds, with a 20 minute cutoff when calculating sessions.
total_actions_20minINTEGERThe total number of actions in the LMS, with a 20 minute cutoff when calculating sessions.
avg_time_seconds_20minFLOATThe average amount of time spent in the LMS in seconds, with a 20 minute time cutoff when calculating sessions.
avg_actions_20minFLOATThe average number of actions in the LMS, with a 20 minute cutoff when calculating sessions.
num_sessions_30minINTEGERThe number of sessions in the LMS, with a 30 minute cutoff when calculating sessions.
total_time_seconds_30minINTEGERThe total time spent in the LMS in seconds, with a 30 minute cutoff when calculating sessions.
total_actions_30minINTEGERThe total number of actions in the LMS, with a 30 minute cutoff when calculating sessions.
avg_time_seconds_30minFLOATThe average amount of time spent in the LMS in seconds, with a 30 minute cutoff when calculating sessions.
avg_actions_30minFLOATThe average number of actions in the LMS, with a 30 minute cutoff when calculating sessions.
num_tool_launchesINTEGERThe total number of LTI tool launches.
num_tools_launchedINTEGERThe total number of LTI tools launched.
tool_launch_detail.launch_app_nameSTRINGAn array of the launch app names of the LTI tools launched.
tool_launch_detail.num_launchesINTEGERAn array of the number of launches for each LTI tool.
file_viewsINTEGERThe number of file views.
num_files_viewedINTEGERThe number of files viewed.
file_access_detail.file_idINTEGERAn array of the UDP IDs of the files viewed.
file_access_detail.display_nameSTRINGAn array of the display names of the files viewed.
file_access_detail.content_typeSTRINGAn array of the content types of the files viewed.
file_access_detail.content_sub_typeSTRINGAn array of the content subtypes of the files viewed.
file_access_detail.num_times_viewedINTEGERAn array of the number of times each file was viewed.
### **mart/taskforce\_rolling/level1\_aggregated** The *taskforce\_rolling* schema is the same as the *taskforce* schema, except that the static week definitions are replaced with rolling week definitions. `week_in_term` refers to the static week in term for both marts. | Field | Data Type | Description | | ----------------- | --------- | ------------------------------------------------------ | | week\_in\_term | INT64 | 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. | ## Field Structure and Subcategories This level 1 table has many fields, so we’ve grouped them together in the following subcategories * Assignments * Submissions * Assignments * Missing Assignments * Late Submissions * Submission Time Buffer * Published Scores * Discussions * Discussion Threads * Discussion Entries * Learning Environment Activity * View days * Sessions * Tool Launches * File Views #### Assignment Weight Groupings Many fields have names that include *tiny*, *small*, *medium*, *large*, and *major*. These classifications are based on the *group\_weight* field in the ‘learner\_activity\_group’ entity. The cases are as follows: * If the *group\_weight* is null or equal to 0%, the submission is ‘unweighted’. * If the *group\_weight* is less than or equal to 2%, the submission is ‘tiny’. * If the *group\_weight* is greater than 2% and less than or equal to 5%, the submission is ‘small’. * If the *group\_weight* is greater than 5% and less than or equal to 10%, the submission is ‘medium’. * If the *group\_weight* is greater than 10% and less than or equal to 25%, the submission is ‘large’. * If the *group\_weight* is greater than 25%, the submission is ‘major’. * If the *group\_weight* is not null and greater than 0%, the submission is 'weighted'. #### Week Calculation 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. The start dates for both terms and courses can be sourced from either the SIS or LMS. In the presence of start dates from SIS, we coalesce to these values, but LMS start dates will surface if nulls are present on the SIS side. The week\_in\_term is 1-indexed. There will be no `week_in_term` values of 0 in the data mart. ### Assignments #### Number of Submissions **Fields** * num\_tiny\_submissions * num\_small\_submissions * num\_medium\_submissions * num\_large\_submissions * num\_major\_submissions * num\_unweighted\_submissions * num\_weighted\_submissions * num\_submissions\_without\_due\_date * num\_submissions\_with\_due\_date * num\_submissions **Context Store Dependencies** * learner\_activity\_result * learner\_activity * learner\_activity\_group * learner\_activity\_override **Description** These fields present the number of submissions by a student in a course for each week in the term. A submission is defined by the *learner\_activity\_result\_id* field in the ‘learner\_activity\_result’ entity. The week of the submission, *week\_in\_term,* is determined by the due date of the assignment. The due date of the assignment is derived from the *due\_date* fields in 'learner\_activity' and 'learner\_activity\_override.' If the due date of the learner activity is overridden, then the due date is defined as the *due\_date* in 'learner\_activity\_override.' If not, it is just the *due\_date* in 'learner\_activity.' If the due date is null, the week of the submission is derived from the *response\_date* field in the ‘learner\_activity\_result’ entity instead. One of the fields, *num\_submissions,* shows the overall number of submissions, while *num\_submissions\_with\_due\_date* and *num\_submissions\_without\_due\_date* show the number of submissions with and without due dates respectively. The rest of the fields focus on submissions either classified as ‘unweighted’, ‘tiny’, ‘small’, ‘medium’, ‘large’, ‘major’, or 'weighted.' #### Number of Assignments **Fields** * num\_tiny\_assignments * num\_small\_assignments * num\_medium\_assignments * num\_large\_assignments * num\_major\_assignments * num\_unweighted\_assignments * num\_weighted\_assignments * num\_assignments\_without\_due\_date * num\_assignments\_with\_due\_date * num\_assignments **Context Store Dependencies** * learner\_activity\_result * learner\_activity * learner\_activity\_group * learner\_activity\_override **Description** These fields present the number of assignments for a student in a course for each week in the term. An assignment is defined by the *learner\_activity\_id* in the ‘learner\_activity’ entity. The week of the assignment, *week\_in\_term,* is determined by its due date. The due date of the assignment is derived from the *due\_date* fields in 'learner\_activity' and 'learner\_activity\_override.' If the due date of the learner activity is overridden, then the due date is defined as the *due\_date* in 'learner\_activity\_override.' If not, it is just the *due\_date* in 'learner\_activity.' If the due date is null and at least one submission is associated with the assignment, the week of the assignment is derived from the *response\_date* field in the ‘learner\_activity\_result’ entity instead. One of the fields, *num\_assignments,* shows the total number of assignments, while *num\_assignments\_with\_due\_date* and *num\_assignments\_without\_due\_date* show the number of assignments with and without due dates respectively. The rest of the fields focus on assignments either classified as ‘unweighted’, ‘tiny’, ‘small’, ‘medium’, ‘large’, ‘major’, or 'weighted.' #### Number of Missing Submissions **Fields** * num\_tiny\_missing\_submissions * num\_small\_missing\_submissions * num\_medium\_missing\_submissions * num\_large\_missing\_submissions * num\_major\_missing\_submissions * num\_unweighted\_missing\_submissions * num\_weighted\_missing\_submissions * num\_missing\_submissions **Context Store Dependencies** * learner\_activity\_result * learner\_activity * learner\_activity\_group * learner\_activity\_override **Description** These fields present the number of missing submissions of a student in a course for each week in the term. A submission is considered missing if it meets the following criteria: 1. The *published\_score* field in ‘learner\_activity\_result’ is null. 2. The *grading\_status* field in ‘learner\_activity\_result’ has a value of ‘unsubmitted’. 3. The due date of the assignment has a date older than the current date. The field *num\_missing\_submissions* presents the total number of missing submissions. The rest of the fields focus on missing submissions classified as ‘unweighted’, ‘tiny’, ‘small’, ‘medium’, ‘large’, ‘major’, or 'weighted.' #### Number of Late Submissions **Fields** * num\_tiny\_late\_submissions * num\_small\_late\_submissions * num\_medium\_late\_submissions * num\_large\_late\_submissions * num\_major\_late\_submissions * num\_unweighted\_late\_submissions * num\_weighted\_late\_submissions * num\_late\_submissions **Context Store Dependencies** * learner\_activity\_result * learner\_activity * learner\_activity\_group * learner\_activity\_override **Description** These fields present the number of late submissions by a student in a course for each week in the term. A submission is late if it meets the following criteria: * The *submission\_date* field in ‘learner\_activity\_result’ has a later date than the due date of the assignment One of the fields, *num\_late\_submissions,* shows the total number of late submissions. The rest of the fields focus on late submissions classified as ‘unweighted’, ‘tiny’, ‘small’, ‘medium’, ‘large’, ‘major’, or 'weighted.' #### Average Time Buffer in Hours **Fields** * avg\_time\_buffer\_hrs\_tiny * avg\_time\_buffer\_hrs\_small * avg\_time\_buffer\_hrs\_medium * avg\_time\_buffer\_hrs\_large * avg\_time\_buffer\_hrs\_major * avg\_time\_buffer\_hrs\_unweighted * avg\_time\_buffer\_hrs\_weighted * avg\_time\_buffer\_hrs **Context Store Dependencies** * learner\_activity\_result * learner\_activity * learner\_activity\_group * learner\_activity\_override **Description** These fields present the average time buffer in hours for submissions by a student in a course for each week in the term. The time buffer of a submission is the difference between when the assignment was due and when the student submitted the assignment. We calculate this as the difference in hours between the due date of the assignment and the date of the *submission\_date* field in ‘learner\_activity\_result’. One of the fields, *avg\_time\_buffer\_hrs,* shows the average time buffer in hours for all submissions. The rest of the fields focus on submissions classified as ‘unweighted’, ‘tiny’, ‘small’, ‘medium’, ‘large’, ‘major’, or 'weighted.' #### Average Published Score Percentage **Fields** * avg\_published\_score\_pct\_tiny * avg\_published\_score\_pct\_small * avg\_published\_score\_pct\_medium * avg\_published\_score\_pct\_large * avg\_published\_score\_pct\_major * avg\_score\_pct\_unweighted * avg\_published\_score\_pct\_weighted * avg\_published\_score\_pct\_without\_due\_date * avg\_published\_score\_pct\_with\_due\_date * avg\_published\_score * avg\_published\_score\_pct\_tiny\_cumulative * avg\_published\_score\_pct\_small\_cumulative * avg\_published\_score\_pct\_medium\_cumulative * avg\_published\_score\_pct\_large\_cumulative * avg\_published\_score\_pct\_major\_cumulative * avg\_score\_pct\_unweighted\_cumulative * avg\_published\_score\_pct\_weighted\_cumulative * avg\_published\_score\_pct\_without\_due\_date\_cumulative * avg\_published\_score\_pct\_with\_due\_date\_cumulative * avg\_published\_score\_cumulative **Context Store Dependencies** * learner\_activity\_result * learner\_activity * learner\_activity\_group * learner\_activity\_override **Description** These fields show the average published score percentage of submissions by a student in a course for each week in the term. The published score percentage of a submission is calculated by dividing the published score of the submission by the maximum number of points for the assignment. To do this, we divide the *published\_score* field in ‘learner\_activity\_result’ by the *points\_possible* field in ‘learner\_activity’. To calculate the average published score percentage for submissions, we first multiply the calculated published score percentage by the group weight of the assignment, which is derived from the *group\_weight* field in ‘learner\_activity\_group’. We add all of those computed values together for each student, course, and week in term. Then, we divide that sum by the sum of the assignment group weights for each student, course, and week to compute the average published score percentage. If there are no weighted assignments for the given week, meaning all of the assignments due that week are unweighted, the average published score percentage is defined as the sum of all scores on the assignments due divided by the sum of the maximum number of points possible for the assignments due. Once again, one of these fields, *avg\_published\_score*, shows the average published score percentage for all submissions. The fields *avg\_published\_score\_pct\_with\_due\_date* and *avg\_published\_score\_without\_due\_date* show the average published score percentage for submissions with and without due dates respectively. The other fields each focus on a specific set of submissions, classified as ‘unweighted’, ‘tiny’, ‘small’, ‘medium’, ‘large’, ‘major’, or 'weighted.' **Example 1 - All Weighted Submissions** Given all weighted submissions in scope, the calculation for `average` is defined as: $$ avg = \frac{\Sigma(weight \* published\_score\_pct)}{\Sigma(weight)} $$ Assume the following 3 submissions and scores are weighted as follows:
SubmissionWeightPoints PossibleScorePublished Score %
140504080
2103030100
35015010066.67
The `avg_published_score` calculation will yield a value of 75.33% **Example 2 - All Unweighted Submissions** Given all unweighted submissions in scope, the calculation for `average` is defined as: $$ avg = \frac{\Sigma(score)}{\Sigma(points\_possible)} $$ Assume the same 3 submissions and scores, except they are now all unweighted:
SubmissionWeightPoints PossibleScorePublished Score %
1null504080
2null3030100
3null15010066.67
The `avg_published_score` calculation will yield a value of 73.91% **Example 3 - Mix of Weighted and Unweighted Submissions** If a mix of weighted and unweighted submissions are in scope, the calculation only includes weighted. This matches the numeric values we receive in the `scores` table directly from Canvas in CD2. In these cases, we suggest leveraging both the `avg_published_score` fields plus the `_unweighted` fields. Assume the same 3 submissions and scores, except all but 1 are weighted:
SubmissionWeightPoints PossibleScorePublished Score %
1null504080
2103030100
35015010066.67
The `avg_published_score` calculation will yield a value of 72.22%. The `avg_publsihed_score_pct_unweighted` calculation will yield a value of 80%. ### Discussions #### Discussion Entry Counts **Fields** * discussion\_entry\_count * discussion\_post\_count * discussion\_reply\_count **Context Store Dependencies** * discussion\_entry **Description** These fields present the number of discussion entries posted by a student in a course for each week in the term. A discussion entry is defined by the *discussion\_entry\_id* field in the ‘discussion\_entry’ entity. The week of the discussion entry is defined by the date the discussion entry was created, which is derived from the *created\_date* field in ‘discussion\_entry’. The field *discussion\_entry\_count* shows the total number of discussion entries, and is computed by counting the number of distinct *discussion\_entry\_id* values. A discussion post is defined as a discussion entry with the value of the *position* field in ‘discussion\_entry’ equal to 1. Therefore, *discussion\_post\_count* is computed by counting the number of distinct *discussion\_entry\_id* values with *position* = 1. On the other hand, a discussion reply is defined as a discussion entry with the value of the *position* field greater than 1. To compute *discussion\_reply\_count,* we count the number of distinct *discussion\_entry\_id* values with *position* > 1. #### Discussion Counts **Fields** * discussion\_count * assignment\_discussion\_count * threaded\_discussion\_count * side\_comment\_discussion\_count **Context Store Dependencies** * discussion\_entry * discussion **Description** These fields present the number of discussions a student has interacted with in a course for each week in the term. A discussion is defined by the *discussion\_id* field in the ‘discussion’ entity, and the week of the discussion is defined by the *created\_date* of the discussion entry. The field *discussion\_count* shows the total number of discussions, and is computed by counting the number of distinct *discussion\_id* values. The field *assignment\_discussion\_count* looks at the number of discussions that are associated with an assignment. This field is computed by counting the number of distinct *discussion\_id* values that have non-null *learner\_activity\_id* values. The *threaded\_discussion\_count* field is computed by counting the number of distinct *discussion\_id* values with *discussion\_type =* ‘threaded’, and the *side\_comment\_discussion\_count* field is computed by counting the number of distinct *discussion\_id* values with *discussion\_type* = ‘side\_comment’. #### Total Discussion Counts **Fields** * total\_discussion\_count * total\_assignment\_discussion\_count * total\_threaded\_discussion\_count * total\_side\_comment\_discussion\_count **Context Store Dependencies** * discussion **Description** These fields present the total number of discussions in a course for a given week in the term. The field *total\_discussion\_count* is computed by counting the number of distinct *discussion\_id* values in the ‘discussion’ entity for each course and week in the term. The *total\_assignment\_discussion\_count* field counts the number of distinct *discussion\_id* values with non-null *learner\_activity\_id* values. The *total\_threaded\_discussion\_count* field is computed by counting the number of distinct *discussion\_id* values with *discussion\_type =* ‘threaded’ , while the *total\_side\_comment\_discussion\_thread* field is computed by counting the number of distinct *discussion\_id* values with *discussion\_type =* ‘side\_comment’ instead. #### Average Discussion Entry Length **Fields** * avg\_discussion\_entry\_length * avg\_discussion\_post\_length * avg\_discussion\_reply\_length **Context Store Dependencies** * discussion\_entry **Description** These fields present the average length of the discussion entry messages for a student in a course for a given week in the term. These fields are computed by taking the average of the *message\_length* field in the ‘discussion\_entry’ entity for each student, course, and week in the term. For the *avg\_discussion\_entry\_length* field, we take the average for all discussion entries. The *avg\_discussion\_post\_length* field only takes the average for discussion entries with *position* = 1, while the *avg\_discussion\_reply\_length* field only takes the average for discussion entries with *position* > 1. ### Learning Environment Activity #### View Days **Fields** * view\_days **Description** The *view\_days* field shows the number of days a student viewed the learning environment for a course in a given week. This field is computed using the UDP’s event store. We count the number of distinct session dates for a student in a course for a week in the term. The session date is defined by the minimum *event\_time* of a session in the learning environment. #### Sessions with 10, 20, and 30 Minute Cutoffs **Fields** * num\_sessions\_10min * total\_time\_seconds\_10min * total\_actions\_10min * avg\_time\_seconds\_10min * avg\_actions\_10min * num\_sessions\_20min * total\_time\_seconds\_20min * total\_actions\_20min * avg\_time\_seconds\_20min * avg\_actions\_20min * num\_sessions\_30min * total\_time\_seconds\_30min * total\_actions\_30min * avg\_time\_seconds\_30min * avg\_actions\_30min **Description** For this mart, we define sessions using three different time cutoffs: 10 minutes, 20 minutes, and 30 minutes. We define the time spent in a session by adding up the duration of events in the session. For Caliper events in the event store, we define the duration of events by calculating the difference between the event time and the previous event time. If this difference is greater than or equal to the time cutoff for the session, the duration of the event is not counted towards the session and a new session is defined. So, for example, if an event duration is greater than or equal to 10 minutes, we will not include that event in a session with a 10 minute cutoff, and will define a new session. To show how sessions are defined, take an event with a duration of 9 minutes and 30 seconds. This event is less than 10 minutes, so it is included in a session with a 10 minute cutoff. It is also included in a session with 20 minute and 30 minute cutoffs, since the event is shorter than 20 and 30 minutes respectively. However, an event that is 13 minutes long is not included in a session with a 10 minute cutoff, since it is longer than 10 minutes. It is still included in a session with 20 and 30 minute cutoffs though. The same goes for an event with a duration of 25 minutes. This event is not included in a session with 10 and 20 minute cutoffs, but is still included in a session with a 30 minute cutoff. And finally, a 35 minute event is not included in a session with 10, 20, or 30 minute cutoffs.
Event Length (in Minutes)Sessions that Include the Event
0 - 9Sessions with a 10 minute cutoff, 20 minute cutoff, and 30 minute cutoff
10 - 19Sessions with a 20 minute cutoff and a 30 minute cutoff
20 - 29Sessions with a 30 minute cutoff
30 and aboveNone
The *num\_sessions\_x* fields present the total number of sessions with the given time cutoff for a student in the learning environment of a course for a week in the term. The *total\_time\_seconds\_x* fields show the amount of time in seconds a student spent in the learning environment. To find the total time spent in seconds, we add up all the durations of the sessions with the given time cutoff per student, course, and week in term. The *total\_actions\_x* fields present the number of actions of a student in the learning environment. The number of actions is calculated by counting the number of events associated with the sessions with the given time cutoff. The *avg\_time\_seconds\_x* fields compute the average time in seconds a student spent in the learning environment. This is computed by dividing the total time spent in the learning environment (*total\_time\_seconds\_x)* by the total number of sessions in the learning environment (*num\_sessions\_x)* for each student, course, and week in term. The *avg\_actions\_x* fields present the average number of actions in the learning environment. To compute this, we divide the total number of actions in the learning environment (*total\_actions\_x)* by the total number of sessions in the learning environment (*num\_sessions\_x)* for each student, course, and week in term. Please visit our [separate interaction session mart documentation page here](/products/data-and-analytics/unizin-data-platform/data-stores/data-marts/interaction-sessions.md) for a visual example and more information about interaction sessions. #### Tool Launches **Fields** * num\_tool\_launches * num\_tools\_launched * tool\_launch\_detail.launch\_app\_name * tool\_launch\_detail.num\_launches **Description** These fields focus on the LTI tools launched by a student in the learning environment of a course for each week in the term. We define a LTI tool launch using the ‘expanded’ table in the event store. An event is a LTI launch event if the name of the object ([*object.name*](http://object.name)) is ‘context\_external\_tool’. The field *num\_tool\_launches* presents the total number of LTI tool launches. This field is computed by counting the number of LTI launch events for each student, course, and week. *num\_tools\_launched* presents the number of distinct LTI tools launched. This is computed by counting the number of distinct *launch\_app\_name* values. The *launch\_app\_name* field is extracted from the *asset\_name* field of the *object.extensions* chunk of the JSON Caliper event. The *tool\_launch\_detail* fields go into more detail about the LTI tools that were launched. *tool\_launch\_detail.launch\_app\_name* is an array of all the LTI tools launched. This array includes all the *launch\_app\_name* values associated with the given student, course, and week. The *tool\_launch\_detail.num\_launches* is an array that presents the number of launches corresponding to each LTI tool in the *tool\_launch\_detail.launch\_app\_name* array. This field computes the number of launches for each tool by counting the number of launch events for each *launch\_app\_name* value in the array. #### File Views **Fields** * file\_views * num\_files\_viewed * file\_access\_detail.file\_id * file\_access\_detail.display\_name * file\_access\_detail.content\_type * file\_access\_detail.content\_sub\_type * file\_access\_detail.num\_times\_viewed **Context Store Dependencies** * file **Description** These fields focus on the files viewed by a student in the learning environment of a course for each week in the term. We identify that a file has been viewed by using the ‘expanded’ table in the event store and the ‘file’ entity in the context store. We first identify the subset of events in which an interaction with an object occurred, or when the object ID for an event is not null. The object ID is extracted from the *object.id* field in the ‘expanded’ table. We only want to include events in which an interaction with a file occurred, so we only include events that have an object ID corresponding to a file ID found in the ‘file’ entity. The *file\_views* field presents the total number of file views. To compute this field, we count the number of file interaction events per student, course, and week. The *num\_files\_viewed* field presents the number of files viewed. This is calculated by counting the number of distinct *file\_id* values associated with file interaction events. The *file\_access\_detail* fields go into more detail about the files the student accessed. The *file\_access\_detail.file\_id* is an array of all the IDs of the files accessed. The *file\_access\_detail.display\_name* is an array of the corresponding display names of these files, derived from the *display\_name* field in the ‘file’ entity. The *file\_access\_detail.content\_type* field is an array of the corresponding content types of these files. These values are extracted from the *content\_type* field in the ‘file’ entity, extracting the part of the string before the ‘/’ from the field. So, for example, if the value of the *content\_type* field in ‘file’ is ‘application/pptx’, the value of *file\_access\_detail.content\_type* would be ‘application’. The *file\_access\_detail.content\_sub\_type* field is an array of the content subtypes of the files accessed. These values are again extracted from the *content\_type* field in the ‘file’ entity, but taking the part of the string after the ‘/’ instead. For the previous example, the value of *file\_access\_detail.content\_sub\_type* would be ‘pptx’. The final field in this section is the *file\_access\_detail.num\_times\_viewed* field. This is an array of the number of times each file accessed was viewed. To compute the number of times each file was viewed, we count the number of events associated with each file. --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://resources.unizin.org/products/data-and-analytics/unizin-data-platform/data-stores/data-marts/taskforce/level-1-aggregated.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.