Tempos Médios de Resposta da Equipe aos Tópicos

Dados e relatórios
1

Este relatório do Explorador de Dados foi projetado para analisar os tempos médios de resposta dos membros da equipe (administradores e moderadores) aos tópicos criados dentro de um intervalo de datas e categoria especificados.

Este relatório pode ajudar a entender a eficiência e a capacidade de resposta das interações da equipe em um site Discourse, o que pode ser crucial para o gerenciamento da comunidade.

-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2024-01-30
-- int_list :categories = 0
-- boolean :include_subcategories = false

WITH filtered_topics AS (
    SELECT t.id AS topic_id, t.category_id, t.created_at::DATE AS created_at
    FROM topics t
    WHERE
      (
        ':categories' = '0'
        OR t.category_id IN
          (
            SELECT id
            FROM categories
            WHERE id IN(:categories)
              OR (:include_subcategories AND parent_category_id IN(:categories))
          )
      )
      AND t.created_at >= :start_date
      AND t.created_at < :end_date
      AND t.archetype = 'regular'
      AND t.deleted_at IS NULL
),
staff_replies AS (
    SELECT p.topic_id, MIN(p.created_at) AS first_staff_reply
    FROM posts p
    JOIN users u ON p.user_id = u.id
    WHERE (u.admin = true OR u.moderator = true)
      AND p.deleted_at IS NULL
      AND p.post_type = 1
    GROUP BY p.topic_id
),
response_times AS (
    SELECT
        ft.topic_id,
        ft.created_at AS topic_created_at,
        sr.first_staff_reply,
        ft.category_id as category_id,
        EXTRACT(EPOCH FROM (sr.first_staff_reply - ft.created_at)) / 3600 AS response_time_hours
    FROM filtered_topics ft
    LEFT JOIN staff_replies sr ON ft.topic_id = sr.topic_id
)
SELECT
    topic_id,
    category_id,
    topic_created_at,
    response_time_hours as staff_response_time_hours
FROM response_times
ORDER BY topic_created_at ASC

Explicação da Consulta SQL

O relatório usa várias Expressões de Tabela Comuns (CTEs) para detalhar a consulta:

  • filtered_topics: Esta CTE filtra os tópicos com base nos parâmetros de entrada, como intervalo de datas, categoria e se deve incluir subcategorias. Ela garante que apenas tópicos não excluídos e do arquétipo regular sejam considerados.
  • staff_replies: Esta CTE identifica a primeira resposta feita por um membro da equipe (administrador ou moderador) aos tópicos identificados na CTE filtered_topics. Ela filtra posts excluídos e considera apenas os posts principais (post_type = 1).
  • response_times: Esta CTE calcula o tempo de resposta encontrando a diferença entre o tempo de criação do tópico e o tempo da primeira resposta da equipe. O resultado é convertido de segundos para horas.

A instrução SELECT final da CTE response_times busca o ID do tópico, o ID da categoria, a data de criação do tópico e o tempo de resposta da equipe calculado em horas, ordenando os resultados pela data de criação do tópico.

Parâmetros

  • start_date (date): A data de início do período para o qual analisar a criação de tópicos.
  • end_date (date): A data de término do período para o qual analisar a criação de tópicos.
  • categories (int_list): Uma lista de IDs de categoria para filtrar os tópicos. Se definido como 0, todas as categorias são incluídas.
  • include_subcategories (boolean): Um sinalizador para determinar se as subcategorias das categorias especificadas devem ser incluídas na análise.

Resultados

  • topic_id: O identificador exclusivo do tópico.
  • category_id: A categoria à qual o tópico pertence.
  • topic_created_at: A data em que o tópico foi criado.
  • staff_response_time_hours: O tempo que a equipe levou para responder ao tópico em horas. Um valor NULL indica que não houve resposta da equipe ao tópico.

Exemplo de Resultados

topic_id category_id topic_created_at staff_response_time_hours
101 5 2024-01-02 1.5
102 5 2024-01-02 3.2
103 12 2024-01-03 NULL
104 12 2024-01-04 0.75
1 curtida
2

Existe alguma maneira fácil de excluir tópicos iniciados por funcionários?

1 curtida
3

Sim, você pode modificar o relatório da seguinte forma para excluir tópicos criados por usuários da equipe.

-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2024-01-30
-- int_list :categories = 0
-- boolean :include_subcategories = false

WITH staff_users AS (
    SELECT id
    FROM users
    WHERE admin = true OR moderator = true
),
filtered_topics AS (
    SELECT t.id AS topic_id, t.category_id, t.created_at::DATE AS created_at
    FROM topics t
    LEFT JOIN staff_users su ON t.user_id = su.id
    WHERE
      (
        ':categories' = '0'
        OR t.category_id IN
          (
            SELECT id
            FROM categories
            WHERE id IN(:categories)
              OR (:include_subcategories AND parent_category_id IN(:categories))
          )
      )
      AND t.created_at >= :start_date 
      AND t.created_at < :end_date 
      AND t.archetype = 'regular' 
      AND t.deleted_at IS NULL
      AND su.id IS NULL  -- Exclui tópicos criados pela equipe
),
staff_replies AS (
    SELECT p.topic_id, MIN(p.created_at) AS first_staff_reply
    FROM posts p
    JOIN staff_users su ON p.user_id = su.id
    WHERE p.deleted_at IS NULL
      AND p.post_type = 1
    GROUP BY p.topic_id
),
response_times AS (
    SELECT 
        ft.topic_id,
        ft.created_at AS topic_created_at,
        sr.first_staff_reply,
        ft.category_id as category_id,
        EXTRACT(EPOCH FROM (sr.first_staff_reply - ft.created_at)) / 3600 AS response_time_hours
    FROM filtered_topics ft
    LEFT JOIN staff_replies sr ON ft.topic_id = sr.topic_id
)
SELECT 
    topic_id,
    category_id,
    topic_created_at,
    response_time_hours as staff_response_time
FROM response_times
ORDER BY topic_created_at ASC

Modificações Explicadas:

  1. CTE staff_users: Uma nova CTE é adicionada para identificar usuários que são administradores ou moderadores. Isso ajuda a filtrar tópicos criados pela equipe nas etapas subsequentes.
  2. CTE filtered_topics: Esta CTE agora inclui um LEFT JOIN com a CTE staff_users para excluir tópicos onde o user_id corresponde ao ID de qualquer membro da equipe. Isso é feito verificando su.id IS NULL, o que garante que o tópico não foi criado por um membro da equipe.
1 curtida
4

Obrigado! O resultado está muito menos agitado agora.

Droga, tudo é fácil… quando se tem habilidade suficiente.

1 curtida