¡Hola! Estamos utilizando la versión alojada en la nube de Discourse y hemos estado ejecutando nuestra plataforma durante aproximadamente 1,5 años. Nos gustaría obtener algunas métricas históricas sobre la adopción y el uso de la plataforma. Ya estamos revisando el informe de ‘Registros’ disponible en la pestaña Informes de Administración, pero también queremos poder mostrar el número total de usuarios activos cada mes. ¿Alguien tiene alguna idea sobre cómo podría hacer esto? No parece haber nada apropiado en Informes o Explorador de datos.
Esta consulta del explorador de datos devolverá las visitas únicas de usuarios mensuales. ¿Es eso lo que quieres?
--[params]
-- date :start_date = 2021-01-01
-- date :end_date = 2021-12-31
-- string_list :group_name = staff,admins
WITH not_allowed_groups AS (
SELECT gu.user_id FROM group_users gu
INNER JOIN groups g ON (g.id = gu.group_id)
WHERE g.name IN (:group_name)
)
SELECT
to_char(date_trunc('month', uv.visited_at)::DATE,'YYYY-MM') AS "month",
date_trunc('month', uv.visited_at)::DATE AS date_user_visits,
COUNT(distinct uv.user_id) AS qtt_user_visits
FROM user_visits uv
WHERE
uv.user_id NOT IN (SELECT user_id FROM not_allowed_groups)
AND uv.visited_at::DATE BETWEEN :start_date AND :end_date
GROUP BY date_trunc('month', uv.visited_at)::DATE
ORDER BY date_trunc('month', uv.visited_at)::DATE
Muchas gracias, pero no exactamente… Nos gustaría el número de usuarios que están activos (registrados y no desactivados o eliminados) al final de cada mes natural, independientemente de si visitaron la plataforma ese mes o no.
Estamos viendo la gráfica de registros, pero la suma de registros durante un mes determinado no refleja nuestra base de usuarios porque cada mes también tenemos desactivaciones automáticas por inactividad. Me pregunto si la única forma es acceder a exportaciones retrospectivas de listas de usuarios para fechas específicas. Pero como estamos alojados en la nube, no sé si tenemos acceso a eso. ¿Necesitaríamos solicitarlo a Discourse?
Soy Discourse. 
Puedes preguntar aquí o enviarnos un correo electrónico directamente a team@discourse.org. Alguien con mejores conocimientos de SQL podrá ayudar.
4 Me gusta
No me di cuenta, ya que soy nuevo en este foro, ¡gracias! ¿Tiene copias de seguridad históricas almacenadas de sitios alojados en la nube que pueda proporcionar a petición?
1 me gusta
Almacenamos copias de seguridad para fines de continuidad del negocio y se pueden restaurar en casos de emergencia, pero no realmente por otras razones.
1 me gusta
Ok, tengo otra idea. Le pregunté a nuestro asistente de IA.
Consulta SQL para usuarios activos por mes IA
HAWK:
Escríbeme una consulta de explorador de datos que devuelva una lista de cuentas de usuario que todavía están activas y se registraron dentro de un mes determinado.
DiscourseHelper:
Buscando
Buscando: Consulta de explorador de datos de Discourse para encontrar usuarios activos registrados dentro de un mes específico
Leyendo
Para crear una consulta de explorador de datos que devuelva una lista de cuentas de usuario que todavía están activas y se registraron dentro de un mes específico, puedes usar la siguiente consulta SQL. Esta consulta asume que un usuario activo es alguien que ha realizado una publicación o respuesta dentro de un período de tiempo determinado.
--[params]
-- int :month = 5
-- int :year = 2023
SELECT u.id, u.username, u.created_at, u.last_seen_at
FROM users u
WHERE u.active = TRUE
AND EXTRACT(MONTH FROM u.created_at) = :month
AND EXTRACT(YEAR FROM u.created_at) = :year
AND u.last_seen_at IS NOT NULL -- Asumiendo que activo significa que accedieron al sitio recientemente
ORDER BY u.created_at
Explicación de la consulta:
:monthy:yearson parámetros que puedes establecer para filtrar por el mes y año específicos.u.active = TRUEasegura que la cuenta de usuario todavía esté activa.EXTRACT(MONTH FROM u.created_at) = :monthyEXTRACT(YEAR FROM u.created_at) = :yearfiltran la fecha de registro al mes y año especificados.u.last_seen_at IS NOT NULLconsidera al usuario activo si ha iniciado sesión en algún momento.- El resultado proporciona el ID de usuario, nombre de usuario, fecha de registro y fecha de último acceso.
No dudes en ajustar los parámetros y campos según tus necesidades. ¡Avísame si necesitas más ayuda!
Siéntete libre de darle más contexto si me falta algo.
3 Me gusta
Esta es una explicación de consulta increíble, ¡gracias! Sin embargo, lamentablemente no estoy seguro de si es lo que estamos buscando.
Por ejemplo, si establezco los parámetros 01 y 2024, estoy mirando enero de este año.
Entiendo que la salida incluirá a todos los usuarios que se registraron en enero y que todavía están activos hoy. ¿Es correcto?
Pero lo que queremos es el número de personas que tuvieron u.active=TRUE cada mes desde el lanzamiento (no ahora). Luego podemos graficar y mostrar cómo ha cambiado nuestra base de usuarios mes a mes. Al final de cada mes, para nosotros esto sería:
Usuarios activos = Valor del mes anterior + nuevos registros - nuevas eliminaciones - nuevas desactivaciones
No veo nada sobre desactivaciones o fechas vinculadas a la variable activa en el Explorador de datos. El curso más probable entonces, puede ser usar los registros de acciones del personal y analizar por fecha, PERO no creo que la función de exportación esté funcionando para nosotros. Cuando exportamos los resultados del registro de acciones del personal, extrañamente obtenemos una hoja de cálculo con el contenido de diferentes publicaciones, no una tabla de acciones del personal. Si pudieran ayudarnos con esto, ¡quizás sería una solución alternativa! ¿Son conscientes de que otros tienen el mismo problema?
Una pregunta interesante.
Primero eché un vistazo al ejemplo aquí. Pero eso ignora a los usuarios eliminados. Solo obtienes el número de usuarios que se registraron en ese momento y todavía están, no aquellos que han sido eliminados en el ínterin.
Mi idea fue, por lo tanto, tomar el ID del usuario que se registró por última vez en el mes. Este es el número máximo posible de usuarios en ese momento. Luego se puede restar el número de usuarios eliminados. Sin embargo, las cuentas de bots (como forum-helper) tienen un ID negativo, pero se cuentan si se eliminan. (Pero esto es probablemente una desviación menor). Mi consulta fue:
-- [params]
-- date :start_date
-- date :end_date
WITH month_dates AS (
-- Generate end-of-month dates between the start and end date
SELECT DATE_TRUNC('month', generate_series)::date + INTERVAL '1 month' - INTERVAL '1 day' AS month_end
FROM generate_series(:start_date::date, :end_date::date, '1 month'::interval)
),
recent_user AS (
-- For each end-of-month date, find the most recent user created before that date
SELECT md.month_end,
(SELECT id
FROM users u
WHERE u.created_at < md.month_end
ORDER BY u.created_at DESC
LIMIT 1) AS user_max_id
FROM month_dates md
),
cumulative_deletion_count AS (
-- Calculate the cumulative deletions up to each end-of-month date
SELECT md.month_end,
(SELECT COUNT(*)
FROM user_histories uh
WHERE uh.action = 1 AND uh.updated_at < md.month_end) AS deletions_count
FROM month_dates md
)
SELECT
md.month_end,
ru.user_max_id,
cdc.deletions_count,
ru.user_max_id - cdc.deletions_count AS number_of_users
FROM
month_dates md
LEFT JOIN recent_user ru ON md.month_end = ru.month_end
LEFT JOIN cumulative_deletion_count cdc ON md.month_end = cdc.month_end
ORDER BY md.month_end
Pero lo que no tiene en cuenta es la (des)activación, que también se almacena en la tabla user_histories. Pero tal vez te sirva como punto de partida.
5 Me gusta