Leser und aktive Benutzer pro Tag

Daten & Berichterstattung
1

Dieser SQL-Bericht liefert eine tägliche Zählung von Lesern (Benutzer, die nur Beiträge lesen) und aktiven Benutzern (Benutzer, die an einem Tag mindestens einen Beitrag erstellt haben) über einen bestimmten Zeitraum, mit einem optionalen Filter für die Kategorie-ID.

Dieser Bericht soll Administratoren helfen, die Aktivität ihrer Community zu verstehen, nicht nur in Bezug auf die Inhaltserstellung, sondern auch auf den Konsum von Inhalten. Diese doppelte Perspektive ermöglicht ein detaillierteres Verständnis des Benutzerengagements und hebt Tage mit hoher Leserschaft oder Posting-Aktivität hervor.

-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2024-02-01
-- int :reader_post_read_count = 10
-- null int :category_id

WITH posts_by_user AS (
    SELECT
        p.user_id,
        p.created_at::date AS post_date,
        p.topic_id,
        COUNT(*) AS posts
    FROM posts p
    WHERE p.created_at::date BETWEEN :start_date AND :end_date
    GROUP BY p.user_id, post_date, p.topic_id
), 

posts_read_by_user AS (
    SELECT
        uv.user_id,
        uv.visited_at::date AS visit_date,
        SUM(uv.posts_read) AS posts_read
    FROM user_visits uv
    LEFT JOIN group_users gu ON gu.user_id = uv.user_id
    LEFT JOIN groups g ON g.id = gu.group_id
    LEFT JOIN posts p ON p.user_id = uv.user_id AND p.created_at BETWEEN :start_date AND :end_date
    LEFT JOIN topics t ON t.id = p.topic_id AND t.category_id = :category_id
    WHERE uv.visited_at BETWEEN :start_date AND :end_date
    AND (:category_id IS NULL OR t.category_id IS NOT NULL)
    GROUP BY uv.user_id, visit_date
),

active_users_per_day AS (
    SELECT
        pbu.post_date,
        COUNT(DISTINCT pbu.user_id) AS active_user_count
    FROM posts_by_user pbu
    JOIN topics t ON pbu.topic_id = t.id
    WHERE posts > 0
    AND (:category_id IS NULL OR t.category_id = :category_id)
    GROUP BY post_date
),

readers_per_day AS (
    SELECT
        prbu.visit_date,
        COUNT(DISTINCT prbu.user_id) AS reader_count
    FROM posts_read_by_user prbu
    LEFT JOIN posts_by_user pbu ON prbu.user_id = pbu.user_id AND prbu.visit_date = pbu.post_date
    WHERE pbu.posts IS NULL
    AND prbu.posts_read > :reader_post_read_count
    GROUP BY prbu.visit_date
)

SELECT
    rpd.visit_date,
    rpd.reader_count,
    COALESCE(aupd.active_user_count, 0) AS active_user_count
FROM readers_per_day rpd
LEFT JOIN active_users_per_day aupd ON rpd.visit_date = aupd.post_date
ORDER BY rpd.visit_date

SQL-Abfrageerklärung

Parameter

  • start_date und end_date: Definieren den Zeitrahmen für die Analyse.
  • reader_post_read_count: Legt den Schwellenwert fest, um jemanden als „Leser“ zu betrachten, basierend auf der Anzahl der von ihm gelesenen Beiträge.
  • category_id: Optional. Wenn angegeben, konzentriert sich der Bericht auf eine einzelne Kategorie, was eine gezieltere Analyse ermöglicht.

CTEs

  1. posts_by_user: Diese CTE identifiziert die Anzahl der von jedem Benutzer an jedem Tag innerhalb des angegebenen Zeitraums erstellten Beiträge. Sie ist entscheidend, um zu verstehen, welche Benutzer Inhalte beitragen und wie sich diese Aktivität im Laufe der Zeit verteilt.
  2. posts_read_by_user: Diese CTE berechnet die Anzahl der von jedem Benutzer an jedem Tag gelesenen Beiträge, ebenfalls innerhalb des angegebenen Zeitraums. Sie nutzt Benutzerbesuchsdaten, um den Konsum von Inhalten zu schätzen, ein Schlüsselindikator für das Engagement.
  3. active_users_per_day: Basierend auf der CTE posts_by_user zählt diese CTE die Anzahl der eindeutigen Benutzer, die pro Tag mindestens einmal gepostet haben. Sie liefert eine tägliche Momentaufnahme aktiver Content-Ersteller.
  4. readers_per_day: Mithilfe der CTE posts_read_by_user identifiziert diese CTE Benutzer, die mehr als eine angegebene Anzahl von Beiträgen (reader_post_read_count) gelesen haben, ohne selbst etwas zu posten. Sie hebt die stille Mehrheit hervor, die durch Lesen interagiert.

Ergebnisse

Die endgültige Ausgabe präsentiert einen täglichen Überblick über das Benutzerengagement und gibt die Anzahl der Leser und aktiven Benutzer (die gepostet haben) pro Tag an.

Beispielergebnisse

visit_date reader_count active_user_count
2024-01-02 150 25
2024-01-03 175 30
2024-01-04 160 20
2024-01-05 180 22
… … …
4 „Gefällt mir“