Отчет по панели управления: основные источники трафика

Создание сообщества Данные и отчетность
, ,
1

Это SQL-версия отчёта панели управления по основным источникам трафика.

Данный отчёт панели управления предоставляет анализ основных внешних источников, ссылающихся на посты на сайте Discourse в указанном диапазоне дат и, при необходимости, в конкретной категории или её подкатегориях.

Определяя, какие внешние домены ссылаются на их сообщество, администраторы могут оценить эффективность маркетинговых усилий, уточнить стратегию контента для привлечения более широкой аудитории, изучить возможности сотрудничества с интересными сайтами, оптимизировать SEO и управлять любыми потенциальными кризисами, возникающими из-за нежелательных источников трафика.

-- [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

Параметры

  • Параметры даты:
    • Запрос принимает два параметра даты, :start_date и :end_date, которые определяют диапазон дат для отчёта. Оба параметра даты принимают формат даты YYYY-MM-DD.
  • Параметры категории:
    • :category_id: Целочисленный параметр, который можно установить в ID конкретной категории, чтобы сузить анализ до постов в этой категории. Если он установлен в null или не предоставлен, учитываются посты из всех категорий.
    • :include_subcategories: Булев параметр, который определяет, включать ли посты из подкатегорий указанной :category_id. Если установлено в true, отчёт будет включать ссылки на посты как в указанной категории, так и в её подкатегориях; если false, учитывается только указанная категория.
  • Ограничение результатов:
    • :limit: Целочисленный параметр, который ограничивает количество строк, возвращаемых запросом, до топ-N результатов на основе количества кликов. Значение по умолчанию, указанное в запросе, равно 10.

Пояснение к SQL-запросу

  • SELECT: Отчёт структурирован так, чтобы выбирать три элемента информации:
    • ind.name AS domain: Этот столбец будет отображать имя каждого домена, который направлял пользователей на форум.
    • COUNT(*) AS clicks: Этот столбец показывает общее количество кликов по входящим ссылкам с каждого домена.
    • COUNT(DISTINCT p.topic_id) AS topics: Этот столбец представляет количество уникальных тем, к которым был осуществлён доступ через эти входящие ссылки.
  • FROM и JOIN: Запрос агрегирует данные из нескольких таблиц:
    • incoming_links il: Это основная таблица, где хранятся данные о входящих ссылках.
    • posts p: Таблица, связанная с постами форума.
    • topics t: Таблица, содержащая данные о темах.
    • categories c: Таблица, содержащая информацию о категориях.
    • incoming_referers ir: Таблица, хранящая информацию о ссылках-реферерах.
    • incoming_domains ind: Таблица, каталогизирующая домены, из которых исходят ссылки на форум.
    • INNER JOINs гарантируют, что мы учитываем только посты и темы, которые не были удалены, и связывают данные о входящих ссылках с правильными постами, темами и доменами-реферерами.
  • WHERE: Эта часть запроса применяет фильтры для сужения набора результатов:
    • Она ограничивает записи только теми, которые относятся к архетипу ‘regular’, т. е. обычным веткам форума (в отличие от личных сообщений или других специальных типов).
    • Она фильтрует даты, включая только ссылки, созданные в указанный период (:start_date по :end_date).
    • Она применяет фильтрацию по категориям на основе предоставленного параметра :category_id. Если :category_id равен null, учитываются все категории. Если :include_subcategories равен true, а :category_id не равен null, также включаются подкатегории указанной категории.
  • GROUP BY: Запрос группирует данные по имени домена (ind.name), обеспечивая, чтобы подсчёты были разделены по каждому уникальному домену-рефереру.
  • ORDER BY и LIMIT: Финальный набор данных сортируется по убыванию количества кликов, тем самым отдавая приоритет доменам с наибольшим реферальным трафиком. Клаузула LIMIT :limit ограничивает количество результатов, делая отчёт лаконичным и сфокусированным на основных доменах.

Пример результатов

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

Мне очень нравится читать каждую из них.

Отличная работа!

3

Ребята, у вас есть идеи, почему здесь получаются слишком маленькие цифры? На моём форуме данные по Facebook вообще не сходятся — не хватает тысяч, а некоторые сайты вообще отсутствуют. Мобильные устройства скрывают источник или что-то в этом роде?