Условие

Однажды в группу антифрода Яндекса пришёл стажёр. К ним поступили жалобы: реклама из группы фрод-ботов, «крутившая» переходы через сайты, вдруг начала вести трафик на нестандартные для группы регионы. Аналитики предполагают: некоторые сайты подменили свой настоящий регион на регион «А», чтобы дотянуться до этой рекламы.

Бот-трафик отличается от живого тем, что в сутки бот-сайт получает существенно меньше «живых» заходов: ботов мало (они дороги), а на честных сайтах живой трафик в десятки раз выше.

У вас есть таблица logs (timestamp, site_id, city_id) за период с 2025-07-30 по 2025-08-10. Каждая запись — посещение одним устройством. Нужно найти все сайты, трафик которых состоял в основном из ботов и которые подменили регион на A.

Решение

Подход

Идея в две стадии:

Среди всех сайтов, у которых есть посещения с city_id = 'A', найти те, у которых дневной трафик подозрительно низкий (мало посещений за сутки = бот-сайт).
Дополнительно посмотреть на долю «А» в общем трафике сайта: настоящие сайты Москвы тоже имеют большой трафик из «А», но их суммарный трафик высокий и распределён по дням; бот-сайты имеют почти весь трафик из «А» и низкие объёмы.

Бот-сайт — это сайт с малой суточной активностью (например, медиана посещений в день < порог) и доминированием «А» в трафике.

Реализация (PostgreSQL / ClickHouse-подобный SQL)

WITH per_day AS (
    SELECT
        site_id,
        DATE(timestamp) AS d,
        COUNT(*) AS hits,
        SUM(CASE WHEN city_id = 'A' THEN 1 ELSE 0 END) AS hits_A
    FROM logs
    WHERE timestamp >= '2025-07-30' AND timestamp < '2025-08-11'
    GROUP BY site_id, DATE(timestamp)
),
agg AS (
    SELECT
        site_id,
        AVG(hits)        AS avg_hits_day,
        SUM(hits)        AS total_hits,
        SUM(hits_A)      AS total_A,
        SUM(hits_A) * 1.0 / NULLIF(SUM(hits), 0) AS share_A
    FROM per_day
    GROUP BY site_id
)
SELECT site_id
FROM agg
WHERE total_A > 0                  -- сайт вообще касается города A
  AND avg_hits_day < 50            -- мало живого трафика
  AND share_A > 0.7                -- доминирует A
ORDER BY share_A DESC, avg_hits_day ASC;

Пороги (50, 0.7) — гиперпараметры, их подбирают по распределению и метке.

Анализ

Можно усилить: посмотреть на распределение avg_hits_day для всех сайтов и взять, например, нижний 5-перцентиль; по share_A использовать z-score относительно сайтов того же объёма.

WITH stats AS (
    SELECT
        site_id,
        AVG(daily_hits) AS mean_d,
        STDDEV(daily_hits) AS sd_d
    FROM per_day
    GROUP BY site_id
)
-- сравнить с глобальным медианным сайтом

Подводные камни

Сайты Москвы, которые легально имеют большой трафик из A. Их отсеиваем по абсолютной величине трафика.
Малые сайты в принципе. Если у сайта 5 заходов всего, делать выводы по доле — преждевременно. Минимальный порог по total_hits.
Граничные даты. Период «по 10.08» включительно — < '2025-08-11'.
city_id может быть пустым — нужно NULLIF либо явное условие.
Подмена в другую сторону. Возможно, бот сайт подменил «А» на что-то другое и попал в выборку — но это уже не наш кейс.

Альтернативы

Изоляционный лес / DBSCAN на признаках (avg_hits_day, share_A, day_variance) найдёт ту же аномальную группу.
Сравнение с эталоном: взять «честные» сайты с похожими доменами/категориями и посмотреть, как сильно отклоняется наш кандидат.

Эталонный ответ

WITH per_day AS (
    SELECT site_id, DATE(timestamp) d, COUNT(*) hits,
           SUM((city_id = 'A')::int) hits_A
    FROM logs
    GROUP BY site_id, DATE(timestamp)
),
agg AS (
    SELECT site_id, AVG(hits) avg_d, SUM(hits) tot, SUM(hits_A) tot_A
    FROM per_day GROUP BY site_id
)
SELECT site_id FROM agg
WHERE tot_A > 0 AND avg_d < 50 AND tot_A * 1.0 / tot > 0.7;

Бот-сайты = низкий дневной трафик (мало живых) + доля посещений из A высокая.

Яндекс — Боты и только боты: найти фейковые сайты с подменой региона