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.

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?

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

merefield (Robert) 2022 年3 月 16 日 21:45 13

谢谢 Simon，这确实很有用（而且两年后仍然无需更改）。依我看，这应该是标准的详细报告之一。
我常常在想：“天哪，到底是谁在做所有这些推荐？？”

话题		回复	浏览量
How to see exact link of ‘top referred topics’ Data & reporting sql-query , dashboard-reports	5	819	2023 年9 月 29 日
Top traffic sources by topic Data & reporting	5	699	2025 年3 月 21 日
Dashboard Report - Top Traffic Sources Data & reporting sql-query , dashboard-reports , dashboard-sql	2	574	2023 年12 月 14 日
How to include more entries in the "Top Referrers" report Data & reporting sql-query	7	1224	2020 年1 月 7 日
Dashboard Report - Top Referred Topics Data & reporting sql-query , dashboard-reports , dashboard-sql	0	384	2023 年12 月 12 日

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

相关话题