Informe del panel - Términos de búsqueda de tendencia

Este es una versión SQL del Informe del Panel para Términos de Búsqueda de Tendencia.

Este informe proporciona información sobre los términos de búsqueda utilizados en un sitio dentro de un rango de fechas especificado y ayuda a comprender qué términos se buscan con más frecuencia y con qué frecuencia los usuarios hacen clic en los resultados de búsqueda.

--[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

Explicación de la Consulta SQL

Aquí hay un desglose de la consulta:

• Parámetros:
  • :start_date - El inicio del rango de fechas para el informe.
  • :end_date - El final del rango de fechas para el informe.
    • Ambos parámetros de fecha aceptan el formato YYYY-MM-DD.
• Selección de Datos:
  • lower(term) AS search_term: Convierte el término de búsqueda a minúsculas para una agrupación consistente y nombra la columna resultante search_term.
  • count(id) AS term_count: Cuenta el número total de búsquedas para cada término y etiqueta esta columna 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 la tasa de clics (CTR) para cada término determinando el porcentaje de búsquedas que resultaron en un clic. Maneja los casos en que search_result_type es NULL (indicando que no hubo clic) asignando cero, de lo contrario uno. El resultado se divide luego por el recuento total de búsquedas para ese término para obtener una tasa, que luego se multiplica por 100 para obtener un porcentaje.
  • SUM(CASE WHEN search_result_type IS NULL THEN 0 ELSE 1 END) as click_count: Suma el número de veces que se hizo clic en los resultados de búsqueda para cada término.
• Filtrado:
  • WHERE sl.created_at::date BETWEEN :start_date AND :end_date: Filtra los registros de búsqueda para incluir solo las entradas donde la fecha created_at se encuentra entre las fechas de inicio y fin especificadas.
• Agrupación y Ordenación:
  • GROUP BY lower(term): Agrupa los resultados por el término de búsqueda en minúsculas para agregar recuentos y clics para cada término único.
  • ORDER BY term_count DESC: Ordena los resultados por term_count en orden descendente, mostrando los términos buscados con más frecuencia en la parte superior.

Resultados de Ejemplo