Relatório do Painel - Termos de Pesquisa em Alta

Dados e relatórios
, ,
1

Este é um relatório SQL do painel para termos de pesquisa em tendência.

Este relatório fornece informações sobre os termos de pesquisa usados em um site dentro de um intervalo de datas especificado e ajuda a entender quais termos são mais pesquisados e com que frequência os usuários clicam nos resultados da pesquisa.

--[params]
-- date :start_date
-- date :end_date

SELECT 
	lower(term) AS search_term,
	count(id) AS term_count,
	(COALESCE(SUM(CASE WHEN search_result_type IS NULL THEN 0 ELSE 1 END), 0) / count(id)::float) * 100 AS click_through_rate,
	SUM(CASE WHEN search_result_type IS NULL THEN 0 ELSE 1 END) as click_count
FROM search_logs sl
WHERE sl.created_at::date BETWEEN :start_date AND :end_date
GROUP BY lower(term)
ORDER BY term_count DESC

Explicação da Consulta SQL

Aqui está uma análise da consulta:

  • Parâmetros:
    • :start_date - O início do intervalo de datas para o relatório.
    • :end_date - O fim do intervalo de datas para o relatório.
      • Ambos os parâmetros de data aceitam o formato AAAA-MM-DD.
  • Seleção de Dados:
    • lower(term) AS search_term: Converte o termo de pesquisa para minúsculas para agrupamento consistente e nomeia a coluna resultante como search_term.
    • count(id) AS term_count: Conta o número total de pesquisas para cada termo e rotula esta coluna como term_count.
    • (COALESCE(SUM(CASE WHEN search_result_type IS NULL THEN 0 ELSE 1 END), 0) / count(id)::float) * 100 AS click_through_rate: Calcula a taxa de cliques (CTR) para cada termo, determinando a porcentagem de pesquisas que resultaram em um clique. Ele lida com casos em que search_result_type é NULL (indicando nenhum clique) atribuindo zero, caso contrário, um. O resultado é então dividido pela contagem total de pesquisas para esse termo para obter uma taxa, que é então multiplicada por 100 para obter uma porcentagem.
    • SUM(CASE WHEN search_result_type IS NULL THEN 0 ELSE 1 END) as click_count: Soma o número de vezes que os resultados da pesquisa foram clicados para cada termo.
  • Filtragem:
    • WHERE sl.created_at::date BETWEEN :start_date AND :end_date: Filtra os logs de pesquisa para incluir apenas entradas onde a data created_at está entre as datas de início e fim especificadas.
  • Agrupamento e Ordenação:
    • GROUP BY lower(term): Agrupa os resultados pelo termo de pesquisa em minúsculas para agregar contagens e cliques para cada termo exclusivo.
    • ORDER BY term_count DESC: Ordena os resultados por term_count em ordem decrescente, mostrando os termos mais pesquisados no topo.

Exemplo de Resultados

search_term term_count click_through_rate click_count
example search term 756 0 0
#category search term 545 0.3669724770642202 2
in:personal order:latest 70 0 0
test 59 5.084745762711865 3
search 57 14.03508771929824 8
… … … …
5 curtidas