Telecom customer churn analysis: lines and accounts

Telecom customer churn analysis needs separate measures for disconnected lines, lost accounts and recurring revenue. Use the same monthly boundaries, but match each loss to its own opening denominator. One canceled line can reduce an account without ending the customer relationship.

Define the unit before counting losses

For this review, a line is a distinct service identifier and an account is the customer relationship that owns those lines. An account is lost only when its closing qualifying line count is zero. Keep suspended services in a separate status until the business has decided whether they count as active or disconnected.

Opening-line churn = Opening lines disconnected during the month / Opening active lines

Opening-account churn = Opening accounts with no qualifying lines at month end / Opening active accounts

Recurring revenue lost = Opening monthly recurring charges attached to disconnected opening lines

These are operational definitions for the example, rather than claims about every carrier's reporting policy. For the broader denominator choices, see telecom churn rate. Use an unchanged definition when comparing periods, and label any policy change beside the figures.

Start with the line and account exports

Take opening and closing active-line files plus the month's additions and disconnections. Keep line ID, account ID, opening status, activation date, disconnection date and monthly recurring charge. Use the service effective date consistently; a request received in one month and completed in another must not appear as two cancellations.

Create a prepared line sheet with these columns:

Use numeric flags only after checking missing dates and unmatched account IDs. An empty status is an exception to resolve, rather than evidence that a service was inactive. Keep migration records alongside the line mapping so an internal product change does not become a customer loss.

Work the month at account level

This illustrative US provider has the following opening relationships. The table is a small review slice, not an industry benchmark. All charges are monthly recurring amounts in USD; no price changes, credits or migrations occur in this example.

Only Beacon is a lost account. Alder, Dover and Elm lost lines but retain a relationship; Cedar added a line within an existing account.

The results follow directly from the table, with percentages rounded to one decimal place:

The revenue percentage differs because canceled lines carried different charges. It is a loss measure on the opening charge base, rather than a statement that revenue recognized in the month fell by that amount.

Rebuild the account view in a spreadsheet

Put an account ID in Accounts!A2. On a prepared Lines sheet with the layout above, these formulas count opening lines and disconnected opening lines:

=COUNTIFS(Lines!B:B,A2,Lines!C:C,1)
=COUNTIFS(Lines!B:B,A2,Lines!C:C,1,Lines!D:D,1)

Both conditions must hold on the same row, as the Google Sheets COUNTIFS documentation explains. To obtain opening and closing account line counts, put the first formula in B2 and the following in C2; flag the lost account in D2:

=COUNTIFS(Lines!B:B,A2,Lines!F:F,1)
=IF(AND(B2>0,C2=0),1,0)

Calculate the lost opening charge for that account with:

=SUMIFS(Lines!G:G,Lines!B:B,A2,Lines!C:C,1,Lines!D:D,1)

The SUMIFS documentation describes summing a range against multiple criteria. Covirage is not affiliated with Google. For a neighboring spreadsheet workflow, use customer churn analysis in Excel.

Prove the movements before assigning actions

The line check is 11 opening − 4 disconnected + 1 added = 8 closing. The charge check is 660 − 260 + 50 = 450. Opening accounts also reconcile: 5 − 1 lost = 4 closing, with no new accounts in this slice.

No line both starts and ends during this example month. In a full export, exclude those lines from opening-line churn, but include their additions and disconnections in the closing reconciliation. Track new-line disconnections separately so the full line bridge is opening lines minus opening-line disconnections plus additions minus new-line disconnections.

Run those checks against the actual closing exports, rather than merely calculating an expected closing balance. A difference needs an identified movement such as a migration, reactivation or charge change. Do not bury it inside an unexplained adjustment.

Account managers can review partial reductions with the remaining relationship in view. Complete losses need a separate reason review. Add customer concentration when one account's reduction carries unusual commercial weight. For a business without explicit cancellations, retail customer churn analysis uses a different observation rule.

Where it goes wrong

• Counting lines as customers. A multi-line account can produce several cancellations and only one relationship loss.
• Subtracting additions from cancellations. Net movement answers whether the base grew; gross losses answer what left.
• Joining by display name. Account renaming can create a false loss and replacement. Use a stable identifier.
• Mixing charges and invoices. Usage, equipment and partial-month billing do not belong in a recurring-charge snapshot.
• Ignoring migrations. A replacement service may preserve the relationship even when the old service identifier closes.

Analyze your telecom export in Covirage

Bring a sample of your own line, account and recurring-charge exports to Covirage. Covirage maps the columns, and its tools compute and check the movements; the external AI model explains the results with every figure traced to its rows. Create a free Covirage account and bring your opening and closing files.

See Covirage for telecom teams for the supporting industry context.