Telecom revenue analysis: subscribers, usage and rates

Telecom revenue analysis explains service revenue changes through billable subscribers, usage, realized rates and plan mix. Start with matched billing periods and separate recurring plan revenue from usage revenue, then reconcile their movements to the service revenue ledger.

Define the service revenue you are explaining

Choose one service population and reporting window before calculating: for example, a US provider's monthly business connectivity service revenue in USD. Keep equipment sales and unrelated services outside that comparison. State how discounts, credits and revenue recognition adjustments enter the total.

Request billing line ID, subscriber or service ID, account ID, plan, service dates, charge type, billable quantity, net revenue and reporting period. Include a recognized-revenue field or reconciliation schedule when invoice timing differs from the ledger. Invoice totals alone cannot prove recognized revenue.

Build a consistent plan mapping across periods. A renamed plan is not automatically a new product, and a new service ID does not necessarily mean a new customer. Customer revenue analysis addresses the account view; the service comparison here needs the underlying billable units.

Use subscriber-month equivalents for the recurring charge denominator. A full period of service contributes one equivalent; partial periods contribute the applicable fraction under your billing convention. This avoids dividing a full month's revenue by a single closing-day subscriber count.

Summarize the billing export

In a worksheet named Billing, put reporting period in A, service ID in B, plan in C, subscriber-month equivalent in D, recurring net revenue in E, usage quantity in F and usage net revenue in G. This layout uses one summarized row per service, plan and period: do not repeat the subscriber-month equivalent on every charge line.

On a summary worksheet, J2 contains the plan and K1 the reporting period. Recurring revenue is:

=SUMIFS(Billing!$E:$E,Billing!$C:$C,$J2,Billing!$A:$A,K$1)

Change the first range to Billing!$D:$D for subscriber-month equivalents. Microsoft's SUMIFS documentation explains how the function sums values meeting multiple criteria. Divide recurring revenue by the corresponding equivalents for the realized monthly plan rate.

Build a separate usage table for each consistent unit, such as chargeable minutes. Do not combine minutes, messages and data units into one quantity. For unlimited plans with no usage-linked charge, usage may explain customer behavior but does not directly produce a billed usage movement.

Covirage is not affiliated with Microsoft.

Work a subscriber, mix and rate example

The following illustrative provider compares two monthly periods. All recurring revenue comes from the displayed subscriber-month equivalents and realized rates; the rate already reflects recurring discounts.

The prior blended recurring rate is $46,000 / 1,000 = $46.00. Calculate it from the rows with:

=SUMPRODUCT(B2:B3,C2:C3)/SUM(B2:B3)

Microsoft documents SUMPRODUCT as summing products of corresponding ranges. Here it weights each plan rate by its billable exposure, rather than taking a simple average of plan prices.

Use the following ordered bridge. The order allocates the interaction between a changing base, changing mix and changing rates, so keep it consistent between reports.

The mix movement compares actual current plan quantities at old rates with current total exposure at the old blended rate. It captures the shift toward Premium without calling that shift a price increase.

For the table's B:E columns and a prior blended rate stored in H2, the calculations are:

Subscriber effect =(SUM(D2:D3)-SUM(B2:B3))*$H$2
Plan mix =SUMPRODUCT(D2:D3,C2:C3)-SUM(D2:D3)*$H$2
Rate effect =SUMPRODUCT(D2:D3,E2:E3)-SUMPRODUCT(D2:D3,C2:C3)

Add usage and prove the total

These same services also generate the following separately billed usage. The quantities are aggregate chargeable units, so their movement includes both changes in the customer base and consumption per service.

Usage quantity contributes (3,000 - 2,000) × $0.10 = $100. The usage rate contributes 3,000 × ($0.08 - $0.10) = -$60. Together they explain the $40 usage revenue increase.

Prior service revenue is $46,000 + $200 = $46,200. Current service revenue is $51,600 + $240 = $51,840. The complete check is $46,200 + $2,300 + $2,700 + $600 + $100 - $60 = $51,840, with zero residual.

Growth is $5,640 / $46,200 = 12.2%, rounded to one decimal place. Current recurring revenue per equivalent is $51,600 / 1,050 = $49.14, rounded to cents. Its rise from $46.00 reflects both mix and realized rates; it is not a pure price measure.

Where it goes wrong

• Closing subscribers as the denominator. They miss activations and disconnections within the month. Use matched billable exposure.
• Repeated subscriber counts. Multiple billing lines for one service inflate the denominator unless exposure is counted once.
• Credits counted as rate alone. Separate unusual or prior-period credits before interpreting a recurring price change. Investigate unexplained omissions through revenue leakage in telecom.
• Usage assumed to mean revenue. Included usage on a fixed-price plan does not create an incremental usage charge.
• Base growth called retention. A growing total can conceal disconnections. Use a cohort retention table for that separate question.

Finance owns the ledger reconciliation, billing operations confirms units and credits, and commercial teams explain plan migrations. Keep their explanations attached to the rows that produced each movement.

Analyze your own telecom revenue in Covirage

Bring a sample of your own billing export with service periods, plans, recurring charges and usage. Covirage maps the columns, its tools compute and check the comparisons, and the external AI model explains the result with every figure traced to its rows. Create a free Covirage account and bring your file after setting up your business.

See revenue driver analysis for the supporting approach to explaining movements that reconcile.