Dashboard Report - Top Traffic Sources

data & reporting
, ,
1

This is an SQL version of the Dashboard Report for Top Traffic Sources.

This dashboard report provides an analysis of the top external sources that link to posts on a Discourse site within a specified date range and, optionally, a particular category or its subcategories.

By identifying which external domains link to their community, admin can assess the effectiveness of marketing efforts, refine their content strategy to attract a broader audience, explore collaborative opportunities with engaging sites, optimize for SEO, and manage any potential crises arising from unwanted traffic sources.

-- [params]
-- date :start_date = 2024-01-01
-- date :end_date= 2025-01-01
-- null int :category_id
-- int :limit = 10
-- boolean :include_subcategories = true

SELECT 
  ind.name AS domain,
  COUNT(*) AS clicks,
  COUNT(DISTINCT p.topic_id) AS topics
FROM incoming_links il
  INNER JOIN posts p ON p.deleted_at ISNULL AND p.id = il.post_id
  INNER JOIN topics t ON t.deleted_at ISNULL AND t.id = p.topic_id
  INNER JOIN categories c ON c.id = t.category_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.archetype = 'regular'
AND il.created_at::date BETWEEN :start_date AND :end_date
AND (:category_id IS NULL
  OR t.category_id = :category_id
  OR (:include_subcategories AND c.parent_category_id = :category_id))
GROUP BY ind.name
ORDER BY clicks DESC
LIMIT :limit

Parameters

  • Date Parameters:
    • The query accepts two date parameters, :start_date and :end_date, which define the date range for the report. Both date parameters accept the date format of YYYY-MM-DD.
  • Category Parameters:
    • :category_id: An integer parameter that can be set to the ID of a specific category to narrow down the analysis to posts within that category. If it’s set to null or not provided, posts from all categories are considered.
    • :include_subcategories: A boolean parameter that controls whether to include posts from subcategories of the specified :category_id. If set to true, the report will include links to posts in both the specified category and its subcategories; if false, only the specified category will be considered.
  • Results Limit:
    • :limit: An integer parameter that limits the number of rows returned by the query to the top N results based on the number of clicks. The default value provided in the query is 10.

SQL Query Explanation

  • SELECT: The report is structured to select three pieces of information:
    • ind.name AS domain: This column will display the name of each domain that has referred users to the forum.
    • COUNT(*) AS clicks: This column shows the total number of incoming link clicks from each domain.
    • COUNT(DISTINCT p.topic_id) AS topics: This column represents the number of unique topics that have been accessed through those incoming links.
  • FROM AND JOIN: The query aggregates data from several tables:
    • incoming_links il: This is the primary table where incoming link data is stored.
    • posts p: The table related to forum posts.
    • topics t: The table containing topics data.
    • categories c: The table containing information about categories.
    • incoming_referers ir: The table that stores information about the referer links.
    • incoming_domains ind: The table that catalogs the domains from which links to the forum originate.
    • The INNER JOINs ensure that we only consider posts and topics that have not been deleted and link the incoming link data to the correct posts, topics, and refering domains.
  • WHERE: This part of the query applies filters to narrow down the result set:
    • It limits the records to only those that are in the ‘regular’ archetype, ie. normal forum threads (as opposed to private messages or other special types).
    • It filters the dates to include only links created within the specified period (:start_date to :end_date).
    • It applies category filtering based on the provided :category_id parameter. If :category_id is null, all categories are considered. If :include_subcategories is true and :category_id is not null, it also includes subcategories of the specified category.
  • GROUP BY: The query groups the data by the domain name (ind.name), ensuring that the counts are partitioned by each distinct referring domain.
  • ORDER BY and LIMIT: The final dataset is sorted in descending order based on the click count, hence prioritizing the domains with the highest referral traffic. The LIMIT :limit clause restricts the number of results to make the report concise and focused on the top domains.

Example Results

domain Clicks Topics
www.examplesite1.com 1234 123
www.examplesite2.com 56 67
www.examplesite3.com 40 95
… …
2 Likes
2

Really like reading each one of these.

Nice job!

1 Like
3

Do you guys have any ideas why this could give way too small numbers? On my forum Facebook is not even close meaning thousands are missing and some sites aren’t there at all. Mobiles are hiding source or something?