Blog · Territory, capacity and quota planning · Retail banking
Build retail banking groups from approved product, activity and contribution exports. Use explicit assignment rules, calculate group comparisons in Excel and check that every relationship appears exactly once.
Customer segmentation in banking turns approved relationship attributes, product holdings and observed activity into groups that can be compared. Write the assignment rules before reviewing the outcomes, apply them to one row per relationship, and reconcile every group to the source population. A useful segment has a clear definition and a business question attached to it.
This workflow helps a US retail bank compare relationship activity and management contribution. It does not construct lending criteria. The initial question is practical: which groups need a closer relationship review, and which differences come from product use rather than customer count?
Use the opening relationship cohort for June 2026 and retain relationships that close during the month. Record status at June 30, products held at that date, and customer-initiated transactions during June. Keep new customers acquired during June in a separate view so they do not change the cohort midway through the comparison.
Use the bank's approved relationship identifier and group ownership convention. Household groupings and individual customers are different populations. Commercial business relationships also need different operating measures; use commercial banking customer segmentation for that separate question.
The working export needs an identifier, distinct product count, posted customer-initiated transaction count, management contribution, closing deposit balance and closing status. Aggregate accounts first. Exclude bank-generated interest entries from the activity count under the example's stated definition.
Finance supplies contribution for the same month using one approved allocation method. Treat it as a supplied management measure, rather than calling it a deposit balance or a statutory profit figure. If contribution is unavailable for a relationship, flag the missing value; do not silently replace it with zero.
Put the following columns in A1:F1 and the relationship rows in rows 2 through 7. These are illustrative records for a US bank. Dollar inputs are USD; all counts are whole numbers.
| Relationship | Products | June transactions | June contribution (USD) | Closing balance (USD) | Closing status |
|---|---|---|---|---|---|
| R1 | 3 | 30 | 180 | 30,000 | Open |
| R2 | 2 | 12 | 120 | 20,000 | Open |
| R3 | 1 | 4 | 40 | 12,000 | Open |
| R4 | 2 | 8 | 70 | 8,000 | Open |
| R5 | 1 | 0 | -20 | 5,000 | Open |
| R6 | 0 | 0 | -10 | 0 | Closed |
| Total | 9 | 54 | 380 | 75,000 |
For the distinction between a descriptive profile and an assigned group, see customer profiling and segmentation. Here, the segment is a reproducible rule, rather than an invented persona.
Use this precedence: a closed relationship is Closed; an open relationship with no June transactions is Inactive; any other relationship holding at least two products is Multi-product; the remaining active relationship is Single-product. Inactive describes this measurement window, rather than a regulatory dormancy classification.
In G1 enter Segment. In G2 enter the formula and fill down through G7:
=IF(F2="Closed","Closed",IF(C2=0,"Inactive",IF(B2>=2,"Multi-product","Single-product")))
Microsoft's IF documentation explains the logical test and the two possible results. The nesting makes precedence explicit: R6 is Closed even though it also has no transactions. R5 is Inactive even though it holds a product.
Before using the formula, validate that product and transaction counts are present and nonnegative and that status contains an approved value. An active record with zero products should be reviewed rather than accepted as a single-product relationship. All example rows pass these input checks.
List Multi-product, Single-product, Inactive and Closed in I2:I5. Calculate the relationship count in J2, contribution in K2 and mean contribution in L2:
=COUNTIFS($G$2:$G$7,I2)
=SUMIFS($D$2:$D$7,$G$2:$G$7,I2)
=IF(J2=0,"",K2/J2)
Microsoft documents COUNTIFS as counting rows that meet the selected criteria. Copy the formulas down; sum C for transactions and E for balances using the same segment criteria. Display mean contribution to two decimals, with all other dollar values shown as whole dollars.
| Segment | Relationships | Transactions | Contribution (USD) | Mean contribution (USD) | Closing balance (USD) |
|---|---|---|---|---|---|
| Multi-product | 3 | 50 | 370 | 123.33 | 58,000 |
| Single-product | 1 | 4 | 40 | 40.00 | 12,000 |
| Inactive | 1 | 0 | -20 | -20.00 | 5,000 |
| Closed | 1 | 0 | -10 | -10.00 | 0 |
| Total | 6 | 54 | 380 | 63.33 | 75,000 |
The total mean is 380 / 6 = $63.33, rounded. It is not the average of the displayed group means. For the broader measure definitions, use the financial-services customer KPI guide.
The count check is 3 + 1 + 1 + 1 = 6. Contribution ties as 370 + 40 - 20 - 10 = $380; balances tie as 58,000 + 12,000 + 5,000 + 0 = $75,000. Each relationship identifier must appear once, and no Segment cell may be blank.
Multi-product relationships are 3 / 6 = 50.0% of this cohort. Their higher contribution is an observation, not proof that adding a product causes an increase. Costs, customer tenure and balance levels may differ. Have finance validate allocations and relationship managers review the underlying records before recommending action.
When the question becomes movement between lifecycle stages, use customer journey segmentation. Keep that separate from the product and activity rules used here.
Covirage is not affiliated with Microsoft.
Bring a prepared relationship export with approved attributes, product counts, activity, contribution and the segment rules used in your review. Covirage maps the columns; its tools compute and check group comparisons, 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 Covirage for retail banking for the banking context.
Start with variables needed for the business question, such as distinct product holdings, customer-initiated activity and consistently measured contribution. Use approved attributes and definitions. Adding more fields does not automatically make a segment more useful or interpretable.
Match the review cycle to the decision and the availability of complete exports. Keep prior assignments and the rule version so that a change in customer activity can be distinguished from a change in the segmentation method.
A relationship can have several descriptive labels, but a segment table used for additive totals needs one primary assignment. Set precedence rules for overlapping conditions and retain secondary labels separately so customer counts and balances are not duplicated.