Lost in the mapping sheet: when your systems can't track what you need
By the 1point Team
A business needed to tag and track its work by categories that mattered to management. The information simply was not available in its systems, so every month the data was downloaded into Excel, extra mapping columns were added and the file was cleaned by hand into the shape the business needed.
To run a business well, you need to see it your way. Which service lines are most profitable? Which customer types pay late? How does each region, contract or team perform? These are the questions that drive real decisions.
The information isn't in your systems
Most SME systems were not set up to capture these views. The accounting software has a chart of accounts, but not your service lines. The job app records the work, but not the cost category. Tags exist in some tools, but they are limited, optional and used differently by each person.
So the work moves to Excel
- Export invoices, costs and jobs from each system.
- Maintain a mapping sheet that links each customer to a segment, each item to a service line and each staff member to a team.
- Run lookups, then hunt down the rows that come back as #N/A.
- Reclassify manually the entries that were coded wrongly or not at all.
- Build the pivot tables and charts, then do it all again next month.
Why it becomes a problem
- The mapping goes out of date. Every new customer, product or staff member needs to be added, and often isn't.
- Unmatched rows quietly disappear, so the totals look right but aren't.
- Only one person understands the file, and the reporting stops when they are away.
- You cannot drill back from a number in the report to the transaction behind it.
- Insight arrives late, days or weeks after the month has closed.
How 1point approaches it
Instead of fixing the data after the fact, 1point tags it at the source.
- You define your tags once as business rules: service line, region, customer type, contract, team, or whatever matters to you.
- AI agents apply them automatically as each quote, job, invoice and cost is created.
- New customers and items are tagged as they appear. When the agent is unsure, it asks a person to confirm rather than guessing.
- Tags are shared across finance, operations, sales and HR, because everything runs on one platform.
For example, to see profit by service line, invoice revenue is tagged from the job type, and costs are tagged from the technician hours and materials used on that job. The report is ready the moment the transactions are, with no mapping sheet in between.
Three questions to ask
- Which of your reports depend on a mapping sheet?
- What happens when a new customer or product isn't on the list?
- Who maintains the file, and what happens when they are away?