Anforderung für komplexen Benutzeraktivitätsbericht

Gemeinschaft Daten & Berichterstattung
1

Ich suche nach einer Möglichkeit, Discourse so einzurichten, dass es Aktivitätsberichte generiert, die einen Überblick über alle Benutzeraktivitäten zwischen zwei beliebigen, benutzerdefinierten Daten pro Jahr bieten (die aktuelle integrierte Funktionalität scheint nur voreingestellte Bereiche zu haben, und zwar ausschließlich in Bezug auf das heutige Datum), und die Ergebnisse in eine Excel-kompatible Datei zu exportieren (derzeit scheint dies nur für Aktivitätsberichte einzelner Benutzer möglich zu sein).

  • Ich möchte zudem Beiträge einbeziehen, die per E-Mail gesendet und per E-Mail beantwortet wurden (ohne eine doppelte Zählung, falls dieselben Beiträge online im Forum gelesen oder gepostet wurden). Aktuelle Berichtsfunktionen scheinen Beiträge per E-Mail auszuschließen.

  • Ich möchte diese Ergebnisse nach einem bestimmten benutzerdefinierten Benutzerprofilfeld (eine eindeutige Mitgliedsnummer) filtern.

  • Ich möchte Ergebnisse für Benutzer mit Mitgliedsnummern ausschließen, die nicht in einem festgelegten numerischen Bereich liegen.

  • Idealerweise möchte ich auch die Möglichkeit haben, pro Benutzer einen Aktivitätspunktestand für den Zeitraum zu generieren, der gewichtet ist nach gelesenen Beiträgen, erstellten Beiträgen und Beiträgen, die Likes erhalten haben.

  • Die Gewichtungsfaktoren (Multiplikatoren) für jede dieser Aktivitätsvariablen sollten anpassbar und vom Administrator einstellbar sein. Die Ergebnisse sollen auf den nächsten 5 Punkte abgerundet werden und bei einer vom Administrator festgelegten maximalen Punktzahl abgeschnitten werden.

  • Als „Nice-to-have" wäre eine Aufschlüsselung nach Themenkategorien oder Tags pro Benutzer wünschenswert.

  • Ideal wäre es, wenn dieser Bericht automatisch generiert und mir zu einem festgelegten Zeitpunkt jedes Jahr per E-Mail zugesendet würde (das wäre dann das Sahnehäubchen).

Wie machbar ist all das?

Wäre dafür ein neues benutzerdefiniertes Plugin erforderlich, oder ist es über eine ausgeklügelte SQL-Abfrage im aktuellen Data Explorer-Plugin möglich?

Oder wäre der klügere Weg, eine relativ einfache „Export-alle"-Option zu suchen und den Rest in Excel zu bearbeiten?

2 „Gefällt mir“
2

Der Data Explorer wäre hier wahrscheinlich der beste Ansatz. Möglicherweise müssen Sie für jede Aufgabe unterschiedliche Abfragen erstellen (z. B. wäre die Aufschlüsselung pro Benutzer eine andere Abfrage).

Das Einzige, was der Data Explorer nicht leisten kann, ist der Punkt „automatisch generiert und an mich per E-Mail gesendet“. Falls dies eine Anforderung ist, könnten Sie dies potenziell umsetzen, indem Sie die Data-Explorer-API von einem anderen System aus aufrufen.

3 „Gefällt mir“
3

Danke, David.
Es sieht so aus, als müsste ich mich mit SQL-Abfragen vertraut machen.

Kann eine Abfrage gefilterte Ausgaben einer anderen Abfrage als Eingabe verwenden? Kann auch die auf E-Mail-Listen basierende Interaktion definitiv einbezogen werden? Wird diese irgendwie anders gespeichert? Ich hatte befürchtet, dass sie aus irgendeinem obskuren, aber unüberwindlichen Grund aus den Standardaktivitätsberichten ausgeschlossen wurde.

4

Nein, Sie müssten Abschnitte der Abfrage kopieren/einfügen.

Die Tabelle posts in der Datenbank enthält ein boolesches Feld via_email, sodass Sie ermitteln können, welche Beiträge per E-Mail erstellt wurden :+1:

Allerdings enthält Discourse keine Tracker in den E-Mails, die es sendet, sodass es keine Möglichkeit gibt, festzustellen, ob eine E-Mail-Benachrichtigung ‘gelesen’ wurde oder nicht.

5

Ist die ‘fest integrierte’ Benutzeraktivitäts-Berichterstattung über SQL-Abfragen realisiert, die ich überallher kopieren und anpassen kann? – damit ich nicht wochenlang das Rad neu erfinde?

6

Schauen Sie sich den Bericht über die „täglich aktiven Benutzer

7

Ich plane einen Aktivitätsbericht pro Benutzer im CSV-Format für einen bestimmten Zeitraum. Das Ziel ist es, jedem Benutzer eine Aktivitätspunktzahl für das Jahr (oder einen anderen Zeitraum) zu geben, basierend auf erhaltenen/gelesenen Nachrichten online oder per E-Mail, gesendeten Nachrichten online oder per E-Mail, wobei Nachrichten mit Likes mehr Punkte erhalten. Als Basis wollte ich den Bericht verwenden, den man beim Klicken auf Admin/Benutzer als Erstes sieht, da dieser bereits vieles von dem bietet, was ich brauche.

image
image868×321 44.3 KB

8

Die Logik des Benutzerverzeichnisses finden Sie hier: discourse/app/models/directory_item.rb at 1e66e4602f408d7b2d1105b6c228542dce7f4b38 · discourse/discourse · GitHub

9

Hmm, das sieht nicht nach einer trivialen Aufgabe aus, die sich per SQL nachzubauen ist (zumindest nicht für mich), da ich gleichzeitig genug SQL lernen müsste, um es von Grund auf selbst zu lösen, falls es nichts Ähnliches gibt, das ich direkt kopieren kann.
Das letzte Mal, dass ich programmiert habe, war vor vielen Jahren in der Schule, als BASIC noch nicht so peinlich war, es zuzugeben.

1 „Gefällt mir“
10

Ja, ich denke, dafür sind fundierte SQL-Kenntnisse erforderlich. Wenn Sie ein Budget für die Umsetzung haben, können Sie im Marketplace vielleicht jemanden finden, der Ihnen hilft.

11

Cheers, ich habe kein Budget (das Forum generiert kaum Spendeneinnahmen über die grundlegenden Hosting-Kosten hinaus), aber es sieht so aus, als müsste ich trotzdem diesen Weg gehen.

1 „Gefällt mir“
12

@Paul_King

Diese Abfrage könnte hilfreich sein.

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

13

Vielen Dank! Ich habe es ausprobiert, erhalte jedoch einen Syntaxfehler.

PG::SyntaxError: ERROR: syntax error at or near “WITH”
LINE 13: WITH date_range AS (

(Entschuldigung, ich habe dir vorher eine PM über diesen Thread geschickt, bevor mir klar wurde, dass du und der ursprüngliche Autor dort ein und dieselbe Person seid!)

1 „Gefällt mir“
14

Ist es dir recht, wenn ich frage, welche Werte du für die Variablen verwendest?

Ich habe die Abfrage gerade abgerufen und gegen eine Testseite mit folgenden Werten ausgeführt:

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

Hallo, ich habe es mit ein paar versucht, aber alle haben mir dieses Ergebnis geliefert – z. B.

image
image1428×491 21.7 KB

16

Entschuldigung, ich kann den Fehler nicht nachvollziehen.

Kannst du die Abfrage hier so einfügen, wie du sie hast?

17 
SELECT 1-- Abdeckung: 'week', 'all' oder 'date'
-- [Parameter]
-- 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
  ) AS 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
1 „Gefällt mir“
18

Ah, verstehe.

Das SELECT 1 am Anfang ist nicht Teil der Abfrage und ist die Ursache Ihres Problems. Es handelt sich um einen Platzhalter, der erstellt wird, wenn Sie eine neue Abfrage im Data Explorer anlegen. Entfernen Sie diesen, und es sollte funktionieren.

-- coverage: 'week', 'all', or '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
19

Danke @Grayden_Shand

Der Fehler ist verschwunden.

Darf ich dich noch ein wenig um Rat fragen: Beinhalten die von dieser Abfrage generierten Zählungen auch Beiträge per E-Mail und Antworten per E-Mail für Benutzer im E-Mail-Listenmodus? Falls nicht, wie kann ich diese einbeziehen?

Außerdem: Wie kann ich den Wert eines benutzerdefinierten Profilfelds neben dem Benutzernamen anzeigen?

Gib es irgendwelche Tipps, wie man den betreffenden Feldnamen identifiziert und dies umsetzt?

20

Ja, das sollte er. Wie David bereits erwähnt hat, enthält die Tabelle posts ein boolesches Feld via_email. Die aktuelle Abfrage ignoriert dieses Feld und zählt alle Beiträge, unabhängig davon, ob sie per E-Mail oder auf andere Weise veröffentlicht wurden.

Es gibt eine Tabelle namens user_custom_fields. Um ein bestimmtes benutzerdefiniertes Feld einzubeziehen, müssten Sie einen JOIN auf diese Tabelle durchführen.

Ich würde dies wahrscheinlich in der Sub-Abfrage post_summary erledigen.

Zum Beispiel:

...
), 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 == "IHR BENUTZERDEFINIERTES FELDNAME") 
GROUP BY u.id, dr.date, dr.week
ORDER BY u.id, dr.date
)
...

Ich habe eine Spalte in der SELECT-Klausel hinzugefügt und eine neue JOIN-Klausel für die Tabelle user_custom_fields eingefügt.

Beachten Sie, dass Sie "IHR BENUTZERDEFINIERTES FELDNAME" und LABEL_FOR_CUSTOM_FIELD ersetzen müssen.

Anschließend müssen Sie auch die Spalten aktualisieren, die Sie in der endgültigen Abfrage auswählen.

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

Das wäre wahrscheinlich mein Ansatz.

Viel Erfolg!