Telecom ARPU and churn: choose the subscriber base before you divide

One month, three ARPUs

The synthetic prepaid circle below has 1,000 SIMs on the books through August. In September, 100 disconnect mid-month and 150 new SIMs activate on 22 September with a ₹99 starter pack. Every continuing customer spends exactly what they spent in August, and one in five SIMs on the books generates no revenue at all, a pattern often seen with secondary SIMs in dual-SIM handsets.

-- Synthetic prepaid circle: 1,150 SIM ids over Aug and Sep 2026 (not real operator data)
CREATE TABLE subs (sub_id INTEGER PRIMARY KEY, activated TEXT, disconnected TEXT);
CREATE TABLE usage (sub_id INTEGER, month TEXT, revenue REAL);

WITH RECURSIVE n(i) AS (SELECT 1 UNION ALL SELECT i+1 FROM n WHERE i<1150)
INSERT INTO subs
SELECT i,
       CASE WHEN i > 1000 THEN '2026-09-22' ELSE '2025-01-01' END,   -- 150 late-month adds
       CASE WHEN i <= 1000 AND i % 10 = 0 THEN '2026-09-15' END       -- 100 disconnections
FROM n;

-- existing SIMs: 20% generate no revenue; payers spend 199 to 599 a month
-- leavers pay half a month in September; new SIMs buy a 99 starter pack
INSERT INTO usage
SELECT s.sub_id, m.month,
       CASE WHEN s.sub_id > 1000 THEN 99
            WHEN (s.sub_id * 37) % 100 < 80 THEN (199 + (s.sub_id % 5) * 100)
                 * CASE WHEN m.month = '2026-09' AND s.disconnected IS NOT NULL THEN 0.5 ELSE 1 END
            ELSE 0 END
FROM subs s, (SELECT '2026-08' AS month UNION ALL SELECT '2026-09') m
WHERE s.activated <= date(m.month || '-01', '+1 month', '-1 day')
  AND (s.disconnected IS NULL OR s.disconnected >= m.month || '-01');

Compute the base three ways, month by month:

WITH m(month, m_start, m_end) AS (
  VALUES ('2026-08', '2026-08-01', '2026-08-31'), ('2026-09', '2026-09-01', '2026-09-30')),
b AS (
  SELECT m.month,
    (SELECT COUNT(*) FROM subs WHERE activated < m.m_start
        AND (disconnected IS NULL OR disconnected >= m.m_start))   AS opening,
    (SELECT COUNT(*) FROM subs WHERE activated <= m.m_end
        AND (disconnected IS NULL OR disconnected > m.m_end))      AS closing,
    (SELECT COUNT(*) FROM usage u WHERE u.month = m.month AND revenue > 0) AS paying,
    (SELECT SUM(revenue) FROM usage u WHERE u.month = m.month)            AS revenue,
    (SELECT COUNT(*) FROM subs WHERE disconnected BETWEEN m.m_start AND m.m_end) AS churned
  FROM m)
SELECT month, opening, closing, paying, revenue, churned,
       ROUND(revenue / closing, 2)                      AS arpu_closing,
       ROUND(revenue / ((opening + closing) / 2.0), 2)  AS arpu_avg_base,
       ROUND(revenue / paying, 2)                       AS arpu_paying,
       ROUND(100.0 * churned / ((opening + closing) / 2.0), 2) AS churn_pct
FROM b ORDER BY month;

Revenue rose 2.2%. Yet ARPU on the closing base fell 2.7%, on the average base it fell 0.3%, and on paying SIMs it fell 14.0%. Nobody changed their spending. The movement is entirely in who was counted.

Opening, closing or average subscribers: pick per question

The closing base is the most common choice in internal dashboards because it is one number from one snapshot. It penalises every month with late activations and flatters every month with a disconnection drive, since disconnected SIMs vanish from the denominator on the last day.

TRAI publishes quarterly ARPU for the industry in its Performance Indicator Reports. Read the report's own notes for how its revenue and subscriber figures are defined before putting your circle's number beside it. An internal ARPU built on a different base is not wrong, but it is not comparable.

Look at continuing customers before explaining anything

-- SIMs on the books for the whole of both months: the like-for-like base
SELECT u.month, COUNT(*) AS sims, SUM(u.revenue) AS revenue,
       ROUND(SUM(u.revenue) / COUNT(*), 2) AS arpu_continuing
FROM usage u JOIN subs s USING (sub_id)
WHERE s.activated < '2026-08-01' AND s.disconnected IS NULL
GROUP BY u.month;

-- where September's revenue came from
SELECT CASE WHEN s.activated >= '2026-09-01' THEN 'new SIMs'
            WHEN s.disconnected IS NOT NULL THEN 'leavers'
            ELSE 'continuing' END AS segment,
       COUNT(*) AS sims, SUM(u.revenue) AS revenue
FROM usage u JOIN subs s USING (sub_id)
WHERE u.month = '2026-09' GROUP BY segment ORDER BY revenue DESC;

The 900 SIMs on the books for both whole months have ARPU of ₹336.98 in August and ₹336.98 in September. That is the like-for-like view, and it says the base of existing customers is unchanged. Leavers earned ₹15,920 in August and ₹7,960 in their half-month of September; new SIMs added ₹14,850. Once revenue is split this way, "ARPU fell" becomes a specific, checkable sentence about acquisition and churn.

Churn has the same denominator problem

Churn in the table is 100 disconnections divided by the average base of 1,025, which is 9.76%. On the opening base it is exactly 10.00%. The gap is small here and larger in a circle that is growing or shrinking fast. The more serious churn question in prepaid is what counts as a disconnection at all:

• Operator-initiated removal. Many SIMs leave because they stop recharging and a validity or inactivity policy eventually removes them. The churn is recorded months after the customer actually stopped.
• Port-outs. Mobile number portability removes a paying customer on a known date. Report it separately; it is the churn that competitors caused.
• Silent SIMs. A SIM with no revenue for 60 days is on the books but not a customer in any commercial sense. Track it as "at risk" instead of waiting for the disconnection.

Telecom quirks that break the ratio

1. 1Recharge date versus service period. A hypothetical ₹2,999 annual pack recharged in September is not September revenue for eleven twelfths of its value. If revenue comes from the recharge ledger rather than from recognised revenue, long-validity packs inflate the recharge month. The fix is covered in pack mix by validity-normalised revenue.
2. 2Tax inside the price. Prepaid prices include GST. Decide whether your revenue is net of GST and say so; finance will compare your number with net revenue.
3. 3Dual-SIM households. One person with two SIMs is two subscribers. That is correct for network ARPU and misleading for any per-customer claim.
4. 4Mid-month activations counted as a whole month. A new SIM active for eight days should not be expected to produce a month of revenue. The average-base method partly corrects for this; pro-rating days on the books corrects it fully.

The note to the circle head

Keep it to three sentences and one table: "September revenue grew 2.2% to ₹3,26,090. ARPU on the average base was ₹318.14, against ₹319.20 in August; continuing customers were flat at ₹336.98, so the dip comes from 150 late-month activations on the ₹99 starter pack and 100 disconnections. The starter-pack cohort's second-month recharge is the number to watch in October." The circle head acts on the cohort, so name it.

Splitting a headline ratio into its population effects is a habit worth building early; it runs through the SQL and reporting modules of the Data Analyst course.