Automatic data pipeline to BigQuery one source of truth instead of five exports
Data from the CRM, Google Ads, GA4, the invoicing system and the shop lands in BigQuery on its own, hourly or nightly, in a shape that is ready to query. Revenue, ad cost and margin are counted in one place, by the same formula for everybody. When nothing loads for 25 hours you get an alert, before somebody opens a stale report.
- 47%of newly created records in company systems carry at least one error that affects the work. 75 managers each scored the last 100 records from their own department, Harvard Business Review
- 25 hourshow long data can go without refreshing before the pipeline reports the problem itself, on Slack or by email
- 3 to 4 weeksfrom the workshop to a dashboard that refreshes with no person involved; the first data sits in BigQuery inside the first week
The problem
Why does the weekly report take half a day and still convince nobody?
One person exports a CSV out of the CRM, a second one out of Google Ads, a third pulls invoices out of the accounting system. It all lands in Excel, margins are recalculated by hand, and the file is ready on Friday afternoon, by which time it is already out of date. Revenue in the CRM disagrees with the invoices, the ad platforms report more leads than the CRM holds, and every manager keeps a private spreadsheet with different numbers in it.
The question of what a customer from Google Ads costs takes 2 hours, because the answer sits in four systems that do not talk to each other. A year on year trend does not exist, because a standard GA4 property keeps event data for at most 14 months and nobody exported the CRM on a schedule. The argument about whose number is right runs longer than the decision it was meant to support.
Errors in the data are not an impression. In a study published in Harvard Business Review, 75 managers each reviewed the last 100 records created in their own department. On average 47 percent of those records held at least one error that mattered for the work, and only 3 percent of the collections cleared a basic quality threshold. A pipeline does not fix those errors on its own, but it is the first thing to show them, because it puts side by side data nobody had compared before.
- Half a working day every week spent gluing exports together that nobody verifies anyway
- An unprofitable campaign runs a month longer, because nobody can see its cost per customer
- The board decides on week old data, and on numbers nobody can reproduce
- Sales history ends where GA4 retention ends and where the last saved export was made
Why now
What changed, so that a data warehouse no longer needs an IT department?
Every system that produces business data now has an API or a ready connector: HubSpot, Pipedrive, Google Ads, Meta Ads, Fakturownia, Comarch Optima, WooCommerce, Shopify. GA4 exports events to BigQuery natively, up to 1 million events a day on a standard property, at no charge on the Analytics side. Nobody writes a script per source from scratch any more.
BigQuery took the server off the company. No backups, no patching, no database administrator; Google bills for the data a query processes and for storage, and the first 10 GB of storage and 1 TB of queries a month sit inside the free tier, as the pricing page spells out. A company with a CRM, ad accounts and an ERP counts its queries in gigabytes, not terabytes. A query over 500 million rows needs no different machine than a query over 5 million.
The transformation layer moved from Python to SQL. In the ELT approach, raw data goes into the warehouse first and the views are counted afterwards by scheduled queries, so changing the definition of margin pulls nothing from the sources again. On top of that sits the AI agent: BOND reads the same views and answers the question of which channel has the lowest cost per customer in a plain sentence, in 5 to 10 seconds.
What changes
What does a company get once the data loads itself?
The report is finished before the team starts the day
Connectors pull data hourly or nightly, scheduled queries recount the views, and the dashboard in Looker Studio or Power BI opens with today's numbers. Nobody exports, nobody glues anything together.
Hourly refresh for the CRM and the shop, nightly for ads, invoices and GA4
One definition of margin for the whole company
Revenue, ad cost, cost of goods and margin are counted in a single SQL view, by the same formula for the board, for sales and for marketing. The argument about whose spreadsheet is right is over.
The cost per customer view joins Google Ads spend to won CRM deals on utm_campaign and month
History that does not disappear
The raw layer keeps every record exactly as the source handed it over. A year on year trend, and a recount after the business logic changes, are both possible without fetching anything again.
GA4 event data lives 14 months on a standard property; in BigQuery it lives as long as you want
A pipeline that says when it has stopped
A connector can break after an API change or an expired token, and nobody notices until the monthly review. Here, no fresh data for 25 hours fires an alert on Slack or by email.
A 25 hour threshold, because a nightly export can run tens of minutes late and 24 hours would raise false alarms
No duplicates after a retry
When a connector repeats a sync, records load through a MERGE on a unique key rather than an INSERT. A CRM deal or an invoice exists in the warehouse once, even if the source handed it over three times.
A key per record and MERGE instead of INSERT, verified by a reconciliation test against the source totals
Questions to the data asked in a plain sentence
How many leads came from Google Ads this month against last month, revenue from new customers against returning ones this quarter. The AI agent reads the same views as the dashboard and answers in 5 to 10 seconds, in the browser or on Slack.
The same metric definition in the dashboard and in the agent's answer, because both read one view
Proof
Do we load our own warehouses this way?
Yes. Since March 2026 the data warehouses inside our founders' companies have been fed by AI agents, the same ones behind the figures on our AI for business page. In the agency the agent loads the warehouse itself. Below is the scale of that work and one business number, together with what those numbers do NOT prove.
Built for ourselves. These are not client deployments but implementations inside the founders' own companies. We say so plainly, because proof you cannot verify is not proof.
JustBottheir agentJustIdea Agency, a performance marketing agency of around 30 people
- 23
- people from the company work with the agent
- 38
- systems wired in permanently
- 112
- processes run without a human
- 475
- thousand lines of the agent’s code
The agent is wired into Google Ads, Meta, the CRM, the task system, the SEO tool, Analytics, Search Console and the data warehouse. First commit on 1 March 2026, and 23 people use it today.
8 of 38 systems it works in
Google Ads
Meta Ads- HubSpot
- ActiveCollab
- Ahrefs
GA4
Search Console
BigQuery
- Before
- Data on campaigns, leads and tasks sat in seven systems. Putting it side by side was manual work: export, spreadsheet, glue, and every department looked at its own numbers.
- After
- Warehouse loads and ad account reviews run with no person involved. The cost per lead by campaign is a sentence to the agent, not an export out of two systems.
3,506 data warehouse load operations in 30 days, with no person involved
SurfBottheir agentSurf Inc., premium clothing brand, an online shop and two physical stores
- 16
- people from the company work with the agent
- 21
- systems wired in permanently
- 89
- processes run without a human
- 323
- thousand lines of the agent’s code
The agent is wired into the order system, the ERP, the shop, the data warehouse and the ad platforms. Launched in March 2026, fully in use from May. It is used by 16 people, including the warehouse and the store staff.
8 of 21 systems it works in
PrestaShop- BaseLinker
- Comarch Optima
- BigQuery
- Google Ads
- Meta Ads
- GA4
Zalando
- Before
- From February to August 2025, every unit of ad budget came back as 3.53 units of online revenue, and advertising took 28.3 percent of that revenue.
- After
- In the same months of 2026 the ratio is 4.19, and the share of revenue going to advertising fell to 23.9 percent. The ad budget is 6.9 percent lower with sales 10.3 percent higher. Orders, the ERP and ad spend sit in one warehouse, and the agent reads from it.
Return on ad spend from 3.53 to 4.19, up 18.5 percent, on a budget 6.9 percent lower
And in your company
What if our data is a mess, or sits in an unusual system?
Four sentences we hear most often, and what we answer to each of them.
If you are after a neighbouring piece, there are separate pages for the automatic sales report, for the BigQuery integration, and for AI data analysis.
We have no IT department and nobody who knows SQL.
You do not need one. We build the connectors, the schema and the views, and hand over a dashboard you operate the way you operate Google Analytics: click, filter, export. With it comes a data dictionary where every column has a description, so that six months later nobody is guessing what flag_type_3 means.
We run SQL Server, why would we need BigQuery?
If you have an administrator who keeps the server, the backups and the access rights, and the volume is small, you can stay on it, and that is what we will tell you in the workshop. BigQuery wins where nobody wants to manage a server, where GA4 and Google Ads with their native export are in play, and where you would rather pay for data processed than for machine uptime.
Our data is dirty, we have to clean it first.
The other way round. While the data sits in five systems nobody knows where it is dirty, because nobody puts it side by side. The pipeline loads it raw into one layer, and the reconciliation test shows on day one which system has revenue that disagrees with the invoices. Cleaning starts from a list, not from a hunch.
We have our own database and a system with no API.
A PostgreSQL or MySQL database connects directly. A system with no API hands its data over as a CSV or XLSX export onto FTP or Google Drive, which a connector picks up every night. Webhook, file, or a query against the database: one of those three routes exists in every system we have seen so far.
- CRM and sales
- HubSpot, Pipedrive, Salesforce, LiveSpace, Bitrix24
- Ads and analytics
- Google Ads, Meta Ads, LinkedIn Ads, GA4, Mailchimp, ActiveCampaign
- Finance and ERP
- Fakturownia, iFirma, sevDesk, Xero, Comarch Optima, Subiekt, enova365
- Shop and marketplace
- WooCommerce, Shopify, PrestaShop, BaseLinker, Allegro
- Spreadsheets and databases
- Google Sheets, Excel Online, PostgreSQL, MySQL, CSV files on FTP or Google Drive
- Pipeline and reporting layer
- n8n, Make, Airbyte, Fivetran, Python scripts, BigQuery scheduled queries, dbt, Looker Studio, Power BI
Data sources, what reaches BigQuery from each and how often
The most common set of sources in a B2B company and the way we load them. The frequency is settled in the workshop: hourly where somebody looks at the numbers during the day, nightly where yesterday's state is enough.
| Source | What reaches the raw layer | How often and how |
|---|---|---|
| CRM (HubSpot, Pipedrive, Salesforce, LiveSpace) | deals, stages, owner, value, lead source, stage change dates | hourly over the API, incrementally by modification date |
| Google Ads, Meta Ads, LinkedIn Ads | cost, clicks, conversions per campaign and day | nightly, refetching the last 7 days, because the platforms correct figures retroactively |
| GA4 | events, sessions, traffic sources, transactions | native daily export into BigQuery, no connector needed |
| Invoicing and ERP (Fakturownia, Comarch Optima, Xero, sevDesk) | invoices, line items, payments, cost of goods, counterparties | nightly; from an ERP with no API through a file export |
| Shop (WooCommerce, Shopify, PrestaShop, BaseLinker) | orders, line items, returns, statuses | hourly over the API, or a webhook on status change |
| Google Sheets and Excel Online | budgets, targets, manual corrections, campaign dictionaries | on every edit of the sheet, or nightly |
| Your own database (PostgreSQL, MySQL) | the tables named in the workshop, only the columns the metrics need | nightly, incrementally by key or timestamp |
The rows are a starting point. A first rollout usually covers 2 to 4 sources, and we add the rest over the following weeks without stopping a pipeline that already runs.
How we do it
Five steps from spreadsheet exports to a pipeline in production
Each week ends with something you can see and judge before you decide on the next one.
Workshop and source map
Week 1. We write down every system that produces business data and every spreadsheet somebody keeps on the side. We settle which questions the board asks most often and what an answer costs today. Out of that comes a data schema, a list of 5 to 10 metrics for the first dashboard, and a decision on which sources go first.
Connectors and the schema in BigQuery
Week 2. We stand up two layers: a raw one that takes everything in the shape the source gave it, and a presentation one with the views. We build the connectors: the native GA4 export, Airbyte or Fivetran where a ready connector exists, n8n, Make or a Python script where it does not. The first data sits in BigQuery by the end of this week.
SQL views and reconciliation tests
Week 3. We write the views: cost per customer by campaign, margin per product, revenue per customer, LTV. Every view is checked against the number in the source: the sum of invoices in BigQuery has to equal the sum in the invoicing system. We set MERGE on a key against duplicates, and an alert when the data is older than 25 hours.
Dashboard, data dictionary and handover
Week 4. A dashboard in Looker Studio or Power BI with a board view and a manager view, with access through Google IAM: the board sees margins, a manager sees their own department. We train the team and hand over documentation with the data dictionary. From that day the reports refresh themselves.
Measurement after 30 and 90 days
We check whether anybody still exports by hand, how many freshness alerts fired, and whether the metric definitions survived contact with reality. We add further sources because the team asks for them, not because they were on the plan.
Before and after
How Monday changes for the person who builds the report
Before
- Exports CSV files out of the CRM, Google Ads and the invoicing system, glues them in Excel and recalculates margins by hand
- Explains to the board why revenue in the CRM differs from revenue on the invoices by a few percent
- Answers the question about cost per customer from Google Ads with a promise to come back in the afternoon
- Finds out two weeks later that the ads integration stopped working after a token changed
After
- Opens a dashboard that recounted itself overnight and starts from conclusions, not from exports
- Revenue, invoices and ad cost sit in one view, on one definition signed off in the workshop
- Cost per customer by campaign is a number in a view and an answer from the AI agent in 5 to 10 seconds
- Learns about missing data after 25 hours, from an alert, not from an empty chart in a meeting
First data in BigQuery in week 1. Dashboard in week 4. Metric definitions and refresh frequency are reviewed after 30 days, against the questions the team actually asks.
For the person signing this off
What a finance or operations director gets out of it
The argument about whose spreadsheet is right stops landing on your desk, because you sign off the definition of margin and revenue once, in the workshop, and from then on the same view counts it for everybody. The board report no longer depends on whether the analyst was at work on Friday.
The second thing matters more. You see cost per customer by campaign and margin per product on today's data, not on the month end close. You switch off an unprofitable campaign in the week it stopped paying for itself, rather than after it has burned another budget. That is an argument you do not have today in a conversation about ad spend.
- One definition of every metric, written into a data dictionary, independent of any single person
- Raw history kept in BigQuery, so the year on year trend survives even a change of CRM
- An alert after 25 hours without data instead of an empty chart in a board meeting
- BigQuery and tool costs are paid to the vendors directly, with no commission from us
What it costs
The workshop has a published price, the rollout is quoted after it
The process workshop costs EUR 3,500 net per week. On a data pipeline one week is usually enough, because there are two questions: which systems and which metrics. We quote the rollout after the workshop, once it is clear how many sources are in play, whether they have APIs, how much history has to be loaded, and whether the dashboard is needed straight away or the raw layer is enough to start.
Running costs you pay to the vendors directly, with no commission from us. Google bills BigQuery for the data a query processes and for storage, and the first 10 GB of storage and 1 TB of queries a month sit inside the free tier. The ETL tool, meaning n8n, Make or Airbyte, has its own subscription or runs on your infrastructure. Where the AI agent is in scope, model queries cost the vendor EUR 0.005 to 0.07 each.
Process workshop
EUR 3,500 net
per week, on a data pipeline usually one week
A self-contained piece of work. It ends with a source map, a list of metrics with their definitions, and a plan you can execute without us or with your own IT.
- A map of the systems and spreadsheets that produce business data today
- A list of 5 to 10 metrics with a definition and a source for each
- A recommendation: BigQuery or your current database, ETL or ELT, which connectors
- An estimate of data volume and of the BigQuery cost on the Google side
- Most popular
Workshop and pipeline rollout
Quoted after the workshop
scope follows from the number of sources and metrics
The option most companies choose. A working pipeline with a dashboard within 3 to 4 weeks of the workshop, tested on your own data from recent months.
- Everything in the workshop
- Connectors to 2 to 4 sources, a raw and a presentation layer in BigQuery
- SQL views: cost per customer, margin, revenue per customer, LTV, with reconciliation tests
- A freshness alert after 25 hours, MERGE against duplicates, a data dictionary
- A dashboard in Looker Studio or Power BI, and training for the team
Pipeline and an AI agent for the data
Quoted after the workshop
for teams that ask about numbers a dozen times a day
Everything in the rollout plus BOND, which reads the same views in BigQuery and answers in a plain sentence.
- Everything in the pipeline rollout
- An AI agent wired into BigQuery: cost per customer, margin, revenue from new against returning customers
- An answer in 5 to 10 seconds, in the browser or on Slack
- Anomaly alerts: a campaign above the cost per customer threshold, a drop in product margin
The workshop ends with a document that stands on its own: a source map, metric definitions and a tool recommendation you can take to your own IT or to another supplier. If the workshop shows that the native GA4 export and one spreadsheet wired into Looker Studio are enough, we say so and show you how to set it up.
Frequently asked questions
What is an automatic data pipeline to BigQuery?
It is a set of connectors and scheduled queries that pulls data from the CRM, the ad platforms, the invoicing system and the shop, loads it into BigQuery in raw form, and recounts it into views that are ready to analyse: revenue, margin, cost per customer. It runs hourly or nightly, with no manual exports. The dashboard and the AI agent read the same views, so everybody in the company sees the same number.
How long does a data pipeline rollout take?
From the workshop to a working dashboard is usually 3 to 4 weeks, and the first data sits in BigQuery by the end of the first week. With 8 or more sources, or with complex transformations, we count on 6 to 8 weeks. We do not drag a project out for months: every week ends with something you can look at.
Do we need an IT department or somebody who knows SQL?
No. We build the connectors, the schema and the views, and hand over a dashboard you operate the way you operate Google Analytics, plus documentation with a data dictionary. SQL only becomes useful when you want to add metrics yourselves, and at handover we teach that to one person on your side.
ETL or ELT, which approach do you use?
Usually ELT: raw data goes into BigQuery first, and the transformations run in SQL as scheduled queries. Changing the definition of margin then pulls nothing from the sources again, and the history stays in its original shape. We pick ETL, meaning processing before the load in n8n, Make or Python, for sensitive data that has to be anonymised before it is written down, and for small volumes with simple logic. The <a href="https://docs.n8n.io/" rel="noopener">n8n documentation</a> covers both patterns.
Is data in BigQuery safe and GDPR compliant?
BigQuery encrypts data at rest and in transit, and you manage access through Google IAM at dataset and table level: the board sees margins, a manager sees only their own department. You choose where a dataset lives, so the data can stay in an EU region such as europe-central2 in Warsaw. In the workshop we settle which personal fields the metrics genuinely need and which stay in the source system or reach the warehouse anonymised. Duties as controller under <a href="https://eur-lex.europa.eu/eli/reg/2016/679/oj" rel="noopener">Regulation 2016/679</a> stay with you.
Can we start with one source and add more later?
Yes, and that is usually how we do it. A first rollout covers 2 to 4 sources, most often the CRM and the ad platforms, because that is where cost per customer comes from. The ERP, the shop and the spreadsheets follow over the next weeks, without stopping a running pipeline, because the raw layer takes a new source as a new table rather than a rebuild of the schema.
What about a system with no API, or our own database?
A PostgreSQL or MySQL database connects directly, incrementally by key or timestamp. A system with no API hands its data over as a CSV or XLSX export onto FTP or Google Drive, and a connector picks the file up every night. Older on premise ERP systems are handled exactly this way, because the file is the one interface they all have.
How is a data pipeline different from an integration in Zapier or Make?
Zapier and Make handle operational automation: a lead from a form goes into the CRM, an invoice goes to accounting. A pipeline copies full historical sets into a warehouse so they can be analysed, and it has to cope with millions of records, retries and retroactive corrections. n8n and Make are often part of a pipeline as the fetching layer, but Zapier on its own is not built for loading millions of rows into BigQuery.
Let us find where your data goes missing today
The free consultation takes 30 minutes. You tell us which systems the report is assembled from today and how long it takes, even if the honest answer is half of Friday. We tell you which sources we would connect in the first week, which ones need a file export, and whether BigQuery makes sense for you at all or your current database is enough. You write directly to Pawel, with no reception and no tickets. You do not have to give access to any system at this stage.
We run at most two rollouts at a time, so that someone is actually sitting on each of them.
Book a free consultationPS. If somebody at your company spent half a day this month gluing exports together and the board still asked why the numbers disagree, that is exactly this problem. Data scattered across five systems will not produce one answer until it lands in one place with one definition. That is where the workshop starts.