Churn prevention (detecting interest in competitors)

Subscribers become interested in competitors’ offers. To do this, they start regularly visiting competitors’ websites and aggregators. Search queries are not visible in clickstream data, but the final URLs can be tracked.

DPI exports ClickStream — all HTTP/HTTPS requests of users on the Internet. The QoE module aggregates information for each IP (login, if used). The statistics record the URL for HTTP and the domain name for HTTPS.

Required steps for detection:

  1. Collect a list of competitors’ websites
  2. Collect a list of aggregator websites, if any exist in your region. Example search queries: "find an internet provider by address", "connect internet in [city]"
  3. Go to QoE Analytics → Subscribers → Clickstream
  4. Configure a new query filter and add the prepared lists.
    Example of a query filter for the graphical interface: 
    match host (?i)(\W|^)(rt.ru|domru.ru.com)(\W|$)
  5. It is recommended to work with subscribers who visit websites from the export 3 or more times per week. To do this, you can:
    1. Export data daily to your own database via API or directly from ClickHouse QoE, and apply filters there.
    2. Create a trigger in the graphical interface with predefined parameters.

Example database query:
Download the script here

The script returns a list of subscribers who visited the specified competitors’ websites ANY number of times over the last 24 hours.

Run: sh subs_search_for_competitors_sample.sh

Script parameters:
format="CSV" — output format. Default is CSV. Possible formats: https://clickhouse.com/docs/en/interfaces/formats/
periodSecs=24*3600 — period in seconds. Default is 24 hours
hostsMatch="(?i)(\W|)(rt.ru|domru.ru.com)(\W|$)" — regular expression for searching multiple hosts. It is recommended to add relevant local competitors here.