Report Dashboard - Principali Fonti di Traffico

Dati e reportistica
, ,
1

Questo è una versione SQL del report della dashboard per le principali origini di traffico.

Questo report della dashboard fornisce un’analisi delle principali origini esterne che rimandano ai post su un sito Discourse all’interno di un intervallo di date specificato e, facoltativamente, a una particolare categoria o alle sue sottocategorie.

Identificando quali domini esterni rimandano alla loro community, l’amministratore può valutare l’efficacia degli sforzi di marketing, perfezionare la propria strategia di contenuti per attrarre un pubblico più ampio, esplorare opportunità di collaborazione con siti coinvolgenti, ottimizzare per la SEO e gestire eventuali crisi derivanti da origini di traffico indesiderate.

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

Parametri

  • Parametri Data:
    • La query accetta due parametri data, :start_date e :end_date, che definiscono l’intervallo di date per il report. Entrambi i parametri data accettano il formato data AAAA-MM-GG.
  • Parametri Categoria:
    • :category_id: Un parametro intero che può essere impostato sull’ID di una categoria specifica per restringere l’analisi ai post all’interno di tale categoria. Se è impostato su null o non fornito, vengono considerati i post di tutte le categorie.
    • :include_subcategories: Un parametro booleano che controlla se includere i post dalle sottocategorie della :category_id specificata. Se impostato su true, il report includerà i link ai post sia nella categoria specificata che nelle sue sottocategorie; se false, verrà considerata solo la categoria specificata.
  • Limite Risultati:
    • :limit: Un parametro intero che limita il numero di righe restituite dalla query ai primi N risultati in base al numero di clic. Il valore predefinito fornito nella query è 10.

Spiegazione Query SQL

  • SELECT: Il report è strutturato per selezionare tre informazioni:
    • ind.name AS domain: Questa colonna mostrerà il nome di ciascun dominio che ha indirizzato utenti al forum.
    • COUNT(*) AS clicks: Questa colonna mostra il numero totale di clic sui link in entrata da ciascun dominio.
    • COUNT(DISTINCT p.topic_id) AS topics: Questa colonna rappresenta il numero di argomenti unici a cui si è acceduto tramite tali link in entrata.
  • FROM AND JOIN: La query aggrega i dati da diverse tabelle:
    • incoming_links il: Questa è la tabella principale in cui sono archiviati i dati dei link in entrata.
    • posts p: La tabella relativa ai post del forum.
    • topics t: La tabella contenente i dati degli argomenti.
    • categories c: La tabella contenente informazioni sulle categorie.
    • incoming_referers ir: La tabella che memorizza informazioni sui link di riferimento.
    • incoming_domains ind: La tabella che cataloga i domini da cui provengono i link al forum.
    • Le INNER JOIN assicurano che consideriamo solo i post e gli argomenti che non sono stati eliminati e collegano i dati dei link in entrata ai corretti post, argomenti e domini di riferimento.
  • WHERE: Questa parte della query applica filtri per restringere il set di risultati:
    • Limita i record solo a quelli che sono nell’archetipo ‘regular’, ovvero thread del forum normali (a differenza dei messaggi privati o di altri tipi speciali).
    • Filtra le date per includere solo i link creati nell’intervallo specificato (:start_date a :end_date).
    • Applica il filtraggio delle categorie in base al parametro :category_id fornito. Se :category_id è null, vengono considerate tutte le categorie. Se :include_subcategories è true e :category_id non è null, include anche le sottocategorie della categoria specificata.
  • GROUP BY: La query raggruppa i dati per nome del dominio (ind.name), assicurando che i conteggi siano partizionati per ciascun dominio di riferimento distinto.
  • ORDER BY and LIMIT: Il set di dati finale viene ordinato in ordine decrescente in base al conteggio dei clic, dando quindi priorità ai domini con il maggior traffico di riferimento. La clausola LIMIT :limit limita il numero di risultati per rendere il report conciso e focalizzato sui domini principali.

Risultati di Esempio

dominio clic argomenti
www.examplesite1.com 1234 123
www.examplesite2.com 56 67
www.examplesite3.com 40 95
… … …
3 Mi Piace
2

Mi piace molto leggere ognuno di questi.

Bel lavoro!

1 Mi Piace
3

Avete qualche idea sul motivo per cui questo potrebbe dare numeri troppo bassi? Sul mio forum Facebook non è nemmeno vicino, il che significa che mancano migliaia e alcuni siti non ci sono affatto. I cellulari nascondono la fonte o qualcosa del genere?