# Content Audit : Report to pull Categories, Topics and Replies
**URL:** https://meta.discourse.org/t/content-audit-report-to-pull-categories-topics-and-replies/349354
**Category:** Data & reporting
**Tags:** sql-query
**Created:** [January 28, 2025, 11:33am UTC](https://meta.discourse.org/t/content-audit-report-to-pull-categories-topics-and-replies/349354 "2025-01-28T11:33:58Z")
**Posts on this page:** 4
**Page:** 1
### Author:  [@RachFeverBee](https://meta.discourse.org/u/RachFeverBee)
#### Post date: [January 28, 2025, 11:33am UTC](https://meta.discourse.org/t/content-audit-report-to-pull-categories-topics-and-replies/349354/1 "2025-01-28T11:33:58Z")
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](https://meta.discourse.org/t/32566?silent=true) is the best option. I’m looking at these posts :
> [@Dashboard Report - Topics](https://meta.discourse.org/t/dashboard-report-topics/288446):
>
> 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?](https://meta.discourse.org/t/how-to-get-reports-for-just-a-specific-category/155703/6):
>
> 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!
---
### Author:  [@SaraDev](https://meta.discourse.org/u/SaraDev)
#### Post date: [January 29, 2025, 12:42am UTC](https://meta.discourse.org/t/content-audit-report-to-pull-categories-topics-and-replies/349354/3 "2025-01-29T00:42:29Z")
Hi @RachFeverBee,
Using a [Data Explorer](https://meta.discourse.org/t/32566?silent=true) 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](https://meta.discourse.org/t/32566?silent=true) query you could use as a starting point for this:
**Categories Topics and Replies**
```sql
-- [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](https://meta.discourse.org/t/discourse-data-explorer/32566#p-138280-result-limits-and-exporting-queries-7) with the [Data Explorer](https://meta.discourse.org/t/32566?silent=true) plugin.
---
### Author:  [@RachFeverBee](https://meta.discourse.org/u/RachFeverBee)
#### Post date: [January 31, 2025, 10:13am UTC](https://meta.discourse.org/t/content-audit-report-to-pull-categories-topics-and-replies/349354/4 "2025-01-31T10:13:39Z")
Thanks Sara, this is really helpful and aligns with exactly what we need right now.
---
### Author:  [@system](https://meta.discourse.org/u/system)
#### Post date: [March 2, 2025, 11:49pm UTC](https://meta.discourse.org/t/content-audit-report-to-pull-categories-topics-and-replies/349354/6 "2025-03-02T23:49:13Z")
This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.