Mesures des pages utilisateurs

[srinivas.chilukuri]

@SaraDev
pouvez-vous fournir la requête SQL pour les métriques disponibles sur https://meta.discourse.org/u?cards=no&order=post_count
voir l’image ci-dessous

métriques

• likes reçus
• likes donnés
• sujets consultés
• messages lus
• jours visités
• solutions
• cheers

---

nous utilisons des modérateurs de catégorie, nous modifions donc la requête de modérateur pour tout groupe donné

[SaraDev (Sara Devlaeminck)]

Salut @srinivas.chilukuri,

Les statistiques de la page utilisateur /u peuvent être récupérées via l’Explorateur de données en utilisant la table directory_items.

Métriques de la page de répertoire des utilisateurs

-- [params]
-- int :period
-- Options de période :
-- 1. all
-- 2. yearly
-- 3. monthly
-- 4. weekly
-- 5. daily
-- 6. quarterly

SELECT 
    di.user_id,
    COALESCE(di.likes_received, 0) AS likes_received,
    COALESCE(di.likes_given, 0) AS likes_given,
    COALESCE(di.topics_entered, 0) AS topics_viewed,
    COALESCE(di.topic_count, 0) AS topic_count,
    COALESCE(di.post_count, 0) AS post_count,
    COALESCE(di.days_visited, 0) AS days_visited,
    COALESCE(di.posts_read, 0) AS posts_read,
    COALESCE(di.solutions, 0) AS solutions,
    COALESCE(di.gamification_score, 0) AS cheers
FROM 
    directory_items di
WHERE 
    di.period_type = :period
ORDER BY 
    di.user_id

Au lieu des paramètres typiques start_date et end_date, les données de cette table peuvent être filtrées à l’aide du champ period_type, où les valeurs suivantes correspondent aux différentes périodes disponibles sur la page du répertoire :

• 1 : tout temps
• 2 : annuel
• 3 : mensuel
• 4 : hebdomadaire
• 5 : quotidien
• 6 : trimestriel

Les résultats d’exemple pour ce rapport ressembleraient à ceci :

user	likes_received	likes_given	topics_viewed	topic_count	post_count	days_visited	posts_read	solutions	cheers
Username1	4	17	250	69	116	480	217	10	844100
Username2	2	5	47	0	2	43	59	1	112305
Username3	0	4	2	0	0	2	7	0	3100
…	…	..	…	…	…	…	…	…	…

[srinivas.chilukuri]

@SaraDev
J’ai besoin de la date de début et de la date de fin. Existe-t-il une solution de contournement pour obtenir les métriques données avec une date de début et une date de fin ?

• utilisateur
• likes_reçus
• likes_donnés
• sujets_consultés
• nombre_de_sujets
• nombre_de_messages
• jours_visités
• messages_lus
• solutions
• encouragements

Remarque : J’obtiens les métriques pour un petit sous-ensemble d’utilisateurs au total.

[SaraDev (Sara Devlaeminck)]

@srinivas.chilukuri

J’ai besoin de la date de début et de fin. Existe-t-il une solution de contournement pour obtenir les métriques données avec une date de début et de fin

• user
• likes_received
• likes_given
• topics_viewed
• topic_count
• post_count
• days_visited
• posts_read
• solutions
• cheers

Si vous souhaitez afficher ces métriques pour les utilisateurs de votre site et filtrer par des dates de début et de fin spécifiques, vous auriez besoin d’une requête qui extrait les données pour chaque métrique dans une CTE distincte, puis combine les résultats dans une instruction SELECT finale.

Voici à quoi cela ressemblerait :

Métriques utilisateur

-- [params]
-- date :start_date = 2020-01-01
-- date :end_date = 2025-01-01

WITH likes_received AS (
    SELECT
        ua.user_id AS user_id,
        COUNT(*) AS likes_received
    FROM
        user_actions ua
    WHERE
        ua.action_type = 2
        AND ua.created_at BETWEEN :start_date AND :end_date
    GROUP BY
        ua.user_id
),
likes_given AS (
    SELECT
        ua.acting_user_id AS user_id,
        COUNT(*) AS likes_given
    FROM
        user_actions ua
    WHERE
        ua.action_type = 1
        AND ua.created_at BETWEEN :start_date AND :end_date
    GROUP BY
        ua.acting_user_id
),
user_metrics AS (
    SELECT
        us.user_id,
        SUM(us.topics_entered) AS topics_viewed,
        SUM(us.posts_read_count) AS posts_read,
        SUM(us.days_visited) AS days_visited
    FROM
        user_stats us
    WHERE
        us.first_post_created_at BETWEEN :start_date AND :end_date
    GROUP BY
        us.user_id
),
solutions AS (
    SELECT
        ua.acting_user_id AS user_id,
        COUNT(*) AS solutions
    FROM
        user_actions ua
    WHERE
        ua.action_type = 15
        AND ua.created_at BETWEEN :start_date AND :end_date
    GROUP BY
        ua.acting_user_id
),
cheers AS (
    SELECT
        gs.user_id,
        SUM(gs.score) AS cheers
    FROM
        gamification_scores gs
    WHERE
        gs.date BETWEEN :start_date AND :end_date
    GROUP BY
        gs.user_id
)

SELECT
    u.id AS user_id,
    COALESCE(lr.likes_received, 0) AS likes_received,
    COALESCE(lg.likes_given, 0) AS likes_given,
    COALESCE(um.topics_viewed, 0) AS topics_viewed,
    COALESCE(um.posts_read, 0) AS posts_read,
    COALESCE(um.days_visited, 0) AS days_visited,
    COALESCE(sol.solutions, 0) AS solutions,
    COALESCE(ch.cheers, 0) AS cheers
FROM
    users u
LEFT JOIN
    likes_received lr ON u.id = lr.user_id
LEFT JOIN
    likes_given lg ON u.id = lg.user_id
LEFT JOIN
    user_metrics um ON u.id = um.user_id
LEFT JOIN
    solutions sol ON u.id = sol.user_id
LEFT JOIN
    cheers ch ON u.id = ch.user_id
ORDER BY
    u.id

Explication de la requête :

1. Paramètres :
   • :start_date et :end_date sont des paramètres qui définissent la plage de dates pour les données interrogées.
2. Expressions de table communes (CTE) :
   • likes_received : Compte le nombre de “likes” reçus par chaque utilisateur (action_type = 2) dans la plage de dates spécifiée.
   • likes_given : Compte le nombre de “likes” donnés par chaque utilisateur (action_type = 1) dans la plage de dates spécifiée.
   • user_metrics : Agrège les statistiques utilisateur telles que les sujets consultés, les messages lus et les jours de visite pour les utilisateurs dont le premier message a été créé dans la plage de dates spécifiée.
   • solutions : Compte le nombre de solutions fournies par chaque utilisateur (action_type = 15) dans la plage de dates spécifiée.
   • cheers : Additionne les scores de gamification pour chaque utilisateur dans la plage de dates spécifiée.
3. Sélection finale :
   • La requête principale sélectionne les métriques d’engagement des utilisateurs pour chaque utilisateur, y compris les “likes” reçus, les “likes” donnés, les sujets consultés, les messages lus, les jours de visite, les solutions fournies et les “cheers” reçus.
   • Elle utilise LEFT JOIN pour s’assurer que tous les utilisateurs sont inclus, même s’ils n’ont aucune activité dans certaines catégories, en complétant avec des zéros à l’aide de COALESCE.

Exemple de résultats

user_id	likes_received	likes_given	topics_viewed	posts_read	days_visited	solutions	cheers
1	10	5	20	100	15	2	30
2	0	3	5	20	5	0	10
—	—	—	—	—	—	—	—

[srinivas.chilukuri]

@SaraDev

@SaraDev

User Metrics

Nous avons utilisé la requête fournie dans le message ci-dessus et avons les questions suivantes :

1. Est-ce que user_metrics de la table user_stats est la source correcte pour ces informations ? Étant donné que user_stats est une table statique résumant les métriques d’un utilisateur depuis son adhésion à Discourse, elle peut ne pas être idéale pour filtrer les métriques dans une plage de temps spécifique (par exemple, d’une date de début à une date de fin).
2. Comparaison de séries chronologiques (T/S C/O)
   Pour un ensemble donné d’utilisateurs, nous avons comparé les données de période disponibles sur la page utilisateur et avons remarqué des écarts importants.

Principaux écarts :

• topics_entered
• posts_read_count
• days_visited

Pourriez-vous clarifier s’il existe un meilleur moyen de récupérer les métriques utilisateur liées au temps ?

[Jagster (Jakke Lehtonen)]

PG::UndefinedColumn: ERREUR : la colonne uv.topic_id n'existe pas
LIGNE 38 : COUNT(DISTINCT uv.topic_id) AS topics_viewed, -- Cou...

[SaraDev (Sara Devlaeminck)]

@srinivas.chilukuri

Est-ce que user_metrics de la table user_stats est la bonne source pour cette information ? Étant donné que user_stats est une table statique résumant les métriques d’un utilisateur depuis son adhésion à Discourse, elle pourrait ne pas être idéale pour filtrer les métriques dans une période de temps spécifique (par exemple, d’une date de début à une date de fin).

Vous avez raison, la table user_stats est une table statique résumant les métriques à vie d’un utilisateur depuis son adhésion à Discourse.

Au lieu de cela, pour filtrer les métriques par date, comme posts_read_count et days_visited, nous devrions utiliser la table de base de données user_visits pour les posts. Nous devrions également utiliser la table topic_views pour filtrer les métriques topics_entered par date.

@srinivas.chilukuri

Pour un ensemble donné d’utilisateurs, nous avons comparé les données disponibles sur la période dans la page utilisateur et avons remarqué des écarts importants.

Les écarts que vous avez observés proviennent de l’utilisation de la table user_stats au lieu d’autres tables comme user_visits et topic_views pour filtrer ces statistiques par date.

Pour résoudre ce problème, nous pouvons mettre à jour la requête pour utiliser ces tables de base de données à la place :

Voici une version mise à jour de la requête :

Métriques de la page utilisateur

-- [params]
-- date :start_date = 2020-01-01
-- date :end_date = 2026-01-01

WITH likes_received AS (
    SELECT
        ua.user_id AS user_id,
        COUNT(*) AS likes_received
    FROM
        user_actions ua
    WHERE
        ua.action_type = 2
        AND ua.created_at BETWEEN :start_date AND :end_date
    GROUP BY
        ua.user_id
),
likes_given AS (
    SELECT
        ua.acting_user_id AS user_id,
        COUNT(*) AS likes_given
    FROM
        user_actions ua
    WHERE
        ua.action_type = 1
        AND ua.created_at BETWEEN :start_date AND :end_date
    GROUP BY
        ua.acting_user_id
),
user_metrics AS (
    SELECT
        tv.user_id,
        COUNT(DISTINCT tv.topic_id) AS topics_viewed
    FROM
        topic_views tv
    WHERE
        tv.viewed_at BETWEEN :start_date AND :end_date
    GROUP BY
        tv.user_id
),
days_and_posts AS (
    SELECT
        uv.user_id,
        COUNT(DISTINCT uv.visited_at) AS days_visited,
        SUM(uv.posts_read) AS posts_read
    FROM
        user_visits uv
    WHERE
        uv.visited_at BETWEEN :start_date AND :end_date
    GROUP BY
        uv.user_id
),
solutions AS (
    SELECT
        ua.acting_user_id AS user_id,
        COUNT(*) AS solutions
    FROM
        user_actions ua
    WHERE
        ua.action_type = 15
        AND ua.created_at BETWEEN :start_date AND :end_date
    GROUP BY
        ua.acting_user_id
),
cheers AS (
    SELECT
        gs.user_id,
        SUM(gs.score) AS cheers
    FROM
        gamification_scores gs
    WHERE
        gs.date BETWEEN :start_date AND :end_date
    GROUP BY
        gs.user_id
)

SELECT
    u.id AS user_id,
    COALESCE(lr.likes_received, 0) AS likes_received,
    COALESCE(lg.likes_given, 0) AS likes_given,
    COALESCE(um.topics_viewed, 0) AS topics_viewed,
    COALESCE(dp.days_visited, 0) AS days_visited,
    COALESCE(dp.posts_read, 0) AS posts_read,
    COALESCE(sol.solutions, 0) AS solutions,
    COALESCE(ch.cheers, 0) AS cheers
FROM
    users u
LEFT JOIN
    likes_received lr ON u.id = lr.user_id
LEFT JOIN
    likes_given lg ON u.id = lg.user_id
LEFT JOIN
    user_metrics um ON u.id = um.user_id
LEFT JOIN
    days_and_posts dp ON u.id = dp.user_id
LEFT JOIN
    solutions sol ON u.id = sol.user_id
LEFT JOIN
    cheers ch ON u.id = ch.user_id
ORDER BY
    u.id

Notez qu’avec cette méthode, la donnée posts_read dans la table user_visits a une distinction importante : elle ne compte pas les propres publications d’un utilisateur, tandis que les données de la table user_stats incluent les publications rédigées par l’utilisateur lui-même. Vous pourriez donc toujours trouver une différence entre ces deux statistiques dans cette requête et sur la page utilisateur.