Sign in

Blog · Data quality and reconciliation · Telecoms and connectivity

Estate currency on ten sites: the whole arithmetic on one page

The complete estate currency calculation on ten customer sites for one B2B telecoms account, small enough to check by hand: the services on the estate record, the services on the billing file, the join on the service reference, the orphan billed but not on the estate, the ghost on the estate but not billed, the site whose circuit was ceased and still on the record, the currency rate as services that agree over services on either file, the revenue on the orphans, and the assertion that estate services equal matched plus ghosts and billed services equal matched plus orphans, so a reader can reproduce every figure and then run it on their own estate and billing exports.

By CoviragePublished 17 September 20264 min read
The short answerOne account, ten sites, fourteen services on the estate record and fourteen on the billing file, joined on the service reference. Twelve match. Two are ghosts: on the estate, not billed, one a circuit ceased six months ago and never removed. Two are orphans: billed, not on the estate, one a broadband line ordered by a site manager directly. Currency rate is twelve matched over sixteen distinct services, 75 percent. The orphans bill $1,340 a month that the account team does not know it sells; the ghost circuit is a site the account plan says has a 100 Mb service that does not exist. Estate equals matched plus ghosts; billed equals matched plus orphans. Every number can be reproduced by hand.

Estate currency is a join between two files that should describe the same thing, and on ten sites it can be done by hand. This page works the join, the four disagreements, the rate, the revenue and the two assertions.

The estate record

Site Service ref Service Estate status
1 S-101 1 Gb Ethernet Live
1 S-102 SIP trunk, 30 channels Live
2 S-103 100 Mb Ethernet Live
3 S-104 100 Mb Ethernet Live
3 S-105 SIP trunk, 10 channels Live
4 S-106 100 Mb Ethernet Live
5 S-107 Broadband Live
6 S-108 100 Mb Ethernet Live
7 S-109 100 Mb Ethernet Live
7 S-110 Managed Wi-Fi Live
8 S-111 Broadband Live
9 S-112 100 Mb Ethernet Live
10 S-113 100 Mb Ethernet Live
10 S-114 SIP trunk, 10 channels Live, ordered 2 months ago

Fourteen services.

The billing file, latest month

Service ref Site Service Monthly charge
S-101 1 1 Gb Ethernet $2,200
S-102 1 SIP 30 $450
S-103 2 100 Mb $650
S-104 3 100 Mb $650
S-105 3 SIP 10 $180
S-106 4 100 Mb $650
S-107 5 Broadband $60
S-108 6 100 Mb $650
S-110 7 Managed Wi-Fi $320
S-111 8 Broadband $60
S-112 9 100 Mb $650
S-113 10 100 Mb $650
S-207 5 Broadband, second line $90
S-301 2 Mobile bundle, 20 SIMs $1,250

Fourteen charges.

The join

Outcome Refs Count
Matched S-101 to S-108, S-110 to S-113 12
Ghost: on estate, not billed S-109, S-114 2
Orphan: billed, not on estate S-207, S-301 2

The two assertions

estate = matched + ghosts = 12 + 2 = 14. Holds. billed = matched + orphans = 12 + 2 = 14. Holds.

The currency rate

Currency = matched ÷ distinct services on either file = 12 ÷ (12 + 2 + 2) = 12 ÷ 16 = 75%

Over the estate count alone it would read 12 ÷ 14 = 86 percent, and the orphans would be invisible.

The four rows

Ref Site Kind What it is Owner
S-109 7 Ghost 100 Mb circuit, ceased 6 months ago per the order history, never removed from the record; the account plan still shows site 7 on 100 Mb Account team: fix the record and the renewal quote
S-114 10 Ghost SIP trunk ordered 2 months ago, not yet billed: not provisioned, or provisioned and not billed Provisioning, then finance
S-207 5 Orphan Second broadband line ordered by the site manager on a web order; the account team has never seen it Account team: bring it onto the estate
S-301 2 Orphan Mobile bundle sold by a partner under the partner's account Partner record; note on the estate

Orphan revenue: $90 + $1,250 = $1,340 a month, $16,080 a year, billed to this account and absent from every account review.

Where it goes wrong, even at ten

Estate count as the denominator. Eighty-six percent and the orphans never seen.

Ghosts left. Site 7 renewed on a circuit that does not exist; the customer notices at the quote.

Orphans unowned. Twenty SIMs at site 2 that no one on the account team can talk to the customer about.

Checked at renewal. Two years of drift, found in the week the quote is due.

From ten to ten thousand

The same join per service, per account, every billing run, with the four rows owned. Covirage runs it on the estate export and the billing file. The telecoms sales director's questions start with whether the estate is current, and the identity versus reconciliation comparison covers what this join is and is not.

Questions people ask

Why is the currency rate over distinct services rather than the estate count?

Because the estate count hides the orphans. Fourteen on the estate, twelve matched, reads 86 percent; but two more services are billed that the estate has never heard of, and the honest denominator is every service on either file. Sixteen distinct, twelve agree: 75.

Which of the four is the account team's problem?

All four, differently. The ghost circuit is a wrong account plan and possibly a wrong renewal quote; the ghost line is a service order that never got provisioned or never got billed, which finance wants to know; the orphan broadband is revenue the team does not manage; the orphan mobile bundle was sold by a partner and is the partner's line on the record.

How often?

Monthly, with the billing run. The estate drifts by a service or two a month on a large account, and a quarterly check finds a year's drift at renewal, when it costs most.

Related reading

Forecast and pipeline · Telecoms and connectivity

Contract end dates as a dimension: the renewal calendar by account manager

How a business telecoms provider builds the renewal calendar from the contract register: contract value ending per month per account manager, the accounts within the notice window with no logged renewal activity, the co-terminus opportunities where a customer's services end months apart, and the identity that ties the calendar's total to the contracted base.

16 Sept 20262 min readData quality and reconciliation · Telecoms and connectivity

Maintaining the customer estate: the denominator in site penetration for B2B telecoms

Site penetration is only as good as the site count it divides by. This guide sets out where a business telecoms provider gets each customer's estate, how to date and source it, how to reconcile it to billing so closed sites do not linger, and the review cadence that keeps the number honest.

16 Sept 20262 min readBoard and management reporting · Telecoms and connectivity

Order-to-activation time per customer: the provisioning delays that precede churn

How a business telecoms provider measures the days from a service order to activation per customer and per site from the order and provisioning exports, the norm per service type from its own history, the customers whose recent orders took twice the norm, the correlation with contract non-renewal from the provider's own outcomes, and the list that reaches the account manager while the customer is still installing.

16 Sept 20262 min readWallet share and penetration · Telecoms and connectivity

Site penetration and product attach for B2B telecoms sales

How a business telecoms or connectivity provider measures wallet share by site rather than by logo: sites on-net against the customer's estate, product attach per site against the norm, account manager load, and the reconciliation to billed revenue.

16 Sept 20263 min read