Top traffic sources by topic

Data & reporting
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