Benutzerseitige Metriken

[srinivas.chilukuri]

@SaraDev
Kannst du die SQL-Abfrage für die Metriken bereitstellen, die unter https://meta.discourse.org/u?cards=no&order=post_count verfügbar sind?
Siehe Bild unten

Metriken

• Likes erhalten
• Likes vergeben
• Angesehene Themen
• Gelesene Beiträge
• Besuchte Tage
• Lösungen
• Prost

---

Wir nutzen Kategorie-Moderatoren, daher modifizieren wir die Moderator-Abfrage für jede gegebene Gruppe.

[SaraDev (Sara Devlaeminck)]

Hallo @srinivas.chilukuri,

Die Statistiken der /u-Benutzerseite können über den Data Explorer mit der Tabelle directory_items abgerufen werden.

Benutzerverzeichnis-Seitenmetriken

-- [params]
-- int :period
-- Periodenoptionen:
-- 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

Anstelle der üblichen Parameter start_date und end_date können Daten aus dieser Tabelle mithilfe des Feldes period_type gefiltert werden, wobei die folgenden Werte den verschiedenen Zeiträumen entsprechen, die auf der Verzeichnisseite verfügbar sind:

• 1: gesamte Zeit
• 2: jährlich
• 3: monatlich
• 4: wöchentlich
• 5: täglich
• 6: vierteljährlich

Beispielergebnisse für diesen Bericht würden wie folgt aussehen:

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
Ich benötige das Start- und Enddatum. Gibt es eine Problemumgehung, um die angegebenen Metriken mit Start- und Enddatum zu erhalten?

• Benutzer
• erhaltene Likes
• gegebene Likes
• angesehene Themen
• Themenanzahl
• Beitraganzahl
• besuchte Tage
• gelesene Beiträge
• Lösungen
• Beifall

Hinweis: Ich erhalte Metriken für eine kleine Teilmenge der gesamten Benutzer

[SaraDev (Sara Devlaeminck)]

@srinivas.chilukuri

Ich benötige das Start- und Enddatum. Gibt es eine Problemumgehung, um die angegebenen Metriken mit Start- und Enddatum zu erhalten?

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

Wenn Sie diese Metriken für Benutzer auf Ihrer Website anzeigen und nach bestimmten Start- und Enddaten filtern möchten, benötigen Sie eine Abfrage, die die Daten für jede Metrik in einem separaten CTE abruft und dann die Ergebnisse in einer endgültigen SELECT-Anweisung kombiniert.

So würde das aussehen:

Benutzerstatistiken

-- [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

Erklärung der Abfrage:

1. Parameter:
   • :start_date und :end_date sind Parameter, die den Datumsbereich für die abgefragten Daten definieren.
2. Common Table Expressions (CTEs):
   • likes_received: Zählt die Anzahl der von jedem Benutzer erhaltenen Likes (action_type = 2) im angegebenen Datumsbereich.
   • likes_given: Zählt die Anzahl der von jedem Benutzer vergebenen Likes (action_type = 1) im angegebenen Datumsbereich.
   • user_metrics: Aggregiert Benutzerstatistiken wie angesehene Themen, gelesene Beiträge und besuchte Tage für Benutzer, die ihren ersten Beitrag im angegebenen Datumsbereich erstellt haben.
   • solutions: Zählt die Anzahl der von jedem Benutzer bereitgestellten Lösungen (action_type = 15) im angegebenen Datumsbereich.
   • cheers: Summiert die Gamification-Scores für jeden Benutzer im angegebenen Datumsbereich.
3. Finale Auswahl:
   • Die Hauptabfrage wählt die Benutzerinteraktionsmetriken für jeden Benutzer aus, einschließlich erhaltener Likes, gegebener Likes, angesehene Themen, gelesene Beiträge, besuchte Tage, bereitgestellte Lösungen und erhaltene Cheers.
   • Sie verwendet LEFT JOIN, um sicherzustellen, dass alle Benutzer enthalten sind, auch wenn sie in einigen Kategorien keine Aktivität haben, und füllt fehlende Werte mit Nullen mithilfe von COALESCE.

Beispielergebnisse

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

Benutzerstatistiken

Wir haben die im obigen Beitrag bereitgestellte Abfrage verwendet und haben die folgenden Fragen:

1. Ist user_metrics aus der Tabelle user_stats die richtige Quelle für diese Informationen? Da user_stats eine statische Tabelle ist, die die Metriken eines Benutzers seit seinem Beitritt zu Discourse zusammenfasst, ist sie möglicherweise nicht ideal für die Filterung von Metriken innerhalb eines bestimmten Zeitraums (z. B. von einem Startdatum bis zu einem Enddatum).
2. Zeitreihenvergleich (T/S V/G)
   Für eine gegebene Gruppe von Benutzern haben wir die verfügbaren Zeitraumbereiche auf der Benutzerseite verglichen und erhebliche Abweichungen festgestellt.

Wesentliche Abweichungen:

• topics_entered
• posts_read_count
• days_visited

Könnten Sie bitte klären, ob es eine bessere Möglichkeit gibt, zeitgebundene Benutzerstatistiken abzurufen?

[Jagster (Jakke Lehtonen)]

PG::UndefinedColumn: FEHLER:  Spalte uv.topic_id existiert nicht
ZEILE 38:         COUNT(DISTINCT uv.topic_id) AS topics_viewed, -- Cou...

[SaraDev (Sara Devlaeminck)]

@srinivas.chilukuri

Ist user_metrics aus der Tabelle user_stats die richtige Quelle für diese Information? Da user_stats eine statische Tabelle ist, die die Metriken eines Benutzers seit seinem Beitritt zu Discourse zusammenfasst, ist sie möglicherweise nicht ideal für die Filterung von Metriken innerhalb eines bestimmten Zeitraums (z. B. von einem Startdatum bis zu einem Enddatum).

Sie haben Recht, dass die Tabelle user_stats eine statische Tabelle ist, die die lebenslangen Metriken eines Benutzers seit seinem Beitritt zu Discourse zusammenfasst.

Stattdessen würden wir für die Filterung von Metriken nach Datum wie posts_read_count und days_visited die Datenbanktabelle user_visits für posts verwenden. Wir würden auch die Tabelle topic_views verwenden, um die Metriken topics_entered nach Datum zu filtern.

@srinivas.chilukuri

Für eine gegebene Gruppe von Benutzern verglichen wir die verfügbaren Zeitraumsdaten auf der Benutzerseite und stellten erhebliche Diskrepanzen fest.

Die von Ihnen beobachteten Diskrepanzen ergeben sich aus der Verwendung der Tabelle user_stats anstelle anderer Tabellen wie user_visits und topic_views zum Filtern dieser Statistiken nach Datum.

Um dies zu beheben, können wir die Abfrage aktualisieren, um stattdessen diese Datenbanktabellen zu verwenden:

Hier ist eine aktualisierte Version der Abfrage:

Benutzerseitige Metriken

-- [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

Beachten Sie, dass bei dieser Methode die Daten posts_read in der Tabelle user_visits eine wichtige Unterscheidung aufweisen – sie zählen keine eigenen Beiträge eines Benutzers, während die Daten aus der Tabelle user_stats selbst verfasste Beiträge enthalten. Daher können Sie zwischen diesen beiden Statistiken in dieser Abfrage und auf der Benutzerseite immer noch einen Unterschied feststellen.