Requisito complejo de informe de actividad del usuario

Comunidad Datos e informes
1

Estoy buscando una forma de que Discourse genere informes de actividad en forma de un resumen de toda la actividad de los usuarios entre dos fechas personalizadas cada año (la funcionalidad integrada actual solo parece tener rangos predefinidos, y solo en relación con la fecha de hoy), y guardar los resultados en un archivo compatible con Excel (actualmente esto solo parece posible para informes de actividad de usuarios individuales).

  • Me gustaría incluir también los mensajes enviados por correo electrónico y respondidos por correo electrónico (sin contar dos veces los mismos mensajes cuando se leen o publican mientras se usa el foro en línea). Los informes actuales parecen excluir los mensajes enviados por correo electrónico.

  • Me gustaría filtrar estos resultados según un campo personalizado del perfil de usuario (un número de membresía único).

  • Me gustaría excluir los resultados de los usuarios con números de membresía que no estén dentro de un rango numérico designado.

  • También, idealmente, me gustaría tener la capacidad de generar una puntuación de puntos de actividad por usuario para el período, ponderada según los mensajes leídos, los mensajes publicados y los mensajes que han recibido “me gusta”.

  • Las ponderaciones (multiplicadores) para cada una de estas variables de actividad deben ser ajustables y configuradas por el administrador. Los resultados deben redondearse hacia abajo al múltiplo de 5 más cercano y cortarse en un número máximo de puntos predefinido, establecido por el administrador.

  • Sería deseable tener algún tipo de desglose por categoría de tema o etiqueta por usuario.

  • Idealmente, este informe se generaría automáticamente y se me enviaría por correo electrónico en momentos designados cada año (eso sería la cereza del pastel).

¿Qué tan factible es todo esto?

¿Requeriría un nuevo plugin personalizado para implementarlo, o es posible mediante alguna consulta SQL avanzada dentro del plugin Data Explorer actual?

¿O sería la decisión inteligente buscar una opción de exportación “todo” relativamente básica e intentar el resto usando Excel?

2 Me gusta
2

El explorador de datos sería probablemente el mejor enfoque aquí. Es posible que necesites crear consultas diferentes para cada tarea (por ejemplo, el desglose por usuario requeriría una consulta distinta).

Lo único que el explorador de datos no puede hacer es el punto de “generado automáticamente y enviado por correo electrónico”. Si este es un requisito, podrías hacerlo potencialmente llamando a la API del explorador de datos desde otro sistema.

3 Me gusta
3

Gracias, David.
Parece que tendré que ponerme las pilas con las consultas SQL.

¿Puede una consulta tomar como entrada la salida filtrada de otra consulta? Además, ¿se puede incluir definitivamente la interacción basada en listas de correo? ¿Se almacena de alguna manera diferente? Tenía miedo de que hubiera sido excluida de los informes de actividad estándar por alguna razón oscura pero insuperable.

4

No, tendrías que copiar y pegar secciones de la consulta.

La tabla posts en la base de datos incluye un booleano via_email, así que sí, puedes saber qué publicaciones se crearon por correo electrónico :+1:

Sin embargo, Discourse no incluye ningún rastreador en los correos electrónicos que envía, por lo que no habrá forma de determinar si una notificación por correo electrónico ha sido ‘leída’ o no.

5

¿La funcionalidad de informes de actividad de usuario “integrada” se gestiona mediante consultas SQL que puedo copiar de cualquier lugar y modificar, para evitar pasar semanas reinventando la rueda?

6

¿Estás consultando el informe de “usuarios activos diarios”? Este se genera con esta lógica, que utiliza ActiveRecord (por lo que no hay SQL crudo). Aun así, la lógica podría ser un punto de partida útil.

7

Mi objetivo es generar un informe de actividad por usuario en formato CSV, que abarque desde una fecha hasta otra. El objetivo final es asignar a cada usuario una puntuación de actividad para el año (u otro período) basada en los mensajes recibidos/leídos en línea o por correo electrónico, los mensajes publicados en línea o por correo electrónico, y otorgar más puntos a los mensajes que reciben “me gusta”. El informe que me gustaría utilizar como base es el primero que aparece al hacer clic en Admin/Usuarios, ya que ya realiza gran parte de lo que necesito.

image
image868×321 44.3 KB

8

La lógica del directorio de usuarios se encuentra aquí: discourse/app/models/directory_item.rb at 1e66e4602f408d7b2d1105b6c228542dce7f4b38 · discourse/discourse · GitHub

9

Hmm, no parece una tarea trivial de replicar mediante SQL (al menos no para mí), ya que tendré que aprender lo suficiente de SQL para hacerlo desde cero al mismo tiempo, si no hay nada lo suficientemente similar que pueda copiar directamente. La última vez que hice programación fue hace muchos años en la escuela secundaria, cuando BASIC no era tan vergonzoso admitirlo.

1 me gusta
10

Sí, creo que esto requerirá un conocimiento razonable de SQL para implementarlo. Si tienes presupuesto para el trabajo, podrías encontrar a alguien que pueda ayudarte en el Marketplace.

11

¡Saludos! No tengo presupuesto (el foro genera pocos ingresos por donaciones, más allá de los costos básicos de alojamiento), pero parece que tendré que ir por ese camino de todos modos.

1 me gusta
12

@Paul_King

Esta consulta podría ayudar.

https://meta.discourse.org/t/daily-weekly-or-total-stats-by-user-over-a-specified-time-range/275167u=grayden_shand

13

¡Muchas gracias! Lo intenté, pero me aparece un error de sintaxis.

PG::SyntaxError: ERROR: syntax error at or near “WITH”
LINE 13: WITH date_range AS (

(Disculpas, envié un mensaje privado por ese hilo antes de darme cuenta de que tú y el autor original de allí son la misma persona).

1 me gusta
14

¿Te importa si pregunto qué valores estás utilizando para las variables?

Acabo de extraer la consulta y ejecutarla en un sitio de prueba con los siguientes valores:

  • start_date: 2021-07-01
  • end_date: 2021-07-30
  • coverage: all
15

Hola, probé algunas, pero todas me dieron ese resultado, por ejemplo

image
image1428×491 21.7 KB

16

Lo siento, no he podido reproducir el error.

¿Podrías pegar aquí la consulta tal como la tienes?

17 
SELECT 1-- cobertura: 'week', 'all' o 'date'
-- [parámetros]
-- date :start_date = 2019-08-27
-- date :end_date = 2019-09-30
-- text :coverage = week

WITH date_range AS (
SELECT date_trunc('day', dd):: date AS "date", EXTRACT(week from date_trunc('day', dd):: date) AS "week"
FROM generate_series
        ( :start_date::timestamp 
        , :end_date::timestamp
        , '1 day'::interval) dd
), likes_given AS (
SELECT u.id, dr.date, dr.week, count(pa.*) AS "likes"
FROM date_range dr
FULL JOIN users u ON (1=1)
LEFT JOIN post_actions pa ON (pa.created_at:: date = dr.date and post_action_type_id=2 AND user_id = u.id)
GROUP BY dr.date, dr.week, u.id
ORDER BY u.id, dr.date
), posts_summary AS (SELECT u.id, u.username, u.created_at, dr.*, count(p.id) - count(t.id) AS replies, count(t.id) AS topics, COALESCE(sum(p.like_count),0) AS likes_received
from date_range dr
FULL OUTER JOIN users u ON (1=1)
LEFT JOIN posts p ON (p.user_id = u.id AND p.created_at::date=dr.date AND p.deleted_at IS NULL)
LEFT JOIN topics t ON (t.user_id = u.id AND t.created_at::date = dr.date AND p.topic_id = t.id AND t.deleted_at IS NULL)
GROUP BY u.id, dr.date, dr.week
ORDER BY u.id, dr.date), 
visits AS (SELECT u.id, dr.*, COALESCE(sum(posts_read),0) AS posts_read, COALESCE(sum(time_read),0) AS time_read, COUNT(uv.*) AS visits
FROM date_range dr
FULL OUTER JOIN users u ON (1=1)
LEFT JOIN user_visits uv ON (uv.user_id = u.id AND visited_at = dr.date)
GROUP BY u.id, dr.date, dr.week
ORDER BY u.id, dr.date
)

SELECT ps.id, ps.username, ps.created_at, CASE
WHEN :coverage::text = 'week' THEN ps.week::text
WHEN :coverage::text = 'all' THEN '-1'
ELSE ps.date::text
END  AS period, sum(ps.replies) AS replies, sum(ps.topics) AS topics, sum(ps.likes_received) AS likes_received, sum(lg.likes) AS likes_given, COALESCE(sum(posts_read),0) AS posts_read, COALESCE(sum(time_read),0) AS time_read, SUM(visits) AS visits
FROM posts_summary ps
LEFT JOIN likes_given lg ON (ps.date = lg.date AND ps.id = lg.id)
LEFT JOIN visits v ON (v.id = ps.id AND v.date = ps.date)
GROUP BY ps.id, ps.username, ps.created_at, CASE 
WHEN :coverage::text = 'week' THEN ps.week::text
WHEN :coverage::text = 'all' THEN '-1'
ELSE ps.date::text
END  
ORDER BY ps.id, CASE
WHEN :coverage::text = 'week' THEN ps.week::text
WHEN :coverage::text = 'all' THEN '-1'
ELSE ps.date::text
END
1 me gusta
18

Ah, ya veo.

El SELECT 1 al principio no forma parte de la consulta y es la causa de tu problema. Es el marcador de posición que aparece cuando creas una nueva consulta en el Explorador de Datos. Elimínalo y debería funcionar.

-- cobertura: 'semana', 'todo' o 'fecha'
-- [parámetros]
-- fecha :start_date = 2019-08-27
-- fecha :end_date = 2019-09-30
-- texto :cobertura = semana

WITH date_range AS (
SELECT date_trunc('day', dd):: date AS "fecha", EXTRACT(week from date_trunc('day', dd):: date) AS "semana"
FROM generate_series
        ( :start_date::timestamp 
        , :end_date::timestamp
        , '1 day'::interval) dd
), likes_given AS (
SELECT u.id, dr.fecha, dr.semana, count(pa.*) AS "me_gusta"
FROM date_range dr
FULL JOIN users u ON (1=1)
LEFT JOIN post_actions pa ON (pa.created_at:: date = dr.fecha and post_action_type_id=2 AND user_id = u.id)
GROUP BY dr.fecha, dr.semana, u.id
ORDER BY u.id, dr.fecha
), posts_summary AS (SELECT u.id, u.username, u.created_at, dr.*, count(p.id) - count(t.id) AS respuestas, count(t.id) AS temas, COALESCE(sum(p.like_count),0) AS me_gusta_recibidos
from date_range dr
FULL OUTER JOIN users u ON (1=1)
LEFT JOIN posts p ON (p.user_id = u.id AND p.created_at::date=dr.fecha AND p.deleted_at IS NULL)
LEFT JOIN topics t ON (t.user_id = u.id AND t.created_at::date = dr.fecha AND p.topic_id = t.id AND t.deleted_at IS NULL)
GROUP BY u.id, dr.fecha, dr.semana
ORDER BY u.id, dr.fecha), 
visits AS (SELECT u.id, dr.*, COALESCE(sum(posts_read),0) AS publicaciones_leídas, COALESCE(sum(time_read),0) AS tiempo_leído, COUNT(uv.*) AS visitas
FROM date_range dr
FULL OUTER JOIN users u ON (1=1)
LEFT JOIN user_visits uv ON (uv.user_id = u.id AND visited_at = dr.fecha)
GROUP BY u.id, dr.fecha, dr.semana
ORDER BY u.id, dr.fecha
)

SELECT ps.id, ps.username, ps.created_at, CASE
WHEN :cobertura::text = 'semana' THEN ps.semana::text
WHEN :cobertura::text = 'todo' THEN '-1'
ELSE ps.fecha::text
END  AS periodo, sum(ps.respuestas) AS respuestas, sum(ps.temas) AS temas, sum(ps.me_gusta_recibidos) AS me_gusta_recibidos, sum(lg.me_gusta) AS me_gusta_dados, COALESCE(sum(publicaciones_leídas),0) AS publicaciones_leídas, COALESCE(sum(tiempo_leído),0) AS tiempo_leído, SUM(visitas) AS visitas
FROM posts_summary ps
LEFT JOIN likes_given lg ON (ps.fecha = lg.fecha AND ps.id = lg.id)
LEFT JOIN visits v ON (v.id = ps.id AND v.fecha = ps.fecha)
GROUP BY ps.id, ps.username, ps.created_at, CASE 
WHEN :cobertura::text = 'semana' THEN ps.semana::text
WHEN :cobertura::text = 'todo' THEN '-1'
ELSE ps.fecha::text
END  
ORDER BY ps.id, CASE
WHEN :cobertura::text = 'semana' THEN ps.semana::text
WHEN :cobertura::text = 'todo' THEN '-1'
ELSE ps.fecha::text
END
19

Gracias @Grayden_Shand

El error ha desaparecido.

Si puedo aprovechar tu experiencia un poco más, ¿incluyen los recuentos generados por esta consulta las publicaciones por correo electrónico y las respuestas por correo electrónico para los usuarios en modo de lista de correo? Si no es así, ¿cómo puedo incluirlos?

Además, ¿cómo puedo incluir el valor de un campo personalizado del perfil de usuario junto al nombre del usuario?

¿Tienes algún consejo sobre cómo identificar el nombre del campo involucrado y cómo implementarlo?

20

Sí, debería hacerlo. Como mencionó David, la tabla posts contiene un campo booleano via_email. La consulta actualmente ignora este campo y cuenta todas las publicaciones, independientemente de si se enviaron por correo electrónico o no.

Existe una tabla llamada user_custom_fields. Necesitarías hacer un JOIN con esta tabla para incluir un campo personalizado específico.

Probablemente lo haría en la subconsulta post_summary.

Por ejemplo:

...
), posts_summary AS (
SELECT u.id, u.username, u.created_at, dr.*, count(p.id) - count(t.id) AS replies, count(t.id) AS topics, COALESCE(sum(p.like_count),0) AS likes_received, cf.value as LABEL_FOR_CUSTOM_FIELD
from date_range dr
FULL OUTER JOIN users u ON (1=1)
LEFT JOIN posts p ON (p.user_id = u.id AND p.created_at::date=dr.date AND p.deleted_at IS NULL)
LEFT JOIN topics t ON (t.user_id = u.id AND t.created_at::date = dr.date AND p.topic_id = t.id AND t.deleted_at IS NULL)
LEFT JOIN user_custom_fields cf ON (u.id == cf.user_id and cf.name == "YOUR CUSTOM FIELD NAME") 
GROUP BY u.id, dr.date, dr.week
ORDER BY u.id, dr.date
)
...

Añadí una columna a la cláusula SELECT y agregué una nueva cláusula JOIN para la tabla user_custom_fields.

Ten en cuenta que necesitarás reemplazar "YOUR CUSTOM FIELD NAME" y LABEL_FOR_CUSTOM_FIELD.

También deberás actualizar las columnas que seleccionas en la consulta final.

...
SELECT ps.id, ps.username, ps.created_at, ps.LABEL_FOR_CUSTOM_FIELD, CASE
WHEN :coverage::text = 'week' THEN ps.week::text
WHEN :coverage::text = 'all' THEN '-1'
ELSE ps.date::text
END  AS period, sum(ps.replies) AS replies, sum(ps.topics) AS topics, sum(ps.likes_received) AS likes_received, sum(lg.likes) AS likes_given, COALESCE(sum(posts_read),0) AS posts_read, COALESCE(sum(time_read),0) AS time_read, SUM(visits) AS visits
FROM posts_summary ps
..

Probablemente es así como lo abordaría.

¡Buena suerte!