How to structure your company's data

Six sources, three different answers

In a management meeting someone asks how many orders shipped late last month. Logistics says 62. Customer service says 87. The sales manager has 54 written down. None of them is lying: each area counts what it sees from its own system, with its own criteria and its own calendar.

This is the classic symptom of a company with plenty of data and no structure. The information exists, but there is no agreement about what it means. Structuring data is, above all, settling that agreement.

Structure does not mean putting everything in one place

There is a common misunderstanding: assuming the problem is solved by moving everything into one file or one system. It does not work that way. A company can run six different systems and be perfectly structured, and it can have everything in a single system and still not know how many orders shipped on time.

What is actually needed is:

• knowing where each piece of data lives and who maintains it,
• having one single definition for every metric,
• having a model where those definitions are always calculated the same way,
• and a handful of maintenance rules so none of this decays within six months.

Step 1. The source inventory

It is the least glamorous task and the one that prevents the most trouble. For each source, record six things: name, system it lives in, owner, refresh frequency, output format and which metrics it feeds.

A typical inventory in an 80-person company has between 8 and 15 sources. When I run one, three things almost always happen:

1. Two or three sources appear that nobody had mentioned, usually files one single person has maintained for years.
2. Duplicated data turns up in two systems, with values that no longer match.
3. Some critical source is found to have no assigned owner.

Those three findings alone justify the two weeks the inventory takes.

Step 2. One definition per metric

This is where the project is won or lost. Take the opening example: "order shipped on time". Before counting anything, you need answers to:

• Is it measured by warehouse dispatch date or by customer delivery date?
• Do weekends and public holidays count towards the lead time?
• If an order ships in two shipments, is it complete when the first one leaves or the last one?
• Do cancelled orders belong in the denominator?

Four questions, one afternoon of discussion, and the three numbers from the management meeting become one. The outcome goes into a metrics dictionary like this one:

• On-time service — orders delivered within the committed window over orders delivered, counted in working days. Source: transport. Owner: operations director.
• Gross margin — sales net of returns minus the purchase cost of the item. Sources: sales and purchasing. Owner: finance director.
• Active customer — customer with at least one invoiced order in the last 12 months. Source: invoicing. Owner: sales director.
• Stockout — item with zero available stock and demand recorded the same day. Source: warehouse. Owner: operations director.

Three rules for the dictionary: every metric has a single owner, every definition is written in one sentence someone outside the department can understand, and every change is dated. If a metric needs two definitions because finance and operations use it differently, create two metrics with different names. Never one name with two meanings.

Step 3. A model people can follow

With sources inventoried and metrics defined, you need a place where all of it is calculated consistently. Four decisions determine whether the model will survive growth.

Granularity. The level of detail you keep: the order line, the order, or the daily summary. Keeping the finest detail you can realistically maintain lets you answer future questions without rebuilding anything.

Common keys. The same customer cannot become three customers because the name is spelled three ways. You need one identifier per customer, per product and per site, plus a table holding the equivalences between systems. This master-data work is dull, and it is exactly what makes the numbers reconcile.

Calendar. Your own calendar with working days, holidays, weeks and fiscal periods stops every report from inventing its own interpretation of "last month".

Hierarchies. Product, family, category. Store, region, country. Defining them once lets you move from summary to detail without building a new report each time.

Step 4. Minimum viable governance

Governance has a bad reputation because people associate it with committees and documents nobody reads. In a mid-sized company, four rules are enough:

1. Every metric has a named owner who approves any change to its definition.
2. Changes are documented with a date and a reason. Two lines will do.
3. A quarterly half-hour review: which metrics are no longer used, which are missing, which definitions have gone stale.
4. Permissions by role, not by person. When someone changes role, their access follows without extra work.

A thirty-day plan

Days 1 to 10. Inventory. List of sources, owners and frequencies. Mark the three most critical to the business.

Days 11 to 20. Definitions. Pick the eight or ten metrics that show up in management meetings and close their definitions. No more than ten: aim for thirty and you will close none.

Days 21 to 30. First test. Calculate those metrics for a period that is already closed and compare them with what each source system says. The gaps that appear are the exact map of the work left to do.

After a month you will not have finished the structure, but you will have something more valuable: knowing precisely where the problems are and what it costs to fix them.

Signs the structure is not holding

• Meetings start by arguing about where each number came from.
• There is one file that "is the right one", everybody knows where it lives, and nobody knows who maintains it.
• Every area has its own count of active customers.
• Nobody can say with confidence when the data was last refreshed.
• When one specific person leaves, certain reports stop being produced.

If you recognize three or more, the problem is no longer about files: it is about structure.

What you do not need to do

You do not need to migrate everything. You do not need a twelve-month program. And above all you do not need to choose how it will be visualized before knowing which questions have to be answered. The right order is questions, definitions, model and only then screens.

If you would like a review of your sources and your metrics dictionary, write to me. The initial diagnostic is free and I deliver it in under a week.