Este relatório do Explorador de Dados fornece uma análise abrangente de tópicos resolvidos e não resolvidos em um site, dentro de um intervalo de datas especificado e, opcionalmente, filtrado por uma tag específica.
Este relatório requer que o plugin Discourse Solved esteja ativado.
Este relatório requer que o plugin Este relatório é particularmente útil para administradores e moderadores que buscam entender a capacidade de resposta da comunidade e identificar áreas para melhoria no suporte e engajamento do usuário.
Estatísticas de Tópicos Resolvidos e Não Resolvidos com Parâmetros de Data e Tag
--[params]
-- date :start_date = 2022-01-01
-- date :end_date = 2024-01-01
-- text :tag_name = all
WITH valid_topics AS (
SELECT
t.id,
t.user_id,
t.title,
t.views,
(SELECT COUNT(*) FROM posts WHERE topic_id = t.id AND deleted_at IS NULL AND post_type = 1) - 1 AS "posts_count",
t.created_at,
(CURRENT_DATE::date - t.created_at::date) AS "total_days",
STRING_AGG(tags.name, ', ') AS tag_names, -- Agrega tags para cada tópico
c.name AS category_name
FROM topics t
LEFT JOIN topic_tags tt ON tt.topic_id = t.id
LEFT JOIN tags ON tags.id = tt.tag_id
LEFT JOIN categories c ON c.id = t.category_id
WHERE t.deleted_at IS NULL
AND t.created_at::date BETWEEN :start_date AND :end_date
AND t.archetype = 'regular'
GROUP BY t.id, c.name
),
solved_topics AS (
SELECT
vt.id,
dsst.created_at
FROM discourse_solved_solved_topics dsst
INNER JOIN valid_topics vt ON vt.id = dsst.topic_id
),
last_reply AS (
SELECT p.topic_id, p.user_id FROM posts p
INNER JOIN (SELECT topic_id, MAX(id) AS post FROM posts p
WHERE deleted_at IS NULL
AND post_type = 1
GROUP BY topic_id) x ON x.post = p.id
),
first_reply AS (
SELECT p.topic_id, p.user_id, p.created_at FROM posts p
INNER JOIN (SELECT topic_id, MIN(id) AS post FROM posts p
WHERE deleted_at IS NULL
AND post_type = 1
AND post_number > 1
GROUP BY topic_id) x ON x.post = p.id
)
SELECT
CASE
WHEN st.id IS NOT NULL THEN 'solved'
ELSE 'unsolved'
END AS status,
vt.tag_names,
vt.category_name,
vt.id AS topic_id,
vt.user_id AS topic_user_id,
ue.email,
vt.title,
vt.views,
lr.user_id AS last_reply_user_id,
ue2.email AS last_reply_user_email,
vt.created_at::date AS topic_create,
COALESCE(TO_CHAR(fr.created_at, 'YYYY-MM-DD'), '') AS first_reply_create,
COALESCE(TO_CHAR(st.created_at, 'YYYY-MM-DD'), '') AS solution_create,
COALESCE(fr.created_at::date - vt.created_at::date, 0) AS "time_first_reply(days)",
COALESCE(CEIL(EXTRACT(EPOCH FROM (fr.created_at - vt.created_at)) / 3600.00), 0) AS "time_first_reply(hours)",
COALESCE(st.created_at::date - vt.created_at::date, 0) AS "time_solution(days)",
COALESCE(CEIL(EXTRACT(EPOCH FROM (st.created_at - vt.created_at)) / 3600.00), 0) AS "time_solution(hours)",
vt.created_at::date,
vt.posts_count AS number_of_replies,
vt.total_days AS total_days_without_solution
FROM valid_topics vt
LEFT JOIN last_reply lr ON lr.topic_id = vt.id
LEFT JOIN first_reply fr ON fr.topic_id = vt.id
LEFT JOIN solved_topics st ON st.id = vt.id
INNER JOIN user_emails ue ON vt.user_id = ue.user_id AND ue."primary" = true
LEFT JOIN user_emails ue2 ON lr.user_id = ue2.user_id AND ue2."primary" = true
WHERE (:tag_name = 'all' OR vt.tag_names ILIKE '%' || :tag_name || '%')
GROUP BY st.id, vt.tag_names, vt.category_name, vt.id, vt.user_id, ue.email, vt.title, vt.views, lr.user_id, ue2.email, vt.created_at, fr.created_at, st.created_at, vt.posts_count, vt.total_days
ORDER BY topic_create, vt.total_days DESC
Explicação da Consulta SQL
A consulta SQL complexa gera o relatório, utilizando Expressões de Tabela Comuns (CTEs) para organizar e processar os dados de forma eficiente. A consulta é estruturada da seguinte forma:
- valid_topics: Esta CTE filtra os tópicos pelo intervalo de datas especificado e pelo arquétipo (‘regular’), excluindo tópicos excluídos. Ela também agrega tags associadas a cada tópico para filtragem posterior por nome de tag, se especificado.
- solved_topics: Identifica os tópicos que foram marcados como resolvidos.
- last_reply: Determina o usuário que fez a última resposta em cada tópico, encontrando o ID máximo de postagem (indicando a postagem mais recente) que não foi excluída e é do tipo de postagem 1 (indicando uma postagem regular).
- first_reply: Semelhante a last_reply, mas identifica o primeiro usuário a responder ao tópico após a postagem original.
A consulta principal combina essas CTEs para compilar um relatório detalhado de cada tópico, incluindo se ele está resolvido ou não, nomes das tags, nome da categoria, IDs de tópico e usuário, e-mails, visualizações, contagens de respostas e tempos para a primeira resposta e solução.
Parâmetros
- start_date: O início do intervalo de datas para o qual gerar o relatório.
- end_date: O fim do intervalo de datas para o qual gerar o relatório.
- tag_name: A tag específica para filtrar os tópicos. Use ‘all’ para incluir tópicos com qualquer tag.
Resultados
O relatório fornece as seguintes informações para cada tópico dentro dos parâmetros especificados:
- status: Indica se o tópico foi resolvido ou permanece não resolvido.
- tag_names: Mostra as tags associadas ao tópico.
- category_name: Mostra a categoria associada ao tópico.
- topic_id: O identificador exclusivo do tópico.
- topic_user_id: O ID do usuário que criou o tópico.
- user_email: O endereço de e-mail do criador do tópico.
- title: O título do tópico.
- views: O número de visualizações que o tópico recebeu.
- last_reply_user_id: O ID do usuário que fez a última resposta no tópico.
- last_reply_user_email: O endereço de e-mail do usuário que fez a última resposta.
- topic_create: A data em que o tópico foi criado.
- first_reply_create: A data da primeira resposta ao tópico.
- solution_create: A data em que uma solução foi marcada para o tópico (se aplicável).
- time_first_reply(days/hours): O tempo levado para receber a primeira resposta, em dias e horas.
- time_solution(days/hours): O tempo levado para resolver o tópico, em dias e horas.
- created_at: A data de criação do tópico.
- number_of_replies: O número total de respostas ao tópico.
- total_days_without_solution: O número total de dias que o tópico esteve ativo sem uma solução.
Exemplo de Resultados
| status | tag_names | category_name | topic_id | topic_user_id | user_email | title | views | last_reply_user_id | last_reply_user_email | topic_create | first_reply_create | solution_create | time_first_reply(days) | time_first_reply(hours) | time_solution(days) | time_solution(hours) | created_at | number_of_replies | total_days_without_solution |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| solved | support, password | category1 | 101 | 1 | user1@example.com | How to reset my password? | 150 | 3 | user3@example.com | 2022-01-05 | 2022-01-06 | 2022-01-07 | 1 | 24 | 2 | 48 | 2022-01-05 | 5 | 2 |
| unsolved | support, account | category2 | 102 | 2 | user2@example.com | Issue with account activation | 75 | 4 | user4@example.com | 2022-02-10 | 2022-02-12 | 2 | 48 | 0 | 0 | 2022-02-10 | 3 | 412 | |
| solved | support | category3 | 103 | 5 | user5@example.com | Can’t upload profile picture | 200 | 6 | user6@example.com | 2022-03-15 | 2022-03-16 | 2022-03-18 | 1 | 24 | 3 | 72 | 2022-03-15 | 8 | 3 |
| unsolved | NULL | category4 | 104 | 7 | user7@example.com | Error when posting | 50 | 8 | user8@example.com | 2022-04-20 | 0 | 0 | 0 | 0 | 2022-04-20 | 0 | 373 |
