Building a mini marketing data warehouse with n8n and BigQuery

A weekly report sent to Slack answers one question: "how did my campaigns perform this week?" But as soon as the question becomes "how has CPA trended over the last six months, by platform and by month?", a Google Sheets file that keeps growing — or a Supabase table designed for transactional logging — quickly shows its limits: aggregations over a large history get slow, and nobody builds a reliable dashboard on a spreadsheet whose shape changes with every campaign. n8n's Google BigQuery node lets you write directly into a real analytical data warehouse, built for exactly this case — and then wire it up to a dashboard that updates itself.

Why a spreadsheet or a transactional database stop being enough after a few months

A study by Raymond, Brisoux and Azami, Une étude empirique des systèmes d'information marketing dans les PME manufacturières (Revue internationale P.M.E., 2000 — see on Google Scholar), based on 54 Quebec SMEs, shows that the quality of marketing-information collection and distribution directly shapes decision quality and, ultimately, company performance. The finding still holds: data scattered across several Google Sheets, several ad accounts and several formats isn't usable, no matter how good the raw numbers are. Centralizing that data into a single, SQL-queryable warehouse is what makes it actually useful for deciding — not just for archiving.

That's exactly the role of a data warehouse like BigQuery: an engine built to scan whole columns across months of history in seconds, where a spreadsheet chokes and a transactional database like Postgres — optimized for row-by-row reads and writes — isn't built for this kind of computation.

The Google BigQuery node in n8n: Insert and Execute Query

The native node offers two operations, on the same split as the Postgres node:

• Insert: appends rows into an existing table, mapping your items' fields onto the BigQuery schema's columns. The simplest path to archive a reporting workflow's output without writing a line of SQL.
• Execute Query: runs any BigQuery SQL — table creation, aggregation, a MERGE for an upsert, a conditional DELETE. Essential as soon as the need goes beyond a simple append.

Credentials require a Google Cloud project with the BigQuery API enabled, and a service account in JSON format — not your personal Google account, for the same least-privilege reasons covered in our guide on securing API credentials in n8n. A BigQuery Data Editor role scoped to the relevant dataset (rather than Owner at the project level) is enough to read and write the workflow's tables.

Modeling a marketing fact table

Before wiring anything up, a simple schema saves you from rebuilding everything later. A classic fact table, partitioned by date so BigQuery only scans the days you actually query:

CREATE TABLE `my_project.marketing.daily_metrics` (
  metric_date DATE,
  platform STRING,      -- "google_ads", "meta_ads", "search_console"
  campaign STRING,
  spend FLOAT64,
  clicks INT64,
  conversions INT64,
  cpa FLOAT64
)
PARTITION BY metric_date;

One row per platform, per campaign and per day: it's the finest grain that stays useful for marketing reporting, and it lets you later run any aggregation (weekly, monthly, across all platforms) without having to re-ingest the source data.

Feeding the table from your existing n8n workflows

If you've already set up the workflow described in our automated Google Ads and Meta Ads reporting guide, adding this is a single node: right after the Code node that consolidates both platforms' metrics into a common format, a Google BigQuery node in Insert mode writes each row into the fact table, alongside (or instead of) the weekly Slack send. Same principle for Search Console data: the API response already returns dated rows, ready to be inserted as-is with platform = "search_console".

For a gradual migration, a one-off export from an existing Google Sheets also works: a Google Sheets read node, followed by a Code node that reshapes the columns to the target schema, then the BigQuery Insert. Useful for backfilling history already accumulated before switching live collection over.

Writing without duplicates: BigQuery's real trap

The Insert operation has no notion of upsert or uniqueness constraint — unlike Postgres, BigQuery doesn't enforce a primary key at the engine level. A workflow replayed twice on the same day silently inserts the same rows twice. The reliable pattern, as with any replayable webhook (see our guide on webhook idempotency), is to treat the write as a replacement rather than a blind append:

DELETE FROM `my_project.marketing.daily_metrics`
WHERE metric_date = @target_date AND platform = @platform;

run via Execute Query right before the Insert, with the current batch's date and platform as parameters. A cleaner alternative for high-volume flows: a single MERGE that combines delete and insert into one atomic query, written directly in free SQL.

Querying: SQL aggregations and a Looker Studio dashboard

Once the table is populated, an aggregation query in an Execute Query node answers in a fraction of a second questions a spreadsheet would struggle to express:

SELECT
  DATE_TRUNC(metric_date, MONTH) AS month,
  platform,
  SUM(spend) AS total_spend,
  SUM(conversions) AS total_conversions,
  SAFE_DIVIDE(SUM(spend), SUM(conversions)) AS avg_cpa
FROM `my_project.marketing.daily_metrics`
GROUP BY month, platform
ORDER BY month DESC;

The result can flow back into n8n (email, Slack, Google Sheets) exactly like any other node output. But the main point of a real warehouse lies elsewhere: Looker Studio, Google's free dashboarding tool, connects natively to BigQuery as a data source. A dashboard built once — spend trends, CPA by platform, month-over-month comparison — then refreshes itself with every n8n insert, with no manual export or file to email around.

What it actually costs

BigQuery has a permanent free tier, outside any trial credit: roughly 10 GB of storage and 1 TB of data processed by queries each month. For a marketing reporting history spanning a handful of ad accounts — a few tens of thousands of rows per year in the schema above — that tier comfortably covers archiving and the usual dashboard queries. Storage beyond that tier runs a few cents per GB per month; it's mostly the volume of data scanned by poorly written queries (a SELECT * on an unpartitioned table, run several times a day) that can drive up the bill. Consistently filtering on the partition column (metric_date) in every query's WHERE clause is the habit that avoids most bad surprises.

BigQuery or Postgres/Supabase: which to pick

Both have a place in the same n8n architecture, and don't replace each other:

• Postgres/Supabase remains the right choice for an audit trail or anything that looks like an application database: individual events, frequent reads and writes, strict relational constraints, the need to update one specific row.
• BigQuery is built for analytics over large historical volumes: aggregations over months or years of data, dashboards querying millions of rows, columns scanned rather than rows fetched one at a time.

A single n8n workflow can perfectly well write the individual event to Supabase for traceability, and its aggregated version to BigQuery for analytics — the two nodes coexist without conflict on the same canvas.

Common pitfalls

• Confusing Insert with upsert: without a prior DELETE or a MERGE, a replayed workflow silently duplicates rows already written.
• Forgetting the partition in queries: an Execute Query with no filter on metric_date scans the whole table on every run, even to fetch a single day.
• Using a service account with an Owner role at the Google Cloud project level instead of a Data Editor role scoped to the marketing dataset.
• Mixing currencies or time zones across platforms before insertion: normalization has to happen in the consolidation Code node, never after the fact in the dashboard.

Going further

This pattern — collect in dedicated workflows, normalize in a Code node, archive in a queryable warehouse, surface through a self-updating dashboard — directly extends the approach behind the audit and summary workflows in the Compliance & Audit Pack (€149), which already logs every sensitive event to Supabase. Adding BigQuery downstream doesn't replace that audit trail: it gives it an analytical layer the transactional format isn't built to provide.