Blog
Personal and technical writing on small-business analytics, Grain First data modeling, operations, and building CareerTalkLab with AI.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
The Machine is Wrong: Designing AI Feedback Loops for EFL
Why fluent AI corrections can miss a learner’s goal, and how lesson constraints, calibrated evaluation, and reviewed learner feedback can improve EFL tutoring.
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.
Your AI Tutor is Making You Worse at English
You paste your paragraph, the AI fixes it, you copy the result. The AI practiced writing professional English. You practiced copy-paste. Here's why that's a problem.
Why Generic AI is Failing Professional English Learners
Most professionals think ChatGPT as a tutor is a breakthrough. It is — but it has a hidden flaw. Here's what generic AI gets wrong, and how Errorless Teaching fixes it.
Why Your Best Engineers Can’t Explain Their Own Work
Your lead engineer just shipped something that reduced latency by 40%. In the retro, they said: 'I fixed the queries. It's faster now.' That's not a communication failure — it's an invisibility problem.
What is Errorless Teaching — and Why Does It Work for Adult Professionals?
Most language apps let you fail, then correct you. Errorless Teaching flips the model. Here's the science behind it and why it matters for professionals.
Hello, World
A brief introduction to why I'm starting this blog and what you can expect from it.
Building This Site
The technical decisions behind johnserra.com — Next.js, Tailwind, MDX, and a bento grid layout.
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.