Blog · Territory, capacity and quota planning · Telecommunications
Customer segmentation in telecom groups accounts by service mix and tenure before comparing revenue and renewal outcomes. Build an account-level table, calculate the groups in a spreadsheet, and reconcile the result to billing.
Customer segmentation in telecom groups accounts by service mix and tenure so sales teams can compare revenue and renewal outcomes on a consistent basis. Start with billing and account exports, assign each account once, and calculate group totals before deciding who needs attention. A connection count and a customer count answer different questions.
A US business connectivity provider wants to know whether established connectivity-only accounts need a different renewal approach from established accounts buying several service families. Newer accounts need separate attention because onboarding and expansion differ from an established relationship's renewal decision.
That is a commercial grouping exercise. The broader method for choosing useful characteristics belongs in customer profiling and segmentation. Here the task is to make the billing and renewal comparison hold together.
Agree on the account identifier first. A parent organization might have several billing accounts and many circuits. If the relationship manager owns the parent, map billing accounts to that parent before counting customers. Preserve the original billing identifiers for reconciliation.
Set the period to July 1 through September 30, 2026. Measure tenure at the period opening, revenue within the period, and renewal outcomes for decisions due during that period. Freeze the opening population and flag any accounts added later; otherwise a changing population can masquerade as improving performance.
Keep the billing detail in a separate worksheet. Sum billed charges net of credits to each account, using the same revenue boundary as the report. Do not mix recurring service revenue with device sales in one group and exclude device sales from another.
Service families describe different purchases, such as connectivity, managed security, and hosted voice. Several circuits in the same family still count as one family. The account export supplies opening tenure and opening service mix; the contract register supplies the renewal decision.
This illustrative export has one commercial renewal decision per account during the period at most. Due and renewed are binary indicators. A renewed flag is permitted only when due is also flagged.
| Account | Opening tenure (months) | Opening service families | Net revenue (USD) | Renewal due | Renewed |
|---|---|---|---|---|---|
| T01 | 36 | 1 | 12,000 | 1 | 1 |
| T02 | 24 | 2 | 18,000 | 1 | 1 |
| T03 | 8 | 1 | 6,000 | 0 | 0 |
| T04 | 48 | 3 | 24,000 | 1 | 0 |
| T05 | 18 | 1 | 9,000 | 1 | 1 |
| T06 | 5 | 2 | 7,500 | 0 | 0 |
| Total | 76,500 | 4 | 3 |
Zero in renewed means no completed renewal in this defined window. It does not identify a cancellation reason. Keep pending outcomes visible in the underlying register rather than silently classifying them as lost business.
Place the table in A1:F7. Column G contains the group. The rule evaluates newer accounts first, then service mix:
=IF(B2<12,"Newer accounts",IF(C2>=2,"Mature multi-service","Mature connectivity"))
Fill G2 down through G7. An account with several services and short tenure belongs to the newer group because that condition has priority. The thresholds are management choices for this example, not telecom benchmarks.
This uses observed purchases, the same principle explained in behavioral segmentation. Use B2B customer segmentation from the ledger when choosing financial characteristics beyond the telecom service mix.
Put the group names in I2:I4. In J2 count accounts, K2 sum revenue, L2 sum due decisions, M2 sum renewals, and N2 calculate the renewal rate:
=COUNTIF($G$2:$G$7,I2)
=SUMIFS($D$2:$D$7,$G$2:$G$7,I2)
=SUMIFS($E$2:$E$7,$G$2:$G$7,I2)
=SUMIFS($F$2:$F$7,$G$2:$G$7,I2)
=IF(L2=0,"Not eligible",M2/L2)
These formulas work in Excel and Google Sheets. Microsoft documents SUMIFS as summing values that meet specified criteria; Google documents the same argument order. Keep sum ranges and criteria ranges aligned. Covirage is not affiliated with Microsoft or Google.
| Group | Accounts | Net revenue (USD) | Due | Renewed | Renewal rate |
|---|---|---|---|---|---|
| Mature multi-service | 2 | 42,000 | 2 | 1 | 50.0% |
| Mature connectivity | 2 | 21,000 | 2 | 2 | 100.0% |
| Newer accounts | 2 | 13,500 | 0 | 0 | Not eligible |
| Total | 6 | 76,500 | 4 | 3 | 75.0% |
Rates are rounded to one decimal place. The total is three renewals divided by four due decisions. Dividing by all six accounts would mix eligible and ineligible relationships.
Revenue reconciles as 42,000 + 21,000 + 13,500 = $76,500. Tie that amount to the billing detail for the same account population and period. Resolve credits, unmatched identifiers, and excluded charges before interpreting a difference between groups.
The account check is six assignments against six distinct accounts. Renewal due totals four and renewed totals three in both tables. On each detail row, renewed must be less than or equal to due. A revenue check returning zero is:
=SUM(K2:K4)-SUM(D2:D7)
The mature multi-service group earns more revenue but has the lower observed renewal rate. Review its underlying contracts before proposing a response: one unresolved decision can materially change a small group's result. The table does not establish that buying more services causes weaker renewal.
Use RFM analysis for B2B customers for purchase recency and frequency; keep those measures distinct from contractual renewal eligibility.
Bring a billing export, account file, and renewal register with your approved group definitions. Covirage maps the columns, and its tools compute and check the group comparisons; 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 telecoms and connectivity teams for the supporting business context.
Use accounts for commercial relationship decisions and connections or sites for service penetration. Keep those denominators separate. A business with several circuits is still one account, although its footprint may justify a different service or sales approach.
Start with an account identifier, service family, billed amount, activation date, and contract renewal information. Use a dated account export to establish tenure and billing records to establish revenue. Add other fields only when they change a specific commercial decision.
Refresh the underlying records on the review schedule your team uses, but keep the assignment rules stable during comparisons. When an account crosses a tenure threshold or adds a service, distinguish that migration from a change in the group's underlying performance.