# Long Inactivity

The *mart\_course\_offering.long\_inactivity* mart surfaces students actively enrolled in courses in the current term with extended time without activity in the LMS. The *mart\_course\_section.long\_inactivity* mart also includes this information, but at the course section level.

Extended time without activity is defined as either:

1. A person is actively enrolled in a course, but no Caliper events have been generated.
2. A person is actively enrolled in a course, but the latest Caliper event is at least 5-days in the past.

## BQ Prod Dataset Location <a href="#lastactivity-bqproddatasetlocation" id="lastactivity-bqproddatasetlocation"></a>

* mart\_course\_offering
* mart\_course\_section

## Interactive Mart Dependency Diagram <a href="#lastactivity-interactivemartdependencydiagram" id="lastactivity-interactivemartdependencydiagram"></a>

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.](https://resources.unizin.org/products/data-and-analytics/unizin-data-platform/data-stores/data-marts)

{% hint style="info" %}
[Click here to open the interactive chart.](https://assets.public.unizin.org/udp-marts/mart/mart_course_offering__long_inactivity.svg)
{% endhint %}

<div data-full-width="true"><figure><img src="https://3709019308-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FKYwtHNGgdPXS3PWAlZUr%2Fuploads%2Fgit-blob-1a9a1d1045d9132f37fbaadc3d25a241d24519cf%2Fmart_course_offering__long_inactivity.svg?alt=media" alt=""><figcaption><p>mart_course_offering.long_inactivity</p></figcaption></figure></div>

## Schema <a href="#lastactivity-schema" id="lastactivity-schema"></a>

### **Mart/Course\_offering/long\_inactivity**

<table data-full-width="true"><thead><tr><th width="299.33333333333337"></th><th width="167">Type</th><th>Description</th></tr></thead><tbody><tr><td>udp_course_offering_id</td><td>INTEGER</td><td>The UDP ID of the Course offering.</td></tr><tr><td>lms_course_offering_id</td><td>STRING</td><td>The LMS ID of the Course offering.</td></tr><tr><td>udp_person_id</td><td>INTEGER</td><td>The UDP ID of the person.</td></tr><tr><td>lms_person_id</td><td>STRING</td><td>The LMS ID of the person.</td></tr><tr><td>academic_organization_array</td><td>ARRAY&#x3C;STRING></td><td>Array of string values for the academic organization(s)</td></tr><tr><td>academic_organization_display</td><td>STRING</td><td>The name of the academic organization(s) as a string with comma separation.</td></tr><tr><td>academic_term_name</td><td>STRING</td><td>The name of the academic term, i.e. <em>Fall 2020.</em></td></tr><tr><td>term_begin_date</td><td>DATE</td><td>The start date for the academic term.</td></tr><tr><td>term_end_date</td><td>DATE</td><td>The end date for the academic term.</td></tr><tr><td>course_offering_title</td><td>STRING</td><td>The title of the Course offering, i.e. <em>Linear Algebra.</em></td></tr><tr><td>course_start_date</td><td>DATE</td><td>The start date of the Course offering.</td></tr><tr><td>course_end_date</td><td>DATE</td><td>The end date of the Course offering.</td></tr><tr><td>instructor_display</td><td>STRING</td><td>An array of all the instructors' names for a Course offering written as a string.</td></tr><tr><td>instructor_name_array</td><td>ARRAY&#x3C;STRING></td><td>An array of all instructors’ names for a Course offering.</td></tr><tr><td>instructor_email_address_array</td><td>ARRAY&#x3C;STRING></td><td>An array of all instructors' emails for a Course offering</td></tr><tr><td>instructor_email_address_display</td><td>STRING</td><td>An array of all instructors' emails for a Course offering written as a string.</td></tr><tr><td>person_name</td><td>STRING</td><td>The name of the person.</td></tr><tr><td>last_activity</td><td>DATETIME</td><td>The time of the person’s last activity in the Course offering.</td></tr><tr><td>has_no_activity</td><td>INTEGER</td><td>Indicates if the user has no recorded activity in the course. 1 = no activity; 0 = any activity</td></tr><tr><td>days_since_last_activity</td><td>INTEGER</td><td>The number of days between the last recorded activity and the current datetime.</td></tr><tr><td>is_5_days</td><td>INTEGER</td><td>Indicates if the days since last activity is greater than or equal to 5 days. 1 = 5 days or greater. 0 = otherwise.</td></tr><tr><td>is_7_days</td><td>INTEGER</td><td>Indicates if the days since last activity is greater than or equal to 7 days. 1 = 7 days or greater. 0 = otherwise.</td></tr><tr><td>is_10_days</td><td>INTEGER</td><td>Indicates if the days since last activity is greater than or equal to 10 days. 1 = 10 days or greater. 0 = otherwise.</td></tr><tr><td>is_14_days</td><td>INTEGER</td><td>Indicates if the days since last activity is greater than or equal to 14 days. 1 = 14 days or greater. 0 = otherwise.</td></tr></tbody></table>

### **Mart/Course\_section/last\_activity**

The course section mart includes all the fields found in the course offering mart, as well as two more fields, which are presented in the table below.

<table data-full-width="true"><thead><tr><th width="283.33333333333337">Field Name</th><th width="235">Type</th><th>Description</th></tr></thead><tbody><tr><td>udp_course_section_id</td><td>INTEGER</td><td>The UDP ID of the Course section.</td></tr><tr><td>lms_course_section_id</td><td>STRING</td><td>The LMS ID of the Course section.</td></tr></tbody></table>

### Fields <a href="#lastactivity-fields" id="lastactivity-fields"></a>

#### **last\_activity**

This field is pulled from the *mart\_course\_offering.last\_activity* datamart. Per person, per course we pull the most recent event timestamp, regardless of event type. This signifies the latest activity of any type for a student in a course.

#### **has\_no\_activity**

For instances of active enrollment in the current term, an absence of Caliper events is checked here. If no Caliper events generated by the enrolled student can be found, we set this flag to 1. Otherwise, 0.

#### **days\_since\_last\_activity**

This is calculated using the [DATE\_DIFF ](https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions#date_diff)function, and an integer representing the number of days between the current date and the last\_activity for the person-course\_offering/section:

```
COALESCE(DATE_DIFF(current_datetime(),last_activity, DAY), null)
```

A COALESCE to null is used for instances where has\_no\_activity applies.

#### **is\_X\_days**

These fields also adhere to a DATE\_DIFF calculation with the current date. For the is\_5\_days field, the following logic is applied:

```
CASE
    WHEN last_activity is null then null
    WHEN DATE_DIFF(current_datetime(),last_activity, DAY) >= 5 then 1 
    ELSE 0
END AS is_5_days
```

Again, we respect nulls first in cases of no activity. The only change for the 7, 10, and 14-day thresholds is in the >= check.

### Criteria for Actively Enrolled in Current Term

For the active academic term, we look for all rows in the *academic\_term* entity whose *term\_begin\_date* is before the current date and whose *term\_end\_date* is after the current date. We filter out all courses and terms with dates outside of these bounds or terms with null values for begin and/or end dates.

For the "actively enrolled" students, we look at the [role\_status](https://docs.udp.unizin.org/tables/ref_role_status.html) and [enrollment\_status](https://docs.udp.unizin.org/tables/ref_enrollment_status.html) values in *course\_section\_enrollment*.

Excluded role\_status values:

* Dropped
* Wait Listed
* Not Enrolled
* No Data
* None
* Completed

Excluded enrollment\_status values:

* Inactive
* Not Enrolled
* No Data
* None
* Completed

OR logic is used here; if either a role\_status or enrollment\_status in the exclusion lists above surfaces for a student, that enrollment is filtered out of the final results.

## Tableau Template Workbook

As a starting point for visualization for this data mart, Unizin has created a Tableau template workbook available for download here:

{% file src="<https://3709019308-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FKYwtHNGgdPXS3PWAlZUr%2Fuploads%2FPLzemHDleghZCbxHluzw%2FLast%20Activity%20and%20Long%20Inactivity%20Dashboard%20.twbx?alt=media&token=c71a2b6c-ee3d-4a82-85a0-43e9881d0afc>" %}
Last Activity and Long Inactivity Dashboard Tableau Workbook File
{% endfile %}

### Connecting to Data <a href="#lmstooluse-fields-1" id="lmstooluse-fields-1"></a>

The default data connection in the workbook is to Unizin's synthetic data. After downloading the workbook, the first step will be to transfer the connection to your own production UDP data. Here is a [guide document](https://docs.google.com/document/d/1v_4JjgCz46nky5WZbZZGmO_6F6E2zoyBYifOSoZJNk4/edit?usp=sharing) to help with this process; Tableau also has a [published doc](https://help.tableau.com/current/pro/desktop/en-us/connect_basic_replace.htm) for replacing data sources.  For any issues connecting to source data, please contact <support@unizin.org>.

### Tableau Dashboard Filters <a href="#lmstooluse-fields-1" id="lmstooluse-fields-1"></a>

The following fields are used as filters in the Tableau Workbook:

* Academic Organization - The academic department name
* Instructor Display - The list of names of the course's instructors.
* Course Offering Title - The title of the course offering.
* UDP Course Offering ID - The UDP ID of the course offering.
* Academic Term - The academic term of the course; the default selection is the current term.

These filters are in scope for all visuals in the dashboard.

### Tableau Visualizations

<figure><img src="https://3709019308-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FKYwtHNGgdPXS3PWAlZUr%2Fuploads%2FpagivwMu3bWf1h14a1zY%2FScreenshot%202025-05-12%20at%2012.31.21%E2%80%AFPM.png?alt=media&#x26;token=2cfcdb50-4feb-487c-9b48-849fffb36044" alt="" width="563"><figcaption><p>Metric Cards</p></figcaption></figure>

<figure><img src="https://3709019308-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FKYwtHNGgdPXS3PWAlZUr%2Fuploads%2FC7t5LFmBEiFZYlamn6Cu%2FScreenshot%202025-05-12%20at%2012.31.41%E2%80%AFPM.png?alt=media&#x26;token=63ecf420-d96d-47d2-b729-7ebadd097ed6" alt="" width="563"><figcaption><p>Metric Cards</p></figcaption></figure>

The metric cards show a quick summary of student activity in the course. They include the total number of enrolled, active, and inactive students. You can also see the average number of days students were active or inactive, and how many hours they spent in the course. This helps spot long periods of inactivity and low engagement.

<figure><img src="https://3709019308-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FKYwtHNGgdPXS3PWAlZUr%2Fuploads%2FZFE8egDHAPubtUMKsBsB%2FScreenshot%202025-05-12%20at%206.59.16%E2%80%AFPM.png?alt=media&#x26;token=82d7d760-4fcf-40db-af4d-be053382529c" alt=""><figcaption><p>Long Inactivity by Day of the Week and Hour</p></figcaption></figure>

The Long Inactivity by Day of Week and Hour table highlights trends in long inactivity over time, showing when students tend to disengage. It includes breakdowns by day of the week and hour of the day.

<figure><img src="https://3709019308-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FKYwtHNGgdPXS3PWAlZUr%2Fuploads%2Fw0QnDqZAqChTOy1YyBwi%2FScreenshot%202025-05-12%20at%207.00.20%E2%80%AFPM.png?alt=media&#x26;token=2018724e-9814-466d-9d48-a32222e5f780" alt=""><figcaption><p>Days Percent Inactivity</p></figcaption></figure>

This chart displays the percentage of total long inactivity occurrences for each day of the week.&#x20;

<figure><img src="https://3709019308-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FKYwtHNGgdPXS3PWAlZUr%2Fuploads%2F9Jd2yZchkfAApszUg6Ey%2FScreenshot%202025-05-12%20at%207.01.59%E2%80%AFPM.png?alt=media&#x26;token=8de838dc-7f74-49e2-82d4-ef360d56d779" alt=""><figcaption><p>Proportion of Students with Long Inactivity in Courses</p></figcaption></figure>

This chart shows how inactivity is distributed among students in courses. The percentage of students who have been inactive in one or more courses is displayed here.&#x20;

<figure><img src="https://3709019308-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FKYwtHNGgdPXS3PWAlZUr%2Fuploads%2FpdlhIsiXvbqlsLgXG09t%2FScreenshot%202025-05-12%20at%207.02.20%E2%80%AFPM.png?alt=media&#x26;token=0642d901-0ce1-4232-8ddd-ba0efcc97f8e" alt=""><figcaption><p>Proportion of Students with Long Inactivity Over Time</p></figcaption></figure>

The pie chart shows the distribution of students based on how long they have remained inactive. It helps track inactivity trends over time.

<figure><img src="https://3709019308-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FKYwtHNGgdPXS3PWAlZUr%2Fuploads%2FoJLKg06ftkaniWTXPdtf%2FScreenshot%202025-05-12%20at%207.07.00%E2%80%AFPM.png?alt=media&#x26;token=49c3a144-09f0-4729-940a-da8cded281cb" alt=""><figcaption><p>Long Inactivity Table</p></figcaption></figure>

The Long Inactivity Table lists key details to help identify disengaged students. It includes the academic organization, course offering ID, and student names, along with the timestamp of their last activity. It also shows the number of events, assignments interacted with, and days since last activity.

NOTE: For production use, please make sure it's appropriate for users to have visibility into student names. If names are too sensitive, please drop this column from the visual before sharing to users

<figure><img src="https://3709019308-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FKYwtHNGgdPXS3PWAlZUr%2Fuploads%2FnIyzPnDEssmlmx5Yi1pZ%2FScreenshot%202025-05-12%20at%207.14.49%E2%80%AFPM.png?alt=media&#x26;token=57efaa97-c426-428b-96e8-34506b5ff536" alt=""><figcaption><p>Course Level Inactivity Summary</p></figcaption></figure>

The Course Level Inactivity Summary table shows long inactivity trends across courses. It includes the course IDs, course offering titles, instructor names, and total enrollments. It also highlights how many students have been inactive for 5 + days, 10+ days, and more than 10 days, helping spot courses with high inactivity.