Blog · Territory, capacity and quota planning · Banking
Commercial banking customer segmentation separates business size and sector from product holdings and relationship behavior. Build the groups from customer and product exports, compare quarterly income, and reconcile every relationship to the source files.
Commercial banking customer segmentation groups business relationships by size and sector, then compares their product holdings and relationship income. Keep those business attributes separate from relationship behavior so a change in product usage does not silently change the peer group. The starting point is a customer export with stable relationship IDs, supported by product holdings and income files for the same reporting period.
Start with the question a relationship manager can act on: which comparable businesses hold different product families, and where does recorded relationship income differ? The broader customer segmentation in banking guide covers the banking framework; this method focuses on commercial business relationships.
Choose one approved sector field and one business-size measure. Annual company revenue works when its observation date and reporting basis are known. Employee count can work instead, but do not alternate between the measures inside a single band. Record missing size information as unknown rather than treating it as a small business.
For this illustration, a US bank defines small relationships as businesses with annual revenue below $20,000,000 and midsize relationships as those at or above that threshold. These are internal analytical bands, not an industry standard. Manufacturing and services are the supplied sector labels. Freeze those definitions before comparing income.
The general method for choosing groups belongs in B2B customer segmentation. Here, the extra work is aligning banking relationships, product families and income without multiplying the same customer across accounts.
Bring the customer master, the active product holdings snapshot at September 30, 2026, and the relationship income export for July through September 2026. Map legal entities and accounts to the approved relationship ID first. Aggregate each supporting file to that ID before combining it with the customer master.
For the worked example, a product flag is one when a relationship has any active holding in that family and zero otherwise. Multiple deposit accounts still produce a single deposit flag. Relationship income is the bank's supplied quarterly net interest income after its funding allocations, plus relationship fee income. It is an income measure, not a complete profit measure.
Put the following table in an Excel sheet named Relationships, with the headers in row 1 and the records in rows 2 through 7. All figures are illustrative business records.
| Relationship | Sector | Annual business revenue (USD) | Deposits flag | Lending flag | Treasury flag | Quarterly relationship income (USD) |
|---|---|---|---|---|---|---|
| A | Manufacturing | 8,000,000 | 1 | 1 | 1 | 24,000 |
| B | Manufacturing | 12,000,000 | 1 | 0 | 0 | 12,000 |
| C | Services | 7,000,000 | 1 | 0 | 1 | 18,000 |
| D | Services | 18,000,000 | 1 | 0 | 0 | 9,000 |
| E | Manufacturing | 30,000,000 | 1 | 1 | 1 | 42,000 |
| F | Services | 40,000,000 | 1 | 0 | 1 | 30,000 |
Column C describes the customer's business revenue. Column G describes income earned by the bank. They have different meanings and must never be substituted for each other.
Add Size band in H1, Active families in I1 and Segment in J1. Enter these formulas in row 2 and fill down through row 7:
H2 =IF(C2="","Unknown",IF(C2<20000000,"Small","Midsize"))
I2 =SUM(D2:F2)
J2 =B2&" / "&H2
Treat missing product flags as unresolved data during preparation; the sum assumes every flag has already been validated. Keep the source observation dates alongside the workbook so a later reviewer can distinguish a genuine business change from a revised record.
Create a summary sheet with the sector in A2 and size band in B2. For each combination, use:
C2 =COUNTIFS(Relationships!$B$2:$B$7,A2,Relationships!$H$2:$H$7,B2)
D2 =SUMIFS(Relationships!$G$2:$G$7,Relationships!$B$2:$B$7,A2,Relationships!$H$2:$H$7,B2)
E2 =IF(C2=0,"",SUMIFS(Relationships!$I$2:$I$7,Relationships!$B$2:$B$7,A2,Relationships!$H$2:$H$7,B2)/C2)
F2 =IF(C2=0,"",SUMIFS(Relationships!$F$2:$F$7,Relationships!$B$2:$B$7,A2,Relationships!$H$2:$H$7,B2)/C2)
The summary columns are relationship count, income, average active families and treasury penetration. Format F as a percentage. Microsoft's documentation explains that COUNTIFS counts rows meeting all supplied criteria, while SUMIFS adds values meeting the criteria. Covirage is not affiliated with Microsoft.
Treasury penetration = Relationships with treasury / Relationships in the segment.
Average active families = Sum of active family counts / Relationships in the segment.
| Sector | Size band | Relationships | Quarterly income (USD) | Average active families | Treasury penetration |
|---|---|---|---|---|---|
| Manufacturing | Small | 2 | 36,000 | 2.0 | 50.0% |
| Services | Small | 2 | 27,000 | 1.5 | 50.0% |
| Manufacturing | Midsize | 1 | 42,000 | 3.0 | 100.0% |
| Services | Midsize | 1 | 30,000 | 2.0 | 100.0% |
| Total | 6 | 135,000 | 2.0 | 66.7% |
Display averages and percentages to one decimal place; calculate with unrounded values. Small manufacturing income is 24,000 + 12,000 = $36,000, or 36,000 / 2 = $18,000 per relationship. Small services income is 18,000 + 9,000 = $27,000, or $13,500 per relationship. That difference invites a review of balances, pricing and usage; it does not establish the reason.
The income check is 36,000 + 27,000 + 42,000 + 30,000 = $135,000, matching the six source rows. Product family counts sum to 12, so the overall average is 12 / 6 = 2.0. Treasury flags sum to four, so overall penetration is 4 / 6 = 66.7%. Recompute the total ratios from those counts rather than averaging the segment percentages.
Tie the income total to the same scoped bank report and confirm every relationship appears in exactly one segment. Keep an unknown group in the reconciliation if any customer lacks an approved classification.
The relationship team owns the follow-up list; finance owns the income definition, and the customer-data owner maintains identity and business attributes. Review missing holdings with the account owner before interpreting them as unmet needs. A relationship may have no recorded treasury holding because it does not need the service, because another provider supplies it, or because the export is incomplete.
Add documented context through customer profiling and segmentation rather than inferring motives from income alone. Preserve the original group assignment when comparing later periods so a size-band change can be separated from a change in relationship performance.
Upload a relationship file with your approved groups, product holdings and income, and Covirage maps the columns while its tools compute and check the comparisons. The external AI model explains the results with every figure traced to its rows. Create a free Covirage account, which takes about a minute, then set up your business and bring a sample of your own data.
See Covirage for commercial banking for the supporting industry overview.
Use the unit your relationship team manages, such as an approved parent relationship or legal entity. Document the choice and map every account to that unit once. Mixing parent relationships with subsidiary accounts overstates customer counts and can duplicate income.
Keep product holdings as a separate comparison unless a specific review requires a product-based group. A company's sector and size describe the business. Its deposits, lending and treasury holdings describe its current relationship with the bank and can change independently.
Not necessarily. Relationship income does not establish profitability unless the calculation also includes the relevant funding allocations, service costs and other approved charges. Label the income basis explicitly and avoid calling an income ranking a risk-adjusted profit ranking.