This is an SQL version of the Dashboard Report for Moderator Activity. This report provides a comprehensive overview of the activities performed by moderators within a specified time frame on a Discourse site. The report combines multiple aspects of moderator activity: time spent on the platform, number of flags reviewed, posts created, personal messages (PMs) created, topics created, and post revisions made. This dashboard report is a valuable tool for administrators looking to measure the effectiveness and engagement of their moderation team, providing a detailed look at their activity and contributions. The insights provided by the report can inform decisions on moderator training, recognition, and recruitment, and ensuring the moderation team is well-balanced and effective in maintaining community standards. --[params] -- date :start_date = 2023-12-01 -- date :end_date = 2024-12-01 -- boolean :include_admins = false WITH mods AS ( SELECT id AS user_id, username_lower AS username, uploaded_avatar_id FROM users u WHERE ((u.moderator = true) OR (:include_admins AND u.admin = true)) AND u.id > 0 ), time_read AS ( SELECT SUM(uv.time_read) AS time_read, uv.user_id FROM mods m JOIN user_visits uv ON m.user_id = uv.user_id WHERE uv.visited_at >= :start_date AND uv.visited_at = :start_date AND created_at <= :end_date ), agreed_flags AS ( SELECT pa.agreed_by_id AS user_id, COUNT(*) AS flag_count FROM mods m JOIN period_actions pa ON pa.agreed_by_id = m.user_id GROUP BY agreed_by_id ), disagreed_flags AS ( SELECT pa.disagreed_by_id AS user_id, COUNT(*) AS flag_count FROM mods m JOIN period_actions pa ON pa.disagreed_by_id = m.user_id GROUP BY disagreed_by_id ) SELECT COALESCE(af.user_id, df.user_id) AS user_id, COALESCE(af.flag_count, 0) + COALESCE(df.flag_count, 0) AS flag_count FROM agreed_flags af FULL OUTER JOIN disagreed_flags df ON df.user_id = af.user_id ), revision_count AS ( SELECT pr.user_id, COUNT(*) AS revision_count FROM mods m JOIN post_revisions pr ON pr.user_id = m.user_id JOIN posts p ON p.id = pr.post_id WHERE pr.created_at >= :start_date AND pr.created_at pr.user_id GROUP BY pr.user_id ), topic_count AS ( SELECT t.user_id, COUNT(*) AS topic_count FROM mods m JOIN topics t ON t.user_id = m.user_id WHERE t.archetype = 'regular' AND t.created_at >= :start_date AND t.created_at = :start_date AND p.created_at = :start_date AND p.created_at <= :end_date GROUP BY p.user_id ) SELECT m.user_id, m.username, fc.flag_count as flags_reviewed, ROUND(((tr.time_read) / 3600.00),2) as time_reading_hours, tc.topic_count as topics_created, pmc.pm_count as PMs_created, pc.post_count as posts_created, rc.revision_count as revisions FROM mods m LEFT JOIN time_read tr ON tr.user_id = m.user_id LEFT JOIN flag_count fc ON fc.user_id = m.user_id LEFT JOIN revision_count rc ON rc.user_id = m.user_id LEFT JOIN topic_count tc ON tc.user_id = m.user_id LEFT JOIN post_count pc ON pc.user_id = m.user_id LEFT JOIN pm_count pmc ON pmc.user_id = m.user_id ORDER BY m.username ASC Parameters :start_date and :end_date - These parameters define the date range for the report. Both date parameters accept the date format of YYYY-MM-DD. :include_admins - This parameter determines whether to include administrators in the report alongside moderators. SQL Query Explanation The report is structured using common table expressions (CTEs) to segment the data processing into manageable and logical sections. Here’s what happens in each CTE: mods: Identifies all users with moderator status or admin status (if included by the :include_admins parameter). It selects only relevant user columns for further queries. time_read: Calculates the total time (in seconds) each moderator has spent reading content on the platform between the provided start and end dates. flag_count: Counts the number of flags that moderators have agreed with or disagreed with during the specified period. It takes into account multiple flag types represented by their respective post action type IDs. revision_count: Counts the number of post revisions made by moderators on other users’ posts within the given timeframe. topic_count: Counts the number of regular topics created by moderators. post_count: Counts the number of posts created by moderators in regular topics. pm_count: Counts the number of private messages initiated by moderators. After collecting data in the CTEs, the main query joins them based on the user ID and compiles the final report displaying each moderator’s username, the total time spent reading (converted to hours), the number of flags reviewed, topics created, personal messages created, posts created, and revisions made. The results are ordered alphabetically by the moderator’s username. Example Results user username flags_reviewed time_reading_hours topics_created pms_created posts_created revisions 1 moderator1 NULL 36.11 NULL 344 8 15 2 moderator2 46 104.52 2 271 466 363 3 moderator3 NULL 72.15 NULL 418 64 16

Hi @SaraDev, This is really useful. Would it be possible to add '‘Posts approved’ and ‘Posts rejected’ columns to the Moderator Activity report? Thanks, T_Disco

Hi @T_Disco, [image] T: Would it be possible to add '‘Posts approved’ and ‘Posts rejected’ columns to the Moderator Activity report? Yes, to add Posts approved and Posts rejected results to the Moderator Activity report, we can utilize the reviewables and reviewable_scores tables. Specifically, the status field in reviewable_scores can be used to determine whether a post was approved (status = 1) or rejected (status = 2). Here’s what this would look like added to the report: Moderator Activity with Posts Approved and Posts Rejected -- [params] -- date :start_date = 2023-12-01 -- date :end_date = 2024-12-01 -- boolean :include_admins = false WITH mods AS ( SELECT id AS user_id, username_lower AS username, uploaded_avatar_id FROM users u WHERE ((u.moderator = true) OR (:include_admins AND u.admin = true)) AND u.id > 0 ), time_read AS ( SELECT SUM(uv.time_read) AS time_read, uv.user_id FROM mods m JOIN user_visits uv ON m.user_id = uv.user_id WHERE uv.visited_at >= :start_date AND uv.visited_at = :start_date AND created_at <= :end_date ), agreed_flags AS ( SELECT pa.agreed_by_id AS user_id, COUNT(*) AS flag_count FROM mods m JOIN period_actions pa ON pa.agreed_by_id = m.user_id GROUP BY agreed_by_id ), disagreed_flags AS ( SELECT pa.disagreed_by_id AS user_id, COUNT(*) AS flag_count FROM mods m JOIN period_actions pa ON pa.disagreed_by_id = m.user_id GROUP BY disagreed_by_id ) SELECT COALESCE(af.user_id, df.user_id) AS user_id, COALESCE(af.flag_count, 0) + COALESCE(df.flag_count, 0) AS flag_count FROM agreed_flags af FULL OUTER JOIN disagreed_flags df ON df.user_id = af.user_id ), revision_count AS ( SELECT pr.user_id, COUNT(*) AS revision_count FROM mods m JOIN post_revisions pr ON pr.user_id = m.user_id JOIN posts p ON p.id = pr.post_id WHERE pr.created_at >= :start_date AND pr.created_at pr.user_id GROUP BY pr.user_id ), topic_count AS ( SELECT t.user_id, COUNT(*) AS topic_count FROM mods m JOIN topics t ON t.user_id = m.user_id WHERE t.archetype = 'regular' AND t.created_at >= :start_date AND t.created_at = :start_date AND p.created_at = :start_date AND p.created_at = :start_date AND rs.reviewed_at <= :end_date GROUP BY rs.reviewed_by_id ) SELECT m.user_id, m.username, fc.flag_count as flags_reviewed, ROUND(((tr.time_read) / 3600.00),2) as time_reading_hours, tc.topic_count as topics_created, pmc.pm_count as PMs_created, pc.post_count as posts_created, rc.revision_count as revisions, ra.posts_approved, ra.posts_rejected FROM mods m LEFT JOIN time_read tr ON tr.user_id = m.user_id LEFT JOIN flag_count fc ON fc.user_id = m.user_id LEFT JOIN revision_count rc ON rc.user_id = m.user_id LEFT JOIN topic_count tc ON tc.user_id = m.user_id LEFT JOIN post_count pc ON pc.user_id = m.user_id LEFT JOIN pm_count pmc ON pmc.user_id = m.user_id LEFT JOIN reviewable_actions ra ON ra.user_id = m.user_id ORDER BY m.username ASC Where the results for this report would look like: user username flags_reviewed time_reading_hours topics_created pms_created posts_created revisions posts_approved posts_rejected 1 moderator1 NULL 36.11 NULL 344 8 15 10 5 2 moderator2 46 104.52 2 271 466 363 7 3 3 moderator3 NULL 72.15 NULL 418 64 16 NULL NULL

That’s brilliant, thank you @SaraDev! Will those columns be added to the Moderator Activity report on the /admin/dashboard/moderation tab at some point? Thanks again :slight_smile: T_Disco

@SaraDev Is a query possible for number of topic merges carried out in a given time period

[image] T: Will those columns be added to the Moderator Activity report on the /admin/dashboard/moderation tab at some point? We don’t currently have any plans to add those columns to the dashboard version of the report, but I can bring this up with our team to see if we can make some improvements in the future. :slightly_smiling_face: [image] srinivas chilukuri: Is a query possible for number of topic merges carried out in a given time period Yes, to create a query that shows when posts were moved to other topics, you can filter the posts table for entries where the action_code indicates a move with a split_topic entry. For example: Individual Post Move Actions SELECT id AS post_id, user_id, topic_id, post_number, created_at::date, updated_at::date, action_code FROM posts WHERE action_code = 'split_topic' ORDER BY created_at DESC Would show results like: post user topic post_number created_at updated_at action_code A post was merged into an existing topic:: [Merged Topic Title] USERNAME Original_Topic_Title 3 2024-10-30 2024-10-30 split_topic 2 posts were merged into an existing topic:: [Merged Topic Title] USERNAME Original_Topic_Title 5 2024-10-30 2024-10-30 split_topic A post was split to a new topic: [Split Topic Title] USERNAME Original_Topic_Title 2 2024-10-30 2024-10-30 split_topic … … … … … … … If you wanted to show a total count of post moves for each user on a site, and add a couple parameters to filter the move actions by date, you could also use a query like the following: Number of Post Move Actions per User -- [params] -- date :start_date -- date :end_date SELECT user_id, COUNT(*) AS move_count FROM posts WHERE action_code = 'split_topic' AND created_at BETWEEN :start_date AND :end_date GROUP BY user_id ORDER BY move_count DESC Where example results would look like: user move_count Username_1 5 Username_2 2 … … Note that with both of these queries, moving any number of posts from one topic to a different topic is only counted as one action, regardless of the number of posts that were moved. Moving the content on an entire topic to a different topic is also only counted as one action. The number of posts that were moved during each action can be seen in the Individual Post Move Actions query under the post column with the text X posts were merged into an existing topic..., however, this information is not present in the second query. In the Individual Post Move Actions query, you may also see posts with the text: A post was split to a new topic ..., which indicate that the post was split into a new topic instead of being moved into an existing topic, as Discourse considers both of these actions split_topic actions, since posts are being moved from one topic to a different topic.

Rapport du tableau de bord - Activité du modérateur - #7 par SaraDev - Données et rapports

Sujet		Réponses	Vues	Activité
Analyzing Moderation and Flagging Activity Reports Data & reporting moderation , sql-query	0	315	Février 13, 2025
User Page Metrics Data & reporting sql-query	6	346	Février 27, 2025
Add more Moderator activity metrics to the Moderation tab on the dashboard Feature moderation , dashboard-reports , reporting	0	123	Juillet 4, 2025
Solved Topic and Moderator Response Stats by Category Data & reporting solved , sql-query	0	444	Avril 3, 2024
Dashboard Report - Flags Status Data & reporting sql-query , dashboard-reports , dashboard-sql	0	461	Décembre 22, 2023

Analyzing Moderation and Flagging Activity Reports

Data & reporting

moderation , sql-query

0

315

Février 13, 2025

User Page Metrics

Data & reporting

sql-query

6

346

Février 27, 2025

Add more Moderator activity metrics to the Moderation tab on the dashboard

Feature

moderation , dashboard-reports , reporting

0

123

Juillet 4, 2025

Solved Topic and Moderator Response Stats by Category

Data & reporting

solved , sql-query

0

444

Avril 3, 2024

Dashboard Report - Flags Status

Data & reporting

sql-query , dashboard-reports , dashboard-sql

0

461

Décembre 22, 2023

Rapport du tableau de bord - Activité du modérateur

Sujets connexes