Retail, five-store luxury resale retailer selling in store and online
Shopify Retail Analytics on BigQuery: Daily n8n ETL, Metabase
Seven n8n pipelines land Shopify, Meta Ads, Google Ads and Klaviyo data in BigQuery daily, and eight Metabase dashboards read from the modelled views. Accuracy is reconciled against Shopify itself: revenue agrees within 0.3% and inventory within 0.2%. The repository is not public, so the detail available here is the project record rather than published code; no time-saved or revenue-impact figure was measured.
- Our role
- Analytics engineering, ingestion pipelines, warehouse modelling and reconciliation
- Timeline
- Built as a warehouse, model and dashboard layer, with history backfilled behind the daily pipelines
- Sector
- Retail, five-store luxury resale retailer selling in store and online
At a glance
- Sources landed
- Shopify, Meta Ads, Google Ads, Klaviyo
- Ingestion pipelines
- 7: running daily
- Reporting views
- 9: deduplicated
- Dashboards served
- 8: covering 82 saved questions
- Revenue variance vs Shopify
- Within 0.3%
- Inventory variance vs Shopify
- Within 0.2%
- Stack
- n8n, Python, BigQuery, Metabase
What was breaking
Trading data sat in four systems that had no common ground. Shopify held orders, products and inventory across five stores; Meta Ads and Google Ads held spend and campaign performance; Klaviyo held the email programme. Each one has a competent report of its own, and none of them can answer a question that crosses two of them, which is most of the questions worth asking in resale, where a single item is unique and inventory and marketing are the same conversation.
Five stores makes the problem structural rather than inconvenient. Any question about performance needs to be answerable per store and in total, and the two have to agree. Reading five separate exports and adding them up by hand produces a total nobody can reproduce a week later.
There was also no history to work from. Platform reports are strong on the current period and weak on the past, so trend and cohort questions had nowhere to go until the back history existed in one place alongside the daily feed.
No question could cross two systems
Spend, orders, inventory and email engagement each lived in their own tool, so the relationships between them could only be guessed at.
Five stores reported separately
Per-store and total figures were assembled by hand from separate exports, which is slow and not reproducible.
No usable history
Platform reporting covers the current period well; trend and cohort analysis needed the back history landed in one warehouse.
Nothing to check the numbers against
Without a reconciliation against Shopify, a dashboard figure is a claim. In resale, where every unit is unique, inventory accuracy is not a reporting nicety.
What we built
We split the work along the usual line: seven n8n pipelines handle ingestion on a daily schedule, Python handles the backfills, and all of the meaning lives in BigQuery. Keeping transformation out of the pipelines is what makes the model reviewable, a definition that lives in a pipeline step is a definition nobody can read without opening the pipeline.
Every source is landed before it is interpreted. Shopify orders, products and inventory across the five stores, Meta Ads and Google Ads spend and performance, and Klaviyo engagement all arrive as their own tables with their own grain. Nine reporting views then sit on top of that raw layer, each deduplicated, and those are the only objects the dashboards are allowed to read.
Deduplication at the view layer is deliberate rather than defensive. A daily pipeline that re-reads a window to catch late-arriving rows will see the same record more than once, and so will a rerun after a failure. If the raw layer is append-only and the views pick one row per key, both of those are harmless; if the pipeline is responsible for not writing twice, every retry becomes a risk to the numbers. We would rather the ingest be simple and the model be careful.
Reconciliation against Shopify is the measure we handed over on. Revenue agrees within 0.3% and inventory within 0.2%, not because an approximation was acceptable, but because the differences that remain are understood and bounded rather than unexplained. A warehouse that is close to the source and known to be close is usable; one that is exact on the day it shipped and unmeasured afterwards is not.
The serving layer is eight Metabase dashboards covering eighty-two saved questions. Metabase rather than a BI build-out because the questions in a five-store retail business change weekly, and a saved question somebody on the team can clone and edit is worth more than a dashboard only its author can change.
Land raw, model in the warehouse
Pipelines move data and nothing else. Every definition lives in a BigQuery view, where it can be read and reviewed without opening an automation tool.
Deduplicate in the model, not in the pipeline
An append-only raw layer and views that pick one row per key make reruns and overlapping windows safe instead of making them a hazard to the figures.
Reconcile against the system of record
Shopify defines revenue and inventory. The warehouse is measured against it, 0.3% and 0.2%, so the difference is a known quantity rather than an open question.
Per-store and total from one model
Both come out of the same views, so the five stores and the group figure cannot drift apart or be assembled two different ways.
Non-negotiables
- Shopify is the source of truth for revenue and inventory. The warehouse had to be measured against it, not presented as an alternative version of it.
- The daily feed could not depend on a person triggering it, so ingestion, backfill and modelling all had to be scheduled work.
- Per-store and total figures had to come from the same model, so they cannot disagree.
- Pipelines that re-read overlapping windows must not inflate the numbers, so deduplication had to be handled in the model rather than trusted to the sources.
The pipeline
Drawn as text and shapes rather than shipped as an image, so the step labels stay readable and downloadable.
Four sources, one warehouse, eight dashboards
Seven n8n pipelines land each source daily into an append-only raw layer; Python backfills the history behind them; nine deduplicated views are the only objects Metabase reads.
- Data store or CRM
- Trigger
- Processing step
- Branch / filter
- Delivered output
- Primary path
- Flow
- Conditional or exception path
How the work runs
Each stage, what it does, and the specific failure it is built to prevent.
Land each source on its own terms, daily
Seven n8n pipelines read Shopify orders, products and inventory across the five stores, Meta Ads and Google Ads spend and performance, and Klaviyo engagement. Each lands in its own table at the grain the source provides. The pipelines move data and do not transform it, so what arrives can always be compared with what the source shows.
Keeps the ingest simple enough that a failed run can be repeated without reasoning about what it had already changed.
Backfill the history behind the daily feed
A daily pipeline only ever gives you tomorrow's data. Python backfills loaded the back history into the same raw tables, so trend and cohort questions have something to run against from day one. The backfills write to the same place as the daily feed rather than to a parallel set of tables, because two histories eventually disagree.
Deduplicate in the view layer
Nine reporting views sit on the raw layer, each selecting one row per business key. That is what makes an overlapping read window or a repeated run harmless: the raw layer may hold the same order twice, and the view will not. Pushing this into the model rather than the pipeline means retries are safe by default instead of being something to be careful about.
Prevents re-read windows and pipeline reruns from inflating revenue or unit counts.
Model per-store and group figures together
Five stores have to be readable individually and in total, and the two must agree. Both come out of the same views, so there is no separate group-level calculation that can drift from the store-level one. In resale this matters more than in ordinary retail, because inventory is unique per item and a double-counted unit is a real stock error, not a rounding issue.
Reconcile against Shopify before handover
Shopify is the system of record for revenue and inventory, so the warehouse was measured against it rather than presented alongside it. Revenue agrees within 0.3% and inventory within 0.2%. Those are the numbers we handed over on, and they are the ones to re-check after any change to the ingest or the views.
Catches dropped or duplicated rows in the pipelines before a dashboard figure is trusted for a trading decision.
Serve it as dashboards people can edit
Eight Metabase dashboards cover eighty-two saved questions across trading, marketing spend, inventory and email performance. Metabase was chosen so the team can clone a question and change it themselves; in a five-store retail business the questions move faster than a reporting backlog does. The dashboards read only the nine modelled views, never the raw tables.
Restricting dashboards to the view layer stops a one-off chart from quietly introducing its own definition of revenue.
What changed
| Aspect | Before | After |
|---|---|---|
| Cross-system questions | Unanswerable, spend, orders, inventory and email each lived in their own tool. | All four landed daily in one warehouse, so a question can cross them. |
| Five stores | Assembled from separate exports by hand, with no reproducible group figure. | Per-store and total come out of the same nine views, so they cannot disagree. |
| History | Whatever each platform's own reporting retained. | Back history backfilled into the same raw tables as the daily feed. |
| Accuracy | No measurement: a dashboard number was a claim. | Reconciled against Shopify: revenue within 0.3%, inventory within 0.2%. |
| Pipeline reruns | A risk to the figures. | Harmless: the raw layer is append-only and the views select one row per key. |
What it runs on
Every tool listed with the job it actually does in this build.
Data
BigQuery
The warehouse and the place every definition lives, an append-only raw layer per source and the nine deduplicated views the dashboards read.
Orchestration
n8n
Runs the seven daily ingestion pipelines. Chosen for scheduled API ingestion that the client can see and operate, with transformation deliberately kept out of it.
Python
Backfilled the back history into the same raw tables as the daily feed, so trend and cohort questions had data from the start.
Integration
Shopify
Source of orders, products and inventory across the five stores, and the system of record the warehouse is reconciled against.
Meta Ads
Paid social spend and campaign performance, landed alongside trading data so the two can be read together.
Google Ads
Paid search spend and campaign performance, at the source's own grain rather than forced into a shared ad schema.
Klaviyo
Email programme engagement, so campaign activity can be set against orders rather than read in isolation.
Delivery
Metabase
The serving layer: eight dashboards over eighty-two saved questions, reading the modelled views only, and editable by the team.
What stopped being manual
Snippets are illustrative and generic. No client data appears in any of them.
One row per order, whatever the pipeline did
Replaces the need for the ingest to guarantee it never writes the same record twice. The raw layer may hold duplicates from an overlapping window or a rerun; the view the dashboards read cannot.
sql
-- Illustrative. Every reporting view starts from a keyed dedup so a
-- repeated run or an overlapping read window cannot inflate a figure.
CREATE OR REPLACE VIEW model.orders AS
SELECT * EXCEPT (rn) FROM (
SELECT o.*,
ROW_NUMBER() OVER (
PARTITION BY o.store_id, o.order_id
ORDER BY o.ingested_at DESC
) AS rn
FROM raw.shopify_orders AS o
)
WHERE rn = 1;Daily reconciliation against the source of truth
Replaces an eyeball comparison between a dashboard and the Shopify admin. This is the check behind the 0.3% revenue and 0.2% inventory figures, and the first thing to run after any change to the ingest or the views.
sql
-- Variance per store per day, against the figure Shopify reports.
SELECT w.store_id, w.order_date,
w.revenue AS warehouse_revenue,
s.revenue AS shopify_revenue,
SAFE_DIVIDE(w.revenue - s.revenue, s.revenue) AS pct_variance
FROM model.revenue_daily AS w
JOIN raw.shopify_reported_revenue AS s
USING (store_id, order_date)
WHERE ABS(SAFE_DIVIDE(w.revenue - s.revenue, s.revenue)) > 0.005
ORDER BY ABS(pct_variance) DESC;What it was worth
The number that matters here is the variance against Shopify: 0.3% on revenue and 0.2% on inventory. A warehouse is only useful to a trading team if they can stop checking it against the platform, and they will only stop once someone has shown them the size of the gap and where it comes from. That is why the reconciliation, not the dashboard count, is the deliverable we would point at.
The second thing worth noting is where the deduplication lives. Because the raw layer is append-only and the nine views select one row per key, a failed pipeline can simply be run again. That turns the most common operational event in a daily ETL from something requiring care into something requiring nothing, which is most of the reason the daily feed stays correct without supervision.
Eighty-two saved questions across eight dashboards is a sign of what the team does with it rather than a measure of how much was built. In resale, where each item is unique, the useful questions change constantly, and the point of serving through Metabase over a modelled layer is that the team can write the next one without us. We did not measure hours saved or a revenue effect, and we are not going to state one.
Revenue variance against Shopify
Shopify remains the system of record; the warehouse is measured against it rather than replacing it.
Inventory variance against Shopify
In resale each unit is unique, so a stock discrepancy is a real error rather than a rounding artefact.
Daily ingestion pipelines
Covering Shopify, Meta Ads, Google Ads and Klaviyo, with Python backfills behind them.
Deduplicated reporting views
One row per business key, and the only objects the dashboards are allowed to read.
Dashboards and saved questions in Metabase
Across trading, marketing spend, inventory and email performance.
Warehouse refresh
Scheduled, with no manual trigger in the path.
Stores modelled individually and in total
Both figures come from the same views, so they cannot drift apart.
Handed over
- A BigQuery warehouse with an append-only raw layer per source: Shopify, Meta Ads, Google Ads and Klaviyo.
- Seven n8n pipelines landing all four sources daily, with transformation kept out of the pipelines.
- Python backfills loading the back history into the same raw tables as the daily feed.
- Nine deduplicated reporting views, selecting one row per business key, as the only objects the dashboards read.
- Per-store and group modelling from a single set of views across the five stores.
- A reconciliation against Shopify covering revenue and inventory, agreeing within 0.3% and 0.2% respectively.
- Eight Metabase dashboards covering eighty-two saved questions across trading, marketing spend, inventory and email.
Questions about this build
What teams usually ask when they recognise their own process here.
Because it makes the common failure safe instead of dangerous. Daily pipelines re-read overlapping windows to catch late rows, and any pipeline will occasionally be rerun after a failure. If the ingest is responsible for never writing a record twice, every retry is a risk to the revenue figure. With an append-only raw layer and views that select one row per key, the duplicate simply never reaches a dashboard, and a rerun needs no thought at all.
It is the honest position, and a known gap is the thing to aim for rather than a claim of exactness. Revenue agrees within 0.3% and inventory within 0.2%, and the remaining differences are bounded and understood rather than unexplained. A warehouse presented as exact but never measured afterwards is the worse outcome: the first time someone finds a discrepancy, the whole layer stops being trusted.
Because they are good at different jobs. n8n handles scheduled API ingestion and is something the client can see and operate, which matters for a team without a data engineer on staff. But a metric defined inside an automation step is invisible to anyone reading the warehouse, so all nine views and every definition live in BigQuery where they can be read, reviewed and version-controlled.
Because the questions in a five-store resale business change faster than a reporting backlog can be cleared. Eighty-two saved questions exist because the team can clone one and change it themselves. The constraint that keeps that safe is that dashboards read only the nine modelled views, so a new question inherits the agreed definition of revenue rather than inventing its own.
Because the available record for it is a project summary rather than a written architecture document, and the repository is not public. We would rather publish a shorter study than fill the gap with plausible detail we cannot source. The figures quoted (seven pipelines, nine views, eight dashboards, eighty-two questions, and the 0.3% and 0.2% reconciliation) are the ones the project record supports.
Capabilities this used
Have a process that works like this one did?
Book a strategy call. We will walk the workflow with you and say plainly which parts are worth automating and which are not.