Relatório do Painel - Principais Fontes de Tráfego

Dados e relatórios
, ,
1

Este é um relatório de painel em SQL para as principais fontes de tráfego.

Este relatório de painel fornece uma análise das principais fontes externas que se vinculam a postagens em um site Discourse dentro de um intervalo de datas especificado e, opcionalmente, a uma categoria específica ou suas subcategorias.

Ao identificar quais domínios externos se vinculam à sua comunidade, o administrador pode avaliar a eficácia dos esforços de marketing, refinar sua estratégia de conteúdo para atrair um público mais amplo, explorar oportunidades de colaboração com sites envolventes, otimizar para SEO e gerenciar quaisquer crises potenciais decorrentes de fontes de tráfego indesejadas.

-- [params]
-- date :start_date = 2024-01-01
-- date :end_date= 2025-01-01
-- null int :category_id
-- int :limit = 10
-- boolean :include_subcategories = true

SELECT 
  ind.name AS domain,
  COUNT(*) AS clicks,
  COUNT(DISTINCT p.topic_id) AS topics
FROM incoming_links il
  INNER JOIN posts p ON p.deleted_at ISNULL AND p.id = il.post_id
  INNER JOIN topics t ON t.deleted_at ISNULL AND t.id = p.topic_id
  INNER JOIN categories c ON c.id = t.category_id
  INNER JOIN incoming_referers ir ON ir.id = il.incoming_referer_id
  INNER JOIN incoming_domains ind ON ind.id = ir.incoming_domain_id
WHERE t.archetype = 'regular'
AND il.created_at::date BETWEEN :start_date AND :end_date
AND (:category_id IS NULL
  OR t.category_id = :category_id
  OR (:include_subcategories AND c.parent_category_id = :category_id))
GROUP BY ind.name
ORDER BY clicks DESC
LIMIT :limit

Parâmetros

  • Parâmetros de Data:
    • A consulta aceita dois parâmetros de data, :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.
  • Parâmetros de Categoria:
    • :category_id: Um parâmetro inteiro que pode ser definido para o ID de uma categoria específica para refinar a análise para postagens dentro dessa categoria. Se for definido como nulo ou não fornecido, postagens de todas as categorias são consideradas.
    • :include_subcategories: Um parâmetro booleano que controla se deve incluir postagens de subcategorias da :category_id especificada. Se definido como true, o relatório incluirá links para postagens tanto na categoria especificada quanto em suas subcategorias; se false, apenas a categoria especificada será considerada.
  • Limite de Resultados:
    • :limit: Um parâmetro inteiro que limita o número de linhas retornadas pela consulta aos N principais resultados com base no número de cliques. O valor padrão fornecido na consulta é 10.

Explicação da Consulta SQL

  • SELECT: O relatório é estruturado para selecionar três informações:
    • ind.name AS domain: Esta coluna exibirá o nome de cada domínio que referenciou usuários ao fórum.
    • COUNT(*) AS clicks: Esta coluna mostra o número total de cliques de links de entrada de cada domínio.
    • COUNT(DISTINCT p.topic_id) AS topics: Esta coluna representa o número de tópicos exclusivos que foram acessados por meio desses links de entrada.
  • FROM E JOIN: A consulta agrega dados de várias tabelas:
    • incoming_links il: Esta é a tabela principal onde os dados de links de entrada são armazenados.
    • posts p: A tabela relacionada às postagens do fórum.
    • topics t: A tabela contendo dados de tópicos.
    • categories c: A tabela contendo informações sobre categorias.
    • incoming_referers ir: A tabela que armazena informações sobre os links de referência.
    • incoming_domains ind: A tabela que cataloga os domínios de onde se originam os links para o fórum.
    • Os INNER JOINs garantem que consideremos apenas postagens e tópicos que não foram excluídos e vinculam os dados de links de entrada às postagens, tópicos e domínios de referência corretos.
  • WHERE: Esta parte da consulta aplica filtros para refinar o conjunto de resultados:
    • Limita os registros apenas àqueles no arquétipo ‘regular’, ou seja, threads normais do fórum (em oposição a mensagens privadas ou outros tipos especiais).
    • Filtra as datas para incluir apenas links criados dentro do período especificado (:start_date a :end_date).
    • Aplica a filtragem de categoria com base no parâmetro :category_id fornecido. Se :category_id for nulo, todas as categorias são consideradas. Se :include_subcategories for true e :category_id não for nulo, ele também inclui subcategorias da categoria especificada.
  • GROUP BY: A consulta agrupa os dados pelo nome do domínio (ind.name), garantindo que as contagens sejam particionadas por cada domínio de referência distinto.
  • ORDER BY E LIMIT: O conjunto de dados final é classificado em ordem decrescente com base na contagem de cliques, priorizando assim os domínios com o maior tráfego de referência. A cláusula LIMIT :limit restringe o número de resultados para tornar o relatório conciso e focado nos principais domínios.

Resultados de Exemplo

domínio Cliques Tópicos
www.examplesite1.com 1234 123
www.examplesite2.com 56 67
www.examplesite3.com 40 95
… …
3 curtidas
2

Gosto muito de ler cada um destes.

Bom trabalho!

1 curtida
3

Vocês têm alguma ideia do porquê isso pode estar dando números muito baixos? No meu fórum, o Facebook nem chega perto, o que significa que faltam milhares e alguns sites nem aparecem. Celulares estão escondendo a fonte ou algo assim?