Análisis de Cohorte - Actividad Mensual de Usuarios

[SaraDev (Sara Devlaeminck)]

Este es un informe de análisis de cohortes de actividad de usuarios en versión SQL, para usar dentro del plugin Data Explorer.

El Informe de Análisis de Cohortes está diseñado para proporcionar a los administradores información sobre la participación de los usuarios a lo largo del tiempo. Al analizar la actividad de los usuarios agrupados por su mes de registro (cohortes), este informe rastrea el número de usuarios activos cada mes después del registro que cumplen con un criterio mínimo de actividad de publicación.

Este informe puede ser un recurso valioso para comprender la retención de usuarios, las tendencias de participación, evaluar la salud de la comunidad y la efectividad de las estrategias de crecimiento de la comunidad.

Informe de Análisis de Cohortes - Usuarios Activos por Mes

--[params]
-- date :start_date = 2023-01-01
-- int :min_posts_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 -- Use the start_date parameter to filter users
),
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
),
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 -- Filter users by the minimum posts per month
),
active_users AS (
    SELECT
        cohort,
        months_after_registration,
        COUNT(DISTINCT user_id) AS active_users
    FROM activity_counts
    GROUP BY cohort, 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 -- Aggregate to get the total users signed up for each cohort
    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

El informe opera segmentando a los usuarios en cohortes según el mes en que se unieron. Luego rastrea estas cohortes para ver cuántos usuarios permanecen activos en los meses siguientes, basándose en un número mínimo definido de publicaciones por mes.

Parámetros

Este informe tiene dos parámetros:

• start_date: La fecha de inicio a partir de la cual se consideran los usuarios para el análisis de cohortes. Los usuarios que se unieron después de esta fecha se incluyen en el informe.
• min_posts_per_month: El número mínimo de publicaciones que un usuario debe realizar en un mes para ser considerado activo durante ese mes.

CTEs

El Informe de Análisis de Cohortes utiliza varias Expresiones Comunes de Tabla (CTEs) para organizar y procesar datos para el análisis. Cada CTE cumple un propósito específico en la consulta general, basándose en las anteriores para producir finalmente el informe final. Aquí hay un desglose de cómo opera cada CTE:

1. user_cohorts

Esta CTE identifica las cohortes basándose en el mes en que los usuarios se unieron. Para cada usuario, calcula la cohorte a la que pertenece truncando su marca de tiempo created_at al mes. También cuenta el número de usuarios que se registraron en cada cohorte.

• Operaciones Clave:
  • DATE_TRUNC('month', created_at) AS cohort: Trunca la marca de tiempo created_at a la granularidad del mes, agrupando efectivamente a los usuarios por su mes de registro.
  • COUNT(id) OVER (PARTITION BY DATE_TRUNC('month', created_at)): Cuenta el número de usuarios en cada cohorte.

2. posts_activity

Esta CTE rastrea la actividad de publicación de los usuarios en relación con su fecha de registro. Une las tablas posts y users para asociar cada publicación con el usuario que la realizó y calcula cuántos meses han pasado desde el registro del usuario en el momento de cada publicación.

• Operaciones Clave:
  • EXTRACT(YEAR FROM AGE(p.created_at, u.created_at)) * 12 + EXTRACT(MONTH FROM AGE(p.created_at, u.created_at)): Calcula el número de meses transcurridos desde el registro del usuario para cada publicación.
  • DATE_TRUNC('month', u.created_at) AS cohort: Identifica la cohorte del usuario basándose en su mes de registro.

3. activity_counts

Esta CTE agrega la actividad de publicación de posts_activity para contar el número de publicaciones que cada usuario realizó en cada mes después del registro. Filtra estos recuentos para incluir solo a los usuarios que cumplen con la actividad mínima de publicación especificada por el parámetro min_posts_per_month.

• Operaciones Clave:
  • GROUP BY cohort, months_after_registration, user_id: Agrupa los datos por cohorte, meses después del registro y ID de usuario para prepararlos para contar las publicaciones.
  • HAVING COUNT(user_id) >= :min_posts_per_month: Filtra los datos agrupados para incluir solo a los usuarios que realizaron al menos el número mínimo de publicaciones en un mes.

4. active_users

Esta CTE agrega aún más los datos de activity_counts para contar el número de usuarios activos distintos en cada cohorte para cada mes después del registro.

• Operaciones Clave:
  • COUNT(DISTINCT user_id) AS active_users: Cuenta el número de usuarios activos únicos en cada cohorte para cada mes después del registro.

5. cohorts_series

Esta CTE genera una serie de enteros del 0 al 11, que representan los meses posteriores al registro. Esta serie se utiliza para garantizar que el informe final incluya todos los meses hasta 12 para cada cohorte, incluso si no hay datos de actividad para algunos meses.

• Operaciones Clave:
  • generate_series(0, 11): Genera una serie de enteros del 0 al 11.

6. cohorts

Esta CTE agrega los datos de user_cohorts para obtener el número total de usuarios registrados para cada cohorte.

• Operaciones Clave:
  • MAX(users_signed_up) AS users_signed_up: Agrega el número total de usuarios registrados para cada cohorte.

7. cross_join

Esta CTE realiza una unión cruzada entre cohorts y cohorts_series para crear una cuadrícula de todas las combinaciones posibles de cohortes y meses posteriores al registro. Esto garantiza que el informe final incluya filas para cada mes para cada cohorte, facilitando el cálculo de usuarios activos por mes.

8. final_counts

Esta CTE combina los datos de cross_join y active_users para calcular el recuento final de usuarios activos para cada cohorte para cada mes posterior al registro. Utiliza una unión izquierda para garantizar que se incluyan todas las combinaciones de cohortes y meses, incluso si no hay usuarios activos para algunos.

• Operaciones Clave:
  • COALESCE(au.active_users, 0) AS active_users: Asegura que el informe muestre 0 usuarios activos para combinaciones sin actividad, en lugar de dejarlas en blanco.

La SELECT final fuera de las CTEs luego formatea y presenta estos datos, mostrando el número de usuarios registrados y el número de usuarios activos para cada mes posterior al registro para cada cohorte.

Resultados

El informe genera una tabla con las siguientes columnas:

• Joined In: El mes y año en que se creó la cohorte, lo que indica cuándo se registraron estos usuarios.
• Users Signed Up: El número total de usuarios que se registraron en esa cohorte.
• Month 1 a Month 12: Cada una de estas columnas representa el número de usuarios activos para la cohorte en cada mes subsiguiente después de unirse, hasta 12 meses. Un usuario activo se define como alguien que ha realizado al menos el número mínimo de publicaciones especificado por el parámetro min_posts_per_month.

Resultados de Ejemplo

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	120	40	8	4	3	3	3	4	3	2	1	1	4
Feb 2023	119	40	7	5	3	2	2	7	2	2	2	1	1
…	…	…	…	…	…	…	…	…	…	…	…	…	…

Los resultados completos del informe mostrarán datos de un año después de la start_date.