Отчет по панели управления - Трафик сайта

[SaraDev (Sara Devlaeminck)]

Это SQL-версия отчёта по трафику сайта.

Данный отчёт предоставляет обзор метрик трафика сайта, сгруппированных по датам. Он разбивает просмотры страниц на несколько категорий, включая просмотры из браузеров авторизованных пользователей, анонимные просмотры из браузеров, просмотры от поисковых роботов (краулеров) и другие рассчитанные типы трафика.

См.: Понимание отслеживания просмотров страниц на хостинговых планах для получения дополнительных сведений об этом отчёте.

-- [params]
-- date :start_date = 2025-01-01
-- date :end_date = 2025-02-01

WITH first_browser_pageview_date AS (
  SELECT 
    MIN(date) AS first_date
  FROM 
    application_requests
  WHERE 
    req_type IN (13, 15) -- 13: page_view_anon_browser, 15: page_view_logged_in_browser
)

SELECT 
  date,
  SUM(CASE WHEN req_type = 15 THEN count ELSE 0 END) AS page_view_logged_in_browser, -- 15: page_view_logged_in_browser
  SUM(CASE WHEN req_type = 13 THEN count ELSE 0 END) AS page_view_anon_browser, -- 13: page_view_anon_browser
  SUM(CASE WHEN req_type = 6 THEN count ELSE 0 END) AS page_view_crawler, -- 6: page_view_crawler
  SUM(
    CASE 
      WHEN req_type = 8 THEN count -- 8: page_view_anon
      WHEN req_type = 7 THEN count -- 7: page_view_logged_in
      WHEN req_type = 13 THEN -count -- 13: page_view_anon_browser
      WHEN req_type = 15 THEN -count -- 15: page_view_logged_in_browser
      ELSE 0
    END
  ) AS page_view_other
FROM 
  application_requests
WHERE 
  date >= :start_date 
  AND date <= :end_date
  AND date >= (SELECT first_date FROM first_browser_pageview_date)
GROUP BY 
  date
ORDER BY 
  date ASC

Объяснение SQL-запроса

• Параметры
  • :start_date: Дата начала отчёта.
  • :end_date: Дата окончания отчёта.
• CTE: first_browser_pageview_date
  • Эта таблица общего назначения (CTE) определяет самую раннюю дату, когда был зафиксирован просмотр страницы из браузера (анонимный или авторизованный).
  • Фильтрует req_type по значениям 13 (анонимные просмотры из браузеров) и 15 (просмотры из браузеров авторизованных пользователей).
• Основной запрос
  • Агрегирует данные из таблицы application_requests по date.
  • Фильтрует строки, включая только те, которые находятся в указанном диапазоне дат (:start_date – :end_date) и после даты первого просмотра страницы из браузера (first_browser_pageview_date).
  • Рассчитывает следующие метрики:
    • page_view_logged_in_browser: Общее количество просмотров страниц из браузеров авторизованных пользователей (req_type = 15).
    • page_view_anon_browser: Общее количество анонимных просмотров страниц из браузеров (req_type = 13).
    • page_view_crawler: Общее количество просмотров страниц от краулеров (req_type = 6).
    • page_view_other: Общее количество других просмотров страниц, рассчитываемое как:
      • Сложение количеств для req_type = 8 (анонимные просмотры) и req_type = 7 (просмотры авторизованных пользователей).
      • Вычитание количеств для req_type = 13 (анонимные просмотры из браузеров) и req_type = 15 (просмотры из браузеров авторизованных пользователей).
• Результаты
  • Просмотры из браузеров авторизованных пользователей: Общее количество просмотров страниц авторизованными пользователями через браузер.
  • Анонимные просмотры из браузеров: Общее количество просмотров страниц анонимными пользователями через браузер.
  • Просмотры от краулеров: Общее количество просмотров страниц, сгенерированных краулерами (например, ботами поисковых систем).
  • Другие просмотры страниц: Включает другие типы просмотров страниц, не классифицируемые как просмотры из браузеров или от краулеров.

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

Дата	Просмотры из браузеров авторизованных пользователей	Анонимные просмотры из браузеров	Просмотры от краулеров	Другие просмотры страниц
2025-03-20	150	300	50	20
2025-03-21	200	400	60	30
2025-03-22	180	350	55	25