Questo report di Data Explorer fornisce un’analisi completa degli argomenti risolti e irrisolti su un sito, entro un intervallo di date specificato e opzionalmente filtrato per un tag specifico.
Questo report richiede che il plugin Discourse Solved sia abilitato.
Questo report richiede che il plugin Questo report è particolarmente utile per amministratori e moderatori che desiderano comprendere la reattività della community e identificare aree di miglioramento nel supporto e nell’engagement degli utenti.
Statistiche argomenti risolti e irrisolti con parametri 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, -- Aggrega i tag per ogni argomento
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
Spiegazione della query SQL
La reportistica viene generata tramite una complessa query SQL che utilizza Common Table Expressions (CTE) per organizzare ed elaborare i dati in modo efficiente. La query è strutturata come segue:
- valid_topics: Questa CTE filtra gli argomenti in base all’intervallo di date specificato e all’archetipo (‘regular’), escludendo gli argomenti eliminati. Aggrega anche i tag associati a ciascun argomento per un successivo filtraggio per nome del tag, se specificato.
- solved_topics: Identifica gli argomenti contrassegnati come risolti.
- last_reply: Determina l’utente che ha effettuato l’ultima risposta in ciascun argomento trovando l’ID del post massimo (che indica il post più recente) che non è eliminato ed è di tipo post 1 (indicando un post regolare).
- first_reply: Simile a last_reply, ma identifica il primo utente a rispondere all’argomento dopo il post originale.
La query principale combina quindi queste CTE per compilare un report dettagliato su ciascun argomento, includendo se è risolto o irrisolto, nomi dei tag, nome della categoria, ID argomento e utente, email, visualizzazioni, conteggio delle risposte e tempistiche per la prima risposta e la soluzione.
Parametri
- start_date: L’inizio dell’intervallo di date per cui generare il report.
- end_date: La fine dell’intervallo di date per cui generare il report.
- tag_name: Il tag specifico per filtrare gli argomenti. Utilizzare ‘all’ per includere argomenti con qualsiasi tag.
Risultati
Il report fornisce le seguenti informazioni per ciascun argomento all’interno dei parametri specificati:
- status: Indica se l’argomento è stato risolto o rimane irrisolto.
- tag_names: Mostra i tag associati all’argomento.
- category_name: Mostra la categoria associata all’argomento.
- topic_id: L’identificatore univoco dell’argomento.
- topic_user_id: L’ID dell’utente che ha creato l’argomento.
- user_email: L’indirizzo email del creatore dell’argomento.
- title: Il titolo dell’argomento.
- views: Il numero di visualizzazioni ricevute dall’argomento.
- last_reply_user_id: L’ID dell’utente che ha effettuato l’ultima risposta all’argomento.
- last_reply_user_email: L’indirizzo email dell’utente che ha effettuato l’ultima risposta.
- topic_create: La data di creazione dell’argomento.
- first_reply_create: La data della prima risposta all’argomento.
- solution_create: La data in cui è stata contrassegnata una soluzione per l’argomento (se applicabile).
- time_first_reply(days/hours): Il tempo impiegato per ricevere la prima risposta, in giorni e ore.
- time_solution(days/hours): Il tempo impiegato per risolvere l’argomento, in giorni e ore.
- created_at: La data di creazione dell’argomento.
- number_of_replies: Il numero totale di risposte all’argomento.
- total_days_without_solution: Il numero totale di giorni in cui l’argomento è stato attivo senza una soluzione.
Risultati di esempio
| 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 | Come reimpostare la mia 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 | Problema con l’attivazione dell’account | 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 | Impossibile caricare l’immagine del profilo | 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 | Errore durante la pubblicazione | 50 | 8 | user8@example.com | 2022-04-20 | 0 | 0 | 0 | 0 | 2022-04-20 | 0 | 373 |
