Dashboard-Bericht - Top-Traffic-Quellen

Daten & Berichterstattung
, ,
1

Dies ist eine SQL-Version des Dashboard-Berichts für Top-Traffic-Quellen.

Dieser Dashboard-Bericht bietet eine Analyse der wichtigsten externen Quellen, die auf Beiträge in einer Discourse-Site innerhalb eines bestimmten Datumsbereichs und optional einer bestimmten Kategorie oder deren Unterkategorien verlinken.

Durch die Identifizierung, welche externen Domains auf ihre Community verlinken, können Administratoren die Wirksamkeit von Marketingbemühungen bewerten, ihre Content-Strategie verfeinern, um ein breiteres Publikum anzusprechen, Kooperationsmöglichkeiten mit ansprechenden Websites erkunden, für SEO optimieren und potenzielle Krisen bewältigen, die aus unerwünschten Traffic-Quellen entstehen.

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

Parameter

  • Datumsangaben:
    • Die Abfrage akzeptiert zwei Datumsangaben, :start_date und :end_date, die den Datumsbereich für den Bericht definieren. Beide Datumsangaben akzeptieren das Datumsformat JJJJ-MM-TT.
  • Kategorieparameter:
    • :category_id: Ein Integer-Parameter, der auf die ID einer bestimmten Kategorie gesetzt werden kann, um die Analyse auf Beiträge innerhalb dieser Kategorie zu beschränken. Wenn er auf null gesetzt oder nicht angegeben wird, werden Beiträge aus allen Kategorien berücksichtigt.
    • :include_subcategories: Ein boolescher Parameter, der steuert, ob Beiträge aus Unterkategorien der angegebenen :category_id eingeschlossen werden sollen. Wenn er auf true gesetzt ist, enthält der Bericht Links zu Beiträgen sowohl in der angegebenen Kategorie als auch in ihren Unterkategorien; wenn er auf false gesetzt ist, wird nur die angegebene Kategorie berücksichtigt.
  • Ergebnislimit:
    • :limit: Ein Integer-Parameter, der die Anzahl der von der Abfrage zurückgegebenen Zeilen auf die Top-N-Ergebnisse basierend auf der Anzahl der Klicks beschränkt. Der in der Abfrage angegebene Standardwert ist 10.

Erklärung der SQL-Abfrage

  • SELECT: Der Bericht ist so strukturiert, dass drei Informationen ausgewählt werden:
    • ind.name AS domain: Diese Spalte zeigt den Namen jeder Domain an, die Benutzer auf das Forum verwiesen hat.
    • COUNT(*) AS clicks: Diese Spalte zeigt die Gesamtzahl der eingehenden Link-Klicks von jeder Domain an.
    • COUNT(DISTINCT p.topic_id) AS topics: Diese Spalte stellt die Anzahl der eindeutigen Themen dar, auf die über diese eingehenden Links zugegriffen wurde.
  • FROM AND JOIN: Die Abfrage aggregiert Daten aus mehreren Tabellen:
    • incoming_links il: Dies ist die primäre Tabelle, in der eingehende Link-Daten gespeichert werden.
    • posts p: Die Tabelle, die sich auf Forum-Beiträge bezieht.
    • topics t: Die Tabelle, die Themen-Daten enthält.
    • categories c: Die Tabelle, die Informationen über Kategorien enthält.
    • incoming_referers ir: Die Tabelle, die Informationen über die verweisenden Links speichert.
    • incoming_domains ind: Die Tabelle, die die Domains katalogisiert, von denen Links zum Forum stammen.
    • Die INNER JOINs stellen sicher, dass wir nur Beiträge und Themen berücksichtigen, die nicht gelöscht wurden, und verknüpfen die eingehenden Link-Daten mit den richtigen Beiträgen, Themen und verweisenden Domains.
  • WHERE: Dieser Teil der Abfrage wendet Filter an, um den Ergebnissatz einzugrenzen:
    • Er beschränkt die Datensätze auf diejenigen, die zum ‘regulären’ Archetyp gehören, d. h. normale Forum-Threads (im Gegensatz zu privaten Nachrichten oder anderen speziellen Typen).
    • Er filtert die Daten, um nur Links einzuschließen, die innerhalb des angegebenen Zeitraums (:start_date bis :end_date) erstellt wurden.
    • Er wendet die Kategorie-Filterung basierend auf dem bereitgestellten :category_id-Parameter an. Wenn :category_id null ist, werden alle Kategorien berücksichtigt. Wenn :include_subcategories true ist und :category_id nicht null ist, werden auch die Unterkategorien der angegebenen Kategorie einbezogen.
  • GROUP BY: Die Abfrage gruppiert die Daten nach dem Domainnamen (ind.name), wodurch sichergestellt wird, dass die Zählungen nach jeder einzelnen verweisenden Domain partitioniert werden.
  • ORDER BY und LIMIT: Der endgültige Datensatz wird absteigend nach der Klickzahl sortiert, wodurch die Domains mit dem höchsten Verweisverkehr priorisiert werden. Die Klausel LIMIT :limit beschränkt die Anzahl der Ergebnisse, um den Bericht prägnant zu gestalten und sich auf die Top-Domains zu konzentrieren.

Beispielergebnisse

domain Clicks Topics
www.examplesite1.com 1234 123
www.examplesite2.com 56 67
www.examplesite3.com 40 95
… … …
3 „Gefällt mir“
2

Ich lese jeden einzelnen davon wirklich gerne.

Gute Arbeit!

1 „Gefällt mir“
3

Habt ihr vielleicht eine Idee, warum das zu kleine Zahlen ergeben könnte? Auf meinem Forum ist Facebook nicht einmal annähernd vorhanden, was bedeutet, dass Tausende fehlen und einige Websites gar nicht erst auftauchen. Verstecken sich Handys oder so etwas in der Quelle?