Lecteurs et utilisateurs actifs par jour

Données et rapports
1

Ce rapport SQL fournit un décompte quotidien des lecteurs (utilisateurs qui ne font que lire des publications) et des utilisateurs actifs (utilisateurs qui ont créé au moins une publication au cours de la journée), sur une plage de dates spécifiée, avec un filtre facultatif pour l’ID de catégorie.

Ce rapport est conçu pour aider les administrateurs à comprendre l’activité de leur communauté, non seulement en termes de création de contenu, mais aussi de consommation de contenu. Cette double perspective permet une compréhension plus détaillée de l’engagement des utilisateurs, mettant en évidence les jours de forte activité de lecture ou de publication.

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

Explication de la requête SQL

Paramètres

  • start_date et end_date : Définissent la période d’analyse.
  • reader_post_read_count : Définit le seuil pour considérer quelqu’un comme un « lecteur » en fonction du nombre de publications qu’il a lues.
  • category_id : Facultatif. Lorsqu’il est spécifié, le rapport se concentre sur une seule catégorie, permettant une analyse plus ciblée.

CTEs

  1. posts_by_user : Cette CTE identifie le nombre de publications créées par chaque utilisateur chaque jour dans la plage de dates spécifiée. Elle est cruciale pour comprendre quels utilisateurs contribuent au contenu et comment cette activité est répartie dans le temps.
  2. posts_read_by_user : Cette CTE calcule le nombre de publications lues par chaque utilisateur chaque jour, également dans la plage de dates spécifiée. Elle utilise les données de visite des utilisateurs pour estimer la consommation de contenu, un indicateur clé d’engagement.
  3. active_users_per_day : Basée sur la CTE posts_by_user, cette CTE compte le nombre d’utilisateurs uniques qui ont publié au moins une fois par jour. Elle fournit un instantané quotidien des créateurs de contenu actifs.
  4. readers_per_day : En utilisant la CTE posts_read_by_user, cette CTE identifie les utilisateurs qui ont lu plus qu’un nombre spécifié de publications (reader_post_read_count) sans publier eux-mêmes. Elle met en évidence la majorité silencieuse qui s’engage en lisant.

Résultats

La sortie finale présente un aperçu quotidien de l’engagement des utilisateurs, détaillant le nombre de lecteurs et d’utilisateurs actifs (ceux qui ont publié) chaque jour.

Exemple de résultats

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 « J'aime »