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
--[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
end_date parameters. Dates should be in the form ‘yyyy-mm-dd’, for example
The query limits the number of results to 100. If you need to change that, adjust the query’s
--[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 ). Should be one of the standard detailed reports imho.
I often think: “goodness, who is doing all those referals??”