To analyse sales by customer without relying on IT, start by writing down the questions sales management asks every month, make sure each sale has a customer, a date, a product and a value in a reliable source, and use a tool that answers plain-language questions about that data. Questions that come up often should move to a dashboard, and every figure should be checkable at its source.
In most small and medium-sized businesses the data already exists. It sits in the invoicing software, in a database or in the Excel exports someone prepares at month-end. What is missing is a way to reach the answer when the question comes up. This article covers which customer questions are worth asking, what data you need, the options for answering them and how to move from one-off questions to regular monitoring.
Why does customer analysis always get pushed back?
The pattern is familiar. The sales director wants to know which customers are buying less than last year. The request goes to whoever can pull data out of the system, usually the IT team or someone in finance. The answer arrives a few days later as a spreadsheet with a pivot table. By then the question has moved on, and the next request joins the back of the queue.
This is not a lack of goodwill on the part of whoever answers. Every new question needs a new extract, and the people who look after the systems have other priorities. The consequence is predictable. The questions that would allow action in time, such as a large customer slowing its orders, only surface once the effect is already in the accounts.
Which customer questions are worth asking?
Sales analysis by customer is the study of what each customer buys, when they buy it and what they are worth over time, so that you can decide where to focus the sales effort. It does not need dozens of reports. Half a dozen questions cover most decisions.
| Question | Decision it supports |
|---|---|
| Who are the ten largest customers and what share of sales do they account for? | Assess dependence on a few customers |
| Which customers bought less this quarter than in the same quarter last year? | Choose whom to visit or contact first |
| Which customers have not placed an order for more than 60 days? | Win customers back before they are lost |
| What is the average order value and buying frequency of each customer? | Adjust commercial terms and visit plans |
| Which product families does each customer buy, and which do they not? | Spot cross-selling opportunities |
| How have sales by customer evolved in each region or for each sales rep? | Allocate targets and accounts |
There is also a classic way of combining three of these questions. RFM analysis ranks each customer by recency (how long since their last purchase), frequency (how often they buy) and monetary value (how much they spend). A high-value customer whose recency is getting worse deserves contact before anyone else.
What data do you need, and where is it?
Sales documents with five fields are enough to answer these questions.
- Customer, with a unique code rather than just a name.
- Date of the document.
- Product or product family, to see what is sold to whom.
- Quantity and value, ideally excluding VAT.
- Document type, to separate invoices from credit notes.
Region, account manager and line cost add a great deal, but are not needed to get started. This data usually lives in the invoicing software's database or in the ERP exports to Excel that are already produced for other purposes.
Before the first analysis, it is worth writing three definitions, one sentence each. What counts as a sale (invoiced excluding VAT and net of credit notes, for instance). What an active customer is (at least one purchase in the last 12 months, for instance). And how duplicate customers are handled, those that appear under two codes or with the name spelt in different ways. Without these definitions, two people ask the same question and get different figures, and the meeting ends up debating where the number came from rather than the decision.
What options are there for answering without IT?
There are three common options, and each suits a different kind of question.
| Option | Works well when | Limitation |
|---|---|---|
| Excel with a pivot table | Someone is comfortable with spreadsheets and the question is a one-off | Depends on an up-to-date export and is repeated by hand every month |
| Business intelligence dashboard | Questions are stable and recurring | A new question needs someone to build the chart |
| Conversational data analysis | Questions change from week to week | Needs clear definitions and each answer to be checked |
Conversational data analysis means querying data by typing the question as you would ask a colleague, with no SQL and no report building. The tool turns the question into a query on the data and returns the answer. For how it compares with dashboards, see the article on conversational analytics and dashboards.
In EngiAnalytics, an analyst types, for example, “which customers bought less this quarter than in the same quarter last year?” and gets the answer in seconds, in the most suitable format. When the answer is a list, it arrives as a table, ready to sort. When it is a trend or a comparison, it arrives as a chart, and the chart type can be changed with one click.
Three details make a difference in customer analysis.
- Every answer explains itself. A sentence says what the query returns and, if that sentence quotes a number that is not in the data returned, the reader is warned.
- The query is visible. The SQL query behind the answer can be opened and copied, next to the table with the data. Someone who knows the data can confirm that the agreed definition of a sale was used.
- The result goes to whoever decides. Data exports as CSV, the chart as an image and the full report as a PDF.
It works on the data the organisation already has, in MySQL and PostgreSQL databases and in Excel and CSV files, with no migration or system replacement. Access profiles define, table by table, what each person can query, and a question outside that scope is refused without exposing restricted information.
How do you move from one-off questions to ongoing monitoring?
Some questions are asked once. Others, such as the share held by the largest customers or sales against the previous year, come back every week. Those should leave the conversation and move to a dashboard.
In EngiAnalytics, each person gathers their reports in a dashboard of their own, which they can share, and indicators show variation and trend, with data refreshed every time it is opened. Whoever creates an indicator can state whether going up is good or bad, so that the change is flagged as positive or negative. For choosing those figures, the article on which indicators a small business should track helps build the list.
The next step is not having to ask at all. The platform continuously monitors the data and flags what deserves attention as soon as it detects it. One example of a sales alert is “3 of the 10 largest clients reduced orders in the last 4 weeks.” There is more on this kind of alert in the article on automatic KPI alerts.
A practical example
Imagine an electrical supplies distributor with 40 staff and around 600 active customers. Until now, the Monday sales meeting started with a spreadsheet prepared by finance on Friday, showing the month's sales by customer. Any question outside that spreadsheet was left for the following week.
Management decides to change this in four steps.
- It writes down the six questions in the table above and the three definitions, of a sale, an active customer and a duplicate customer.
- It checks that the invoicing database holds customer, date, product, value and document type, and fixes the most obvious duplicate customers.
- In the meeting, questions are asked in plain language and answered there and then. When a figure is surprising, someone opens the query and checks the definition.
- The three questions that come up every week move to a shared dashboard, with the direction of each indicator set.
The aim is for the meeting to stop debating where each number came from and start discussing which customers to contact and with what offer. The IT team stops receiving extract requests and keeps the part that belongs to it, which is keeping the data reliable and access well defined.
To get started in your own organisation, pick three customer questions that currently take days to answer, write the definitions and confirm where the data is. If the answers change decisions, the rest of the list will follow.
Frequently asked questions
What is sales analysis by customer?
It is the study of what each customer buys, when they buy it and what they are worth over time. It helps decide where to focus the sales effort, which customers to win back and which customers the business depends on too heavily.
What is RFM analysis?
It is a way of ranking customers by the recency of their last purchase, how frequently they buy and the monetary value they spend. It shows who the best customers are and which ones are starting to drift away.
Do you need to know SQL to analyse sales by customer?
No. In a conversational data analysis tool, the question is typed in plain language and the tool generates the query. In EngiAnalytics, that query can be opened and copied by anyone who wants to confirm where each figure comes from.
What data do you need to get started?
Sales documents with customer, date, product, value and document type. Region, sales rep and line cost allow further analysis, but are not required at the start.
Does EngiAnalytics work with Excel files?
Yes. It works on the data the organisation already has, in MySQL and PostgreSQL databases and in Excel and CSV files, with no migration or system replacement.
How do you stop everyone seeing all the sales data?
With access profiles that define, table by table, what each person can query. In EngiAnalytics, a question outside that scope is refused immediately, without exposing restricted information.