User Emails (or Student IDs ) CD2

Hi,

Does anyone know a way to find student IDs or user emails from CD2?

I have checked users and accounts tables in the canvas namespace - but they don't have "email" column. I only have the primary key of userID and accountID and only their full name.

Is there some other place that I should be looking?

Thanks

Find more posts tagged with

Canvas Data 2

Canvas Data CLI

Accepted answers

All comments

aasmund_kvamme

I'd also love to find this. I keep a "local" table that connects UserID and StudentId/email ☹️

robotcars

SIS ID for users is in pseudonyms table, and emails are in the communication_channels table.

https://data-access-platform-api.s3.eu-central-1.amazonaws.com/schema.json

Pete5484

Keep in mind that a user can have more than one pseudonym and more than one communication channel. For instance:

SELECT
u.sortable_name,
p.user_id, 
p.unique_id, 
p.sis_user_id, 
p.authentication_provider_id,
cc.path_type, -- e.g. 'email', 'SMS' etc).
cc.path, -- e.g person@example.com

FROM
canvas.users u
LEFT JOIN canvas.pseudonyms p ON p.user_id = u.id
LEFT JOIN canvas.communication_channels cc ON cc.pseudonym_id = p.id;

You can use DISTINCT ON to select only one email per user/pseudonym

ie SELECT DISTINCT ON (p.user_id) ... WHERE cc.path_type = 'email' ORDER BY p.user_id, cc.path;

OR use a CTE with LIMIT 1. Ideally you would also filter using workflow state.

ZaidGandhi

@KeithSmith_au @Pete5484 @stimme

Thanks guys, this is super helpful and quite crisp explanation - covers the use cases. Yes I can confirm that my org uses emails as logins, hence I used pseudonym table.

Correctly pointed as it was a one to many join on users - since there were not many additional emails (for users - duplicates), I removed them manually for now, but your queries can come in handy when my dataset would increase eventually!

Just to mention - I am doing this to find non-engaged students from canvas_logs.web_logs.

Have you guys done any analytics work regarding student engagement with DAP?

Happy to have a chat - if possible.

Cheers,

jwals

We typically use last_activity_at and total_activity_time off of the enrollments table rather web_logs for student engagement questions. Instructure usually disclaims that web_logs is not guaranteed to be 100% accurate, shouldn't be used for individual-level auditing or academic integrity questions, etc. If you do use the enrollments data, a few things to think about:

1) You'll be dealing with course/section-level data rather than an absolute "last time this user shows up in web_logs" so you'll have to think about how to aggregate across courses. E.g., a student may have a last_activity_at of weeks ago in one course, but activity yesterday in another course. Does that mean they are non-engaged?

2) One way of trying to answer that is by averaging those measures across a course. Then you can see, for example, well every student in this class has a last_activity_at of several weeks ago so maybe the course just doesn't require much Canvas use. (Quite possible at our institution, may of course be different at yours.)

3) Combining last_activity_at and total_activity_time with other data points like missing assignments or submissions can give a fuller sense of (non-)engagement as well. But, the same caveat applies that you probably want to normalize those values across a course rather than just looking at absolutes. It's not out of the ordinary for one of our instructors, for example, to be using Canvas in a way where every student has multiple missing submissions but that is actually fine for their engagement and grade.

Pete5484

@jwals @ZaidGandhi I'm also using the enrollments table for similar purposes.

I'm trialling an analytics table loosely based on scd; so each day I'm grabbing changes in enrolments total_activity_time (ie incremental activity in a course by a user) and storing it in a way that I can aggregate/analyse at a more granular level.

Great points from @jwals about interpreting the enrolment table.

Not even sure if it's useful or works as intended, yet, but basically I execute the following insert each day in databricks immediately after the cd2 incremental update completes. Because the table could get huge, there's a monthly aggregation script with a clean up of the daily data (could do more or less frequently, or partition eg USING DELTA PARTITIONED BY (year_month)). It's still an experiment at the moment.

CREATE TABLE IF NOT EXISTS canvas_analytics.user_activity_daily (etc...)

INSERT INTO canvas_analytics.user_activity_daily (
    timestamp_column,
    year_day,
    year_week,
    year_month,
    user_id,
    enrollment_id,
    course_id,
    course_section_id,
    enrollment_type,
    role_id,
    total_activity_time,
    incremental_activity_time,
    workflow_state,
    e_created_at,
    e_updated_at,
    e_start_at,
    e_end_at,
    e_completed_at
)
SELECT 
    current_timestamp() AS timestamp_column,
    CONCAT(
        EXTRACT(YEAR FROM current_date()), 
        '-', 
        LPAD(EXTRACT(DOY FROM current_date()), 3, '0')
    ) AS year_day,
    CONCAT(
        EXTRACT(YEAR FROM current_date()), 
        '-', 
        LPAD(EXTRACT(WEEK FROM current_date()), 2, '0')
    ) AS year_week,
    CONCAT(
        EXTRACT(YEAR FROM current_date()), 
        '-', 
        LPAD(EXTRACT(MONTH FROM current_date()), 2, '0')
    ) AS year_month,
    e.user_id,
    e.id AS enrollment_id,
    e.course_id,
    e.course_section_id,
    e.type AS enrollment_type,
    e.role_id,
    COALESCE(e.total_activity_time, 0) AS total_activity_time,
    CASE 
        WHEN ua.max_total_activity_time IS NULL THEN COALESCE(e.total_activity_time, 0)
        ELSE COALESCE(e.total_activity_time, 0) - ua.max_total_activity_time
    END AS incremental_activity_time,
    e.workflow_state,
    e.created_at AS e_created_at,
    e.updated_at AS e_updated_at,
    e.start_at AS e_start_at,
    e.end_at AS e_end_at,
    e.completed_at AS e_completed_at
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_daily
    GROUP BY enrollment_id
) ua ON e.id = ua.enrollment_id
WHERE e.workflow_state = 'active'
  AND (ua.max_total_activity_time IS NULL OR COALESCE(e.total_activity_time, 0) > ua.max_total_activity_time)
  AND e.type NOT IN ('StudentViewEnrollment', 'DesignerEnrollment', 'ObserverEnrollment')

Unanswered Questions

EDUCAUSE Insights: Data and Decision-Making
EDUCAUSE is full of inspiration, whether you’re onsite or following from afar. Join the evolution by sharing what sparks your curiosity, the questions you have, and the insights you discover. We'll learn from each other, expand our networks, and strengthen our Community. Your perspective matters, and the more we share,…
Problems with GraphQL in User Pageviews reports
Prompted by the most recent Canvas release notes, we looked into the Page Views feature, and we were happy with the new documented changes. However, we notice that more user actions are done by GraphQL API, which does not provide details in the page view report, either in the GUI or in the CSV file download. We and most…
Metrics Easy Button
I am currently teaching five courses this semester with more than 70 students. To track their progress, I currently have to review each course and section individually, which is very time-consuming. Ideally, I would like to consolidate key information for all students into a single Excel file—showing class attendance…
Analytics API / Metrics calculation logic
Hello, We are currently using the Analytics API provided in the Instructure Developer Documentation Portal to retrieve certain metrics-related data. We would like to know if it’s possible to replicate these datasets using the Canvas Data Platform (CDP), instead of relying on API calls. For example, could you please advise…
CD2: Enhanced Rubrics
Is there a way to tell via CD2 if a course is using Enhanced Rubrics? I didn't see it in the entity diagram for Rubrics table, but I might have missed something... Thanks Ian