cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Community Member

How Do I Determine Time Spent on Site

Hello, I am currently working on a Canvas Data project gathering student measures, and one of the measures I need is "Time Spent on Website". As far as we could determine, this data isn't actually available, and it looks like the idea for keeping that data was rejected based on this post from 2015:

https://community.canvaslms.com/ideas/1193-student-activity-report-student-time-log 

Currently, we are using the "requests" data and determining an approximate "time on site" by looking at the time between requests. This is definitely a sub-optimal solution, since the "requests" data in inherently unstable. Has anybody else come up with a better / more reliable solution? Thanks!

6 Replies
Highlighted
Community Coach
Community Coach

Hello joshe Welcome to the Canvas Community!  Thanks for posting your question here!  I don't really have an answer for you.  However, I wanted to let you know that I am going to share your question with the https://community.canvaslms.com/groups/big-data group here in the Community in hopes that your question will gain some additional exposure.  If you are not yet following this group, I would encourage you to click on the link that I have provided and then click on the "Follow" button at the top right corner of the page.  You may also need to select the "Actions" menu and then choose "Join group" (also located in the upper right corner).  I hope this will be of help to you, Joshua!  Good luck, and Happy New Year!

0 Kudos
Highlighted
Community Team
Community Team

joshe‌, I don't have an answer for you either, but wanted to clarify that the idea to which you linked, https://community.canvaslms.com/ideas/1193-student-activity-report-student-time-log , is currently in Product Radar status and still open for voting and feedback.

Highlighted
Community Advocate
Community Advocate

I've been trying to come up with a better way to handle this too. Have you tried reducing the requests that are not active clicks, and are the app staying alive? Such as 'ping' and 'activity stream'

I also found that by grouping by session and remote ip I can reduce idle time in my counts.

We use something similar to below in a Course LTI we build... It may not be perfect but it does allow the teacher to get an idea about the time spent in the course by each student. The following collects all students in a course, while the one we use lets the teacher select a student and see how much time the student spent on a given day as a stacked bar chart, where the stacks are the individual sessions/time spent.

DECLARE @course_id VARCHAR(16)
SET @course_id = 100000001234567

SELECT
     user_id,
     user_dim.name,
     SUM(time_spent_m) AS total_minutes,
     CAST(CAST((SUM(time_spent_m)) AS int) / 60 AS varchar) + ':'  + right('0' + CAST(CAST((SUM(time_spent_m)) AS int) % 60 AS varchar(2)),2) AS hours_min
FROM (
          SELECT
               session_id,
               r.user_id,
               remote_ip,
               timestamp_day,
               DATEDIFF(minute, MIN([timestamp]), MAX([timestamp])) time_spent_m
          FROM
               CanvasLMS.dbo.requests r
               INNER JOIN CanvasLMS.dbo.course_section_dim csd ON r.course_id = csd.course_id
               INNER JOIN CanvasLMS.dbo.enrollment_dim ed ON ed.course_section_id = csd.id AND r.user_id = ed.user_id
          WHERE r.course_id = @course_id
               AND ed.type = 'StudentEnrollment'
               AND r.web_application_action NOT IN ('ping', 'activity_stream_summary')
          GROUP BY
               session_id,
               r.user_id,
               remote_ip,
               timestamp_day
     ) x
     INNER JOIN CanvasLMS.dbo.user_dim ON x.user_id = user_dim.id
GROUP BY
     user_id,
     user_dim.name
 
Highlighted

Dear Robert,

I am an online tutor and use Canvas. Can you spell out what kind of access I would need to run this and get this extract?

I currently extract(manually) and manipulate data to in the end produce this type of analysis:

Histogram of the Number of Contributions per student

This means that there was 12 students that have never participated and 5 students that participated 9 or more times. I then ask students to see for themselves where they fit.

Histogram of the Number of Views per student

It means that 9 students did 99 or less views while 7 students did more than 300 views.

I does make you think doesn't it? Imagine instead of the proxy's of views and participations one can use time on the course. 

I used the same data to show that the most popular day is the one that I use for the Webinar! I deliberately moved my webinar to Monday to get them into the material earlier in the week.

My dream is to apply predictive analytics through behaviour Charts to help and encourage students. 

Yes you are right I am a passionate data scientist that would value any support or connections to make this work.

Kind Regards

Francois 

0 Kudos
Highlighted

Francois,

The previously posted query uses Canvas Data Portal to compile the requests table logs into course user grouping. Canvas Data is available for institutions using enterprise Canvas? How do I use the Canvas Data Portal for an account? 

There are probably ways of compiling an attendance report for your Canvas course using the API. I have not tried.

To keep the conversation going I'm sharing the following:

The first query, organizes 'attendance' by when students browse the course.

The second query, compiles those counts into Days of The Week. Some of our teachers here use this to schedule their Live Sessions when students are most likely to be available. I also have some that show what time of day students are active.

DECLARE @course_id VARCHAR(16)
SET @course_id = 100000001124567


SELECT timestamp_day, count(user_id) cnt FROM (
     SELECT
          timestamp_day,
          r.user_id
     FROM CanvasLMS.dbo.requests r
          INNER JOIN CanvasLMS.dbo.course_section_dim csd ON r.course_id = csd.course_id
          INNER JOIN CanvasLMS.dbo.enrollment_dim ed ON ed.course_section_id = csd.id AND r.user_id = ed.user_id
     WHERE r.course_id = @course_id
          AND ed.type = 'StudentEnrollment'
          AND r.web_application_action NOT IN ('ping', 'activity_stream_summary')
     GROUP BY r.timestamp_day, r.user_id
    ) a
GROUP BY timestamp_day
ORDER BY timestamp_day;
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

DateAttendance
2018-03-01, Thursday35
2018-03-02, Friday30
2018-03-03, Saturday15
2018-03-04, Sunday22
2018-03-05, Monday34
2018-03-06, Tuesday30
2018-03-07, Wednesday
34
2018-03-08, Thursday34
2018-03-09, Friday31
2018-03-10, Saturday12
2018-03-11, Sunday20
2018-03-12, Monday35
2018-03-13, Tuesday36
2018-03-14, Wednesday39


SELECT
     day_of_week,
     count(user_id) cnt FROM (
     SELECT
          DATENAME(weekday, timestamp_day) day_of_week,
          r.user_id
     FROM CanvasLMS.dbo.requests r
          INNER JOIN CanvasLMS.dbo.course_section_dim csd ON r.course_id = csd.course_id
          INNER JOIN CanvasLMS.dbo.enrollment_dim ed ON ed.course_section_id = csd.id AND r.user_id = ed.user_id
     WHERE r.course_id = @course_id
          AND ed.type = 'StudentEnrollment'
          AND r.web_application_action NOT IN ('ping', 'activity_stream_summary')
     GROUP BY r.timestamp_day, r.user_id
    ) a
GROUP BY day_of_week
ORDER BY day_of_week;‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

WeekdayAttendance
Sunday119
Monday319
Tuesday383
Wednesday286
Thursday361
Friday276
Saturday86
Highlighted
Community Coach
Community Coach

Hello once again, joshe...

I have been reviewing older questions here in the Canvas Community, and I wanted to check in with you because I noticed that there hasn't been any new activity in this topic for quite some time.  Also, we've not heard back from you since you first posted your question on January 2, 2018.  It looks like you've received a bit of help from various members of the Community.  Have you had an opportunity to review the feedback that you've received thus far?  Did any of the above replies help to answer your question?  Or, are you still looking for some assistance with this question?  If you feel that one of the above replies has helped to answer your question, please go ahead and mark that person's answer as "Correct".  However, if you are still looking for some help from Community members, please let us know by posting a message below so that someone might be able to assist you.  For now, I am going to mark your question as "Assumed Answered" because we've not heard back from you and because there hasn't been any new activity in this topic for over five months.  However, that won't prevent you or others from posting additional questions and/or comments below that are relate to this question.  I hope that's alright with you, Joshua.  Looking forward to hearing back from you soon.

Labels