Blog · Board and management reporting · Retail banking
Compare banking transaction activity, balances and product use without confusing a decline with a confirmed customer exit. Work from prepared exports, calculate changes in Excel and reconcile the relationships behind each result.
Bank customer behavior analysis compares transaction activity, balance movements and product use before drawing conclusions about attrition. Measure those changes separately, then check relationship status to distinguish a confirmed exit from reduced use. The goal is a review list supported by rows, rather than an unsupported reason for a customer's behavior.
Start with customers whose relationships were open at the beginning of the earlier window. Keep their identifiers in the file when an account or the whole relationship subsequently closes. New customers belong in a separate acquisition comparison.
Choose observation windows that give activity counts a fair basis. This example uses June 1 through June 30 and July 1 through July 30, 2026: two complete 30-day windows, not two calendar months. Prior and current balances and product counts are snapshots at each window's end.
Define activity as posted customer-initiated transactions, excluding bank-generated interest entries and reversed transactions. Document the included channels. If one export contains card activity and the other omits it, an apparent decline could simply be missing coverage.
Use approved relationship identifiers and a consistent joint-account attribution rule. This workflow supports a bank's relationship review; it does not perform underwriting or fraud detection. The broader customer behavior analysis method covers the general comparison; the banking distinction here is keeping balances, activity and closure evidence separate.
Prepare account-level extracts before joining them to the relationship register. Sum transactions and balances to the relationship grain, count distinct product categories, and bring in confirmed closure status. An account closure is not automatically a relationship closure when another account remains open.
Place the following columns in A1:H1, with the relationship records in rows 2 through 5. The final column is 1 only for a confirmed relationship closure by July 30; otherwise it is 0. All amounts are USD.
| Relationship | Prior transactions | Current transactions | Prior balance (USD) | Current balance (USD) | Prior products | Current products | Confirmed closure |
|---|---|---|---|---|---|---|---|
| R1 | 20 | 10 | 10,000 | 9,500 | 2 | 2 | 0 |
| R2 | 10 | 10 | 20,000 | 10,000 | 2 | 2 | 0 |
| R3 | 8 | 0 | 8,000 | 0 | 1 | 0 | 1 |
| R4 | 0 | 5 | 5,000 | 7,000 | 1 | 1 | 0 |
| Total | 38 | 25 | 43,000 | 26,500 | 6 | 5 | 1 |
R3's zeros are verified values after closure. A missing current extract would need a missing-data flag instead. Keep a separate check for that condition before calculating any change.
In I2 calculate transaction percentage change, in J2 the dollar balance change, and in K2 the product-count change. Fill through row 5:
=IF(B2=0,"N/A",C2/B2-1)
=E2-D2
=G2-F2
Microsoft's IF documentation explains how a logical test selects between results. Here it protects the transaction percentage when the earlier count is zero. Do not substitute 0% for that unavailable result.
For the balance percentage change, use this additional formula; all opening balances in the example are positive:
=IF(D2=0,"N/A",E2/D2-1)
Display percentages to one decimal place. Product changes are whole counts, and dollar changes are shown without decimals.
| Relationship | Transaction change | Balance change (USD) | Balance change | Product change | Confirmed closure |
|---|---|---|---|---|---|
| R1 | -50.0% | -500 | -5.0% | 0 | No |
| R2 | 0.0% | -10,000 | -50.0% | 0 | No |
| R3 | -100.0% | -8,000 | -100.0% | -1 | Yes |
| R4 | N/A | 2,000 | 40.0% | 0 | No |
For the whole cohort, calculate changes from summed counts and balances. The transaction change is 25 / 38 - 1 = -34.2%; the balance change is 26,500 / 43,000 - 1 = -38.4%, both rounded. Do not average relationship percentage changes.
Confirmed attrition uses relationship count, rather than balances or product count:
=COUNTIFS(H2:H5,1)/COUNTA(A2:A5)
Microsoft documents COUNTIFS as counting rows that satisfy the chosen criteria. Here one confirmed closure divided by four opening relationships gives 25.0% attrition. No external benchmark is implied by this small illustration.
Balance changes sum to -500 - 10,000 - 8,000 + 2,000 = -$16,500. That equals $26,500 minus $43,000. Transaction changes are -10, 0, -8 and 5, summing to -13, which equals 25 minus 38. Product counts fall from six to five because R3 loses one product.
R1 warrants an activity review: its products are unchanged and its balance decline is smaller than its activity decline. R2 warrants a balance-movement review because transaction count and products remain unchanged. R3 is the confirmed exit. R4 starts transacting after an inactive prior window; report the five additional transactions without inventing a percentage.
Assign an owner to confirm the relevant facts and document the follow-up date. Use the lost customer review template for a confirmed exit's timeline. If the question becomes the economics of the relationship, use bank customer profitability; balances alone do not establish profitability.
For product-specific investigation, a cross-sell matrix answers a different question from the product-count movement shown here. Avoid calling every decline a product opportunity.
Covirage is not affiliated with Microsoft.
Bring prepared relationship exports with matched observation windows, transaction counts, balances, product holdings and confirmed status. Covirage maps the columns; its tools compute and check the changes, 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.
No. A balance can fall while transaction activity and product holdings remain stable. Review the balance movement alongside those measures and confirmed relationship status. The export establishes what changed; documented customer context helps explain why.
Define a starting relationship cohort and count which relationships have a confirmed closure by the observation date. Divide those closures by the starting cohort count. Report account closures separately when a customer retains other accounts.
Report the absolute increase and show percentage change as unavailable. Dividing by zero cannot produce a meaningful growth rate. Keep that relationship in total counts and compare the rest of its measures using valid denominators.