How to merge UTM campaign data with CRM leads

Where UTM data lives

UTM parameters (utm_source, utm_medium, utm_campaign, and optionally utm_content and utm_term) are added to links. They only reach your CRM if something captures them — a hidden form field, a tracking script or a native integration — and stores them on the lead record. Before merging, open a few recent leads and confirm the fields are actually populated. Leads from direct visits, organic search or offline sources will have them blank, which is expected.

Normalise the values

UTM values are case-sensitive in most analytics tools, so “Facebook” and “facebook” are reported as two sources. Real CRM exports look like this:

1. Lowercase and trim every UTM field.
2. Build a mapping table for known variants (fb → facebook, newsletter → email).
3. Leave unknown values as they are and review them monthly rather than guessing.
4. Fix the tagging at source with a shared UTM builder sheet so the list of variants stops growing.

SELECT l.*,
       COALESCE(m.cleaned, LOWER(TRIM(l.utm_source))) AS source,
       LOWER(TRIM(l.utm_medium)) AS medium,
       LOWER(TRIM(l.utm_campaign)) AS campaign
FROM crm_leads l
LEFT JOIN source_map m ON m.raw = LOWER(TRIM(l.utm_source));

Join to campaigns and outcomes

With clean keys, group leads by source, medium and campaign and add outcomes from the CRM (qualified, won, revenue). Then join a campaign list holding spend, keyed on the same cleaned campaign value. Keep it a left join from leads so that untagged and organic leads still appear, labelled “(none)”, rather than vanishing.

SELECT COALESCE(source, '(none)') AS source, COALESCE(medium, '(none)') AS medium,
       COUNT(*) AS leads,
       COUNT(*) FILTER (WHERE status = 'Qualified' OR status = 'Won') AS qualified,
       SUM(CASE WHEN status = 'Won' THEN deal_value ELSE 0 END) AS revenue
FROM cleaned_leads
GROUP BY 1, 2
ORDER BY revenue DESC;

Worked example: last quarter

Before cleaning, facebook was split across four values and looked like four small sources. After cleaning it's 184 leads but only 4 wins, £8,000 from £5,000 of spend (£1.60 per £1). Google CPC returns £36,000 / £9,000 = £4.00. A quarter of leads (152 of 600) have no UTM at all; that's normal for direct and organic, but if it grows suddenly a form has stopped capturing the fields.

UTM fields usually hold first-touch or last-touch values depending on how they were captured — a hidden field captures the visit where the form was submitted, while some CRMs store the first visit. Know which you have before comparing with analytics reports.

Related checks

Once leads are attributed, find leads that never became customers by source to spot channels that fill the top of the funnel and nothing else. For paid search specifically, the click ID (gclid) gives a more reliable join than UTMs — see matching Google Ads conversions to CRM deals. Analistable can load the CRM export and your mapping sheet together and answer “revenue by cleaned source last quarter” from them in the browser. For more, see the cross-spreadsheet questions guide.

Keep a UTM builder sheet

Clean-up fixes the past; a builder sheet fixes the future. Give everyone who creates links one shared sheet with drop-downs for source and medium and a formula that assembles the URL:

=A2 & IF(ISNUMBER(SEARCH("?", A2)), "&", "?") & "utm_source=" & LOWER(B2) & "&utm_medium=" & LOWER(C2) & "&utm_campaign=" & LOWER(SUBSTITUTE(TRIM(D2), " ", "_"))

Column A holds the landing page, B to D the source, medium and campaign. The formula lowercases the values and swaps spaces for underscores, so “Autumn Sale” becomes autumn_sale every time. Data validation lists on B and C stop new variants of facebook appearing.

Every row in the builder is also a campaign record. Add spend and dates to it and it becomes the campaign table you join leads to, so tagging and reporting use the same list.

Review the leads with values that aren't in the builder once a month. Each one is either a link someone built by hand or a partner tagging traffic their own way, and both are fixed at the source rather than in the report.

A quick health check

Before trusting any source report, count leads per month with and without UTM values. A steady share of untagged leads is normal. A sudden jump usually means a form, landing page or redirect changed and stopped passing parameters. Catching that within a month saves a quarter of unattributed leads.

Also check the reverse: campaigns in your spend list with no matching leads at all. Either the campaign genuinely produced nothing, or its links were tagged with a value nobody added to the campaign list.

Frequently asked questions

Do UTMs work on links in PDFs and apps?

Yes, if the link opens your site with the parameters intact. Some apps open links in an in-app browser that still passes the URL, so the UTMs are captured as long as your form records them.

Should I clean UTMs in the CRM itself?

You can, with workflows that standardise values on create. Keep the raw value in a separate field so you can audit the mapping later.

What should go in utm_medium?

A small fixed list — cpc, paid_social, email, referral, affiliate — agreed across the team. GA4's default channel grouping relies on medium values, so matching its expected values also helps your analytics reports.

Why do some leads have a campaign but no source?

The link was tagged incompletely, or the form captured only some parameters. Treat these as untagged for source reporting and fix the link.

Related guides

• How to match Google Ads conversions to CRM deals
• Which leads never became customers?
• Microsoft Copilot in Excel vs Analistable
• How do sales reps rank across all regions?
• Zapier vs manual exports for combining data