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 of YYYY-MM-DD. 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 top N 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 … …

Really like reading each one of these. Nice job!

Dashboard Report - Top Traffic Sources

Jagster (Jakke Flemming) December 14, 2023, 11:34pm 3

Do you guys have any ideas why this could give way too small numbers? On my forum Facebook is not even close meaning thousands are missing and some sites aren’t there at all. Mobiles are hiding source or something?

Topic		Replies	Views
Reports > Top Traffic Sources - Topics Stat Issue Bug dashboard-reports	1	663	February 16, 2021
Dashboard Report - Top Referrers Data & reporting sql-query , dashboard-reports , dashboard-sql	0	389	December 12, 2023
Dashboard Report - Top Referred Topics Data & reporting sql-query , dashboard-reports , dashboard-sql	0	384	December 12, 2023
How to see exact link of ‘top referred topics’ Data & reporting sql-query , dashboard-reports	5	817	September 29, 2023
Top traffic sources by topic Data & reporting	5	699	March 21, 2025

Dashboard Report - Top Traffic Sources

Related topics