Real-Time Subscriber Experience Analytics for Telecoms

Subscriber churn is most often preceded by degraded experience — slow data speeds, dropped calls, or excessive latency. RisingWave, a PostgreSQL-compatible streaming database, lets telecoms compute per-subscriber Quality of Experience (QoE) scores in real time from IMSI-level network events, enabling proactive retention actions before a customer decides to churn.

Why Subscriber Experience Analytics Matters

Net Promoter Score surveys and churn reports are lagging indicators — they tell you what already happened. Telecoms that win on experience use leading indicators: per-subscriber signal quality, data session performance, and service interruption frequency tracked continuously.

A subscriber experiencing repeated dropped calls in the same location is almost certainly going to churn unless someone intervenes. If the NOC can identify that IMSI 234150123456789 has experienced three dropped calls and average RSRP below -110 dBm in the past hour, retention systems can proactively offer a network booster or a bill credit.

Key per-subscriber signals:

• IMSI (International Mobile Subscriber Identity) — permanent subscriber identifier
• IMEI (International Mobile Equipment Identity) — device identifier
• RSRP / RSRQ reported by the UE measurement reports
• Session throughput (Mbps) from PCRF/UPF
• Call drop rate from VoLTE/VoNR call detail records
• Latency and jitter from active probes or passive DPI
• Packet loss % from session logs

How Streaming SQL Solves This

RisingWave ingests measurement report events, session events, and call detail records from Kafka in real time. Materialized views compute per-subscriber rolling KPI aggregations. A composite QoE score combines signal quality, throughput, and reliability into a single metric that drives downstream retention workflows.

Because RisingWave is PostgreSQL-compatible, CRM and retention platforms can query subscriber QoE directly via SQL without building separate APIs.

Step-by-Step Tutorial

Step 1: Data Source

Create two sources: UE measurement reports and session/call events.

-- UE measurement reports (from RRC or MDT)
CREATE SOURCE ue_measurement_reports (
    imsi            VARCHAR,
    imei            VARCHAR,
    cell_id         VARCHAR,
    rsrp_dbm        DOUBLE PRECISION,
    rsrq_db         DOUBLE PRECISION,
    sinr_db         DOUBLE PRECISION,
    event_time      TIMESTAMPTZ
)
WITH (
    connector      = 'kafka',
    topic          = 'telecom.ue.measurements',
    properties.bootstrap.server = 'kafka:9092',
    scan.startup.mode = 'latest'
)
FORMAT PLAIN ENCODE JSON
( TIMESTAMP COLUMN = event_time );

-- Data session events (from PCRF/UPF)
CREATE SOURCE data_session_events (
    imsi                VARCHAR,
    session_id          VARCHAR,
    throughput_dl_mbps  DOUBLE PRECISION,
    throughput_ul_mbps  DOUBLE PRECISION,
    latency_ms          DOUBLE PRECISION,
    jitter_ms           DOUBLE PRECISION,
    packet_loss_pct     DOUBLE PRECISION,
    session_state       VARCHAR,  -- 'ACTIVE', 'DROPPED', 'COMPLETED'
    event_time          TIMESTAMPTZ
)
WITH (
    connector      = 'kafka',
    topic          = 'telecom.data.sessions',
    properties.bootstrap.server = 'kafka:9092',
    scan.startup.mode = 'latest'
)
FORMAT PLAIN ENCODE JSON
( TIMESTAMP COLUMN = event_time );

Step 2: Core View

Compute 1-hour rolling QoE scores per subscriber, combining signal quality, throughput, and session reliability.

CREATE MATERIALIZED VIEW subscriber_signal_1h AS
SELECT
    imsi,
    window_start,
    window_end,
    AVG(rsrp_dbm)  AS avg_rsrp,
    AVG(rsrq_db)   AS avg_rsrq,
    AVG(sinr_db)   AS avg_sinr,
    COUNT(*)       AS measurement_count
FROM TUMBLE(ue_measurement_reports, event_time, INTERVAL '1 HOUR')
GROUP BY imsi, window_start, window_end;

CREATE MATERIALIZED VIEW subscriber_session_1h AS
SELECT
    imsi,
    window_start,
    window_end,
    AVG(throughput_dl_mbps)  AS avg_throughput_dl,
    AVG(latency_ms)          AS avg_latency,
    AVG(jitter_ms)           AS avg_jitter,
    AVG(packet_loss_pct)     AS avg_packet_loss,
    COUNT(*) FILTER (WHERE session_state = 'DROPPED') AS dropped_sessions,
    COUNT(*)                 AS total_sessions
FROM TUMBLE(data_session_events, event_time, INTERVAL '1 HOUR')
GROUP BY imsi, window_start, window_end;

CREATE MATERIALIZED VIEW subscriber_qoe_1h AS
SELECT
    sig.imsi,
    sig.window_start,
    sig.window_end,
    sig.avg_rsrp,
    sig.avg_rsrq,
    ses.avg_throughput_dl,
    ses.avg_latency,
    ses.avg_packet_loss,
    ses.dropped_sessions,
    ses.total_sessions,
    -- Signal quality score (0-1)
    CASE
        WHEN sig.avg_rsrp >= -85                          THEN 1.0
        WHEN sig.avg_rsrp >= -100                         THEN 0.75
        WHEN sig.avg_rsrp >= -110                         THEN 0.5
        WHEN sig.avg_rsrp >= -115                         THEN 0.25
        ELSE 0.0
    END AS signal_score,
    -- Throughput score (0-1, normalized to 50 Mbps as reference)
    LEAST(1.0, GREATEST(0.0, ses.avg_throughput_dl / 50.0)) AS throughput_score,
    -- Reliability score (0-1, based on drop rate)
    CASE
        WHEN ses.total_sessions > 0
        THEN 1.0 - (ses.dropped_sessions::DOUBLE PRECISION / ses.total_sessions)
        ELSE 1.0
    END AS reliability_score,
    -- Composite QoE score (weighted)
    (
        CASE WHEN sig.avg_rsrp >= -85 THEN 1.0 WHEN sig.avg_rsrp >= -100 THEN 0.75 WHEN sig.avg_rsrp >= -110 THEN 0.5 WHEN sig.avg_rsrp >= -115 THEN 0.25 ELSE 0.0 END * 0.3 +
        LEAST(1.0, GREATEST(0.0, ses.avg_throughput_dl / 50.0)) * 0.4 +
        CASE WHEN ses.total_sessions > 0 THEN 1.0 - (ses.dropped_sessions::DOUBLE PRECISION / ses.total_sessions) ELSE 1.0 END * 0.3
    ) AS qoe_score
FROM subscriber_signal_1h sig
JOIN subscriber_session_1h ses
    ON  sig.imsi         = ses.imsi
    AND sig.window_start = ses.window_start;

Step 3: Alerts and Sinks

Identify subscribers with poor QoE and route them to the CRM retention workflow.

CREATE MATERIALIZED VIEW poor_qoe_subscribers AS
SELECT
    imsi,
    window_end AS assessed_at,
    avg_rsrp,
    avg_throughput_dl,
    dropped_sessions,
    total_sessions,
    qoe_score,
    CASE
        WHEN qoe_score < 0.3 THEN 'CRITICAL'
        WHEN qoe_score < 0.5 THEN 'POOR'
        ELSE 'FAIR'
    END AS qoe_band
FROM subscriber_qoe_1h
WHERE qoe_score < 0.5;

CREATE SINK poor_qoe_sink
FROM poor_qoe_subscribers
WITH (
    connector  = 'kafka',
    topic      = 'telecom.retention.triggers',
    properties.bootstrap.server = 'kafka:9092'
)
FORMAT PLAIN ENCODE JSON;

Comparison Table

FAQ

Q: Can I tie QoE scores to geographic locations for coverage planning? Yes. Join the ue_measurement_reports source with a cell-to-location reference table to attach latitude/longitude to each measurement. Then aggregate QoE scores by grid square or administrative area using a GROUP BY on the location columns.

Q: How do I handle subscribers who roam onto partner networks? Filter for your PLMN ID in the measurement report source. Roaming records typically arrive from the visited network as TAP files on a delay; process them in a separate source with a longer watermark tolerance.

Q: Can I use QoE scores to personalize marketing offers? Yes. The poor_qoe_subscribers sink can feed directly into a CRM or marketing automation platform. Combine QoE band with subscriber plan tier and contract renewal date to prioritize retention offers.

Key Takeaways

• RisingWave computes per-IMSI QoE scores every hour from signal quality, throughput, and session reliability data — all in plain SQL.
• The composite QoE score gives CRM teams a single actionable number rather than a multi-KPI dashboard to interpret.
• Streaming delivery to retention workflow topics enables proactive outreach before subscriber frustration turns into churn.
• The PostgreSQL interface means CRM platforms, BI tools, and data science notebooks can all query subscriber QoE without custom APIs.

Get started | Slack