Is there a way to average course completion time for a student cohort?

[L11ghtman]

I am aware that Canvas offers a limited analytics suite for courses. I am also aware that you can track the course time spent per student. We would like to average how long it takes our students to complete our courses, but as far as I can tell, there is no display of that average. I also don't think I can find a way to display individual student course time on any of the data exports Canvas can create. As far as I am aware, the only way to average student completion time in a course would be by hand--to go to the student tab, and then to type or write manually in Excel or another program the time each student spent in the course, and then manually average them. Our courses have hundreds of students and I was hoping there was a faster way to accomplish this, or if Canvas analytics might display average course time for the entire cohort somewhere.

[Pete5484]

Is your institution pulling Canvas Data 2? You can find total_activity_time in CD2 Enrollments.

e.g.

WITH enrollment_query as (
    SELECT c.course_code,
        c.name as course_name,
        c.id as course_id,
        e.course_section_id,
        cs.name AS course_section_name,
        u.id AS user_id,
        u.sortable_name,
        (
            (e.last_activity_at AT TIME ZONE 'UTC') AT TIME ZONE 'Australia/Sydney'
        ) AS last_activity_SYD,
        e.total_activity_time
    FROM canvas.scores sc
        JOIN canvas.enrollments e ON e.id = sc.enrollment_id
        JOIN canvas.course_sections cs ON cs.id = e.course_section_id
        JOIN canvas.courses c ON e.course_id = c.id
        JOIN canvas.users u ON u.id = e.user_id
    WHERE c.workflow_state <> 'deleted'
        AND e.type = 'StudentEnrollment'
        AND e.workflow_state NOT IN ('inactive', 'deleted')
        AND c.workflow_state = 'available'
        AND sc.course_score = TRUE -- use this just for overall course score
)
SELECT eq.course_code,
    eq.course_name,
    eq.course_id,
    eq.course_section_name,
    to_char(
        avg(eq.total_activity_time) * interval '1 second',
        'HH24:MI:SS'
    ) as avg_total_activity_time
FROM enrollment_query eq
GROUP BY ROLLUP(
        eq.course_code,
        eq.course_name,
        eq.course_id,
        eq.course_section_name
    )
ORDER BY eq.course_code,
    eq.course_name,
    eq.course_id,
    eq.course_section_name

The above is a little convoluted as I've just pulled it together from an existing snippet ... you don't need canvas.scores as the starting join point...  you could drop the seconds, could add count(*) AS n, stddev_pop(total_activity_time) and calc the 95%CI, drill further into the grade schema/final mark, etc. I've filtered inactive, deleted above, but they are also part of the course population and perhaps 'the story', etc. Anyway, it's a possible solution that doesn't involve APIs or manual entry etc.