Requisito complesso per il report sull'attività utente

Sto cercando un modo per far sì che Discourse generi report di attività sotto forma di una panoramica di tutte le attività degli utenti tra due date personalizzate ogni anno (la funzionalità integrata attuale sembra avere solo intervalli predefiniti, e solo in relazione alla data di oggi) e salvare i risultati in un file compatibile con Excel (attualmente questo sembra possibile solo per i report di attività dei singoli utenti).

• Vorrei includere anche i post inviati via email e a cui è stato risposto via email (senza doppi conteggi dove gli stessi post vengono letti o pubblicati mentre si utilizza il forum online). I report attuali sembrano escludere i post via email.

• Vorrei filtrare questi risultati in base a un determinato campo del profilo utente personalizzato (un numero di adesione univoco).

• Vorrei escludere i risultati per gli utenti con numeri di adesione non compresi in un intervallo numerico nominato.

• Idealmente, vorrei anche la possibilità di generare un punteggio di punti di attività per utente per il periodo, ponderato in base ai post letti, ai post pubblicati e ai post che hanno ricevuto like.

• Le ponderazioni (moltiplicatori) per ciascuna di queste variabili di attività dovrebbero essere regolabili e impostate dall’amministratore. I risultati dovrebbero essere arrotondati per difetto al multiplo di 5 più vicino e limitati a un numero massimo di punti preimpostato, stabilito dall’amministratore.

• Sarebbe gradito avere una sorta di ripartizione per categoria di argomento/tag per utente.

• Idealmente, questo report verrebbe generato automaticamente e inviato via email a me a orari prestabiliti ogni anno (questo sarebbe la ciliegina sulla torta).

Quanto è fattibile tutto questo?

Sarebbe necessario un nuovo plugin personalizzato per implementarlo, o è possibile tramite una sorta di query SQL avanzata all’interno del plugin Data Explorer attuale?

O sarebbe la mossa intelligente cercare un’opzione di esportazione “tutto” relativamente semplice e tentare il resto utilizzando Excel?

Data Explorer sarebbe probabilmente l’approccio migliore in questo caso. Potrebbe essere necessario creare query diverse per ogni attività (ad esempio, l’analisi per utente richiederebbe una query distinta).

L’unica cosa che Data Explorer non può gestire è il punto relativo alla “generazione automatica e invio via email”. Se questo è un requisito, è possibile implementarlo chiamando l’API di Data Explorer da un altro sistema.

Grazie David.
Sembra che dovrò familiarizzare con le query SQL.

Una query può prendere in input l’output filtrato di un’altra query? Inoltre, è possibile includere definitivamente le interazioni basate sull’elenco email? Sono memorizzate in qualche modo diverso? Ero preoccupato che fossero state escluse dai rapporti di attività standard per qualche motivo oscuro ma insormontabile.

No, dovresti copiare/incollare manualmente le sezioni della query.

La tabella posts nel database include un campo booleano via_email, quindi sì, puoi scoprire quali post sono stati creati via email

Tuttavia, Discourse non include alcun tracciatore nelle email che invia, quindi non ci sarà modo di determinare se una notifica via email è stata ‘letta’ o meno.

La funzionalità di reporting sull’attività degli utenti integrata viene gestita tramite query SQL che posso copiare da qualche parte e modificare? In modo da non dover perdere settimane a reinventare la ruota.

Stai consultando il rapporto “utenti attivi giornalieri”? Viene generato con questa logica, che utilizza ActiveRecord (quindi nessun SQL grezzo). Tuttavia, la logica potrebbe essere un punto di partenza utile.

Il mio obiettivo è generare un report di attività per utente in formato CSV, relativo a un intervallo di date specifico. L’obiettivo finale è assegnare a ciascun utente un punteggio di attività per l’anno (o per un altro periodo) basato sui messaggi ricevuti/letti online o via email, sui messaggi pubblicati online o via email, con un punteggio più elevato per i messaggi che ricevono ‘like’. Il report che mi chiedevo di utilizzare come base è il primo che appare cliccando su Amministratore/Utenti, poiché già svolge gran parte di ciò che desidero.

image868×321 44.3 KB

La logica della directory degli utenti è disponibile qui: discourse/app/models/directory_item.rb at 1e66e4602f408d7b2d1105b6c228542dce7f4b38 · discourse/discourse · GitHub

Hmm, non sembra un compito banale da replicare tramite SQL (almeno non per me), dato che dovrò imparare abbastanza SQL per farlo da zero allo stesso tempo, se non c’è nulla di abbastanza simile che possa copiare direttamente.
L’ultima volta che ho fatto programmazione è stata molti anni fa alle superiori, quando il BASIC non era ancora così imbarazzante da ammettere.

Sì, penso che per implementarlo serva una conoscenza ragionevole di SQL. Se hai un budget per il lavoro, potresti riuscire a trovare qualcuno che possa aiutarti nel Marketplace.

Grazie! Non ho un budget (il forum genera pochi ricavi dalle donazioni, oltre ai costi base di hosting), ma sembra che dovrò procedere in quel modo comunque.

@Paul_King

Questa query potrebbe essere d’aiuto.

https://meta.discourse.org/t/daily-weekly-or-total-stats-by-user-over-a-specified-time-range/275167u=grayden_shand

Molte grazie! L’ho provato, ma ricevo un errore di sintassi

PG::SyntaxError: ERRORE: errore di sintassi in corrispondenza di “WITH”
LINEA 13: WITH date_range AS (

(scusa, ti ho inviato un messaggio privato tramite quel thread prima di accorgermi che tu e l’autore originale di quel thread siete la stessa persona!)

Ti dispiace se chiedo quali valori stai utilizzando per le variabili?

Ho appena estratto la query e l’ho eseguita su un sito di test con i seguenti valori:

• start_date: 2021-07-01
• end_date: 2021-07-30
• coverage: all

Ciao, ho provato alcune opzioni, ma tutte mi hanno restituito quel risultato, ad esempio

image1428×491 21.7 KB

Mi dispiace, non riesco a riprodurre l’errore.

Puoi incollare qui la query così come la hai?

SELECT 1-- copertura: 'settimana', 'tutto' o 'data'
-- [parametri]
-- date :start_date = 2019-08-27
-- date :end_date = 2019-09-30
-- text :copertura = settimana

WITH date_range AS (
SELECT date_trunc('day', dd):: date AS "data", EXTRACT(week from date_trunc('day', dd):: date) AS "settimana"
FROM generate_series
        ( :start_date::timestamp 
        , :end_date::timestamp
        , '1 day'::interval) dd
), likes_given AS (
SELECT u.id, dr.data, dr.settimana, count(pa.*) AS "like"
FROM date_range dr
FULL JOIN users u ON (1=1)
LEFT JOIN post_actions pa ON (pa.created_at:: date = dr.data and post_action_type_id=2 AND user_id = u.id)
GROUP BY dr.data, dr.settimana, u.id
ORDER BY u.id, dr.data
), posts_summary AS (SELECT u.id, u.username, u.created_at, dr.*, count(p.id) - count(t.id) AS risposte, count(t.id) AS argomenti, COALESCE(sum(p.like_count),0) AS like_riceuti
from date_range dr
FULL OUTER JOIN users u ON (1=1)
LEFT JOIN posts p ON (p.user_id = u.id AND p.created_at::date=dr.data AND p.deleted_at IS NULL)
LEFT JOIN topics t ON (t.user_id = u.id AND t.created_at::date = dr.data AND p.topic_id = t.id AND t.deleted_at IS NULL)
GROUP BY u.id, dr.data, dr.settimana
ORDER BY u.id, dr.data), 
visits AS (SELECT u.id, dr.*, COALESCE(sum(posts_read),0) AS post_letti, COALESCE(sum(time_read),0) AS tempo_lettura, COUNT(uv.*) AS visite
FROM date_range dr
FULL OUTER JOIN users u ON (1=1)
LEFT JOIN user_visits uv ON (uv.user_id = u.id AND visited_at = dr.data)
GROUP BY u.id, dr.data, dr.settimana
ORDER BY u.id, dr.data
)

SELECT ps.id, ps.username, ps.created_at, CASE
WHEN :copertura::text = 'settimana' THEN ps.settimana::text
WHEN :copertura::text = 'tutto' THEN '-1'
ELSE ps.data::text
END  AS periodo, sum(ps.risposte) AS risposte, sum(ps.argomenti) AS argomenti, sum(ps.like_riceuti) AS like_riceuti, sum(lg.like) AS like_dati, COALESCE(sum(post_letti),0) AS post_letti, COALESCE(sum(tempo_lettura),0) AS tempo_lettura, SUM(visite) AS visite
FROM posts_summary ps
LEFT JOIN likes_given lg ON (ps.data = lg.data AND ps.id = lg.id)
LEFT JOIN visits v ON (v.id = ps.id AND v.data = ps.data)
GROUP BY ps.id, ps.username, ps.created_at, CASE 
WHEN :copertura::text = 'settimana' THEN ps.settimana::text
WHEN :copertura::text = 'tutto' THEN '-1'
ELSE ps.data::text
END  
ORDER BY ps.id, CASE
WHEN :copertura::text = 'settimana' THEN ps.settimana::text
WHEN :copertura::text = 'tutto' THEN '-1'
ELSE ps.data::text
END

Ah, capisco.

Il SELECT 1 all’inizio non fa parte della query ed è la causa del tuo problema. È un segnaposto che appare quando crei una nuova query nell’Esploratore di Dati. Rimuovilo e dovrebbe funzionare.

-- coverage: 'week', 'all', o 'date'
-- [params]
-- date :start_date = 2019-08-27
-- date :end_date = 2019-09-30
-- text :coverage = week

WITH date_range AS (
SELECT date_trunc('day', dd):: date AS "date", EXTRACT(week from date_trunc('day', dd):: date) AS "week"
FROM generate_series
        ( :start_date::timestamp 
        , :end_date::timestamp
        , '1 day'::interval) dd
), likes_given AS (
SELECT u.id, dr.date, dr.week, count(pa.*) AS "likes"
FROM date_range dr
FULL JOIN users u ON (1=1)
LEFT JOIN post_actions pa ON (pa.created_at:: date = dr.date and post_action_type_id=2 AND user_id = u.id)
GROUP BY dr.date, dr.week, u.id
ORDER BY u.id, dr.date
), posts_summary AS (SELECT u.id, u.username, u.created_at, dr.*, count(p.id) - count(t.id) AS replies, count(t.id) AS topics, COALESCE(sum(p.like_count),0) AS likes_received
from date_range dr
FULL OUTER JOIN users u ON (1=1)
LEFT JOIN posts p ON (p.user_id = u.id AND p.created_at::date=dr.date AND p.deleted_at IS NULL)
LEFT JOIN topics t ON (t.user_id = u.id AND t.created_at::date = dr.date AND p.topic_id = t.id AND t.deleted_at IS NULL)
GROUP BY u.id, dr.date, dr.week
ORDER BY u.id, dr.date), 
visits AS (SELECT u.id, dr.*, COALESCE(sum(posts_read),0) AS posts_read, COALESCE(sum(time_read),0) AS time_read, COUNT(uv.*) AS visits
FROM date_range dr
FULL OUTER JOIN users u ON (1=1)
LEFT JOIN user_visits uv ON (uv.user_id = u.id AND visited_at = dr.date)
GROUP BY u.id, dr.date, dr.week
ORDER BY u.id, dr.date
)

SELECT ps.id, ps.username, ps.created_at, CASE
WHEN :coverage::text = 'week' THEN ps.week::text
WHEN :coverage::text = 'all' THEN '-1'
ELSE ps.date::text
END  AS period, sum(ps.replies) AS replies, sum(ps.topics) AS topics, sum(ps.likes_received) AS likes_received, sum(lg.likes) AS likes_given, COALESCE(sum(posts_read),0) AS posts_read, COALESCE(sum(time_read),0) AS time_read, SUM(visits) AS visits
FROM posts_summary ps
LEFT JOIN likes_given lg ON (ps.date = lg.date AND ps.id = lg.id)
LEFT JOIN visits v ON (v.id = ps.id AND v.date = ps.date)
GROUP BY ps.id, ps.username, ps.created_at, CASE 
WHEN :coverage::text = 'week' THEN ps.week::text
WHEN :coverage::text = 'all' THEN '-1'
ELSE ps.date::text
END  
ORDER BY ps.id, CASE
WHEN :coverage::text = 'week' THEN ps.week::text
WHEN :coverage::text = 'all' THEN '-1'
ELSE ps.date::text
END

Grazie @Grayden_Shand

L’errore è scomparso.

Se posso chiederti ancora qualche informazione, i conteggi generati da questa query includono i post inviati via email e le risposte via email per gli utenti in modalità elenco email? Se non è così, come posso includerli?

Inoltre, come posso includere il valore di un campo personalizzato del profilo utente accanto al nome dell’utente?

Hai qualche consiglio su come identificare il nome del campo coinvolto e implementare questa funzionalità?

Sì, dovrebbe. Come ha menzionato David, la tabella posts contiene un campo booleano via_email. La query attuale ignora questo campo e conta tutti i post, indipendentemente dal fatto che siano stati inviati via email o meno.

Esiste una tabella chiamata user_custom_fields. Dovrai eseguire un JOIN su questa tabella per includere un campo personalizzato specifico.

Probabilmente lo farei nella sotto-query post_summary.

e.g.

...
), posts_summary AS (
SELECT u.id, u.username, u.created_at, dr.*, count(p.id) - count(t.id) AS replies, count(t.id) AS topics, COALESCE(sum(p.like_count),0) AS likes_received, cf.value as LABEL_FOR_CUSTOM_FIELD
from date_range dr
FULL OUTER JOIN users u ON (1=1)
LEFT JOIN posts p ON (p.user_id = u.id AND p.created_at::date=dr.date AND p.deleted_at IS NULL)
LEFT JOIN topics t ON (t.user_id = u.id AND t.created_at::date = dr.date AND p.topic_id = t.id AND t.deleted_at IS NULL)
LEFT JOIN user_custom_fields cf ON (u.id == cf.user_id and cf.name == "NOME_DEL_TUO_CAMPO_PERSONALIZZATO") 
GROUP BY u.id, dr.date, dr.week
ORDER BY u.id, dr.date
)
...

Ho aggiunto una colonna alla clausola SELECT e una nuova clausola JOIN per la tabella user_custom_fields.

Nota che dovrai sostituire "NOME_DEL_TUO_CAMPO_PERSONALIZZATO" e LABEL_FOR_CUSTOM_FIELD.

Dovrai anche aggiornare le colonne che selezioni nella query finale.

...
SELECT ps.id, ps.username, ps.created_at, ps.LABEL_FOR_CUSTOM_FIELD, CASE
WHEN :coverage::text = 'week' THEN ps.week::text
WHEN :coverage::text = 'all' THEN '-1'
ELSE ps.date::text
END  AS period, sum(ps.replies) AS replies, sum(ps.topics) AS topics, sum(ps.likes_received) AS likes_received, sum(lg.likes) AS likes_given, COALESCE(sum(posts_read),0) AS posts_read, COALESCE(sum(time_read),0) AS time_read, SUM(visits) AS visits
FROM posts_summary ps
..

Probabilmente è così che lo affronterei.

Buona fortuna!