Relatório do Painel - Reações

Dados e relatórios
, , ,
1

Este é um relatório de painel em SQL para Reações.

Este relatório fornece uma contagem diária de todos os “likes” e reações a publicações em um site durante um intervalo de datas especificado.

Este relatório ajuda a fornecer um instantâneo do envolvimento do usuário dentro de uma comunidade Discourse, medindo a frequência de vários emojis de reação e “likes” em publicações. Ao analisar o uso de diferentes emojis de reação, os administradores podem obter informações sobre a frequência com que os usuários interagem com o conteúdo, as respostas emocionais dos usuários às publicações e identificar emojis de reação populares e subutilizados.

:information_source: Este relatório requer que o plugin Discourse Reactions esteja ativado em seu site. As reações disponíveis no relatório dependerão de quais reações específicas foram habilitadas com a configuração do site discourse_reactions_enabled_reactions.

-- [params]
-- date :start_date = 2023-12-16
-- date :end_date = 2024-01-17
 
SELECT
  r.day,
  COALESCE(l.likes_count, 0) as likes_count,
  sum(case when reaction_value = 'laughing' then reactions_count else 0 end)::int as laughing,
  sum(case when reaction_value = 'cry' then reactions_count else 0 end)::int as cry,
  sum(case when reaction_value = 'exploding_head' then reactions_count else 0 end)::int as exploding_head,
  sum(case when reaction_value = 'clap' then reactions_count else 0 end)::int as clap,
  sum(case when reaction_value = 'confetti_ball' then reactions_count else 0 end)::int as confetti_ball,
  sum(case when reaction_value = 'hugs' then reactions_count else 0 end)::int as hugs,
  sum(case when reaction_value = 'chefs_kiss' then reactions_count else 0 end)::int as chefs_kiss,
  sum(case when reaction_value = '100' then reactions_count else 0 end)::int as one_hundred,
  sum(case when reaction_value = '+1' then reactions_count else 0 end)::int as plus_one,
  sum(case when reaction_value = 'rocket' then reactions_count else 0 end)::int as rocket,
  sum(case when reaction_value = 'star_struck' then reactions_count else 0 end)::int as star_struck,
  sum(case when reaction_value = 'eyes' then reactions_count else 0 end)::int as eyes,
  sum(case when reaction_value = 'discourse' then reactions_count else 0 end)::int as discourse
FROM (
  SELECT
    date_trunc('day', drru.created_at)::date as day,
    drr.reaction_value,
    count(drru.id) as reactions_count
  FROM discourse_reactions_reactions as drr
  LEFT OUTER JOIN discourse_reactions_reaction_users as drru on drr.id = drru.reaction_id
  WHERE drr.reaction_users_count IS NOT NULL
  AND drru.created_at::date >= :start_date::date AND drru.created_at::date <= :end_date::date
  GROUP BY drr.reaction_value, day
) r
LEFT JOIN (
  SELECT
    count(pa.id) as likes_count,
    date_trunc('day', pa.created_at)::date as day
  FROM post_actions as pa
  WHERE pa.post_action_type_id = 2 
  AND pa.created_at::date >= :start_date::date AND pa.created_at::date <= :end_date::date 
  GROUP BY day
) l ON r.day = l.day
GROUP BY r.day, l.likes_count
ORDER BY r.day

Explicação da Consulta SQL

Parâmetros

  • A consulta aceita dois parâmetros, :start_date e :end_date, que definem o intervalo de datas para o relatório. Ambos os parâmetros de data aceitam o formato de data AAAA-MM-DD.

Estrutura da Consulta

  • Consulta Interna (Reações): A consulta começa com uma subconsulta que seleciona a data (day), o tipo de reação (reaction_value) e a contagem de reações (reactions_count) da tabela discourse_reactions_reactions unida com discourse_reactions_reaction_users no reaction_id. Essa união garante que estamos contabilizando as reações específicas do usuário.
  • Agregação por Tipos de Reação: Os dados selecionados são então agrupados por day e reaction_value, restringindo o intervalo às datas de início e fim selecionadas. Isso é usado para calcular a contagem total de cada tipo de reação para cada dia no intervalo de datas especificado.
  • Ordenação dos Totais de Reação: Para cada tipo de reação, a consulta usa uma instrução CASE para agregar o número de vezes que a reação específica é usada e converte o resultado como um inteiro para uma contagem limpa.
    • Você pode precisar ajustar o reaction_value = '...' nesta seção, dependendo de quais reações estão habilitadas em seu site.
  • Subconsulta de Contagem de Likes: Uma subconsulta separada é usada para calcular o número total de “likes” (likes_count) para cada dia usando a tabela post_actions, onde post_action_type_id corresponde a “likes”.
  • Combinando Dados: A consulta externa combina a contagem de “likes” com as contagens de reações unindo pelo campo day.
  • Seleção Final: O SELECT mais externo gera a saída final com o day, o número de “likes” (likes_count) e as contagens de cada tipo de reação. Se não houver dados de “likes” em um dia específico, a função COALESCE garante que um zero seja exibido em vez de NULL.
  • Ordenando os Resultados: Os resultados são ordenados por data (r.day) para criar uma série temporal de envolvimento no site.

Colunas

  • day: A data em que as reações e os “likes” foram contados.
  • likes_count: Número total de “likes” para cada dia.
  • Cada tipo de reação (laughing, cry, exploding_head, etc.): Colunas individuais mostrando a contagem total de cada tipo de reação por dia.

Resultados de Exemplo

day likes_count laughing cry exploding_head clap confetti_ball hugs chefs_kiss one_hundred plus_one rocket star_struck eyes discourse
2023-12-16 13 0 3 0 3 1 0 0 0 5 2 2 1 0
2023-12-17 17 1 0 2 2 0 0 0 0 4 4 1 2 0
2023-12-18 46 0 1 0 6 0 1 3 0 27 3 4 5 0
1 curtida
2

E um aviso para nós, que não somos desenvolvedores… essas reações estão codificadas ali e são as que estão em uso aqui. Isso também significa que, se as reações oferecidas mudarem em algum momento, isso mostrará informações erradas de tempos mais antigos.

Tenho a sensação de que isso está contando errado se o padrão mudou de :heart: para :+1:, por exemplo.

3

@JammyDodger corrigiu uma consulta que eu estava usando e que leva isso em consideração, acredito:

2 curtidas