管理员控制面板报告参考指南

[Discourse]

This is a reference guide for describing how the Admin Dashboard Reports function, the data they’re displaying, the corresponding Data Explorer SQL queries, and where to find the Ruby code for each report.

Required user level: Staff

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 discourse.example.com/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

The dashboard-sql topics contain all of the corresponding SQL queries that can be used to generate reports identical to the Admin Dashboard Reports. These queries can be used within the Data Explorer plugin and for Running Data Explorer queries with the Discourse API

Accepted solutions

Displays daily tally of posts marked as solutions.

Ruby code: discourse-solved/plugin.rb at main · discourse/discourse-solved · GitHub

SQL Query: Dashboard Report - Accepted Solutions

Admin Logins

List of admin login times with locations.

Ruby Code: discourse/app/models/concerns/reports/staff_logins.rb

SQL Query: Dashboard Report - Admin Logins

Anonymous

Number of new pageviews by visitors not logged in to an account.

Ruby Code: discourse/app/models/concerns/reports/consolidated_page_views.rb

SQL Query: Dashboard Report - Anonymous

Bookmarks

Number of new topics and posts bookmarked.

Ruby Code: discourse/app/models/concerns/reports/bookmarks.rb

SQL Query: Dashboard Report - Bookmarks

Consolidated API Requests

API usage statistics by date, tracking both regular API requests and user API requests.

Ruby Code: discourse/app/models/concerns/reports/consolidated_api_requests.rb at main · discourse/discourse · GitHub

SQL Query: Dashboard Report - Consolidated API Requests

Consolidated Pageviews

Pageviews for logged in users, anonymous users and crawlers.

Ruby Code: discourse/app/models/concerns/reports/consolidated_page_views.rb

SQL Query: Dashboard Report - Consolidated Pageviews

Consolidated Pageviews with Browser Detection (Experimental)

Pageviews for logged in users, anonymous users, known crawlers and other. This experimental report ensures logged-in/anon requests are coming from real browsers before counting them.

Ruby Code: discourse/app/models/concerns/reports/consolidated_page_views_browser_detection.rb

SQL Query: Dashboard Report - Consolidated Pageviews with Browser Detection

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

SQL Query: Dashboard Report - DAU/MAU

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 Query: Dashboard Report - Daily Engaged Users

Emails Sent

Number of new emails sent.

Ruby Code: discourse/app/models/concerns/reports/emails.rb

SQL Query: Dashboard Report - Emails Sent

Flags

Number of new flags.

Ruby Code: discourse/app/models/concerns/reports/flags.rb

SQL Query: Dashboard Report - Flags

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

SQL Query: Dashboard Report - Flags Status

Likes

Number of new likes.

Ruby Code: discourse/app/models/concerns/reports/likes.rb

SQL Query: Dashboard Report - Likes

Logged In

Number of new pageviews from logged in users.

Ruby Code: discourse/app/controllers/admin/reports_controller.rb#L5

SQL Query: Dashboard Report - Logged In

Moderator Activity

List of moderator activity including flags reviewed, reading time, topics created, posts created, personal messages created, and revisions.

SQL Query: Dashboard Report - Moderator Activity

Moderator Warning

Number of warnings sent by personal messages from moderators.

Ruby Code: discourse/app/models/concerns/reports/moderator_warning_private_messages.rb

SQL Query: Dashboard Report - Moderator Warnings

New Contributors

Number of users who made their first post during this period.

Ruby Code: discourse/app/models/concerns/reports/new_contributors.rb

SQL Query: Dashboard Report - New Contributors

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

SQL Query: Dashboard Report - Notify Moderators

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

SQL Query: Dashboard Report - Notify User

Overall Sentiment

Number of posts classified either positive or negative with the “Sentiment” AI, over a specified period.

Ruby Code: discourse-ai/lib/sentiment/entry_point.rb at main · discourse/discourse-ai · GitHub

SQL Query: Dashboard Report - Overall Sentiment

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 Query: Dashboard Report - Consolidated Pageviews

Post Edits

Number of new post edits.

Ruby Code: discourse/app/models/concerns/reports/post_edits.rb

SQL Query: Dashboard Report - Post Edits

Posts

New posts created during the selected time period

Ruby Code: discourse/app/models/concerns/reports/posts.rb

SQL Query: Dashboard Report - Posts

Post Emotion

Number of posts classified by AI with one of following emotions: Sadness, Surprise, Fear, Anger, Joy, Disgust - group by poster trust level, over a specified period.

Ruby Code: discourse-ai/lib/sentiment/entry_point.rb at main · discourse/discourse-ai · GitHub

SQL Query: Dashboard Report - Post Emotion

Reactions

List most recent reactions.

Ruby code: discourse-reactions/plugin.rb at main · discourse/discourse-reactions · GitHub

SQL Query: Dashboard Report - Reactions

Signups

New account registrations for this period.

Ruby Code: discourse/app/models/concerns/reports/signups.rb

SQL Query: Dashboard Report - Signups

Site Traffic

Ruby Code: discourse/app/models/concerns/reports/site_traffic.rb

SQL Query: Dashboard Report - Site Traffic

Suspicious Logins

Details of new logins that differ suspiciously from previous logins.

Ruby Code: discourse/app/models/concerns/reports/suspicious_logins.rb

SQL Query: Dashboard Report - Suspicious Logins

System

Number of personal messages sent automatically by the System.

Ruby Code: discourse/app/models/concerns/reports/system_private_messages.rb

SQL Query: Dashboard Report - System

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 + discourse/discourse/blob/main/app/models/topic.rb#L1799-L1844

SQL Query: Dashboard Report - Time to First Response

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

SQL Query: Dashboard Report - Top Ignored / Muted Users

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 Query: Dashboard Report - Top Referred Topics

Top Referrers

Users listed by number of clicks on links they have shared.

Ruby Code: discourse/app/models/concerns/reports/top_referrers.rb

SQL Query: Dashboard Report - Top Referrers

Top Traffic Sources

External sources that have linked to this site the most.

Ruby Code: discourse/app/models/concerns/reports/top_traffic_sources.rb

SQL Query: Dashboard Report - Top Traffic Sources

Top Uploads

List all uploads by extension, filesize and author.

Ruby Code: discourse/app/models/concerns/reports/top_uploads.rb

SQL Query: Dashboard Report - Top Uploads

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

SQL Query: Dashboard Report - Top Users by Likes Received

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

SQL Query: Dashboard Report - Top Users by Likes Received from a User with a Lower Trust Level

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

SQL Query: Dashboard Report - Top Users by Likes Received From a Variety of People

Topics

New topics created during this period.

Ruby Code: discourse/app/models/concerns/reports/topics.rb

SQL Query: Dashboard Report - Topics

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

SQL Query: Dashboard Report - Topics with No Response

Topic View Stats

Ruby Code: discourse/app/models/concerns/reports/topic_view_stats.rb

SQL Query: Dashboard Report - Topic View Stats

Trending Search Terms

Most popular search terms with their click-through rates.

Ruby Code: discourse/app/models/concerns/reports/trending_search.rb

SQL Query: Dashboard Report - Trending Search Terms

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

SQL Query: Dashboard Report - Trust Level Growth

Unaccepted policies

This dashboard report identifies topics with policies that have not been accepted by certain users.

Ruby code: discourse-policy/plugin.rb at main · discourse/discourse-policy · GitHub

SQL Query: Dashboard Report - Unaccepted Policies

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

SQL Query: Dashboard Report - User Flagging Ratio

User notes

List most recent user notes.

Ruby code: discourse-user-notes/plugin.rb at main · discourse/discourse-user-notes · GitHub

SQL Query: Dashboard Report - User Notes

User Profile Views

Total new views of user profiles.

Ruby Code: discourse/app/models/concerns/reports/profile_views.rb

SQL Query: Dashboard Report - User Profile Views

User Visits

The total number of logged-in 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

SQL Query: Dashboard Report - User Visits

User Visits (mobile)

Number of unique logged-in users who visited using a mobile device.

Ruby Code: discourse/app/models/concerns/reports/mobile_visits.rb

SQL Query: Dashboard Report - User Visits

User-to-User (excluding replies)

Number of newly initiated personal messages.

Ruby Code: discourse/app/models/concerns/reports/user_to_user_private_messages.rb

SQL Query: Dashboard Report - User-to-User

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 Query: Dashboard Report - User-to-User

Users per Trust Level

Number of users grouped by trust level.

Ruby Code: discourse/app/models/concerns/reports/users_by_trust_level.rb

SQL Query: Dashboard Report - Users Per 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 Query: Dashboard Report - Users Per Type

Web Crawler Pageviews

Total pageviews from web crawlers over time.

Ruby Code: discourse/app/models/report.rb

SQL Query: Dashboard Report - Web Crawler Pageviews

Web Crawler User Agents

List of web crawler user agents, sorted by pageviews.

Ruby Code: discourse/app/models/concerns/reports/web_crawlers.rb

SQL Query: Dashboard Report - Web Crawler User Agents

Last edited by @SaraDev 2025-10-15T20:49:23Z

Last checked by @SaraDev 2025-01-28T21:42:37Z

Check document

Perform check on document:

[pfaffman (Jay Pfaffman)]

@SaraDev

Discourse 包含几个内置的管理仪表板报告，可用于探索社区统计信息。要访问这些报告，您可以在您的站点上访问 /admin/dashboard/reports。

我在 /admin 上看不到此链接。是我没看懂吗？这似乎应该更容易找到。我想我以前知道这些报告在这里，但找了却找不到。

虽然我只花了几分钟就找到了，但也许可以添加类似以下的内容：

插件可以使用 add_report(name, &block) 向仪表板添加报告。

[maiki]

@pfaffman

我在 /admin 上没有看到此链接。

@pfaffman

看起来应该更容易找到。我认为我知道这些报告在这里，但查找时却找不到。

是的，在网站首次创建时，通过私信告知员工可能会更好。

[pfaffman (Jay Pfaffman)]

抱歉。我确实认为以前在哪里见过。

你就是无法让人们阅读东西……但我可以阅读源代码来找出如何在插件中做到这一点？

但也许将上面的内容更新为

@SaraDev

. 要访问这些报告，您可以访问您网站上的 /admin/dashboard/reports 或点击仪表板上的 reports 链接。

我想这可能才是真正让我困惑的地方。（但没有，我没有借口。）

[maiki]

@pfaffman

但也许将上面的内容更新为

我已将主题设为 wiki，请随意编辑！

[hello-smile6 (9pfs)]

@SaraDev

目标是 >30%。

这与 UI 不符（UI 使用的是 20%），应该以哪个为准？

[JammyDodger]

抓得好。最近已更新至 20%。我将在 OP 中进行更改。

Is there a difference in target retention rate between a support-first forum and discussion-first forum?

Here’s a PR with that change: Longer term, I think a more nuanced approach could be powerful. I’d love for communities to be able to set their own goals (with some guidance like what you shared above about why they might choose one vs. the other) and use the dashboard or other related features to help them track their results vs. what they are aiming for.

[Yotam_Hagay]

@SaraDev

热门流量来源

外部来源中链接到此网站最多的来源。

Ruby 代码：discourse/app/models/concerns/reports/top_traffic_sources.rb

你好 @SaraDev 这个报告的输出可以用 SQL 查询获得吗？可以分享一下吗？
谢谢

[SaraDev (Sara Devlaeminck)]

@Yotam_Hagay

您好 @SaraDev 这个报告的输出可以用 SQL 查询获得吗？可以分享一下吗？
谢谢

是的，您可以使用以下 SQL 报告来获取热门流量来源：

-- [params]
-- date :start_date = 01/05/2023
-- date :end_date = 03/06/2023

WITH count_links AS (

SELECT COUNT(*) AS clicks,
       ind.name AS domain
FROM incoming_links il
  INNER JOIN posts p ON p.deleted_at ISNULL AND p.id = il.post_id
  INNER JOIN topics t ON t.deleted_at ISNULL AND t.id = p.topic_id
  INNER JOIN incoming_referers ir ON ir.id = il.incoming_referer_id
  INNER JOIN incoming_domains ind ON ind.id = ir.incoming_domain_id
WHERE t.archetype = 'regular'
  AND il.created_at::date BETWEEN :start_date AND :end_date
GROUP BY ind.name
ORDER BY clicks DESC
),

count_topics AS (

SELECT COUNT(DISTINCT p.topic_id) AS topics,
       ind.name AS domain
FROM incoming_links il
INNER JOIN posts p ON p.deleted_at ISNULL AND p.id = il.post_id
INNER JOIN topics t ON t.deleted_at ISNULL AND t.id = p.topic_id
INNER JOIN incoming_referers ir ON ir.id = il.incoming_referer_id
INNER JOIN incoming_domains ind ON ind.id = ir.incoming_domain_id
WHERE t.archetype = 'regular'
  AND il.created_at > (CURRENT_TIMESTAMP - INTERVAL '30 DAYS')
GROUP BY ind.name
)

SELECT cl.domain,
       cl.clicks AS "Clicks",
       ct.topics AS "Topics"
FROM count_links cl
JOIN count_topics ct ON cl.domain = ct.domain
LIMIT 10

在此查询中，请注意日期参数接受的格式为 日/月/年。

[Yotam_Hagay]

@SaraDev

是的，您可以使用以下 SQL 报表来获取热门流量来源：

您好 @SaraDev，感谢您分享此查询。
关于此报表以及 incoming_links 表，有一个更普遍的问题——它是否只代表帖子页面的流量，而不是论坛所有页面的流量？

背景：我正在寻找分析论坛整体流量趋势，并希望从热门流量来源报表中获得按来源划分的整体流量。
但是，虽然上个月的整体流量约为 272K（用户和匿名用户），但同一时间范围内的流量来源报表中的总点击次数仅为 59K。
此外，我看到您使用了与 topics 和 posts 表的内连接，这意味着如果点击没有附加 post_id，您就不会计算它。

您能否确认我的结论，并或许解释一下 incoming_links 表背后的逻辑？

[Yotam_Hagay]

@SaraDev

Posts

New posts created during this period

您好 @SaraDev 我运行了这个查询，结果与“常规”选项卡中的“帖子”报告不完全匹配。
例如，11月30日：
查询 = 112 篇帖子
报告 = 120 篇帖子
您能检查一下这个差距吗？
谢谢

[JammyDodger]

Just an FYI @Yotam_Hagay - even though Sara’s the OP, the guide is the responsibility of everyone There’s no need for an @mention on each post.

[Yotam_Hagay]

感谢 @JammyDodger 的澄清。

还有其他人我可以标记或求助以获得答案吗？

[Yotam_Hagay]

@SaraDev

SQL 首次响应时间

--[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

此查询结果与“首次响应时间”报告略有不同：
例如，在 11 月 8 日：
查询：93 小时
报告：116 小时
有人能给些建议吗？

[JammyDodger]

我认为其中一些可能需要一些时间来研究。我正在自己研究一下，看看是否能找出问题所在（尽管我的 SQL 技能和 Ruby 技能之间差距很大 ）。

但请继续提供您的发现，因为能够巩固所有这些信息将非常有帮助。

[JammyDodger]

@Yotam_Hagay

您好 @SaraDev，我运行了此查询，结果与“常规”选项卡中的“帖子”报告不完全匹配。
例如，11月30日：
查询 = 112 条帖子
报告 = 120 条帖子
您能检查一下差距吗？
谢谢

对于帖子，我认为库存报告也计算了主题帖子以及来自系统用户的帖子，但只针对 post_type 为 1 的帖子（即，不是低语、小型操作帖子或版主操作）。

我认为 SQL 会更像这样：

--[params]
-- date :start_date
-- date :end_date

SELECT 
    p.created_at::date AS "Day",
    COUNT(p.id) AS "Count"
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.deleted_at ISNULL
    AND t.archetype = 'regular'
    AND p.post_type = 1
GROUP BY p.created_at::date
ORDER BY 1

您能否在您的网站上运行它，看看是否匹配？

[Yotam_Hagay]

谢谢 Jammy，我会查看！
我目前正在进行首次响应时间分析，如果您能帮我看看这个，我将不胜感激。

[JammyDodger]

在查看 SQL 版本时，我认为它缺少 AND p.user_id <> t.user_id 来排除 OP 的回复。如果我添加它，它将给出 OP 和其他人首次回复之间准确的时间：

--[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.user_id <> t.user_id
        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

我还认为股票报告是以小数而不是 SQL 中的小时和分钟显示的。我将再次尝试使其匹配。

---

只是一个小更新，包含 AVG，使其更类似于股票报告的输出：

--[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.user_id <> t.user_id
        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
)

SELECT
    fr.created_at::date dt_topic_created,
    AVG(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
GROUP BY fr.created_at::date
ORDER BY response_time

这似乎与股票报告相符，只要考虑到一个是以小数显示的，而另一个是以 HH:MM 显示的。我相信有一种方法可以将 SQL 的 response_time 转换为小数，但 HH:MM 似乎是一种更直观的显示方式。（我认为其中还包含一些可能不必要的额外条件，但也可能是一种防止异常情况的安全措施，所以我最终将这些部分保留原样，直到我可以确定一件事或另一件事 ）

你能运行这个并看看它如何匹配吗？

[Yotam_Hagay]

是的，现在与股票报告中显示的数字相加了，谢谢！
只是有一个评论 -
我发现下面的 AVG 函数在时间 > 24 小时时会返回缺失的结果（我猜缺少了一个 # 天部分）。

AVG(p.created_at - fr.created_at)::time response_time

[JammyDodger]

是的，强制转换为 time 是个糟糕的选择。 如果删除 ::time，它将恢复为更准确（但更难看）的版本。

我也会编辑上面的内容。