Ad Campaign Workflow Data Model and SDR Inbound Pipeline: The Technical Blueprint

Why Ad Campaign Data Models Break at Scale

Most paid media operations run on ad-hoc naming conventions. Campaign names that made sense when there were 10 campaigns become incomprehensible noise when there are 300. Tags applied inconsistently make filtering impossible. Status fields that were never standardised leave teams unable to answer basic questions: which campaigns are paused versus archived? Which are seasonal hold versus permanently inactive? Which creative variants are still being tested versus already deprecated?

The data model failure compounds when leads generated by campaigns flow into an SDR pipeline with no systematic tracking of source, intent signal, or campaign context. The SDR receives a lead notification with minimal context and starts a conversation from zero, wasting the behavioural intelligence the ad campaign already captured.

This article documents the technical blueprint for a proper ad campaign workflow data model and the SDR inbound pipeline that connects to it. The Follett Software model for inbound marketing pipeline conversion is referenced as a case study in systematic SDR qualification, and the n8n automation layer that connects the two systems is described in detail.

The Ad Campaign Data Model

Core Data Structure

Every campaign record in your data model should capture five categories of attributes:

Identity fields (what the campaign is): - `campaign_id`: System-generated unique identifier (UUID format) - `campaign_name`: Human-readable name following a defined convention (see below) - `platform`: Facebook/Meta, Google, LinkedIn, TikTok, Microsoft, Twitter/X - `account_id`: Platform account ID for multi-account organisations - `campaign_type`: Awareness, Consideration, Conversion, Retargeting, Brand

Taxonomy fields (how the campaign is classified): - `tags`: Multi-value array of descriptive tags (see tag taxonomy below) - `labels`: Platform-specific labels for cross-platform filtering - `product_line`: Which product or service the campaign promotes - `target_audience`: Audience segment code from your audience library - `funnel_stage`: TOFU, MOFU, BOFU

Status fields (what state the campaign is in): - `status`: One of: DRAFT, ACTIVE, PAUSED, SEASONAL_HOLD, UNDER_REVIEW, ARCHIVED, DEPRECATED - `status_changed_at`: Timestamp of last status change - `status_changed_by`: User who made the status change - `status_reason`: Mandatory field explaining why status changed (e.g., "Budget exhausted", "Creative refresh needed", "Seasonal: resume March 2027")

Performance fields (how the campaign is doing): - `spend_lifetime`: Total spend pulled from platform API - `impressions_lifetime`: Total impressions - `clicks_lifetime`: Total clicks - `conversions_lifetime`: Total conversions by type - `roas_lifetime`: Return on ad spend - `cpa_lifetime`: Cost per acquisition - `performance_tier`: AUTO-CALCULATED: Winner (top 25% ROAS), Normal, Underperformer (bottom 25%)

Archiving fields (how the campaign is preserved): - `archived_at`: Timestamp of archiving - `archive_reason`: Structured reason code - `last_active_date`: Last date the campaign delivered impressions - `learnings_note`: Free text field for recording what was learned from the campaign

Campaign Naming Convention

Inconsistent naming is the root cause of data model failures. Every campaign name should encode key attributes so the name itself is filterable and informative:

Format: `[Platform]-[ProductLine]-[AudienceCode]-[FunnelStage]-[Creative]-[Date]`

Example: `META-ENTERPRISESUITE-FINTECH-BOFU-VIDEO-JUL26`

Breaking this down: - `META`: Platform abbreviation (META, GOOG, LNKD, TKTK, MSFT) - `ENTERPRISESUITE`: Product line code (max 15 characters) - `FINTECH`: Target audience segment code - `BOFU`: Funnel stage (TOFU/MOFU/BOFU/RETG) - `VIDEO`: Creative format (VIDEO, STATIC, CAROUSEL, SEARCH, DISPLAY) - `JUL26`: Launch month and year

This convention makes every campaign name self-describing. Filtering for all BOFU campaigns across platforms is a single text search. Finding all campaigns targeting FINTECH audiences is instant. Identifying campaigns from H1 2025 for archiving review requires no manual sorting.

Tag Taxonomy

Tags are multi-value and hierarchical. Define them at three levels:

Level 1, Strategic tags (one per campaign): `brand-awareness`, `lead-generation`, `revenue-acceleration`, `retention`, `competitive-attack`, `new-market-entry`

Level 2, Audience tags (one or more): `smb`, `mid-market`, `enterprise`, `cold-prospect`, `warm-prospect`, `existing-customer`, `churned-customer`

Level 3, Tactical tags (one or more): `a-b-test`, `seasonal`, `evergreen`, `localized`, `competitor-keyword`, `retargeting-7d`, `retargeting-30d`, `lookalike`

When a campaign has `a-b-test` tagged, the data model also captures: `test_hypothesis`, `test_variable`, `test_start_date`, `test_end_date`, `test_winner` (set when test concludes).

Status Lifecycle and Archiving Rules

The seven status values each have defined transition rules:

DRAFT → Can move to ACTIVE (campaign launch), ARCHIVED (never launched, abandoned)

ACTIVE → Can move to PAUSED (temporary hold), SEASONAL_HOLD (scheduled pause, planned return), UNDER_REVIEW (performance concern), ARCHIVED (permanent end)

PAUSED → Can move to ACTIVE (resume), ARCHIVED (confirmed end), SEASONAL_HOLD (extended hold)

SEASONAL_HOLD → Can move to ACTIVE (season resumes), ARCHIVED (campaign retired)

UNDER_REVIEW → Can move to ACTIVE (issues resolved), PAUSED (pending fix), ARCHIVED (underperformer retired)

ARCHIVED → Terminal state. Can move to DEPRECATED only (when the campaign record is scheduled for deletion from active database after retention period)

DEPRECATED → Records move here 2 years after archiving. After 3 years total, deleted per data retention policy.

The SDR Inbound Pipeline: The Follett Software Model

Follett Software, a provider of library management systems for K-12 schools, built a systematic approach to inbound marketing pipeline conversion that reduced average lead-to-opportunity time from 12 days to 4.5 days and increased SQL conversion rate from 18% to 34%. The model is worth studying because it applies to any B2B company with a defined buyer journey and multiple lead sources.

The core principles of the Follett model:

Speed to first meaningful contact. Response time is the single biggest predictor of conversion. Follett mandated a 5-minute response to any inbound lead during business hours. An SDR workflow trigger fires the moment a lead submits, routing to the next available SDR and providing a pre-researched contact brief.

Context richness before the first call. Every SDR receives a lead brief that includes: how the lead found them (which ad, which content, which keyword), what they engaged with on the website, what their firmographic profile suggests about their needs, and suggested discovery questions based on their engagement pattern. SDRs never start from zero.

Qualification by intent signal, not just ICP fit. A lead that fits the ICP perfectly but has viewed only one blog post has different intent than one that has visited the pricing page 3 times, watched a demo video, and downloaded a case study. Scoring incorporates engagement depth, not just company profile.

Systematic follow-up with defined exit criteria. The SDR sequence has exactly 7 touches over 21 days before a lead is moved to long-term nurture: calls (days 1, 4, 10, 21), emails (days 1, 7, 14), LinkedIn connection (day 3). If no response after 7 touches, the lead is not abandoned, it moves to a 90-day nurture cadence with monthly content touches.

Connecting Ad Campaign Data to the SDR Pipeline

The intelligence gap between advertising and sales is where most revenue is lost. An SDR receiving a lead has typically zero information about which ad they clicked, what they found compelling about the message, or what problem it was addressing. That context is sitting in your ad platform data model, disconnected.

The n8n automation layer closes this gap.

The Campaign-to-Lead Attribution Workflow

How it works

1. Lead submits a form on your website. The form captures a UTM parameter: `utm_campaign=META-ENTERPRISESUITE-FINTECH-BOFU-VIDEO-JUL26` 2. n8n receives the form submission via webhook 3. The workflow parses the UTM campaign code against the campaign data model database 4. It retrieves the full campaign record: product line, audience segment, funnel stage, creative format, and any A/B test hypothesis being tested 5. It queries the ad platform API (Meta Marketing API, Google Ads API, or LinkedIn Campaign Manager API) for the specific ad the lead clicked, including the ad creative text, image, and headline 6. This campaign context is written to the lead record in the CRM alongside the standard contact fields 7. The SDR receives a lead alert in Slack that includes: name, company, role, email, phone, ICP score, and the specific ad they engaged with including the ad creative text

The SDR opens their first conversation knowing exactly what message resonated with this lead. That context shapes the conversation. Instead of "How did you hear about us?", the SDR can say "I see you came in through our content about reducing library catalog admin time, is that the challenge you're working on?"

The SDR Pipeline Data Model

The SDR pipeline connects to the campaign data model through shared attribution fields:

Lead record fields added by campaign attribution

• `source_campaign_id`: Foreign key to campaign data model
• `source_ad_id`: Specific ad within the campaign
• `source_keyword`: For search campaigns, the exact keyword that triggered the ad
• `source_audience_segment`: The audience segment that matched this lead
• `ad_creative_headline`: Text of the ad headline they clicked
• `ad_creative_body`: Text of the ad copy
• `campaign_funnel_stage`: TOFU/MOFU/BOFU (informs where to start the conversation)

SDR pipeline stage fields

• `pipeline_stage`: New Lead, Contacted, Qualified, Discovery Scheduled, SQL, Opportunity, Closed Won, Closed Lost, Long-Term Nurture
• `sdr_assigned`: SDR owner
• `first_contact_at`: Timestamp of first outreach attempt
• `first_response_at`: Timestamp of first response from lead (calculates response time lag)
• `qualification_score`: Composite score from ICP fit + engagement depth + ad campaign intent signal
• `disqualification_reason`: Structured reason code if moved to Closed Lost or Long-Term Nurture

The Lead Routing Workflow

When a lead arrives, the routing workflow must assign it to the right SDR within 5 minutes during business hours:

1. n8n receives lead via form webhook 2. Deduplication check: is this lead already in CRM? If yes, route to account owner. If no, continue. 3. ICP scoring: query Clearbit or Apollo for firmographic enrichment. Calculate ICP score (company size, industry, revenue, tech stack). 4. Intent scoring: query website behaviour data from Segment or Heap for engagement depth. 5. Campaign intent signal: retrieve campaign funnel stage from attribution data. 6. Combined score determines routing: High score → immediate Slack alert to senior SDR, calendar slot offer via Calendly link in first email. Medium score → standard SDR round-robin. Low score → nurture sequence only, SDR notified for context. 7. First email is generated by Claude AI based on lead context and campaign message, sent immediately via email provider API. 8. Slack alert to assigned SDR with full lead brief and suggested opening.

A/B Testing SDR Sequences via Campaign Connection

Because every lead is tagged with their source campaign, you can run SDR sequence experiments tied to ad campaign experiments. If your ad campaign is testing two different value propositions (A: time savings, B: compliance risk reduction), you can route leads from each variant into different SDR opening sequences and measure which campaign-to-SDR message consistency produces higher qualification rates.

This closes the feedback loop between advertising and sales that most companies have never achieved. Learnings from SDR conversations flow back into campaign optimization. Campaign message testing informs SDR opening strategy. The two functions become a coordinated revenue system.

Implementation: Building the Connected Workflow in n8n

• Database structure: Two Postgres tables: `campaigns` (the data model described above) and `leads` (CRM mirror with attribution fields added). n8n reads from and writes to both.

n8n workflows required

1. Campaign sync workflow: Daily scheduled job that syncs campaign data from all ad platforms to the campaigns table, updating performance metrics, detecting status changes, and flagging anomalies.

2. Lead ingestion workflow: Webhook receiver for form submissions. Runs attribution lookup, enrichment, scoring, and routing. Execution time target: under 90 seconds from form submit to SDR alert.

3. SDR sequence workflow: Scheduled daily. Queries leads table for sequence step due dates. Generates personalised outreach for each due lead. Posts to CRM task system and email provider.

4. Performance reporting workflow: Weekly report connecting campaign performance to pipeline metrics, cost per SQL by campaign, campaign-to-close rate by audience segment, ROAS adjusted for pipeline stage conversion.

5. Archiving workflow: Monthly scheduled job reviewing campaigns against archiving rules. Flags candidates for archiving with one-click confirmation in Slack.

Frequently Asked Questions

How granular should campaign tags be?

Tags should be granular enough to enable filtering but not so granular that every campaign has unique tags. A practical rule: any tag that applies to fewer than 5% of your campaigns is too specific and should be captured in a notes field instead. Review your tag taxonomy quarterly and merge or retire tags with low usage.

What is the right number of SDR sequence touches?

Research from TOPO (now Gartner) suggests 7-9 touches in the first 21 days maximises contact rate while minimising unsubscribe risk. The Follett model uses 7. The exact number matters less than the quality of each touch and the consistency of execution, which automation guarantees.

How do we handle leads from multiple campaigns (cross-device, multi-touch)?

Multi-touch attribution is complex. For SDR routing purposes, use last-click attribution for simplicity: the campaign that generated the final form submission owns the lead. For revenue attribution (understanding which campaigns contributed to deals), use a data-driven attribution model in your analytics platform. The two can coexist: last-click for operational routing, multi-touch for strategic budget decisions.

Can this work for outbound SDR sequences as well?

The pipeline data model and sequence automation work identically for outbound. The difference is the attribution data: instead of campaign click data, outbound leads are tagged with the outbound sequence they were enrolled in, the list source, and the ICP criteria that qualified them for outreach.

What tools are needed beyond n8n?

Minimum stack: n8n, PostgreSQL, your CRM (HubSpot or Salesforce), your ad platforms (Meta, Google, LinkedIn), an email provider with API (Mailchimp, Klaviyo, or Brevo), and Slack. Optional but valuable: Clearbit or Apollo for enrichment, Segment or Heap for website behaviour data, and a BI tool (Metabase or Looker) for pipeline analytics dashboards.

How long does implementation take?

The campaign data model and sync workflows take 2-3 weeks. The lead ingestion and routing workflow takes 1-2 weeks. The SDR sequence automation takes 1-2 weeks. Total: 4-7 weeks from start to fully connected system. Each component delivers value independently, so the campaign data model can go live and run for 2-3 weeks while the SDR pipeline is being built.

Ready to build a connected ad-to-pipeline revenue system? Book a scoping call at PURIST and we will map the specific integration architecture for your ad platforms, CRM, and SDR process.