Top traffic sources by topic

Data & reporting
1

I tried to find stats for which are the top traffic source a.k.a. referral traffic for a particular topic which got more attention than other topics. I could only find generic top source for all topics as a whole.

Is this not available? And if not, I think it should be planned :slight_smile:
It could even be linked already on the topic itself when you expand the bottom part where you currently see popular links and frequent posters.
Would also be interesting to have the full referral URL available somewhere and not only the domain.

1 Like
2

I think at least most of that is available with the data explorer plugin

3

Not out of the box it seems, so it seems you need to create the correct SQL query to produce these results.

4

Sorry. That is indeed the case. I frequently fail to notice that things are in feature. If you want to solve your problem, you can get help with the needed sql.

5

To find these metrics on your site you can use a Data Explorer query like the following:

Topic External Traffic Sources

-- [params]
-- int :topic_id = 8732

SELECT 
  ind.name AS domain,                   -- External domain referring traffic
  COUNT(*) AS clicks                    -- Total clicks from this source
FROM incoming_links il
INNER JOIN posts p ON p.deleted_at IS NULL AND p.id = il.post_id
INNER JOIN topics t ON t.deleted_at IS NULL AND t.id = p.topic_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
  AND t.id = :topic_id                  -- Filter for the specified topic
GROUP BY ind.name
ORDER BY clicks DESC

This query uses a method similar to the Dashboard Report - Top Traffic Sources to analyze information about external domains that have referred traffic to posts within a specific topic. The report uses a :topic_id: parameter that specifies the topic for which you want to view incoming traffic for.

The query will return a list of external domains (domain ) and the total number of clicks (clicks ) they referred to posts in the specified topic, sorted by the number of clicks in descending order.

2 Likes
6

Adding a slightly different variation of the Topic External Traffic Sources query above for reference here as well.

This version uses an int_list parameter for the topic_ids, so if you wanted to specify multiple topic_ids to run the query for, you can use this query.

-- [params]
-- int_list :topic_ids = 12345

SELECT 
  ind.name AS domain,                   -- External domain referring traffic
  COUNT(*) AS clicks                    -- Total clicks from this source
FROM incoming_links il
INNER JOIN posts p 
  ON p.deleted_at IS NULL 
  AND p.id = il.post_id
INNER JOIN topics t 
  ON t.deleted_at IS NULL 
  AND t.id = p.topic_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.id IN (:topic_ids)              -- Filter for the specified list of topics
  AND ind.name != '127.0.0.1'
GROUP BY ind.name
ORDER BY clicks DESC
2 Likes