John Serra
Writing & Field Notes

Blog

Personal and technical writing on small-business analytics, Grain First data modeling, operations, and building CareerTalkLab with AI.

Filter by Topic
agentsAIautomationevalsorchestrationtech

Living with AI #1: Loose Prompts, Stale Context, and Bugs Around the Model

Three failures stood out in my AI workflows this week, and none of them called for a different model. Here is what broke across a stale September audit note, a slide parser unit test, and an Android MCP header check, how I verified each fix, and four checks for next week.

Read article
AnalyticsBusiness OperationsData AuditreportingSmall Business Analyticsspreadsheets

Why Counting Rows Can Mislead You: Row Grain vs. Unit of Analysis

Counting spreadsheet rows only works when one row equals one unit of what your question measures. In a 12-row exam booking export, valid pass rates span 41.67%, 55.56%, 50.00%, and 62.50%—here is a 3-step spreadsheet audit to match numerator and denominator before dividing.

Read article
AnalyticsBusiness OperationsData AuditreportingSmall Business Analyticsspreadsheets

How Many Extra Sales Must a Discount Generate?

A 10% discount on a 40% margin item needs 33.33% more units, not 10%, to keep the same gross profit. Divide the discount per unit by the profit left after the discount, then audit any promotion plan against that benchmark.

Read article
AnalyticsData AuditData QualityreportingSmall Business Analyticsspreadsheets

How Much Revenue Depends on Your Largest Customers?

Ranking an invoice export ranks invoices, not customers. Roll sales up to one total per customer inside your reporting window, break ties cleanly, and audit any reported Top-1 or Top-5 share in one spreadsheet tab.

Read article
AnalyticsData AuditData QualityreportingSmall Business Analyticsspreadsheets

Calculate Repeat-Customer Rate from an Order Export

Dividing order rows by order rows gives the wrong repeat-customer rate. Deduplicate customers inside the window, choose within-window or returning, and audit any reported number against both in one spreadsheet tab.

Read article
Data AuditData QualitySmall BusinessSpreadsheet Managementspreadsheets

Hand Over a Report Without Breaking Its Refresh

Sharing a report link does not hand over its refresh. Transfer the source spreadsheet, report, and data source ownership, switch Data credentials to the successor, and test refresh after removing the outgoing owner.

Read article
Data AuditData QualitySmall BusinessSpreadsheet Managementspreadsheets

Dashboard Filters Are Not Row-Level Access Controls

Dashboard filters change what a report displays, not who can access the source data. Use viewer-email filtering or separate per-rep files, then verify access as the recipient.

Read article
Business IntelligenceData QualityreportingSmall Business Analyticsspreadsheets

Why the Dashboard Total Differs from the Spreadsheet

When a Data Studio dashboard total differs from your Google Sheet after refreshing, check filter scope across every metric, replace AVG or SUM of row percentages with a guarded SUM/SUM calculated field, and match COUNT_DISTINCT(order_id) versus COUNT(line_id) grain.

Read article
Business IntelligenceData QualityreportingSmall Business Analyticsspreadsheets

Why Your Data Studio Report Shows Old Data

Check saved edits, the Google Sheets connector Optional Range, report date filters, and the 15-minute query cache in order, then compare the report total, row count, and latest date against a sheet check block.

Read article
Business IntelligenceData QualityreportingSmall Business Analyticsspreadsheets

Build Your First Data Studio Report from Google Sheets

Connect a Google Sheet to Data Studio, build two Scorecards, a monthly Time Series and a category Table, then check every chart against totals calculated in the sheet to catch total rows and out-of-window dates.

Read article
Data AuditData QualitySmall BusinessSpreadsheet Managementspreadsheets

Protect Formula Cells in a Shared Google Sheet

Protect formula cells while keeping inputs editable in Google Sheets and Excel for the web, add a one-column check that flags overwritten formulas, and see where sheet protection stops.

Read article
Data AuditData QualitySmall BusinessSpreadsheet Managementspreadsheets

Did Your Report Include the New Month?

Check whether your monthly report includes the complete new month. Compare source files, row counts, dates, and sales totals against independent controls before accepting a refresh.

Read article
Data AuditData QualitySmall BusinessSpreadsheet Managementspreadsheets

Fix Date Locale Before Building a Monthly Report

Fix day/month interpretation before building a monthly report. Preserve raw dates, apply the source locale, and reconcile both invoice totals and exception rows.

Read article
Data AuditData QualitySmall BusinessSpreadsheet Managementspreadsheets

Find Unmatched Customer IDs with XLOOKUP

Make unmatched customer IDs visible with XLOOKUP, diagnose spaces and missing records, and reconcile both exception dollars and row counts before approving a report.

Read article
Data AuditData QualitySmall BusinessSpreadsheet Managementspreadsheets

Why Merging Tables Inflates Your Sales Total

Repeated lookup keys can multiply orders and overstate sales. Test key uniqueness, choose a business-rule filter or aggregation, then reconcile totals and each order.

Read article
Data AuditData QualitySmall BusinessSpreadsheet Managementspreadsheets

Append vs Merge: How Should You Combine Tables?

Append stacks the same kind of row; merge adds details through a key. Choose the right method and check rows, columns, numeric sales counts and totals afterward.

Read article
Data AuditData QualitySmall BusinessSpreadsheet Managementspreadsheets

Combine Monthly CSV Files with Power Query

Combine monthly CSV exports with Power Query, preserve leading-zero codes, and check each file’s row count and sales total after every refresh.

Read article
Data AuditData QualitySmall BusinessSpreadsheet Managementspreadsheets

Use Dropdowns to Keep Spreadsheet Categories Consistent

A category dropdown stops typos, and prevents the list from outgrowing its range. It does not correct old entries or prevent pastes. Set it up in Google Sheets and Excel, then add an audit column and a reconciliation check so no expense drops out of your category totals.

Read article
reportingSmall BusinessSmall Business Analyticsspreadsheets

Use a Rolling Average to Read Sales Trends

A guarded 3-month rolling average formula for Excel and Google Sheets that blocks partial, blank and non-consecutive windows, and the one piece of arithmetic that explains why the average can rise while sales fall.

Read article
Business OperationsSmall BusinessSmall Business Analyticsspreadsheets

Set a Provisional KPI Target Without Historical Data

Set a provisional KPI target using break-even costs and delivery capacity, then review three closed months before approving your next target.

Read article
reportingSmall BusinessSpreadsheet Managementspreadsheets

Budget vs Actual: Explain the Variance Correctly

Build a budget vs. actual worksheet in Excel or Google Sheets that reconciles to profit, flags bad inputs, and separates order-count and average-order-value changes.

Read article
reportingSmall BusinessSpreadsheet Managementspreadsheets

Why You Should Not Average Margin Percentages

Calculate overall gross margin correctly in Excel and Google Sheets. See why averaging percentages gives 55% when the business earned 25.90%, and how to fix it.

Read article
reportingSmall BusinessSpreadsheet Managementspreadsheets

Percentage Change vs. Percentage Points in Business Metrics

Use percentage change for dollar and unit totals, and percentage points (pp) for metrics that are already percentages. Here are the exact Excel and Google Sheets formulas and custom number formats.

Read article
reportingSmall BusinessSpreadsheet Managementspreadsheets

Compare Monthly Sales When Trading Days Differ

Compare monthly sales fairly with sales per open day. Count actual trading days in Excel or Google Sheets, handle closures, and separate calendar effects from daily averages.

Read article
Data ManagementData QualitySpreadsheet Managementspreadsheets

Build a Monthly Sales Pivot Table in Google Sheets

Group sales by year and month in Google Sheets, include new rows in your pivot table, and check the source total against the pivot to keep monthly reporting reconciled.

Read article
Data ManagementData QualitySpreadsheet Managementspreadsheets

Build a Small Data Dictionary for Your Spreadsheet

Build a spreadsheet data dictionary with nine fields, use dropdowns to guide entries, audit category values, and assign ownership so definitions and approved choices stay current.

Read article
data analysisreportingSmall Businessspreadsheets

Blank or Zero? Why Missing Values Change Your Average

See how blanks, zeros and empty-string formulas change spreadsheet averages, and report missing sales without treating unknown values as zero.

Read article
Data AuditData QualitySmall Businessspreadsheets

Duplicate Sales Rows or Legitimate Order Lines?

Repeated order IDs can be legitimate line items. Use a line-level key, confirm duplicates against the source, and reconcile every row before deleting.

Read article
Data AuditData QualitySmall BusinessSpreadsheet Managementspreadsheets

A Repeatable Workflow for Cleaning Business Spreadsheet Data

Keep an untouched export, log cleaning changes and exclusions, and use totals plus source-row checks to prove that no business records disappeared.

Read article
Data QualitySmall BusinessSpreadsheet Managementspreadsheets

Import a CSV into Excel Without Losing Leading Zeros

Import CSV identifiers into Excel as text, preserve leading zeros and long account numbers, and verify the result with nine exact checks.

Read article
Data QualitySmall BusinessSpreadsheet Managementspreadsheets

What Does One Row Represent in Your Sales Spreadsheet?

Learn what one row represents in a sales export, avoid double-counting order totals, and check order-line grain before calculating sales or averages.

Read article
Business IntelligenceSmall BusinessSmall Business Analytics

Data Studio (Formerly Looker Studio) vs Power BI vs Tableau for Small Business

A plain comparison of Data Studio (formerly Looker Studio), Power BI, and Tableau: what each costs for a small team, where the viewer fees land, and how to choose.

Read article
Business IntelligenceSmall BusinessSmall Business Analytics

Is Data Studio (Formerly Looker Studio) Free for Small Business?

Google's reporting tool is free to build and share. Here is what Pro, outside connectors, and BigQuery can add, and how to run a useful dashboard at $0.

Read article
Business IntelligenceSmall BusinessSmall Business Analytics

Is Power BI Worth It for a Small Business?

Power BI is inexpensive to build with, but sharing takes paid licenses for viewers. Here is what it costs, where it fits a Microsoft shop, and when to skip it.

Read article
Business IntelligenceSmall BusinessSmall Business Analyticsspreadsheets

When to Move From Spreadsheets to a BI Tool

Five specific signs that it's time to move from spreadsheets to a BI tool, what to try first for each, and what a new tool won't fix.

Read article
AISmall BusinessSmall Business Analytics

Business Data You Should Never Give an AI Tool

Four kinds of business data to keep out of unapproved AI tools, safer ways to ask for help, and a short policy your team can adapt.

Read article
AISmall BusinessSmall Business Analytics

Is It Safe to Upload Business Data to ChatGPT?

Whether it's safe depends on the account, the settings, and the data. A plain-English guide to personal vs. business plans, turning off training, redacting first, and three rules for a small team.

Read article
Small BusinessSmall Business AnalyticsSpreadsheet Managementspreadsheets

How to Track Inventory in Google Sheets

Build an inventory tracker in Google Sheets with three tabs: an item list, a log of every stock movement, and a stock tab that calculates balances and flags what to reorder.

Read article
automationSmall BusinessSmall Business AnalyticsSpreadsheet Managementspreadsheets

How to Get Alerted When Your Numbers Change

Decide which numbers deserve an alert, how to set the line for each, what every alert should say, and how to prune the list without hiding real risks.

Read article
automationSmall BusinessSmall Business AnalyticsSpreadsheet Managementspreadsheets

How to Set Up Automatic Alerts in Google Sheets

Four ways to get Google Sheets to warn you: notification settings, conditional notifications, color rules, and a short Apps Script that emails you once when a number crosses your minimum.

Read article
Business OperationsSmall BusinessSmall Business AnalyticsSpreadsheet Managementspreadsheets

How to Build a Cash Flow Tracker in Google Sheets

Build a 13-week cash flow tracker in Google Sheets: the row layout, the formulas, a minimum-cash warning, and a 15-minute weekly update.

Read article
AnalyticsBusiness Operationsdata analysis

Revenue Per Available Parking Space, Explained

Calculate revenue per available parking space, handle capacity changes, and separate occupancy and realized yield when interpreting a change.

Read article
AnalyticsBusiness Operationsdata analysis

How to Measure Parking Facility Occupancy

Calculate parking occupancy with a defined space count, validate the inputs, and build enough observation history to report measured peaks responsibly.

Read article
AnalyticsBusiness Operationsdata analysis

The KPIs Every Parking Lot Operator Should Track

Six parking KPIs and the data each requires: peak occupancy, hours near capacity, revenue per available space, rate mix, cost per space, and payment capture.

Read article
AnalyticsBusiness Operationsdata analysis

Data Analytics for Property Management Companies

Use five property metrics to spot occupancy, collections, work order, vacancy, and controllable expense issues before deciding what to change.

Read article
AnalyticsSmall BusinessSmall Business Analytics

How to Track Where Customers Came From for Free

Use tagged campaign links, analytics referrers, a “how did you hear” question, and one spreadsheet to make a practical judgment about where leads came from.

Read article
AnalyticsSmall BusinessSmall Business Analytics

Google Analytics 4 Explained for Business Owners

Learn the GA4 reports a small business owner needs: Traffic acquisition and Pages and screens, plus a check that valuable actions are recorded as key events.

Read article
AnalyticsBusiness Operationsdata analysisSmall BusinessSmall Business Analytics

Which Website Metrics Matter for a Small Business

Choose four to seven website metrics based on what your site does. Inquiry sites should measure conversions, traffic source, landing pages, engagement, cost per inquiry, and speed. Product sites need activation and retention.

Read article
AnalyticsBusiness IntelligenceData StrategySmall BusinessSmall Business Analytics

How Much Should a Small Business Spend on Data Tools

Start with the data tools your business already has. Learn when sharing licenses, connectors, or a larger platform justify their full cost.

Read article
AnalyticsBusiness IntelligenceBusiness OperationsreportingSmall BusinessSmall Business Analytics

A Monthly Reporting Pack Template for Small Business

A five-page monthly reporting pack: summary and core metrics, financials against budget, operations and team, pipeline, and decisions. Copy the page outlines and update them each month.

Read article
AnalyticsBusiness IntelligenceBusiness OperationsreportingSmall BusinessSmall Business Analytics

What to Include in a Monthly Business Report

A monthly business report needs six things: core metrics, financials against budget, operations, pipeline, team capacity, and decisions. Here's the checklist, and what to leave out.

Read article
AnalyticsBusiness IntelligenceBusiness OperationsSmall BusinessSmall Business Analytics

How Many Charts Should a Dashboard Have?

There’s no fixed number of charts for a dashboard. Use the one-screen test, give every chart a question, and lay the screen out in three parts: cards, trends, and breakdowns.

Read article
AnalyticsBusiness OperationsData ManagementData QualityProcess ImprovementSmall BusinessSmall Business Analytics

Why Your Business Numbers Don’t Match Across Systems

Why your payment processor, bank, and accounting software show different totals, and a monthly reconciliation bridge that shows how the numbers connect.

Read article
AnalyticsBusiness IntelligenceBusiness OperationsreportingSmall BusinessSmall Business AnalyticsSpreadsheet Management

How to Build a Monthly KPI Scorecard

Build a monthly KPI scorecard in Google Sheets or Excel: a four-layer workbook, formulas for current and year-to-date results, targets from your own history, and a repeatable month-end routine.

Read article
AnalyticsAnalytics HiringBusiness IntelligenceBusiness OperationsData ConsultantSmall BusinessSmall Business Analytics

How Much Does a Small Business Dashboard Cost?

Why there is no single price for a business dashboard, and what drives it: how clean your data is, how many systems feed it, whether it updates itself, and how many metrics you ask for. Plus what a comparable quote should contain.

Read article
AnalyticsBusiness IntelligenceBusiness OperationsreportingSmall BusinessSmall Business Analytics

KPIs for a Small Professional Services Firm

Five KPIs for a small professional services firm: utilization, realization, project margin, DSO, and pipeline—plus plain formulas and the warning each one gives you.

Read article
AnalyticsBusiness IntelligenceBusiness OperationsProcess ImprovementreportingSmall BusinessSmall Business Analytics

How to Get Your Team to Actually Use Your Reports

Reports get used when they end in decisions. Use a one-page summary, a short off-track note, a weekly meeting, and an action log that checks whether anything happened.

Read article
AnalyticsBusiness IntelligenceBusiness OperationsSmall BusinessSmall Business Analytics

Why Nobody Looks at Your Dashboard (and How to Fix It)

When a team stops opening the dashboard, the cause is usually trust, relevance, missing actions, friction, or leadership. How to tell which one you have and where to start.

Read article
AnalyticsBusiness IntelligenceBusiness OperationsSmall BusinessSmall Business Analytics

7 Dashboard Mistakes Small Businesses Make

Seven common dashboard mistakes that make small business dashboards hard to use, how to recognize each one, and a quick check for your own.

Read article
AnalyticsBusiness IntelligenceBusiness OperationsSmall BusinessSmall Business Analytics

How to Improve a Business Dashboard You Already Have

Before you rebuild a cluttered dashboard, check whether it can be fixed. Use this three-question check and four-step renovation plan.

Read article
AnalyticsBusiness IntelligenceBusiness OperationsSmall BusinessSmall Business Analytics

How Many KPIs Should a Small Business Track?

Most small businesses should track four to seven KPIs. Here is a three-question test for which metrics earn a place, what to do with the rest, and starting scorecards for services, manufacturing, and restaurants.

Read article
Business OperationsData AuditData ManagementSmall Business AnalyticsSpreadsheet Management

The Business Data You Already Have (No New Tools)

Unlock the hidden value in your small business data without buying expensive new software. Learn how to extract and analyze existing records from your accounting, POS, banking, and scheduling tools using just a simple spreadsheet.

Read article
Analytics HiringBusiness IntelligenceData ConsultantData StrategySmall Business Analytics

What to Expect From a Small Business Analytics Project

What happens after you hire analytics help: scope, data, drafts, timeline, testing, and handoff, plus what you need to do at each stage.

Read article
Analytics HiringBusiness IntelligenceData AuditData ConsultantData SecurityData StrategySmall Business Analytics

12 Questions to Ask Before Hiring a Data Consultant

Don't hire a data consultant without asking these 12 critical questions. Protect your business by clarifying deliverables, data security, IP ownership, and fixed-price scoping from day one.

Read article
AnalyticsBusiness OperationsData AuditData ManagementData QualityData UsabilityProcess ImprovementSmall Business

The Small Business Data Audit Checklist

A step-by-step checklist to help small businesses map their data sources, test usability, and fix critical errors so their records are decision-ready.

Read article
Business Intelligencedata analysisreportingspreadsheets

Do You Need a Data Analyst or Better Spreadsheets?

Small businesses experiencing reporting failures should identify the root cause before investing in new software or full-time data analysts. Determining whether issues stem from untrustworthy data, fragile spreadsheet workflows, or a lack of analytical judgment allows companies to target the smallest, most effective solution first.

Read article
Analytics HiringBusiness IntelligenceData AnalystData StrategyFractional AnalyticsSmall Business AnalyticsSpreadsheet Management

When Should a Small Business Hire a Data Analyst?

Learn when a small business should hire a data analyst—and when spreadsheet cleanup, freelance help, or fractional support is the better choice.

Read article
agencyAImulti-trackorchestrationproductivity

The Architect’s Leap: Orchestrating Agency in a Multi-Track World

Agency isn't about working harder — it's about shrinking the distance between intention and execution. How I built an orchestration layer to run two demanding roles and a startup in parallel.

Read article
personalwriting

Hello, World

A brief introduction to why I'm starting this blog and what you can expect from it.

Read article
designnext.jstech

Building This Site

The technical decisions behind johnserra.com — Next.js, Tailwind, MDX, and a bento grid layout.

Read article
Email Notifications

Get notified when new Practical Analytics articles are published.

Field notes on table grain, trustworthy KPIs, and AI-assisted data workflows for small and medium businesses. Sent only when a new guide goes live.

No spam or automated drip funnels. Unsubscribe anytime.