Entel · Strategic Collections Analytics

01 · Context

Stop chasing every debt. Chase the debt that impacts the bottom line.

In telecommunications, 90-day arrears (mora 90 días) is a critical health metric. However, looking at a global percentage across millions of clients obscures actionable reality. A 9% arrears rate in a region with 1,000 clients is a local operational issue. A 4% arrears rate in a region with 1,000,000 clients is a massive financial risk.

The goal of this analysis was to move management away from reading flat Excel-style tables and towards a visual risk-matrix that allows the Collections team (Call Center) to prioritize resources based on capital exposure, segmenting by Wireline (Alámbrico) and Wireless (Inalámbrico) services.

The Old Way

Looking at 16x12 data grids of percentages. Management reacts to the "highest red number" regardless of underlying client volume.

The New Strategy

Scatter plots mapping Arrears Rate vs. Client Volume. Collections team instantly identifies the "High Risk / High Volume" quadrant.

02 · The Analytics Pipeline

Joining billing facts with collection states.

01 · INGEST

Billing & Collections DB

Extracting monthly snapshots of invoiced accounts and payment statuses per service ID.

02 · TRANSFORM

Service & Region Mapping

Categorizing services (Wireline vs Wireless) and joining with geospatial dimension tables.

03 · AGGREGATE

Risk Calculation

Computing 90-day arrears (Mora) and total client base (Q) partitioned by YearMonth and Region.

-- Example logic for aggregating arrears by region and service type
WITH ClientStatus AS (
    SELECT 
        r.region_name,
        s.service_type, -- 'Alámbrico' or 'Inalámbrico'
        b.period_yyyymm,
        COUNT(DISTINCT b.client_id) AS q_clientes,
        SUM(CASE WHEN c.days_overdue >= 90 THEN 1 ELSE 0 END) AS q_mora_90
    FROM fact_billing b
    LEFT JOIN fact_collections c ON b.invoice_id = c.invoice_id
    JOIN dim_region r ON b.region_id = r.region_id
    JOIN dim_service s ON b.service_id = s.service_id
    GROUP BY 1, 2, 3
)
SELECT 
    region_name, service_type, period_yyyymm,
    q_clientes,
    ROUND((q_mora_90 * 100.0) / q_clientes, 2) AS tasa_mora_pct
FROM ClientStatus;

03 · Insights & Risk Matrix

Translating data into call center priorities.

Wireline Risk Matrix: Arrears Rate vs. Client Volume

Snapshot: Regional distribution

The quadrant defines the action. Atacama has a high rate (~8.9%) but very low volume. Valparaíso has a lower rate (~4.3%) but massive volume. Collections focus should prioritize Valparaíso to recover the most capital, not Atacama just because its percentage is red.

90-Day Arrears Heatmap (Wireline) & The 0.00% Catch

Monthly trend 202208 - 202302

Data Quality Catch: Looking at the raw data for Q1 2023, the arrears rate suddenly drops to exactly 0.00% across all regions. This isn't a miraculous recovery; it's an unclosed financial month in the database. Spotting this anomaly before presenting to the board prevents loss of trust in the entire report.

04 · What I Owned

Strategic choices and data quality assurance.

Strategic Decisions

Splitting Wireline vs. Wireless. Customer payment behavior varies drastically between a fixed home fiber connection and a mobile plan. Aggregating them blurs the reality. Segmenting them allows for tailored collection scripts.
Killing the "Table of Percentages". A 16x12 grid of numbers is unreadable for a C-Level executive. Introducing the Scatter Plot (Risk Matrix) immediately answers the question: "Who do we call first today?"

Data Quality Defense

The "Zero Percent" Trap. Dashboards that blindly ingest data will plot a 0% as a massive success. I implemented a hard rule: if standard deviation drops to absolute zero across all regions, flag the month as "Data Not Closed" rather than rendering a misleading chart. Data engineering isn't just pipeline creation; it's defense.

"Effective collections isn't about chasing every debt; it's about chasing the right debt based on capital exposure." — Business takeaway from this analysis

This case demonstrates the ability to translate raw financial data into strategic, actionable matrices for C-Level management, while maintaining strict data quality defenses.

Contact Javier