Lesson 3.1: Auditing a customer's data landscape
Before you build, find out where the customer's data lives, who owns it and whether it can be used. Learn a one-week audit that finds the surprises early.
By the end of this lesson you will be able to run a one-week audit of a customer's data and write a map of what you found: which systems hold the data you need, who owns each one, how you can get at it, how trustworthy it is and how sensitive it is. You will learn seven questions to ask about every source, a three-test method for judging whether a source is usable and a scorecard that turns your findings into an effort estimate. You will see the audit applied to three customers and use a simulator to see how missing documentation and access multiply the work. This lesson opens Module 3, on customer data and systems. It builds on Module 2: you have a problem, a set of people and a scoped pilot, and now you need to know whether the data to run it exists and can be reached.
Almost every customer says the same thing at the start: "the data is all in our system." Sometimes it is. More often it is spread over six or nine places, some of which are not systems at all: a spreadsheet kept by one person, a file that arrives by email every night, a paper register, a messaging group. The customer is not hiding anything. People describe their data by the system they use every day, and they forget the sources that feed it, the places it is copied to and the manual fixes in between. The audit is how you find all of that before it finds you.
Why audit first
It is tempting to start building as soon as the pilot is approved, particularly when the customer is keen and the clock is running. The audit costs days, and those days are the cheapest of the project, for three reasons.
Data decides feasibility. A plan that needs a field the customer does not record, or records badly, cannot work however well you code. You want to learn that in week one, while the scope can still change.
Data decides the schedule. Getting access to data is often the slowest part of a pilot. Approvals, accounts and extracts take weeks, so the audit tells you what to start requesting immediately.
Data decides the quality of the result. A model, a dashboard or a reminder system is only as good as what it is fed. If you find the problems early, you can design around them and tell the customer honestly what to expect.
An audit is not a research project. It has a time limit, usually about a week, and it produces a short document. The point is to reduce surprises, not to remove all of them.
Seven questions for every source
For each place where relevant data lives, ask the same seven questions. Write the answers in a table, one row per source. A blank cell is a task.
1. What is it? The name of the system or file, and what it is used for. "Loan management system, used by operations to record disbursements and repayments."
2. What data does it hold? The main tables or sections and, for your pilot, the specific fields you need.
3. Who owns it? There are usually three different people. The business owner decides what the data means and who may use it. The system owner runs the technology. The steward knows the details, such as why a field is blank for older accounts. You need all three.
4. How can we get at it? A direct interface (an API), a database connection, a scheduled file, a manual export or a screen that someone copies from. Each has a different effort and risk.
5. How fresh is it? Real time, hourly, nightly, weekly, or whenever someone remembers to update it. The pilot's design depends on this.
6. How sensitive is it? Whether it holds personal, financial or health information, and which rules apply. Lesson 3.4 covers this in depth, but the audit must record it.
7. What do we know about quality? Known problems, gaps and history. Ask the steward: "What would surprise a new person looking at this?"
The seventh question gets the best answers. People who work with data every day know exactly where it is unreliable, and they will tell you if you ask in a way that makes it safe to do so.
Owner, system and steward
It is worth pausing on the three kinds of people in question three, because mixing them up is a common source of delay.
The business owner is the person accountable for the data from the business side. They answer "may we use this, and for what?" In a lender, this might be the head of collections for repayment data. The system owner is the technical team that runs the system. They answer "can you have access, in what form and by when?" This is often a central IT or data platform team. The steward is the person who knows the data day to day. They answer "what does this field really mean?" and are often an analyst or an operations lead, someone with no authority and a great deal of knowledge.
A request that goes to the wrong one of the three stalls. Ask the system owner whether the data may be used for a new purpose and you will be told to ask the business owner. Ask the business owner for a technical export and you will be sent to IT. Ask neither about a puzzling field and you will guess wrong. Learn who is who for each source in the first days.
Look beyond the systems
Most data audits find the official systems quickly and miss the unofficial ones. Ask directly about each of these.
Spreadsheets. Someone almost always keeps a spreadsheet that fills a gap in the system: a list of special cases, a mapping of codes, a tracker of exceptions. These files often hold exactly the information that makes the official data usable.
Files by email or shared drive. A nightly or weekly file that arrives from a partner, a bank or a regulator. Find out who sends it, in what format, and what happens when it is late.
Paper and manual registers. As the hospital example in Lesson 1.1 showed, a paper register may hold the data that matters, such as reserved beds.
Messaging groups. Operational decisions are sometimes made, and recorded, in a chat. These are rarely a data source you can use, but they tell you where the real process lives.
The person who "just knows". Some facts live in one head. If a single person's absence would stop the data from being understood, record that as a risk.
A useful question is "when the system is wrong, how do you fix it?" The answer usually reveals a manual correction step that is not recorded anywhere else.
Predict first: a customer tells you: "All the data you need is in our CRM." Before reading on, think of the first request you would make to check this, which would cost the customer very little and tell you the most.
The best first request is for a small, real sample: "Could you export a thousand recent records, with all the fields, and a list of what each field means?" A sample answers questions that a conversation cannot: whether the fields you need exist, how they are filled in, what the formats are and how messy the data is. It takes the customer an hour. It also shows how easy it is to get data out, which is itself a finding. If the export takes two weeks and three approvals, you now know your schedule.
A sample only helps if it is big enough to contain the problem. If one row in thirty has a blank in a key field, a sample of ten may show none and a sample of a thousand will show it many times. Set the share of rows with a problem and the size of your sample, and see how likely you are to spot it.
At the defaults, a problem affecting 3 per cent of rows is all but certain to appear in a thousand-row sample, with about thirty examples. In a sample of fifty it would be missed about one time in five.
Three tests for each source
When you have a profile of a source, judge it with three tests, in this order. A source must pass the first to be worth testing the second, and so on.
1. Does it exist? Does the data you need exist in this source at all, with the detail you need? A pilot about repayment timing needs the date of each payment, not only the balance. If the system keeps only the latest balance, the data you need does not exist here, however well it is stored.
2. Can we reach it? Can you get the data in a form you can use, safely, within the pilot's timeline? This involves the access route, the approvals, the volume and the freshness. A source that exists but cannot be reached for twelve weeks is useless to a ten-week pilot.
3. Can we trust it? Is it complete, consistent and correct enough for the purpose? Run simple checks on your sample: how many records are missing key fields, how many duplicates, how many impossible values. Lesson 3.3 covers quality in depth. The audit needs only a first impression.
A table of results is a good way to present this. Here is an illustrative one for a lender.
Source · Exists? · Reachable? · Trustworthy? · Main concernLoan management system · Yes · Nightly file, 2 weeks to approve · Mostly · Two date formats
Collections CRM · Partly (call notes only) · Manual export · Unknown · Notes are free text
Payment gateway · Yes · API, needs a security review · Yes · Review takes about 3 weeks
Language preference list · Partly (half the borrowers) · Spreadsheet from one analyst · Unknown · One person holds it
Regulator contact-hours rules · Yes · A PDF · Yes · Needs a person to interpret it
A table like this is the main product of the audit. It shows the customer where the project is easy, where it is hard and where a decision is needed.
A one-week audit plan
Here is a realistic shape for the week. The days are illustrative, and a smaller customer may need less.
Day 1: the list. With the sponsor and the system owners, list every source that touches the problem. Ask the seven questions, and ask "where else does this data go, and where does it come from?" Draw a rough picture of the flows, with boxes for sources and arrows for moves.
Day 2: samples. Request a sample from each important source, and start any access requests that take a long time. Do this on day two so that the slow approvals run while you work.
Day 3: profile. Look at the samples. Count records, check the fields, look for blanks, duplicates and strange values. Write down surprises.
Day 4: stewards. Sit with the people who know the data. Show them what you found and ask them to explain it. This is where "that field changed meaning in 2023" comes out.
Day 5: the map. Write the data map: the table of sources and tests, the flow picture, the risks and the list of decisions needed. Review it with the sponsor and the system owners, and update the one-page plan.
Worked example 1: the lender's overdue accounts
The pilot needs a nightly list of overdue accounts with due date, amount, language and payment history. The first meeting suggested one source, the loan system. The audit finds five.
The loan system holds accounts and balances. Its payment history is in a different table that the nightly file does not include, which means the data on past payments does not reach the pilot without a change to the export. The collections CRM holds the call notes but not the call outcomes in a usable form. The payment gateway holds recent payments faster than the loan system, but needs a security review. The language preference sits in a spreadsheet kept by one analyst, covering about half the borrowers. The regulator's rules on contact hours are in a PDF.
By the end of the week the picture has changed. The pilot can run on the nightly file if the export is extended to include payment history, which the system owner estimates at two weeks. Language is the weakest link: for half the borrowers the pilot will use a default. The engineer tells the sponsor, who decides to run the pilot in the first language for the borrowers with a known preference and to treat the others as a separate group. The pilot is smaller than first imagined and is now feasible.
Worked example 2: the hospital group's beds
The hospital group's bed screen needs bed status from six hospitals. The audit finds six different systems, each with a different definition of "free", as Lesson 1.1 described. It also finds a paper register at one hospital for beds reserved by the emergency department.
The three tests give a clear picture. The data exists in five hospitals and not in the sixth, where beds are tracked on paper. It is reachable in three hospitals through a database connection and in two through a nightly file, with a different owner for each. It is trustworthy at two hospitals, uncertain at three and unknown at the paper-based one. The system owner for the largest hospital will not allow direct database access without a security review.
The engineer proposes a sequence: start with the two trusted hospitals, then add the three uncertain ones as definitions are agreed, and leave the paper-based hospital for the second phase with a simple daily form. This is the kind of honest sequencing the audit makes possible. Without it, the project would have promised six hospitals and delivered two late.
Counting the hospitals that pass each test shows where the project is exposed. The data exists almost everywhere and can be reached almost everywhere, but only a third of the sources are fully trustworthy.
Worked example 3: the retailer's forecast
The retail chain wants its forecast to feed the weekly order suggestion. The audit lists the point-of-sale system, the ordering system, the forecasting team's output and a file of promotions.
The point-of-sale data is complete and fresh. The ordering system accepts only manual entry or a weekly file in a fixed format. The forecast is in a report that is emailed as a spreadsheet each Monday. The promotions file is maintained by the marketing team and is updated "when they remember". The first three exist and are reachable, and the fourth is the weak link: if promotions are missing, the forecast will be wrong in exactly the weeks when it matters most.
The audit also reveals the central finding: the forecast arrives on Monday at 11 a.m., but orders are placed by Monday at 9 a.m. The data exists, and it arrives too late. The fix is a schedule change, not a technical build, and it needs the person who owns the ordering process. The audit found a problem that no amount of modelling would have solved.
Turning the audit into an effort estimate
The scorecard is more useful when it leads to numbers. A source's integration effort depends on a few things. A baseline effort for any source is a few days: connect, read, check, load. Then add effort for three kinds of missing preparation.
Access. If there is no ready export or interface, someone has to build or request one.
Documentation. If nobody has written down what the fields mean, you have to find out by asking people and testing guesses.
Ownership. If it is unclear who owns the source, each question takes longer, and approvals stall.
The simulator below turns this into arithmetic. Set the number of sources, the share with a ready export or interface, the share that are documented, the share with a known owner and the baseline days to integrate one well-prepared source. It estimates the total effort in days and weeks, and compares it with the same project if every source were well prepared.
The multipliers are illustrative assumptions, chosen to show the shape: missing access hurts most, then missing documentation, then unclear ownership. They are not measured figures. Use your own once you have a few real projects to calibrate them against.
Try three experiments. First, raise the number of sources from six to ten and see the effort grow faster than the count, because the unprepared ones multiply. Second, set the share with a ready export to 100 per cent and see how much of the total came from access. Third, set all three shares to 100 and see the cost of a perfectly prepared landscape.
At the defaults, six sources with half having a ready export, 40 per cent documented and 70 per cent with a known owner come to about 59 days, almost twelve weeks for one engineer, against 30 days if every source had been well prepared. Nearly half of the effort comes from missing preparation. That is the figure to put in front of a sponsor who asks why the data work takes so long.
What to do with the findings
The audit is useful only if it changes something. Bring four things to the review with the customer.
The map. The table of sources, owners and tests, with the flow picture.
The decisions. Which sources are in the pilot and which wait, who must approve access and what must change, such as the export that needs extending.
The risks. Which findings threaten the pilot's metric, for example a field that is missing for half the borrowers.
The updated plan. The one-page plan from Lesson 2.4, revised with the scope, dates and data requests that the audit changed.
Do this promptly. A customer who sees the findings within a week treats the audit as help. One who hears the same findings in week six treats them as excuses.
Common mistakes
Trusting "it is all in the system". Ask for a sample and a list of sources, and look for the spreadsheets.
Auditing only the official systems. Ask where data goes and how errors are fixed. The unofficial sources often decide whether the data is usable.
Mixing up the three owners. Ask the business owner about purpose, the system owner about access and the steward about meaning.
Waiting to request access. Start the slow approvals on day two, not when the code is ready.
Skipping the sample. A thousand real rows teach more than a meeting.
Keeping findings private. Share the map and the decisions needed quickly, and update the plan.
Chasing perfection. The audit is a week long. It reduces surprises and does not remove them all.
Key takeaways
Customers describe their data by the system they use, and the real landscape is usually several times larger than the first description.
Ask seven questions of every source: what it is, what it holds, who owns it, how to reach it, how fresh it is, how sensitive it is and what is known about its quality.
Separate the business owner, the system owner and the steward, and ask each the right question.
Look at spreadsheets, files, paper registers and people's heads as well as systems.
Judge each source with three tests in order: does it exist, can we reach it and can we trust it.
Request a sample on day two and start slow approvals at once. Turn the findings into a map, a list of decisions and an updated plan.
Check yourself
1. Name the seven questions to ask of every data source.
What it is, what data it holds, who owns it, how we can get at it, how fresh it is, how sensitive it is and what is known about its quality.
2. What are the three kinds of owner, and who answers which question?
The business owner answers whether the data may be used and for what. The system owner answers whether access can be given and how. The steward explains what the fields really mean.
3. Why is a sample of a thousand real records often the best first request?
It shows whether the fields exist, how they are filled in and how messy the data is. It also shows how easy it is to get data out, which is itself a finding.
4. Name the three tests in order, and give an example of a source that fails the first.
Does it exist, can we reach it, can we trust it. A system that keeps only the latest balance fails the first test for a pilot that needs each payment's date.
5. With 8 sources, 5 days per well-prepared source, all sources having a ready export, all documented and all with known owners, what is the effort?
8 × 5 = 40 days, with no extra, because nothing is missing.
Try this
This exercise takes about forty minutes and builds a data map for a problem you know. Use your own work, or a task you did in the earlier modules.
1. Write the question or decision that needs data, in one line.
2. List every source that touches it, including spreadsheets, emails, paper and people. Aim for at least five. If you find fewer, ask someone else.
3. For each source, answer the seven questions in one line each. Mark blanks.
4. Judge each source on the three tests with a yes, partly or no, and one line of reason.
5. Put the sources in a table like the lender's. Circle the one that is weakest.
6. Use the simulator with your own numbers and write the estimated effort.
7. List three decisions or approvals you would start this week, and who owns each.
A good result is a one-page data map with a source table, a flow sketch, a list of risks and three first requests. It is the data section of the plan from Lesson 2.4, and it is what turns "the data is all in our system" into something you can work with.
Sources
The three customers, the source tables, the figures and the simulator's multipliers are the author's illustrations for teaching, not data from real companies. The seven questions and the three tests are a practical method written for this lesson, not an industry standard.
Tags: Forward Deployed Engineer Data audit Customer data Integration Beginner