Auditoria de Conteúdo: Relatório para extrair Categorias, Tópicos e Respostas

[RachFeverBee (Rachael Power)]

Estou trabalhando com um cliente para concluir uma Auditoria de Conteúdo. Gostaria de executar uma consulta para extrair todas as categorias, tópicos e respostas de 2023 até agora. Idealmente, gostaria que o relatório incluísse datas, autor, títulos, links e texto completo.

Assumo que o Discourse Data Explorer é a melhor opção. Estou olhando estes 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

Alguém já executou um relatório semelhante? Poderia oferecer as melhores práticas com base na sua experiência? Obrigado!

[SaraDev (Sara Devlaeminck)]

Olá @RachFeverBee,

Usar uma consulta do Data Explorer seria a melhor maneira de encontrar as informações que você procura.

Com base nos tópicos que você compartilhou e na descrição de suas necessidades de relatórios, aqui está uma consulta do Data Explorer que você pode usar como ponto de partida:

Categorias, Tópicos e Respostas

-- [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 "Categoria",
    fd.topic_id AS "ID do Tópico",
    fd.topic_title AS "Título do Tópico",
    fd.topic_created_at AS "Tópico Criado Em",
    fd.topic_user_id AS "Autor do Tópico",
    fd.post_id AS "ID da Postagem",
    fd.post_content AS "Conteúdo da Postagem",
    fd.post_created_at AS "Postagem Criada Em",
    fd.post_user_id AS "Autor da Postagem",
    fd.post_number AS "Número da Postagem"
FROM 
    final_data fd
ORDER BY 
    fd.topic_created_at ASC, 
    fd.post_created_at ASC

Este relatório geraria os seguintes resultados:

• Nome da Categoria: Nome da categoria.
• ID do Tópico: ID clicável para o tópico.
• Título do Tópico: Título do tópico.
• Tópico Criado Em: Data de criação do tópico.
• Autor do Tópico: Usuário que criou o tópico.
• ID da Postagem: ID clicável para a postagem.
• Conteúdo da Postagem: Texto completo da postagem.
• Postagem Criada Em: Data de criação da postagem.
• Autor da Postagem: Usuário que criou a postagem.
• Número da Postagem: O número da postagem dentro do tópico.

Exemplo de Resultados

Categoria	ID do Tópico	Título do Tópico	Tópico Criado Em	Autor do Tópico	ID da Postagem	Conteúdo da Postagem	Postagem Criada Em	Autor da Postagem	Número da Postagem
Discussão Geral	101	Bem-vindo ao Fórum!	2023-01-02 10:00:00 UTC	1	201	Olá a todos, bem-vindos!	2023-01-02 10:05:00 UTC	2	1
Discussão Geral	101	Bem-vindo ao Fórum!	2023-01-02 10:00:00 UTC	1	202	Obrigado pelas boas-vindas!	2023-01-02 10:10:00 UTC	3	2
Suporte Técnico	102	Como redefinir meu dispositivo?	2023-02-15 14:30:00 UTC	4	203	Alguém pode me ajudar a redefinir isso?	2023-02-15 14:35:00 UTC	4	1
Suporte Técnico	102	Como redefinir meu dispositivo?	2023-02-15 14:30:00 UTC	4	204	Claro, aqui estão os passos…	2023-02-15 14:40:00 UTC	5	2
Anúncios	103	Novos Recursos Lançados!	2023-03-01 09:00:00 UTC	6	205	Confira nossos novos recursos!	2023-03-01 09:05:00 UTC	6	1

Você pode querer ajustar como a consulta ordena os resultados com a instrução ORDER BY no final, dependendo de como deseja visualizar os resultados, e também observar que, se seu site tiver um grande número de tópicos e postagens, você pode precisar ter em mente os Limites de Resultados com o plugin Data Explorer.

[RachFeverBee (Rachael Power)]

Obrigado, Sara, isso é muito útil e está alinhado com exatamente o que precisamos agora.