Este es un informe de análisis de cohortes de actividad de usuarios en versión SQL, para usar dentro del plugin Data Explorer.
Este informe requiere que el plugin Discourse Solved esté habilitado.
Este informe requiere que el plugin Este informe de análisis de cohortes proporciona información sobre la participación de los usuarios al rastrear la actividad de los usuarios que se han registrado desde una fecha de inicio especificada. Se enfoca específicamente en los usuarios que cumplen o superan ciertos umbrales de publicaciones creadas y soluciones proporcionadas cada mes después de su registro.
Este informe es valioso para los administradores que buscan comprender la efectividad de su comunidad para retener y atraer a nuevos usuarios con el tiempo, específicamente en relación con la publicación y la provisión de soluciones a temas. Este informe también puede ser útil para evaluar la salud de la comunidad y la efectividad de las estrategias de crecimiento de la comunidad.
Usuarios Activos por Mes después del Registro con Parámetros Mínimos de Publicaciones + Soluciones
--[params]
-- date :start_date = 2023-01-01
-- int :min_posts_per_month = 1
-- int :min_solutions_per_month = 1
WITH user_cohorts AS (
SELECT
id AS user_id,
DATE_TRUNC('month', created_at) AS cohort,
COUNT(id) OVER (PARTITION BY DATE_TRUNC('month', created_at)) AS users_signed_up
FROM users
WHERE created_at >= :start_date
),
posts_activity AS (
SELECT
p.user_id,
EXTRACT(YEAR FROM AGE(p.created_at, u.created_at)) * 12 + EXTRACT(MONTH FROM AGE(p.created_at, u.created_at)) AS months_after_registration,
DATE_TRUNC('month', u.created_at) AS cohort
FROM posts p
JOIN users u ON p.user_id = u.id
WHERE p.created_at >= u.created_at
),
solutions_counts AS (
SELECT
p.user_id,
COUNT(p.user_id) as solutions_count,
EXTRACT(YEAR FROM AGE(p.created_at, u.created_at)) * 12 + EXTRACT(MONTH FROM AGE(p.created_at, u.created_at)) AS months_after_registration,
DATE_TRUNC('month', u.created_at) AS cohort
FROM discourse_solved_solved_topics dsst
INNER JOIN posts p ON p.id = dsst.answer_post_id
JOIN topics t ON t.id = p.topic_id
JOIN users u ON p.user_id = u.id
WHERE p.created_at >= u.created_at
GROUP BY months_after_registration, cohort, p.user_id
HAVING COUNT(p.user_id) >= :min_solutions_per_month
),
activity_counts AS (
SELECT
cohort,
months_after_registration,
COUNT(user_id) AS posts_count,
user_id
FROM posts_activity
GROUP BY cohort, months_after_registration, user_id
HAVING COUNT(user_id) >= :min_posts_per_month
),
active_users AS (
SELECT
sc.cohort,
sc.months_after_registration,
COUNT(DISTINCT ac.user_id) AS active_users
FROM solutions_counts sc
FULL JOIN activity_counts ac ON sc.user_id = ac.user_id
AND sc.months_after_registration = ac.months_after_registration
AND sc.cohort = ac.cohort
GROUP BY sc.cohort, sc.months_after_registration
),
cohorts_series AS (
SELECT generate_series AS months_after_registration
FROM generate_series(0, 11)
),
cohorts AS (
SELECT
cohort,
MAX(users_signed_up) AS users_signed_up
FROM user_cohorts
GROUP BY cohort
),
cross_join AS (
SELECT
c.cohort,
c.users_signed_up,
cs.months_after_registration
FROM cohorts c
CROSS JOIN cohorts_series cs
),
final_counts AS (
SELECT
cj.cohort,
cj.users_signed_up,
cj.months_after_registration,
COALESCE(au.active_users, 0) AS active_users
FROM cross_join cj
LEFT JOIN active_users au ON au.cohort = cj.cohort AND au.months_after_registration = cj.months_after_registration
)
SELECT
TO_CHAR(cohort, 'Mon YYYY') AS "Joined In", -- Include the year in the Joined In column
users_signed_up AS "Users Signed Up",
MAX(CASE WHEN months_after_registration = 0 THEN active_users END) AS "Month 1",
MAX(CASE WHEN months_after_registration = 1 THEN active_users END) AS "Month 2",
MAX(CASE WHEN months_after_registration = 2 THEN active_users END) AS "Month 3",
MAX(CASE WHEN months_after_registration = 3 THEN active_users END) AS "Month 4",
MAX(CASE WHEN months_after_registration = 4 THEN active_users END) AS "Month 5",
MAX(CASE WHEN months_after_registration = 5 THEN active_users END) AS "Month 6",
MAX(CASE WHEN months_after_registration = 6 THEN active_users END) AS "Month 7",
MAX(CASE WHEN months_after_registration = 7 THEN active_users END) AS "Month 8",
MAX(CASE WHEN months_after_registration = 8 THEN active_users END) AS "Month 9",
MAX(CASE WHEN months_after_registration = 9 THEN active_users END) AS "Month 10",
MAX(CASE WHEN months_after_registration = 10 THEN active_users END) AS "Month 11",
MAX(CASE WHEN months_after_registration = 11 THEN active_users END) AS "Month 12"
FROM final_counts
GROUP BY cohort, users_signed_up
ORDER BY cohort
Explicación de la Consulta SQL
Parámetros
Este informe utiliza tres parámetros:
start_date: La fecha desde la cual se comenzará a rastrear los registros de nuevos usuarios.min_posts_per_month: El número mínimo de publicaciones que un usuario debe realizar en un mes para ser considerado activo.min_solutions_per_month: El número mínimo de soluciones (respuestas aceptadas) que un usuario debe proporcionar en un mes para ser considerado activo.
Los usuarios deben cumplir tanto con los requisitos de min_posts_per_month como con los de min_solutions_per_month para ser considerados activos durante el mes.
CTEs
La consulta SQL proporcionada utiliza varias Expresiones Comunes de Tabla (CTEs) para desglosar el proceso de cálculo de usuarios activos por mes después del registro, basándose en los criterios mínimos de publicaciones y soluciones. Aquí hay una explicación de cada CTE:
user_cohorts
Esta CTE identifica las cohortes de usuarios según su mes de registro. Para cada usuario, calcula el mes de su registro (cohort) y cuenta el número total de usuarios que se registraron en ese mismo mes. Esto ayuda a comprender el tamaño inicial de cada cohorte.
posts_activity
Esta CTE rastrea la actividad de los usuarios en términos de publicaciones realizadas después de su registro. Para cada publicación, calcula cuántos meses han transcurrido desde la fecha de registro del usuario (months_after_registration) y agrupa esto por la cohorte de registro del usuario. Esto se utiliza para rastrear cuán activos son los usuarios en términos de publicación de contenido a lo largo del tiempo.
solutions_counts
Esta CTE se enfoca en contar las soluciones (respuestas aceptadas) proporcionadas por los usuarios. Filtra las publicaciones marcadas como soluciones y las cuenta para cada usuario, asegurando que solo se consideren las publicaciones realizadas después del registro del usuario. También calcula cuántos meses han transcurrido desde el registro del usuario para cada solución. Los usuarios se incluyen en este recuento solo si cumplen o superan el número mínimo especificado de soluciones por mes.
activity_counts
Esta CTE agrega el número de publicaciones realizadas por cada usuario por mes después del registro. Agrupa a los usuarios por su cohorte de registro y el número de meses desde el registro, y luego cuenta el número de publicaciones realizadas. Solo se incluyen en este recuento los usuarios que cumplen o superan el número mínimo especificado de publicaciones por mes.
active_users
Esta CTE combina los datos de solutions_counts y activity_counts para identificar a los usuarios activos, aquellos que cumplen ambos criterios de publicaciones y soluciones. Cuenta los usuarios distintos que están activos según los criterios establecidos para publicaciones y soluciones, agrupados por su cohorte de registro y el número de meses desde el registro.
cohorts_series
Esta CTE genera una serie de números del 0 al 11, que representan el número de meses después del registro. Esto se utiliza para garantizar que el informe final incluya datos para cada mes hasta 12 meses, incluso si no hay usuarios activos en algunos meses.
cohorts
Esta CTE agrega los datos de user_cohorts para obtener el número total de usuarios que se registraron en cada cohorte. Asegura que el informe final incluya el número total de usuarios registrados para cada cohorte.
cross_join
Esta CTE realiza una unión cruzada entre la CTE cohorts y la CTE cohorts_series. Esto asegura que cada cohorte se represente para cada mes después del registro, facilitando el cálculo de usuarios activos para cada mes en el paso final.
final_counts
Esta CTE combina todas las CTEs anteriores para calcular los recuentos finales de usuarios activos para cada mes después del registro, para cada cohorte. Utiliza una unión izquierda para hacer coincidir a los usuarios activos de la CTE active_users con las cohortes y meses generados en la CTE cross_join. Asegura que cada par cohorte-mes tenga un recuento de usuarios activos, con un valor predeterminado de 0 si no hay usuarios activos para ese par.
SELECT final
La declaración SELECT final en la consulta agrupa a los usuarios en cohortes según su mes y año de registro, y luego calcula el número de usuarios activos para cada mes hasta un año después del registro. Esto se hace a través de una combinación de transformaciones y agregaciones condicionales, que giran los datos a un formato donde cada fila corresponde a una cohorte, y cada columna representa el número de usuarios activos para cada mes después del registro, desde “Mes 1” hasta “Mes 12”.
Resultados
El informe presenta una tabla con las siguientes columnas:
- Joined In: El mes y año de la cohorte (cuando los usuarios se registraron).
- Users Signed Up: El número total de usuarios que se registraron en esa cohorte.
- Month 1 a Month 12: El número de usuarios activos para cada mes después del registro, hasta 12 meses.
Ejemplo de Resultados
| Joined In | Users Signed Up | Month 1 | Month 2 | Month 3 | Month 4 | Month 5 | Month 6 | Month 7 | Month 8 | Month 9 | Month 10 | Month 11 | Month 12 |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Jan 2023 | 50 | 40 | 8 | 4 | 3 | 3 | 3 | 4 | 3 | 2 | 1 | 1 | 4 |
| Feb 2023 | 63 | 40 | 7 | 5 | 3 | 2 | 2 | 7 | 2 | 2 | 2 | 1 | 1 |
| … | … | … | … | … | … | … | … | … | … | … | … | … | … |
Los resultados completos del informe mostrarán un año de datos después de la start_date.