This is an SQL version of the Dashboard Report for Top Traffic Sources.
This dashboard report provides an analysis of the top external sources that link to posts on a Discourse site within a specified date range and, optionally, a particular category or its subcategories.
By identifying which external domains link to their community, admin can assess the effectiveness of marketing efforts, refine their content strategy to attract a broader audience, explore collaborative opportunities with engaging sites, optimize for SEO, and manage any potential crises arising from unwanted traffic sources.
-- [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
Parameters
- Date Parameters:
- The query accepts two date parameters,
:start_date
and:end_date
, which define the date range for the report. Both date parameters accept the date format ofYYYY-MM-DD
.
- The query accepts two date parameters,
- Category Parameters:
:category_id
: An integer parameter that can be set to the ID of a specific category to narrow down the analysis to posts within that category. If it’s set to null or not provided, posts from all categories are considered.:include_subcategories
: A boolean parameter that controls whether to include posts from subcategories of the specified:category_id
. If set to true, the report will include links to posts in both the specified category and its subcategories; if false, only the specified category will be considered.
- Results Limit:
:limit
: An integer parameter that limits the number of rows returned by the query to the topN
results based on the number of clicks. The default value provided in the query is 10.
SQL Query Explanation
- SELECT: The report is structured to select three pieces of information:
ind.name AS domain
: This column will display the name of each domain that has referred users to the forum.COUNT(*) AS clicks
: This column shows the total number of incoming link clicks from each domain.COUNT(DISTINCT p.topic_id) AS topics
: This column represents the number of unique topics that have been accessed through those incoming links.
- FROM AND JOIN: The query aggregates data from several tables:
incoming_links il
: This is the primary table where incoming link data is stored.posts p
: The table related to forum posts.topics t
: The table containing topics data.categories c
: The table containing information about categories.incoming_referers ir
: The table that stores information about the referer links.incoming_domains ind
: The table that catalogs the domains from which links to the forum originate.- The
INNER JOINs
ensure that we only consider posts and topics that have not been deleted and link the incoming link data to the correct posts, topics, and refering domains.
- WHERE: This part of the query applies filters to narrow down the result set:
- It limits the records to only those that are in the ‘regular’ archetype, ie. normal forum threads (as opposed to private messages or other special types).
- It filters the dates to include only links created within the specified period (
:start_date
to:end_date
). - It applies category filtering based on the provided
:category_id
parameter. If:category_id
is null, all categories are considered. If:include_subcategories
is true and:category_id
is not null, it also includes subcategories of the specified category.
- GROUP BY: The query groups the data by the domain name (
ind.name
), ensuring that the counts are partitioned by each distinct referring domain. - ORDER BY and LIMIT: The final dataset is sorted in descending order based on the click count, hence prioritizing the domains with the highest referral traffic. The
LIMIT :limit
clause restricts the number of results to make the report concise and focused on the top domains.
Example Results
domain | Clicks | Topics |
---|---|---|
www.examplesite1.com |
1234 | 123 |
www.examplesite2.com |
56 | 67 |
www.examplesite3.com |
40 | 95 |
… | … |