Dashboard-Bericht - Reaktionen

Daten & Berichterstattung
, , ,
1

Dies ist eine SQL-Version des Dashboard-Berichts für Reaktionen.

Dieser Bericht liefert eine tägliche Zählung aller Likes und Reaktionen auf Beiträge auf einer Website über einen bestimmten Zeitraum.

Dieser Bericht hilft dabei, einen Schnappschuss des Benutzerengagements innerhalb einer Discourse-Community zu liefern, indem die Häufigkeit verschiedener Reaktions-Emojis und Likes auf Beiträge gemessen wird. Durch die Analyse der Nutzung verschiedener Reaktions-Emojis können Administratoren Einblicke gewinnen, wie oft Benutzer mit Inhalten interagieren, welche emotionalen Reaktionen Benutzer auf Beiträge haben und sowohl beliebte als auch untergenutzte Reaktions-Emojis identifizieren.

:information_source: Für diesen Bericht muss das Plugin Discourse Reactions auf Ihrer Website aktiviert sein. Die im Bericht verfügbaren Reaktionen hängen davon ab, welche spezifischen Reaktionen mit der Site-Einstellung discourse_reactions_enabled_reactions aktiviert wurden.

-- [params]
-- date :start_date = 2023-12-16
-- date :end_date = 2024-01-17
 
SELECT
  r.day,
  COALESCE(l.likes_count, 0) as likes_count,
  sum(case when reaction_value = 'laughing' then reactions_count else 0 end)::int as laughing,
  sum(case when reaction_value = 'cry' then reactions_count else 0 end)::int as cry,
  sum(case when reaction_value = 'exploding_head' then reactions_count else 0 end)::int as exploding_head,
  sum(case when reaction_value = 'clap' then reactions_count else 0 end)::int as clap,
  sum(case when reaction_value = 'confetti_ball' then reactions_count else 0 end)::int as confetti_ball,
  sum(case when reaction_value = 'hugs' then reactions_count else 0 end)::int as hugs,
  sum(case when reaction_value = 'chefs_kiss' then reactions_count else 0 end)::int as chefs_kiss,
  sum(case when reaction_value = '100' then reactions_count else 0 end)::int as one_hundred,
  sum(case when reaction_value = '+1' then reactions_count else 0 end)::int as plus_one,
  sum(case when reaction_value = 'rocket' then reactions_count else 0 end)::int as rocket,
  sum(case when reaction_value = 'star_struck' then reactions_count else 0 end)::int as star_struck,
  sum(case when reaction_value = 'eyes' then reactions_count else 0 end)::int as eyes,
  sum(case when reaction_value = 'discourse' then reactions_count else 0 end)::int as discourse
FROM (
  SELECT
    date_trunc('day', drru.created_at)::date as day,
    drr.reaction_value,
    count(drru.id) as reactions_count
  FROM discourse_reactions_reactions as drr
  LEFT OUTER JOIN discourse_reactions_reaction_users as drru on drr.id = drru.reaction_id
  WHERE drr.reaction_users_count IS NOT NULL
  AND drru.created_at::date >= :start_date::date AND drru.created_at::date <= :end_date::date
  GROUP BY drr.reaction_value, day
) r
LEFT JOIN (
  SELECT
    count(pa.id) as likes_count,
    date_trunc('day', pa.created_at)::date as day
  FROM post_actions as pa
  WHERE pa.post_action_type_id = 2 
  AND pa.created_at::date >= :start_date::date AND pa.created_at::date <= :end_date::date 
  GROUP BY day
) l ON r.day = l.day
GROUP BY r.day, l.likes_count
ORDER BY r.day

SQL-Abfrageerklärung

Parameter

  • Die Abfrage akzeptiert zwei Parameter, :start_date und :end_date, die den Datumsbereich für den Bericht definieren. Beide Datumsparameter akzeptieren das Datumsformat JJJJ-MM-TT.

Abfragestruktur

  • Innere Abfrage (Reaktionen): Die Abfrage beginnt mit einer Unterabfrage, die das Datum (day), die Art der Reaktion (reaction_value) und die Anzahl der Reaktionen (reactions_count) aus der Tabelle discourse_reactions_reactions auswählt, die mit discourse_reactions_reaction_users über reaction_id verknüpft ist. Diese Verknüpfung stellt sicher, dass wir benutzerspezifische Reaktionen berücksichtigen.
  • Aggregation nach Reaktionstypen: Die ausgewählten Daten werden dann nach day und reaction_value gruppiert, wobei der Bereich auf die ausgewählten Start- und Enddaten beschränkt wird. Dies wird verwendet, um die Gesamtzahl jedes Reaktionstyps für jeden Tag im angegebenen Zeitraum zu berechnen.
  • Sortierung der Reaktionssummen: Für jeden Reaktionstyp verwendet die Abfrage eine CASE-Anweisung, um die Häufigkeit der Verwendung der spezifischen Reaktion zu aggregieren und das Ergebnis als Ganzzahl für eine saubere Zählung zu konvertieren.
    • Sie müssen möglicherweise reaction_value = '...' in diesem Abschnitt anpassen, je nachdem, welche Reaktionen auf Ihrer Website aktiviert sind.
  • Unterabfrage für die Anzahl der Likes: Eine separate Unterabfrage wird verwendet, um die Gesamtzahl der Likes (likes_count) für jeden Tag mithilfe der Tabelle post_actions zu berechnen, bei der post_action_type_id den Likes entspricht.
  • Kombination von Daten: Die äußere Abfrage kombiniert dann die Zählung der Likes mit den Reaktionszählungen, indem sie nach dem Feld day verknüpft wird.
  • Endgültige Auswahl: Die äußerste SELECT-Anweisung generiert die endgültige Ausgabe mit dem day, der Anzahl der Likes (likes_count) und den Zählungen jedes Reaktionstyps. Wenn an einem bestimmten Tag keine Daten für Likes vorhanden sind, stellt die COALESCE-Funktion sicher, dass anstelle von NULL eine Null angezeigt wird.
  • Sortierung der Ergebnisse: Die Ergebnisse werden nach Datum (r.day) sortiert, um eine Zeitreihe des Engagements auf der Website zu erstellen.

Spalten

  • day: Das Datum, an dem die Reaktionen und Likes gezählt wurden.
  • likes_count: Gesamtzahl der Likes für jeden Tag.
  • Jeder Reaktionstyp (lachend, weinend, explodierender Kopf usw.): Einzelne Spalten, die die Gesamtzahl jedes Reaktionstyps pro Tag anzeigen.

Beispielergebnisse

day likes_count laughing cry exploding_head clap confetti_ball hugs chefs_kiss one_hundred plus_one rocket star_struck eyes discourse
2023-12-16 13 0 3 0 3 1 0 0 0 5 2 2 1 0
2023-12-17 17 1 0 2 2 0 0 0 0 4 4 1 2 0
2023-12-18 46 0 1 0 6 0 1 3 0 27 3 4 5 0
1 „Gefällt mir“
2

Und zur Information für uns Nicht-Entwickler… diese Reaktionen sind dort fest einprogrammiert und werden hier verwendet. Das bedeutet auch, dass, wenn sich die angebotenen Reaktionen irgendwann ändern, dies fälschlicherweise ältere Zeiten anzeigt.

Ich habe das Gefühl, dass dies falsch gezählt wird, wenn sich der Standard beispielsweise von :heart: zu :+1: geändert hat.

3

@JammyDodger hat eine von mir verwendete Abfrage korrigiert, die dies berücksichtigt, glaube ich:

2 „Gefällt mir“