Борьба с оттоком (поиск интереса к конкурентам)

Модуль QoE: возможности DPI для превентивной технической поддержки и борьбы с оттоком на Rutube:

Абоненты интересуются предложениями конкурентов. Для этого они начинают регулярно посещать сайты конкурентов и агрегаторов. Поисковые запросы в кликстриме не видны, но можно отследить конечные URL.

DPI выгружает CickStream — все HTTP/HTTPS запросы пользователей в сети интернет. QoE модуль агрегирует информацию по каждому IP (login, если используется). В статистике фиксируется URL для HTTP и имя домена для HTTPS. Необходимые действия для поиска:

  1. Собрать список сайтов конкурентов
  2. Собрать список сайтов-агрегаторов, если они есть в вашем регионе. Примеры поисковых запросов: "найти интернет провайдера по адресу", "подключить интернет в [городе]"
  3. Перейти в раздел QoE Аналитика → Абоненты → Кликстрим
  4. Настроить новый фильтр запроса, внести туда подготовленные списки.
    Пример фильтра запроса для графического интерфейса: 
    match host (?i)(\W|^)(rt.ru|domru.ru.com)(\W|$)
  5. Рекомендуется работать с абонентами, которые 3 и более раз в неделю посещают сайты из выгрузки. Для этого можно:
    1. Выгружать данные ежедневно в свою базу данных через API или напрямую из Click House QoE, уже в ней накладывать фильтры.
    2. Сделать триггер в графическом интерфейсе с заданными параметрами.

Пример запроса в базу:
Скачать скрипт здесь

Скрипт возвращает список абонентов, посещавших указанные сайты конкурентов ЛЮБОЕ количество раз за последние 24 часа.

Запуск: sh subs_search_for_competitors_sample.sh

Параметры в скрипте:
format="CSV" — формат вывода. По умолчанию CSV. Возможные форматы: https://clickhouse.com/docs/en/interfaces/formats/
periodSecs=24*3600 — период в секундах. По умолчанию 24 часа
hostsMatch="(?i)(\W|)(rt.ru|domru.ru.com)(\W|$)" — регулярное выражение для поиска по нескольким хостам. Сюда рекомендуется добавлять актуальных местных конкурентов.