Relatório de Análise de Coorte - Atividade Mensal do Usuário

Dados e relatórios
1

Este é um relatório de Análise de Coorte em versão SQL para Atividade do Usuário, para uso dentro do plugin Data Explorer.

O Relatório de Análise de Coorte foi projetado para fornecer aos administradores insights sobre o engajamento do usuário ao longo do tempo. Ao analisar a atividade de usuários agrupados por seu mês de registro (coortes), este relatório rastreia o número de usuários ativos a cada mês pós-registro que atendem a um critério mínimo de atividade de postagem.

Este relatório pode ser um recurso valioso para entender a retenção de usuários, tendências de engajamento, avaliar a saúde da comunidade e identificar a eficácia das estratégias de crescimento da comunidade.

Relatório de Análise de Coorte - Usuários Ativos por Mês

--[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 o parâmetro start_date para filtrar usuários
),
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 -- Filtra usuários pelo número mínimo de posts por mês
),
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 -- Agrega para obter o total de usuários inscritos para cada coorte
    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", -- Inclui o ano na coluna Joined In
    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

Explicação da Consulta SQL

O relatório opera segmentando usuários em coortes com base no mês em que ingressaram. Em seguida, ele rastreia essas coortes para ver quantos usuários permanecem ativos nos meses subsequentes, com base em um número mínimo definido de postagens por mês.

Parâmetros

Este relatório possui dois parâmetros:

  • start_date: A data de início a partir da qual os usuários são considerados para análise de coorte. Usuários que ingressaram após esta data são incluídos no relatório.
  • min_posts_per_month: O número mínimo de postagens que um usuário deve fazer em um mês para ser considerado ativo naquele mês.

CTEs

O Relatório de Análise de Coorte utiliza várias Expressões de Tabela Comuns (CTEs) para organizar e processar dados para análise. Cada CTE serve a um propósito específico na consulta geral, construindo sobre as anteriores para, finalmente, produzir o relatório final. Aqui está uma análise de como cada CTE opera:

1. user_cohorts

Esta CTE identifica as coortes com base no mês em que os usuários ingressaram. Para cada usuário, ela calcula a coorte à qual pertence, truncando seu timestamp created_at para o mês. Ela também conta o número de usuários que se inscreveram em cada coorte.

  • Operações Principais:
    • DATE_TRUNC('month', created_at) AS cohort: Trunca o timestamp created_at para granularidade mensal, agrupando efetivamente os usuários por seu mês de inscrição.
    • COUNT(id) OVER (PARTITION BY DATE_TRUNC('month', created_at)): Conta o número de usuários em cada coorte.

2. posts_activity

Esta CTE rastreia a atividade de postagem dos usuários em relação à sua data de registro. Ela une as tabelas posts e users para associar cada postagem ao usuário que a fez e calcula quantos meses se passaram desde o registro do usuário no momento de cada postagem.

  • Operações Principais:
    • EXTRACT(YEAR FROM AGE(p.created_at, u.created_at)) * 12 + EXTRACT(MONTH FROM AGE(p.created_at, u.created_at)): Calcula o número de meses que se passaram desde o registro do usuário para cada postagem.
    • DATE_TRUNC('month', u.created_at) AS cohort: Identifica a coorte do usuário com base em seu mês de registro.

3. activity_counts

Esta CTE agrega a atividade de postagem de posts_activity para contar o número de postagens que cada usuário fez em cada mês após o registro. Ela filtra essas contagens para incluir apenas usuários que atendem à atividade mínima de postagem especificada pelo parâmetro min_posts_per_month.

  • Operações Principais:
    • GROUP BY cohort, months_after_registration, user_id: Agrupa os dados por coorte, meses após o registro e ID do usuário para preparar a contagem de postagens.
    • HAVING COUNT(user_id) >= :min_posts_per_month: Filtra os dados agrupados para incluir apenas usuários que fizeram pelo menos o número mínimo de postagens em um mês.

4. active_users

Esta CTE agrega ainda mais os dados de activity_counts para contar o número de usuários ativos distintos em cada coorte para cada mês após o registro.

  • Operações Principais:
    • COUNT(DISTINCT user_id) AS active_users: Conta o número de usuários ativos únicos em cada coorte para cada mês após o registro.

5. cohorts_series

Esta CTE gera uma série de inteiros de 0 a 11, representando os meses após o registro. Esta série é usada para garantir que o relatório final inclua todos os meses até 12 para cada coorte, mesmo que não haja dados de atividade para alguns meses.

  • Operações Principais:
    • generate_series(0, 11): Gera uma série de inteiros de 0 a 11.

6. cohorts

Esta CTE agrega os dados de user_cohorts para obter o número total de usuários inscritos para cada coorte.

  • Operações Principais:
    • MAX(users_signed_up) AS users_signed_up: Agrega o número total de usuários inscritos para cada coorte.

7. cross_join

Esta CTE realiza um cross join entre cohorts e cohorts_series para criar uma grade de todas as combinações possíveis de coortes e meses após o registro. Isso garante que o relatório final inclua linhas para cada mês para cada coorte, facilitando o cálculo de usuários ativos por mês.

8. final_counts

Esta CTE combina os dados de cross_join e active_users para calcular a contagem final de usuários ativos para cada coorte em cada mês após o registro. Ela usa um left join para garantir que todas as combinações de coortes e meses sejam incluídas, mesmo que não haja usuários ativos para alguns.

  • Operações Principais:
    • COALESCE(au.active_users, 0) AS active_users: Garante que o relatório mostre 0 usuários ativos para combinações sem atividade, em vez de deixá-las em branco.

A instrução SELECT final fora das CTEs, em seguida, formata e apresenta esses dados, mostrando o número de usuários inscritos e o número de usuários ativos para cada mês após o registro para cada coorte.

Resultados

O relatório gera uma tabela com as seguintes colunas:

  • Joined In: O mês e ano em que a coorte foi criada, indicando quando esses usuários se inscreveram.
  • Users Signed Up: O número total de usuários que se inscreveram naquela coorte.
  • Month 1 a Month 12: Cada uma dessas colunas representa o número de usuários ativos para a coorte em cada mês subsequente após o ingresso, até 12 meses. Um usuário ativo é definido como alguém que fez pelo menos o número mínimo de postagens especificado pelo parâmetro min_posts_per_month.

Exemplo 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 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
… … … … … … … … … … … … … …

Os resultados completos do relatório produzirão um ano de dados após a start_date.

3 curtidas