Looking for information about the Admin Dashboard Reports? You’re in the right place!
This is a reference guide for describing how the Admin Dashboard Reports function, the data they’re displaying, and where to find the Ruby code for each report.
Admin Dashboard Reports
This post is a Wiki! Feel free to add information here you feel may be helpful for other admins, moderators, or developers using Discourse Reports.
Discourse contains several built-in Admin Dashboard Reports that can be useful for exploring stats about a community. To access these reports, you can visit /admin/dashboard/reports on your site ( or click the Reports link at the top of the dashboard). Note that only staff users will have access to these reports.
Data from all users on a site is included in these reports (including staff activity like visiting admin pages). The only condition that is put on users in the reports is that they are ‘real’ users, which is used to exclude the system user from the reports.
Plugins can also add reports to the dashboard with add_report(name, &block).
Ruby models for most reports are located at: discourse/app/models/concerns/reports/. Some reports also reference: discourse/app/models/report.rb
Below you’ll find detailed descriptions about each report available on the Admin Dashboard:
The Ruby code for some reports contains the exact SQL query used to generate the report, and can easily be adapted for use with the Data Explorer plugin and Running Data Explorer queries with the Discourse API
Accepted solutions
Ruby code: https://github.com/discourse/discourse-solved/blob/main/plugin.rb#L368
Admin Logins
List of admin login times with locations.
Ruby Code: discourse/app/models/concerns/reports/staff_logins.rb
Anonymous
Number of new pageviews by visitors not logged in to an account.
Ruby Code: discourse/app/models/concerns/reports/consolidated_page_views.rb
Bookmarks
Number of new topics and posts bookmarked.
Ruby Code: discourse/app/models/concerns/reports/bookmarks.rb
Consolidated Pageviews
Pageviews for logged in users, anonymous users and crawlers.
Ruby Code: discourse/app/models/concerns/reports/consolidated_page_views.rb
SQL Consolidated Pageviews
-- [params]
-- date :start_date
-- date :end_date
SELECT
ar.date::date AS "date",
CASE
WHEN ar.req_type=6 THEN 'Crawlers'
WHEN ar.req_type=7 THEN 'Logged in users'
WHEN ar.req_type=8 THEN 'Anonymous users'
END user_type,
SUM(ar.count) AS views
FROM application_requests ar
WHERE req_type IN (6,7,8)
AND ar.date::date BETWEEN :start_date AND :end_date
GROUP BY "date", user_type
ORDER BY "date"
DAU/MAU
Number of members that logged in in the last day divided by number of members that logged in in the last month – returns a % which indicates community ‘stickiness’. Aim for >20%.
Ruby Code: discourse/app/models/concerns/reports/dau_by_mau.rb
Daily Engaged Users
Number of users that have liked or posted in the last day.
Ruby Code: discourse/app/models/concerns/reports/daily_engaged_users.rb
SQL Daily engaged users
--[params]
-- date :start_date
-- date :end_date
SELECT
ua.created_at::date,
COUNT(DISTINCT ua.user_id) AS qtt
FROM user_actions ua
INNER JOIN topics t on t.id = ua.target_topic_id
WHERE ua.created_at::date BETWEEN :start_date AND :end_date
AND ua.action_type IN (1, 4, 5, 12)
GROUP BY ua.created_at::date
ORDER BY 2 DESC
Emails Sent
Number of new emails sent.
Ruby Code: discourse/app/models/concerns/reports/emails.rb
Flags
Number of new flags.
Ruby Code: discourse/app/models/concerns/reports/flags.rb
Flags Status
List of flags’ statuses including type of flag, poster, flagger, and time to resolution.
Ruby Code: discourse/app/models/concerns/reports/flags_status.rb
Likes
Number of new likes.
Ruby Code: discourse/app/models/concerns/reports/likes.rb
Logged In
Number of new pageviews from logged in users.
Ruby Code: discourse/app/controllers/admin/reports_controller.rb#L5
Moderator Activity
List of moderator activity including flags reviewed, reading time, topics created, posts created, personal messages created, and revisions.
Moderator Warning
Number of warnings sent by personal messages from moderators.
Ruby Code: discourse/app/models/concerns/reports/moderator_warning_private_messages.rb
New Contributors
Number of users who made their first post during this period.
Ruby Code: discourse/app/models/concerns/reports/new_contributors.rb
Notify Moderators
Number of times moderators have been privately notified by a flag.
Ruby Code: discourse/app/models/concerns/reports/notify_moderators_private_messages.rb
Notify User
Number of times users have been privately notified by a flag.
Ruby Code: discourse/app/models/concerns/reports/notify_user_private_messages.rb
Pageviews
Number of new pageviews from all visitors. Same as the total for Consolidated Pageviews.
Discourse uses the follow query to determine total pageviews:
SQL Total Pageviews
-- [params]
-- date :start_date
-- date :end_date
SELECT
ar.date::date AS "date",
SUM(ar.count) AS pageviews
FROM application_requests ar
WHERE req_type IN (6,7,8)
AND ar.date::date BETWEEN :start_date AND :end_date
GROUP BY "date"
ORDER BY "date" DESC
Post Edits
Number of new post edits.
Ruby Code: discourse/app/models/concerns/reports/post_edits.rb
Posts
New posts created during this period
Ruby Code: discourse/app/models/concerns/reports/posts.rb
SQL Posts
--[params]
-- date :start_date
-- date :end_date
SELECT
p.created_at::date,
COUNT(p.id) AS qtd
FROM posts p
INNER JOIN topics t ON t.id = p.topic_id AND t.deleted_at ISNULL
WHERE p.created_at::date BETWEEN :start_date AND :end_date
AND p.post_number > 1
AND p.user_id > 0
AND p.deleted_at ISNULL
AND t.archetype = 'regular'
GROUP BY p.created_at::date
ORDER BY 2 DESC
Reactions
List most recent reactions.
Ruby code: https://github.com/discourse/discourse-reactions/blob/main/plugin.rb#L194
Signups
New account registrations for this period.
Ruby Code: discourse/app/models/concerns/reports/signups.rb
SQL Signups
-- [params]
-- date :start_date
-- date :end_date
SELECT
u.created_at::date AS Day,
Count (id)
FROM users u
WHERE
u.created_at::date BETWEEN :start_date::date AND :end_date::date
GROUP BY u.created_at::date
Suspicious Logins
Details of new logins that differ suspiciously from previous logins.
Ruby Code: discourse/app/models/concerns/reports/suspicious_logins.rb
System
Number of personal messages sent automatically by the System.
Ruby Code: discourse/app/models/concerns/reports/system_private_messages.rb
Time to first response
Average time (in hours) of the first response to new topics.
Ruby Code: discourse/app/models/concerns/reports/time_to_first_response.rb
SQL Time to first response
--[params]
-- date :date_start
-- date :date_end
WITH first_reply AS (
SELECT
p.topic_id,
MIN(post_number) post_number,
t.created_at
FROM posts p
INNER JOIN topics t ON (p.topic_id = t.id)
WHERE p.deleted_at IS NULL
AND p.post_number != 1
AND p.post_type = 1
AND p.user_id > 0
AND t.user_id > 0
AND t.deleted_at IS NULL
AND t.archetype = 'regular'
AND t.created_at::date BETWEEN :date_start AND :date_end
GROUP BY p.topic_id, t.created_at
ORDER BY 2 DESC
)
SELECT
p.topic_id,
fr.created_at::date dt_topic_created,
(p.created_at - fr.created_at) response_time
FROM posts p
INNER JOIN first_reply fr
ON fr.topic_id = p.topic_id
AND fr.post_number = p.post_number
AND p.created_at > fr.created_at
ORDER BY response_time
Top Ignored / Muted Users
Users who have been muted and/or ignored by many other users.
Ruby Code: discourse/app/models/concerns/reports/top_ignored_users.rb
Top Referred Topics
Topics that have received the most clicks from external sources.
Ruby Code: discourse/app/models/concerns/reports/top_referred_topics.rb
SQL Top Referred Topics
--[params]
-- date :start_date
-- date :end_date
SELECT
p.topic_id,
COUNT(*) AS external_click_count
FROM incoming_links il
INNER JOIN posts p ON p.id = il.post_id
WHERE il.created_at::date BETWEEN :start_date AND :end_date
AND p.deleted_at ISNULL
GROUP BY p.topic_id
ORDER BY external_click_count DESC
LIMIT 10
Top Referrers
Users listed by number of clicks on links they have shared.
Ruby Code: discourse/app/models/concerns/reports/top_referrers.rb
SQL Top referrers
--[params]
-- date :start_date
-- date :end_date
SELECT
il.user_id,
COUNT(*) Clicks,
COUNT(DISTINCT p.topic_id) Topics
FROM incoming_links il
INNER JOIN posts p ON p.id = il.post_id
WHERE il.created_at::date BETWEEN :start_date AND :end_date
AND il.user_id > 0
GROUP BY il.user_id
ORDER BY 2 DESC
LIMIT 10
Top Traffic Sources
External sources that have linked to this site the most.
Ruby Code: discourse/app/models/concerns/reports/top_traffic_sources.rb
Top Uploads
List all uploads by extension, filesize and author.
Ruby Code: discourse/app/models/concerns/reports/top_uploads.rb
SQL Top uploads
--[params]
-- date :start_date
-- date :end_date
SELECT
user_id,
extension,
ROUND((filesize/1048576.00),1)||'MB' AS "File size",
original_filename Filename
FROM uploads
WHERE created_at::date BETWEEN :start_date AND :end_date
ORDER BY filesize DESC
Top Users by likes received
Top 10 users who have received likes.
Ruby Code: discourse/app/models/concerns/reports/top_users_by_likes_received.rb
Top Users by likes received from a user with a lower trust level
Top 10 users in a higher trust level being liked by people in a lower trust level.
Ruby Code: discourse/app/models/concerns/reports/top_users_by_likes_received_from_inferior_trust_level.rb
Top Users by likes received from a variety of people
Top 10 users who have had likes from a wide range of people.
Ruby Code: discourse/app/models/concerns/reports/top_users_by_likes_received_from_a_variety_of_people.rb
Topic Views
Ruby Code:
SQL Topic Views
--[params]
-- date :start_date
-- date :end_date
SELECT
tv.viewed_at::date,
COUNT(*) AS qtd
FROM topic_views tv
WHERE tv.viewed_at::date BETWEEN :start_date AND :end_date
GROUP BY tv.viewed_at::date
ORDER BY tv.viewed_at::date DESC
Topics
New topics created during this period.
Ruby Code: discourse/app/models/concerns/reports/topics.rb
SQL Topics
--[params]
-- date :start_date
-- date :end_date
SELECT
p.created_at::date,
COUNT(p.id) AS qtd
FROM posts p
INNER JOIN topics t ON t.id = p.topic_id AND t.deleted_at ISNULL
WHERE p.created_at::date BETWEEN :start_date AND :end_date
AND p.post_number = 1
AND p.user_id > 0
AND p.deleted_at ISNULL
AND t.archetype = 'regular'
GROUP BY p.created_at::date
ORDER BY 2 DESC
Topics with no response
Number of new topics created that did not receive a response.
Ruby Code: discourse/app/models/concerns/reports/topics_with_no_response.rb
Trending Search Terms
Most popular search terms with their click-through rates.
Ruby Code: discourse/app/models/concerns/reports/trending_search.rb
SQL Trending search terms
--[params]
-- date :start_date
-- date :end_date
SELECT
lower(term),
count(id) AS term_count
FROM search_logs sl
WHERE sl.created_at::date BETWEEN :start_date
AND :end_date
GROUP BY lower(term)
ORDER BY 2 DESC
Trust Level growth
Number of users who increased their Trust Level during this period.
The Trust Level Growth report is pulling data from the user_histories table in the Discourse database. Specifically, this report is counting the number of times a user_histories.action is recorded for an increase in a user trust level.
Ruby Code: discourse/app/models/concerns/reports/trust_level_growth.rb
Unaccepted policies
Ruby code: https://github.com/discourse/discourse-policy/blob/main/plugin.rb#L159
User Flagging Ratio
List of users ordered by ratio of staff response to their flags (disagreed to agreed).
Ruby Code: discourse/app/models/concerns/reports/user_flagging_ratio.rb
User notes
List most recent user notes.
Ruby code: https://github.com/discourse/discourse-user-notes/blob/main/plugin.rb#L153
User Profile Views
Total new views of user profiles.
Ruby Code: discourse/app/models/concerns/reports/profile_views.rb
User Visits
The total number of user visits in the forum for the selected time period (today, yesterday, last 7 days, etc).
A User Visit is counted anytime a unique logged in user visits the site, up to once per day. For example, if a user visited a site every day within a week, Discourse would count that as 7 user visits.
Ruby Code: discourse/app/models/concerns/reports/visits.rb
User Visits (mobile)
Number of unique users who visited using a mobile device.
Ruby Code: discourse/app/models/concerns/reports/mobile_visits.rb
User-to-User (excluding replies)
Number of newly initiated personal messages.
Ruby Code: discourse/app/models/concerns/reports/user_to_user_private_messages.rb
User-to-User (with replies)
Number of all new personal messages and responses.
Ruby Code: discourse/app/models/concerns/reports/user_to_user_private_messages_with_replies.rb
SQL User to User (with replies)
--[params]
-- date :start_date
-- date :end_date
SELECT
p.created_at::date,
COUNT(p.user_id)
FROM posts p
INNER JOIN topics t ON (p.topic_id = t.id)
WHERE archetype = 'private_message'
AND p.user_id > 0
AND t.subtype = 'user_to_user'
AND p.deleted_at ISNULL
AND t.deleted_at ISNULL
AND p.created_at::date BETWEEN :start_date AND :end_date
GROUP BY p.created_at::date
ORDER BY p.created_at::date
Users currently away
…
Users per Trust Level
Number of users grouped by trust level.
Ruby Code: discourse/app/models/concerns/reports/users_by_trust_level.rb
SQL Users per trust level
SELECT
CASE
WHEN trust_level = 0 THEN 'newuser'
WHEN trust_level = 1 THEN 'basic'
WHEN trust_level = 2 THEN 'member'
WHEN trust_level = 3 THEN 'regular'
WHEN trust_level = 4 THEN 'leader'
ELSE 'unknown'
END,
COUNT(users)
FROM users
WHERE
id > 0
AND NOT EXISTS(SELECT 1
FROM anonymous_users a
WHERE a.user_id = users.id)
GROUP BY trust_level
ORDER BY trust_level
Users per Type
Number of users grouped by admin, moderator, suspended, and silenced.
Ruby Code: discourse/app/models/concerns/reports/users_by_type.rb
SQL Users per type
SELECT "Type", "Count" FROM (
SELECT 'Admin' AS "Type", COUNT(id) AS "Count", 1 rk FROM users WHERE admin = true AND id > 0
UNION SELECT 'Moderator', COUNT(id), 2 FROM users WHERE moderator = true AND id > 0
UNION SELECT 'Suspended', COUNT(id), 3 FROM users WHERE suspended_till > current_date
UNION SELECT 'Silenced', COUNT(id), 4 FROM users WHERE silenced_till > current_date) AS data
ORDER BY rk
Web Crawler Pageviews
Total pageviews from web crawlers over time.
Ruby Code: discourse/app/models/concerns/reports/web_crawlers.rb
Web Crawler User Agents
List of web crawler user agents, sorted by pageviews.
Ruby Code: discourse/app/models/concerns/reports/web_crawlers.rb
