How do you get ad spend and CRM revenue into one report?

Rhobin

July 31, 2026

7 min read

You get ad spend and CRM revenue into one report by landing both in one warehouse and joining them on a click identifier captured when the lead is created, then reporting on the click date rather than the close date. Blending them on campaign name in a dashboard looks like the same thing and falls apart as soon as someone asks which deals the revenue is made of.

The symptom

You can tell a client to the euro what they spent last month, and to the lead how many enquiries came in. What you cannot tell them is what any of it was worth. Cost sits in Google Ads and Meta. Revenue sits in the CRM, booked by a salesperson six weeks after the click. The monthly deck puts them on two different slides, and everyone quietly agrees that leads count as a result.

So someone tries to fix it. A connector pulls Google Ads into Looker Studio, a CRM export lands beside it, and the two get blended on campaign name. It holds for about a month. Then a campaign gets renamed, a batch of leads arrives with the field empty, and the client asks which deals the revenue number is made of. Nobody can say.

What is left is a spreadsheet rebuilt by hand each month, and a report that cannot answer the question that matters: which channel produced money, not motion.

Why it happens

It looks like a plumbing problem, and the connector market is happy to agree. Plumbing is the easy part. These two datasets resist each other for three reasons, and no further integration fixes any of them.

They do not describe the same kind of thing

Ad platforms report in aggregate. Meta's Marketing API Insights returns performance at account, campaign, ad set and ad level, sliced by breakdowns such as age or placement. There are no person-level rows in it, by design. A CRM is the opposite shape: one row per contact, one row per deal, each with a name attached to it.

There is therefore no natural key between "this campaign spent 4,000 in July" and "this deal closed at 18,000". Campaign name is what people reach for, because it is the only string in both systems. It is a label, not an identifier, and it changes the moment someone tidies up the account.

Cost and revenue are dated differently

Google Ads reports conversions against the time of the click, not the time of the conversion. Google's own documentation says the primary conversion columns are "calculated based on the time of the click, not the time of the conversion", which keeps cost per conversion honest, because the cost was incurred at the click. Your CRM does the reverse and dates revenue when the deal closes.

Put July spend next to July closed revenue in one row and you are describing two different groups of people. Google offers separate "by conv. time" columns for this reason, which helps inside the platform and does nothing for a report holding both systems at once.

The attribution window closes before the deal does

This is the one that decides whether a client needs a warehouse at all. In Google Ads the click-through conversion window can be set anywhere from 1 to 30, 60, or 90 days, and defaults to 30. A deal that closes on day 120 is not attributed poorly inside Google Ads. It cannot be attributed there, at any setting.

Then layer the input problem underneath. Around 15-30% of conversions are consistently never captured at all, so the lead rows arriving in your CRM already under-represent paid before any joining starts. And where the browser does hold a source, it often does not hold it for long. Safari caps script-set cookies at seven days, and drops that to one day when the visitor arrives through a decorated link from a domain classified as tracking-capable, which is a fair description of an ad click. A cookie served properly as first-party can run to 400 days. A 90-day sales cycle outlives a one-day cookie every time.

What good looks like

The working version is not a better dashboard. It is one place where cost, behaviour and revenue land as rows, joined on a key created when the lead was created rather than reverse-engineered afterwards. Three things are true of it.

The lead record carries its own origin. The click identifier and campaign context are written onto the contact at the moment the form is submitted, and they travel with that contact into the deal. This is the whole join. Reconstruct it later from campaign names and you are guessing. Capture it at the source and it holds for as long as the record does, however long the deal takes to close.

Cost arrives on a schedule, not by hand. Google Ads spend can be landed in BigQuery through the BigQuery Data Transfer Service, which supports Google Ads, Campaign Manager, Display and Video 360 and Search Ads 360 as managed sources. Other channels come in through their own connectors. Nobody exports a CSV.

The report is a cohort, not a month. Spend stays on the click date, revenue attaches to the deal that click produced, and both are read over a window long enough to contain the sales cycle. That is what makes a cost per closed deal, and a margin per channel, mean anything.

The collection layer underneath is what most setups are missing, and it is what Archon Pixel provides: first-party event collection with cookies served from the client's own domain, capturing around 25% more than a standard GA4 setup and landing events in their warehouse rather than only in a reporting interface.

Be straight about the ceiling. Some share of leads will never carry a source, because consent was refused, a blocker intervened, or the request failed. Size that bucket, show it as unattributed, and leave it visible. Redistributing it quietly across channels is how a report starts lying at the moment it looks most impressive.

Plenty of clients do not need per-deal attribution at all: if deal values cluster tightly, aggregated cost against a lead source field answers the question for a fraction of the effort. Per-deal joins earn their cost when values vary widely, when the cycle is long, or when margin differs sharply by product, which is also where the shift from ROAS to POAS stops being theoretical.

FAQ

Can we not just blend Google Ads and the CRM in Looker Studio?

For a channel-level view, yes, for a while. Blends join on fields both sides happen to share, in practice campaign name, so they break on renames and on leads that arrive with the field empty. The harder limit is that a blend cannot tell you which deals a revenue figure is made of, because neither input holds a per-deal key. It is a reporting shortcut, not a data model.

Does GA4 not already combine cost and revenue?

Only in aggregate. GA4's campaign data import brings in aggregated cost, clicks and impressions per campaign, source and medium, which is enough to compare channels and not enough to carry a per-lead join. Its BigQuery export does give raw event rows, though standard properties are capped at one million events per day. GA4 describes behaviour on the site. It does not know what the deal closed at.

How long does this take, and is it worth it for a smaller client?

It depends almost entirely on CRM discipline. If the CRM already has consistent lead sources and clean deal stages, the work is connection and modelling. If it does not, that cleanup is the project and no tooling shortcuts it. On the payoff side, the performance agency in our case study saved 14 hours per project once reporting stopped being assembled by hand. For a small client with uniform deal values, channel-level reporting is still the honest recommendation.

Once revenue sits in the warehouse, can we send it back to the ad platforms?

That is usually the highest-value thing you do with it. Closed deals can be uploaded to Google Ads against the stored click identifier, which is why the identifier has to be on the record before the deal closes rather than found afterwards. We cover feeding offline conversions back into the ad platforms separately, because the bidding implications deserve their own treatment.

What are the privacy constraints on joining CRM data to ad data?

Joining data inside your client's own warehouse for their own reporting is a different question from sending it to a platform. Once personal data goes to Google for users in the EEA, the UK or Switzerland, Google's EU user consent policy requires consent for that collection and sharing, records of the consent given, a clear route to revoke it, and that each party receiving the data is identified to the user. Treat that as a design input at the start, not a review at the end.

If you want to know whether your client's current setup can support this join before you promise anyone a revenue report, that is what a free tracking audit is for.