Informe del panel - Principales fuentes de tráfico

Datos e informes
, ,
1

Este es una versión SQL del informe del panel de control de las principales fuentes de tráfico.

Este informe del panel de control proporciona un análisis de las principales fuentes externas que enlazan a las publicaciones en un sitio de Discourse dentro de un rango de fechas especificado y, opcionalmente, a una categoría en particular o sus subcategorías.

Al identificar qué dominios externos enlazan a su comunidad, el administrador puede evaluar la efectividad de los esfuerzos de marketing, refinar su estrategia de contenido para atraer a una audiencia más amplia, explorar oportunidades de colaboración con sitios atractivos, optimizar para SEO y gestionar cualquier crisis potencial que surja de fuentes de tráfico no deseadas.

-- [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 fecha:
    • La consulta acepta dos parámetros de fecha, :start_date y :end_date, que definen el rango de fechas para el informe. Ambos parámetros de fecha aceptan el formato de fecha YYYY-MM-DD.
  • Parámetros de categoría:
    • :category_id: Un parámetro entero que se puede establecer en el ID de una categoría específica para reducir el análisis a las publicaciones dentro de esa categoría. Si se establece en nulo o no se proporciona, se consideran las publicaciones de todas las categorías.
    • :include_subcategories: Un parámetro booleano que controla si se incluyen las publicaciones de las subcategorías de :category_id especificado. Si se establece en true, el informe incluirá enlaces a publicaciones tanto en la categoría especificada como en sus subcategorías; si se establece en false, solo se considerará la categoría especificada.
  • Límite de resultados:
    • :limit: Un parámetro entero que limita el número de filas devueltas por la consulta a los N principales resultados según el número de clics. El valor predeterminado proporcionado en la consulta es 10.

Explicación de la consulta SQL

  • SELECT: El informe está estructurado para seleccionar tres piezas de información:
    • ind.name AS domain: Esta columna mostrará el nombre de cada dominio que ha referido usuarios al foro.
    • COUNT(*) AS clicks: Esta columna muestra el número total de clics de enlaces entrantes de cada dominio.
    • COUNT(DISTINCT p.topic_id) AS topics: Esta columna representa el número de temas únicos a los que se ha accedido a través de esos enlaces entrantes.
  • FROM AND JOIN: La consulta agrega datos de varias tablas:
    • incoming_links il: Esta es la tabla principal donde se almacenan los datos de enlaces entrantes.
    • posts p: La tabla relacionada con las publicaciones del foro.
    • topics t: La tabla que contiene datos de temas.
    • categories c: La tabla que contiene información sobre categorías.
    • incoming_referers ir: La tabla que almacena información sobre los enlaces de referencia.
    • incoming_domains ind: La tabla que cataloga los dominios de los que provienen los enlaces al foro.
    • Los INNER JOINs aseguran que solo consideramos publicaciones y temas que no han sido eliminados y vinculan los datos de enlaces entrantes con las publicaciones, temas y dominios de referencia correctos.
  • WHERE: Esta parte de la consulta aplica filtros para reducir el conjunto de resultados:
    • Limita los registros solo a aquellos que son del arquetipo ‘regular’, es decir, hilos de foro normales (en contraposición a mensajes privados u otros tipos especiales).
    • Filtra las fechas para incluir solo los enlaces creados dentro del período especificado (:start_date a :end_date).
    • Aplica el filtrado de categorías según el parámetro :category_id proporcionado. Si :category_id es nulo, se consideran todas las categorías. Si :include_subcategories es true y :category_id no es nulo, también incluye las subcategorías de la categoría especificada.
  • GROUP BY: La consulta agrupa los datos por el nombre del dominio (ind.name), asegurando que los recuentos se particionen por cada dominio de referencia distinto.
  • ORDER BY y LIMIT: El conjunto de datos final se ordena en orden descendente según el recuento de clics, priorizando así los dominios con el mayor tráfico de referencia. La cláusula LIMIT :limit restringe el número de resultados para que el informe sea conciso y se centre en los dominios principales.

Resultados de ejemplo

domain clicks topics
www.examplesite1.com 1234 123
www.examplesite2.com 56 67
www.examplesite3.com 40 95
… … …
3 Me gusta
2

Me gusta mucho leer cada uno de estos.

¡Buen trabajo!

1 me gusta
3

¿Tienen alguna idea de por qué esto podría dar números demasiado pequeños? En mi foro, Facebook ni siquiera está cerca, lo que significa que faltan miles y algunos sitios ni siquiera están allí. ¿Los móviles están ocultando la fuente o algo así?