Analyze customer profitability

Step 1: Merge dataExcel

Open both exports and convert each to a proper Excel Table (select the range, then Ctrl+T) rather than leaving them as plain ranges — a Table auto-expands when next month's export has more rows and lets you reference columns by name instead of by letter. Confirm both Tables share a common key column, like Customer ID, in the same format; watch for one storing IDs as text and the other as numbers, which won't throw an error, it'll just silently fail to match.

In a new sheet, use XLOOKUP against the structured references (=XLOOKUP(A2, CostTable[CustomerID], CostTable[ServiceCost])) to pull each customer's service cost onto the same row as their revenue, then repeat for any other cost columns you need. If you get #N/A, check for trailing spaces or a format mismatch before assuming the customer is simply missing from one file.

Step 2: Calculate marginExcel

Add a Gross Margin column (=[@Revenue]-[@DirectCosts]) and a Margin % column (=[@GrossMargin]/[@Revenue]) directly inside the Table — because it's a Table, the formula fills down automatically as rows are added later, instead of you dragging a fill handle and risking a broken reference. Format the margin column as a percentage.

Before you trust the numbers, check for two traps: a customer with costs but zero recorded revenue (shows as −100% or worse, and usually means a billing gap, not a real loss) and blank cost cells, which Excel treats as zero and makes that customer look more profitable than they are. Flag both cases in a separate column instead of deleting the rows, so the data problem stays visible rather than disappearing.

Step 3: Spot patterns in the drainsCopilotChatGPTClaude

Sort the table by Margin % ascending and select the bottom 10% of customers — note the exact cutoff (row count or percentage) you used so you can reproduce it next month. If Microsoft 365 Copilot is licensed, open it in Excel's side pane and ask it to summarize patterns in the selected range directly; it reads the live data without any copying.

Without a license, copy the selected rows — contract terms and service-usage columns included, not just the margin figure — into ChatGPT or Claude and ask it to group the accounts by likely cause. Either way, treat the AI's grouping as a starting hypothesis: open two or three accounts from each group and confirm the pattern actually holds before you present it as fact.

Prompt idea:

Here is a table of our lowest-margin customers with their contract terms and service usage. Group them by likely cause of the low margin (pricing, usage volume, service cost) and list the 3 most common patterns you see.

Step 4: Review and share the findingsExcel

Paste the AI's category for each customer back into Excel as a new column next to the original data, so the grouping stays attached to the numbers it came from. Before this leaves your machine, strip or mask anything beyond what the renegotiation conversation actually needs — account numbers, named contacts, anything that identifies an individual past what's required.

Finish with a short, one-page list: the bottom-10% customers, a one-line reason for each (duplicated service, aggressive legacy discount, missing invoice), and a specific next action per customer — renegotiate, review contract, write off — not just the raw numbers.

Check the work

• Reconcile total revenue and direct costs against the source exports.
• Confirm the join did not duplicate or drop customer records.
• Inspect every negative-margin result against its source rows.
• Exclude or clearly label records with missing cost data.