Customer analytics in banking: compare relationships

Customer analytics in banking compares relationship balances, product holdings and retention on the same customer base. Start with a prepared relationship export, keep the observation dates fixed, and calculate each measure separately. A balance increase, a broader product relationship and a retained customer are different outcomes.

Define the relationship before comparing it

Decide whether a relationship means an individual customer, an approved household grouping or another established bank identifier. Use that definition throughout the comparison. An account identifier is insufficient when a customer holds several accounts: adding account rows counts that customer repeatedly.

For this workflow, the relationship register supplies one row per customer who was open on June 1, 2026. The balance and product measures are observed at June 30, 2026. New customers acquired during June belong in a separate acquisition view, so the opening cohort remains available for an honest retention calculation.

Use approved, pseudonymous relationship identifiers in the working file. Keep the data limited to the fields needed for the comparison. This is a commercial relationship review, with no credit decision or live operational instruction attached to a score.

When group definitions are still unsettled, use the separate customer segmentation in banking workflow. The calculation here assumes each relationship already has one agreed group assignment.

Prepare an export with a fixed grain

The minimum columns are relationship ID, group, opening balance, closing balance, closing product count and retained flag. A retained flag of 1 means the relationship remains open under the bank's stated rule; 0 means it has closed. A zero balance alone does not establish closure.

Aggregate individual accounts to the relationship level before joining these fields. Decide how jointly held accounts are attributed so that the same balance does not appear twice. Count product categories consistently: two checking accounts do not automatically mean two different products.

The example below uses deposit relationship balances in USD and distinct product categories. Closed relationship R3 has verified zeros, rather than missing values. Group is the opening assignment, held fixed for the month.

Put these headers in A1:F1 and the relationship rows in rows 2 through 5:

Calculate the comparison in Excel

Create a summary elsewhere on the sheet. Put Branch in H2 and Digital in H3. In I2, count cohort relationships; in J2 and K2, sum opening and closing balances:

=COUNTIFS($B$2:$B$5,H2)
=SUMIFS($C$2:$C$5,$B$2:$B$5,H2)
=SUMIFS($D$2:$D$5,$B$2:$B$5,H2)

Microsoft documents that COUNTIFS counts rows meeting the stated criteria and that SUMIFS adds values meeting criteria. Copy the formulas down for the other group.

Calculate balance change in L2, products per opening relationship in M2, and retention in N2:

=K2/J2-1
=SUMIFS($E$2:$E$5,$B$2:$B$5,H2)/I2
=SUMIFS($F$2:$F$5,$B$2:$B$5,H2)/I2

The example has positive opening balances and nonempty groups. For a group with no opening balance, show balance growth as unavailable; a percentage change from zero has no defined denominator. Display percentages to one decimal place and product averages to one decimal place, using unrounded cells in calculations.

The total rates use the underlying totals: balance change is 85,000 / 100,000 - 1, retention is 3 / 4, and products per opening relationship is 6 / 4. Do not average group growth percentages. The denominator behind a customer comparison determines what the result actually describes.

Reconcile, then decide what deserves attention

The group balance checks are 65,000 + 35,000 = 100,000 at opening and 67,000 + 18,000 = 85,000 at closing. Tie both amounts to the same scoped balance extracts. The relationship count must remain four even though only three relationships survive. Reconcile the closing product total of six to the product extract under the same counting rule.

Branch balances rose by $2,000 while Digital balances fell by $17,000, giving the overall $15,000 decline. R3 accounts for $20,000 of the Digital fall; R4 increased by $3,000. That is a specific review question about one closure, rather than evidence that all Digital relationships weakened.

Give the relationship team the contributing rows and ask for documented context. Product opportunity needs a separate cross-sell matrix; an average product count cannot identify which missing product fits a customer. Keep the wider financial-services customer KPI definitions consistent with this review.

Where it goes wrong

• Counting accounts as customers. Aggregate to the chosen relationship grain before counting or averaging.
• Changing the cohort. Removing closed customers inflates retention and changes the balance denominator.
• Calling balances revenue. A deposit balance is not income or customer contribution; label it as a balance.
• Treating missing as closed. A missing snapshot needs investigation, not a zero balance and a lost-customer flag.
• Attributing cause to a group label. Group differences describe the supplied rows; they do not prove that a channel caused retention or growth.

Covirage is not affiliated with Microsoft.

Compare your own banking export in Covirage

Bring a prepared relationship export with opening and closing balances, product counts, group assignments and status. Covirage maps the columns; its tools compute and check the 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.