Tracking · Attribution & Source Tracking

Map Marketing Campaign IDs to CRM Deals

Stitch campaign IDs from your ad platforms and marketing tools all the way through to closed-won deals.

managerAdvanced⏱ 10-14 hours
When to use
Use this when you can see ad spend per campaign in Meta/Google but cannot see ARR per campaign in your CRM. Run before any paid scale-up, before reporting ROAS to a client or your founder, or when integrating a new email/ad tool.
The prompt
You are a marketing analytics consultant for digital marketing agencies. You wire campaign IDs across ad platforms, marketing tools, and CRMs so spend and revenue meet in one row.
Agency: [AGENCY_NAME] — [SERVICES] | CRM: [CRM_TOOL] | Ad platforms: [AD_PLATFORMS] | Marketing tools: [MARKETING_TOOLS] | Current join state: [CURRENT_STATE] | Reporting destination: [REPORT_DESTINATION]
Design the field structure, naming convention, and data flow that carries each platform's Campaign ID into the CRM deal record and into the final ROI report.

- One canonical Campaign ID field at the deal level — not 5 platform-specific fields
- Platform Campaign IDs stored on the contact (first-touch) and copied to deal on association
- Use a campaign naming convention as the bridge when IDs collide across platforms
- Spend pulled from platform APIs, revenue pulled from CRM, joined on Campaign ID in [REPORT_DESTINATION]
- Must handle multi-platform campaigns (same campaign name across Meta + Google + LinkedIn)

1) Data model: which IDs live where (table)
2) Naming convention bridge (when IDs don't match)
3) Flow diagram described in text: ad click -> UTM -> contact -> deal -> report
4) Join spec: how to combine ad platform spend and CRM revenue in [REPORT_DESTINATION]
5) Failure modes and how to detect each
Variables
  • [AGENCY_NAME] — Your agency name
  • [SERVICES] — Your services
  • [CRM_TOOL] — Your CRM
  • [AD_PLATFORMS] — Meta, Google, LinkedIn, Reddit, etc.
  • [MARKETING_TOOLS] — HubSpot Marketing, Klaviyo, Customer.io, etc.
  • [CURRENT_STATE] — What you can and cannot join today
  • [REPORT_DESTINATION] — Looker, Hex, Sheets, native CRM, etc.
Example input
Agency: Northbeam Studio | CRM: HubSpot | Ad platforms: Meta, Google, LinkedIn | Marketing tools: HubSpot Marketing | Current: spend in platforms, revenue in HubSpot, no join | Report: Hex
Example output
DATA MODEL
Meta Ads -> ad_id, adset_id, campaign_id (in Meta)
Google Ads -> gclid, campaign_id (in Google)
LinkedIn -> li_fat_id, campaign_id (in LinkedIn)
Contact in HubSpot -> First-Touch Campaign Name (text), First-Touch Campaign ID (text), First-Touch Platform (picklist)
Deal in HubSpot -> Campaign Name (copied from contact), Campaign ID (copied from contact), Platform (copied)

NAMING BRIDGE
Canonical campaign name: yyyyqN-offer-audience (same across Meta/Google/LinkedIn). When platform Campaign IDs differ, join on canonical name.

FLOW
Ad click w/ UTM -> landing page -> form fill -> HubSpot contact created with utm_campaign captured -> first deal associated copies fields from contact -> Hex pulls spend (platform APIs) + revenue (HubSpot) joined on canonical campaign name.

JOIN SPEC (Hex)
SELECT campaign_name, SUM(spend), SUM(closed_won_arr), SUM(closed_won_arr)/NULLIF(SUM(spend),0) AS roas
FROM spend_union JOIN deals USING (campaign_name)
GROUP BY 1.

FAILURE MODES
Null Campaign ID on deal -> contact wasn't tagged at form fill (fix UTM hygiene).
Same campaign name in 2 platforms but you want them separate -> add platform suffix.
Pro tips
  • The canonical campaign NAME is your join key, not the platform Campaign ID — IDs change, names you control
  • Audit null Campaign ID rate weekly — anything over 10% means a form, redirect, or pixel is broken
  • Pull spend daily, not monthly — month-end reconciliation hides intra-month problems until it's too late to fix
Works with
ClaudeChatGPTGemini
Done with prompts? Time to install the system
Book a STAOS call
Related prompts