Blog · How-to guides · Finance and FP&A teams
Calculate customer profitability in Google Sheets from sales, product costs and service activity. Allocate documented cost pools, rank customers and reconcile the totals before making decisions.
Customer profitability in Google Sheets is net revenue less product costs and the service costs attributable to each customer. Build the calculation with stable customer IDs, explicit allocation rates and a reconciliation to the cost pools. Show the result as contribution after selected service costs so readers know what remains outside the calculation.
Use one period and currency throughout. This example covers a US distributor's September 2026 activity in USD. The accounting close supplies net revenue after credits and the matching product cost; operations supplies orders handled and support hours.
Create these tabs with headers in row 1:
| Tab | Columns | Source control |
|---|---|---|
| Sales | A invoice reference, B customer ID, C net revenue, D product cost, E date | Revenue and product cost tie to the ledger |
| Activity | A date, B customer ID, C orders handled, D support hours | Counts and hours tie to reviewed activity reports |
| Pools | A pool, B cost, C total driver units, D rate | Costs tie to selected ledger accounts |
| Summary | A customer ID through H contribution margin | One row per customer across both exports |
Do not build the customer list from positive invoices alone. Include customers with service work, credit memos or zero net sales. Where operational and accounting identifiers differ, approve the mapping before combining the files.
The existing cost-to-serve method covers how to choose cost pools and drivers. This workflow applies those choices in Sheets; it does not assume a revenue-based allocation is appropriate for every business.
Put the period start and end dates in Summary!K1 and K2. In Pools, enter order-processing cost in B2 and support cost in B3. Populate the driver totals from the whole activity export, including customers absent from the sales file.
=SUMIFS(Activity!$C$2:$C$1000,Activity!$A$2:$A$1000,">="&Summary!$K$1,Activity!$A$2:$A$1000,"<="&Summary!$K$2)
=SUMIFS(Activity!$D$2:$D$1000,Activity!$A$2:$A$1000,">="&Summary!$K$1,Activity!$A$2:$A$1000,"<="&Summary!$K$2)
These formulas go in C2 and C3 respectively. Google's SUMIFS documentation describes summing values that meet multiple criteria; here both date limits must hold. This assumes date-only values, so remove time components consistently before applying an inclusive end-date filter.
In D2 enter =B2/C2 and fill down to D3. A zero denominator requires investigation, not a zero rate.
| Pool | Cost (USD) | Driver units | Rate (USD per unit) |
|---|---|---|---|
| Order processing | 12,000 | 600 orders | 20 |
| Customer support | 12,000 | 200 hours | 60 |
| Total cost | 24,000 |
The rates are 12,000 / 600 = $20 per order and 12,000 / 200 = $60 per support hour. Rates allocate the selected pool; they do not establish the cash savings from removing an individual order or customer.
On Summary, use B for revenue, C for product cost, D for orders, E for support hours, F for service cost, G for contribution and H for contribution margin. With the customer ID in A2, enter revenue in B2:
=SUMIFS(Sales!$C$2:$C$1000,Sales!$B$2:$B$1000,$A2,Sales!$E$2:$E$1000,">="&$K$1,Sales!$E$2:$E$1000,"<="&$K$2)
For C2 use the same formula with Sales!$D$2:$D$1000 as its sum range. In D2 calculate orders:
=SUMIFS(Activity!$C$2:$C$1000,Activity!$B$2:$B$1000,$A2,Activity!$A$2:$A$1000,">="&$K$1,Activity!$A$2:$A$1000,"<="&$K$2)
For E2 change the sum range to Activity!$D$2:$D$1000. Then enter F2, G2 and H2:
=D2*Pools!$D$2+E2*Pools!$D$3
=B2-C2-F2
=IF(B2=0,"NO REVENUE",G2/B2)
Copy these formulas down the complete customer list. The example's source totals produce this result; margins are rounded to two decimal places.
| Customer | Revenue (USD) | Product cost (USD) | Orders | Support hours | Service cost (USD) | Contribution (USD) | Margin |
|---|---|---|---|---|---|---|---|
| A | 120,000 | 72,000 | 120 | 30 | 4,200 | 43,800 | 36.50% |
| B | 100,000 | 60,000 | 400 | 120 | 15,200 | 24,800 | 24.80% |
| C | 80,000 | 56,000 | 80 | 50 | 4,600 | 19,400 | 24.25% |
| Total | 300,000 | 188,000 | 600 | 200 | 24,000 | 88,000 | 29.33% |
For customer B, service cost is 400 × 20 + 120 × 60 = $15,200. Contribution is 100,000 - 60,000 - 15,200 = $24,800. Keep the terminology consistent with gross, contribution and net margin.
In a separate output area, rank contribution dollars using:
=SORT(A2:H4,7,FALSE)
Google's SORT documentation supports sorting a range by a specified column; FALSE puts the largest contribution first. Use column 8 for a separate percentage ranking when all relevant margins are numeric. Covirage is not affiliated with Google.
Three controls complete the calculation. Revenue and product costs must match the independently checked sales export. Summary orders and hours must equal the complete activity totals. Finally, allocated service cost must equal the pools:
=SUM(F2:F4)-SUM(Pools!B2:B3)
That is 24,000 - 24,000 = $0. Portfolio contribution is 300,000 - 188,000 - 24,000 = $88,000; divide by $300,000 for 29.33%, rather than averaging customer percentages. The contribution worked example gives another account-level reconciliation.
Bring the sales workbook, supplied service-cost allocations and the activity that supports them, or upload the Google Sheet. Covirage maps the columns, its tools compute and check the figures, and the external AI model explains the results with every figure traced to its rows. Create a free Covirage account, then set up your business and bring a sample of your own data.
See customer profitability with Covirage for the related account-level work.
Use a driver that reasonably reflects how the cost arises, such as orders handled or support hours. Revenue allocation gives every customer the same service cost percentage, which can hide different service demands. Document the chosen driver and review unusual results with the operating team.
No. Contribution after selected product and service costs excludes any corporate expenses, interest and taxes outside those pools. Label the measure precisely. A fully allocated profitability view needs additional documented allocations and should remain distinguishable from a measure used for service decisions.
Keep them in the customer list when they incur service activity or credits. Their dollar contribution still matters, but a margin percentage with zero revenue is undefined. Display a clear label instead of converting the division error into a zero percent margin.
A low result starts an investigation rather than deciding the relationship. Check credits, product mix, unusual support work and allocation assumptions. Then examine whether pricing, order size or service arrangements can improve the economics, including which costs would actually change.