John Serra – Backend

Tag: Data Audit

  • 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.

  • 12 Questions to Ask Before Hiring a Data Consultant

    Don’t just ask for case studies; ask how the consultant will scope the first two weeks, who will own each deliverable, and what system access the work requires. Strong answers should be tied to your systems, metrics, users, and constraints—not a generic template.

    Before the call, inventory your data sources, reporting bottlenecks, and the decisions the work needs to support. The Small Business Data Audit Checklist gives you a practical prep list, so you can spend the conversation evaluating the consultant instead of reconstructing your own environment.

    Engagement (Scope and Fit)

    1. What will you do in the first two weeks, and what will I have at the end of it?

    • Good answer: “The first two weeks are a discovery and data auditing phase. We will inspect your raw data sources, map the data flow, and deliver a technical scope document along with a working prototype or wireframe of the reporting dashboard.”
    • Bad answer: “We will start building the final dashboards immediately and have the complete solution ready in two weeks without needing to review your underlying data sources first.”

    2. What do you need to understand before you can recommend a solution?

    • Good answer: “Before recommending anything, we need to understand the decisions you are trying to make, how your metrics are defined, where the data comes from, who uses the output, and what constraints we need to work within.”
    • Bad answer: “We understand the problem from your brief and can start with our standard dashboard template without further discovery.”

    3. What is your approach to cleaning data that isn’t ready for analysis?

    • Good answer: “Data cleaning and transformation are explicit line items in our project scope. We document data anomalies, build automated transformation pipelines where possible, and establish data validation rules.”
    • Bad answer: “We assume your data is already clean and perfectly formatted, so data preparation won’t take any time or budget.”

    Money and Ownership

    4. How do you structure your fees, and can you provide a fixed-price option for the first phase?

    • Good answer: “We offer fixed-price scoping and discovery packages, followed by either fixed milestone pricing or structured hourly rates for ongoing iterations with clear cap limits.”
    • Bad answer: “We only work on open-ended hourly billing with no initial estimate, maximum ceiling, or milestone deliverables.”

    For directional context, Clutch’s 2026 marketplace data lists U.S. and Canadian BI and analytics firms at $100–$149 per hour and reviewed projects typically at $10,000–$49,999. Those figures are not a quote for your project; a tightly scoped first phase may be much smaller.

    5. Who owns each deliverable, and does the contract assign the IP or grant the rights I need?

    • Good answer: “The agreement identifies each deliverable, any pre-existing tools, and the ownership or license rights you receive upon payment. We will put those rights and handoff obligations in writing.”
    • Bad answer: “Ownership is standard—there is no need to spell out which code, dashboards, models, or documentation you can use after the engagement ends.”

    For contractor work, a “work made for hire” label applies only when the work meets specific statutory conditions. The U.S. Copyright Office explains that specially commissioned work must fit one of nine categories and be covered by an express signed agreement; a written assignment or license is a separate route for defining rights. Have counsel review the final language.

    6. If we stop working together in six months, what do I actually have?

    • Good answer: “You will have full admin access to all deployed tools, complete source code repositories, documented data models, and an offboarding playbook that allows another analyst to maintain the system.”
    • Bad answer: “The reports run inside our proprietary platform, so if our contract ends, you lose access to the dashboards and underlying pipelines.”

    System Access, Security, and Compliance

    7. Exactly what systems do you need access to, and can you use ‘read-only’ credentials?

    • Good answer: “We use least privilege: read-only by default. Any write or administrator access will be justified, role-based, time-limited, and separated from production where practical.”
    • Bad answer: “We need admin-level passwords and full write privileges across all your core production databases and business accounts.”

    8. How will you store or transfer the data files I send you?

    • Good answer: “All file transfers occur via encrypted channels (SFTP, secure cloud storage), and data stored locally during development resides on encrypted drives with strict deletion protocols upon project completion.”
    • Bad answer: “You can just email us raw CSV files or share unencrypted Google Sheets containing sensitive customer records.”

    9. What is your process for data security, and how do you handle sensitive customer information?

    • Good answer: “We document the data involved, use MFA and encrypted transfer and storage, limit access, mask sensitive fields where practical, and agree in writing how data will be retained, returned, or deleted.”
    • Bad answer: “We don’t have formal security protocols; we just assume small business data isn’t a high-risk target.”

    For example, New York’s SHIELD Act requires any business that owns or licenses computerized data containing a New York resident’s “private information” to maintain reasonable safeguards. Its examples include selecting capable service providers and requiring safeguards by contract. Other state, federal, sector-specific, or international rules may apply based on the data and where the parties operate. Identify the applicable requirements and have counsel review the contract.

    Proof and Testing

    10. Can you show me a similar dashboard or report you built for a business this size?

    • Good answer: “Yes, here is a sanitized demo or portfolio sample created for a similar business, showing how we structured the metrics, user filters, and data refreshes.”
    • Bad answer: “Client confidentiality prevents us from showing completed work, and we will not offer a sanitized demo, synthetic example, architecture walkthrough, reference, or comparable-process explanation.”

    11. What is your policy on ‘post-delivery’ bugs?

    • Good answer: “The contract defines what counts as a defect, the correction window and response time, what support is included, and the rate for maintenance or changes after acceptance.”
    • Bad answer: “Support starts on a new open-ended hourly work order, and we do not define defects, acceptance, response times, or post-delivery responsibilities in advance.”

    12. Can I run a paid trial before committing to a full project?

    • Good answer: “Yes, we recommend starting with a short, paid discovery phase or small data audit to test working chemistry and validate data feasibility before signing a full engagement.”
    • Bad answer: “No, we only accept full-scale long-term contracts with large upfront commitments.”

    How to Decide After the Call

    Move forward when the consultant gives concrete answers to the top three questions—scope, ownership, and access—and the key terms are documented in writing.

    Choose a paid trial when the fit looks promising, but the scope, data quality, or working relationship still needs to be tested.

    Walk away when the consultant is evasive about ownership, asks for unjustified access, or cannot explain how your data will be protected.

    If you are not sure whether your business is ready, take the five-minute Analytics Health Assessment. If you are ready to discuss a defined project, start the conversation.

    Frequently Asked Questions

    Should I sign an NDA?

    Use an NDA before sharing genuinely confidential information, but do not treat it as a substitute for least-privilege access, data-security terms, deletion and return obligations, or any required data-processing agreement. Have counsel review the contract when the stakes or data sensitivity justify it.

    Should I pay hourly or fixed price?

    Use fixed-price arrangements for clearly defined scope and initial deliverables like audits or initial dashboards. Hourly structures are best reserved for ongoing maintenance, advisory work, or unpredictable operational support.

    What if they use a tool I don’t have?

    If you don’t already own the tool, you likely need a strategy implementation expert rather than a pure analyst. Make sure the consultant builds solutions on infrastructure you own and can support long-term.

    How long should a first project be?

    I recommend a bounded two-week paid discovery or audit with named outputs: a data-source inventory, risk and quality findings, a prioritized scope, and a go/no-go decision. The key is a clear deliverable and decision point with no automatic long-term commitment.

  • 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.