Telecom Churn Prediction & Network Quality Analytics¶

Use Case Overview¶

Business Context¶

Regional wireless carriers face relentless competitive pressure from national operators and MVNOs. Subscriber churn averages 1.5-2.5% monthly, making retention the single largest lever for revenue protection. Meanwhile, 5G rollout demands precise capacity planning to maximise capital efficiency.

This use case deploys Microsoft Fabric to unify call detail records (CDRs), network performance telemetry, subscriber profiles, and billing data into a single analytics platform. Five interconnected workstreams deliver measurable business value:

1. Churn Prediction -- propensity scoring and retention offer optimisation
2. Network Quality Analytics -- dropped calls, throughput, latency by cell/sector
3. Revenue Assurance -- rating accuracy, unbilled usage, fraud detection
4. Customer Experience Management -- NPS drivers, journey analytics
5. 5G Capacity Planning -- traffic forecasting, small cell placement

Architecture¶

High-Level Data Flow¶

+-------------------+     +------------------+     +--------------------+
| CDR / xDR Feeds   |---->| Eventstream      |---->| Eventhouse (KQL)   |
| (voice, data, SMS)|     | (real-time ingest)|     | (real-time network |
+-------------------+     +------------------+     |  KPIs, 15-min agg) |
                                                    +--------------------+
+-------------------+     +------------------+            |
| Subscriber CRM    |---->| Lakehouse Bronze |<-----------+
| (batch daily)     |     |                  |
+-------------------+     +--------+---------+
                                   |
+-------------------+     +--------v---------+
| Network Mgmt      |---->| Lakehouse Silver |
| (PM counters,     |     | (enriched CDRs,  |
|  alarms, config)  |     |  validated subs)  |
+-------------------+     +--------+---------+
                                   |
                          +--------v---------+
                          | Lakehouse Gold   |
                          | - gold_churn_scores
                          | - gold_network_kpis
                          | - gold_revenue_assurance
                          | - gold_cust_experience
                          | - gold_capacity_plan
                          +------------------+

Medallion Tables¶

Workstream 1: Churn Prediction¶

Problem Statement¶

Monthly churn of 2.1% costs the carrier ~\(100M annually in lost revenue and subscriber acquisition costs (\)350-450 per gross add). Proactive retention campaigns targeting high-propensity churners can reduce churn by 15-25% when paired with optimised offers.

Feature Engineering¶

Features are computed in the Gold layer from Silver aggregates:

Propensity Scoring¶

The churn model uses a gradient-boosted tree (LightGBM) trained on 12 months of historical churn labels. The model outputs a probability score (0.0 - 1.0) per subscriber, refreshed daily.

Scoring tiers: - High risk (>0.7): Immediate retention intervention -- dedicated agent call - Medium risk (0.4-0.7): Targeted offer via SMS/app push (e.g., 20% discount, data bonus) - Low risk (<0.4): Standard engagement programmes

Retention Offer Optimisation¶

A contextual bandit model selects the optimal retention offer from a portfolio:

KQL Real-Time Churn Signals¶

// Detect subscribers with sudden usage drop (potential pre-churn signal)
CDR_Stream
| where EventTime > ago(7d)
| summarize DailyDataGB = sum(BytesDown + BytesUp) / 1073741824.0 by SubscriberId, bin(EventTime, 1d)
| summarize AvgRecent = avg(DailyDataGB) by SubscriberId
| join kind=inner (
    CDR_Stream
    | where EventTime between(ago(37d) .. ago(7d))
    | summarize DailyDataGB = sum(BytesDown + BytesUp) / 1073741824.0 by SubscriberId, bin(EventTime, 1d)
    | summarize AvgBaseline = avg(DailyDataGB) by SubscriberId
) on SubscriberId
| where AvgRecent < AvgBaseline * 0.5
| project SubscriberId, AvgBaseline, AvgRecent, DropPct = round((1 - AvgRecent/AvgBaseline) * 100, 1)
| order by DropPct desc

Workstream 2: Network Quality Analytics¶

Key Performance Indicators¶

Cell Site Performance Analysis¶

Network PM counters arrive every 15 minutes from the RAN. The Silver layer aggregates these into hourly and daily metrics per cell/sector, while the Eventhouse provides sub-minute visibility for NOC operations.

// Top 20 worst-performing cells by dropped call rate (last 24h)
NetworkPM
| where Timestamp > ago(24h)
| summarize
    TotalAttempts = sum(CallAttempts),
    DroppedCalls = sum(DroppedCalls),
    AvgDLThroughputMbps = avg(DLThroughputMbps),
    AvgULThroughputMbps = avg(ULThroughputMbps)
    by CellId, Sector
| extend DCR = round(DroppedCalls * 100.0 / TotalAttempts, 2)
| where TotalAttempts > 100
| top 20 by DCR desc
| project CellId, Sector, DCR, TotalAttempts, DroppedCalls, AvgDLThroughputMbps

Sector-Level Heat Maps¶

Gold-layer aggregates feed Power BI Direct Lake reports with geospatial heat maps. NOC analysts can drill from regional view down to individual cell-sector performance, overlaid with subscriber density and terrain data.

Workstream 3: Revenue Assurance¶

Rating Accuracy¶

CDRs are re-rated in the Silver layer against the subscriber's plan terms. Discrepancies between the switch-rated amount and the re-rated amount flag potential revenue leakage:

Unbilled Usage Detection¶

-- Identify CDRs with no matching billing record (potential revenue leakage)
SELECT
    c.cdr_id,
    c.subscriber_id,
    c.call_type,
    c.duration_sec,
    c.start_dt,
    c.rated_amount
FROM silver_telecom_usage c
LEFT JOIN billing_rated_events b
    ON c.cdr_id = b.cdr_id
WHERE b.cdr_id IS NULL
    AND c.start_dt >= DATEADD(day, -7, GETDATE())
    AND c.duration_sec > 0
ORDER BY c.rated_amount DESC

Fraud Detection Patterns¶

Workstream 4: Customer Experience Management¶

NPS Driver Analysis¶

Net Promoter Score surveys are correlated with operational data to identify causal drivers of satisfaction and detraction:

Journey Analytics¶

Customer journey stages are tracked through interaction events:

1. Awareness -- Marketing campaign exposure and response
2. Acquisition -- Store visit, online signup, activation
3. Onboarding -- First 30 days usage patterns, setup calls
4. Usage -- Steady-state consumption, plan utilisation
5. Service -- Support interactions, complaints, escalations
6. Renewal -- Contract renewal decision, upgrade, churn

Each stage has associated health metrics that feed the churn model.

Workstream 5: 5G Capacity Planning¶

Traffic Forecasting¶

Time-series forecasting (Prophet/ARIMA) on per-cell traffic volumes predicts when cells will reach capacity thresholds. The model accounts for:

• Seasonal patterns (time of day, day of week, holidays)
• Growth trends (subscriber additions, data usage growth)
• Event-driven spikes (concerts, sports, emergencies)
• Technology migration (4G to 5G traffic shift)

Small Cell Placement Optimisation¶

Gold-layer analysis combines:

• Traffic density maps (from CDR geolocation)
• Coverage gap analysis (from drive test data)
• Building footprints and zoning data
• Fibre availability for backhaul
• Revenue density (ARPU by location)

Output is a prioritised list of candidate small cell locations with projected ROI.

Capacity KPIs¶

CPNI Compliance¶

Regulatory Framework¶

Customer Proprietary Network Information (CPNI) is protected under 47 CFR 64.2001-2009 (FCC regulations). Carriers must safeguard subscriber data including:

• Call detail records (numbers called, call duration, time of day)
• Service configuration (plan type, features subscribed)
• Usage data (data consumption, roaming patterns)

Fabric Implementation¶

Row-Level Security for CPNI¶

-- RLS policy: restrict CDR access to subscriber's own account or authorised roles
CREATE FUNCTION dbo.fn_cpni_filter(@subscriber_id VARCHAR(50))
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
    SELECT 1 AS result
    WHERE
        IS_MEMBER('CPNIAuthorisedAnalysts') = 1
        OR @subscriber_id = SESSION_CONTEXT(N'subscriber_id')

GDPR Considerations (EU Roaming)¶

For subscribers roaming on EU partner networks, additional GDPR requirements apply:

Cost Analysis¶

Fabric Consumption Breakdown¶

ROI Model¶

Additional value from network quality improvements (reduced truck rolls, optimised CapEx), revenue assurance (plugging leakage), and improved NPS (reduced acquisition cost) is not included in this conservative estimate.

Power BI Reports¶

Dashboard Suite¶

1. Churn Command Centre -- Real-time churn risk heatmap, retention campaign tracker, offer conversion rates, subscriber-level drill-through
2. Network Operations -- Cell site health map, DCR/CSSR trends, PRB utilisation gauges, alarm correlation timeline
3. Revenue Assurance -- Rating discrepancy waterfall, unbilled usage tracker, fraud alert queue with investigation workflow
4. Customer Experience -- NPS trend by segment, journey stage health, complaint resolution SLA tracking
5. Capacity Planning -- Traffic forecast charts, cell congestion predictions, CapEx planning scenarios

All reports use Direct Lake mode for sub-second query performance on the Gold layer.

References¶