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 : [image] Dashboard Report - Topics Data & reporting 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_… [image] How to get reports for just a specific category? Data & reporting 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!

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.

Content Audit : Report to pull Categories, Topics and Replies

Community Building Data & reporting

RachFeverBee (Rachael Power ) January 31, 2025, 10:13am 4

Thanks Sara, this is really helpful and aligns with exactly what we need right now.

Topic		Replies	Views
Stats reports on a per-category basis? Data & reporting	2	1603	March 11, 2024
View all topics in category filter by date created Product Support	2	1015	November 13, 2017
Aggregate dashboard report data by time interval Data & reporting sql-query	4	1253	September 24, 2022
Use API to get topics for a period using js Development rest-api	2	907	February 28, 2023
Fetch Discourse Topics and Categories Extras	0	204	December 17, 2024

Content Audit : Report to pull Categories, Topics and Replies

Related topics