Métricas de la página del usuario

[srinivas.chilukuri]

@SaraDev
¿puedes proporcionar la consulta SQL para las métricas disponibles en https://meta.discourse.org/u?cards=no&order=post_count
consulta la imagen a continuación

métricas

• me gusta recibidos
• me gusta dados
• temas vistos
• publicaciones leídas
• días visitados
• soluciones
• saludos

---

estamos utilizando moderadores de categoría por lo que estamos modificando la consulta de moderador para cualquier grupo dado

[SaraDev (Sara Devlaeminck)]

Hola @srinivas.chilukuri,

Las estadísticas de la página de usuario /u se pueden recuperar a través del Explorador de datos utilizando la tabla directory_items.

Métricas de la página del directorio de usuarios

-- [params]
-- int :period
-- Opciones de período:
-- 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

En lugar de los parámetros típicos start_date y end_date, los datos de esta tabla se pueden filtrar utilizando el campo period_type, donde los siguientes valores corresponden a los diferentes períodos de tiempo disponibles en la página del directorio:

• 1: todo el tiempo
• 2: anual
• 3: mensual
• 4: semanal
• 5: diario
• 6: trimestral

Los resultados de ejemplo para este informe se verían así:

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
Requiero la fecha de inicio y fin. ¿Hay alguna solución alternativa para obtener las métricas dadas con fecha de inicio y fin

• usuario
• likes_recibidos
• likes_dados
• temas_vistos
• numero_temas
• numero_posts
• dias_visitados
• posts_leidos
• soluciones
• aplausos

Nota: Estoy obteniendo métricas para un pequeño subconjunto de usuarios totales

[SaraDev (Sara Devlaeminck)]

@srinivas.chilukuri

Requiero la fecha de inicio y fin. ¿Hay alguna solución alternativa para obtener las métricas dadas con fecha de inicio y fin?

• usuario
• likes_recibidos
• likes_dados
• temas_vistos
• recuento_temas
• recuento_publicaciones
• dias_visitados
• publicaciones_leidas
• soluciones
• aplausos

Si desea ver esas métricas para los usuarios en su sitio y filtrar por fechas de inicio y fin específicas, necesitaría una consulta que extraiga los datos de cada métrica en una CTE separada y luego combine los resultados en una declaración SELECT final.

Así es como se vería:

Métricas de usuario

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

Explicación de la consulta:

1. Parámetros:
   • :start_date y :end_date son parámetros que definen el rango de fechas para los datos que se consultan.
2. Expresiones Comunes de Tabla (CTE):
   • likes_received: Cuenta el número de “me gusta” recibidos por cada usuario (action_type = 2) dentro del rango de fechas especificado.
   • likes_given: Cuenta el número de “me gusta” dados por cada usuario (action_type = 1) dentro del rango de fechas especificado.
   • user_metrics: Agrega estadísticas de usuario como temas vistos, publicaciones leídas y días visitados para usuarios que publicaron por primera vez dentro del rango de fechas especificado.
   • solutions: Cuenta el número de soluciones proporcionadas por cada usuario (action_type = 15) dentro del rango de fechas especificado.
   • cheers: Suma las puntuaciones de gamificación para cada usuario dentro del rango de fechas especificado.
3. Selección Final:
   • La consulta principal selecciona las métricas de participación del usuario para cada usuario, incluyendo “me gusta” recibidos, “me gusta” dados, temas vistos, publicaciones leídas, días visitados, soluciones proporcionadas y aplausos recibidos.
   • Utiliza LEFT JOIN para garantizar que se incluyan todos los usuarios, incluso si no tienen actividad en algunas categorías, rellenando con ceros usando COALESCE.

Resultados de ejemplo

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

Métricas de usuario

Usamos la consulta proporcionada en la publicación anterior y tenemos las siguientes preguntas:

1. ¿Es user_metrics de la tabla user_stats la fuente correcta para esta información? Dado que user_stats es una tabla estática que resume las métricas de un usuario desde que se unió a Discourse, puede que no sea ideal para filtrar métricas dentro de un rango de tiempo específico (por ejemplo, desde una fecha de inicio hasta una fecha de finalización).
2. Comparación de series temporales (T/S C/O)
   Para un conjunto determinado de usuarios, comparamos los datos del período de tiempo disponibles en la página de usuario y notamos discrepancias significativas.

Discrepancias clave:

• topics_entered
• posts_read_count
• days_visited

¿Podría aclarar si hay una mejor manera de recuperar métricas de usuario limitadas en el tiempo?

[Jagster (Jakke Lehtonen)]

PG::UndefinedColumn: ERROR:  la columna uv.topic_id no existe
LINE 38:         COUNT(DISTINCT uv.topic_id) AS topics_viewed, -- Cou...

[SaraDev (Sara Devlaeminck)]

@srinivas.chilukuri

¿Es user_metrics de la tabla user_stats la fuente correcta para esta información? Dado que user_stats es una tabla estática que resume las métricas de un usuario desde que se unió a Discourse, puede que no sea ideal para filtrar métricas dentro de un rango de fechas específico (por ejemplo, desde una fecha de inicio hasta una fecha de fin).

Tienes razón en que la tabla user_stats es una tabla estática que resume las métricas de por vida de un usuario desde que se unió a Discourse.

En su lugar, para filtrar métricas por fecha como posts_read_count y days_visited, querríamos usar la tabla de base de datos user_visits para posts. También querríamos usar la tabla topic_views para filtrar las métricas de topics_entered por fecha.

@srinivas.chilukuri

Para un conjunto dado de usuarios, comparamos los datos disponibles del período de tiempo en la página de usuario y notamos discrepancias significativas.

Las discrepancias que observaste se deben al uso de la tabla user_stats en lugar de otras tablas como user_visits y topic_views para filtrar esas estadísticas por fecha.

Para abordar esto, podemos actualizar la consulta para usar esas tablas de base de datos en su lugar:

Aquí hay una versión actualizada de la consulta:

Métricas de la Página de Usuario

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

Ten en cuenta que con este método, los datos de posts_read en la tabla user_visits tienen una distinción importante: no cuentan las publicaciones propias de un usuario, mientras que los datos de la tabla user_stats sí incluyen las publicaciones autogeneradas, por lo que es posible que aún encuentres una diferencia entre estas dos estadísticas en esta consulta y en la Página de Usuario.