Requisito complexo de relatório de atividade do usuário

Comunidade Dados e relatórios
1

Estou procurando uma maneira de fazer o Discourse gerar relatórios de atividade na forma de um resumo de toda a atividade dos usuários entre duas datas personalizadas a cada ano (a funcionalidade integrada atual parece ter apenas intervalos predefinidos e apenas em relação à data de hoje) e salvar os resultados em um arquivo compatível com o Excel (atualmente, isso parece ser possível apenas para relatórios de atividade de usuários individuais).

  • Gostaria também de incluir mensagens enviadas por e-mail e respondidas por e-mail (sem contar duas vezes os mesmos posts lidos ou publicados enquanto se usa o fórum online). Os relatórios atuais parecem excluir posts via e-mail.

  • Gostaria de filtrar esses resultados com base em um determinado campo personalizado do perfil do usuário (um número de identificação exclusivo).

  • Gostaria de excluir resultados de usuários cujos números de identificação não estejam dentro de um intervalo numérico especificado.

  • Idealmente, também gostaria de ter a capacidade de gerar uma pontuação de atividade por usuário para o período, ponderada de acordo com os posts lidos, posts feitos e posts que receberam curtidas.

  • As ponderações (multiplicadores) para cada uma dessas variáveis de atividade devem ser ajustáveis e definidas pelo administrador. Os resultados devem ser arredondados para baixo para o múltiplo de 5 mais próximo e limitados a um número máximo de pontos predefinido, definido pelo administrador.

  • Seria interessante ter algum tipo de detalhamento por categoria de tópico ou tag por usuário.

  • Idealmente, esse relatório seria gerado automaticamente e enviado por e-mail para mim em horários específicos a cada ano (isso seria a cereja do bolo, embora).

Qual a viabilidade de tudo isso?

Seria necessário um novo plugin personalizado para implementar, ou é possível por meio de alguma consulta SQL avançada no plugin Data Explorer atual?

Ou a escolha mais inteligente seria buscar uma opção de exportação “tudo” relativamente simples e tentar fazer o restante usando o Excel?

2

O Explorador de Dados provavelmente seria a melhor abordagem aqui. Você pode precisar criar consultas diferentes para cada tarefa (por exemplo, a análise por usuário seria uma consulta distinta).

A única coisa que o Explorador de Dados não consegue fazer é o ponto de “gerado automaticamente e enviado por e-mail para mim”. Se isso for um requisito, você poderia potencialmente fazê-lo chamando a API do Explorador de Dados a partir de algum outro sistema.

3

Obrigado, David.
Parece que vou precisar me familiarizar com consultas SQL.

Uma consulta pode receber como entrada a saída filtrada de outra consulta? Além disso, é possível incluir definitivamente a interação baseada em listas de e-mail? Isso é armazenado de alguma forma diferente? Eu estava preocupado que tivesse sido excluído dos relatórios de atividade padrão por algum motivo obscuro, mas insuperável.

4

Não, você teria que copiar/colar trechos da consulta.

A tabela posts no banco de dados inclui um campo booleano via_email, então sim, é possível identificar quais posts foram criados por e-mail :+1:

No entanto, o Discourse não inclui nenhum rastreador nos e-mails que envia, portanto não haverá como determinar se uma notificação por e-mail foi ‘lida’ ou não.

5

A funcionalidade de relatórios de atividade do usuário ‘integrada’ é tratada por meio de consultas SQL que posso copiar de algum lugar e modificar? — para que eu não acabe gastando semanas reinventando a roda?

6

Você está olhando para o relatório de “usuários engajados diariamente”? Ele é gerado com essa lógica, que usa ActiveRecord (portanto, sem SQL puro). Mas ainda assim, a lógica pode ser um bom ponto de partida.

7

Meu objetivo é gerar um relatório de atividade por usuário, no formato CSV, abrangendo um período de datas específico. O objetivo final é atribuir a cada usuário uma pontuação de atividade para o ano (ou outro período) com base nas mensagens recebidas/lidas online ou por e-mail, nas mensagens postadas online ou por e-mail, e onde mensagens com curtidas recebem mais pontos. O relatório que eu estava pensando em usar como base é o primeiro que aparece ao clicar em Admin/Usuários, pois ele já faz grande parte do que eu preciso.

image
image868×321 44.3 KB

8

A lógica do diretório de usuários pode ser encontrada aqui: discourse/app/models/directory_item.rb at 1e66e4602f408d7b2d1105b6c228542dce7f4b38 · discourse/discourse · GitHub

9

Hmm, não parece ser uma tarefa trivial de replicar via SQL (pelo menos não para mim), já que precisarei aprender o suficiente de SQL para fazê-lo do zero ao mesmo tempo, caso não haja nada semelhante o suficiente para eu copiar diretamente.
A última vez que fiz qualquer programação foi há muitos anos, no ensino médio, quando o BASIC não era tão embaraçoso de admitir.

10

Sim, acho que isso exigirá um conhecimento razoável de SQL para ser implementado. Se você tiver um orçamento para o trabalho, talvez consiga encontrar alguém que possa ajudar no Marketplace.

11

Valeu! Não tenho orçamento (o fórum gera pouca renda com doações, além dos custos básicos de hospedagem), mas parece que vou ter que seguir por esse caminho mesmo.

12

@Paul_King

Essa consulta pode ajudar.

https://meta.discourse.org/t/daily-weekly-or-total-stats-by-user-over-a-specified-time-range/275167u=grayden_shand

13

Muito obrigado! Tentei, mas estou recebendo um erro de sintaxe

PG::SyntaxError: ERROR: syntax error at or near “WITH”
LINE 13: WITH date_range AS (

(peço desculpas, enviei uma mensagem privada para você naquele tópico antes de perceber que você e o autor original são a mesma pessoa!)

14

Você se importa se eu perguntar quais valores você está usando para as variáveis?

Acabei de buscar a consulta e executá-la em um site de teste com os seguintes valores:

  • start_date: 2021-07-01
  • end_date: 2021-07-30
  • coverage: all
15

Olá, tentei alguns, mas todos me deram esse resultado - por exemplo

image
image1428×491 21.7 KB

16

Desculpe, não consegui reproduzir o erro.

Você pode colar a consulta aqui como você a tem?

17 
SELECT 1-- cobertura: 'semana', 'todas' ou 'data'
-- [parâmetros]
-- data :start_date = 2019-08-27
-- data :end_date = 2019-09-30
-- texto :cobertura = semana

WITH date_range AS (
  SELECT date_trunc('day', dd)::date AS "date", EXTRACT(week FROM date_trunc('day', dd)::date) AS "week"
  FROM generate_series(
    :start_date::timestamp,
    :end_date::timestamp,
    '1 day'::interval
  ) AS dd
),
likes_given AS (
  SELECT u.id, dr.date, dr.week, COUNT(pa.*) AS "likes"
  FROM date_range dr
  FULL JOIN users u ON (1=1)
  LEFT JOIN post_actions pa ON (pa.created_at::date = dr.date AND post_action_type_id = 2 AND user_id = u.id)
  GROUP BY dr.date, dr.week, u.id
  ORDER BY u.id, dr.date
),
posts_summary AS (
  SELECT u.id, u.username, u.created_at, dr.*, COUNT(p.id) - COUNT(t.id) AS replies, COUNT(t.id) AS topics, COALESCE(SUM(p.like_count), 0) AS likes_received
  FROM date_range dr
  FULL OUTER JOIN users u ON (1=1)
  LEFT JOIN posts p ON (p.user_id = u.id AND p.created_at::date = dr.date AND p.deleted_at IS NULL)
  LEFT JOIN topics t ON (t.user_id = u.id AND t.created_at::date = dr.date AND p.topic_id = t.id AND t.deleted_at IS NULL)
  GROUP BY u.id, dr.date, dr.week
  ORDER BY u.id, dr.date
),
visits AS (
  SELECT u.id, dr.*, COALESCE(SUM(posts_read), 0) AS posts_read, COALESCE(SUM(time_read), 0) AS time_read, COUNT(uv.*) AS visits
  FROM date_range dr
  FULL OUTER JOIN users u ON (1=1)
  LEFT JOIN user_visits uv ON (uv.user_id = u.id AND visited_at = dr.date)
  GROUP BY u.id, dr.date, dr.week
  ORDER BY u.id, dr.date
)

SELECT 
  ps.id, 
  ps.username, 
  ps.created_at, 
  CASE
    WHEN :coverage::text = 'semana' THEN ps.week::text
    WHEN :coverage::text = 'todas' THEN '-1'
    ELSE ps.date::text
  END AS period, 
  SUM(ps.replies) AS replies, 
  SUM(ps.topics) AS topics, 
  SUM(ps.likes_received) AS likes_received, 
  SUM(lg.likes) AS likes_given, 
  COALESCE(SUM(posts_read), 0) AS posts_read, 
  COALESCE(SUM(time_read), 0) AS time_read, 
  SUM(visits) AS visits
FROM posts_summary ps
LEFT JOIN likes_given lg ON (ps.date = lg.date AND ps.id = lg.id)
LEFT JOIN visits v ON (v.id = ps.id AND v.date = ps.date)
GROUP BY 
  ps.id, 
  ps.username, 
  ps.created_at, 
  CASE 
    WHEN :coverage::text = 'semana' THEN ps.week::text
    WHEN :coverage::text = 'todas' THEN '-1'
    ELSE ps.date::text
  END
ORDER BY 
  ps.id, 
  CASE
    WHEN :coverage::text = 'semana' THEN ps.week::text
    WHEN :coverage::text = 'todas' THEN '-1'
    ELSE ps.date::text
  END
18

Ah, entendi.

O SELECT 1 no início não faz parte da consulta e é a origem do seu problema. Esse é o marcador de posição quando você cria uma nova consulta no Data Explorer. Remova-o e tudo deve funcionar.

-- coverage: 'week', 'all' ou 'date'
-- [params]
-- date :start_date = 2019-08-27
-- date :end_date = 2019-09-30
-- text :coverage = week

WITH date_range AS (
SELECT date_trunc('day', dd):: date AS "date", EXTRACT(week from date_trunc('day', dd):: date) AS "week"
FROM generate_series
        ( :start_date::timestamp 
        , :end_date::timestamp
        , '1 day'::interval) dd
), likes_given AS (
SELECT u.id, dr.date, dr.week, count(pa.*) AS "likes"
FROM date_range dr
FULL JOIN users u ON (1=1)
LEFT JOIN post_actions pa ON (pa.created_at:: date = dr.date and post_action_type_id=2 AND user_id = u.id)
GROUP BY dr.date, dr.week, u.id
ORDER BY u.id, dr.date
), posts_summary AS (SELECT u.id, u.username, u.created_at, dr.*, count(p.id) - count(t.id) AS replies, count(t.id) AS topics, COALESCE(sum(p.like_count),0) AS likes_received
from date_range dr
FULL OUTER JOIN users u ON (1=1)
LEFT JOIN posts p ON (p.user_id = u.id AND p.created_at::date=dr.date AND p.deleted_at IS NULL)
LEFT JOIN topics t ON (t.user_id = u.id AND t.created_at::date = dr.date AND p.topic_id = t.id AND t.deleted_at IS NULL)
GROUP BY u.id, dr.date, dr.week
ORDER BY u.id, dr.date), 
visits AS (SELECT u.id, dr.*, COALESCE(sum(posts_read),0) AS posts_read, COALESCE(sum(time_read),0) AS time_read, COUNT(uv.*) AS visits
FROM date_range dr
FULL OUTER JOIN users u ON (1=1)
LEFT JOIN user_visits uv ON (uv.user_id = u.id AND visited_at = dr.date)
GROUP BY u.id, dr.date, dr.week
ORDER BY u.id, dr.date
)

SELECT ps.id, ps.username, ps.created_at, CASE
WHEN :coverage::text = 'week' THEN ps.week::text
WHEN :coverage::text = 'all' THEN '-1'
ELSE ps.date::text
END  AS period, sum(ps.replies) AS replies, sum(ps.topics) AS topics, sum(ps.likes_received) AS likes_received, sum(lg.likes) AS likes_given, COALESCE(sum(posts_read),0) AS posts_read, COALESCE(sum(time_read),0) AS time_read, SUM(visits) AS visits
FROM posts_summary ps
LEFT JOIN likes_given lg ON (ps.date = lg.date AND ps.id = lg.id)
LEFT JOIN visits v ON (v.id = ps.id AND v.date = ps.date)
GROUP BY ps.id, ps.username, ps.created_at, CASE 
WHEN :coverage::text = 'week' THEN ps.week::text
WHEN :coverage::text = 'all' THEN '-1'
ELSE ps.date::text
END  
ORDER BY ps.id, CASE
WHEN :coverage::text = 'week' THEN ps.week::text
WHEN :coverage::text = 'all' THEN '-1'
ELSE ps.date::text
END
19

Obrigado @Grayden_Shand

O erro desapareceu.

Se posso incomodar um pouco mais sua expertise, as contagens geradas por esta consulta incluem posts por e-mail e respostas por e-mail para usuários no modo de lista de e-mails? Se não, como posso incluí-los?

Além disso, como posso incluir o valor de um campo personalizado do perfil do usuário ao lado do nome do usuário?

Você tem alguma dica sobre como identificar o nome do campo envolvido e implementar isso?

20

Sim, deveria incluir. Como David mencionou, a tabela posts contém um campo booleano via_email. A consulta atual ignora esse campo e conta todas as postagens, independentemente de terem sido feitas por e-mail ou não.

Existe uma tabela chamada user_custom_fields. Você precisaria fazer um JOIN nessa tabela para incluir um campo personalizado específico.

Eu provavelmente faria isso na subconsulta post_summary.

Por exemplo:

...
), posts_summary AS (
SELECT u.id, u.username, u.created_at, dr.*, count(p.id) - count(t.id) AS replies, count(t.id) AS topics, COALESCE(sum(p.like_count),0) AS likes_received, cf.value as LABEL_FOR_CUSTOM_FIELD
from date_range dr
FULL OUTER JOIN users u ON (1=1)
LEFT JOIN posts p ON (p.user_id = u.id AND p.created_at::date=dr.date AND p.deleted_at IS NULL)
LEFT JOIN topics t ON (t.user_id = u.id AND t.created_at::date = dr.date AND p.topic_id = t.id AND t.deleted_at IS NULL)
LEFT JOIN user_custom_fields cf ON (u.id == cf.user_id and cf.name == "NOME_DO_SEU_CAMPO_CUSTOMIZADO") 
GROUP BY u.id, dr.date, dr.week
ORDER BY u.id, dr.date
)
...

Adicionei uma coluna à cláusula SELECT e incluí um novo JOIN para a tabela user_custom_fields.

Observe que você precisará substituir "NOME_DO_SEU_CAMPO_CUSTOMIZADO" e LABEL_FOR_CUSTOM_FIELD.

Em seguida, você também precisará atualizar as colunas que está selecionando na consulta final.

...
SELECT ps.id, ps.username, ps.created_at, ps.LABEL_FOR_CUSTOM_FIELD, CASE
WHEN :coverage::text = 'week' THEN ps.week::text
WHEN :coverage::text = 'all' THEN '-1'
ELSE ps.date::text
END  AS period, sum(ps.replies) AS replies, sum(ps.topics) AS topics, sum(ps.likes_received) AS likes_received, sum(lg.likes) AS likes_given, COALESCE(sum(posts_read),0) AS posts_read, COALESCE(sum(time_read),0) AS time_read, SUM(visits) AS visits
FROM posts_summary ps
..

Provavelmente é assim que eu abordaria isso.

Boa sorte!