Wie bekomme ich die Anzahl der Erwähnungen und die Anzahl der Mitglieder von Gruppen im letzten Jahr?

Gemeinschaft Daten & Berichterstattung
,
1

Hallo, ich bin Moderatorin im Hopscotch-Forum und würde gerne wissen, ob wir Unterstützung bei diesen Data-Explorer-Abfragen erhalten könnten:

  • Wie viele @-Erwähnungen Gruppen in den letzten 365 Tagen erhalten haben
    • sortiert von der niedrigsten zur höchsten Anzahl,
    • und nur, wenn die Gruppe seit mehr als 365 Tagen existiert (d. h. das Erstellungsdatum liegt mehr als 365 Tage zurück)
  • Wie viele @-Erwähnungen Gruppen in den letzten 365 Tagen erhalten haben
    • nur, wenn die Gruppe weniger als 5 Erwähnungen in den letzten 365 Tagen hat
    • sortiert von der niedrigsten zur höchsten Anzahl,
    • und nur, wenn die Gruppe seit mehr als 365 Tagen existiert (d. h. das Erstellungsdatum liegt mehr als 365 Tage zurück)
  • Wie viele Mitglieder Gruppen haben,
    • nur, wenn die Gruppe weniger als 10 Mitglieder hat
    • sortiert von der niedrigsten zur höchsten Anzahl

Das Ziel ist es, inaktive Gruppen zu identifizieren und zu entfernen. (Ich würde die Abfragen an einen Administrator weitergeben, damit diese erstellt werden können.)

Vielen Dank im Voraus,
Tracey

(Ich würde gerne den Tag data-explorer hinzufügen, aber es scheint, als könnte ich das im Moment nicht tun.)

1 „Gefällt mir“
2

Hallo,

Nur zur Klärung: Benötigen Sie drei Abfragen?

  1. Für alle Gruppen, die älter als 365 Tage sind, listen Sie die Anzahl der Erwähnungen in aufsteigender Reihenfolge auf.
  2. Für alle Gruppen, die älter als 365 Tage sind, listen Sie die Anzahl der Erwähnungen in aufsteigender Reihenfolge auf, aber nur, wenn sie weniger als fünf Erwähnungen hatten.
  3. Für alle Gruppen mit weniger als 10 Mitgliedern listen Sie die Anzahl der Mitglieder auf.

Falls ja, kann ich das für Sie erledigen – alle benötigten Informationen sind in den Tabellen groups, group_users und group_mentions gespeichert.

3

Soweit ich das beurteilen kann, verfolgt Discourse Erwähnungen nicht explizit, hält aber Links, die zu Themen hinzugefügt werden, fest. Eine Gruppenerwähnung in einem Beitrag wird als Wert der url in der Tabelle topic_links mit folgendem Format aufgezeichnet: "/groups/<gruppenname>". Die folgende Abfrage sollte dir Ergebnisse liefern, die deinem Wunsch sehr nahe kommen. Du musst Werte für die Parameter start_date und end_date angeben. Diese Daten müssen im Format jjjj-mm-tt vorliegen. Um beispielsweise Daten für das vergangene Jahr zu erhalten, gib 2020-01-01 als start_date und 2020-12-31 als end_date ein:

--[params]
-- date :start_date
-- date :end_date

WITH group_mentions AS (
SELECT
split_part(url, '/', 3) AS group_name
FROM topic_links tl
JOIN topics t ON t.id = tl.topic_id
WHERE internal = true
AND url LIKE '/groups/%'
AND t.deleted_at IS NULL
AND t.archetype = 'regular'
AND tl.created_at::date BETWEEN :start_date AND :end_date
)

SELECT
gm.group_name,
COUNT(gm.group_name) AS mention_count
FROM group_mentions gm
JOIN groups g ON g.name = gm.group_name
WHERE g.created_at::date <= :start_date
GROUP BY gm.group_name
ORDER BY mention_count DESC

Die Abfrage gibt nur Erwähnungen zurück, die zu regulären Beiträgen hinzugefügt wurden (sie schließt jedoch auch Erwähnungen ein, die zu Flüsternbeiträgen hinzugefügt wurden). Wenn du auch Erwähnungen aus persönlichen Nachrichten in den Ergebnissen haben möchtest, entferne die Zeile AND t.archetype = 'regular' aus der ersten Abfrage.

Lass mich wissen, falls dies nicht die Art von Daten zurückgibt, die du suchst. Ich werde versuchen, morgen auf deine anderen Fragen zu antworten, falls sie bis dahin niemand anders beantwortet hat.

3 „Gefällt mir“