One summary for the whole company
The operating summary — how it's built
When a business runs several stores across two channels, everyone has their own "truth" in the numbers. Here's the method we use to bring sales, ads and stock down to a single screen — and why it works in any multi-store business.
Sources load into ClickHouse; on top sit Power Pivot and DAX measures: plan/actual, ACoS with inverted logic, stock consolidation. Below — how the model is built and what recomputes itself when you hit "Refresh".
01 The problem: everyone has their own spreadsheet
Several stores across Shopify and Amazon, each with its own tax setup, several people on the team — and everyone has their own file. The fulfillment lead looks at one, sales at another, the owner asks for a summary someone assembles by hand for two hours out of four web back-offices.
The first half-hour of every discussion goes not to decisions but to arguing whose spreadsheet is freshest. "Let's all open the same report" doesn't work — a single report simply doesn't exist.
02 The principle: one screen, one source of truth
The result of the method is a single file you open in the morning. Hit "Refresh" — in 3–4 minutes everything's in place: sales per store, ads, stock, plans. The home sheet is the operating summary: key metrics by channel, store and period. Turn the filters — this is that screen:
| Metric | Actual | Plan | % to plan | M/M |
|---|
* synthetic data; the structure matches the production model
Green and red in the "% to plan" column are measures, not hand-colored cells. For ACoS the logic is inverted: a decrease is good ▲, an increase is bad ▼. ACoS doesn't rise for happy reasons.
03 The cut by product group
The same metrics — but by brand or category. Pick a group and see how it performs on each channel: orders, ACoS, plan attainment, profit. That picture used to be assembled from three separate spreadsheets.
| Group | Shopify | Amazon | ACoS | % to plan | Profit |
|---|
04 Stock always next to sales
The key trick of the method — stock is visible on the same screen as sales. One metric consolidates the own warehouse, Shopify fulfillment, Amazon FBA, goods in transit and the wholesale warehouse. Sales rise but stock drops 30% in a week — you see it at once, with no separate file.
05 One "Refresh" button
The user presses one button — "Refresh all". No passwords or keys to type, it's all set up. Plans are entered in an online sheet, taxes on a separate tab; on the next refresh everything pulls in by itself. In 3–4 minutes the whole company is looking at one screen again — and the numbers in it agree.
01 Architecture: from API to Power Pivot
The root of "everyone has their own spreadsheet" is that the data lives in different back-offices with different APIs and there's no shared dimension reference. So it all starts not with a report but with a single store: Python scripts pull the raw Shopify, Amazon and warehouse data into ClickHouse on a schedule, normalize it and bring it to common keys.
ClickHouse is the core for volume: tens of millions of transaction and daily-stock rows are processed in seconds on a columnar engine. Power Query on top does only "light" finishing and loads the result into the model — Excel can't carry millions of rows on a sheet, but works beautifully with aggregates from Power Pivot.
02 A single dimension reference
So metrics are computed once and agree everywhere, the facts are joined to dimension references in a star schema. The slicers on the summary sheet filter every measure at once because they hit one dimension, not the columns of a specific table.
Dim_Stores— store × tax regime;Dim_Products— SKU × brand × category × owner;Calendar_Scale— a dynamic calendar (see below);Dim_Channel— Shopify / Amazon / Wholesale.
03 The ACoS measure with an inverted flag
The color in the "% to plan" column isn't per-cell conditional formatting but a measure. For most metrics "above plan = good", but for ACoS the logic is reversed: a rising cost share is bad. The "better" direction is stored right in the metric reference, and the measure compares the sign of the delta against it:
// Delta color accounting for the metric's "better" direction Flag Color = VAR _delta = [Actual] - [Plan] VAR _lessBetter = SELECTEDVALUE( Dim_Metrics[Inverted] ) // 1 for ACoS VAR _good = IF( _lessBetter = 1, _delta < 0, _delta >= 0 ) RETURN IF( _good, "#1f8a5b", "#b23b2e" ) // green / red
The same measure drives the ▲/▼ arrow. One source of truth for the number and its color — so "red" in the summary always means the same thing, in any cut.
04 Stock consolidation by SKU key
Stock in the summary is the sum over five sources, all brought to a single SKU key on the way into ClickHouse. So in the model stock is one measure, not five columns from different exports that have to be "reconciled by hand":
// Avg daily stock: own WH + fulfillment + FBA + transit + wholesale Avg Daily Stock = [Stock Own WH] // OMS + [Stock Shopify Fulfillment] // daily Shopify stock + [Stock Amazon FBA] // daily Amazon stock + [Stock In Transit] // inbound to fulfillment + [Stock Wholesale] // wholesale warehouse
Each term is its own measure over a normalized stock table. Add a new warehouse — add a source in the ETL and one term; all availability analytics picks it up automatically.
05 Plans, taxes, calendar — in references
Everything the business enters is taken out of the logic and into references — the model only reads them:
- Plans — an online sheet (XLSX by a Drive link), row key
code × month × channel × type. Power Query downloads the current file on every refresh. - Taxes — the
Dim_Storessheet: change a rate in one cell and the profit measures recompute for every period and SKU at once. - Calendar — the
Calendar_Scalesheet with "Date from" / "Date to" fields; change a date and Power Query rebuilds the date reference to the right length.
Let's build this summary on your data
In a free review we look at your back-offices and exports and show how to bring them to one screen — with plan, ACoS and stock. No obligation.