Blog · Board and management reporting
Customer revenue analysis reconciles a sales movement to the accounts behind it. Build a new, lost and continuing customer bridge from invoice exports, with spreadsheet formulas and ledger checks.
Customer revenue analysis explains a revenue change by showing which accounts started buying, stopped buying, expanded or contracted between comparable periods. Build the customer table first, reconcile it to the period totals, and then use the movement to decide which accounts need commercial review.
Start with two complete periods on the same basis. This example compares August and September 2026 for a US distributor, using USD net revenue. Its invoice posting dates match revenue recognition; where that is untrue, use revenue ledger rows instead and reconcile invoice billings separately.
Export customer ID, document ID, posting date, signed net amount and currency. Exclude sales tax and apply the same policy for freight, discounts and rebates in both periods. Credit memos need negative amounts, so their effect remains visible in the customer total.
Keep an account mapping table for renamed customers and merged billing entities. Otherwise, a change in customer ID can appear as a loss and a new account even when the underlying relationship continued. Preserve the original IDs so a reviewer can trace the mapping.
This is a movement report. The separate customer value analysis addresses broader account value, while a customer profitability report adds costs and contribution.
Put transaction customer IDs in column A, posting dates in B and signed net revenue in C, using rows 2:1000. Extend those ranges to include the entire export. In E2:E7, list each unique customer appearing in either period, including customers with no current-period sales.
Microsoft's SUMIFS documentation explains summing amounts that meet several criteria. Use the customer ID and posting-date boundaries together:
F2 = SUMIFS($C$2:$C$1000,$A$2:$A$1000,E2,
$B$2:$B$1000,">="&DATE(2026,8,1),
$B$2:$B$1000,"<"&DATE(2026,9,1))
G2 = SUMIFS($C$2:$C$1000,$A$2:$A$1000,E2,
$B$2:$B$1000,">="&DATE(2026,9,1),
$B$2:$B$1000,"<"&DATE(2026,10,1))
H2 = G2-F2
I2 = IF(AND(F2=0,G2>0),"New",
IF(AND(F2>0,G2=0),"Lost",
IF(AND(F2>0,G2>0),"Continuing","Review")))
Fill down to row 7. The exclusive upper date boundary also handles posting dates that include times. Negative customer totals and other unusual combinations go to Review, so a return-heavy account cannot silently become an ordinary loss.
Here, New means revenue appears only in September, and Lost means revenue appears only in August. Check earlier history before describing New as a first-ever customer; it could be a reactivation.
| Customer | August revenue (USD) | September revenue (USD) | Change (USD) | Group |
|---|---|---|---|---|
| A | 100,000 | 125,000 | 25,000 | Continuing |
| B | 80,000 | 60,000 | -20,000 | Continuing |
| C | 50,000 | 0 | -50,000 | Lost |
| D | 0 | 45,000 | 45,000 | New |
| E | 30,000 | 30,000 | 0 | Continuing |
| F | 0 | 20,000 | 20,000 | New |
| Total | 260,000 | 280,000 | 20,000 |
Revenue rises by $20,000. Growth is 20,000 / 260,000 = 7.7%, rounded to one decimal place. That headline alone hides the departure of C and the contraction at B.
Split the continuing group into increases and decreases, keeping contraction and lost revenue as positive amounts that the bridge subtracts.
Current revenue = Prior revenue + New revenue - Lost revenue + Continuing expansion - Continuing contraction
Use these formulas against the summary columns:
New = SUMIFS(G2:G7,I2:I7,"New")
Lost = SUMIFS(F2:F7,I2:I7,"Lost")
Expansion = SUMIFS(H2:H7,I2:I7,"Continuing",H2:H7,">0")
Contraction = -SUMIFS(H2:H7,I2:I7,"Continuing",H2:H7,"<0")
| Bridge step | Movement (USD) | Running revenue (USD) |
|---|---|---|
| August opening total | 260,000 | 260,000 |
| New-period accounts D and F | 65,000 | 325,000 |
| Lost-period account C | -50,000 | 275,000 |
| Expansion at A | 25,000 | 300,000 |
| Contraction at B | -20,000 | 280,000 |
The final $280,000 equals the September customer total. A second check isolates continuing customers: August revenue of $210,000 plus $25,000 expansion less $20,000 contraction equals September revenue of $215,000. Adding $65,000 from the new group again gives $280,000.
Count accounts too. Microsoft documents COUNTIFS for counting rows that meet the supplied criteria. =COUNTIFS(I2:I7,"Continuing") returns 3; replacing the criterion with New and Lost returns 2 and 1. Those groups reconcile to all 6 customers. Covirage is not affiliated with Microsoft.
Suppose D's September rows include an additional $15,000 credit memo. Its net revenue falls from $45,000 to $30,000, and the new group falls from $65,000 to $50,000. Everything else stays unchanged.
The revised bridge is 260,000 + 50,000 - 50,000 + 25,000 - 20,000 = $265,000. Growth becomes 5,000 / 260,000 = 1.9%, rounded to one decimal place. A missing credit would therefore overstate both the ending total and the acquisition story.
Finance owns the reconciliation; account owners explain the transactions behind the largest movements. Use customer survival analysis when the next question concerns retention at comparable tenure rather than revenue dollars.
Bring both periods of signed revenue rows and your customer mapping file. Covirage maps the columns, and its tools compute and check customer totals and movement comparisons; 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 export.
See revenue driver analysis for the supporting review approach.
Use a transaction export with customer ID, posting date, document ID, currency and signed net revenue. Include credit memos and a customer master for account mapping. Use revenue ledger rows when invoice timing differs from the accounting period in which revenue is recognized.
No. Zero revenue in the comparison period means the account contributes no revenue to that period's bridge. Seasonal buying, delayed orders or a contract pause may explain it. Use a separate, documented customer loss definition before calling the relationship churned.
Revenue measures sales attributed to the account. Profitability also subtracts the costs included in your contribution definition, such as goods and servicing costs. A customer can produce more revenue while generating less contribution if discounts or service demands increase.