John Serra – Backend

Tag: Business Intelligence

  • How to Improve a Business Dashboard You Already Have

    Edit your dashboard before you replace it. Confirm the numbers match your source systems, cut the main view to four to seven metrics, lay out what remains on one screen with a target beside each number, fix the manual data steps that keep breaking, and put one person's name on each metric. Most cluttered dashboards can be repaired in the tool you already have.

    A rebuild feels cleaner, but it tends to recreate the same problem in new software. Dashboards get cluttered because requests keep getting added and nothing gets removed. A new tool doesn't change that habit.

    Should You Fix Your Dashboard or Rebuild It?

    Fix it if the underlying numbers are right and the business still works the way it did when the dashboard was built. Rebuild only when the data feeding it can't be repaired, or the business has changed so much that the old metrics no longer describe it.

    Answer three questions before changing anything:

    1. Do the numbers match the source? Pick two or three headline figures, such as last month's revenue or current receivables, and compare them with your accounting system, bank, or POS for the same period. If they match, or the gaps have a known cause like payout timing, the foundation is usable. If nobody can explain the gaps, fix the data first. Redesigning a screen full of wrong numbers wastes the afternoon.
    2. Does the dashboard still describe the business? If you've added a service line, closed a location, or changed how you price, some metrics may describe a business you no longer run.
    3. Is the tool really the problem? Google Sheets, Excel, Looker Studio, and Power BI can all display a clean, focused set of KPIs. Switching tools rarely fixes a layout problem.

    If the answers point to renovation, work through the four steps below in order.

    The Four-Step Dashboard Renovation

    Step 1: Cut the Main View to Four to Seven Metrics

    The fastest improvement is removal. List every chart, card, and table on the dashboard, then mark each one: keep on the main view, move to a diagnostic tab, or delete.

    Which metrics survive depends on the business, but the question I ask is the same: does this metric make a difference? Does it help bring in more revenue, make the company more efficient, or cut costs? If it does none of those, it's a candidate for deletion.

    My rule of thumb is four to seven core metrics, and each one has to move the needle: when it changes, someone makes a specific decision. The full test for sorting metrics is in How Many KPIs Should a Small Business Track?

    Expect pushback on deletions. Instead of arguing, move disputed metrics to a tab labeled "Diagnostic." If nobody opens that tab in two months, delete them.

    Step 2: Make It Readable at a Glance

    Arrange what's left so someone can tell whether the business is on track within a few seconds, on one laptop screen, without scrolling.

    • Replace gauges and dials with number cards. A speedometer graphic uses a lot of space to show one value. A card showing the current value, the target, and last period's value shows more in less room.
    • Take large tables off the main view. A 50-row table is a report. Summarize it in a card or a small trend line and move the detail to the diagnostic tab.
    • Give every number a comparison. "$74,200" alone doesn't tell you much. "$74,200 against an $80,000 target, up from $69,800 last month" tells you where you stand and which way you're heading.
    • Save color for exceptions. Use gray and neutral tones for the layout. Reserve red, yellow, and green for metrics outside their target range, so color means something when it appears.

    If the main view still doesn't fit on one screen, go back to Step 1. The next useful check is to look for repeated design errors such as missing comparisons, raw tables, and detail mixed with headline results.

    Step 3: Fix the Data Steps That Break

    Find every place where someone copies, pastes, or retypes data to update the dashboard. Manual steps are where errors creep in, and where updates stop when that person is on vacation.

    • Connect instead of paste where your tools allow it: a built-in data connector in Looker Studio or Power BI, or the IMPORTRANGE function to pull from another Google Sheet.
    • Document what can't be connected. Write down which report is exported, with which filters and date range, by whom, and when.
    • Match the refresh schedule to your decisions. A metric you review weekly needs a dependable daily or weekly refresh, not necessarily a live feed. Real-time connections can add cost or maintenance complexity, and faster data can invite reactions to swings that even out by the end of the week.

    Step 4: Put a Name on Every Metric

    Assign one person to each metric on the main view and show their name or initials on the card. That person explains the number when it moves outside its range and says what they're doing about it.

    When a metric belongs to everyone, nobody follows up. A named owner means a red number gets an explanation and a next step. Ownership works best when the dashboard is reviewed at a regular meeting with a consistent follow-up routine.

    Dashboard Renovation Checklist

    1. Compare two or three headline numbers with your accounting, bank, or POS records.
    2. List every item on the dashboard and mark it keep, diagnostic, or delete.
    3. Move diagnostic items to a separate tab and delete the rest.
    4. Fit the remaining four to seven metrics on one screen.
    5. Replace space-heavy gauges with cards showing actual, target, and prior period.
    6. List every manual data step, then connect or document each one.
    7. Add an owner's name to every metric.

    Frequently Asked Questions

    How long does it take to fix a cluttered dashboard?

    The cuts and layout changes are usually the quickest part. Data problems take longer, especially if nobody documented how the dashboard was built.

    Should a small business dashboard update in real time?

    Rarely. Match the refresh to how often you act on the number. A scheduler may need daily capacity data, while a closed-month margin calculation only needs to update after the accounting period ends.

    When is it worth paying for a rebuild?

    When the source data can't be reconciled, the business has changed enough that the metrics need to be redesigned, or nobody on staff can maintain the data connections.

    Start With the Numbers

    Begin with the first check: pull two headline figures and compare them with your source systems. If they hold up, the rest of the renovation is editing. If your team still ignores the dashboard after it's cleaned up, investigate trust and routine rather than redesigning it again.

  • How Many KPIs Should a Small Business Track?

    Most small businesses should track four to seven KPIs on their main scorecard. Where you land in that range depends on the business, but the rule for what earns a place does not change: every metric has to move the needle. A change in the number should lead someone to make a specific decision about cash, capacity, or customers. You can still measure everything else. It just belongs in a second layer you open when a core number goes off track.

    Four to seven is an operating guideline, not a scientifically fixed limit. It is deliberately shorter than most “essential KPI” lists. Those lists are written to cover every possible business. Your scorecard only has to cover yours, and it has to be short enough that you and your team will read it every week.

    Why Four to Seven KPIs?

    Four to seven metrics can cover the questions that keep a small business healthy: Do we have cash? Are we making money on the work? Can we deliver? Is new work coming in? That is enough coverage without turning the weekly review into a research project.

    Below four, something important usually goes unwatched. An owner who tracks only the bank balance sees problems weeks after they start: a slow-paying customer, a job that ran over budget, a quiet month in the pipeline. The balance tells you where you are. It says little about what is coming.

    Above seven, three things tend to happen.

    The review takes too long. A short scorecard can fit into a brief weekly review. A 25-metric dashboard is more likely to turn that review into a status recital, so the meeting gets skipped, or the important change gets buried.

    Nobody can tell which change matters. In any given week, some of 25 numbers will rise, and some will fall for ordinary reasons. When everything moves, the one signaling a real problem is easy to miss.

    Ownership blurs. Each of the seven metrics can have a named person responsible for explaining it. With twenty-five metrics, ownership is harder to keep clear, and follow-up becomes less consistent.

    Treat the range as a guideline. A business with several distinct departments may need a short scorecard for each one. The principle holds at every level: the list any one person reviews should be short enough to act on.

    What Makes a Metric a Needle-Mover Instead of a Vanity Metric?

    A needle-mover leads to a specific decision when it changes. A vanity metric can look impressive and still tell you nothing about what to do next.

    Analytics expert Avinash Kaushik calls this the “Three Layers of So What” test. Keep asking “so what?” until the metric leads to a recommended action. If it cannot, it does not belong on the main scorecard.

    Run every metric you currently track through three questions:

    1. The Monday test. If this number dropped 20 percent by Monday morning, who on the team would do what? If the honest answer is “nobody” or “we’d keep an eye on it,” the metric is not a KPI.
    2. The anchor test. Does the metric connect directly to cash, margin, delivery capacity, or keeping customers? If the connection takes three steps of reasoning to explain, it is a supporting metric at best.
    3. The response test. Can your team make a useful decision when this number changes? You may not control the weather, the economy, or a vendor’s pricing, but you can still change staffing, purchasing, pricing, or cash plans in response. If the team cannot influence the number or respond to it, keep it off the main scorecard.

    A metric has to pass all three to earn a place on the main scorecard.

    Here is how some common vanity metrics compare with metrics that answer a similar question in a form you can act on:

    Looks usefulMoves the needleWhy
    Website page viewsQualified inquiries, and the share that become quotesTraffic can rise while the phone stays quiet
    Social media followersCost to acquire a paying customerFollowers don’t pay invoices
    Revenue bookedGross margin and cash collectedRevenue earned at a loss still costs you money
    Total labor or machine hours loggedShare of work done right the first timeBusy hours can hide rework
    Number of proposals sentProposal win rate and pipeline valueVolume without wins is activity, not progress

    The metrics in the left column still have uses. Page views can help explain why inquiries fell. They just don’t belong in the weekly review.

    What Do You Do With All the Other Metrics?

    Keep them, but take them off the main scorecard. Move them into a diagnostic layer that you open only when a core KPI goes outside its normal range.

    Think of it as two tiers.

    Tier 1: the scorecard. Four to seven KPIs, reviewed on a fixed schedule, usually weekly. (Some, such as gross margin, only update meaningfully once the month closes.) Each has a normal range, a named owner, and a place on one page or one screen without scrolling.

    Tier 2: diagnostics. The supporting detail: revenue by customer, overtime by crew, cost by vendor, scrap by workstation, website traffic by source. These live in a separate tab, a saved report, or an export from software you already use. You don’t review them every week. You open them to find out why a Tier 1 number moved.

    Here is how the two tiers work together. Say gross margin is one of your scorecard KPIs and normally runs between 45 and 50 percent. One month it comes in at 39 percent. That is a clear signal, so you open the diagnostics: material costs by vendor, overtime hours, rework on specific jobs. The scorecard tells you that something is wrong. The diagnostic layer tells you where.

    Most Tier 2 data already exists in your accounting, payment, scheduling, and operations systems. The Business Data You Already Have covers where to find it and how to test an export in a spreadsheet.

    Which Four to Seven KPIs Fit Your Business?

    The right KPIs depend on what limits your business: billable time in a service firm, flow through the shop in a manufacturer, food and labor costs in a restaurant. The three scorecards below are starting points. Replace any metric that fails the three-question test in your business.

    Each list includes a cash measure in a form that fits the business model. Whatever you change, keep one.

    Professional Services Firm

    Consulting, accounting, design, engineering, and agency firms sell expert time.

    1. Billable utilization: billable hours as a share of available hours, tracked by role
    2. Project gross margin: project revenue minus direct labor and project expenses, as a share of project revenue
    3. Days sales outstanding (DSO): how long, on average, clients take to pay
    4. Qualified pipeline: value of opportunities likely to close in the next 90 days
    5. Client retention: share of last year’s clients that still buy from you this year

    Job Shop or Light Manufacturer

    Output is limited by the slowest step in the process, so this scorecard watches flow, quality, and delivery.

    1. Queue time: how long work in progress waits between operations, such as between machining and assembly
    2. First-pass yield: share of parts or jobs completed without rework or scrap
    3. On-time, in-full delivery: share of orders shipped complete by the promised date
    4. Quote-to-order rate: share of quotes that become orders
    5. Cash runway: weeks of operating expenses covered by available cash

    Queue time is the one most shops overlook. A part can spend two hours on a machine and several days waiting for the next operation. Adding machine capacity won’t shorten that wait if the delay is downstream.

    Restaurant or Hospitality Business

    Food and labor are the highest costs you can control week to week, and they move quickly.

    1. Prime cost: cost of goods sold plus total labor, as a share of sales
    2. Labor cost as a share of sales, by shift or day of the week
    3. Average check, or spend per guest
    4. Table turns during peak periods
    5. Weekly cash in compared with fixed costs going out

    Prime cost and labor overlap on purpose. Prime cost tells you whether the combined total is under control. Labor by shift tells you where to change the schedule.

    Each list stops at five. That leaves room for one or two metrics specific to your situation, such as a major customer’s order volume or a seasonal inventory position, without going past seven.

    How Do You Cut an Existing Metric List Down?

    Use four steps: list what you track now, sort each metric with the three-question test, set a normal range for the survivors, and move everything else to the diagnostic tier.

    Step 1: List Everything You Currently Track

    Include dashboard widgets, spreadsheet tabs, numbers in your accountant’s monthly packet, and figures you check inside software on your own. Most owners find more than they expected.

    Step 2: Sort Each Metric Into Three Groups

    Apply the Monday, anchor, and response tests, then mark each metric:

    • Scorecard: passes all three tests
    • Diagnostic: helps explain a scorecard metric but fails the Monday test on its own
    • Drop: fails the anchor test and doesn’t help explain anything on the scorecard

    If more than seven metrics pass, rank them by how much cash or capacity is at stake and keep the top seven. Several of the ones that fall off will make good diagnostics.

    Step 3: Set a Normal Range and a Trigger

    For each scorecard KPI, write down its normal range and the point that requires action. Base the range on your own last 12 months rather than an industry average you found online. For example: “Gross margin normally runs 45 to 50 percent. Below 44 percent, the operations manager reviews job costs within a week.”

    Assign each KPI to one person, who explains it when it moves.

    Step 4: Move the Rest to the Diagnostic Tier

    Put diagnostic metrics in a separate tab or saved report, and remove dropped metrics from the weekly view. If someone objects to losing a metric, move it to diagnostics and check whether anyone opens it over the next two months.

    Example: 16 Metrics Down to 5

    This is an illustrative example, not a client case. An eight-person commercial cleaning company tracks 16 metrics in a spreadsheet. After the three-question test, they sort like this:

    ResultMetrics
    Scorecard (5)Weeks of cash on hand · Gross margin by contract · Invoice dollars more than 45 days past due · Labor hours versus bid hours by site · Contracts at risk or cancelled
    Diagnostic (7)Revenue by client · Supply cost by site · Overtime by crew · Re-cleans and complaints by site · Quote win rate · Average contract value · Days from signed quote to first service
    Drop (4)Website visits · Social media followers · Total square feet cleaned · Prospecting emails sent

    Labor hours versus bid hours made the scorecard because a site that consistently takes longer than bid is losing money, and the site supervisor can act on it that week. Overtime by crew stayed in diagnostics: it helps explain a labor problem but doesn’t need weekly attention on its own. Total square feet cleaned was dropped. It grows as the company grows but says nothing about whether the work is profitable.

    Next Step: Put Your KPIs on a Scorecard

    Once you have your four to seven, lay them out so they are quick to review: current value, normal range, prior period, and owner, all on one page. Start in a spreadsheet if that is the tool your team already uses. The important part is the decision and follow-up attached to each number.

  • What to Expect From a Small Business Analytics Project

    A small business analytics project should turn one business question into something your team can use: a report, dashboard, analysis, or improved spreadsheet. Expect three broad stages: define the question, build and review drafts, then hand over the finished work with documentation. You will agree on the scope, provide data and access, review progress, check the numbers, and confirm who maintains the result. A good proposal explains what is included, what counts as done, and what happens after handoff. On my Services page, I describe the process as define the question, analyze and iterate, and hand over with clarity.

    If you are still deciding whether you need outside help, start with When Should a Small Business Hire a Data Analyst? Here, I assume you have chosen someone, or are close to it, and want to know what happens next.

    To make each stage concrete, I will follow a hypothetical project: a service-business owner wants a weekly view of overdue invoices using an invoice export and customer list. The example is an illustration, not a client case study.

    What happens at the start of an analytics project?

    The first step is agreeing on the question the project will answer and what you will receive. Expect a conversation about the decision you need to make, followed by a short written scope that you approve before building starts.

    That conversation should be about your business, not software. What decision will this support? Who will use the output, and how often? Which numbers do you trust? Microsoft’s guidance on planning business intelligence solutions likewise recommends defining the problem collaboratively with the people who will use the result.

    The scope that comes out of that conversation should be short. It should name:

    • The decision the work supports
    • The output: a report, a dashboard, an analysis, or sometimes just a better spreadsheet
    • The data sources involved
    • What is excluded
    • Who is responsible for what, on both sides
    • When you will review progress
    • What counts as done

    Exclusions matter as much as inclusions. They keep a small project small.

    In the late-payment example, the scope might say: Decision: whether to change terms for repeat late payers. Output: an analysis with recommendations and a weekly overdue-invoice view. Sources: the invoice export and customer list. Excluded: disputed invoices and cash-flow forecasting. Checkpoint: review the first findings. Done: the owner can act on the analysis, and the office manager can update the weekly view without help.

    The output does not have to be a new tool. If the scope can be met with software you already have, that is often the simpler answer. “Do You Need a Data Analyst or Better Spreadsheets?” helps you tell the difference. If you are still comparing consultants, questions 1 and 2 in 12 Questions to Ask Before Hiring a Data Consultant cover what to ask about this stage before you sign.

    What data and access will you need to provide?

    You will need to supply the data the question depends on, access to the systems it lives in, and someone who knows how the records work. The consultant cannot guess your business rules, such as when an invoice counts as late, so that knowledge is your most important contribution.

    Expect to be asked for:

    • The reports and spreadsheets you use today, even the messy ones
    • Exports from the source systems, or read-only access to them
    • A named person who knows how the records are entered and what the odd entries mean
    • Your working definitions: what counts as overdue, active, or complete
    • Known problems, such as duplicate customers or a system change partway through the year

    Access should be the minimum the work requires, and read-only is a sensible default. Question 7 in 12 Questions covers what to ask.

    Messy or incomplete data does not necessarily prevent a useful project. If a field is missing or unreliable, record the limitation and work within it, or narrow the question to what the data can support. Data that was never captured cannot be reconstructed as fact.

    The late-payment analysis needs the issue, due, and payment dates for each invoice. If older records lack a due date, the team might start where that field becomes reliable and document the limitation rather than inventing history.

    To check how usable your data is before the project starts, work through The Small Business Data Audit Checklist. It covers where your data lives, who owns it, and whether it can be exported.

    What will you see before the work is finished?

    You should see a rough working version early, before it is polished, so wrong assumptions get caught while they are still cheap to fix. Treat it as a draft for review, not the finished deliverable.

    Analytics work should move in cycles: build part of the solution, check it, adjust, and repeat. Microsoft’s planning guidance recommends iterative development and validation rather than one long build followed by a reveal. A draft may have rough edges; at this stage, a wrong definition matters more than unfinished formatting.

    When a draft arrives, review it against three questions:

    • Is the right information included, with nothing important left out?
    • Do the definitions match how your team actually talks about the business?
    • Can the person who will use it each week understand it without an explanation?

    Name one person to collect feedback from your side. Record each agreed change in writing so both sides know what the next version will include.

    In the late-payment example, the first draft counts payment-plan invoices as late because they passed their original due date. The owner explains that those invoices follow an approved schedule, so the team records a new rule: exclude payment-plan invoices. This is the kind of business context only the client can supply, and it is easier to correct during review than after handoff.

    How long does an analytics project take, and what can change the plan?

    It depends on the scope, how ready your data is, and how quickly your side responds, so be cautious about a firm timeline quoted before anyone has seen your data. A sensible start is a short, bounded first phase with named outputs, followed by estimates for the rest based on what that phase finds.

    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. That is a starting point, not a prediction of the full project timeline.

    After that, ask for estimates tied to milestones in the agreed scope, such as the first draft, the tested version, and handoff, rather than a single end date.

    Plans usually change for predictable reasons:

    • An export you expected does not exist, or lacks a field
    • Two people define the same metric differently, and someone has to decide
    • Feedback on a draft takes longer than planned
    • New requirements appear partway through

    Agree in writing how added requirements affect time and cost. If the owner adds a cash-flow forecast to the late-payment example, it becomes a new request with its own estimate.

    How will you know the numbers are right?

    Agree how the work will be tested before it starts, then check the numbers against records you already trust. A report is not finished until its figures match an agreed baseline and the person who will use it has tried it for real.

    Microsoft’s guidance on validating reports recommends testing data against a known baseline, confirming that features such as refresh work, checking access, and having users test whether the result meets the business need. It also recommends documenting success criteria in advance. For a small business, that becomes four practical tests:

    • Accuracy: the key figures match a source you already trust, for the same date and with the same exclusions
    • Refresh: new data comes through when it should, without manual repair
    • Access: the right people can open it, and nobody else can
    • Use: the person who will rely on it completes their real task with it

    The last test is often called user acceptance testing. In plain English, the people who will use the output confirm it does the job before you accept it.

    In the late-payment example, testing has two parts. The first is the inputs. Check the days-late figure by hand on a handful of invoices, then confirm that the number and value of late invoices for one period match the invoicing system, using the agreed exclusions for disputed and payment-plan invoices.

    The second is the assumptions. If the analysis estimates the cost of late payment, document the financing rate and every other input. Recalculate several invoices by hand and confirm that the formula uses the agreed dates and exclusions.

    If a figure does not match, investigate it. A difference may be acceptable once its cause is understood, documented, and approved; an unexplained difference is not. Keep the test record so you can show how the number was checked later.

    What should you receive at handoff, and who maintains it?

    At handoff you should receive the finished output plus everything needed to run it without the consultant: documentation, metric definitions, operating instructions, and access that you control. Who maintains it afterward should be settled in the proposal, not discovered after launch.

    A useful handoff checklist:

    • The agreed output and its files, stored in accounts your business owns
    • Metric definitions, written in plain language
    • Documented limitations, such as the date range the data covers
    • Operating instructions: how to refresh it, what to check, and what to do if something breaks
    • A walkthrough with the person who will run it, not only the owner
    • Clear ownership and access, including admin rights where needed
    • A support contact, and what support, if any, is included
    • The line between a fix and a new request

    A fix means the output does not do what was agreed. A new request asks it to do something else. Because they may be handled differently, define the boundary in writing.

    Microsoft’s guidance on supporting reports after release calls the period after a major change “hypercare.” It recommends planning for added feedback and clarifying support responsibilities. Your proposal should say who answers questions after handoff and for how long.

    In the late-payment example, the handoff includes the findings, the method and assumptions behind them, and the agreed recommendations. It also includes the weekly overdue-invoice view in the owner’s account, a one-page definitions sheet, update instructions, and a walkthrough with the office manager.

    The proposal also states who handles a fix if the export format changes.

    For the contract side of handoff, including who owns the work, what you keep if the engagement ends, and how post-delivery bugs are handled, see questions 5, 6, and 11 in 12 Questions to Ask Before Hiring a Data Consultant.

    How can you prepare for a useful kickoff?

    Come to the first meeting with one clear business question and the materials behind it. Before kickoff, gather:

    • One business question, written as a decision
    • The reports or spreadsheets you use today
    • A contact for each data source
    • Known data problems
    • One named reviewer from your side
    • How often the decision is made: weekly, monthly, or quarterly

    For the example, bring the question “Which customers pay late, and what does it cost us?”, the current overdue-invoices report, the bookkeeper as source contact, known exceptions such as payment plans, and the office manager as reviewer.

    For more thorough preparation, The Small Business Data Audit Checklist goes further. If you are not sure your business is ready, the five-minute Analytics Health Assessment gives you a starting point before any conversation.

    A well-run project asks the same things of you at every stage: explain the decision, supply the inputs, review the draft, test the numbers against a baseline, and take ownership at handoff.

    Have a reporting problem in mind? Send a short description of the decision you need to make and the systems you use.

    Sources

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

  • Do You Need a Data Analyst or Better Spreadsheets?

    Do You Need a Data Analyst or Better Spreadsheets?

    If your reporting process has already become too slow, fragile, or hard to trust, the first question is not “Which software should we buy?” It is “What is actually broken?” For most small businesses with stable reporting needs, a better spreadsheet is the right first investment—not a full-time data analyst. Analyst support becomes necessary when the remaining problem is judgment: defining measures, investigating changes, and turning results into decisions.

    A better spreadsheet is usually the right first move when your business questions are routine, your source data is reasonably trustworthy, and the main problem is a workbook that takes too long to update or breaks too easily. Analyst help becomes more valuable when the report works, but nobody can define the right measures, investigate changes, or turn the numbers into decisions. If the underlying records or definitions are inconsistent, neither option will work well until that foundation is repaired.

    This article helps you identify which situation you have and choose the smallest investment that addresses it. It does not compare business-intelligence products or cover the full hiring and cost analysis. Cost is another reason to fix the earliest broken layer first. Clutch reports that reviewed business-intelligence and data-analytics projects commonly cost $10,000–$49,999, although project scope varies considerably. For an owner whose reporting questions and business definitions are already clear, hiring broad analytical assistance may add cost before solving the immediate problem. A focused spreadsheet rebuild can be the more practical investment: the owner supplies the domain knowledge, definitions, and decision requirements, while the specialist turns those requirements into a controlled, documented, and repeatable reporting system.

    This sequence also reduces the risk of outsourcing judgment too early. An outside analyst will not initially possess all the operational context held by the owner and employees. That expertise can still be valuable, but it should challenge and clarify the business’s definitions—not replace its knowledge of how the company works. Once the spreadsheet and source data are trustworthy, the owner can see which questions remain unanswered and purchase targeted analysis with a much clearer scope.

    Do you need a data analyst or better spreadsheets?

    Start with the work you need done, not the title of the person or the name of the tool.

    A spreadsheet stores data, applies rules, and repeats calculations. A well-designed Excel or Google Sheets workbook can consolidate inputs, standardize recurring calculations, reduce copy-and-paste work, and produce a dependable management report. It is especially useful when a small group follows a stable process and needs the same answers each week or month.

    An analyst contributes judgment. Analysts help turn an unclear business concern into a testable question, decide which measures matter, challenge definitions, investigate unexpected changes, and explain what the result means for a decision. A spreadsheet can calculate a margin exactly as instructed; it cannot decide whether that definition of margin is appropriate for the decision in front of you.

    That gives you a practical starting rule:

    • Choose a spreadsheet rebuild when the questions and definitions are settled but producing the report is unreliable or labor-intensive.
    • Choose targeted analyst help when the system produces usable numbers, but the business lacks the expertise or ownership to interpret and act on them.
    • Repair the data first when the inputs or definitions are not trustworthy.
    • Use both when more than one layer is broken, but buy only the expertise needed for the current stage rather than committing immediately to a full-time role or a large software migration.

    These are not permanent labels. A spreadsheet may be sufficient for today’s reporting and become inadequate as more teams, decisions, and systems depend on it. The purpose of the diagnostic is to choose the right next step, not to declare one tool universally better.

    Use this three-part diagnostic

    Look for the earliest point at which a reliable answer becomes impossible. Begin with the source records, then examine the reporting workflow, and finally examine how the results are interpreted. The first broken layer is normally the first one to address.

    1. Is it a data or definition problem?

    You have a data problem when the source records or the meaning of important measures cannot be trusted. The issue exists before the information reaches the spreadsheet.

    Common symptoms include:

    • Revenue, customer, inventory, or margin totals disagree across systems or departments.
    • Required fields are often blank, duplicated, mistyped, or recorded in inconsistent formats.
    • Teams use the same label—such as “active customer,” “qualified lead,” or “gross margin”—but calculate it differently.
    • Each reporting cycle begins with a long manual reconciliation before anyone will use the result.

    The first action is to inventory the source systems, agree on definitions, identify who owns each field, and correct the process that creates bad records. A new workbook can expose these problems, and a specialist can help diagnose them, but neither can manufacture trustworthy answers from missing or contradictory inputs.

    If this describes your situation, use the Small Business Data Audit Checklist as the next step. Do not automate a number until you know what it means and where it comes from.

    2. Is it a spreadsheet or workflow problem?

    You have a tooling problem when the underlying records and business rules are understood, but the method of turning them into a report is fragile, slow, or difficult to hand off.

    Common symptoms include:

    • Weekly or monthly reporting requires repeated downloads, copy-and-paste steps, and manual reformatting.
    • Several files are treated as the “master,” and nobody knows which version is current.
    • Formulas regularly break when a column, file name, or input format changes.
    • Only one person knows the update sequence, even though the intended calculations are clear.

    This is the strongest case for improving the spreadsheet before buying a larger platform. Separate raw inputs from calculations and outputs. Standardize the input format. Keep important business rules in one visible place. Add validation and error checks. Automate stable imports where the source supports it. Document the update process so another person can run it.

    The goal is not a more impressive dashboard. It is a reporting process that produces the same result from the same inputs, shows where a number came from, and can survive a routine handoff. A scoped spreadsheet rebuild may be enough; if the workflow still fails after those improvements, you will have much better evidence about what the next system must do.

    3. Is it a skills or ownership problem?

    You have a skills or ownership problem when the source data and reporting workflow are usable, but nobody is accountable for maintaining the logic, investigating changes, or helping leaders interpret the result.

    Common symptoms include:

    • The report arrives on time, but meetings stall at “What does this mean?”
    • Leaders ask new questions, but nobody can translate them into a useful analysis.
    • Unexpected movements are reported without investigation or business context.
    • Ownership is so unclear that definitions and calculations drift as requirements change.

    The first action depends on the size of the gap. A clear internal owner and targeted training may be enough for a stable monthly report. A scoped analyst engagement can help define measures, examine a specific problem, or establish a repeatable review process. Recurring analyst support makes sense when important decisions continually generate questions that the existing team cannot answer alongside its normal work.

    Notice the boundary: one employee being the only person who can refresh a complicated workbook may indicate a tooling and documentation problem. One employee being the only person who can explain why customer retention changed is more likely an expertise problem. The observable failure—not the job title—determines the category.

    What if you recognize all three?

    Mixed cases are common because failures compound. Poor source records create manual cleanup. Manual cleanup makes the workbook fragile. A fragile workbook consumes the time that could have been spent analyzing results.

    Use data, tooling, and analysis as a default sequence, not an inflexible rule. First establish enough shared definitions and source reliability to produce a meaningful result. Next stabilize the recurring reporting workflow. Then decide whether the remaining questions justify ongoing analyst support.

    You may need limited expertise earlier. For example, an analyst can help define a metric, locate the cause of a discrepancy, or design the requirements for a rebuild. That is different from hiring someone into a recurring role before the underlying reporting process is ready. The aim is to use the right expertise at each stage.

    What can better spreadsheets fix—and where do they stop?

    A good spreadsheet is not merely a temporary substitute for “real” analytics software. For a small business with a manageable number of sources, a stable reporting rhythm, and a limited group of users, it can be the appropriate long-term system.

    A well-built workbook can:

    • Bring consistent exports or inputs into one controlled model.
    • Apply documented calculations the same way every reporting period.
    • Separate source data, business logic, and presentation so changes are safer.
    • Flag missing inputs, duplicates, and unexpected values before publication.
    • Produce repeatable summaries and charts for routine decisions.
    • Make the logic visible enough to review, test, and hand to another owner.

    What “better spreadsheets” means in practice

    A rebuild should simplify the reporting process, not merely decorate the existing workbook. Before changing formulas, map the route from each source record to the final number and decide which steps genuinely need to remain manual. Then design the workbook so its structure reflects that route.

    A practical rebuild normally includes:

    • One clearly identified source or input area, with validation rules for the fields people enter.
    • A separate calculation layer so raw records are not mixed with presentation logic.
    • Documented definitions for the measures leaders use, including the owner of each definition.
    • Checks that reveal missing records, duplicate identifiers, broken imports, and unexpected totals.
    • A repeatable refresh process with fewer file copies and fewer steps that depend on memory.
    • A concise output designed around recurring decisions rather than every available metric.

    It should also have an exit criterion. Agree in advance on what “reliable enough” means: how long an update may take, which checks must pass, who signs off on the result, and whether another trained person can run the process. After several reporting cycles, review what still causes delay or confusion. Remaining problems may justify analyst support or a different system; solved problems should not be used to support a larger purchase.

    That does not mean spreadsheets are error-proof. Raymond Panko’s review of spreadsheet research reported high error rates across many audited operational spreadsheets. The paper was published in 2008 and draws substantially on studies from the 1990s and early 2000s, so it should not be treated as a current estimate for every business. Its durable lesson is narrower: complex spreadsheets deserve controls, testing, and review rather than automatic trust.

    Published row or cell limits are rarely the most useful decision test for a small business. You can remain far below a product’s technical ceiling and still have an unsuitable process. The more important limits are operational:

    • Several teams need to update or use the same data at the same time.
    • Permissions must be more precise than sharing an entire workbook allows.
    • Reliable audit history, approvals, or regulatory controls are required.
    • Refreshes must run without a person opening files and performing a sequence of steps.
    • The model depends on many systems, frequent changes, or calculations that are difficult to test.
    • The time spent maintaining the workbook consistently exceeds the value of keeping the process there.

    Those are signs that you may need a shared reporting system or a managed data pipeline. They do not tell you which product to buy. Product selection and migration deserve a separate evaluation of requirements, costs, ownership, and implementation risk.

    Choose the next action that matches your result

    Do not begin with a job description or software demonstration. Begin with the failure you can observe.

    • Data or definition problem: Run a source-data audit. Agree on critical definitions, assign field owners, and repair the process that creates missing or inconsistent records.
    • Spreadsheet or workflow problem: Map the current reporting steps, remove duplicate versions, separate inputs from logic, add checks, and rebuild the recurring workflow before considering a platform migration.
    • Skills or ownership problem: Name an accountable reporting owner. Use targeted training or scoped expert help for the specific questions the team cannot answer.
    • Mixed problem: Fix enough of the data foundation to make the numbers meaningful, stabilize the recurring workflow, and then assess the remaining need for recurring analysis.

    If you are still deciding whether to bring in outside or full-time help, the companion article on when a small business should hire a data analyst covers that threshold and the cost considerations. If the diagnostic points to unreliable inputs, start instead with the Small Business Data Audit Checklist. If a stable spreadsheet can no longer meet your access, control, or refresh requirements, the later guide on moving from spreadsheets to a BI tool will help you evaluate the system decision.

    The cheapest credible fix is the one aimed at the layer that is actually broken. Sometimes that is a cleaner, documented spreadsheet. Sometimes it is a person who can frame and investigate the right questions. Sometimes it is both in sequence. Diagnose first, make the smallest useful change, and reassess after the reporting process is producing information you can trust.

    Source

    Raymond R. Panko, “Spreadsheet Errors: What We Know. What We Think We Can Do” (2008): https://arxiv.org/pdf/0802.3457

  • When Should a Small Business Hire a Data Analyst?

    When Should a Small Business Hire a Data Analyst?

    A small business should hire analytics help when recurring decisions depend on numbers that are slow to assemble, difficult to reconcile, or hard to trust. That does not automatically mean hiring a full-time employee. If the need is periodic or still unclear, freelance, fractional, or part-time help is usually a better first step.

    A full-time analyst makes sense only when the business has enough continuing reporting, analysis, data-quality work, and stakeholder requests to keep one person productively occupied. If the main problem is one fragile spreadsheet, inconsistent data entry, or metrics nobody has defined, fix that foundation first.

    Hire help when the reporting problem is recurring and decision-critical

    The clearest sign that you need analytics help is not that your spreadsheet feels annoying. It is that the same reporting problem keeps returning and affects decisions that matter.

    Use this three-part test:

    1. Meaningful time goes into assembling the numbers each month.
    2. Data from at least two systems must be reconciled by hand.
    3. An important decision depends on getting a reliable answer.

    The decision could involve hiring, inventory, pricing, advertising, staffing, or cash flow. The point is not the size of the spreadsheet. The point is whether unreliable reporting creates a real business constraint.

    All three conditions matter. A complicated report that nobody uses is not a reason to hire. A valuable decision based on clean information from one dependable system may not require an analyst either. Paid help becomes easier to justify when the work is recurring, the data is fragmented, and the answer changes what the business does.

    You may need better spreadsheets before you need an analyst

    Many small businesses do not have an analysis problem yet. They have a process problem.

    Common examples include inconsistent product names, duplicate customer records, changing definitions of revenue, formulas copied incorrectly, and employees maintaining separate versions of the same workbook. An analyst can spend time cleaning these issues, but hiring someone does not automatically prevent them from returning.

    Before paying for analysis, make sure the business has:

    • One owner for each recurring report.
    • Consistent definitions for the metrics people discuss.
    • A dependable process for entering and correcting data.
    • One agreed version of the final report.
    • A clear decision that the report is meant to support.

    If those basics are missing, start with a small data audit and spreadsheet cleanup. Document where the information comes from, who changes it, how often the report is produced, and where manual steps create errors or delays.

    This is the “not yet” answer—not “never.” Better structure may remove the need for outside help, or it may reveal a smaller and more useful project to hire for.

    Match the type of help to how often the work occurs

    The right question is not simply, “Do I need an analyst?” It is, “What level of help matches the work I actually have?”

    Support ModelWhen to Use ItThe Real CommitmentCost Structure
    Internal OwnerThe data lives in one or two simple tools and a documented process exists.The opportunity cost of diverted operational time; requires protected time and strict accountability.Opportunity cost of staff time.
    Fractional / Part-TimeYou have recurring monthly needs (reporting + system growth) but not enough work for a 40-hour week.A predictable monthly retainer; builds long-term context and steady improvement without full-time overhead.Monthly retainer or part-time compensation
    Freelance / ProjectYou need a specific, one-off outcome: a repaired workbook, metric definitions, or a new dashboard.Scoping time and defined handoff; bounded cost tied to a specific, final deliverable.Hourly rate or fixed project fee
    Full-Time AnalystRequests arrive daily, multiple teams depend on the data, and you need a permanent internal owner.The most significant commitment: recruiting, payroll, benefits, management, and long-term career development.Salary, benefits, and recruiting costs

    Make a full-time hire only when the workload needs a permanent owner

    One dashboard is not a full-time job. Neither is a monthly report that takes a few hours to update after the process is cleaned up.

    A sustainable analyst role usually includes a continuing backlog: recurring reports, investigation of changes in performance, requests from multiple teams, data-quality checks, metric governance, documentation, automation, and support for planning decisions.

    Before opening a full-time position, write down the work you expect the person to own during a normal month. Separate the one-time cleanup tasks from recurring responsibilities. If the list is mostly a single project, start with outside help. If the list is substantial, recurring, and important across the business, a permanent role may be appropriate.

    Also ask whether the company is ready to use the analyst’s work. Someone must set priorities, explain business context, grant access to systems, review results, and act on recommendations. Hiring an analyst into a business with no decision process simply creates a new person waiting for direction.

    Frequently asked questions

    What does a data analyst do for a small business?

    They turn data from sales, finance, marketing, and operations into reporting people can act on. In a small business, the early work is rarely modeling or forecasting — it is usually consolidating sources, agreeing on definitions, and replacing manual reporting with something repeatable. The advanced analysis becomes possible only after that foundation exists.

    Should I hire an analyst or a consultant first?

    If the need is project-based or still unclear, start with a scoped freelance or fractional engagement. A short engagement answers the question a job posting cannot: whether there is enough recurring work to justify a permanent role. It also produces something useful either way — cleaner data, defined metrics, a working report — rather than a hire you may need to unwind.

    Who should own reporting if I am not ready to hire?

    Assign one accountable person, usually a lead in operations, finance, or marketing. What matters is not their title but their authority to standardize definitions, set the reporting cadence, and declare which version of a report is final. Reporting that belongs to everyone belongs to no one, and that is the condition that makes reports untrustworthy in the first place.

    What should I prepare before bringing in analytics help?

    Write down four things: the decisions you need to make, the reports you currently rely on, the systems the data lives in, and the time spent each month reconciling it. Note specifically which numbers your team does not trust and why. That short audit turns a vague request into a scoped engagement, and it usually costs a few hours of your time.

    The practical next step is a simple data audit. Choose one important recurring decision, document where its numbers come from, and measure the work required to produce a trusted answer. That evidence will point toward the right next move: process cleanup, project help, recurring part-time support, or a full-time analyst.