Time series analysis for enrollments.total_active_time and active user counts

[Pete5484]

Wondering if anyone has any suggestions regarding some "time series-like" analytics for enrollments total_activity_time and active user counts?

I'm conscious that CD2 is "eventual consistency" so time series analysis of things like weekly total_activity_time and overall active user count trends arent possible. There's no real way to time travel back to earlier dates. So I'm wondering if something like a SCD could work, maybe on a weekly 4am trigger (but even then enrollments will get huge) though active users wouldn't be a problem. 

I've been thinking of doing something like:

INSERT INTO canvas_analytics.enrollments_activity_time
(   timestamp_column,
    year_week,
    user_id,
    enrollment_id,
    course_id,
    course_section_id,
    enrollment_type,
    role_id,
    total_activity_time,
    incremental_activity_time,
    workflow_state
    )
SELECT 
    current_timestamp(),
    CONCAT(
        EXTRACT(YEAR FROM DATE_SUB(current_date(), 1)), 
        '-', 
        LPAD(EXTRACT(WEEK FROM DATE_SUB(current_date(), 1)), 2, '0')
    ) AS year_week,
    e.user_id,
    e.id AS enrollment_id,
    e.course_id,
    e.course_section_id,
    e.type,
    e.role_id,
    e.total_activity_time,
    -- e.total_activity_time AS incremental_activity_time, --first time run only
    e.total_activity_time - ua.max_total_activity_time AS incremental_activity_time, --subsequent runs
    e.workflow_state
FROM canvas.enrollments e
JOIN canvas.pseudonyms p ON e.user_id = p.user_id
LEFT JOIN (
    SELECT enrollment_id, MAX(total_activity_time) AS max_total_activity_time
    FROM canvas_analytics.user_activity
    GROUP BY enrollment_id
) ua ON e.id = ua.enrollment_id
WHERE e.completed_at is NULL    
    AND p.last_request_at >= date_sub(current_date(), 7)
    AND e.type NOT IN ('StudentViewEnrollment', 'DesignerEnrollment','ObserverEnrollment')
    AND e.total_activity_time > ua.max_total_activity_time --remove for first time run only

  and

INSERT INTO canvas_analytics.user_counts
(timestamp_column, year_week, active_user_count, created_user_count, updated_user_count, deleted_user_count, suspended_user_count)

SELECT 
    current_timestamp(),
    CONCAT(
        EXTRACT(YEAR FROM DATE_SUB(current_date(), 1)), 
        '-', 
        LPAD(EXTRACT(WEEK FROM DATE_SUB(current_date(), 1)), 2, '0')
    ) AS year_week,
    COUNT(DISTINCT p.user_id),
    COUNT(DISTINCT CASE 
                        WHEN YEAR(p.created_at) = YEAR(DATE_SUB(current_date(), 1))
                        THEN p.user_id
                        ELSE NULL
                    END),
    COUNT(DISTINCT CASE 
                        WHEN YEAR(p.updated_at) = YEAR(DATE_SUB(current_date(), 1)) THEN p.user_id
                        ELSE NULL
                    END),
    COUNT(DISTINCT CASE 
                        WHEN YEAR(p.deleted_at) = YEAR(DATE_SUB(current_date(), 1)) THEN p.user_id
                        ELSE NULL
                    END),
    COUNT(DISTINCT CASE 
                    WHEN p.workflow_state IN ('suspended') AND YEAR(p.updated_at)= YEAR(DATE_SUB(current_date(), 1)) THEN p.user_id
                    ELSE NULL
                END)
FROM canvas.users u 
LEFT JOIN canvas.pseudonyms p ON p.user_id = u.id
WHERE YEAR(last_request_at) = YEAR(DATE_SUB(current_date(), 1))

[venkat5121]

Hi @Pete5484 

I don't see any issue in what your SQL does.

I am wondering if you've considered a Snapshot approach on storing the data on weekly basis:

Week1	User1	Course1	total_activity_time1
Week2	User1	Course1	total_activity_time2
.	.	.	.
WeekN	User1	Course1	total_activity_timeN

and let the visualization tool calculate the incremental time analysis?

The only difference between the two approaches is:

In former model, if a student will cease to exist as soon as there is no change in total_activity_time and requirements such as who are the students that do not have any activity time in certain time ranges might be difficult to achieve.

So it all narrows down to your requirement.

I hope this helps!