Blog · Board and management reporting · Telecommunications
Compare telecom subscriber losses by service, region and contract type. Build fixed starting cohorts from subscriber exports, calculate comparable rates, and reconcile retained connections with the closing base.
Customer churn rate in telecom industry reporting is comparable only when the subscriber unit, loss rule and observation window match. For an internal cohort view, count starting connections that are no longer active at the cutoff and divide by the starting base. Keep service, region and prepaid or postpaid contract type visible throughout.
Decide whether the report counts billing accounts, individual connections or customer relationships. One company can have several accounts and many connections. Losing a line does not prove the company stopped buying every service.
This workflow measures September 2026 mobile connections using a fixed starting cohort. A connection is lost when it was active at the opening cutoff and is inactive at the closing cutoff. A connection that disconnects and returns before closing is retained under this particular rule; an event-based disconnect report would answer a different question.
Published definitions deserve the same scrutiny. Verizon's 2025 annual report defines wireless churn using disconnects divided by average connections in the period. That denominator differs from this starting-cohort method, so the example below is not an operator benchmark. Covirage is not affiliated with Verizon or Microsoft.
Use the general churn definition for the basic loss measure. The work here is making telecom export populations comparable.
Combine the opening and closing subscriber snapshots into a connection list that includes both retained and closed IDs. Obtain disconnect records if closed connections disappear from the current export. Deduplicate account transactions and status history before joining them to the list.
On a sheet named Subscribers, use these columns:
| Column | Field | Rule |
|---|---|---|
| A | Connection ID | Unique and stable across snapshots |
| B | Service | Mobile in this example |
| C | Region | Opening region for starting connections |
| D | Contract type | Opening prepaid or postpaid classification |
| E | Opening active flag | Numeric 1 or 0 |
| F | Closing active flag | Numeric 1 or 0 |
| G | New at closing | Not in opening base, active at closing |
| H | Starting-cohort loss | In opening base, inactive at closing |
Here, new at closing means new to the opening base, including reactivated older connections; it does not mean newly acquired customers. Use their closing region and contract classification. Keep opening classifications fixed for existing connections: an internal prepaid-to-postpaid migration should not become a customer loss. These closing balances are attributed to the opening cohort; current-plan balances need a transfer reconciliation.
In Excel, enter these flags in G2 and H2 and fill through every populated connection row:
=IF(AND(E2=0,F2=1),1,0)
=IF(AND(E2=1,F2=0),1,0)
Require explicit flags. A missing closing status is a review item until you establish whether it means disconnected or missing data. For prepaid, document the inactivity or expiry rule that makes the connection inactive; do not infer a loss from a missing invoice alone.
Create a summary sheet with this layout in A:H. Rows 2:5 contain the four segments. All connections in this illustration are mobile, so the service is held constant.
| Region | Contract | Starting base | Losses | Retained base | New at closing | Closing base | Cohort churn |
|---|---|---|---|---|---|---|---|
| Northeast | Postpaid | 5,000 | 75 | 4,925 | 100 | 5,025 | 1.50% |
| Northeast | Prepaid | 2,000 | 100 | 1,900 | 140 | 2,040 | 5.00% |
| Midwest | Postpaid | 3,000 | 30 | 2,970 | 60 | 3,030 | 1.00% |
| Midwest | Prepaid | 1,000 | 80 | 920 | 90 | 1,010 | 8.00% |
| Total | 11,000 | 285 | 10,715 | 390 | 11,105 | 2.59% |
Use these formulas in C2, D2 and F2, then fill down:
=SUMIFS(Subscribers!$E$2:$E$50000,Subscribers!$B$2:$B$50000,"Mobile",Subscribers!$C$2:$C$50000,A2,Subscribers!$D$2:$D$50000,B2)
=SUMIFS(Subscribers!$H$2:$H$50000,Subscribers!$B$2:$B$50000,"Mobile",Subscribers!$C$2:$C$50000,A2,Subscribers!$D$2:$D$50000,B2)
=SUMIFS(Subscribers!$G$2:$G$50000,Subscribers!$B$2:$B$50000,"Mobile",Subscribers!$C$2:$C$50000,A2,Subscribers!$D$2:$D$50000,B2)
The conditions use the structure described in Microsoft's SUMIFS documentation. Extend every range together if the export exceeds row 50000. In E2, G2 and H2, use:
=C2-D2
=E2+F2
=IF(C2=0,"",D2/C2)
For the total churn cell, divide summed losses by summed starting connections:
=SUM(D2:D5)/SUM(C2:C5)
That is 285 / 11,000 = 2.59%, rounded to two decimals. Do not average the displayed segment percentages.
The company bridge must close: 11,000 starting connections minus 285 losses equals 10,715 retained; adding 390 new connections still active at closing gives 11,105. Tie that closing population to the subscriber snapshot using the same service exclusions.
The new-at-closing column excludes new connections that also disconnected during September. Consequently, this bridge is not a gross additions and disconnect events schedule. Keep that distinction on the report.
The Midwest prepaid result warrants investigation, but it does not identify a cause. Check expiry rules and status completeness before comparing offer terms or customer behavior. Telecom customer churn analysis covers the diagnostic step; customer lifetime value in telecom covers the separate value question.
Bring your opening and closing subscriber exports, service classifications and effective loss rules. Covirage maps the columns; its tools compute and check segment losses and base reconciliations, while the external AI model explains the results with every figure traced to its rows. Create a free Covirage account and bring a sample of your own data.
See Covirage for telecom teams for the supporting industry context.
Choose the unit that matches the decision. Connection churn measures lost lines or services; account churn measures accounts that meet an explicit loss rule. A business account losing one mobile line has connection churn without necessarily ending its relationship with the operator.
Only after aligning the observation window, subscriber definition and effective loss rule. Prepaid inactivity and expiry policies may create a different loss trigger from postpaid termination. Show the two populations separately before calculating a combined rate from their total losses and starting bases.
A reported rate can use disconnect events and average connections rather than losses from a fixed starting cohort. Reactivations, acquisitions, product exclusions and reporting intervals can also differ. Reproduce the published methodology before treating the difference as evidence of stronger or weaker retention.