Rapport de tableau de bord - Principales sources de trafic

Données et rapports
, ,
1

Ceci est une version SQL du rapport de tableau de bord pour les principales sources de trafic.

Ce rapport de tableau de bord fournit une analyse des principales sources externes qui renvoient vers des publications sur un site Discourse dans une plage de dates spécifiée et, éventuellement, une catégorie particulière ou ses sous-catégories.

En identifiant les domaines externes qui renvoient vers leur communauté, l’administrateur peut évaluer l’efficacité des efforts de marketing, affiner sa stratégie de contenu pour attirer un public plus large, explorer des opportunités de collaboration avec des sites engageants, optimiser le référencement et gérer toute crise potentielle découlant de sources de trafic indésirables.

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

Paramètres

  • Paramètres de date :
    • La requête accepte deux paramètres de date, :start_date et :end_date, qui définissent la plage de dates du rapport. Les deux paramètres de date acceptent le format de date AAAA-MM-JJ.
  • Paramètres de catégorie :
    • :category_id : un paramètre entier qui peut être défini sur l’ID d’une catégorie spécifique pour affiner l’analyse aux publications de cette catégorie. S’il est défini sur null ou non fourni, les publications de toutes les catégories sont prises en compte.
    • :include_subcategories : un paramètre booléen qui contrôle s’il faut inclure les publications des sous-catégories de :category_id spécifié. S’il est défini sur true, le rapport inclura les liens vers les publications dans la catégorie spécifiée et ses sous-catégories ; sinon, seule la catégorie spécifiée sera prise en compte.
  • Limite des résultats :
    • :limit : un paramètre entier qui limite le nombre de lignes renvoyées par la requête aux N meilleurs résultats en fonction du nombre de clics. La valeur par défaut fournie dans la requête est 10.

Explication de la requête SQL

  • SELECT : le rapport est structuré pour sélectionner trois informations :
    • ind.name AS domain : cette colonne affichera le nom de chaque domaine qui a renvoyé des utilisateurs vers le forum.
    • COUNT(*) AS clicks : cette colonne montre le nombre total de clics de liens entrants provenant de chaque domaine.
    • COUNT(DISTINCT p.topic_id) AS topics : cette colonne représente le nombre de sujets uniques qui ont été consultés via ces liens entrants.
  • FROM ET JOIN : la requête agrège les données de plusieurs tables :
    • incoming_links il : il s’agit de la table principale où sont stockées les données des liens entrants.
    • posts p : la table liée aux publications du forum.
    • topics t : la table contenant les données des sujets.
    • categories c : la table contenant des informations sur les catégories.
    • incoming_referers ir : la table qui stocke les informations sur les liens de référencement.
    • incoming_domains ind : la table qui catalogue les domaines d’où proviennent les liens vers le forum.
    • Les INNER JOINs garantissent que nous ne considérons que les publications et les sujets qui n’ont pas été supprimés et relient les données des liens entrants aux bons domaines de publication, de sujet et de référencement.
  • WHERE : cette partie de la requête applique des filtres pour affiner l’ensemble de résultats :
    • Elle limite les enregistrements à ceux qui sont de l’archétype « regular », c’est-à-dire les fils de discussion normaux du forum (par opposition aux messages privés ou à d’autres types spéciaux).
    • Elle filtre les dates pour inclure uniquement les liens créés dans la période spécifiée (:start_date à :end_date).
    • Elle applique le filtrage des catégories en fonction du paramètre :category_id fourni. Si :category_id est null, toutes les catégories sont prises en compte. Si :include_subcategories est true et :category_id n’est pas null, elle inclut également les sous-catégories de la catégorie spécifiée.
  • GROUP BY : la requête regroupe les données par le nom du domaine (ind.name), garantissant que les décomptes sont partitionnés par chaque domaine référent distinct.
  • ORDER BY et LIMIT : le jeu de données final est trié par ordre décroissant en fonction du nombre de clics, priorisant ainsi les domaines ayant le trafic de référencement le plus élevé. La clause LIMIT :limit limite le nombre de résultats pour rendre le rapport concis et axé sur les principaux domaines.

Résultats d’exemple

domaine Clics Sujets
www.examplesite1.com 1234 123
www.examplesite2.com 56 67
www.examplesite3.com 40 95
… … …
3 « J'aime »
2

J’aime vraiment lire chacun de ces articles.

Beau travail !

1 « J'aime »
3

Avez-vous des idées sur la raison pour laquelle cela pourrait donner des chiffres trop bas ? Sur mon forum, Facebook n’est même pas proche, ce qui signifie que des milliers sont manquants et certains sites n’y figurent pas du tout. Les mobiles cachent-ils la source ou quelque chose comme ça ?