Ciao! Stiamo utilizzando la versione ospitata su cloud di Discourse e gestiamo la nostra piattaforma da circa 1,5 anni. Vorremmo estrarre alcune metriche storiche sull’adozione e sull’utilizzo della piattaforma. Stiamo già esaminando il report ‘Iscrizioni’ disponibile nella scheda Report dell’amministratore, ma vorremmo anche essere in grado di mostrare il numero totale di utenti attivi ogni mese. Qualcuno ha qualche suggerimento su come potrei fare? Non riesco a trovare nulla di appropriato in Report o Data Explorer.
Questa query dell’esploratore di dati restituirà le visite mensili uniche degli utenti. È quello che desideri?
--[params]
-- date :start_date = 2021-01-01
-- date :end_date = 2021-12-31
-- string_list :group_name = staff,admins
WITH not_allowed_groups AS (
SELECT gu.user_id FROM group_users gu
INNER JOIN groups g ON (g.id = gu.group_id)
WHERE g.name IN (:group_name)
)
SELECT
to_char(date_trunc('month', uv.visited_at)::DATE,'YYYY-MM') AS "month",
date_trunc('month', uv.visited_at)::DATE AS date_user_visits,
COUNT(distinct uv.user_id) AS qtt_user_visits
FROM user_visits uv
WHERE
uv.user_id NOT IN (SELECT user_id FROM not_allowed_groups)
AND uv.visited_at::DATE BETWEEN :start_date AND :end_date
GROUP BY date_trunc('month', uv.visited_at)::DATE
ORDER BY date_trunc('month', uv.visited_at)::DATE
Grazie mille, ma non esattamente… Vorremmo il numero di utenti attivi (registrati e non disattivati o eliminati) alla fine di ogni mese di calendario, indipendentemente dal fatto che abbiano visitato la piattaforma quel mese o meno.
Stiamo guardando il grafico delle iscrizioni, ma la somma delle iscrizioni durante un dato mese non riflette la nostra base utenti perché ogni mese abbiamo anche disattivazioni automatiche dovute all’inattività. Mi chiedo se l’unico modo sia accedere alle esportazioni dell’elenco utenti retrospettive per date specifiche? Ma poiché siamo ospitati su Cloud, non so se abbiamo accesso a ciò. Dovremmo richiederlo a Discourse?
Sono Discourse. 
Puoi chiedere qui o inviarci un’e-mail direttamente a team@discourse.org. Qualcuno con una migliore conoscenza di SQL sarà in grado di aiutarti.
4 Mi Piace
Non me ne ero reso conto dato che sono nuovo in questo forum, grazie! Per caso hai backup storici archiviati di siti ospitati su cloud che potresti fornire su richiesta?
1 Mi Piace
Conserviamo i backup per garantire la continuità operativa e possono essere ripristinati in caso di emergenza, ma non per altri motivi.
1 Mi Piace
Ok, ho un’altra idea. Ho chiesto al nostro assistente AI.
Query SQL per utenti attivi per mese AI
HAWK:
Scrivimi una query di Data Explorer che restituisca un elenco di account utente ancora attivi e registrati all’interno di un dato mese
DiscourseHelper:
Ricerca
Ricerca per: Query di Data Explorer di Discourse per trovare utenti attivi registrati in un mese specifico
Lettura
Lettura: Utenti attivi per mesi specifici
Per creare una query di Data Explorer che restituisca un elenco di account utente ancora attivi e registrati all’interno di un mese specifico, è possibile utilizzare la seguente query SQL. Questa query presuppone che un utente attivo sia qualcuno che ha effettuato un post o una risposta entro un determinato periodo di tempo.
--[params]
-- int :month = 5
-- int :year = 2023
SELECT u.id, u.username, u.created_at, u.last_seen_at
FROM users u
WHERE u.active = TRUE
AND EXTRACT(MONTH FROM u.created_at) = :month
AND EXTRACT(YEAR FROM u.created_at) = :year
AND u.last_seen_at IS NOT NULL -- Si presume che attivo significhi che hanno acceduto di recente al sito
ORDER BY u.created_at
Spiegazione della query:
:monthe:yearsono parametri che è possibile impostare per filtrare il mese e l’anno specifici.u.active = TRUEgarantisce che l’account utente sia ancora attivo.EXTRACT(MONTH FROM u.created_at) = :montheEXTRACT(YEAR FROM u.created_at) = :yearfiltrano la data di registrazione al mese e all’anno specificati.u.last_seen_at IS NOT NULLconsidera l’utente attivo se ha effettuato l’accesso in qualsiasi momento.- Il risultato fornisce ID utente, nome utente, data di registrazione e data dell’ultimo accesso.
Sentiti libero di regolare i parametri e i campi in base alle tue esigenze. Fammi sapere se hai bisogno di ulteriore assistenza!
Sentiti libero di fornire ulteriore contesto se mi manca qualcosa.
3 Mi Piace
Questa è un’ottima spiegazione della query, grazie! Sfortunatamente, non sono sicuro che sia quello che stiamo cercando.
Ad esempio, se imposto i parametri 01 e 2024, sto guardando gennaio di quest’anno.
Capisco che l’output includerà tutti gli utenti che si sono iscritti a gennaio e che sono ancora attivi oggi. È corretto?
Ma quello che vogliamo è il numero di persone che hanno avuto u.active=TRUE ogni mese dal lancio (non adesso). Quindi possiamo tracciare e mostrare come la nostra base di utenti è cambiata di mese in mese. Alla fine di ogni mese, per noi sarebbe:
Utenti attivi = Valore del mese precedente + nuove iscrizioni - nuove eliminazioni - nuove disattivazioni
Non vedo nulla riguardo alle disattivazioni o alle date legate alla variabile active nel Data Explorer. Il percorso più probabile, quindi, potrebbe essere quello di utilizzare i log delle azioni dello staff e analizzare per data, MA non credo che la funzione di esportazione funzioni per noi. Quando esportiamo i risultati del log delle azioni dello staff, stranamente otteniamo un foglio di calcolo con il contenuto di diversi post, non una tabella di azioni dello staff. Se poteste aiutarci con questo, forse sarebbe una soluzione alternativa! Siete a conoscenza di altri che hanno lo stesso problema?
Una domanda interessante.
Ho prima dato un’occhiata all’esempio qui. Ma questo ignora gli utenti eliminati. Ottieni solo il numero di utenti che si sono registrati in quel momento e lo sono ancora, non quelli che sono stati eliminati nel frattempo.
La mia idea era quindi quella di prendere l’ID dell’utente che si è registrato per ultimo nel mese. Questo è il numero massimo possibile di utenti in quel momento. Il numero di utenti eliminati può quindi essere sottratto da questo. Tuttavia, gli account bot (come forum-helper) hanno un ID negativo, ma vengono conteggiati se vengono eliminati. (Ma questa è probabilmente una deviazione minore). La mia query era:
-- [params]
-- date :start_date
-- date :end_date
WITH month_dates AS (
-- Generate end-of-month dates between the start and end date
SELECT DATE_TRUNC('month', generate_series)::date + INTERVAL '1 month' - INTERVAL '1 day' AS month_end
FROM generate_series(:start_date::date, :end_date::date, '1 month'::interval)
),
recent_user AS (
-- For each end-of-month date, find the most recent user created before that date
SELECT md.month_end,
(SELECT id
FROM users u
WHERE u.created_at < md.month_end
ORDER BY u.created_at DESC
LIMIT 1) AS user_max_id
FROM month_dates md
),
cumulative_deletion_count AS (
-- Calculate the cumulative deletions up to each end-of-month date
SELECT md.month_end,
(SELECT COUNT(*)
FROM user_histories uh
WHERE uh.action = 1 AND uh.updated_at < md.month_end) AS deletions_count
FROM month_dates md
)
SELECT
md.month_end,
ru.user_max_id,
cdc.deletions_count,
ru.user_max_id - cdc.deletions_count AS number_of_users
FROM
month_dates md
LEFT JOIN recent_user ru ON md.month_end = ru.month_end
LEFT JOIN cumulative_deletion_count cdc ON md.month_end = cdc.month_end
ORDER BY md.month_end
Ma quello che non tiene conto è l’attivazione/(dis)attivazione, che sono anche memorizzate nella tabella user_histories. Ma forse ti aiuterà come punto di partenza.
5 Mi Piace