Telecommunications, national internet and TV provider, reported on for a marketing agency's AI division
AI Reporting Agent on BigQuery: Reconciled Data Layer, 9 Tools
The agent, the Cloud Run services, the scheduled queries and the n8n gateway all run live against the client's own data. Reconciliation is documented: all 168 national and market periods tie out, 8 of 10 figures from published weekly reports matched exactly and the remaining two differed by 1 and 3 sales, and 8 of 8 figures from the team's weekly Python script were reproduced in SQL. Client names, identifiers and financial figures are withheld; no time-saved or cost figure was measured.
- Our role
- Analytics engineering, BigQuery data layer, reconciliation programme and the agent's tool surface
- Timeline
- Build, reconciliation and handover, delivered in flows across the sales, digital and reporting modules
- Sector
- Telecommunications, national internet and TV provider, reported on for a marketing agency's AI division
At a glance
- Reconciled periods
- 168 of 168 national vs market totals
- Agent surface
- 9 tools, 20 written rules
- Geography levels
- 44 plazas, 83 districts, 9 commercial regions
- Period types
- 6: plus any custom range
- Reporting cut
- 6 to 20 seconds, chart and analysis text included
- Client objects edited or deleted
- 0: built alongside the existing layer
- Stack
- BigQuery, Cloud Run, Cloud Scheduler, Vertex AI Agent Engine, Gemini, GA4 Data API, n8n
What was breaking
The reporting team's primary sales source was a Google Sheet maintained for people to read. There was no Salesforce API available to us, the dates lived in the header row rather than in a date column, and the layout carried meaning that a naive import destroys. Every attempt to treat it as a table had to start by reading the header to find out what each column was a date for.
On top of that sat the manual work. The daily and weekly sales update, the cut leadership actually reads, was assembled by hand each time: pull the figures, build the chart in the team's format, write the commentary. That is slow, it is unrepeatable, and it means the shape of the number depends on who built it that morning.
The digital numbers had a correctness problem rather than a labour problem. The existing export ran one to two percent above what the Analytics screen showed for sessions and active users. A one percent gap is small enough to survive review and large enough to make every digital figure arguable, which is the worst combination: nobody trusts the number and nobody can prove it wrong.
The geography made all of it harder. The business is reported at three levels (44 plazas, 83 districts and 9 commercial regions), and these are not a clean hierarchy that can be summed upward. Adding them together produces a plausible, wrong total. Any layer serving this data has to keep the levels apart by construction, because the obvious mistake is also the easy one.
And underneath everything, the question that decides whether an AI agent is useful here at all: if the model is the thing adding up the sales, then every answer is a new opportunity for arithmetic that looks right. For a national total that goes to leadership, a confidently wrong number is worse than no number.
The daily and weekly cut was hand-built every time
Figures, chart and commentary assembled manually, so the output varied with whoever produced it and could not be reproduced later.
Digital figures disagreed with the Analytics screen
The existing export ran 1 to 2% above GA4 for sessions and active users, which left every digital number open to challenge.
The sales source was a human-formatted sheet
No Salesforce API was available, and the dates sat in the header row, so any import that ignored the layout silently mislabelled periods.
Three geography levels invited a wrong total
Plazas, districts and regions are reported separately and must never be added together, but nothing in the data stopped someone from doing it.
No reconciliation trail
Without a recorded comparison against the audited reports, a difference between two numbers was a matter of argument rather than a documented decision.
What we built
We put the entire metric definition in the data layer and gave the agent a tool for each question it is allowed to answer. Nine tools, each backed by a specific query: national sales, plaza and district, region, custom range, digital acquisition, channel groups, products, apps, and the reporting cut itself, plus a tool whose only job is to report the latest available date per source, so the agent can say what it does not yet have. Twenty written rules sit alongside, and the first of them forbids the model from doing arithmetic. It reads resolved figures and explains them.
That split is the whole design. An agent that calculates is a model you have to trust; an agent that reads a reconciled view is a model you can check. Every number it says has a query behind it that a human can run independently and get the same answer from. When a stakeholder asks where a figure came from, the answer is a file, not a prompt.
The sales layer reads the Google Sheet on its own terms. Rather than asking the team to restructure the sheet, the ingest reads each column's date out of the header row and derives the period from it, so the sheet stays usable for the people who maintain it. From there the figures are served at national, plaza, district and regional level for every period type the business uses (day, week, half month, month, month to date, the previous month cut at the same point, and any custom range) modelled once as table functions rather than reimplemented in each dashboard.
The three geography levels are kept apart deliberately. 44 plazas, 83 districts and 9 commercial regions each have their own query, and none of them aggregates into another. The reconciliation that matters most here is the national total against the sum of the 44 markets: across 168 periods, the audited national figure matches. That is the check that tells us the sales layer is not quietly double-counting or losing rows, and it is the one we would run first on any future change.
For the digital layer we replaced the export with the GA4 Data API, read three times a day for headline sessions, users and closing rate. Sessions and active users now match the Analytics screen exactly, where the previous export ran one to two percent above it. Media, acquisition, behaviour, channel groups, products and apps each keep their own meaning rather than being forced into one shared grain, the common failure in cross-platform reporting is a single wide table that makes every metric slightly wrong.
The reporting cut moved from a manual assembly to a Cloud Run service. It pulls the reconciled figures, renders the chart in the team's format, and writes the analysis text from rules rather than from a free-text model pass, so the same inputs produce the same commentary. It takes six to twenty seconds. It is available inside the agent and through a key-protected endpoint so n8n can request it on a schedule and deliver it without anyone opening the agent at all.
Reconciliation was treated as a deliverable in its own right, not as a step before go-live. Each difference found is recorded with its metric, period, source, size, cause and the decision taken. Ten figures from published weekly reports were compared: eight matched exactly and two differed by one and three sales respectively, at most 0.06%, each one explained rather than smoothed over. Separately, the team's weekly Python script was reproduced in SQL and all eight of its figures came out identical, which is what made retiring that manual process a decision rather than a leap.
All metric logic lives in the data layer
The agent reads and explains; it never adds up. Every figure it reports has a query behind it that a person can run and verify independently.
Reconciliation is a deliverable, not a phase
Each difference is logged with metric, period, source, size, cause and decision. A documented 1-sale variance is trustworthy; an undocumented exact match is not.
Build alongside, never on top
New objects got new names and no client object was edited or deleted, so the previous definitions remained available as a rollback reference throughout.
Keep levels that must not be summed structurally apart
Plazas, districts and regions each have their own query and none rolls into another, so the plausible wrong total is not reachable by accident.
Stop incomplete data before it is published
A freshness and completeness check runs ahead of the cut. A late source produces a stated gap, not a quietly understated number.
Read sources as they are
The sales sheet is formatted for humans, so the ingest reads the dates out of its header row rather than requiring the team to change how they work.
Non-negotiables
- The client's existing objects were read-only. Nothing could be edited or deleted; every new object got a new name and the old definitions were kept as a rollback reference.
- There was no Salesforce API, so the sales layer had to read the human-formatted sheet as it actually existed rather than asking the team to restructure it.
- The agent was not permitted to perform arithmetic. All metric logic had to live in the data layer, with the model limited to reading and explaining.
- The reporting cut had to come out in the team's existing chart format, because a correct number in an unfamiliar layout does not get used.
- Incomplete source data had to be stopped before publication rather than published with a caveat.
The pipeline
Drawn as text and shapes rather than shipped as an image, so the step labels stay readable and downloadable.
Reconciled data layer with the agent on top
Sources land in BigQuery, period and geography logic is modelled once as views and table functions, reconciliation gates what is exposed, and only then do the nine agent tools and the n8n gateway read it.
- Data store or CRM
- Processing step
- Branch / filter
- Error handling
- AI / model call
- 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.
Read the sales sheet on its own terms
The primary sales source is a Google Sheet formatted for people, with each column's date in the header row and no API behind it. We read the header to resolve what period each column represents, then expose the result as an external table in BigQuery. Asking the team to restructure the sheet would have broken the process that actually keeps it up to date.
Prevents the silent period mislabelling that any header-blind import of this sheet produces.
Land the digital sources without flattening their meaning
Media and acquisition data from Google Ads and Meta arrive through connectors; GA4 behaviour, channel groups, products and apps are kept as their own modules. Each keeps its own grain and its own definition rather than being merged into one wide table. A single shared schema across platforms is convenient once and wrong every time a metric is read afterwards.
Replace the GA4 export with the GA4 Data API
The existing export ran one to two percent above the Analytics screen for sessions and active users, which made every digital figure contestable. We read headline sessions, users and closing rate directly from the GA4 Data API three times a day instead. Those figures now match the Analytics screen exactly, so the comparison a stakeholder will make on their own is the comparison that passes.
Removes the standing 1 to 2% discrepancy between reported digital figures and the client's own Analytics view.
Model period types and geography once, in SQL
The business asks for day, week, half month, month, month to date, the previous month cut at the same point, and arbitrary custom ranges. Those are built as table functions in the data layer so every consumer gets the same definition. The three geography levels (44 plazas, 83 districts, 9 commercial regions) each have their own query and none aggregates into another, because the plausible wrong total must not be reachable.
Stops the same period or level being redefined differently by each dashboard, agent tool or script that needs it.
Reconcile before exposing anything
Nothing is served to the agent until it ties out. The national total was compared against the sum of the 44 markets across 168 periods and matches in all of them. Ten figures from published weekly reports were checked: eight matched exactly, two differed by one and three sales, at most 0.06%, and each difference was recorded with its metric, period, source, size, cause and decision rather than adjusted away.
Catches double counting and dropped rows in the sales layer before a wrong figure reaches leadership.
Reproduce the manual weekly script in SQL
The team maintained a weekly Python script that produced its own set of figures. We rebuilt its logic in the data layer and compared all eight outputs; they came out identical. That comparison is what turned retiring the script into an evidenced decision instead of a request to trust the new layer.
Ensures the process being retired and the process replacing it agree before the old one is switched off.
Give the agent tools, not arithmetic
Nine tools cover the modules the agent is allowed to answer from, each backed by a named query, plus a tool that reports the latest available date per source so the agent can state what it does not have yet. Twenty written rules constrain it, beginning with a prohibition on calculating. The agent's job is to choose the right tool, read the resolved figure and explain it.
Removes the class of failure where a model produces a confident total it computed itself.
Serve the reporting cut through a key-protected gateway
A Cloud Run service assembles the daily and weekly cut: reconciled figures, the chart in the team's existing format, and analysis text generated from rules so the same inputs always yield the same commentary. It completes in six to twenty seconds. The same endpoint is exposed behind a key so n8n can request the cut on a schedule and deliver it without anyone opening the agent.
Keeps the cut reproducible and the endpoint closed: an unauthenticated request is rejected rather than served.
What changed
| Aspect | Before | After |
|---|---|---|
| Daily and weekly cut | Assembled by hand each time, figures, chart and commentary. | Generated in 6 to 20 seconds with the chart in the team's format and rule-based analysis text. |
| Digital figures | An export running 1 to 2% above the Analytics screen. | GA4 sessions and active users read from the Data API and matching the Analytics screen exactly. |
| Where metrics are defined | Re-expressed in each dashboard and script that needed them. | Defined once in BigQuery views and table functions; every consumer reads the same definition. |
| Geography | Three levels in the same data, with nothing preventing a summed total. | Plazas, districts and regions served by separate queries that do not aggregate into one another. |
| Differences between sources | A matter of argument when two numbers disagreed. | Logged with metric, period, source, size, cause and decision, including the two weekly figures off by 1 and 3 sales. |
| Asking a question | A request to the reporting team, answered when they had time. | Nine agent tools over the reconciled layer, with the agent prohibited from calculating anything itself. |
| Late or incomplete sources | Published and understated, or noticed afterwards. | Stopped before publication, with the gap stated rather than absorbed into the figure. |
What it runs on
Every tool listed with the job it actually does in this build.
Data
BigQuery
The data layer where every metric is defined, external tables over the sources, views and table functions for period and geography logic, scheduled queries, and the validation and audit queries that gate what gets exposed.
AI
Vertex AI Agent Engine
Hosts the agent, built with the Agent Development Kit. Chosen so the tool surface and the rule set are explicit deployed artefacts rather than prompt text.
Gemini
The model behind the agent. It selects tools, reads resolved figures and writes explanation. It is not permitted to perform arithmetic.
Platform
Cloud Run
Runs the FastAPI reporting service that renders the chart in the team's format and assembles the cut text, plus the separate key-protected gateway.
Orchestration
Cloud Scheduler
Drives the recurring work: the GA4 reads three times a day and the scheduled refreshes behind the data layer.
Integration
GA4 Data API
Read directly for headline sessions, users and closing rate, replacing an export that ran 1 to 2% above the Analytics screen.
Windsor.ai
Connector layer bringing Google Ads and Meta media and acquisition data into BigQuery.
Google Apps Script
Reads the human-formatted sales sheet, including the dates held in its header row, so the sheet does not have to be restructured.
Delivery
n8n
Requests the daily and weekly cut from the key-protected endpoint on a schedule and delivers it, so the reporting cadence does not depend on anyone opening the agent.
What stopped being manual
Snippets are illustrative and generic. No client data appears in any of them.
Period types defined once as a table function
Replaces each dashboard, script and agent tool deciding for itself what 'month to date' or 'previous month to the same cutoff' means. One definition, called by every consumer.
sql
-- Illustrative shape. One definition of each period type, called by
-- every agent tool rather than re-expressed per consumer.
CREATE OR REPLACE TABLE FUNCTION layer.sales_for_period(
period_type STRING, as_of DATE
) AS (
SELECT d.sale_date, d.plaza_id, d.units
FROM layer.sales_daily AS d
WHERE d.sale_date BETWEEN period_start(period_type, as_of)
AND period_end(period_type, as_of)
);
-- "previous month to the same cutoff" is a period type, not an ad hoc filter
SELECT SUM(units) FROM layer.sales_for_period('prev_month_to_date', @as_of);National total against the sum of the markets
Replaces an eyeball comparison of two reports. This is the check that proves the sales layer is neither double counting nor dropping rows, and it is the first thing we run after any change to the ingest.
sql
-- Run per period. Any non-zero diff is logged with metric, period,
-- source, size, cause and decision -- never silently adjusted.
WITH national AS (
SELECT period_id, SUM(units) AS national_units
FROM layer.sales_national GROUP BY period_id
), markets AS (
SELECT period_id, SUM(units) AS market_units
FROM layer.sales_by_plaza GROUP BY period_id
)
SELECT n.period_id, n.national_units, m.market_units,
n.national_units - m.market_units AS diff
FROM national n JOIN markets m USING (period_id)
WHERE n.national_units <> m.market_units;An agent tool returns a resolved figure, not a calculation
Replaces the possibility of the model computing a total. The tool runs a named query and hands back the number with the period and source attached, so the agent has nothing left to add up.
python
# Illustrative. Each of the nine tools maps to one named query file.
def national_sales(period_type: str, as_of: str) -> dict:
rows = bq.run("sql/agent_queries/01_national.sql",
period_type=period_type, as_of=as_of)
if not rows:
return {"status": "no_data", "period": period_type}
return {
"status": "ok",
"value": rows[0]["units"], # already aggregated in SQL
"period": period_type,
"source": "layer.sales_national",
"as_of": rows[0]["last_loaded"], # so the agent can state freshness
}What it was worth
The figure we would point at first is 168 of 168. The audited national total matches the sum of the 44 markets in every period checked, which is the single test that tells you a sales layer is neither double counting nor losing rows. Everything the agent says rests on that, and it is the first check we would re-run after any change to the ingest.
The comparison against published weekly reports is the more interesting one, because it is not perfect. Eight of ten figures matched exactly; two differed by one and three sales, at most 0.06%. We recorded both with their cause rather than tuning the query until the numbers agreed. A reconciliation log containing explained differences is worth more than a clean one, because a clean one usually means the comparison was not sharp enough to find anything.
Retiring the weekly Python script was possible because all eight of its figures were reproduced in SQL first. That is the pattern we would use again for any manual process: do not argue that the new system is better, show that it produces the same answer, then switch the old one off. The decision stops being a matter of confidence.
The GA4 change is small in size and large in effect. A one to two percent gap against the Analytics screen is exactly big enough to make every digital number arguable in a meeting, and stakeholders check the screen. Reading the Data API directly means the comparison someone will make unprompted is the comparison that passes.
On the agent itself: the reporting cut now takes six to twenty seconds including the chart and the commentary, where it used to be assembled by hand. We have not measured hours saved, and we are not going to assert a figure we did not measure. What we can say precisely is that the cut is reproducible, that the same inputs produce the same output, and that no figure in it was calculated by a language model.
Periods where national matches the sum of 44 markets
The core reconciliation behind the sales layer, re-run after any ingest change.
Published weekly figures matched exactly
The other two differed by 1 and 3 sales, at most 0.06%, each recorded with its cause.
Figures of the manual weekly script reproduced in SQL
The evidence that made retiring the script a decision rather than a leap.
The agent's permitted surface
One tool per module plus a freshness tool; rule one forbids the agent from doing arithmetic.
To produce a daily or weekly reporting cut
Reconciled figures, the chart in the team's format and rule-based analysis text, previously assembled by hand.
GA4 sessions and active users vs the Analytics screen
The earlier export ran 1 to 2% above it.
Client objects edited or deleted
Everything was built alongside the existing layer, with old definitions kept as a rollback reference.
Handed over
- A reconciled BigQuery data layer: external tables over the sources, views and table functions for all six period types plus custom ranges, and scheduled refreshes.
- Separate query paths for the three geography levels (44 plazas, 83 districts and 9 commercial regions) that do not aggregate into one another.
- A sales ingest that reads the human-formatted Google Sheet, including the dates held in its header row, without requiring the sheet to change.
- A digital layer covering media, acquisition, behaviour, channel groups, products and apps, each retaining its own grain and meaning.
- A GA4 Data API sync for headline sessions, users and closing rate, read three times a day, replacing an export that ran 1 to 2% high.
- A Gemini agent on Vertex AI Agent Engine with nine tools and twenty written rules, the first of which forbids the agent from doing arithmetic.
- A Cloud Run reporting service producing the daily and weekly cut with the chart in the team's existing format and rule-based analysis text.
- A key-protected gateway so n8n can request the cut on a schedule; unauthenticated requests are rejected.
- A validation suite covering national-versus-market reconciliation, orders per week, channels by week, weekly variance by article and media spend by channel.
- Data quality, freshness and lineage audit scripts, plus a health check and an API comparison script.
- A SQL reproduction of the team's weekly Python script, matching all eight of its figures, as the basis for retiring it.
- Written documentation: case study, technical architecture, delivery report with the validation log, and a handover covering redeploy and identity migration.
Questions about this build
What teams usually ask when they recognise their own process here.
Because a model that computes is something you have to trust, and a model that reads a reconciled view is something you can check. Every figure this agent reports has a named query behind it that a person can run independently and get the same answer from. The cost of the rule is that a question nobody built a tool for cannot be answered; the benefit is that there is no path to a confident wrong total reaching leadership. For a national sales figure that is the right trade.
That the sales layer is internally consistent and agrees with the client's own audited reporting. The national total matched the sum of the 44 markets in all 168 periods, which rules out double counting and dropped rows. Against published weekly reports, eight of ten figures matched exactly and two differed by one and three sales. We logged those two with metric, period, source, size, cause and decision rather than adjusting them, because an explained difference is evidence and an unexplained match is not.
Because there was no Salesforce API available to us and the sheet is the live source maintained by the team. Restructuring it would have meant changing how a working process operates in order to suit the pipeline, which tends to end with the pipeline being correct and the sheet being abandoned. Reading each column's date out of the header keeps the sheet usable for the people who maintain it and still gives the data layer an unambiguous period per column.
Because they are three reporting levels over the same business, not a hierarchy that sums cleanly, 44 plazas, 83 districts and 9 commercial regions. If they share one query path, someone will eventually aggregate across them and get a total that looks reasonable and is wrong. Separate queries that do not roll into each other make that mistake unreachable rather than merely discouraged.
The cut is stopped rather than published. A freshness and completeness check runs ahead of it, and the agent also has a tool whose only job is to report the latest available date per source, so it can say what it does not have yet. Publishing a figure built on a partially loaded day produces an understated number that looks normal, which is harder to catch later than an absence.
Yes. The parts that matter are structural rather than vendor-specific: metric definitions in the warehouse, one definition per period type, levels that must not be summed kept apart, a reconciliation suite that gates what gets exposed, and an agent limited to calling named queries. BigQuery's table functions and external tables made the sheet ingest and the period modelling convenient here, but the same design holds on any warehouse with views and scheduled queries.
Through a separate key-protected endpoint on Cloud Run. n8n requests the daily or weekly cut on a schedule and delivers it, and a request without the key is rejected. Separating the gateway from the agent means the reporting cadence does not depend on a person opening a chat interface, while the same reconciled figures and the same chart format back both paths.
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.