/
Notifications trends and metrics

Notifications trends and metrics

Purpose

This document presents the metrics and trends from a large OpenedX instance. To fully understand this document, please go through the overview of the notifications feature here: https://openedx.atlassian.net/wiki/x/BIAmGwE

Data Sources:

  1. Notification events – triggered by user or system activity

  2. Notifications table – raw notification records

Here’s how this analysis informed improvements:

  1. Bug detection

    • Example 1: A sudden drop in read percentage led us to discover that unintended recipients were receiving new_discussion_post notifications.

    • Example 2: We noticed the combined count of response_endorsed and response_endorsed_on_thread notifications exceeded twice the number of responses endorsed—a clear anomaly. Investigation showed an error in how the recipient list was being generated.

  2. Optimizing defaults

    • Example: We observed high adoption of the ora_staff_notifications preference by course teams, so we set it as default ON.

  3. Feature enhancements

    • Example: Tray-open events revealed some instructors had exceptionally high unread counts due to large course enrollments and because they opted in new_discussion_post notifications. We introduced notification grouping, which improved CTR for new_discussion_post from 8% to 27% and overall CTR from 23% to 30%.

Images and code snippets in this document need to be expanded to be viewed.

Notification Events

Notification events and their triggers are as follows:

  1. edx.notifications.generated: A notification is created in the table.

  2. edx.notifications.read: A notification is clicked.

  3. edx.notifications.tray_opened: Tray is opened.

  4. edx.notifications.preferences.updated: A preference is updated (except cadence change).

  5. edx.notifications.app_all_read: Mark all read button in tray is clicked.

  6. edx.notifications.email_digest: When a notification email is sent to a user.

  7. edx.notifications.preferences.one_click_unsubscribe: One-click unsubscribe is used.

Attribute Type Description Values

content_url

String

URL where user should be directed to after clicking the notification.

Link to a post in discussion MFE: https://discussions.edx.org/course-v1:HarvardX+ECD01+2T2025/posts/687cc337e9c415049c3253c2 Link to ORA assignment in grading MFE: https://ora-grading.edx.org/block-v1:edX+EDT1+2T2024+type@openassessment+block@53260c150a8f44fea88c18cdc5ed4f52 Link to ORA assignment in learning MFE: https://courses.edx.org/courses/course-v1:HarvardX+GSE2x+2T2024/jump_to/block-v1:HarvardX+GSE2x+2T2024+type@openassessment+block@c87820cda3084ec0a72478ac53d203c8 Link to course updates page: https://courses.edx.org/courses/course-v1:MITx+6.431x+2T2025/course/updates
course_id String Course ID course-v1:HarvardX+ECD01+2T2025 notification_app String Platform area that the notification is relevant to. Possible values: Discussions, Grading, Updates notification_content String Notification text in HTML <p>Your response has been endorsed on the post <strong>Question about fusion</strong></p>

notification_type

String

Activity name that the notification is for

new_discussion_post new_question_post new_response new_comment new_comment_on_response response_on_followed_post comment_on_followed_post response_endorsed response_endorsed_on_thread content_reported new_instructor_all_learners_post ora_staff_notifications ora_grade_assigned course_updates
recipients_count Int Count of recipients of this notification recipients_id List List of user IDs of recipients, capped at 100 [47715382, 65279277, 61480460] recipients_truncated Bool TRUE when list of recipients>100 sender_id ID ID of user whose action triggered the notification 47715382

Platform Activities v.s. Notification Types

Notifications are automatically triggered via edX platform activities. Listed below is a mapping between these activities and the type of notifications they CAN trigger.

Platform area

Activity

Notification type

Platform area

Activity

Notification type

Forum

New response

new_response (when response is on my post)

response_on_followed_post (when response is on a post i’m following)

New comment

new_comment (when comment is on someone else’s response on my post)

new_comment_on_response (when comment is on my response)

comment_on_followed_post (when comment is on someone else’s response on a post i’m following)

New discussion post

new_discussion_post

New question post

new_question_post

New post with notify all learners option

new_instructor_all_learners_post

Post/response/comment reported

content_reported

Response endorsed

response_endorsed (when my response is endorsed)

response_endorsed_on_thread (when someone else’s response is endorsed on my post)

Course update

New course update

course_updates

Grading

New ORA submission requiring staff grading

ora_staff_notifications

Grade assigned to ORA submission

ora_grade_assigned

Notifications count

Distribution of all notification types is presented below. Note that these distributions are highly dependent on enrollment counts of courses.

image-20251112-095402.png
Distribution of notification types

Distribution of only discussions notifications is presented below.

Distribution of discussions notification types

Click-Through Rate (CTR)

Click-through rates (CTRs) are calculated only for tray notifications, not email. They help us monitor engagement, optimize defaults and plan improvements like grouping.

Unlike emails, users can interact with notifications only while they are on the platform. To ensure CTR reflects actual opportunity to click, we exclude any notifications created after the user’s most recent visit, since those were never visible to them.

Here’s is an example query used to calculate CTRs in the table below.

SELECT SUM(CASE WHEN NEW_COLUMN_CONDITION = 'true' THEN 1 ELSE 0 END) / SUM(CASE WHEN IS_CREATED_AFTER_LATEST_EVENT = 'true' THEN 0 ELSE 1 END) as CTR FROM ( SELECT CASE WHEN A.CREATED > MAX(B.TIMESTAMP) OR MAX(B.TIMESTAMP) IS NULL THEN 'true' ELSE 'false' END as IS_CREATED_AFTER_LATEST_EVENT, CASE WHEN A.LAST_READ IS NOT NULL THEN 'true' ELSE 'false' END as NEW_COLUMN_CONDITION FROM prod.lms.notifications_notification A LEFT JOIN prod.tracking_events.tracking_events_with_metadata B ON A.USER_ID = B.USER_ID AND B.TIMESTAMP > DATEADD(day, -60, CURRENT_DATE) WHERE A.CREATED > DATEADD(day, -60, CURRENT_DATE) AND A.CREATED < current_date() AND A.NOTIFICATION_TYPE='ora_grade_assigned' GROUP BY A.CREATED, A.NOTIFICATION_TYPE, A.USER_ID, A.LAST_READ, A.ID ) as SubQuery

Notification type

CTR (%)

last 60 days

Average recipient count per notification

Notification type

CTR (%)

last 60 days

Average recipient count per notification

1

new_response

36

1.00

2

response_on_followed_post

25

2.07

3

new_comment

30

1.00

4

new_comment_on_response

40

1.00

5

comment_on_followed_post

29

2.2

6

new_discussion_post

27

24.92

7

new_question_post

7

23.49

8

new_instructor_all_learners_post

15

931.44

9

content_reported

12

3.71

10

response_endorsed

52

1.00

11

response_endorsed_on_thread

43

1.00

12

course_updates

7

2909.15

13

ora_staff_notifications

13

8.98

14

ora_grade_assigned

24

1.00

15

Overall CTR excluding new_discussion_post, new_question_post, course_updates and new_instructor_all_learners_post

30

1.19

16

Overall seen rate excluding new_discussion_post, new_question_post, course_updates and new_instructor_all_learners_post

59

1.19

Key Trends and Insights

Presented below are some trends and metrics that help us detect any sudden changes in notifications performance.

Trend

Notes

Trend

Notes

1

Notifications clicked (read) and tray opens

Tray open count is almost 4 times the count of notifications clicked likely because users view and interact with notifications one after the other.

WITH TrayOpenCount AS ( SELECT DATE(timestamp) AS event_date, COUNT(*) AS tray_open_count FROM tracking_events.tracking_events_with_metadata WHERE event_name LIKE 'edx.notifications.tray_opened' -- AND event_data:unseen_notifications_count>0 AND timestamp > dateadd(day, -60, current_date()) AND timestamp < current_date() GROUP BY DATE(timestamp) ), ReadNotifications AS ( SELECT DATE(timestamp) AS event_date, COUNT(*) AS notifications_read FROM tracking_events.tracking_events_with_metadata WHERE event_name = 'edx.notifications.read' AND timestamp > dateadd(day, -60, current_date()) AND timestamp < current_date() GROUP BY DATE(timestamp) ) SELECT rn.event_date, COALESCE(rn.notifications_read, 0) AS notifications_read, COALESCE(toc.tray_open_count, 0) AS tray_open_count FROM ReadNotifications rn LEFT JOIN TrayOpenCount toc ON rn.event_date = toc.event_date ORDER BY rn.event_date;
2

Unseen count vs tray count

Count of tray opens when there are no unseen notifications is slightly higher than when there is 1 unseen notification.

SELECT event_data:unseen_notifications_count AS Unseen_count, COUNT(*) AS tray_open_count FROM tracking_events.tracking_events_with_metadata WHERE event_name LIKE 'edx.notifications.tray_opened' -- AND event_data:unseen_notifications_count>0 AND timestamp > dateadd(day, -10, current_date()) AND timestamp < current_date() GROUP BY Unseen_count ORDER BY Unseen_count DESC
3

Notifications read by type

SELECT DATE(timestamp) AS event_date, event_data:notification_type::String AS notification_type, COUNT(*) AS notifications_read FROM tracking_events.tracking_events_with_metadata WHERE event_name = 'edx.notifications.read' AND COURSERUN_KEY!='course-v1:edX+DemoX.1+2T2019' AND timestamp > dateadd(day, -60, current_date()) AND timestamp < current_date() GROUP BY DATE(timestamp), notification_type
4

Notification count from event

Count of notifications created from edx.notifications.generated event.

SELECT DATE(timestamp) AS event_date, event_data:notification_type::String AS notification_type, COUNT(*) AS notifications_read FROM tracking_events.tracking_events_with_metadata WHERE event_name = 'edx.notifications.generated' AND COURSERUN_KEY!='course-v1:edX+DemoX.1+2T2019' AND timestamp > dateadd(day, -60, current_date()) AND timestamp < current_date() GROUP BY DATE(timestamp), notification_type
5

Notifications created from DB

This graph may seem very different from the notification count from event because notifications for new discussion or question post, course updates and notify all learners emit only one event but can have a lot of recipients. And each recipient has their own row in the notifications DB.

SELECT DATE(CREATED) as NotificationDate, NOTIFICATION_TYPE, COUNT(*) as TotalCount FROM lms.notifications_notification WHERE CREATED > dateadd(day, -60, current_date()) AND CREATED < current_date() AND COURSE_ID != 'course-v1:edX+DemoX.1+2T2019' GROUP BY DATE(CREATED), NOTIFICATION_TYPE ORDER BY DATE(CREATED) DESC
6

Tray preference turned ON

SELECT DATE(TIMESTAMP), CASE WHEN EVENT_DATA:notification_type::String='core' THEN 'Discussion Activity' WHEN EVENT_DATA:notification_type::String='new_question_post' THEN 'New Question Post' WHEN EVENT_DATA:notification_type::String='new_discussion_post' THEN 'New Discussion Post' WHEN EVENT_DATA:notification_type::String='new_instructor_all_learners_post' THEN 'New posts from instructors' WHEN EVENT_DATA:notification_type::String='content_reported' THEN 'Content Reported' WHEN EVENT_DATA:notification_type::String='ora_staff_notifications' THEN 'New ORA submission for staff grading' WHEN EVENT_DATA:notification_type::String='ora_grade_assigned' THEN 'Essay assignment grade received' WHEN EVENT_DATA:notification_type::String='course_updates' THEN 'Course Updates' WHEN EVENT_DATA:notification_type::String='audit_access_expiring_soon' THEN 'Audit access' ELSE EVENT_DATA:notification_type::String END AS Preference, COUNT(*) FROM tracking_events.tracking_events_with_metadata WHERE event_name LIKE 'edx.notifications.preferences.updated' AND TIMESTAMP > DATEADD(day, -60, CURRENT_DATE()) AND TIMESTAMP < CURRENT_DATE() AND EVENT_DATA:notification_channel::String='web' AND EVENT_DATA:value=TRUE GROUP BY DATE(TIMESTAMP), Preference
7

Tray preference turned OFF

Any spikes or new entries in this trend should be investigated.

SELECT DATE(TIMESTAMP), CASE WHEN EVENT_DATA:notification_type::String='core' THEN 'Discussion Activity' WHEN EVENT_DATA:notification_type::String='new_question_post' THEN 'New Question Post' WHEN EVENT_DATA:notification_type::String='new_discussion_post' THEN 'New Discussion Post' WHEN EVENT_DATA:notification_type::String='new_instructor_all_learners_post' THEN 'New posts from instructors' WHEN EVENT_DATA:notification_type::String='content_reported' THEN 'Content Reported' WHEN EVENT_DATA:notification_type::String='ora_staff_notifications' THEN 'New ORA submission for staff grading' WHEN EVENT_DATA:notification_type::String='ora_grade_assigned' THEN 'Essay assignment grade received' WHEN EVENT_DATA:notification_type::String='course_updates' THEN 'Course Updates' WHEN EVENT_DATA:notification_type::String='audit_access_expiring_soon' THEN 'Audit access' ELSE EVENT_DATA:notification_type::String END AS Preference, COUNT(*) FROM tracking_events.tracking_events_with_metadata WHERE event_name LIKE 'edx.notifications.preferences.updated' AND TIMESTAMP > DATEADD(day, -60, CURRENT_DATE()) AND TIMESTAMP < CURRENT_DATE() AND EVENT_DATA:notification_channel::String='web' AND EVENT_DATA:value=FALSE GROUP BY DATE(TIMESTAMP), Preference
8

Read & seen %

Any dips in either of these should be investigated. We’ve caught several bugs, especially related to extra audience members using these two trends. Notifications with type content_reported, course_updates and new_instructor_all_learners_post are excluded because their read and seen rates are too low due to large recipient counts.

WITH TotalNotifications AS ( SELECT DATE(CREATED) as NotificationDate, COUNT(*) as TotalCount FROM lms.notifications_notification WHERE CREATED > DATEADD(day, -60, CURRENT_DATE()) AND CREATED < CURRENT_DATE() AND NOTIFICATION_TYPE NOT IN ('content_reported', 'course_updates', 'new_instructor_all_learners_post') AND COURSE_ID != 'course-v1:edX+DemoX.1+2T2019' GROUP BY DATE(CREATED) ), ReadNotifications AS ( SELECT DATE(CREATED) as NotificationDate, COUNT(*) as ReadCount FROM lms.notifications_notification WHERE CREATED > DATEADD(day, -60, CURRENT_DATE()) AND CREATED < CURRENT_DATE() AND LAST_READ IS NOT NULL AND NOTIFICATION_TYPE NOT IN ('content_reported', 'course_updates', 'new_instructor_all_learners_post') AND COURSE_ID != 'course-v1:edX+DemoX.1+2T2019' GROUP BY DATE(CREATED) ), SeenNotifications AS ( SELECT DATE(CREATED) as NotificationDate, COUNT(*) as SeenCount FROM lms.notifications_notification WHERE CREATED > DATEADD(day, -60, CURRENT_DATE()) AND CREATED < CURRENT_DATE() AND LAST_SEEN IS NOT NULL AND NOTIFICATION_TYPE NOT IN ('content_reported', 'course_updates', 'new_instructor_all_learners_post') AND COURSE_ID != 'course-v1:edX+DemoX.1+2T2019' GROUP BY DATE(CREATED) ) SELECT tn.NotificationDate, tn.TotalCount, rn.ReadCount, CASE WHEN tn.TotalCount = 0 THEN 0 ELSE rn.ReadCount::FLOAT / tn.TotalCount *100 END as ReadRatio, sn.SeenCount, CASE WHEN tn.TotalCount = 0 THEN 0 ELSE sn.SeenCount::FLOAT / tn.TotalCount *100 END as SeenRatio FROM TotalNotifications tn LEFT JOIN ReadNotifications rn ON tn.NotificationDate = rn.NotificationDate LEFT JOIN SeenNotifications sn ON tn.NotificationDate = sn.NotificationDate;

User Activity and Notification Volume Trends

These 10 trends compare three distinct counts related to notification activity and function as a sanity-check:

  1. Event count for the source activity: This refers to the number of times a specific activity occurs.

    1. Example: The event edx.forum.thread.created is emitted every time a new discussion thread is posted.

  2. Notification event count: This tracks how often the notification system generates a notification in response to those activities.

    1. Example: The event edx.notifications.generated is emitted whenever a notification is created. This event includes metadata such as the notification type and associated user IDs.

  3. Notification records in the database: This represents the actual number of user-level notification entries stored in the database.

    1. A single edx.notifications.generated event may result in millions of database entries — one per recipient. For instance, if a course update notification is intended for 500,000 users, the system emits just one event, but inserts 500,000 records into the notifications table (one per user).

Activity/ Trend

Notes

Activity/ Trend

Notes

1

Forum response

Notification type: User-level

  • Trend: All three counts (activity, notification event, DB) track closely.

  • Reason:

    • Notifications are default ON.

    • Each notification typically has 1 recipient — either the post author or a follower.

    • Following is opt-in, so follower count remains small.

SELECT COALESCE(a.event_date, b.event_date, c.event_date) AS date, a.count AS Notification_count_from_tracking_event, b.count AS Notification_count_from_table, c.count AS Response_count FROM (SELECT DATE(timestamp) AS event_date, COUNT(*) AS count FROM tracking_events.tracking_events_with_metadata WHERE event_name = 'edx.notifications.generated' AND event_data:notification_type IN ('new_response', 'response_on_followed_post') AND timestamp BETWEEN DATEADD(day, -59, CURRENT_DATE()) AND CURRENT_DATE() AND COURSERUN_KEY!='course-v1:edX+DemoX.1+2T2019' GROUP BY DATE(timestamp)) a FULL OUTER JOIN (SELECT DATE(created) AS event_date, COUNT(*) AS count FROM lms.notifications_notification WHERE created BETWEEN DATEADD(day, -59, CURRENT_DATE()) AND CURRENT_DATE() AND notification_type IN ('new_response', 'response_on_followed_post') AND COURSE_ID!='course-v1:edX+DemoX.1+2T2019' GROUP BY DATE(created)) b ON a.event_date = b.event_date FULL OUTER JOIN (SELECT DATE(timestamp) AS event_date, COUNT(*) AS count FROM tracking_events.tracking_events_with_metadata WHERE event_name = 'edx.forum.response.created' AND timestamp BETWEEN DATEADD(day, -59, CURRENT_DATE()) AND CURRENT_DATE() AND COURSERUN_KEY!='course-v1:edX+DemoX.1+2T2019' GROUP BY DATE(timestamp)) c ON COALESCE(a.event_date, b.event_date) = c.event_date ORDER BY date;
2

Forum comment

Notification type: User-level

  • Trend: Notification event and DB counts are slightly higher than comment events.

  • Reason:

    • Notifications are sent to two recipients: the post author and the response author (if different).

    • If users are following the post, they are also notified — further increasing the DB count.

SELECT COALESCE(a.event_date, b.event_date, c.event_date) AS date, a.count AS Notification_count_from_tracking_event, b.count AS Notification_count_from_table, c.count AS Comment_count FROM (SELECT DATE(timestamp) AS event_date, COUNT(*) AS count FROM tracking_events.tracking_events_with_metadata WHERE event_name = 'edx.notifications.generated' AND event_data:notification_type IN ('new_comment', 'new_comment_on_response', 'comment_on_followed_post') AND timestamp BETWEEN DATEADD(day, -59, CURRENT_DATE()) AND CURRENT_DATE() AND COURSERUN_KEY!='course-v1:edX+DemoX.1+2T2019' GROUP BY DATE(timestamp)) a FULL OUTER JOIN (SELECT DATE(created) AS event_date, COUNT(*) AS count FROM lms.notifications_notification WHERE created BETWEEN DATEADD(day, -59, CURRENT_DATE()) AND CURRENT_DATE() AND notification_type IN ('new_comment', 'new_comment_on_response', 'comment_on_followed_post') AND COURSE_ID!='course-v1:edX+DemoX.1+2T2019' GROUP BY DATE(created)) b ON a.event_date = b.event_date FULL OUTER JOIN (SELECT DATE(timestamp) AS event_date, COUNT(*) AS count FROM tracking_events.tracking_events_with_metadata WHERE event_name = 'edx.forum.comment.created' AND timestamp BETWEEN DATEADD(day, -59, CURRENT_DATE()) AND CURRENT_DATE() AND COURSERUN_KEY!='course-v1:edX+DemoX.1+2T2019' GROUP BY DATE(timestamp)) c ON COALESCE(a.event_date, b.event_date) = c.event_date ORDER BY date;
3

Forum question thread

Notification type: Course-level

  • Trend: Notification events and question thread counts align, but DB count is significantly higher.

  • Reason:

    • Notification is default OFF, but course teams may enable it.

    • High DB count suggests that many learners and staff have opted in.

SELECT COALESCE(a.event_date, b.event_date, c.event_date) AS date, a.count AS Notification_count_from_tracking_event, b.count AS Notification_count_from_table, c.count AS Question_thread_count FROM (SELECT DATE(timestamp) AS event_date, COUNT(*) AS count FROM tracking_events.tracking_events_with_metadata WHERE event_name = 'edx.notifications.generated' AND event_data:notification_type = 'new_question_post' AND timestamp BETWEEN DATEADD(day, -59, CURRENT_DATE()) AND CURRENT_DATE() AND COURSERUN_KEY!='course-v1:edX+DemoX.1+2T2019' GROUP BY DATE(timestamp)) a FULL OUTER JOIN (SELECT DATE(created) AS event_date, COUNT(*) AS count FROM lms.notifications_notification WHERE created BETWEEN DATEADD(day, -59, CURRENT_DATE()) AND CURRENT_DATE() AND notification_type = 'new_question_post' AND COURSE_ID!='course-v1:edX+DemoX.1+2T2019' GROUP BY DATE(created)) b ON a.event_date = b.event_date FULL OUTER JOIN (SELECT DATE(timestamp) AS event_date, COUNT(*) AS count FROM tracking_events.tracking_events_with_metadata WHERE event_name = 'edx.forum.thread.created' AND event_data:thread_type = 'question' AND timestamp BETWEEN DATEADD(day, -59, CURRENT_DATE()) AND CURRENT_DATE() AND COURSERUN_KEY!='course-v1:edX+DemoX.1+2T2019' GROUP BY DATE(timestamp)) c ON COALESCE(a.event_date, b.event_date) = c.event_date ORDER BY date;
4

Forum discussion thread

Notification type: Course-level

Grouping enabled

  • Trend: Notification event count closely matches the number of threads created.

  • Reason:

    • This is a coincidence, as the notification is default OFF.

    • The notification DB count is significantly lower due to a grouping mechanism:

      • When multiple discussion threads are created in a short span, older notifications are overwritten (grouped) rather than duplicated.

      • This causes earlier records to be updated instead of new entries being added.

    • The spike at the end of the trend graph reflects this behavior:

      • When a newer notification triggers grouping, the existing record’s modified timestamp is updated, making it appear recent.

SELECT COALESCE(a.event_date, b.event_date, c.event_date) AS date, a.count AS Notification_count_from_tracking_event, b.count AS Notification_count_from_table, c.count AS Discussion_thread_count FROM (SELECT DATE(timestamp) AS event_date, COUNT(*) AS count FROM tracking_events.tracking_events_with_metadata WHERE event_name = 'edx.notifications.generated' AND event_data:notification_type = 'new_discussion_post' AND COURSERUN_KEY!='course-v1:edX+DemoX.1+2T2019' AND timestamp BETWEEN DATEADD(day, -59, CURRENT_DATE()) AND CURRENT_DATE() GROUP BY DATE(timestamp)) a FULL OUTER JOIN (SELECT DATE(created) AS event_date, COUNT(*) AS count FROM lms.notifications_notification WHERE created BETWEEN DATEADD(day, -59, CURRENT_DATE()) AND CURRENT_DATE() AND notification_type IN ('new_discussion_post', 'new_discussion_posts') -- Name was changed a few months back AND COURSE_ID!='course-v1:edX+DemoX.1+2T2019' GROUP BY DATE(created)) b ON a.event_date = b.event_date FULL OUTER JOIN (SELECT DATE(timestamp) AS event_date, COUNT(*) AS count FROM tracking_events.tracking_events_with_metadata WHERE event_name = 'edx.forum.thread.created' AND event_data:thread_type = 'discussion' AND COURSERUN_KEY!='course-v1:edX+DemoX.1+2T2019' AND timestamp BETWEEN DATEADD(day, -59, CURRENT_DATE()) AND CURRENT_DATE() GROUP BY DATE(timestamp)) c ON COALESCE(a.event_date, b.event_date) = c.event_date ORDER BY date;
5

Forum content reported

Notification type: Course-level

  • Trend: Notification event count aligns with reported content events, but DB count is significantly higher.

  • Reason:

    • Similar to forum question thread: a single event generates multiple user-level notifications.

    • Likely due to broad staff/admin recipient lists or default ON settings.

SELECT COALESCE(a.event_date, b.event_date, c.event_date) AS date, a.count AS Notification_count_from_tracking_event, b.count AS Notification_count_from_table, c.count AS Reported_content_count FROM (SELECT DATE(timestamp) AS event_date, COUNT(*) AS count FROM tracking_events.tracking_events_with_metadata WHERE event_name = 'edx.notifications.generated' AND event_data:notification_type = 'content_reported' AND COURSERUN_KEY!='course-v1:edX+DemoX.1+2T2019' AND timestamp BETWEEN DATEADD(day, -59, CURRENT_DATE()) AND CURRENT_DATE() GROUP BY DATE(timestamp)) a FULL OUTER JOIN (SELECT DATE(created) AS event_date, COUNT(*) AS count FROM lms.notifications_notification WHERE created BETWEEN DATEADD(day, -59, CURRENT_DATE()) AND CURRENT_DATE() AND notification_type = 'content_reported' AND COURSE_ID!='course-v1:edX+DemoX.1+2T2019' GROUP BY DATE(created)) b ON a.event_date = b.event_date FULL OUTER JOIN (SELECT DATE(timestamp) AS event_date, COUNT(*) AS count FROM tracking_events.tracking_events_with_metadata WHERE event_name IN ('edx.forum.thread.reported', 'edx.forum.response.reported', 'edx.forum.comment.reported') AND COURSERUN_KEY!='course-v1:edX+DemoX.1+2T2019' AND timestamp BETWEEN DATEADD(day, -59, CURRENT_DATE()) AND CURRENT_DATE() GROUP BY DATE(timestamp)) c ON COALESCE(a.event_date, b.event_date) = c.event_date ORDER BY date;
6

Forum response endorsed

Notification type: User-level

  • Trend:

    • Notification events ≈ endorsements.

    • DB count is higher but within expected bounds.

  • Reason:

    • Notification is default ON.

    • Sent to both the post author and the response author.

    • DB count should not exceed 2× the number of endorsements. A bug was identified when this threshold was exceeded.

SELECT COALESCE(a.event_date, b.event_date, c.event_date) AS date, a.count AS Notification_count_from_tracking_event, b.count AS Notification_count_from_table, c.count AS Response_endorsed_count FROM (SELECT DATE(timestamp) AS event_date, COUNT(*) AS count FROM tracking_events.tracking_events_with_metadata WHERE event_name = 'edx.notifications.generated' AND event_data:notification_type IN ('response_endorsed', 'response_endorsed_on_thread') AND COURSERUN_KEY!='course-v1:edX+DemoX.1+2T2019' AND timestamp BETWEEN DATEADD(day, -59, CURRENT_DATE()) AND CURRENT_DATE() GROUP BY DATE(timestamp)) a FULL OUTER JOIN (SELECT DATE(created) AS event_date, COUNT(*) AS count FROM lms.notifications_notification WHERE created BETWEEN DATEADD(day, -59, CURRENT_DATE()) AND CURRENT_DATE() AND notification_type IN ('response_endorsed', 'response_endorsed_on_thread') AND COURSE_ID!='course-v1:edX+DemoX.1+2T2019' GROUP BY DATE(created)) b ON a.event_date = b.event_date FULL OUTER JOIN (SELECT DATE(timestamp) AS event_date, COUNT(*) AS count FROM tracking_events.tracking_events_with_metadata WHERE event_name = 'edx.forum.response.mark' AND COURSERUN_KEY!='course-v1:edX+DemoX.1+2T2019' AND timestamp BETWEEN DATEADD(day, -59, CURRENT_DATE()) AND CURRENT_DATE() GROUP BY DATE(timestamp)) c ON COALESCE(a.event_date, b.event_date) = c.event_date ORDER BY date;
7

Forum notify all learners thread

Notification type: Course-level

  • Trend:

    • Notification event count = threads with "notify all learners" enabled.

    • DB count is much higher.

  • Reason:

    • Notification is default ON for all learners.

    • Even if some opt out, at least one recipient triggers the event, and notifications are sent to all eligible learners.

SELECT COALESCE(a.event_date, b.event_date, c.event_date) AS date, a.count AS Notification_count_from_tracking_event, b.count AS Notification_count_from_table, c.count AS Notify_leaners_thread_count FROM (SELECT DATE(timestamp) AS event_date, COUNT(*) AS count FROM tracking_events.tracking_events_with_metadata WHERE event_name = 'edx.notifications.generated' AND event_data:notification_type = 'new_instructor_all_learners_post' AND COURSERUN_KEY!='course-v1:edX+DemoX.1+2T2019' AND timestamp BETWEEN DATEADD(day, -59, CURRENT_DATE()) AND CURRENT_DATE() GROUP BY DATE(timestamp)) a FULL OUTER JOIN (SELECT DATE(created) AS event_date, COUNT(*) AS count FROM lms.notifications_notification WHERE created BETWEEN DATEADD(day, -59, CURRENT_DATE()) AND CURRENT_DATE() AND notification_type = 'new_instructor_all_learners_post' AND COURSE_ID!='course-v1:edX+DemoX.1+2T2019' GROUP BY DATE(created)) b ON a.event_date = b.event_date FULL OUTER JOIN (SELECT DATE(timestamp) AS event_date, COUNT(*) AS count FROM tracking_events.tracking_events_with_metadata WHERE event_name='edx.forum.thread.created' AND EVENT_DATA:options:notify_all_learners=TRUE AND COURSERUN_KEY!='course-v1:edX+DemoX.1+2T2019' AND timestamp BETWEEN DATEADD(day, -59, CURRENT_DATE()) AND CURRENT_DATE() GROUP BY DATE(timestamp)) c ON COALESCE(a.event_date, b.event_date) = c.event_date ORDER BY date;
8

Course update

Notification type: Course-level

  • Trend: Matches pattern in #7.

  • Reason:

    • Notification is sent to all learners.

    • Notification is default ON, event fired once, DB entries per user.

SELECT COALESCE(a.event_date, b.event_date, c.event_date) AS date, a.count AS Notification_count_from_tracking_event, b.count AS Notification_count_from_table, c.count AS Course_updates_count FROM (SELECT DATE(timestamp) AS event_date, COUNT(*) AS count FROM tracking_events.tracking_events_with_metadata WHERE event_name = 'edx.notifications.generated' AND event_data:notification_type IN ('course_update', 'course_updates') AND COURSERUN_KEY!='course-v1:edX+DemoX.1+2T2019' AND timestamp BETWEEN DATEADD(day, -59, CURRENT_DATE()) AND CURRENT_DATE() GROUP BY DATE(timestamp)) a FULL OUTER JOIN (SELECT DATE(created) AS event_date, COUNT(*) AS count FROM lms.notifications_notification WHERE created BETWEEN DATEADD(day, -59, CURRENT_DATE()) AND CURRENT_DATE() AND notification_type IN ('course_update', 'course_updates') AND COURSE_ID!='course-v1:edX+DemoX.1+2T2019' GROUP BY DATE(created)) b ON a.event_date = b.event_date FULL OUTER JOIN (SELECT DATE(timestamp) AS event_date, COUNT(*) AS count FROM tracking_events.tracking_events_with_metadata WHERE event_name='edx.contentstore.course_update' AND COURSERUN_KEY!='course-v1:edX+DemoX.1+2T2019' AND timestamp BETWEEN DATEADD(day, -59, CURRENT_DATE()) AND CURRENT_DATE() GROUP BY DATE(timestamp)) c ON COALESCE(a.event_date, b.event_date) = c.event_date ORDER BY date;
9

ORA grade assigned

Notification type: User-level

  • Trend: Notification event and DB counts match. Slight mismatch with ORA submissions.

  • Reason:

    • Each grade generates one notification to one recipient.

    • ORA submissions may be higher due to opt-outs or lower when staff-graded assignments delay the notification.

SELECT COALESCE(a.event_date, b.event_date, c.event_date) AS date, a.count AS Notification_count_from_tracking_event, b.count AS Notification_count_from_table, c.count AS All_ORA_submission_count FROM (SELECT DATE(timestamp) AS event_date, COUNT(*) AS count FROM tracking_events.tracking_events_with_metadata WHERE event_name = 'edx.notifications.generated' AND event_data:notification_type = 'ora_grade_assigned' AND COURSERUN_KEY!='course-v1:edX+DemoX.1+2T2019' AND timestamp BETWEEN DATEADD(day, -59, CURRENT_DATE()) AND CURRENT_DATE() GROUP BY DATE(timestamp)) a FULL OUTER JOIN (SELECT DATE(created) AS event_date, COUNT(*) AS count FROM lms.notifications_notification WHERE created BETWEEN DATEADD(day, -59, CURRENT_DATE()) AND CURRENT_DATE() AND notification_type = 'ora_grade_assigned' AND COURSE_ID!='course-v1:edX+DemoX.1+2T2019' GROUP BY DATE(created)) b ON a.event_date = b.event_date FULL OUTER JOIN (SELECT DATE(timestamp) AS event_date, COUNT(*) AS count FROM tracking_events.tracking_events_with_metadata WHERE event_name='openassessmentblock.create_submission' AND COURSERUN_KEY!='course-v1:edX+DemoX.1+2T2019' AND timestamp BETWEEN DATEADD(day, -59, CURRENT_DATE()) AND CURRENT_DATE() GROUP BY DATE(timestamp)) c ON COALESCE(a.event_date, b.event_date) = c.event_date ORDER BY date;
10

ORA submission for staff grading

Notification type: Course-level

Grouping enabled

  • Trend:

    • Event and submission counts track closely.

    • DB count is significantly higher (up to 14×).

  • Reason:

    • Notification is default ON for all Staff and Admin roles.

    • Many courses have a large number of staff/admin users, leading to high recipient count per event.

SELECT COALESCE(a.event_date, b.event_date, c.event_date) AS date, a.count AS Notification_count_from_tracking_event, b.count AS Notification_count_from_table, c.count AS All_ORA_submission_count FROM (SELECT DATE(timestamp) AS event_date, COUNT(*) AS count FROM tracking_events.tracking_events_with_metadata WHERE event_name = 'edx.notifications.generated' AND event_data:notification_type IN ('ora_staff_notification', 'ora_staff_notifications') AND COURSERUN_KEY!='course-v1:edX+DemoX.1+2T2019' AND timestamp BETWEEN DATEADD(day, -59, CURRENT_DATE()) AND CURRENT_DATE() GROUP BY DATE(timestamp)) a FULL OUTER JOIN (SELECT DATE(created) AS event_date, COUNT(*) AS count FROM lms.notifications_notification WHERE created BETWEEN DATEADD(day, -59, CURRENT_DATE()) AND CURRENT_DATE() AND notification_type IN ('ora_staff_notification', 'ora_staff_notifications') AND COURSE_ID!='course-v1:edX+DemoX.1+2T2019' GROUP BY DATE(created)) b ON a.event_date = b.event_date FULL OUTER JOIN (SELECT DATE(timestamp) AS event_date, COUNT(*) AS count FROM tracking_events.tracking_events_with_metadata WHERE event_name='openassessmentblock.create_submission' AND COURSERUN_KEY!='course-v1:edX+DemoX.1+2T2019' AND timestamp BETWEEN DATEADD(day, -59, CURRENT_DATE()) AND CURRENT_DATE() GROUP BY DATE(timestamp)) c ON COALESCE(a.event_date, b.event_date) = c.event_date ORDER BY date;

Email notifications

Activity/ Trend

Notes

Activity/ Trend

Notes

1

Notification email types

  • Most emails sent are Daily summaries because that is the default for email is/turned ON.

SELECT DATE(TIMESTAMP), EVENT_DATA:cadence_type::String, COUNT(*) FROM tracking_events.tracking_events_with_metadata WHERE event_name ='edx.notifications.email_digest' AND TIMESTAMP>dateadd(day, -60, current_date()) AND TIMESTAMP < current_date() GROUP BY DATE(TIMESTAMP), EVENT_DATA:cadence_type::String ORDER BY DATE(TIMESTAMP), EVENT_DATA:cadence_type::String DESC
2

One-click unsubscribes

  • TRUE → The learner’s email preferences were successfully updated after clicking unsubscribe.

  • FALSE → The learner clicked the unsubscribe link again after all preferences had already been turned off. These additional clicks have no effect.

  • High counts at the start of the trend (followed by no activity in the middle) was traced to a bug in the unsubscribe workflow. The issue was identified through this trend and has since been fixed.

SELECT TIMESTAMP, EVENT_DATA:is_preference_updated, EVENT_DATA:username::String AS Username, EVENT_DATA:event_type::String AS Event_type FROM prod.tracking_events.tracking_events_with_metadata WHERE event_name='edx.notifications.preferences.one_click_unsubscribe' AND TIMESTAMP>dateadd(day, -60, current_date()) AND TIMESTAMP < current_date() ORDER BY USERNAME DESC, TIMESTAMP DESC
3

Email preferences turned OFF

SELECT DATE(TIMESTAMP), CASE WHEN EVENT_DATA:notification_type::String='core' THEN 'Discussion Activity' WHEN EVENT_DATA:notification_type::String='new_question_post' THEN 'New Question Post' WHEN EVENT_DATA:notification_type::String='new_discussion_post' THEN 'New Discussion Post' WHEN EVENT_DATA:notification_type::String='new_instructor_all_learners_post' THEN 'New posts from instructors' WHEN EVENT_DATA:notification_type::String='content_reported' THEN 'Content Reported' WHEN EVENT_DATA:notification_type::String='ora_staff_notifications' THEN 'New ORA submission for staff grading' WHEN EVENT_DATA:notification_type::String='ora_grade_assigned' THEN 'Essay assignment grade received' WHEN EVENT_DATA:notification_type::String='course_updates' THEN 'Course Updates' WHEN EVENT_DATA:notification_type::String='audit_access_expiring_soon' THEN 'Audit access' ELSE EVENT_DATA:notification_type::String END AS Preference, COUNT(*) FROM tracking_events.tracking_events_with_metadata WHERE event_name LIKE 'edx.notifications.preferences.updated' AND TIMESTAMP > DATEADD(day, -60, CURRENT_DATE()) AND TIMESTAMP < CURRENT_DATE() AND EVENT_DATA:notification_channel::String='email' AND EVENT_DATA:value=FALSE GROUP BY DATE(TIMESTAMP), Preference

 

4

Email preferences turned ON

SELECT DATE(TIMESTAMP), CASE WHEN EVENT_DATA:notification_type::String='core' THEN 'Discussion Activity' WHEN EVENT_DATA:notification_type::String='new_question_post' THEN 'New Question Post' WHEN EVENT_DATA:notification_type::String='new_discussion_post' THEN 'New Discussion Post' WHEN EVENT_DATA:notification_type::String='new_instructor_all_learners_post' THEN 'New posts from instructors' WHEN EVENT_DATA:notification_type::String='content_reported' THEN 'Content Reported' WHEN EVENT_DATA:notification_type::String='ora_staff_notifications' THEN 'New ORA submission for staff grading' WHEN EVENT_DATA:notification_type::String='ora_grade_assigned' THEN 'Essay assignment grade received' WHEN EVENT_DATA:notification_type::String='course_updates' THEN 'Course Updates' WHEN EVENT_DATA:notification_type::String='audit_access_expiring_soon' THEN 'Audit access' ELSE EVENT_DATA:notification_type::String END AS Preference, COUNT(*) FROM tracking_events.tracking_events_with_metadata WHERE event_name LIKE 'edx.notifications.preferences.updated' AND TIMESTAMP > DATEADD(day, -60, CURRENT_DATE()) AND TIMESTAMP < CURRENT_DATE() AND EVENT_DATA:notification_channel::String='email' AND EVENT_DATA:value<>FALSE GROUP BY DATE(TIMESTAMP), Preference
5

Email cadence preference changed

SELECT DATE(TIMESTAMP), CASE WHEN EVENT_DATA:notification_type::String='core' THEN 'Discussion Activity' WHEN EVENT_DATA:notification_type::String='new_question_post' THEN 'New Question Post' WHEN EVENT_DATA:notification_type::String='new_discussion_post' THEN 'New Discussion Post' WHEN EVENT_DATA:notification_type::String='new_instructor_all_learners_post' THEN 'New posts from instructors' WHEN EVENT_DATA:notification_type::String='content_reported' THEN 'Content Reported' WHEN EVENT_DATA:notification_type::String='ora_staff_notifications' THEN 'New ORA submission for staff grading' WHEN EVENT_DATA:notification_type::String='ora_grade_assigned' THEN 'Essay assignment grade received' WHEN EVENT_DATA:notification_type::String='course_updates' THEN 'Course Updates' WHEN EVENT_DATA:notification_type::String='audit_access_expiring_soon' THEN 'Audit access' ELSE EVENT_DATA:notification_type::String END AS Preference, COUNT(*) FROM tracking_events.tracking_events_with_metadata WHERE event_name LIKE 'edx.notifications.preferences.updated' AND TIMESTAMP > DATEADD(day, -60, CURRENT_DATE()) AND TIMESTAMP < CURRENT_DATE() AND EVENT_DATA:notification_channel::String='email' AND EVENT_DATA:value<>FALSE GROUP BY DATE(TIMESTAMP), Preference
SELECT DATE(TIMESTAMP), EVENT_DATA:value::String AS Cadence, COUNT(*) FROM tracking_events.tracking_events_with_metadata WHERE event_name LIKE 'edx.notifications.preferences.updated' AND TIMESTAMP > DATEADD(day, -60, CURRENT_DATE()) AND TIMESTAMP < CURRENT_DATE() AND EVENT_DATA:notification_channel::String='email_cadence' GROUP BY DATE(TIMESTAMP), Cadence