Olá! Estamos usando a versão hospedada na nuvem do Discourse e estamos executando nossa plataforma há cerca de 1,5 ano. Gostaríamos de extrair algumas métricas históricas sobre a adoção e o uso da plataforma. Já estamos analisando o relatório ‘Inscrições’ disponível na aba Relatórios do Administrador, mas também queremos poder mostrar o número total de usuários ativos a cada mês. Alguém tem alguma ideia de como posso fazer isso? Não consigo encontrar nada apropriado em Relatórios ou Explorador de Dados.
Esta consulta do explorador de dados retornará as visitas únicas de usuários mensais. É isso que você deseja?
--[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
Muito obrigado, mas não exatamente… Gostaríamos do número de usuários ativos (cadastrados e não desativados ou excluídos) no final de cada mês civil, independentemente de terem visitado a plataforma naquele mês ou não.
Estamos olhando para o gráfico de cadastros, mas a soma de cadastros durante um determinado mês não reflete nossa base de usuários porque a cada mês também temos desativações automáticas por inatividade. Eu me pergunto se a única maneira é acessar exportações de listas de usuários retrospectivas para datas específicas? Mas como somos hospedados na nuvem, não sei se temos acesso a isso. Precisaríamos solicitar ao Discourse?
Eu sou o Discourse. 
Você pode perguntar aqui ou nos enviar um e-mail diretamente para team@discourse.org. Alguém com melhor conhecimento de SQL poderá ajudar.
4 curtidas
Não percebi, pois sou novo neste fórum, obrigado! Você por acaso tem backups históricos armazenados de sites hospedados na nuvem que poderia fornecer mediante solicitação?
1 curtida
Armazenamos backups para fins de continuidade de negócios e eles podem ser restaurados em casos de emergência, mas não realmente por outros motivos.
1 curtida
Ok, tenho outra ideia. Perguntei ao nosso assistente de IA.
Consulta SQL para Usuários Ativos por Mês IA
HAWK:
Escreva uma consulta do explorador de dados que retorne uma lista de contas de usuário que ainda estão ativas e foram registradas dentro de um determinado mês
DiscourseHelper:
Pesquisando
Pesquisando por: Consulta do Discourse Data Explorer para encontrar usuários ativos registrados em um mês específico
Lendo
Para criar uma consulta do Data Explorer que retorne uma lista de contas de usuário que ainda estão ativas e foram registradas dentro de um determinado mês, você pode usar a seguinte consulta SQL. Esta consulta assume que um usuário ativo é alguém que fez uma postagem ou resposta dentro de um determinado período de tempo.
--[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 -- Assumindo que ativo significa que eles acessaram o site recentemente
ORDER BY u.created_at
Explicação da Consulta:
:monthe:yearsão parâmetros que você pode definir para filtrar o mês e o ano específicos.u.active = TRUEgarante que a conta do usuário ainda esteja ativa.EXTRACT(MONTH FROM u.created_at) = :montheEXTRACT(YEAR FROM u.created_at) = :yearfiltram a data de registro para o mês e ano especificados.u.last_seen_at IS NOT NULLconsidera o usuário ativo se ele fez login a qualquer momento.- O resultado fornece o ID do usuário, nome de usuário, data de registro e data da última visualização.
Sinta-se à vontade para ajustar os parâmetros e campos de acordo com suas necessidades. Me diga se precisar de mais alguma ajuda!
Sinta-se à vontade para dar mais contexto se eu estiver perdendo alguma coisa.
3 curtidas
Esta é uma explicação de consulta incrível, obrigado! No entanto, infelizmente, não tenho certeza se é o que estamos procurando.
Por exemplo, se eu definir os parâmetros 01 e 2024, estarei olhando para janeiro deste ano.
Entendo que a saída incluirá todos os usuários que se inscreveram em janeiro e ainda estão ativos hoje. Isso está correto?
Mas o que queremos é o número de pessoas que tiveram u.active=TRUE a cada mês desde o lançamento (não agora). Então, podemos plotar e mostrar como nossa base de usuários mudou de mês a mês. No final de todo mês, para nós, isso seria:
Usuários ativos = Valor do mês anterior + novos cadastros - novas exclusões - novas desativações
Não vejo nada sobre desativações ou datas associadas à variável ativa no Data Explorer. O curso mais provável, então, pode ser usar os logs de ações da equipe e analisar por data, MAS não acho que a função de exportação esteja funcionando para nós. Quando exportamos os resultados do log de ações da equipe, estranhamente obtemos uma planilha com o conteúdo de diferentes postagens, não uma tabela de ações da equipe. Se vocês pudessem nos ajudar com isso, talvez essa fosse uma solução alternativa! Vocês estão cientes de que outros estão tendo o mesmo problema?
Uma pergunta interessante.
Primeiro dei uma olhada no exemplo aqui. Mas isso ignora usuários excluídos. Você só obtém o número de usuários que foram registrados naquela época e ainda são, não aqueles que foram excluídos no ínterim.
Minha ideia foi, portanto, pegar o ID do usuário que se registrou por último no mês. Este é o número máximo possível de usuários naquela época. O número de usuários excluídos pode então ser subtraído disso. No entanto, contas de bot (como forum-helper) têm um ID negativo, mas são contadas se forem excluídas. (Mas isso provavelmente é um desvio menor). Minha consulta foi:
-- [params]
-- date :start_date
-- date :end_date
WITH month_dates AS (
-- Gerar datas de fim de mês entre a data de início e a data de fim
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 (
-- Para cada data de fim de mês, encontrar o usuário mais recente criado antes dessa data
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 (
-- Calcular as exclusões cumulativas até cada data de fim de mês
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
Mas o que ele não leva em conta é a (des)ativação, que também são armazenadas na tabela user_histories. Mas talvez isso ajude você como um ponto de partida.
5 curtidas