Blog · Board and management reporting
Calculate utility account churn from opening and closing service exports. Separate supplier switching, moves and other closures, then reconcile account losses by service area and customer segment.
Customer churn rate in utility industry reporting should separate supplier switching from moves and other account closures. Calculate total account losses from a fixed starting cohort, then show each reason against that same base. A closed service agreement is not automatically an avoidable customer loss.
Start with one business role: an electricity supplier, distribution utility or another service provider. A customer can change an electricity supplier while continuing to use the same distribution service. Decide which relationship your export represents before classifying the loss.
State regulation also differs. The US Energy Information Administration explains that some states fully regulate electricity prices while others combine unregulated generation prices with regulated transmission and distribution. Check the applicable regulator's service-area rules before treating switching as an available customer choice. Do not assume the same market arrangement for electricity, gas and water.
The worked example follows commercial electricity supply agreements during September 2026 in two fictional competitive retail service areas. Both areas permit supplier choice for the illustrated accounts. It measures agreements, not meters, premises or parent-company relationships. The churn glossary covers the general denominator; the task here is classifying utility exits honestly.
Take the opening agreement snapshot, the closing snapshot and the service termination export. Retain closed IDs even if they disappear from the current active-account file. Keep the original opening area and customer segment so a reclassification does not move the denominator after the period begins.
On a sheet named Accounts, use this layout:
| Column | Field | Content |
|---|---|---|
| A | Agreement ID | Stable identifier for one supply agreement |
| B | Service area | Opening area for the starting cohort |
| C | Customer segment | Small commercial in this example |
| D | Opening active | Numeric 1 or 0 |
| E | Closing active | Numeric 1 or 0 |
| F | Exit reason | Switch, Move or Other closure |
| G | Starting-cohort loss | Calculated flag |
Use the effective service termination date, not merely the date someone requested a closure. A transfer still pending at the closing cutoff remains active. Where the utility's records link a move to a successor agreement, preserve that link for a separate relationship view; the original agreement still closes in this account-level measure.
Enter the loss flag in G2 and fill through all populated rows:
=IF(AND(D2=1,E2=0),1,0)
Review unknown reasons before publishing. Other closure is a documented category, such as a verified business closure, not a replacement for missing evidence. Keep temporary service interruptions separate from terminated agreements.
Create the following summary in A:I, with North in row 2 and South in row 3. The figures are illustrative and are not outside benchmarks.
| Area | Starting accounts | Switch | Move | Other closure | All losses | Retained | Total churn | Switching loss rate |
|---|---|---|---|---|---|---|---|---|
| North | 600 | 12 | 18 | 6 | 36 | 564 | 6.0% | 2.0% |
| South | 400 | 4 | 12 | 4 | 20 | 380 | 5.0% | 1.0% |
| Total | 1,000 | 16 | 30 | 10 | 56 | 944 | 5.6% | 1.6% |
In Excel, use these formulas in B2 and C2, then fill down:
=SUMIFS(Accounts!$D$2:$D$50000,Accounts!$B$2:$B$50000,$A2,Accounts!$C$2:$C$50000,"Small commercial")
=COUNTIFS(Accounts!$B$2:$B$50000,$A2,Accounts!$C$2:$C$50000,"Small commercial",Accounts!$G$2:$G$50000,1,Accounts!$F$2:$F$50000,"Switch")
The first adds opening flags using the conditions described in Microsoft's SUMIFS documentation. The second counts losses satisfying every condition, as specified in Microsoft's COUNTIFS documentation. Covirage is not affiliated with Microsoft.
Copy C2 into D2 and E2, changing only the final criterion to "Move" and "Other closure" respectively. Extend all source ranges together if the export exceeds row 50000. In F2:I2, use these formulas in order:
=SUM(C2:E2)
=B2-F2
=IF(B2=0,"",F2/B2)
=IF(B2=0,"",C2/B2)
Company rates divide the summed numerators by summed starting accounts. Thus total churn is 56 / 1,000 = 5.6%, while switching loss is 16 / 1,000 = 1.6%. Show percentages to one decimal place and retain unrounded counts.
The reasons must exhaust the classified starting-cohort losses: 16 switching plus 30 moves plus 10 other closures equals 56. Also compare that sum with the G flags independently; a missing or duplicated reason should stop publication.
| Reconciliation step | Accounts |
|---|---|
| Starting cohort | 1,000 |
| Less all starting-cohort losses | 56 |
| Starting accounts retained at closing | 944 |
| Plus accounts outside opening base active at closing | 90 |
| Closing active population | 1,034 |
The 90 includes new agreements and any reactivations outside the opening base. It is not a gross acquisition count. Tie 1,034 to the closing supply snapshot using identical service and segment scope.
North's total loss rate includes moves and other closures; its switching rate answers a narrower question. Investigate documented switching reasons before assigning retention work, and use average churn rate by industry only after definitions match. The telecom churn workflow illustrates the same need to distinguish service connections from customer relationships.
Bring opening and closing agreement exports with service areas, customer segments and documented exit reasons. Covirage maps the columns; its tools compute and check classified losses and account reconciliations, and 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 self-service analytics for the supporting workflow.
A move can close a service account without ending the customer relationship. Include it in account closure churn when that is your defined measure, but classify it separately. Link successor accounts if you also report relationship retention, rather than assuming every closed account is a lost customer.
Only when the service, customer eligibility, choice rules and observation period are comparable. An area without competitive supplier choice does not provide a meaningful switching benchmark for a competitive retail market. Compare total closures separately and keep moves outside the switching numerator.
Use a unit that matches the business question and remains consistent across both snapshots. A meter can serve successive customers, while one customer can hold several service agreements. Stable agreement IDs suit account closure analysis; relationship IDs are needed to determine whether the customer left entirely.