Este é um relatório de Análise de Coorte em SQL para Atividade do Usuário, para uso dentro do plugin Data Explorer.
Este relatório requer que o plugin Discourse Solved esteja ativado.
Este relatório requer que o plugin Este relatório de Análise de Coorte fornece insights sobre o engajamento do usuário, rastreando a atividade de usuários que se inscreveram desde uma data de início especificada. Ele se concentra especificamente em usuários que atendem ou excedem certos limites para posts criados e soluções fornecidas a cada mês após o registro.
Este relatório é valioso para administradores que buscam entender a eficácia de sua comunidade em reter e engajar novos usuários ao longo do tempo, especificamente em relação à postagem e fornecimento de soluções para tópicos. Este relatório também pode ser útil para avaliar a saúde da comunidade e identificar a eficácia das estratégias de crescimento da comunidade.
Usuários Ativos por Mês após Inscrição com Parâmetros Mínimos de Posts + Soluções
--[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
Explicação da Consulta SQL
Parâmetros
Este relatório usa três parâmetros:
start_date: A data a partir da qual começar a rastrear novas inscrições de usuários.min_posts_per_month: O número mínimo de posts que um usuário deve fazer em um mês para ser considerado ativo.min_solutions_per_month: O número mínimo de soluções (respostas aceitas) que um usuário deve fornecer em um mês para ser considerado ativo.
Os usuários devem atender aos requisitos de min_posts_per_month e min_solutions_per_month para serem considerados ativos no mês.
CTEs
A consulta SQL fornecida usa várias Expressões de Tabela Comuns (CTEs) para detalhar o processo de cálculo de usuários ativos por mês após a inscrição, com base nos critérios mínimos de posts e soluções. Aqui está uma explicação de cada CTE:
user_cohorts
Esta CTE identifica as coortes de usuários com base em seu mês de inscrição. Para cada usuário, ela calcula o mês de sua inscrição (cohort) e conta o número total de usuários que se inscreveram no mesmo mês. Isso ajuda a entender o tamanho inicial de cada coorte.
posts_activity
Esta CTE rastreia a atividade dos usuários em termos de posts feitos após sua inscrição. Para cada post, ela calcula quantos meses se passaram desde a data de registro do usuário (months_after_registration) e agrupa isso pela coorte de inscrição do usuário. Isso é usado para rastrear o quão ativos os usuários estão em termos de postagem de conteúdo ao longo do tempo.
solutions_counts
Esta CTE se concentra na contagem de soluções (respostas aceitas) fornecidas pelos usuários. Ela filtra posts marcados como soluções e os conta para cada usuário, garantindo que apenas posts feitos após a inscrição do usuário sejam considerados. Ela também calcula quantos meses se passaram desde a inscrição do usuário para cada solução. Os usuários são incluídos nesta contagem apenas se atenderem ou excederem o número mínimo especificado de soluções por mês.
activity_counts
Esta CTE agrega o número de posts feitos por cada usuário por mês após o registro. Ela agrupa os usuários por sua coorte de inscrição e o número de meses desde o registro, e então conta o número de posts feitos. Apenas os usuários que atendem ou excedem o número mínimo especificado de posts por mês são incluídos nesta contagem.
active_users
Esta CTE combina os dados de solutions_counts e activity_counts para identificar usuários ativos — aqueles que atendem a ambos os critérios de posts e soluções. Ela conta usuários distintos que estão ativos com base nos critérios definidos para posts e soluções, agrupados por sua coorte de inscrição e o número de meses desde o registro.
cohorts_series
Esta CTE gera uma série de números de 0 a 11, representando o número de meses após o registro. Isso é usado para garantir que o relatório final inclua dados para cada mês até 12 meses, mesmo que não haja usuários ativos em alguns meses.
cohorts
Esta CTE agrega os dados de user_cohorts para obter o número total de usuários que se inscreveram em cada coorte. Ela garante que o relatório final inclua o número total de usuários inscritos para cada coorte.
cross_join
Esta CTE realiza um cross join entre a CTE cohorts e a CTE cohorts_series. Isso garante que cada coorte seja representada para cada mês após a inscrição, facilitando o cálculo de usuários ativos para cada mês na etapa final.
final_counts
Esta CTE combina todas as CTEs anteriores para calcular as contagens finais de usuários ativos para cada mês após a inscrição, para cada coorte. Ela usa um left join para corresponder aos usuários ativos da CTE active_users com as coortes e meses gerados na CTE cross_join. Ela garante que cada par coorte-mês tenha uma contagem de usuários ativos, com valor padrão 0 se não houver usuários ativos para esse par.
SELECT Final
A instrução SELECT final na consulta agrupa os usuários em coortes com base em seu mês e ano de inscrição e, em seguida, calcula o número de usuários ativos para cada mês até um ano após a inscrição. Isso é feito por meio de uma combinação de transformações e agregações condicionais, que pivotam os dados em um formato onde cada linha corresponde a uma coorte e cada coluna representa o número de usuários ativos para cada mês após a inscrição, de “Mês 1” a “Mês 12”.
Resultados
O relatório gera uma tabela com as seguintes colunas:
- Joined In: O mês e ano da coorte (quando os usuários se inscreveram).
- Users Signed Up: O número total de usuários que se inscreveram naquela coorte.
- Month 1 a Month 12: O número de usuários ativos para cada mês após a inscrição, até 12 meses.
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 | 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 |
| … | … | … | … | … | … | … | … | … | … | … | … | … | … |
Os resultados completos do relatório gerarão dados de um ano após a start_date.