Аудит контента: отчет по категориям, темам и ответам

[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,

Использование запроса в Data Explorer — лучший способ найти нужную информацию.

Основываясь на предоставленных вами темах и описании ваших потребностей в отчётности, вот запрос Data Explorer, который можно использовать как отправную точку:

Категории, темы и ответы

-- [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 сообщения	Содержимое сообщения	Дата создания сообщения	Автор сообщения	Номер сообщения
Общие обсуждения	101	Добро пожаловать на форум!	2023-01-02 10:00:00 UTC	1	201	Всем привет, добро пожаловать!	2023-01-02 10:05:00 UTC	2	1
Общие обсуждения	101	Добро пожаловать на форум!	2023-01-02 10:00:00 UTC	1	202	Спасибо за тёплый приём!	2023-01-02 10:10:00 UTC	3	2
Техподдержка	102	Как сбросить устройство?	2023-02-15 14:30:00 UTC	4	203	Кто-нибудь может помочь мне сбросить это?	2023-02-15 14:35:00 UTC	4	1
Техподдержка	102	Как сбросить устройство?	2023-02-15 14:30:00 UTC	4	204	Конечно, вот шаги…	2023-02-15 14:40:00 UTC	5	2
Объявления	103	Вышли новые функции!	2023-03-01 09:00:00 UTC	6	205	Ознакомьтесь с нашими новыми функциями!	2023-03-01 09:05:00 UTC	6	1

Вы можете изменить порядок сортировки результатов в запросе с помощью оператора ORDER BY в конце, в зависимости от того, как вы хотите их просматривать. Также имейте в виду, что если на вашем сайте большое количество тем и сообщений, вам следует учитывать ограничения результатов плагина Data Explorer.

[RachFeverBee (Rachael Power)]

Спасибо, Сара, это очень полезно и полностью соответствует тому, что нам сейчас нужно.