Joining Ad Platform Data With CRM

Why this matters

As a Marketing Analyst, you often need to answer: Which campaigns create qualified leads? What is our cost per opportunity? Which channels drive revenue? To do this, you must connect ad clicks/spend (Google Ads, Meta) with CRM records (leads, opportunities, orders). That connection lives in SQL joins, attribution windows, and careful deduplication.

Who this is for

• Marketing Analysts and growth practitioners linking acquisition to revenue
• Data/BI analysts building dashboards for CAC, ROAS, LTV
• Anyone preparing for analyst interviews with SQL attribution tasks

Prerequisites

• Comfort with SELECT, WHERE, GROUP BY, JOINs
• Basics of date/time and window functions (ROW_NUMBER)
• Understanding of UTM parameters and click IDs (gclid, fbclid)

Concept explained simply

You have two worlds: ad platforms (clicks, campaigns, spend) and CRM (leads, deals, revenue). You connect them by keys captured at conversion time (e.g., gclid/fbclid) or by matching timestamps and UTM parameters. Then, you choose attribution rules (e.g., last click within 7 days) and compute metrics (CPL, CAC, ROAS).

Mental model

• Three core tables: clicks, leads, orders
• One mapping: which click gets credit for each lead (pick by rule)
• Then aggregate: daily/campaign metrics and funnels

Data model used in examples

We will reference these example tables/columns:

• ad_clicks(click_id, platform, click_time, campaign_id, adset_id, ad_id, gclid, fbclid, utm_source, utm_medium, utm_campaign)
• ad_spend_daily(campaign_id, spend_date, spend)
• crm_leads(lead_id, created_at, email, phone, gclid, fbclid, utm_source, utm_medium, utm_campaign, status)
• crm_orders(order_id, lead_id, order_date, revenue)

Time zone note: align to one time zone before joining.

Worked examples

Example 1 — Direct click ID join (gclid/fbclid)

Goal: Attribute each lead to its latest ad click within 7 days prior to lead creation.

WITH matched AS (
  SELECT
    l.lead_id,
    l.created_at AS lead_created_at,
    c.platform,
    c.campaign_id,
    c.click_time,
    ROW_NUMBER() OVER (
      PARTITION BY l.lead_id
      ORDER BY c.click_time DESC
    ) AS rn
  FROM crm_leads l
  JOIN ad_clicks c
    ON (
      (c.platform = 'google' AND l.gclid IS NOT NULL AND l.gclid = c.gclid)
      OR (c.platform = 'meta' AND l.fbclid IS NOT NULL AND l.fbclid = c.fbclid)
    )
    AND c.click_time <= l.created_at
    AND c.click_time >= l.created_at - INTERVAL '7 days'
)
SELECT lead_id, platform, campaign_id, click_time, lead_created_at
FROM matched
WHERE rn = 1;

Example 2 — UTM join when click IDs are missing

Goal: Use UTM fields to connect leads to clicks when gclid/fbclid are missing. We add a fallback that matches same campaign name and a 24-hour window.

WITH candidates AS (
  SELECT
    l.lead_id,
    l.created_at,
    c.platform,
    c.campaign_id,
    c.utm_source,
    c.utm_medium,
    c.utm_campaign,
    c.click_time,
    ROW_NUMBER() OVER (
      PARTITION BY l.lead_id
      ORDER BY c.click_time DESC
    ) AS rn
  FROM crm_leads l
  JOIN ad_clicks c
    ON l.utm_source = c.utm_source
   AND l.utm_medium = c.utm_medium
   AND l.utm_campaign = c.utm_campaign
   AND c.click_time BETWEEN l.created_at - INTERVAL '24 hours' AND l.created_at
)
SELECT lead_id, platform, campaign_id, click_time
FROM candidates
WHERE rn = 1;

Example 3 — Daily ROAS from clicks, spend, and orders

Goal: Compute daily ROAS using last-click attribution from Example 1, then joining to orders and daily spend.

WITH last_click AS (
  SELECT l.lead_id, c.campaign_id, DATE(l.created_at) AS conv_date
  FROM (
    SELECT l.lead_id, l.created_at, c.campaign_id,
           ROW_NUMBER() OVER (PARTITION BY l.lead_id ORDER BY c.click_time DESC) rn
    FROM crm_leads l
    JOIN ad_clicks c
      ON ((c.platform='google' AND l.gclid=c.gclid) OR (c.platform='meta' AND l.fbclid=c.fbclid))
     AND c.click_time <= l.created_at
     AND c.click_time >= l.created_at - INTERVAL '7 days'
  ) t
  JOIN crm_leads l ON l.lead_id = t.lead_id
  JOIN ad_clicks c ON c.campaign_id = t.campaign_id
  WHERE t.rn = 1
),
orders_by_day AS (
  SELECT o.order_id, o.revenue, DATE(o.order_date) AS order_date, o.lead_id
  FROM crm_orders o
)
SELECT
  s.spend_date,
  s.campaign_id,
  COALESCE(SUM(CASE WHEN l.conv_date = s.spend_date THEN o.revenue ELSE 0 END),0) AS revenue,
  SUM(s.spend) AS spend,
  CASE WHEN SUM(s.spend) = 0 THEN NULL ELSE COALESCE(SUM(CASE WHEN l.conv_date = s.spend_date THEN o.revenue ELSE 0 END),0) / SUM(s.spend) END AS roas
FROM ad_spend_daily s
LEFT JOIN last_click l ON l.campaign_id = s.campaign_id
LEFT JOIN orders_by_day o ON o.lead_id = l.lead_id AND DATE(o.order_date) = s.spend_date
GROUP BY s.spend_date, s.campaign_id
ORDER BY s.spend_date, s.campaign_id;

Exercises you can run

These mirror the Exercises section below. Use the table structures defined earlier. If your warehouse differs, adapt column names.

Exercise 1 — Build last-click attribution with click IDs

Task: For leads created in the last 30 days, attach the latest eligible ad click (within 7 days before lead creation), preferring platform-specific IDs.

• Input tables: ad_clicks, crm_leads
• Output columns: lead_id, platform, campaign_id, click_time, lead_created_at

Exercise 2 — Daily ROAS

Task: Using your attribution from Exercise 1, calculate daily spend, revenue, and ROAS by campaign for the last 14 days.

• Input tables: ad_spend_daily, crm_orders, plus your attribution mapping
• Output columns: spend_date, campaign_id, revenue, spend, roas

Checklist (self-review)

• Time zones aligned
• Attribution window enforced (7 days)
• Duplicates removed with ROW_NUMBER()
• Null-safe joins (no accidental Cartesian products)
• Revenue and spend use the same date grain

Common mistakes and how to self-check

• Using INNER JOIN when you need LEFT JOIN for audits. Self-check: count leads lost at each step.
• Missing time window. Self-check: verify no click_time after lead creation.
• Many-to-many duplicates (e.g., UTMs). Self-check: row counts explode after join; use ROW_NUMBER() to pick one.
• Mismatched date grains. Self-check: sum daily equals monthly totals.
• Ignoring data quality (empty utm_campaign). Self-check: profile null rates by field.

Practical projects

• Lead Quality Dashboard: Show leads by campaign with SQL filters for status=Qualified and last-click attribution.
• CAC and ROAS Report: Join daily spend to orders and compute CAC per campaign.
• Attribution Audit: Compare match rates for click IDs vs UTM fallback, by platform.

Learning path

1. Join basics: INNER vs LEFT, null handling
2. Identifiers: click IDs and UTM consistency
3. Attribution windows with window functions
4. Aggregation for KPIs (CPL, CAC, ROAS)
5. Validation and reconciliation techniques

Next steps

• Extend to multi-touch attribution using window functions
• Build cohort analyses for LTV by campaign
• Automate daily materialized views for dashboards

Mini challenge

Create a table lead_attribution(lead_id, campaign_id, platform, attributed_click_time, rule) for the last 60 days using a 7-day last-click rule with ID-first, UTM-second fallback. Then compute: top 5 campaigns by qualified lead rate (status in ('Qualified','Opportunity')).