Search for problematic subscribers

Subscribers are expected to pay and use the service — this is normal behavior. If they do not use it (even when paying on time), this may indicate a problem. Statistics allow identifying active and inactive subscribers.

Unfortunately, statistics cannot identify completely inactive subscribers who have no connection to the router (e.g., unplugged cable, powered off router). To find such cases, you need to compare statistics with data exported from the billing system.

It is recommended to segment subscribers into three activity groups:

1. Normal
2. Weak (technical)
3. None

Activity can be measured by:

1. Number of sessions
2. Number of hosts

The normal level can be determined after plotting a distribution from the data export, but usually these values are in the tens per day, at least 3–4 times per week.

The filter can be configured in the GUI under QoE Analytics → Subscribers → ClickStream.

If subscribers move from the “normal activity” segment to the “weak activity” one and remain there for more than three weeks, this may indicate a problem.

Database query method:
Will appear later.

Subscribers may show interest in competitors’ offers by regularly visiting competitor or aggregator websites. Search queries are not visible in ClickStream, but destination URLs can be tracked.

Actions:

1. Make a list of competitor websites.
2. Make a list of aggregator sites relevant to your region. Example searches: “find internet provider by address”, “connect internet in [city]”.
3. Go to QoE Analytics → Subscribers → ClickStream
4. Create a new query filter and add these lists.
   
   Example of a filter for the GUI:

   match host (?i)(\W|^)(rt.ru|domru.ru.com)(\W|$)

It is recommended to focus on subscribers visiting these sites three 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 GUI with the required parameters.

Example database query:
Download script here

The script returns a list of subscribers who visited the specified competitor sites any number of times during the last 24 hours.

Run sh subs_search_for_competitors_sample.sh

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

A regular user typically visits speed test services only when having connection issues. Possible causes:

• low tariff speed (reached the cap)
• operator network issues
• uplink issues (unstable service)
• weak Wi-Fi

All these issues can be identified using DPI metrics or network monitoring data.

You can use a ready-made query that includes the most popular services. Add new ones if necessary:

https://www.speedtest.net/
internet.yandex.ru
https://2ip.ru/speed/
https://internetometer.ru/
https://speedtestt.ru/
https://rt-internet.ru/proverit-skorost
https://skoromer.ru/
https://www.bandwidthplace.com/

It is recommended to focus on subscribers visiting these sites three or more times per week. You can:

• Export data daily via API or directly from ClickHouse QoE and apply filters there.
• Create a trigger in the GUI with the required parameters.
• Use it together with competitor queries for better accuracy.

Example GUI filter:

match host (?i)(\W|^)(speedtest|2ip.ru|fast.com|internetometer.ru|speedtestt.ru|rt-internet.ru|skoromer.ru/|www.bandwidthplace.com)(\W|$)

(?i)(\W|^)(speedtest|2ip|nperf|internetometer|bandwidthplace.com|test.byfly.by|skoromer.ru)(\W|$)

Example database query:
Download script here

Run sh subs_speedtest_sample.sh

Script parameters:
format="CSV" — output format. Default: CSV. Possible formats: https://clickhouse.com/docs/en/interfaces/formats/
periodSecs=24*3600 — period in seconds. Default: 24 hours
hostsMatch="(?i)(\W|)(speedtest.net|internet.yandex.ru|2ip.ru|internetometer.ru|speedtestt.ru|rt-internet.ru|skoromer.ru| — regular expression for searching multiple hosts.

The simplest and most well-studied DPI metric for assessing connection quality is RTT. DPI can measure RTT and separate results by direction (to and from subscriber) and by protocols or devices if necessary. A high “RTT from subscriber” value over a long period likely means that the subscriber is experiencing problems accessing online services such as gaming, video, or communication. Usually, this is due to the Wi-Fi network, but it can also indicate congestion in the operator’s network.

Actions:

1. Go to QoE Analytics → Subscribers → NetFlow
2. Create a filter where:
   • it is recommended to limit the search by protocol http/https to exclude specifics of other protocols when establishing a TCP connection
   • specify average speed to select active users
   • set a lower threshold for RTT from the client

It is recommended to focus on subscribers whose average (or better, median) RTT is above 100 for the last 24 hours. 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 GUI with the required parameters.

When analyzing, consider the following:

1. Check the geographic distribution of the “problem” sample. Clusters usually indicate network overload or old switches, which can be fixed on the operator side. Sometimes the “bad” area is connected via an outdated technology (DSL or radio), where high RTT is already “a feature, not a bug”.
2. The subscriber might not feel discomfort due to latency (and may say so during a call), for example:
   • IoT devices with weak signals
   • old equipment and low expectations
   • does not use online services
3. Wi-Fi issues may be caused by:
   • crowded 2.4 GHz band in apartment buildings
   • weak router
   • large or complex apartment/house layout

Sometimes it is impossible to determine the host to which the subscriber connected with high RTT:

This may happen for three reasons:

• connection by IP
• non-HTTP protocol
• no SNI specified

In addition to RTT, the retransmission percentage metric can be used. If retransmits are significantly higher than the baseline (usually 4–5%) for more than 30 minutes, it indicates service degradation. Using retransmits without RTT is not recommended due to possible false positives.

Example database query:
Download script

Script parameters:
format="CSV" — output format. Default: CSV. Possible formats: https://clickhouse.com/docs/en/interfaces/formats/
periodSecs=24*3600 — period in seconds. Default: 24 hours
rttMore=100 — RTT threshold. Default: 100

1. Segment the subscriber base and identify possible causes of poor statistics:
   • outdated access technology
   • IoT devices present
   • weak routers
2. Check service quality using available metrics from other systems:
   • network monitoring (especially if many clients are connected to one node)
   • support requests
   • irregular payments

It is recommended to create a dedicated “problem subscribers” database and organize regular data export from various systems. A set of “problem” indicators is far more effective than a single one.

1. Conduct satisfaction surveys:
   • by phone
   • using a redirect form through DPI
2. Apply retention mechanisms (promotions, discounts, equipment replacement, etc.)

Having a “problem subscribers” database gives the operator a new measurable indicator — “network health”.