John Serra – Backend

Tag: Data Management

  • Why Your Business Numbers Don’t Match Across Systems

    Your payment processor, your bank, and your accounting software each record a different moment in the life of a sale. The processor records the charge when the customer pays. The bank records a deposit days later, after fees and refunds come out and several sales are batched together. Your accounting software records revenue according to your accounting method, which may be when the revenue is earned rather than when the cash arrives. In most cases, three different numbers mean three different measurements, and a short monthly reconciliation shows how they connect.

    I’ve seen this plenty in operating businesses. An owner compares last month’s sales across two or three systems, gets different totals, and starts wondering whether the bookkeeper made a mistake or whether money is missing. Usually the numbers are fine. Nobody has written down how they relate to each other.

    Why Does Each System Show a Different Number?

    Each system sits at a different step of the same transaction. Follow one sale through all of them, and the differences stop looking mysterious.

    A Sample Scenario: A client pays a $1,000 invoice by card on a Friday.

    1. Accounting software. The invoice was entered when the work was billed, perhaps the previous month. Entering the invoice is not what created the revenue. Under accrual accounting, the $1,000 counted as revenue when it was earned, typically when the work was completed, under the business’s accounting policy. That can fall in a different month from both the invoice and the payment.
    2. Payment processor. The charge is approved on Friday and shows $1,000 in gross sales.
    3. Processor balance. The processor takes its fee. At Stripe’s standard US price for domestic cards, 2.9% plus 30 cents per successful charge, that is $29.30, leaving $970.70 in the processor balance.
    4. Bank account. The money is not available the moment the card is approved. On Stripe’s standard two-business-day timing for US accounts, Friday’s sale becomes available for payout the following Tuesday. When it actually lands in the bank is a separate question, set by your payout schedule and your bank’s own processing, and it arrives batched with other sales in a single payout.
    5. Back in accounting. Someone matches that combined deposit to the invoices it paid and records the $29.30 as a processing expense.

    The same $1,000 now appears as revenue in one month, a $1,000 charge on a Friday, and part of a larger bank deposit some days later. All three are correct.

    The Business Data You Already Have describes what each of these records can tell you on its own. This article covers how to connect them.

    What Are the Four Reasons the Numbers Disagree?

    1. Timing

    Cash lags sales. Funds are not available for payout until the processor’s settlement timing has run; payouts then follow whatever schedule the account is on, and weekend or holiday payouts move to the next business day. Stripe says a new account’s first payout typically takes 7 to 14 days, and later payouts follow a schedule the business can set to daily, weekly, or monthly.

    The lag matters most at month end. Sales on the last two days of March may be in the processor’s March report but in the bank’s April statement. Sales from the end of February land in March deposits. The two months never contain exactly the same transactions.

    2. Fees, Refunds, and Disputes

    Processors usually pay out the net amount. Fees, refunds, and chargebacks come out of the balance before the money moves, so the deposit is smaller than the sales that produced it.

    The problem gets worse when the books record only the deposit. If $970.70 is booked as revenue, sales look lower than they were and the $29.30 processing cost disappears from the expense lines. The usual setup is to record the full sale and the fee separately. Confirm how your bookkeeper handles it.

    3. Cash Versus Accrual

    Under cash accounting, income counts when it is actually or constructively received. Under accrual accounting, it generally counts when it is earned, typically when the work is done or the goods are delivered, regardless of when the customer pays. A business on accrual accounting can show strong March revenue with little March cash, because the invoices are still open.

    Multi-month contracts, deposits, retainers, and milestone billing make this more complicated. Revenue recognition rules for those arrangements are a question for your CPA. Confirm your policy with them before you decide which revenue number a dashboard should show.

    4. Different Definitions

    The same word can mean different things to different teams. Take a signed two-year service contract. Sales may report the whole contract value as a win. Finance may record most of it as deferred revenue, to be recognized month by month. Operations may call the remaining work backlog. Each number is legitimate. They answer different questions, and a report that uses one without saying which will contradict a report that uses another.

    What Does a Monthly Reconciliation Look Like?

    A service business reviews March and finds three totals:

    SystemMarch totalWhat it measures
    Accounting software$52,700.00Revenue recognized in March: $48,200 paid by card plus $4,500 invoiced and still unpaid
    Payment processor$48,200.00Gross card charges in March
    Bank account$45,441.80Processor payouts that reached the bank in March

    The gap between accounting and the processor is the $4,500 in open invoices. It will turn into cash when those clients pay.

    The gap between the processor and the bank takes a few more lines:

    Bridge lineAmount
    Gross card charges$48,200.00
    Less refunds−$600.00
    Less processing fees−$1,697.80
    Net processor activity$45,902.20
    Plus balance not yet paid out on March 1 (late-February sales)+$2,950.00
    Less balance not yet paid out on March 31 (late-March sales)−$3,410.40
    Other balance activity (disputes, reserves, adjustments)$0.00
    Expected bank deposits$45,441.80

    The expected figure matches the bank total, so the month ties out. If the bank had shown $44,900, the unexplained $541.80 would be the thing to investigate.

    The bridge works because it follows the processor’s balance: what was waiting to be paid out at the start, plus the month’s net activity, less what was still waiting at the end, equals what was paid out. This example is deliberately simplified, which is why the “other balance activity” line is zero. A real month can also carry disputes and dispute fees, reserves or holds on the balance, failed or reversed payouts, taxes withheld, and account adjustments, and any of those changes what reaches the bank. Processors such as Stripe provide balance and payout reports that show these items, and each one earns its own line in the bridge.

    How Do You Stop Arguing About Which Number Is Right?

    Decide in advance which system answers which question, and write it down.

    QuestionSystem of record
    How much cash do we have?Bank
    How much revenue did we earn?Accounting software, under the policy your CPA confirmed
    How much did customers pay us, and how?Payment processor or POS
    How much work have we sold?CRM, booking system, or sales tracker

    Once each metric has a home, a dashboard can label the source beside every number: “Revenue (accounting, accrual)” or “Cash (bank, as of the 5th).” People stop comparing numbers that were never meant to match.

    This matters beyond the finance office. When two managers bring different revenue figures to the same meeting, the meeting turns into an argument about the data. After a few of those, people stop trusting the dashboard and go back to their own spreadsheets. Why Nobody Looks at Your Dashboard covers that problem in more detail.

    How Do You Reconcile the Systems Each Month?

    Step 1: Assign a System of Record to Each Metric

    Use the table above as a starting point. Keep the list short and share it with anyone who builds or reads reports.

    Step 2: Record Sales and Fees Separately

    Ask your bookkeeper whether card sales are recorded at the gross amount with fees, refunds, and chargebacks in their own accounts. If the books record only net deposits, you cannot see what processing costs you, and revenue is unlikely to tie to the processor’s report.

    Step 3: Reconcile on a Fixed Schedule

    Pick a day each month, after the books close, to match processor payouts to bank deposits. Use the processor’s payout report, which lists which transactions went into each deposit. Reconcile the same way every month so differences are easy to spot.

    Step 4: Keep a Simple Bridge

    Build the bridge from the March example in a spreadsheet: gross sales, refunds, fees, any other balance activity, the unpaid balance at the start and end of the month, and the bank deposits. Anything left over after those lines is what needs investigating. Once the monthly numbers tie out, they can feed a scorecard; How to Build a Monthly KPI Scorecard shows how to set one up.

    Is It a Timing Difference or a Real Problem?

    Work through these checks before assuming anything is wrong:

    • Does the gap match the balance waiting to be paid out? Compare it with the processor’s pending or in-transit amount at month-end.
    • Does the gap match fees? Divide last month’s total fees by gross charges to get your own fee rate, then see whether the difference is close to that share of sales.
    • Are refunds or disputes involved? Check the processor for refunds, chargebacks, and any reserve or hold on your balance.
    • Are both reports covering the same dates? Confirm the start and end dates, and whether each system uses the same time zone for its cutoff.
    • Is anything counted in one report and not the other? Look for open invoices, cash or check payments, sales tax, tips, or deposits for future work.

    If a difference remains after these checks, it deserves a closer look. Duplicate entries, a payout that never arrived, a sale recorded in the wrong period, and a missing connection between systems are all possible causes. Bring the bridge to your bookkeeper or accountant. It shows exactly how much is unexplained, which makes the conversation much shorter.

    Frequently Asked Questions

    Why doesn’t my Stripe total match my bank deposit?
    Stripe pays out your balance after fees and refunds come out, and it groups transactions into payouts on a schedule. A single deposit usually covers several days of sales, and the amount is net of costs.

    Should my systems match every day?
    No. Daily totals will almost always differ because of settlement timing. Monthly totals should tie out once you account for fees, refunds, and the balance still waiting to be paid out.

    Do I need an accountant to do this?
    You can build and review the bridge yourself. Questions about how revenue should be recognized, especially for contracts, deposits, or milestone billing, belong with your CPA.

    Can software do the reconciliation for me?
    Accounting tools such as QuickBooks and Xero can import bank transactions and suggest matches. Someone still needs to review exceptions and decide which system answers which question.

    Where to Start

    List every system that records money coming in, who has access, and what each one exports. The Small Business Data Audit Checklist walks through that inventory. With the list in hand, pick last month, pull the three totals, and build the bridge once. After the first month, the process gets much faster.

  • The Business Data You Already Have (No New Tools)

    Your business probably already generates records in five places: accounting software, payment or POS systems, banking, scheduling tools, and Google Business Profile. Depending on the platform and permissions, those records may reveal unpaid invoices, customer concentration, transaction patterns, cash timing, booked capacity, and local-search demand. You can test many in a spreadsheet without buying analytics software, although not every system provides a clean spreadsheet file or every field required for every metric.

    If you run a small business, you have probably been told that getting value from data starts with new software: a dashboard subscription, a data warehouse, or an integration service. For most businesses with two to 25 employees, that is the wrong starting point.

    The records already exist. Each time you send an invoice, run a card, clear a deposit, book an appointment, or get a call from your Google listing, a system you already use stores a date, an amount, and often a customer or status. The first job is not buying a tool. It is learning what those records contain, how to get them out, and which questions they can honestly answer.

    What Business Data Do I Already Have Without Buying New Tools?

    Your business already generates or controls access to financial, sales, scheduling, and customer-demand records. What you can learn from them depends on the fields, history, permissions, and export options each source provides.

    Business data usually shows up in one of three forms:

    1. Dashboard summaries: The charts and totals on your software's home screen. They are useful for a quick check, but the numbers are already added up, so you cannot see which invoices, days, or customers drove them.
    2. Record-level reports and exports: Files where each row is one transaction, invoice, appointment, or interaction. This is usually the best starting point, because you can sort, filter, and total the rows yourself. They are often downloaded as a CSV (a plain-text file of rows and columns that any spreadsheet opens) or an Excel file.
    3. Portable archives: Backup or transfer formats such as Google Calendar's .ics files. They preserve the records, but they open as raw text in a spreadsheet and need conversion before analysis.

    The table below covers the five sources most small businesses already use.

    The Existing Data Inventory Matrix

    Source Category Example Records Three Questions It May Answer Example Export Path & Format Important Limitation
    Accounting and invoicing Invoices, payments received, customer balances, vendor bills, expenses 1. Who owes us money, and how overdue is it?
    2. How much of our sales come from our largest customers?
    3. Which vendor costs have changed over the past year?    		 QuickBooks Online: Reports → Standard reports → open a report (e.g., A/R Aging Summary or Sales by Customer Detail) → Export/Print → Export as CSV or Excel Report availability and customization vary by subscription and interface version.
    Payment processor or POS Transactions, items sold, refunds, fees, timestamps 1. What is our average transaction value by day of the week?
    2. Which hours bring in the most sales?
    3. How much do refunds and processing fees take each month?    		 Stripe: Reporting → Financial reports → Balance summary → Download (CSV)
    Square: Reports → select report → export icon (CSV)    		 Some Square reports cannot be exported. Repeat-customer analysis requires a stable customer identifier (an ID that stays the same across visits).
    Online banking Deposits, withdrawals, descriptions, balances 1. When does cash actually arrive?
    2. Which charges recur every month?
    3. Do deposits line up with processor payouts?    		 Chase Connect (example only): Account Activity → Download Options → CSV, PDF, or Excel Paths, formats, and history limits vary by bank and account. Bank activity shows cash movement, not revenue or profit.
    Scheduling or booking Appointment start and end, creation time, invitee status, service type 1. How far ahead do customers book?
    2. How many hours are booked each week?
    3. Where do cancellations cluster?    		 Google Calendar: Settings → Import & export → Export (ZIP file of .ics files) Not spreadsheet-ready, and permissions or an administrator may restrict export. No-show, capacity, and channel analysis require consistently recorded statuses, defined available hours, and a lead-source field.
    Google Business Profile Searches, views, calls, website clicks, direction requests 1. How are people finding us?
    2. Which interactions are rising or falling?
    3. Which periods show stronger local demand?    		 Business Profile Manager: select profile(s) → Actions → Insights → select timeframe → Download Report (spreadsheet) Interactions are not confirmed leads, sales, or revenue. Available metrics vary by business.

    Each source describes a different step in the same cycle: people find you, book time, pay, get recorded in your books, and turn into cash in the bank. Because each system measures a different step, evaluate each one on its own terms before expecting them to agree.

    What Can Accounting, Payment, and Bank Records Tell Me?

    Accounting, payment, and bank records show what was invoiced, what was collected, what moved through your accounts, and when those events occurred. They all deal in dollars, but they answer different questions and should not be treated as interchangeable versions of revenue.

    Accounting and Invoicing Records

    Your accounting software is the system of record for what you billed and what you owe. Beyond the profit-and-loss statement, it supports three useful analyses:

    1. Customer concentration: Export Sales by Customer Detail for the last twelve months, total sales by customer, and calculate each customer's share. There is no universal danger threshold. The real question is whether losing your largest one or two accounts would affect payroll or debt payments.
    2. Unpaid invoices and payment timing: The A/R Aging Summary groups open balances by how overdue they are. If invoice dates and payment dates are both recorded reliably, you can also calculate average days to pay. When that number moves from 24 days to 41, cash tightens even while sales look healthy.
    3. Vendor cost changes: Compare the same vendor and expense category across matching periods, such as the first quarter of this year against the first quarter of last year. Before calling it a price increase, check quantity: higher spending on materials or tooling may reflect more jobs or more scrap, not higher prices.

    Limitation: Report availability and customization vary by subscription and interface version. Confirm the report you need exists on your plan before building a routine around it.

    Payment Processor and POS Records

    Payment processors and point-of-sale systems such as Stripe and Square may record each sale with an amount and a timestamp. When those fields are available, these records are a useful source for patterns by day and hour:

    1. Average transaction value by day and hour: This requires record-level amounts and timestamps. In restaurants and other hourly businesses, it shows whether your busiest hours are also your highest-value hours, or only your busiest.
    2. Refund and fee burden: Define the calculation before running it. Fee rate is total processing fees divided by gross charges for the same month. Refund rate is total refunds divided by gross charges for the same month. A changing fee rate may reflect processor pricing, payment method, or transaction mix. A rising refund rate is a reason to investigate products, fulfillment, or service, not proof of a particular cause.
    3. Repeat purchase interval: If checkout captures a stable customer identifier, such as an email address, customer record, or member ID, you can calculate the median number of days between a customer's first and second purchase.

    Limitation: Anonymous cash and guest transactions cannot support repeat-customer analysis. If your checkout does not collect an identifier, do not try to infer return visits from card types or amounts.

    Online Banking Records

    Your bank records the cash that actually posted to your accounts:

    1. Deposit timing: Comparing deposits with processor payout dates shows the lag between a sale and usable cash, including weekend and holiday delays. That helps you time payroll and vendor payments.
    2. Recurring outflows: Sorting withdrawals by description surfaces subscriptions, leases, and recurring fees. Treat the result as a follow-up list: contracts, invoices, or software administration records are needed to confirm whether a charge is still necessary or duplicated.

    Limitation: Bank activity shows cash movement and nothing more. A deposit may be a loan disbursement, a customer deposit, or sales tax you collected. A withdrawal may be a loan payment or an owner draw rather than an expense. Download formats and history limits vary by bank.

    Why These Three Systems Rarely Match

    Your processor's monthly sales, your accounting revenue, and your bank deposits may not agree, and that does not necessarily mean your books are broken:

    • A processor report may show gross charges, refunds, fees, net activity, or payouts, depending on the report selected.
    • An accounting report may show invoiced or recognized revenue, depending on the report, configuration, and accounting method.
    • A bank export shows posted deposits, whose amounts and dates depend on payout settings, fees, refunds, batching, and settlement timing.

    Forcing these numbers to match in one spreadsheet without documented reconciliation rules creates more confusion than insight. For now, let each source answer its own question: accounting shows what was billed and owed, the processor shows how customers paid, and the bank shows when cash arrived.

    What Can Scheduling and Local-Search Records Tell Me?

    Scheduling records may show when work is booked and how far ahead customers reserve; Google Business Profile may show how people find and interact with your business. Both are demand signals, not proof of completed sales.

    Scheduling and Booking Records

    If you use a booking tool or shared calendar, your appointment records may support three analyses:

    • Booking lead time: The time between when a booking was created and when the appointment starts. This requires both fields. If lead time shrinks from three weeks to four days, it may be an early sign of softening demand before revenue reflects it.
    • Booked hours: The total duration of consistently categorized service appointments. This is booked time, not utilization. Utilization requires first defining your available hours, such as staffed hours minus breaks and administrative time.
    • Cancellations and no-shows: These are available only when cancellation and attendance statuses are recorded consistently. Once they are, group them by weekday, service type, or staff member. Analyzing cancellations by marketing channel also requires a lead-source field.

    Limitation: Statuses need to be recorded when they happen. If staff update appointments in a batch at the end of the day, timestamps cluster and distort the analysis. Google Calendar exports only .ics archives, so leave it out of your first spreadsheet test. Many booking tools offer appointment exports; check your tool's help documentation for the current path and format.

    Google Business Profile

    Google Business Profile shows how people find and interact with your listing, without installing any tracking code:

    1. Searches: How often your profile appeared in search results. The Performance view may also list the search terms that surfaced your profile; check whether your downloaded report includes them.
    2. Views: How many times people viewed your profile over the selected period.
    3. Interactions: Counts of calls, website clicks, direction requests, and other actions available for your business type.

    Limitation: A tap on the call button does not prove a conversation happened, and a direction request does not confirm a visit. Compare consistent periods, such as month over month or the same quarter last year, and treat the results as demand trends rather than revenue inputs.

    How Do I Test One Export in a Spreadsheet?

    Choose one business question and one source, download a detailed CSV or Excel report, preserve the untouched file, and test whether its rows, dates, identifiers, and amounts can support the question.

    The most common mistake is trying to build a master dashboard on day one: six months of bank records, four QuickBooks reports, and a Stripe file pasted into one workbook. The result is broken lookups, mismatched dates, and totals that disagree. Start with one source instead.

    This is a manual, periodic process. You download a fresh file weekly or monthly; nothing updates on its own.

    The 30-Minute Single-Source Test

    Use whichever spreadsheet program you already have: Google Sheets, Microsoft Excel, or LibreOffice.

    Step 1: Define One Question (5 Minutes)

    Write down one question with a practical business consequence before opening any software. For example:

    • Invoicing: "Which five customers accounted for the largest share of invoiced sales over the last twelve months?"
    • Payments: "What was our average transaction value on Saturdays compared with Tuesdays last month?"
    • Banking: "How much did we pay in recurring subscriptions over the last 90 days?"

    Name the period and define your terms. For example, does "sales" include refunds and sales tax?

    Step 2: Export and Preserve the Raw File (5 Minutes)

    Use the platform's documented report or export menu, and choose CSV or Excel over PDF when both are available. Then:

    1. Save the file in a dedicated folder, such as Data_Exports/2026-Q3/.
    2. Rename it with the export date, source, and report, such as 2026-09-12_QBO_SalesByCustomerDetail_RAW.csv.
    3. Record the source, report name, filters, date range, and export date in a short note saved beside the file.
    4. Open a copy and save it as a workbook, such as ..._WORKING.xlsx or a Google Sheet. A CSV file cannot store pivot tables or multiple tabs.

    Never edit the raw file. An untouched original lets you retrace your steps if a formula or deletion goes wrong.

    Step 3: Inspect Before Cleaning (10 Minutes)

    Check the working copy against basic tidy data principles, as described by statistician Hadley Wickham: each variable in its own column, each observation in its own row.

    • One kind of record per row: Each row should be one invoice, transaction, or appointment, not a group header or subtotal.
    • One variable per column: Date, customer or ID, amount, and status should each have their own column.
    • Presentation rows: Many exports, including QuickBooks reports, add title rows, blank separator rows, and totals.
    • Duplicates and gaps: Sort by date and look for repeated rows or missing weeks and months.
    • Dates: Confirm the spreadsheet recognizes dates as dates, not text, and note the format used.

    Here is what that difference looks like, using invented sample data.

    As exported:

    A B C D
    Sales by Customer Detail
    January–December 2025
    Customer Date Invoice Amount
    Acme Supply
    01/14/2025 1041 2,400.00
    03/02/2025 1057 1,150.00
    Total for Acme Supply 3,550.00

    Ready for analysis:

    customer invoice_date invoice_number amount
    Acme Supply 2025-01-14 1041 2400.00
    Acme Supply 2025-03-02 1057 1150.00

    Step 4: Clean and Answer the Question (10 Minutes)

    In the working copy:

    1. Delete title, blank, subtotal, and total rows so that row 1 holds only column headers.
    2. Fill down group values, such as the customer name, so every row carries its own customer.
    3. Give each column one unique header and unmerge any merged cells.
    4. Add a new date column in YYYY-MM-DD format and keep the original date column unchanged.
    5. Insert a pivot table with customer in Rows and amount in Values (set to SUM), then sort in descending order. To see each customer's share, show values as a percentage of the grand total.
    6. Check the pivot table's grand total against the report's total. If they differ, find out why before trusting the answer.

    Keep This Test Inside Your Spreadsheet
    Do not paste unredacted customer lists, payroll data, or financial exports into consumer AI chatbots to clean or summarize them. Whether any AI tool is appropriate depends on the account type, its data settings, and your obligations to customers and employees.

    Do Not Combine Sources Yet

    Resist merging this file with exports from other systems. Combining sources requires agreed definitions, matching keys, and documented reconciliation rules, and that work belongs after a source audit. A well-structured single-source spreadsheet can answer a surprising number of small-business questions. For how far that can take you before you need specialist help, see Do You Need a Data Analyst or Better Spreadsheets?

    If one export answers a real question in thirty minutes, you have confirmed that your existing systems hold usable records, with no new software purchase.

    When Are the Records or Existing Tools Not Enough?

    Existing tools are not enough when the required records are missing, inaccessible, too aggregated, inconsistently defined, or too difficult to maintain at the frequency and level of control the decision requires.

    Native exports are a sound, low-risk starting point, but they are not a permanent answer for every business. Watch for these signs:

    1. You cannot get record-level data. The system offers only summaries or PDFs, or an authorized person cannot get access. A common example is an account owned by a former employee or an outside bookkeeper that the company cannot recover.
    2. Required fields are missing. The export lacks the date, status, amount, or stable identifier your question depends on.
    3. History is missing or definitions conflict. The platform keeps only limited history, or "sales" means one thing to the office and another to operations.
    4. Differences between systems cannot be explained. Gaps between processor, accounting, and bank totals cannot be traced to documented timing, fee, refund, or accounting-method rules.
    5. The process depends on one person. The report is late, or it breaks whenever the person who builds it is out.
    6. Control needs outgrow the workflow. Multiple locations, entities, or users edit the same file, or sensitive customer data travels by email attachment.

    There is no universal row count or hours-per-week cutoff. Whether a spreadsheet is still the right tool depends on its formulas, how consistent the sources are, how many people use it, how often it must update, and what happens if it is wrong.

    The Next Step: Audit Your Sources

    If you recognize one or more of these signs, the answer is not to buy a business intelligence subscription right away. First, document what you have: which sources exist, who owns access, how much history each keeps, and which fields are complete. Take the inventory from this article and work through The Small Business Data Audit Checklist.

  • The Small Business Data Audit Checklist

    A small business data audit is not the same thing as a cybersecurity or compliance audit. This audit has a simpler, more practical purpose: identify the business data you already have, find out where it lives, test whether you can use it, and choose the first problem worth fixing.

    You can complete the first pass in an afternoon. By the end, you should have a one-page inventory of your core commercial, financial, operational, production, and quality sources; a usable/fixable/unusable rating for each; one clear next action; and evidence for the rules that may belong in a company-wide data policy. You do not need a data warehouse, a new analytics platform, or a technical team to begin.

    What is a small business data audit?

    A small business data audit is a structured review of the information your company already collects through its everyday systems. It answers four basic questions: What data exists? Where is it stored? Who controls access? Can the records support the decisions you need to make?

    The emphasis here is usability. A security audit asks whether systems and information are adequately protected. A privacy or compliance review asks whether data is collected, retained, and handled according to applicable rules. Those are important disciplines, but they are separate from determining whether last month’s sales, customer, marketing, or operating records are complete enough to analyze.

    Why should a small business audit its data?

    A data audit prevents you from building reports on assumptions. Small businesses often have plenty of data but no reliable path from the original records to a decision. Revenue may differ between sales, accounting, and bank systems. In a production business, output, scrap, rework, downtime, inspection, and maintenance records may all exist while nobody can use them together to identify the current constraint. An agency, former employee, vendor, or single supervisor may control the only account or spreadsheet needed to explain the result.

    Without an inventory, these problems stay hidden until a decision depends on them. Teams then spend hours debating which total is right, manually rebuilding reports, or buying software before they understand the underlying issue.

    The goal is not to make every source perfect. It is to learn which sources are dependable now, which need a bounded repair, and which cannot support the intended reporting.

    What should you prepare before you start?

    Block two to three uninterrupted hours for the first pass. Open a blank spreadsheet (or copy the free Data Audit Sources Template) and create one row for every system or record set you use. Ask the relevant owners to join you: that may include operations, finance, sales, production, quality, maintenance, or a trusted administrator. No single person needs to know every system.

    Create these columns:

    • System or source
    • Business process supported
    • Data owner
    • Administrator or account owner
    • What records it contains
    • Export available
    • History available
    • Reliable date field
    • Reliable customer, job, transaction, work-order, batch, lot, machine, or product identifier
    • Completeness and freshness
    • Rating
    • Failed check
    • Next action

    Do not place passwords, customer records, credentials, or sensitive exports in the inventory. Record what exists and who can access it; keep the underlying data in its approved system.

    Step 1: Start with the decisions you need to make

    Before listing tools, write down the decisions you want your data to support. Examples include how much cash will be available next month, which services or products are most profitable, which marketing sources produce customers, where jobs are delayed, which operation constrains throughput, why scrap or rework is rising, whether planned and actual cycle times differ, and whether corrective actions improve quality.

    This keeps the audit from becoming a software inventory. A system matters because it holds records needed for a decision. If you do not know what decision a source supports, note that uncertainty instead of inventing a purpose.

    Choose one to three priority decisions for this first audit. Then name the five to seven headline numbers you currently use—or wish you could use—to make them. In a production setting, those might include throughput, schedule attainment, first-pass yield, scrap or rework, downtime by reason, work in process, on-time delivery, or a constraint measure. That short list becomes the test for whether your sources are useful.

    Step 2: List where commercial, operational, production, and quality data lives

    Most small businesses can begin with the categories below. Use the ones that apply to your operating model, then add one row for every other source that supports a priority decision:

    • Point of sale, order-management, ecommerce, or booking system
    • Accounting software or finance module
    • CRM, customer-service platform, or shared sales inbox
    • Bank and merchant processor
    • Payroll and timekeeping system
    • Scheduling or workforce-management tool
    • Website, campaign, or ecommerce analytics
    • Spreadsheets
    • Production planning, ERP, MES, machine, or operator logs
    • Quality management, inspection, test, nonconformance, and corrective-action records
    • Maintenance, downtime, and changeover logs
    • Inventory, warehouse, procurement, and supplier-quality records

    Add other systems only when they contain records needed for your priority decisions. This can include project-management software, laboratory or test equipment, supplier portals, paper inspection sheets, operator logs, and industry-specific applications. If the business relies on the record—even if it is informal—it belongs in the inventory.

    At this stage, do not evaluate quality. First, make the list complete. Include the “unofficial” spreadsheet someone updates every Friday and the inbox that functions as a lightweight CRM. If the business relies on it, it belongs in the inventory.

    Step 3: Confirm ownership, access, and exportability

    For each source, identify a named data owner and a named administrator. The data owner understands what the records mean. The administrator can manage access and produce an export. One person may perform both roles, but write down both responsibilities.

    Then answer three questions:

    • Can an authorized employee sign in without relying on an agency, former employee, or personal account?
    • Can that employee export record-level data rather than only view a dashboard or screenshot?
    • Can a second authorized person recover access if the primary administrator is unavailable?

    Open a current export when it is safe to do so. A successful download is better evidence than a remembered feature. You are looking for detailed rows with dates and stable identifiers, not only a monthly total. Record the file format and the person who completed the test.

    If nobody can export the data, mark the source as a priority access issue. Do not request or collect live credentials in the audit worksheet.

    Step 4: Test each source on five usability checks

    Rate every source against the same five checks. Consistent criteria make the result more useful than a general impression that the data is “good” or “messy.”

    Exportability. Can an authorized employee obtain detailed records in a usable format? A screenshot or summary PDF usually cannot support reconciliation or flexible analysis.

    History depth. Does the source cover the period needed for the decision? Twelve months may be enough for a recent trend; seasonality or retention analysis may require more.

    Reliable date field. Does each important record have a date or timestamp that means what you think it means—such as order date, service date, production start or completion, inspection time, failure time, payment date, or lead-created date? Document which date you intend to use.

    Reliable customer, job, transaction, work-order, batch, lot, machine, line, shift, or product identifier. Is there a stable ID that distinguishes one record from another and connects inputs, process events, quality results, and outcomes? Names and free-text descriptions alone are often inconsistent.

    Completeness and freshness. Are expected records present, are there unexplained gaps, and is the data updated quickly enough for the decision?

    Write the failed check in the inventory. “Fixable—date field unclear” is far more actionable than “data quality problem.”

    Step 5: Rate each source as usable, fixable, or unusable

    Use three ratings:

    • Usable: The source can be exported, covers the needed period, and has reliable critical fields that are current enough for the intended reporting.
    • Fixable: A bounded cleanup, access change, definition, or export change can make the source usable.
    • Unusable: The source cannot be exported or lacks critical history or identifiers needed for the intended analysis or reconciliation.

    The rating depends on the decision. A scheduling export without customer IDs may still be usable for staffing by hour but unusable for customer-level profitability. State the intended use beside the rating so nobody treats it as universal.

    For every fixable source, write one concrete repair and an owner. Examples include moving administrator access to a company-controlled account, documenting which date field governs a metric, standardizing campaign names, recovering older exports, or adding a stable job ID.

    Do not spend the afternoon cleaning every source. The audit identifies work; it does not need to complete all of it.

    Step 6: Check whether two systems agree

    Choose one important number that appears in at least two systems. Monthly revenue is a common commercial example; completed units, scrap quantity, downtime, yield, or on-time delivery may be more useful in production. Compare the same period, products, locations, lines, and status rules using written definitions.

    Differences do not automatically mean one system is wrong. A point-of-sale system may report orders when they are placed, accounting software may recognize revenue according to a different rule, and bank deposits may be reduced by fees or shifted by settlement timing.

    When totals differ, test these common causes:

    • Different date fields or time zones
    • Refunds, cancellations, discounts, or taxes treated differently
    • Cash versus accrual timing
    • Processing fees or grouped deposits
    • Missing locations, channels, products, or manual entries
    • Duplicate or late records
    • Different definitions of the metric

    Record the difference, the explanation if known, and the source that should govern the specific metric. If you cannot explain a material difference, mark reconciliation as the next action instead of averaging the totals or choosing the most convenient one.

    Step 7: Choose the single issue to fix first

    Your finished inventory may contain several red flags. Resist the urge to start all of them. Choose the issue that sits furthest upstream and blocks the most important decision.

    A useful priority order is:

    • Agree on the metric definition.
    • Assign its source of truth.
    • Secure company-controlled access and record-level exports.
    • Repair missing dates, identifiers, or coverage.
    • Reconcile conflicting totals.
    • Build or improve the report.
    • Set a recurring review and assign actions.

    This order prevents a polished dashboard from hiding a weak foundation. If you do not agree on what revenue includes, dashboard formatting is not the first problem.

    Write the first fix as a small outcome that can be verified. “Improve data quality” is too vague. “Export twelve complete months of order-level records with order date and order ID by Friday” is specific enough to own and check.

    What if your company already collects production and quality data?

    If you already collect production and quality data, the audit should focus less on finding more data and more on connecting existing records to operating decisions. Many manufacturers have years of inspection results, downtime logs, production counts, scrap records, corrective actions, and process documentation. The problem is often that those records were created for different purposes, use different identifiers, or are reviewed without a clear decision rule.

    Depending on region and customer requirements, this work may already sit inside a formal quality-management initiative. In many European and Middle Eastern businesses, that may be an active ISO 9001 project; in the United States, similar process-documentation and quality-improvement work may exist without the ISO label. Those documented processes and retained records are valuable inputs, but documented collection does not automatically make the data decision-ready. The audit adds an analytical layer by asking: Which decision should this record change? What threshold or pattern triggers action? Who reviews it, how often, and what happened after the last review?

    Useful production questions include:

    • Where does work wait longest between operations?
    • Which line, machine, tool, supplier, material, product, or shift contributes most to lost throughput?
    • How do planned and actual cycle times differ?
    • Which downtime and changeover reasons consume the most constraint time?
    • Where do first-pass yield, scrap, rework, defects, or nonconformities change?
    • Can corrective actions be linked to a later measurable result?
    • Do production, quality, maintenance, and planning systems agree on the same work order, batch, lot, quantity, and status?

    Do not begin with a plant-wide average. Constraints are usually local, and averages can hide the operation that governs the system’s output. Start with the flow of one important product family or value stream, map the records at each step, and identify the decision that should follow when performance crosses an agreed threshold.

    A manufacturing example: finding the real production constraint

    Imagine a midsize manufacturer that wants to increase output without adding another shift. It already records work orders and due dates in its planning system, machine cycle times and downtime codes on the floor, inspections and nonconformities in its quality process, scrap and rework in spreadsheets, and maintenance events in a separate application.

    The audit shows that most of the data is exportable, but it is difficult to connect. Machine records use equipment IDs, quality records use batch numbers, and rework spreadsheets rely on product descriptions. Downtime reasons are entered inconsistently, while some changeovers are recorded as planned stops and others as equipment downtime. The business has substantial documentation and many measurements, but no dependable thread from work order to process event to quality outcome.

    When the team aligns work orders, batches, machines, shifts, and completion timestamps, it finds that the apparent capacity problem is concentrated around one operation. Average plant utilization had hidden the queue. Inconsistent changeover and downtime coding made the constraint look like several unrelated issues.

    The first fix is not a new dashboard or another sensor. It is a shared identifier and reason-code rule, followed by a weekly constraint review that compares planned versus actual cycle time, queue time, downtime, scrap, and rework for the constrained operation. That is a successful first audit: the business connects existing records to a decision and can verify whether the corrective action improves flow.

    Keep privacy and security in scope—but keep the boundary clear

    During the audit, you may discover personal information, weak access controls, or unclear retention practices. Record the issue and route it to the appropriate owner. Do not copy sensitive records into an informal worksheet, email exports unnecessarily, or request passwords from colleagues.

    Privacy, security, retention, and legal obligations vary by business, location, industry, and data type. This checklist is not legal, privacy, cybersecurity, or assurance advice. If the audit reveals a material concern in those areas, seek qualified guidance rather than expanding this usability review into a do-it-yourself compliance assessment.

    Your completed SMB data audit checklist

    Before you finish, confirm that you can answer every item below:

    • We listed every system that supports a priority business decision.
    • We identified the records each source contains.
    • We named a data owner and an administrator for each critical source.
    • An authorized employee tested record-level export access.
    • We recorded how much history is available.
    • We identified the date field used for reporting.
    • We identified stable customer, job, transaction, work-order, batch, lot, machine, line, shift, or product IDs where needed.
    • We checked completeness and freshness.
    • We rated each source usable, fixable, or unusable for a stated purpose.
    • We compared one important number across two systems.
    • We documented the reason for any known difference.
    • We chose one upstream fix, one owner, and one verification step.
    • We recorded recurring rules that should become company policy.
    • We set a review cadence and event-based triggers for future audits.

    If several answers are “not sure,” that is a useful result. Verification should come before new analytics work.

    What should you do after the audit?

    Keep the inventory as a living one-page reference. Update it when you add a system, change an administrator, revise a metric definition, or discover a gap. Use repeated findings to distinguish one-time cleanup from rules the whole company should follow.

    Turn the audit into a company-wide data policy

    A completed audit gives you the evidence to create or improve a company-wide data policy. The policy turns lessons from one review into repeatable rules for how the business defines, owns, collects, accesses, checks, uses, shares, corrects, and retires important data.

    The policy does not need to be long. It should define:

    • The business decisions and processes that critical data must support
    • The owner and source of truth for each headline metric and critical data set
    • Required definitions, identifiers, naming rules, and quality checks
    • Who receives administrator access and how the company retains control when people or vendors change
    • Where sensitive data may be stored, exported, and shared
    • How errors, missing records, conflicting totals, quality exceptions, and access failures are reported and resolved
    • How reports are reviewed, how actions are assigned, and how results are verified
    • When retention, deletion, privacy, security, contractual, or regulatory questions require qualified guidance
    • Who owns the policy, approves changes, and confirms that teams follow it

    The policy should also establish when the next audit happens. Set a regular cadence appropriate to how quickly the business and its systems change, then add event-based triggers. A new ERP, CRM, MES, site, production line, product family, acquisition, vendor transition, ownership change, recurring reconciliation problem, or serious quality issue can justify an earlier review.

    Treat the policy as an operating document, not a file created once and forgotten. Each audit should test whether the rules still match reality, record exceptions, and update the policy when the business learns something new.

    Then test the broader analytics system around the data. The free SMB Analytics Health Assessment checks four connected areas: metrics, data, reporting, and ownership. It takes about five minutes and gives you an immediate score, your primary barrier, and a first recommendation before asking for contact details.

    Take the free SMB Analytics Health Assessment

    The purpose of the audit is not to prove that your data is perfect. It is to replace uncertainty with a usable map: what exists, what can be trusted, what needs repair, and what to do first.