# Dashboard Report - Top Traffic Sources

**URL:** <https://meta.discourse.org/t/dashboard-report-top-traffic-sources/288745>\
**Category:** Data & reporting\
**Tags:** sql-query, dashboard-reports, dashboard-sql\
**Created:** [December 14, 2023, 9:23pm UTC](https://meta.discourse.org/t/dashboard-report-top-traffic-sources/288745 "2023-12-14T21:23:43Z")\
**Posts on this page:** 1\
**Showing post:** 1

<div class="post-metadata">

**Author:** ![SaraDev](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/saradev/32/335139_2.png) [@SaraDev](https://meta.discourse.org/u/SaraDev)\
**Post date:** [December 14, 2023, 9:23pm UTC](https://meta.discourse.org/t/dashboard-report-top-traffic-sources/288745/1 "2023-12-14T21:23:44Z")

</div>

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.

```sql
-- [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 |
| … | … | |

---

_[View the full topic](https://meta.discourse.org/t/dashboard-report-top-traffic-sources/288745)._