Este informe del Explorador de Datos proporciona un análisis completo de los temas resueltos y no resueltos en un sitio, dentro de un rango de fechas especificado y, opcionalmente, filtrado por una etiqueta específica.
Este informe requiere que el plugin Discourse Solved esté habilitado.
Este informe requiere que el plugin Este informe es particularmente útil para administradores y moderadores que buscan comprender la capacidad de respuesta de la comunidad e identificar áreas de mejora en el soporte y la participación de los usuarios.
Estadísticas de temas resueltos y no resueltos con parámetros de fecha y etiqueta
--[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 etiquetas para cada tema
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
Explicación de la consulta SQL
La generación del informe se realiza a través de una compleja consulta SQL que utiliza Expresiones Comunes de Tabla (CTE) para organizar y procesar los datos de manera eficiente. La consulta está estructurada de la siguiente manera:
- valid_topics: Esta CTE filtra los temas por el rango de fechas especificado y el arquetipo (‘regular’), excluyendo los temas eliminados. También agrega las etiquetas asociadas a cada tema para un filtrado posterior por nombre de etiqueta si se especifica.
- solved_topics: Identifica los temas que han sido marcados como resueltos.
- last_reply: Determina el usuario que realizó la última respuesta en cada tema encontrando el ID de publicación máximo (que indica la publicación más reciente) que no está eliminado y es de tipo de publicación 1 (indicando una publicación regular).
- first_reply: Similar a last_reply, pero identifica al primer usuario que responde al tema después de la publicación original.
La consulta principal combina estas CTE para compilar un informe detallado de cada tema, incluyendo si está resuelto o no, nombres de etiquetas, nombre de categoría, IDs de tema y usuario, correos electrónicos, vistas, recuentos de respuestas y tiempos de la primera respuesta y solución.
Parámetros
- start_date: El inicio del rango de fechas para el cual generar el informe.
- end_date: El final del rango de fechas para el cual generar el informe.
- tag_name: La etiqueta específica para filtrar los temas. Usa ‘all’ para incluir temas con cualquier etiqueta.
Resultados
El informe proporciona la siguiente información para cada tema dentro de los parámetros especificados:
- status: Indica si el tema ha sido resuelto o permanece sin resolver.
- tag_names: Muestra las etiquetas asociadas al tema.
- category_name: Muestra la categoría asociada al tema.
- topic_id: El identificador único del tema.
- topic_user_id: El ID del usuario que creó el tema.
- user_email: La dirección de correo electrónico del creador del tema.
- title: El título del tema.
- views: El número de vistas que ha recibido el tema.
- last_reply_user_id: El ID del usuario que realizó la última respuesta en el tema.
- last_reply_user_email: La dirección de correo electrónico del usuario que realizó la última respuesta.
- topic_create: La fecha en que se creó el tema.
- first_reply_create: La fecha de la primera respuesta al tema.
- solution_create: La fecha en que se marcó una solución para el tema (si corresponde).
- time_first_reply(days/hours): El tiempo transcurrido para recibir la primera respuesta, en días y horas.
- time_solution(days/hours): El tiempo transcurrido para resolver el tema, en días y horas.
- created_at: La fecha de creación del tema.
- number_of_replies: El número total de respuestas al tema.
- total_days_without_solution: El número total de días que el tema ha estado activo sin una solución.
Resultados de ejemplo
| 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 |
