内容审计：报告提取类别、主题和回复

[RachFeverBee (Rachael Power)]

我正在与一位客户合作完成内容审计。我想运行一个查询，以提取自 2023 年至今的所有类别、主题和回复。理想情况下，我希望报告包含日期、发帖人、标题、链接和完整文本。

我认为 Discourse Data Explorer 是最佳选择。我正在查看这些帖子：

Dashboard Report - Topics

This is an SQL version of the Dashboard Report for Topics. This report provides a count of new topics created on a daily basis within a specified date range. The report does not include personal messages, only regular topics that are visible on the forum. --[params] -- date :start_date -- date :end_date SELECT p.created_at::date as day, COUNT(p.id) AS topics_created 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_…

How to get reports for just a specific category?

I have been looking for this too- I have a category for Spanish language discussions and my facilitator is curious about who is reading content. The only way to find out which reports have filters fo category is to click through them all - so far I have found the ones with this feature include: Posts Post Edits Bookmarks Likes e.g. all the things users do to interact with a post. I cannot find anything that lets me understand traffic within a category

有人运行过类似的报告吗？能否根据您的经验提供最佳实践？谢谢！

[SaraDev (Sara Devlaeminck)]

您好 @RachFeverBee，

使用数据探索器查询是查找您所需信息的最佳方式。

根据您分享的主题以及您的报告需求描述，以下是一个数据探索器查询，您可以将其作为起点：

分类主题和回复

-- [params]
-- date :start_date = 2023-01-01
-- date :end_date = 2025-01-28

WITH filtered_topics AS (
    SELECT
        t.id AS topic_id,
        t.title AS topic_title,
        t.created_at AS topic_created_at,
        t.user_id AS topic_user_id,
        t.category_id AS topic_category_id
    FROM
        topics t
    WHERE
        t.created_at BETWEEN :start_date AND :end_date
),
filtered_posts AS (
    SELECT
        p.id AS post_id,
        p.topic_id,
        p.user_id AS post_user_id,
        p.created_at AS post_created_at,
        p.raw AS post_content,
        p.post_number
    FROM
        posts p
    WHERE
        p.created_at BETWEEN :start_date AND :end_date
),
categories_with_topics AS (
    SELECT
        c.id AS category_id,
        c.name AS category_name,
        ft.topic_id,
        ft.topic_title,
        ft.topic_created_at,
        ft.topic_user_id
    FROM
        categories c
    JOIN
        filtered_topics ft ON c.id = ft.topic_category_id
),
final_data AS (
    SELECT
        cwt.category_name,
        cwt.topic_id,
        cwt.topic_title,
        cwt.topic_created_at,
        cwt.topic_user_id,
        fp.post_id,
        fp.post_content,
        fp.post_created_at,
        fp.post_user_id,
        fp.post_number
    FROM
        categories_with_topics cwt
    LEFT JOIN
        filtered_posts fp ON cwt.topic_id = fp.topic_id
)
SELECT
    fd.category_name AS "分类",
    fd.topic_id AS "主题 ID",
    fd.topic_title AS "主题标题",
    fd.topic_created_at AS "主题创建时间",
    fd.topic_user_id AS "主题发帖人",
    fd.post_id AS "帖子 ID",
    fd.post_content AS "帖子内容",
    fd.post_created_at AS "帖子创建时间",
    fd.post_user_id AS "帖子发帖人",
    fd.post_number AS "帖子编号"
FROM
    final_data fd
ORDER BY
    fd.topic_created_at ASC,
    fd.post_created_at ASC

此报告将生成以下结果：

• 分类名称: 分类的名称。
• 主题 ID: 可链接的主题 ID。
• 主题标题: 主题的标题。
• 主题创建时间: 主题的创建日期。
• 主题发帖人: 创建主题的用户。
• 帖子 ID: 可链接的帖子 ID。
• 帖子内容: 帖子的全部文本。
• 帖子创建时间: 帖子的创建日期。
• 帖子发帖人: 创建帖子的用户。
• 帖子编号: 主题内的帖子编号。

示例结果

分类	主题 ID	主题标题	主题创建时间	主题发帖人	帖子 ID	帖子内容	帖子创建时间	帖子发帖人	帖子编号
General Discussion	101	Welcome to the Forum!	2023-01-02 10:00:00 UTC	1	201	Hello everyone, welcome!	2023-01-02 10:05:00 UTC	2	1
General Discussion	101	Welcome to the Forum!	2023-01-02 10:00:00 UTC	1	202	Thanks for the warm welcome!	2023-01-02 10:10:00 UTC	3	2
Tech Support	102	How to reset my device?	2023-02-15 14:30:00 UTC	4	203	Can someone help me reset this?	2023-02-15 14:35:00 UTC	4	1
Tech Support	102	How to reset my device?	2023-02-15 14:30:00 UTC	4	204	Sure, here are the steps…	2023-02-15 14:40:00 UTC	5	2
Announcements	103	New Features Released!	2023-03-01 09:00:00 UTC	6	205	Check out our new features!	2023-03-01 09:05:00 UTC	6	1

您可能需要根据您希望查看结果的方式，调整查询末尾 ORDER BY 语句的排序方式，并且请注意，如果您的网站有大量主题和帖子，您可能需要注意数据探索器插件的 结果限制。

[RachFeverBee (Rachael Power)]

谢谢 Sara，这真的很有帮助，而且与我们现在所需要的完全一致。