Quick question for the community- I see in the dashboard we have a top 10 for Top referred topics and traffic sources. Is this information stored in a table in a database? What I am most interested in is the records outside the top 10. Thanks for any tips!

You can find them with the data explorer plugin.

Thanks. Good call. Let me go take a look at the tables/ fields to see how they are stored.

Here is a Data Explorer query that returns the top referred topics for a given time period. I’ll add a query for the top traffic sources soon. The query returns a list of Discourse topics, and the number of times links to each topic have been clicked from an external source. The query requires you to supply start_date and end_date parameters in the form ‘yyyy-mm-dd’, for example 2020-01-08. Results are ordered by click count in descending order. The top 100 referred topics for the time period are returned. If you need more results than that, adjust the query’s LIMIT value. --[params] -- date :start_date -- date :end_date SELECT t.id AS topic_id, COUNT(p.id) AS external_click_count FROM incoming_links il JOIN posts p ON p.id = il.post_id JOIN topics t ON t.id = p.topic_id WHERE t.archetype = 'regular' AND il.created_at::date BETWEEN :start_date::date AND :end_date::date GROUP BY p.id, t.id ORDER BY external_click_count DESC LIMIT 100

For traffic sources, give this query a try. It returns the traffic source domain name, the number of clicks from the traffic source within the time period, and the number of distinct topics that were linked to from the traffic source. Results are ordered by click count in descending order. The query requires you to set start_date and end_date parameters. Dates should be in the form ‘yyyy-mm-dd’, for example 2020-01-09. The query limits the number of results to 100. If you need to change that, adjust the query’s LIMIT value. --[params] -- date :start_date -- date :end_date WITH links AS ( SELECT ind.name, t.id AS topic_id FROM incoming_links il JOIN posts p ON p.id = il.post_id JOIN topics t ON t.id = p.topic_id JOIN incoming_referers ir ON ir.id = il.incoming_referer_id JOIN incoming_domains ind ON ind.id = ir.incoming_domain_id WHERE t.archetype = 'regular' AND il.created_at::date BETWEEN :start_date::date AND :end_date::date ) SELECT name, COUNT(name) AS clicks, COUNT(DISTINCT topic_id) AS topics FROM links GROUP BY name ORDER BY clicks DESC LIMIT 100

Thanks Simon, that was really useful (and required no changes 2 years later :+1: :sweat_smile: ). Should be one of the standard detailed reports imho. I often think: “goodness, who is doing all those referals??”

Is the info Top Referred Topics/ Top Traffic Sources stored in a table in the database?

Community Building Data & reporting

anon48433008 December 21, 2019, 12:59am 5

The data explorer had this removed and I wanted to run longer quires than the dashboard, I’ve searched for the query to copy/import with no luck, is it available anywhere?

Topic		Replies	Views
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 Data & reporting sql-query , dashboard-reports , dashboard-sql	2	574	December 14, 2023
How to include more entries in the "Top Referrers" report Data & reporting sql-query	7	1224	January 7, 2020
Dashboard Report - Top Referred Topics Data & reporting sql-query , dashboard-reports , dashboard-sql	0	384	December 12, 2023

Is the info Top Referred Topics/ Top Traffic Sources stored in a table in the database?

Related topics