Ad spend

Owner: whoever runs the ad accounts.

Stitch can tell you what every channel produced. Until spend arrives it cannot tell you what any of it cost, which means no CAC, no cost per funded loan, and no ROAS — the numbers a media buyer actually decides on.

This page is how spend gets in. There are two ways and you can start with the first today.

1. The CSV (works now, no API access needed)

Every platform exports this. Pull the same report you would for a weekly review, at daily × campaign grain.

day,platform,account,campaign_id,campaign_name,spend,impressions,clicks
2026-08-31,google_ads,gads-main,17384920,bll_acq_prospecting_gads_purchase-rates_az,4120.50,50000,900
2026-08-31,meta,meta-main,239481,bll_acq_lookalike_meta_homeowner-lal,2210.75,90000,610

day — YYYY-MM-DD. Not 31/08/2026.
spend — dollars, as the platform exports them. $1,234.56 and 1234.56 both work; it becomes cents once, on the way in.
campaign_name — must be exactly what the campaign is called in the platform. This is the join key. See below.
campaign_id — the platform’s own id. It survives a rename, which is what makes a restated day overwrite cleanly.

Then:

node scripts/import-spend.mjs --file august.csv --dry-run   # check it first
node scripts/import-spend.mjs --file august.csv

--dry-run reads the file, reports what it found, names the line number of anything it could not read, and sends nothing. Run it first — it costs nothing and it catches a wrong date format before it becomes a month of missing spend.

Re-importing a day is safe and expected. Platforms restate the last three to seven days as click fraud is filtered out, and the later number replaces the earlier one. Import the last fortnight every time; nothing doubles.

Authentication comes from STITCH_IMPORT_TOKEN in your environment — the Worker’s IMPORT_TOKEN, which can import spend and nothing else (see the internal access runbook). Do not pass a token on the command line of a shared machine.

2. Typing one in (works now, for spend no platform reports)

A referral partner invoices monthly. An agency retainer is a line on a contract. A sponsorship is a handshake. None of it has an API, and a channel whose cost is missing looks free — which is a worse lie than one whose cost is asserted.

On the channels screen, under the cost table: Add a cost by hand. Pick a period, pick a channel (or type a campaign name), put in the amount.

Three things it does that are worth knowing:

A range is divided across its days. Every report here buckets by day, so “$5,000 in August” becomes 31 rows of about $161. The total is yours; the daily split is ours, and the rows are marked manual_spread so nothing reads a Tuesday as measured. A single-day entry is marked manual instead.

A channel cost is declared, not observed. Spend normally reaches a channel by matching its campaign name against the campaigns real sessions arrived carrying — we can point at the visits the money bought. A cost typed against a channel has no campaign, so it attaches because you said so. It counts in that channel’s cost, and it is reported separately from spend we matched.

Where both exist, the measurement wins: if sessions carrying a campaign landed on Paid Social, that is where its spend goes, whatever channel somebody declared it against.

Entering the same period again replaces it. Same rule as a platform import, for the same reason.

A cost typed against a campaign no session ever carried still lands in unmatched spend. Typing it in does not make the tracking work.

3. The platform APIs (needs credentials you create)

(Microsoft’s refresh exchange goes to login.microsoftonline.com with the msads.manage offline_access scope since plan 36; it used to be sent to Google’s token endpoint. The Microsoft spend sync still does not work end to end: ad-sync.ts builds a Google Ads report request for every non-Meta platform, and a secret cannot hold Microsoft’s rotating refresh token — a follow-up, with the Microsoft export row’s credential as the likely home.)

Once an account is connected, the nightly cron pulls a trailing fortnight every run — platforms restate the last three to seven days as click fraud is filtered out, and re-importing a day replaces it rather than doubling it.

What you create

Google Ads. An OAuth client (client ID and secret), a refresh token for an account with read access, and a developer token — that last one is an application to Google and it takes a few days to approve. If your accounts sit under a manager account, note its customer ID too.

Meta. An app with ads_read, and a system-user token for it. Production access needs app review. System-user tokens do not expire on a schedule, so there is no refresh dance.

Where they go

Not in the database. A refresh token in a table row is readable by anything with database access, lands in every backup, and ends up in a SELECT * in somebody’s terminal scrollback. They go in a Cloudflare secret:

# From workers/processor. Paste the JSON when prompted — never on the command
# line of a shared machine, where it lands in shell history.
# AD_PLATFORM_CREDENTIALS is set as a Worker secret by the operator

The value is a JSON object keyed by account key:

{
  "gads-main": {
    "client_id": "…apps.googleusercontent.com",
    "client_secret": "…",
    "refresh_token": "…",
    "developer_token": "…",
    "login_customer_id": "123-456-7890"
  },
  "meta-main": { "access_token": "…" }
}

Turning one on

INSERT INTO ad_accounts (account_key, platform, external_id, label, brand_default, currency, enabled)
VALUES ('gads-main', 'google_ads', '123-456-7890', 'Google Ads — main', 'bll', 'USD', 1);

account_key must match the key in the secret. It deliberately does not fall back to a default: an account key that matches nothing is recorded as an error, because a typo would otherwise look like a sync that ran clean.

Watching it

“Is spend still arriving?” on the data-quality screen. Every account, when it last delivered, and how many rows.

last_synced_at moves only on success. A run that fails records its error and leaves the timestamp where it was, so “last synced” means what it says. An account silent for two days is flagged, because that is roughly where a cost figure starts lying: revenue keeps arriving and cost does not, so cost-per-funded falls, ROAS rises, and payback looks earlier than it was. Nothing errors. The only symptom is a channel that appears to have got cheaper.

Zero rows is recorded rather than treated as a clean run — a fortnight of nothing is either a broken account or a genuinely dark one, and from the outside they look identical.

A caveat worth reading

The request shapes here are built from Google’s and Meta’s documentation and every one is unit-tested — URL, headers, date range, paging, and what each class of failure means. What has not been tested is whether the platforms accept them, because that cannot be checked without a live account. The first real sync may need a field name changed. The response parsing is the better-established half: it is tested against each platform’s documented payload, including the array-of-chunks searchStream actually returns.

Versions

Pinned — Google Ads v25, Meta Graph v25.0 — because both sunset on a schedule and a version that vanishes should fail on a date somebody chose. Override per account with "api_version" in its credentials block.

The campaign name is the join

Spend attaches to value by matching campaign_name against the utm_campaign your ads actually carry. Exact string, no fuzzy matching.

That is deliberate. Fuzzy matching quietly attributes spend to the wrong channel, and a wrong number nobody can see is worse than a missing one they can. So the rule is simple and the failures are loud:

The campaign’s name in the platform must equal the utm_campaign in its tracking template. If Google Ads calls it bll_acq_prospecting_gads_purchase-rates_az, the landing URL must carry utm_campaign=bll_acq_prospecting_gads_purchase-rates_az.

What you get for following the convention

{brand}_{objective}_{audience}_{channel}_{descriptor}_{geo?} — spec 1.12.

A name that parses lets Stitch do two things it otherwise cannot:

Hold brand defence out of CAC. A campaign with objective brand is reported as overhead with its own cost-per-defended-conversion, and excluded from acquisition CAC. Brand search defends; it does not acquire. Folding it in lets it flatter itself in every calculation and slowly eat the budget that generates the demand it is harvesting.
Stop retargeting taking acquisition credit. Audience retargeting or crm marks a touch as nurture, so a campaign aimed at people we already have cannot be credited with finding them.

A name that does not parse still imports and its spend is still counted. It just cannot be split by objective — so if any of it is brand defence, it lands in acquisition CAC where it does not belong.

Unmatched spend

The number to look at first, on the “Is our ad spend being tracked?” card of the data-quality screen.

It is spend against a campaign no session ever arrived carrying. There are three usual causes and all three are expensive:

The tracking template broke. Someone edited a campaign and the utm_campaign went with it. Money out, nothing in, no error anywhere.
The campaign was renamed in the platform but not in its tracking.
Tracking was removed from the landing page.

None of these raise an error in an ad platform, in Stitch, or anywhere else. The only symptom is spend that reaches no touchpoint — which is why it gets a figure of its own rather than being quietly distributed across channels.

Unmatched spend is excluded from every channel’s cost. Spreading it would make every channel look slightly worse and the real problem invisible.

Reading the result

Two tables, because there are two ledgers and they are never summed.

Cost ladder (per loan, on the channel lens) — spend → cost per app → cost per funded → ROAS. Channels reorder down this ladder, often dramatically: a channel with cheap applications that do not fund is not a cheap channel.

Acquisition (per person) — cost per lead, cost per person acquired, LTV/CAC, and brand spend held separately. Cost per lead and cost per person acquired are different numbers and the gap between them is how many leads a channel’s people raise before one of them applies.

The date trap

Spend is dated by the day it was spent. Value is dated by whatever anchor the filter is set to — lead date by default.

“October spend vs October funded loans” is almost always the wrong comparison in mortgage. October’s funded loans were bought in July. The cost report names both anchors rather than leaving the mismatch implicit, but it is still yours to reason about: for anything decision-grade, compare a cohort’s spend against that cohort’s outcomes, not a calendar month against itself.

Owned channels show no spend

Email and SMS cost a CRM subscription, not an auction. They show $0 media spend, which is true and is not the same as being free.

Advertising where we cannot lend

Once spend is imported and licensed_states is filled in, the data-quality screen answers a question nothing else in the stack can: how much are we paying to advertise into states we are not licensed to originate in?

That money cannot become a loan however well the ad performs. It is not inefficiency, it is waste, and every one of those clicks looks like a perfectly good click to the ad platform.

The check reads the state from the last segment of the campaign name, which is the other thing the naming convention buys you:

bll_acq_prospecting_gads_purchase-rates_va
                                        ^^ checked against licensed_states

A campaign with no state in its name cannot be checked either way. That is reported separately rather than counted as fine — “we cannot tell” and “it is fine” are different answers, and folding one into the other is how a report stops being trusted.

Set up your licence footprint before relying on it:

INSERT INTO licensed_states (state, brand_id, licensed_from, licensed_to, note)
VALUES ('AZ', 'bll', NULL, NULL, 'active');

licensed_from and licensed_to are optional epoch-millisecond bounds. A campaign running in a state whose licence lapsed last month is the same waste as one that never had it, so lapsed licences are caught too.

With no licences on file the card reports nothing at all rather than flagging every state — a false alarm on an empty table would train people to ignore it.

Did the money come back?

The Payback screen groups people by the month we first found them, and follows what those people have produced since against what that month cost. It is the one report that answers the question a budget meeting actually asks: not what a channel produced, but whether the cheque has come back yet.

Four things are done deliberately, and each is the difference between a payback chart and a misleading one.

Only people we paid for are counted. A month’s arrivals include people who came through search, referral and word of mouth and cost nothing. Counting their revenue against media spend makes every month look like it pays back. The other revenue is shown beside the number, because some of it was probably created by the same ads — brand searches especially — and none of that is provable.

Brand and nurture spend are held out. Brand defence protects demand rather than creating it. Retargeting is aimed at people an earlier month already found. Both appear as a separate figure rather than in any month’s cost.

Recent months are listed but not read. A mortgage funds about 45 days after the click, so a one-month-old cohort is structurally near zero. Drawing it as a failure shows a cliff that is the funding cycle, not the marketing.

The blended curve narrows as it goes right. Each point uses only the months old enough to have reached it, so the line goes dashed once a month or two of spend is all that is left behind it. Reading the far right as a trend is reading two data points.

The chart stops at three times break-even and marks anything above with an arrow. Past 3x the decision is already made, and letting one spectacular month set the scale hides the break-even line every other month is read against. The exact figures are in the grid below it.