Sales analysis: a worked example by month, product, region and customer

By Killian, founder · Updated 4 November 2026 · Markdown

A sales analysis answers four questions from your sales data: how much did we sell over time, what sold, where, and to whom. In practice that means revenue by month, by product, by region (or segment) and by customer, each compared with the period before, plus a few checks that the numbers are right: returns netted out, every order matched to a customer, customers counted once. Below is a worked example on a real-looking quarter of orders, with every answer, the spreadsheet formulas, three mistakes that skew the numbers, and how to ask an AI agent the same questions across two sheets.

The example data

A small coffee wholesaler’s third quarter (July to September 2026), in two sheets:

Orders: one row per order line (39 rows), exported from the shop or invoicing tool. Columns order_id, date, month, customer_id, product, quantity, unit_price, amount. Two rows are returns, with a negative quantity and amount.

Customers: one row per customer (8 rows). Columns customer_id, name, region, segment. Region is North, South or West; segment is Café, Office or Retail.

The Customers sheet:

Made-up data, small enough to check by hand. Order 1037 comes from C09, a new customer who isn’t in the Customers sheet yet; that happens in every real export and matters below.

The four questions, answered

All totals are net of returns: 11,570 of sales minus 280 of returns gives 11,290 of net revenue for the quarter.

1. Over time: revenue by month

August was flat (+0.9% on July); September grew 38.9% on August (+1,300). Question 3 shows where that came from.

2. What sold: revenue by product

Two products make almost 90% of revenue. A line with a high return rate (the grinder: 1 of 2 came back) is worth a look even when its revenue is small.

3. Where: revenue by region, and what changed in September

September’s +1,300 is North +1,830 and the new customer +480, against West −580 and South −430. So the growth is one region (mostly Morning Cup: six order lines and 1,900 in September) plus one new account, while the two other regions fell. By segment: Café 5,350, Office 3,750, Retail 1,710, plus the 480 not yet in Customers.

4. To whom: top 5 customers

Five of nine customers bring 82.6% of revenue, and one brings a quarter. That’s the concentration risk to watch: losing Morning Cup would cost more than all of South.

The formulas in Excel or Google Sheets

With the orders on a sheet called Orders (month in column C, customer in D, product in E, amount in H):

• Revenue by month (next to a list of months in column A): =SUMIFS(Orders!$H:$H, Orders!$C:$C, $A2). The same with column E gives revenue by product. A pivot table (rows: month, values: SUM of amount) does both in one go.
• Region on each order: add a column to Orders with =XLOOKUP(D2, Customers!$A:$A, Customers!$C:$C, "Not in Customers"), then pivot on it. The fourth argument matters (mistake 3).
• Customers in a period: Google Sheets =COUNTUNIQUE(FILTER(Orders!$D:$D, Orders!$C:$C=$A2)); Excel 365 =ROWS(UNIQUE(FILTER(Orders!$D:$D, Orders!$C:$C=$A2))).
• Share: each line’s revenue divided by the total, formatted as a percentage.

Keep month as text such as 2026-07, so grouping and lookups match exactly.

Three mistakes that skew a sales analysis

1. Adding up customers across months. 5 + 7 + 7 = 19 “customers” in the quarter, but only 9 different customers ordered. Most customers order in more than one month, so monthly customer counts never add up to a quarterly one. Count distinct customers over the whole period instead.

2. Mixing returns into averages. Average order value as net revenue ÷ all rows is 11,290 ÷ 39 = 289.49, because the two return rows count as “orders”. Sales ÷ sales rows is 11,570 ÷ 37 = 312.70. Pick one definition, write it down, and use it every month; the difference here is 8%.

3. Losing orders in a lookup. A plain VLOOKUP returns #N/A for C09, and a pivot that filters out errors or blanks shows regions adding up to 10,810 instead of 11,290. The 480 doesn’t disappear from the business, only from the report. Give the lookup a fallback (“Not in Customers”) and check that the regional total equals the overall total.

Ask an agent instead of rebuilding the pivots

Each new question (“and by segment?”, “only North?”, “who stopped ordering?”) means another pivot or formula, and pasting both sheets into a chat stops working once the export has tens of thousands of lines (the token cost calculator shows how fast a pasted sheet fills the context window). datatomcp keeps both sheets in one workspace and lets any agent (Claude, ChatGPT, Cursor, an n8n flow) query them, so the rows stay with us and only the question and the answer go through the chat.

1. Add both sheets. Sign in at app.datatomcp.com, open Add data, and upload the two CSVs or paste their Google Sheets links. A Google Sheet stays in sync, so next month’s orders show up without a re-upload.

2. Confirm the link. datatomcp checks the columns the sheets share and finds customer_id in both. Because C09 isn’t in Customers, 8 of the 9 customer IDs in Orders are found (89%), so the link is suggested, not confirmed on its own. Confirm it on the relationship map:

3. Connect your agent (any MCP client, or Claude step by step) and ask in plain words.

We ran the four questions above through datatomcp’s query engine on these two sheets; every table on this page is its output. Each one is a single aggregate call on Orders: grouped by month, by product, by customers.region (a column of the linked sheet) or by customers.name, summing amount. The shares and the month-on-month changes are the agent dividing two numbers.

What the results say on their own:

• Unmatched rows are reported, not hidden. Grouped by region, the answer carries the 480 in a blank group and says that 1 row found no match in Customers; the agent is told to mention it. That is mistake 3 caught without a check formula.
• Customers are counted once. “How many customers ordered in Q3?” is a distinct count on customer_id: 9, not 19.
• Returns are rows like any other. Ask for “sales only” and the agent adds a filter amount > 0 (11,570 over 37 lines); ask for “net revenue” and it doesn’t. Say which one you mean, or see the returns themselves with query_rows (orders 1016 and 1039).

FAQ

What is a sales analysis? A review of sales data that shows how revenue changes over time and where it comes from: by product, by region or segment, and by customer. It usually compares one period with the previous one and with a target.

How do I analyze sales data in Excel? Put the export in one sheet, add lookups for the customer’s region or segment, and build pivot tables of revenue by month, product, region and customer. Use SUMIFS for fixed reports and COUNTUNIQUE (Sheets) or UNIQUE (Excel 365) to count customers. Check that every breakdown adds up to the same total.

What should a sales analysis report include? Revenue by period with the change on the previous period, revenue by product and by region or segment with shares, the top customers with their share (concentration), returns, and any orders that couldn’t be matched to a customer or product.

Why don’t my monthly customer counts add up to the quarter? Because the same customer orders in several months. Count distinct customers over the whole quarter instead of adding monthly counts.

Can ChatGPT or Claude analyze sales data? Yes, if they compute the totals rather than read them. With a small file uploaded, the chat runs code and can be right. With a full export, two linked sheets or the same questions every month, connect the sheets so the agent sends a query and gets back counted totals: how datatomcp works with Claude.

Create a free account and try it on your own orders and customers. Free covers 3 datasets of up to 100,000 rows each.

More: Budget vs actual: variance formula and the double-counting trap · Test an AI data tool on linked sheets · Why AI chats get spreadsheet totals wrong