Content Audit : Report to pull Categories, Topics and Replies

Data & reporting
3

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.

5 Likes