Skip to content
Get a free demo
Guide

How to organize scattered business data

Find out where everything lives, choose one home for each subject, and connect them with IDs. You don’t need new software to begin.

Map what you have

Spend one session listing every place business information lives. Include what feels too small to count: a notebook by the phone, a whiteboard, a text thread with a supplier, one person’s personal Excel file.

An example data map, with invented entries
InformationWhere it livesWho updates itTrouble
CustomersBilling software, an Excel list, phonesOffice, sales staffThree versions of each phone number
JobsWhiteboard, shared spreadsheetOfficeWhiteboard gets wiped each week
InvoicesBilling softwareOfficeNone; this one works
PricesA PDF and in people’s headsOwnerDifferent staff quote differently

Ask the people who do the work. They are the ones who know about the side file that actually runs Tuesdays.

Choose one home for each subject

For every row on the map, decide where the true version lives. People often call this the source of truth. Everything else becomes a copy that gets refreshed from there, or it gets retired.

Choose by who needs to change the data and what it already connects to. If invoices already work in your accounting software, keep them there. Moving something that works rarely pays off, so spend your effort on the rows marked with trouble.

Give things IDs and shared names

Scattered data can’t be joined up without a reliable way to say that two records are the same thing. Names don’t do that, because a customer can be J. Smith Plumbing in one file and Smith Plumbing LLC in another.

  • Assign IDs. Give each customer, product and job a short code that never changes and never gets reused.
  • Agree on a short word list. Decide on one spelling for each status, region and category, and put it in dropdowns where you can.
  • Agree on formats. Write dates as 2025-03-04 in anything you export, and phone numbers in one consistent form.

Clean as you bring things together

Start with your most valuable list, which is usually customers. Merge the copies into one, and keep a column showing which file each row came from.

  1. Export each copy to a spreadsheet, and tidy it with our file cleaner or by hand.
  2. Stack them into one sheet. Our guide to merging Excel files shows how.
  3. Sort by phone or e-mail to bring likely matches together, and decide on each pair by eye.
  4. Assign the IDs once the list is settled.
  5. Announce the new home, and make the old copies read-only so nobody keeps updating them by habit.

Name and file things so they can be found

Documents scatter just as quickly as data. A simple shared folder structure, limited to about two levels, beats anything elaborate that nobody follows. Name files so they sort properly by starting with a date:

2025-03-04 Harbor Lane invoice 1042.pdf

Store them in one shared drive, and keep nothing important only on one person’s desktop or phone.

Keep it from scattering again

Data scatters because the official place is more awkward than a quick note. So make the right place the easy one: a short form on a phone will beat a spreadsheet that takes a minute to open.

Give each subject a named owner, and book a short check every quarter for new side files. A new one is useful information, because it tells you what the main system doesn’t do yet. When those requests add up, it may be time to look at an app.

Want everything in one place?

We build a single app around your customers, jobs and paperwork, in your own terms. Tell us where your data lives today.

Get a free demo