How to match Google Ads conversions to CRM deals
By the Analistable team · Updated · 6 min read
Google Ads counts form submissions; your CRM knows which ones became paying customers. Capture the GCLID (Google's click ID) in a hidden form field and store it on the lead, then join CRM deals to the leads by GCLID and roll revenue up by campaign. Comparing cost per conversion with cost per closed deal shows which campaigns actually pay.
Part of our guide: How to join spreadsheets on a common column
What you need
- Auto-tagging on in Google Ads, so landing page URLs carry a
gclidparameter. - A hidden field on your forms that stores the GCLID (and the UTM campaign) on the CRM record.
- Exports: CRM leads/deals with GCLID, campaign, stage and amount; Google Ads campaign report with cost and conversions for the same period.
Join and roll up
Deals per campaign =COUNTIFS(Deals[Campaign], [@Campaign], Deals[Stage], "Closed won")
Revenue per campaign =SUMIFS(Deals[Amount], Deals[Campaign], [@Campaign], Deals[Stage], "Closed won")
Cost per deal =IF([@Deals]=0, "no deals", [@Cost] / [@Deals])| Campaign | Cost | Ads conversions | Cost / conversion | Closed deals | Revenue | Cost / deal |
|---|---|---|---|---|---|---|
| brand_uk | 1200 | 80 | 15 | 12 | 54000 | 100 |
| generic_merge_excel | 3400 | 170 | 20 | 4 | 9600 | 850 |
| competitor_terms | 2100 | 35 | 60 | 6 | 31200 | 350 |
The generic campaign looks cheapest per conversion in Google Ads but is the most expensive per closed deal. Optimising on form fills alone would have shifted budget the wrong way.
Close the loop
Once the GCLID is on each deal, you can upload closed deals back to Google Ads as offline conversions with their value, so bidding optimises for revenue rather than form submissions. Check the upload by matching the upload file against closed-won deals each month — the same join as above.
Matching GA4 data with ad costs instead: merging GA4 data with ad cost data.
When there's no GCLID
Without click IDs, join on the UTM campaign stored on the lead instead. It's less precise (it can't separate keywords or ad groups), and leads who came back through another channel before converting are credited to whichever campaign the form captured.
Step by step
- Export leads or deals from the CRM with the stored GCLID, UTM campaign, created date, stage and amount.
- Export the Google Ads campaign report for the same date range with campaign name, cost and conversions.
- If the CRM only holds the GCLID, you need a click-to-campaign lookup: a click performance report in Google Ads can provide one, or rely on the UTM campaign captured at the same time.
- Make campaign names match exactly on both sides — lower-case and trim them, because UTM values are typed by hand.
- Build a summary table with one row per campaign and the COUNTIFS and SUMIFS formulas shown above.
In Excel 365 and Google Sheets, =UNIQUE(Deals[Campaign]) gives the campaign list for the summary. In Excel 2019, copy the campaign column and use Data → Remove Duplicates instead, or build a pivot table from the deals table.
Second example: the date problem
A campaign's cost lands in the month of the click, but deals close weeks or months later. Judging September's campaigns on deals closed in September undercounts them. Group deals by lead created date instead, and only compare cohorts that are old enough to have closed.
| Lead month | Leads | Closed won so far | Revenue | Age of cohort |
|---|---|---|---|---|
| June | 14 | 4 | 20800 | 4 months |
| July | 12 | 2 | 10400 | 3 months |
| August | 9 | 0 | 0 | 2 months |
August looks like a failure only because it's young. With a typical sales cycle of three months, June's cohort is the fair one to compare with cost; at 4 deals and 20,800 in revenue it's that cohort's cost per deal that tells you whether the campaign pays.
Troubleshooting
| Symptom | Likely cause | Fix |
|---|---|---|
| Most leads have an empty GCLID | Hidden field not on every form, or the parameter is lost on a redirect | Test each form with a tagged URL and check the stored value |
| Campaign names don't match Ads | UTM values typed differently from campaign names | Keep a mapping table of UTM value to Ads campaign |
| Closed deals counted twice | Several leads attached to one deal | Count distinct deal IDs, not lead rows |
| Revenue far above Ads conversion value | Ads values are estimates set per conversion action | Compare against CRM revenue only, or upload real values as offline conversions |
Which method when
- GCLID join: the most precise, down to the click, and needed for offline conversion uploads.
- UTM campaign join: works across ad platforms and email, but only at campaign level.
- Lead source field only: a last resort that usually just says Paid search; fine for a channel split, not for comparing campaigns.
Repeat the analysis monthly with the same workbook: paste the new CRM export and Ads report, refresh the summary, and check that the number of leads with a campaign plus those without one equals the total lead count. For a wider look at combining ad and analytics data, see combining data from multiple sources and joining spreadsheets.
Checking an offline conversion upload
If you upload closed deals to Google Ads, compare the upload file with closed-won deals each month. Deals with a GCLID that are missing from the upload were lost by the export; uploaded rows with no matching deal suggest a test or a deal that was later reopened. Google Ads only accepts clicks within a limited lookback window, so very old clicks may be rejected — check the upload results in Google Ads rather than assuming every row was accepted.
In upload? =COUNTIF(Upload[GCLID], [@GCLID]) > 0
Missing rows =FILTER(Deals[Deal ID], (Deals[Stage] = "Closed won") * (Deals[GCLID] <> "") * NOT(Deals[In upload?]), "None")Common mistakes
- Comparing Ads conversions for one date range with CRM deals for a different one.
- Counting leads that sales disqualified as failures of the campaign rather than of the targeting.
- Summing Ads conversion value and CRM revenue together in one total.
Reading the results with care
Small numbers move a lot: four closed deals on a campaign can become six next month and halve its cost per deal. Look at cost per deal over a rolling quarter, and judge campaigns with only one or two deals on lead quality — the share of leads sales accepted — rather than on revenue alone.
Frequently asked questions
- How do I connect Google Ads leads to CRM deals?
- Store the GCLID from the landing page URL in a hidden form field, save it on the CRM record, and join deals to leads by GCLID or campaign.
- Why do Google Ads conversions not match CRM deals?
- Ads counts conversions such as form submissions; most leads don't close, and some close months later.
- What are offline conversions?
- Conversions you upload to Google Ads from your CRM, such as closed deals with their value, matched to the original click by GCLID.
- How long should I wait before judging a campaign on closed deals?
- At least your typical sales cycle. Compare campaigns on leads old enough to have had a chance to close.
- Can I do this with Microsoft Ads or Meta?
- Yes. Microsoft Ads uses an MSCLKID and Meta an FBCLID; store the relevant click ID or UTM campaign on the lead and join the same way.
- Should I group deals by close date or lead date?
- By lead created date when judging campaigns, because the cost was spent when the lead arrived. Close date suits revenue reporting, not campaign comparison.
- Why do some deals have no campaign?
- They came from other channels, or the form didn't capture the click ID or UTM. Report them as a separate row so totals still add up.