Why Don't Your Ecommerce Sales Numbers Match?

The first week inside a brand I’ve bought, I ask for one number. What did we do in revenue last month.

I’ve never once gotten one number.

I get two. Sometimes three, if someone thinks to mention the tax return.

And there’s always a pause first, while they decide which one to give me.

That pause is what I’m listening for. It tells me nobody in the business has ever settled what a sale is, which means every decision made from these numbers has been made on a definition that shifts depending on who you asked.

None of those numbers is wrong. That is the part worth understanding, and it is why this never gets fixed on its own.

Why do your systems report different revenue?

Start with the simplest question in your business. Is shipping revenue?

A customer pays you $48 for product and $7 to ship it. Your store reports $55. Your P&L probably shows $48, with the $7 sitting against fulfilment cost. Both are defensible.

But if your ad buyer is calculating return on $55 while your bookkeeper reports $48, two people are optimizing against different denominators and neither of them knows it.

Now do that four more times.

Sales tax was never yours. You collected it on behalf of the state and you owe it back. It belongs on your balance sheet as a liability, not in income. It also sits inside plenty of platform and marketplace totals, so it inflates any headline number nobody has taken apart.

A gift card sale is not revenue. Money arrived, but what you created was an obligation. The revenue happens later, when someone redeems it. Count the sale and then count the redemption and you have booked the same dollar twice.

Discounts change which headline you quote. Shopify will show you gross sales, then discounts, then returns, then net sales, then a total that adds shipping and tax back on. Those are all on the same screen. Most of us quote whichever one we happened to be looking at when someone asked.

Gross sales is looser than it sounds, too. It’s list price times quantity, and Shopify counts pending, cancelled and unpaid orders in it. The biggest number on the screen includes money that was never going to arrive.

Refunds move backwards. Some systems subtract a refund from the month it was issued. Others reach back and reduce the month of the original order. Same refund, two different Novembers, and a quarter you already reported changes after the fact.

Amazon money arrives net. Settlements reach you after Amazon has taken referral and fulfilment fees, so revenue on that channel is a different animal from revenue on your store until you gross it back up. Put the two side by side without doing that and your best channel can look like your worst.

Ad platforms count conversions they influenced, not orders you received. Run Meta and Google and TikTok at once and their reported revenue can sum to more than you sold. Each is telling the truth about its own contribution, as it defines contribution.

Not one of these systems is behaving badly. Each is correct for the job it was built to do. Shopify is a store. QuickBooks is a tax record. Meta is an ad platform.

You are the only one asking a question that crosses all three. And you are the only one who gets to decide what the answer should mean.

What is a data warehouse, in plain terms?

A data warehouse is a place your data goes to stop being trapped in separate apps.

That’s it. There’s no dashboard, and there’s nothing you log into on a Tuesday. Every night, a copy of what each system knows lands in one place: your orders, your settlements, your refunds, your ad spend, your product costs. For the first time, the whole picture exists somewhere.

Three things about how it’s built are worth understanding, because they’re the difference between a warehouse that earns trust and a folder of spreadsheets that doesn’t.

The raw layer never gets edited. The first thing that lands is an exact copy of what each system said, untouched. No cleanup, no corrections, no helpful adjustments. That feels wrong to most people the first time they hear it. It’s the most important rule in the building. When a number looks off six months from now, you have to be able to walk back to what Shopify reported that day. If somebody tidied it on the way in, you’re guessing.

The model layer is where your definitions live. More on this below, because it’s the part that does the real work.

Everything downstream reads from the models. Your Monday email, your margin table, your inventory forecast, a plain-English question you ask an AI about last week. They all pull from the same modeled tables, which is why they agree with each other.

What is BigQuery?

BigQuery is the warehouse I use. Google runs it. What makes it different from the databases you already have is the whole reason this works at your size.

Every app you run sits on a database. Shopify has one. Your books have one. Those are built for transactions: take an order in fifty milliseconds while four hundred people are checking out, find order 4821 and mark it shipped, do that a few thousand times a second without dropping anything.

They are built to handle one record at a time, fast.

Ask that kind of database to add up three years of orders grouped by product and it will do it slowly, or badly, or refuse. That’s by design. It’s also why the reporting inside your store keeps hitting a wall you can feel.

BigQuery is built the other way around. It answers one question across everything at once. Three years, every order, every line, grouped and summed, in seconds. It reads only the columns your question touches, which is how it covers hundreds of millions of rows without straining.

Two practical consequences.

There’s no server. Nothing runs while you aren’t asking. You don’t size a machine, patch it, or pay for it to sit idle overnight. You ask a question, something wakes up, answers, and goes back to sleep.

That’s why the cost is what it is. You pay to store the data, which is pennies per gigabyte per month, and you pay for the data your questions read. A brand doing a few million dollars has a few gigabytes of order history and asks questions a handful of times a day. That’s a few dollars a month rather than a server bill.

The mental shift worth making: it’s where your questions go.

What is a semantic layer?

This is the piece that usually gets skipped.

You’ve signed contracts. Every serious contract opens with a definitions section: in this agreement, “Revenue” means… Everything after that clause inherits the definition. It exists so that two parties can’t argue later about what a word meant.

A semantic layer is the definitions section for your business.

It’s where you write down, once and precisely, what net revenue means. What a new customer is. Whether shipping counts. When a refund lands. What contribution margin includes and what it leaves out. Then every report in the company inherits those definitions rather than each analyst reconstructing them from memory.

Without one, your definitions still exist. They’re just scattered across a dozen spreadsheets, three tools, and two people’s heads, and no two of them are identical. That is the actual condition of most brands doing a few million dollars, and it’s why the pause happens when I ask for a number.

How do models turn a definition into a number?

A definition in a document is an intention. A model is that definition written as instructions the computer applies every time, to every row, without anyone remembering to.

Say you decide net revenue means: product price, minus discounts, minus refunds allocated back to the original order, excluding shipping, excluding tax, with gift card sales held out until redemption.

That’s a paragraph. A model turns that paragraph into a table where every one of those rules has already been applied. The result is a column called net revenue that means exactly one thing, everywhere, forever.

Three consequences, and they’re bigger than they look.

The definition stops being a conversation. Nobody recalculates revenue for a board deck. They read the column. The argument was had once, deliberately, by you.

Changing your mind rewrites history correctly. Decide two years in that shipping shouldn’t have been revenue, and you change the rule in one place. Every number recomputes, including every historical month, consistently. In a world of spreadsheets that same decision means finding and reworking forty files, and you will miss some.

The definitions have a history of their own. These models live in version control, the same way software does. You can see when a rule changed, what it was before, and why. When your accountant asks why Q2 moved, there’s an answer with a date on it.

This is also where AI stops being a confident liar. Point an AI at raw exports and it will happily add a column called revenue that includes tax, because it has no way to know your intent. Point it at modeled tables where the definitions are already applied, and its answers inherit the same truth every report uses. I have AI write most of the SQL in my models. I design the models, and I verify the work. That order matters.

What does this cost?

For brands up to roughly $10M in revenue, under $50 a month in platform costs.

The data-loading tool is most of it. The warehouse itself runs a few dollars, because you’re storing a few gigabytes and asking it questions a handful of times a day. The modeling layer is free.

Ten years ago this was a six-figure project with a consultant attached, and that’s still the number in most owners’ heads. The technology got cheap and nobody sent an announcement.

What happens when a buyer looks at your numbers?

Brands get bought on numbers.

When I’m looking at buying one, the first thing I do is try to reconcile the same sources against each other. What I find moves what I’m willing to pay.

Nobody lied to me. The definitions never got settled, so a stranger settles them during diligence, and a stranger settles them conservatively. The seller then spends six weeks assembling in a panic what should have been a standing report.

If a buyer asked you for last month’s revenue tomorrow, which system would you open? That answer is worth knowing long before anyone asks.

What do you need to do this yourself?

Less than you’d expect, and the hard part is not technical.

You need somewhere for the data to land, something to load it on a schedule, and a place to write the models. You need product costs you trust, which for most brands is the real project and the reason this gets postponed. And you need to make roughly a dozen decisions about what your words mean, which nobody can make for you because they’re judgment calls about your business, not questions with correct answers.

Simple is not the same as easy. The tooling is a weekend. The definitions are the work.

The bottom line

Your dashboards disagree because each was built to answer a different question, and each answers its own question correctly. That never resolves itself, because nothing is broken.

What changes it is deciding what your words mean, writing those decisions down in a form a computer applies to every row, and putting them somewhere every number in the business comes from.

The pause I listen for in the first week isn’t about the number. It’s about whether anyone ever decided.