Metriche della pagina utente

[srinivas.chilukuri]

@SaraDev
puoi fornire la query SQL per le metriche disponibili su https://meta.discourse.org/u?cards=no&order=post_count
fare riferimento all’immagine sottostante

metriche

• Mi piace ricevuti
• Mi piace dati
• Discussioni visualizzate
• Post letti
• Giorni visitati
• Soluzioni
• Saluti

---

stiamo utilizzando i moderatori di categoria, quindi stiamo modificando la query del moderatore per qualsiasi gruppo dato

[SaraDev (Sara Devlaeminck)]

Ciao @srinivas.chilukuri,

Le statistiche della pagina utente /u possono essere recuperate tramite il Data Explorer utilizzando la tabella directory_items.

Metriche della pagina della directory utente

-- [params]
-- int :period
-- Opzioni periodo:
-- 1. all
-- 2. yearly
-- 3. monthly
-- 4. weekly
-- 5. daily
-- 6. quarterly

SELECT
    di.user_id,
    COALESCE(di.likes_received, 0) AS likes_received,
    COALESCE(di.likes_given, 0) AS likes_given,
    COALESCE(di.topics_entered, 0) AS topics_viewed,
    COALESCE(di.topic_count, 0) AS topic_count,
    COALESCE(di.post_count, 0) AS post_count,
    COALESCE(di.days_visited, 0) AS days_visited,
    COALESCE(di.posts_read, 0) AS posts_read,
    COALESCE(di.solutions, 0) AS solutions,
    COALESCE(di.gamification_score, 0) AS cheers
FROM
    directory_items di
WHERE
    di.period_type = :period
ORDER BY
    di.user_id

Invece dei tipici parametri start_date e end_date, i dati di questa tabella possono essere filtrati utilizzando il campo period_type, dove i seguenti valori corrispondono ai diversi periodi di tempo disponibili nella pagina della directory:

• 1: tutto il tempo
• 2: annuale
• 3: mensile
• 4: settimanale
• 5: giornaliero
• 6: trimestrale

Esempi di risultati per questo report sarebbero:

user	likes_received	likes_given	topics_viewed	topic_count	post_count	days_visited	posts_read	solutions	cheers
Username1	4	17	250	69	116	480	217	10	844100
Username2	2	5	47	0	2	43	59	1	112305
Username3	0	4	2	0	0	2	7	0	3100
…	…	..	…	…	…	…	…	…	…

[srinivas.chilukuri]

@SaraDev
Richiedo la data di inizio e la data di fine. Esiste una soluzione alternativa per ottenere le metriche fornite con una data di inizio e una data di fine?

• utente
• mi_piace_ricevuti
• mi_piace_inviati
• argomenti_visualizzati
• numero_argomenti
• numero_post
• giorni_visitati
• post_letti
• soluzioni
• complimenti

Nota: sto ottenendo le metriche per un piccolo sottoinsieme di utenti totali

[SaraDev (Sara Devlaeminck)]

@srinivas.chilukuri

Richiedo la data di inizio e fine. Esiste una soluzione alternativa per ottenere le metriche fornite con data di inizio e fine

• user
• likes_received
• likes_given
• topics_viewed
• topic_count
• post_count
• days_visited
• posts_read
• solutions
• cheers

Se desideri visualizzare queste metriche per gli utenti sul tuo sito e filtrare per date di inizio e fine specifiche, avresti bisogno di una query che estragga i dati per ciascuna metrica in una CTE separata, e quindi combini i risultati in un’istruzione SELECT finale.

Ecco come apparirebbe:

Metriche utente

-- [params]
-- date :start_date = 2020-01-01
-- date :end_date = 2025-01-01

WITH likes_received AS (
    SELECT
        ua.user_id AS user_id,
        COUNT(*) AS likes_received
    FROM
        user_actions ua
    WHERE
        ua.action_type = 2
        AND ua.created_at BETWEEN :start_date AND :end_date
    GROUP BY
        ua.user_id
),
likes_given AS (
    SELECT
        ua.acting_user_id AS user_id,
        COUNT(*) AS likes_given
    FROM
        user_actions ua
    WHERE
        ua.action_type = 1
        AND ua.created_at BETWEEN :start_date AND :end_date
    GROUP BY
        ua.acting_user_id
),
user_metrics AS (
    SELECT
        us.user_id,
        SUM(us.topics_entered) AS topics_viewed,
        SUM(us.posts_read_count) AS posts_read,
        SUM(us.days_visited) AS days_visited
    FROM
        user_stats us
    WHERE
        us.first_post_created_at BETWEEN :start_date AND :end_date
    GROUP BY
        us.user_id
),
solutions AS (
    SELECT
        ua.acting_user_id AS user_id,
        COUNT(*) AS solutions
    FROM
        user_actions ua
    WHERE
        ua.action_type = 15
        AND ua.created_at BETWEEN :start_date AND :end_date
    GROUP BY
        ua.acting_user_id
),
cheers AS (
    SELECT
        gs.user_id,
        SUM(gs.score) AS cheers
    FROM
        gamification_scores gs
    WHERE
        gs.date BETWEEN :start_date AND :end_date
    GROUP BY
        gs.user_id
)

SELECT
    u.id AS user_id,
    COALESCE(lr.likes_received, 0) AS likes_received,
    COALESCE(lg.likes_given, 0) AS likes_given,
    COALESCE(um.topics_viewed, 0) AS topics_viewed,
    COALESCE(um.posts_read, 0) AS posts_read,
    COALESCE(um.days_visited, 0) AS days_visited,
    COALESCE(sol.solutions, 0) AS solutions,
    COALESCE(ch.cheers, 0) AS cheers
FROM
    users u
LEFT JOIN
    likes_received lr ON u.id = lr.user_id
LEFT JOIN
    likes_given lg ON u.id = lg.user_id
LEFT JOIN
    user_metrics um ON u.id = um.user_id
LEFT JOIN
    solutions sol ON u.id = sol.user_id
LEFT JOIN
    cheers ch ON u.id = ch.user_id
ORDER BY
    u.id

Spiegazione della query:

1. Parametri:
   • :start_date e :end_date sono parametri che definiscono l’intervallo di date per i dati richiesti.
2. Espressioni di Tabella Comuni (CTE):
   • likes_received: Conta il numero di “mi piace” ricevuti da ciascun utente (action_type = 2) nell’intervallo di date specificato.
   • likes_given: Conta il numero di “mi piace” dati da ciascun utente (action_type = 1) nell’intervallo di date specificato.
   • user_metrics: Aggrega le statistiche dell’utente come argomenti visualizzati, post letti e giorni visitati per gli utenti che hanno creato il loro primo post nell’intervallo di date specificato.
   • solutions: Conta il numero di soluzioni fornite da ciascun utente (action_type = 15) nell’intervallo di date specificato.
   • cheers: Somma i punteggi di gamification per ciascun utente nell’intervallo di date specificato.
3. Selezione Finale:
   • La query principale seleziona le metriche di coinvolgimento dell’utente per ciascun utente, inclusi “mi piace” ricevuti, “mi piace” dati, argomenti visualizzati, post letti, giorni visitati, soluzioni fornite e “applausi” ricevuti.
   • Utilizza LEFT JOIN per garantire che tutti gli utenti siano inclusi, anche se non hanno attività in alcune categorie, riempiendo con zeri usando COALESCE.

Risultati di esempio

user_id	likes_received	likes_given	topics_viewed	posts_read	days_visited	solutions	cheers
1	10	5	20	100	15	2	30
2	0	3	5	20	5	0	10

[srinivas.chilukuri]

@SaraDev

@SaraDev

Metriche utente

Abbiamo utilizzato la query fornita nel post precedente e abbiamo le seguenti domande:

1. La tabella user_metrics dalla tabella user_stats è la fonte corretta per queste informazioni? Dato che user_stats è una tabella statica che riassume le metriche di un utente da quando si è unito a Discourse, potrebbe non essere ideale per filtrare le metriche all’interno di un intervallo di tempo specifico (ad esempio, da una data di inizio a una data di fine).
2. Confronto delle serie temporali (T/S C/O)
   Per un dato insieme di utenti, abbiamo confrontato i dati del periodo di tempo disponibili nella pagina utente e abbiamo notato delle discrepanze significative.

Principali discrepanze:

• topics_entered
• posts_read_count
• days_visited

Potresti chiarire se esiste un modo migliore per recuperare le metriche utente limitate nel tempo?

[Jagster (Jakke Lehtonen)]

PG::UndefinedColumn: ERRORE:  la colonna uv.topic_id non esiste
LINEA 38:         COUNT(DISTINCT uv.topic_id) AS topics_viewed, -- Cou...

[SaraDev (Sara Devlaeminck)]

@srinivas.chilukuri

È user_metrics dalla tabella user_stats la fonte corretta per queste informazioni? Dato che user_stats è una tabella statica che riassume le metriche di un utente da quando si è unito a Discourse, potrebbe non essere ideale per filtrare le metriche all’interno di un intervallo di tempo specifico (ad esempio, da una data di inizio a una data di fine).

È corretto che la tabella user_stats sia una tabella statica che riassume le metriche cumulative di un utente da quando si è unito a Discourse.

Invece, per filtrare le metriche per data come posts_read_count e days_visited, dovremmo usare la tabella del database user_visits per posts. Dovremmo anche usare la tabella topic_views per filtrare le metriche topics_entered per data.

@srinivas.chilukuri

Per un dato insieme di utenti, abbiamo confrontato i dati disponibili per periodo di tempo nella pagina utente e abbiamo notato discrepanze significative.

Le discrepanze che hai osservato derivano dall’uso della tabella user_stats invece di altre tabelle come user_visits e topic_views per filtrare tali statistiche per data.

Per risolvere questo problema, possiamo aggiornare la query per utilizzare invece quelle tabelle del database:

Ecco una versione aggiornata della query:

Metriche della pagina utente

-- [params]
-- date :start_date = 2020-01-01
-- date :end_date = 2026-01-01

WITH likes_received AS (
    SELECT
        ua.user_id AS user_id,
        COUNT(*) AS likes_received
    FROM
        user_actions ua
    WHERE
        ua.action_type = 2
        AND ua.created_at BETWEEN :start_date AND :end_date
    GROUP BY
        ua.user_id
),
likes_given AS (
    SELECT
        ua.acting_user_id AS user_id,
        COUNT(*) AS likes_given
    FROM
        user_actions ua
    WHERE
        ua.action_type = 1
        AND ua.created_at BETWEEN :start_date AND :end_date
    GROUP BY
        ua.acting_user_id
),
user_metrics AS (
    SELECT
        tv.user_id,
        COUNT(DISTINCT tv.topic_id) AS topics_viewed
    FROM
        topic_views tv
    WHERE
        tv.viewed_at BETWEEN :start_date AND :end_date
    GROUP BY
        tv.user_id
),
days_and_posts AS (
    SELECT
        uv.user_id,
        COUNT(DISTINCT uv.visited_at) AS days_visited,
        SUM(uv.posts_read) AS posts_read
    FROM
        user_visits uv
    WHERE
        uv.visited_at BETWEEN :start_date AND :end_date
    GROUP BY
        uv.user_id
),
solutions AS (
    SELECT
        ua.acting_user_id AS user_id,
        COUNT(*) AS solutions
    FROM
        user_actions ua
    WHERE
        ua.action_type = 15
        AND ua.created_at BETWEEN :start_date AND :end_date
    GROUP BY
        ua.acting_user_id
),
cheers AS (
    SELECT
        gs.user_id,
        SUM(gs.score) AS cheers
    FROM
        gamification_scores gs
    WHERE
        gs.date BETWEEN :start_date AND :end_date
    GROUP BY
        gs.user_id
)

SELECT
    u.id AS user_id,
    COALESCE(lr.likes_received, 0) AS likes_received,
    COALESCE(lg.likes_given, 0) AS likes_given,
    COALESCE(um.topics_viewed, 0) AS topics_viewed,
    COALESCE(dp.days_visited, 0) AS days_visited,
    COALESCE(dp.posts_read, 0) AS posts_read,
    COALESCE(sol.solutions, 0) AS solutions,
    COALESCE(ch.cheers, 0) AS cheers
FROM
    users u
LEFT JOIN
    likes_received lr ON u.id = lr.user_id
LEFT JOIN
    likes_given lg ON u.id = lg.user_id
LEFT JOIN
    user_metrics um ON u.id = um.user_id
LEFT JOIN
    days_and_posts dp ON u.id = dp.user_id
LEFT JOIN
    solutions sol ON u.id = sol.user_id
LEFT JOIN
    cheers ch ON u.id = ch.user_id
ORDER BY
    u.id

Nota che con questo metodo, i dati posts_read nella tabella user_visits hanno una distinzione importante: non contano i post dell’utente stesso, mentre i dati della tabella user_stats includono i post scritti dall’utente, quindi potresti comunque riscontrare una differenza tra queste due statistiche in questa query e nella Pagina Utente.