Content Audit : Report to pull Categories, Topics and Replies

[RachFeverBee (Rachael Power)]

I’m working with a client to complete a Content Audit. I’d like to run a query to pull all categories, topics and replies from 2023 to now. Ideally, I’d like the report to include dates, poster, titles, links and full text.

I assume Discourse Data Explorer is the best option. I’m looking at these posts :

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

Has anyone run a similar report? Could you offer best practice from your own experience? Cheers!

[SaraDev (Sara Devlaeminck)]

Hi @RachFeverBee,

Using a Data Explorer query would be the best way to find the information you’re looking for.

Based on the topics you shared and the description of your reporting needs, here’s a Data Explorer query you could use as a starting point for this:

Categories Topics and Replies

-- [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 "Category",
    fd.topic_id AS "Topic ID",
    fd.topic_title AS "Topic Title",
    fd.topic_created_at AS "Topic Created At",
    fd.topic_user_id AS "Topic Poster",
    fd.post_id AS "Post ID",
    fd.post_content AS "Post Content",
    fd.post_created_at AS "Post Created At",
    fd.post_user_id AS "Post Poster",
    fd.post_number AS "Post Number"
FROM 
    final_data fd
ORDER BY 
    fd.topic_created_at ASC, 
    fd.post_created_at ASC

This report would generate the following results:

• Category Name: Name of the category.
• Topic ID: Linkable ID for the topic.
• Topic Title: Title of the topic.
• Topic Created At: Creation date of the topic.
• Topic Poster: User who created the topic.
• Post ID: Linkable ID for the post.
• Post Content: Full text of the post.
• Post Created At: Creation date of the post.
• Post Poster: User who created the post.
• Post Number: The post number within the topic.

Example Results

Category	Topic ID	Topic Title	Topic Created At	Topic Poster	Post ID	Post Content	Post Created At	Post Poster	Post Number
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

You may want to adjust how the query orders the results with the ORDER BY statement at the end depending on how you want to view the results, and also note that if your site has a large number of topics and posts you may need to keep in mind the Result Limits with the Data Explorer plugin.