Three monthly controls — data, voice and SMS — to surface subscribers consuming far beyond their plan, detect roaming cost exposure, and separate irregular use from legitimate promotions. Built in Netezza SQL, loaded to SQL Server, reported to Marketing, Finance and Customer Experience.
WOM Chile's commercial strategy — unlimited 4G promotions, double and triple data bags, social network zero-rating — was designed for growth. But it created a monitoring gap: a subscriber with enough promotional services stacked could consume hundreds of gigabytes without triggering any automated limit, while generating real interconnection costs to other operators at fixed per-GB or per-minute rates.
The same problem appeared in voice and SMS. A postpaid line calling more than 300 distinct destinations in one cycle is not normal consumer use — it's either a fraud pattern or a business line incorrectly tariffed on a consumer plan. Without a monthly detection process, both scenarios were invisible.
The three controls in this case study were the detection layer that didn't exist yet. Each produced a cost-modelled report delivered to Marketing, Customer Experience, Finance and Interconnections.
The data heavy user control runs once per month per billing cycle (up to 7 cycles). Any subscriber consuming ≥ 50 GB in their cycle window is extracted, classified and cost-modelled against interconnection rates by operator (Claro, Movistar, Entel) and WOM's own network at CLP 150/GB. The result separates legitimate cases (unlimited plans, promotional bags) from irregular — subscribers whose normal-traffic GB exceeds their theoretical cap.
NORMAL_GB > GB_TEORICO — actual traffic exceeds theoretical cap (plan base × 1.2 + extra bags + double/triple bags).
NORMAL_GB ≤ GB_TEORICO — consumption within theoretical cap. No action required.
Subscriber has an unlimited 4G promotional bag with speed throttling or hourly restrictions. Exempt from cap comparison.
Plan is unlimited or subscriber purchased an unlimited prepaid bag. Exempt. Reported for volume visibility only.
| Operator | Rate / GB (CLP) | Note |
|---|---|---|
| Claro | 706 | Lowest interconnection cost |
| Movistar | 1,157 | 1.6× Claro |
| Entel | 7,987 | 11.3× Claro — critical to monitor |
| WOM (internal) | 150 | Own network transfer price |
Rating group 999 was a data quality signal, not a traffic type. It represented the delta between CDR header and detail — a known discrepancy that needed monitoring to ensure it didn't grow. It was listed separately in every report as a quality flag, not a classification category.
-- Stage 1: aggregate data traffic by subscriber (≥ 50 GB threshold).
-- Breaks down by operator (ID_CODIGO_OPERADOR), network type (ID_RATTYPE)
-- and rating group (free, normal, social, music, WhatsApp, video, roaming...).
-- Result loaded into staging table NZ_USUARIOS.RA.HU_50.
INSERT INTO NZ_USUARIOS.RA.HU_50
SELECT
A.NUMERO_ABONADO,
Z.CICLO_FACTURACION,
-- Total GB (KB_DOWN + KB_UP converted to GB)
ROUND((SUM((A.KB_DOWN_IX+A.KB_UP_IX)/1024)/1024), 2) GB_TOTAL,
-- Per-operator breakdown for cost modelling
NVL(SUM(CASE WHEN A.ID_CODIGO_OPERADOR IN ('73004','73009') THEN ((A.KB_DOWN_IX+A.KB_UP_IX)/1024)/1024 END),0) WOM_GB,
NVL(SUM(CASE WHEN A.ID_CODIGO_OPERADOR = '73003' THEN ((A.KB_DOWN_IX+A.KB_UP_IX)/1024)/1024 END),0) CLARO_GB,
NVL(SUM(CASE WHEN A.ID_CODIGO_OPERADOR = '73002' THEN ((A.KB_DOWN_IX+A.KB_UP_IX)/1024)/1024 END),0) MOVISTAR_GB,
NVL(SUM(CASE WHEN A.ID_CODIGO_OPERADOR IN ('73001','73010','73021') THEN ((A.KB_DOWN_IX+A.KB_UP_IX)/1024)/1024 END),0) ENTEL_GB,
-- Network type: 4G(6), 3G(1), 2G(2)
NVL(SUM(CASE WHEN A.ID_RATTYPE = '6' THEN ((A.KB_DOWN_IX+A.KB_UP_IX)/1024)/1024 END),0) GB_4G,
NVL(SUM(CASE WHEN A.ID_RATTYPE = '1' THEN ((A.KB_DOWN_IX+A.KB_UP_IX)/1024)/1024 END),0) GB_3G,
-- Rating group breakdown: normal vs free vs social vs music etc.
NVL(SUM(CASE WHEN A.ID_RATING_GROUP IN ('103','203','303') THEN ((A.KB_DOWN_IX+A.KB_UP_IX)/1024)/1024 END),0) NORMAL_GB,
NVL(SUM(CASE WHEN A.ID_RATING_GROUP IN ('101','201','301') THEN ((A.KB_DOWN_IX+A.KB_UP_IX)/1024)/1024 END),0) FREE_GB,
NVL(SUM(CASE WHEN A.ID_RATING_GROUP = '302' THEN ((A.KB_DOWN_IX+A.KB_UP_IX)/1024)/1024 END),0) RRSS_GB,
NVL(SUM(CASE WHEN A.ID_RATING_GROUP = '305' THEN ((A.KB_DOWN_IX+A.KB_UP_IX)/1024)/1024 END),0) WSP_GB,
NVL(SUM(CASE WHEN A.ID_RATING_GROUP = '999' THEN ((A.KB_DOWN_IX+A.KB_UP_IX)/1024)/1024 END),0) DIF_GB -- CDR header/detail delta — quality flag
FROM NZ_PROD.STG_TRAF.AGG_TRF_DATOS_ABONADO A
LEFT JOIN NZ_PROD.DWH_WOM.BT_CLE_PARQUE Z ON A.NUMERO_ABONADO = Z.NUMERO_ABONADO AND Z.PERIODO_PARQUE = $PeriodoParque
LEFT JOIN NZ_PROD.DWH_WOM.LK_PLN_PLAN B ON A.ID_PLAN = B.ID_PLAN
WHERE
A.ID_DWH_DIA BETWEEN $desde AND $hasta
AND Z.ESTADO_CONTRATO != 'd'
AND Z.CICLO_FACTURACION IN ($CICLO)
GROUP BY A.NUMERO_ABONADO, Z.CICLO_FACTURACION, B.DESC_CORTA_PLAN, ...
HAVING SUM(((A.KB_DOWN_IX+A.KB_UP_IX)/1024)/1024) >= 50; -- 50 GB threshold-- Stage 3: classify each subscriber and compute interconnection cost.
-- GB_TEORICO = (plan_cap * 1.2) + extra_bags + double_bags + triple_bags
-- Cost: each operator's GB × their interconnection rate in CLP.
SELECT
-- Theoretical cap calculation (only for non-unlimited plans)
CASE WHEN A.CAP != 'ilimitado'
THEN (CAST(TRANSLATE(A.CAP,',','.') AS INT) * 1.2)
+ NVL(A.BOLSA_ADICIONAL_GB,0)
+ NVL(A.BOLSA_DOBLE_GB,0)
+ NVL(A.BOLSA_TRIPLE_GB,0)
END GB_TEORICO,
-- Classification CASE: priority order matters
CASE
WHEN A.BOLSA_4G_ILI_HOUR >= 1 THEN '4G LIBRE, BAJA HORARIA'
WHEN A.BOLSA_4G_ILI_50GB >= 1 THEN '4G LIBRE, BAJA VELOCIDAD'
WHEN A.PPG_UNLIMITED >= 1 THEN 'BOLSA ILIMITADA TODA RED'
WHEN A.CAP = 'ilimitado' THEN 'PLAN LIBRE'
WHEN A.NORMAL_GB > GB_TEORICO THEN 'IRREGULAR' ← action required
WHEN A.NORMAL_GB <= GB_TEORICO THEN 'CORRECTO'
ELSE 'OTRO'
END CLAS,
-- Consumption range for executive summary
CASE
WHEN A.GB_TOTAL BETWEEN 50 AND 100.999 THEN 'MAYOR A 50 GB'
WHEN A.GB_TOTAL BETWEEN 101 AND 250.999 THEN 'MAYOR A 100 GB'
WHEN A.GB_TOTAL BETWEEN 251 AND 500.999 THEN 'MAYOR A 250 GB'
WHEN A.GB_TOTAL > 1000 THEN 'MAYOR A 1000 GB'
END RANGO_CONSUMO,
-- Real interconnection cost per operator (CLP)
(A.CLARO_GB * 706) COSTO_CLARO,
(A.MOVISTAR_GB * 1157) COSTO_MOVISTAR,
(A.ENTEL_GB * 7987) COSTO_ENTEL, -- highest rate — 11× Claro
(A.WOM_GB * 150) COSTO_WOM,
(COSTO_CLARO + COSTO_MOVISTAR + COSTO_ENTEL) COSTO_RN
FROM NZ_USUARIOS.RA.HU_50_PASO A
LEFT JOIN NZ_USUARIOS.RA.FRD_STOCK_DEMOS Z ON A.NUMERO_ABONADO = Z.MOVIL
ORDER BY A.NORMAL_GB DESC;The voice control targets postpaid subscribers who made calls to more than 300 distinct destination numbers in one billing cycle. This threshold flags two scenarios: automated dialers (fraud risk) and business-segment subscribers incorrectly billed on consumer voice plans. The query runs against RTX billing tables — the rated, invoice-ready view of traffic — not raw CDRs, so all figures carry real billing amounts.
A second query then adds the mediated traffic layer to compute off-net and roaming-national minutes per competitor operator, enabling a full cost vs plan value margin analysis for each flagged line.
| Traffic type | Rate (CLP/min) | Context |
|---|---|---|
| Off-net (all operators) | 8.3 | Average interconnection outpayment |
| Roaming Nacional · Claro | 5.1 | Lowest RN rate |
| Roaming Nacional · Movistar | 7.8 | Mid-range RN rate |
| Roaming Nacional · Entel | 21 | Highest RN rate — 4× Claro |
XFILE_IND ≠ 'V' filters out international roaming records from the RTX table — otherwise roaming calls from subscribers abroad would inflate destination counts and generate false positives. SNCODE = 3 isolates voice telephony only, excluding SMS, data bearer and supplementary services.
-- Voice control: detect postpaid lines with >300 distinct destinations per cycle.
-- Uses RTX billing table (UDR_LT) = rated, invoice-ready traffic.
-- SNCODE=3: voice telephony only. XFILE_IND != 'V': excludes intl roaming.
-- UNION covers all billing cycles in the period (each has different date ranges).
TRUNCATE TABLE NZ_USUARIOS.RA.RTX_300_DESTINOS;
INSERT INTO NZ_USUARIOS.RA.RTX_300_DESTINOS
SELECT B.RUT, B.NUMERO_ABONADO, B.NOM_APE_CRP, D.DESC_CORTA_PLAN,
Z.Q_DESTINOS, Z.MIN_RTX, Z.MONTO_NETO, Z.DESDE, Z.HASTA
FROM (
SELECT A.CUST_INFO_BILL_CYCLE, A.CUST_INFO_CONTRACT_ID,
SUM(CAST(A.ROUNDED_VOLUME AS FLOAT)/60) MIN_RTX,
SUM(A.RATED_FLAT_AMOUNT) MONTO_NETO,
COUNT(DISTINCT A.O_P_NUMBER_ADDRESS) Q_DESTINOS,
'20181126' DESDE, '20181225' HASTA
FROM NZ_PROD.STG_RTX.UDR_LT A
WHERE
TO_CHAR(A.INITIAL_START_TIME_TIMESTAMP ...) BETWEEN 20181126 AND 20181225
AND A.CUST_INFO_BILL_CYCLE = '03'
AND A.TARIFF_INFO_SNCODE = 3 -- voice only
AND A.XFILE_IND != 'V' -- exclude intl roaming
GROUP BY A.CUST_INFO_BILL_CYCLE, A.CUST_INFO_CONTRACT_ID, A.TARIFF_INFO_TMCODE
HAVING COUNT(DISTINCT A.O_P_NUMBER_ADDRESS) > 300 -- threshold
UNION -- ... same query for cycles 07, 01/02, 06 with their date ranges
) Z
LEFT JOIN NZ_PROD.DWH_WOM.BT_CLE_PARQUE B ON Z.CUST_INFO_CONTRACT_ID = B.NUMERO_CONTRATO
LEFT JOIN NZ_PROD.DWH_WOM.LK_PLN_PLAN D ON B.ID_PLAN = D.ID_PLAN
ORDER BY Z.Q_DESTINOS DESC;
-- Stage 2: add mediated traffic for per-operator cost breakdown
SELECT A.*,
(MIN_OFFNET*8.3) MONTO_OFFNET,
(MIN_RN_CLARO*5.1) MONTO_RN_CLARO,
(MIN_RN_MOVISTAR*7.8) MONTO_RN_MOVISTAR,
(MIN_RN_ENTEL*21) MONTO_RN_ENTEL,
(MONTO_OFFNET + MONTO_RN_CLARO + MONTO_RN_MOVISTAR + MONTO_RN_ENTEL) TOTAL_COST,
C.VALOR_PLAN
FROM NZ_USUARIOS.RA.RTX_300_DESTINOS A
LEFT JOIN NZ_PROD.STG_TRAF.AGG_TRF_VOZ_MED_ABONADO B
ON A.NUMERO_ABONADO = B.NUMERO_ABONADO
AND B.ID_DWH_DIA BETWEEN A.DESDE AND A.HASTA
AND B.STATUS_CODE = 'H900';The SMS control mirrors the voice logic but runs against the SMSC traffic table instead of RTX. Any mobile line sending SMS to more than 300 distinct destination numbers in January 2019 is extracted. Two additional flags make the detection more precise: a HOOK flag that marks number ranges known to be used in SIM-swap fraud patterns, and a TIPO flag that identifies lines declared as internal test lines (from the fraud stock table).
Off-net SMS (SINK_NAME contains 'OUT') vs on-net (SINK_NAME = 'mt default') vs other are counted separately to assess interconnection exposure.
The HOOK flag marks SIM ranges associated with fraud patterns. If a line's MSISDN falls within those ranges and it's sending SMS to 300+ destinations, the interpretation is different from a regular subscriber — it goes directly to the fraud team, not to the commercial teams.
SRC_NAME = 'Nextel' filters to the WOM network origin (the Nextel brand that became WOM Chile). Without this filter, the query would pick up transit traffic from other operators passing through the SMSC, creating false positives for subscriber detection.
-- SMS control: subscribers sending to >300 distinct destinations in Jan 2019.
-- HOOK: marks MSISDN ranges associated with fraud / SIM-swap patterns.
-- TIPO: identifies internal test lines from the fraud stock table.
-- Onnet vs offnet vs other counted separately for interconnection exposure.
SELECT
SUBSTR(A.ENTRY_DATE,1,6) PERIODO,
A.ORIG, B.RUT, B.NOM_APE_CRP, B.NUMERO_CONTRATO,
C.DESC_CORTA_PLAN, C.DESC_PLAN,
-- Fraud indicator: known high-risk MSISDN ranges
CASE WHEN A.NUMERO_ABONADO BETWEEN '56945950000' AND '56945950999'
OR A.NUMERO_ABONADO BETWEEN '56964590000' AND '56964590999'
OR A.NUMERO_ABONADO BETWEEN '56931402001' AND '56931402501'
OR A.NUMERO_ABONADO BETWEEN '56935500000' AND '56935509999'
THEN 'SI' ELSE 'NO'
END HOOK,
-- Test line identifier (fraud team's internal stock)
CASE WHEN A.ORIG = Z.MOVIL THEN 'PRUEBAS' ELSE 'NO' END TIPO,
COUNT(DISTINCT A.DEST1) Q_DESTINO,
COUNT(1) Q_SMS,
COUNT(CASE WHEN UPPER(A.SINK_NAME) LIKE '%OUT%' THEN 1 END) OFFNET,
COUNT(CASE WHEN A.SINK_NAME = 'mt default' THEN 1 END) ONNET,
COUNT(CASE WHEN (UPPER(A.SINK_NAME) NOT LIKE '%OUT%'
AND A.SINK_NAME != 'mt default') THEN 1 END) OTROS
FROM NZ_PROD.STG_TRAF.BT_TRAFICO_SMSC_SYMSOFT A
LEFT JOIN NZ_PROD.DWH_WOM.BT_CLE_PARQUE B ON A.ORIG = B.NUMERO_ABONADO AND B.PERIODO_PARQUE = 201901
LEFT JOIN NZ_PROD.DWH_WOM.LK_PLN_PLAN C ON B.ID_PLAN = C.ID_PLAN
LEFT JOIN NZ_USUARIOS.RA.FRD_STOCK_DEMOS Z ON A.ORIG = Z.MOVIL
WHERE
A.ENTRY_DATE BETWEEN 20190101 AND 20190131
AND LENGTH(A.ORIG) = 11 -- 11-digit MSISDN only
AND LENGTH(A.DEST1) = 11
AND A.SRC_NAME = 'Nextel' -- WOM network origin only
AND A.STATE IN ('Delivered direct', 'Delivered')
AND A.TYPE = 'M' -- mobile-originated
GROUP BY PERIODO, A.ORIG, B.RUT, B.NOM_APE_CRP, ...
HAVING Q_DESTINO > 300
ORDER BY Q_SMS DESC;NORMAL_GB (rating groups 103/203/303) against the theoretical cap is the only fair comparison.
BT_CLE_SALDO_SERVICIO_DIA required manually setting the date to "yesterday" before each run. A parameterised $fecha_pcrf = CURRENT_DATE - 1 would have eliminated this operational risk with zero cost.
NZ_USUARIOS.RA would have made rate updates a data change, not a code change.
"'Unlimited' is a commercial promise. The network still has a cost per GB — and someone has to know who's consuming 800 of them."
Revenue assurance, anomaly detection, cost modelling — this work sits at the intersection of data engineering and business intelligence. If that's the profile you're hiring for, let's talk.
Contact Javier