This guide explains how to build the dbt-nexus conversion pipeline for server-side marketing destinations (Facebook CAPI, Google Ads Enhanced Conversions, Reddit CAPI, TikTok, LinkedIn, and others).

The pattern is composable:

• Define conversion logic once in a canonical model
• Project thin, destination-specific payload tables
• Deliver with any reverse ETL or extraction tool

Reverse ETL strategy

flowchart TB
  sor[Operational systems of record] --> wh[(Data warehouse)]
  touch[Web and ad touchpoints] --> wh
  wh --> dbt[dbt: identity, attribution, conversion logic]
  dbt --> tables[Tables shaped like each platform API]
  tables --> retl[Reverse ETL]
  retl --> apis[Facebook CAPI, Google Ads, ...]

Ad platforms optimize on the conversions they receive. Offline, delayed, and cross-system outcomes — enrollments in a contract system, appointments booked by phone, renewals in a billing system — never show up in browser pixels or server-side tag flows that are still triggered by web events. Point-to-point webhooks and one-off scripts rarely capture the full picture, break under change, and are hard to audit.

Treat server-side conversion delivery as a data modeling problem. The data warehouse is already the system of record for revenue and customer outcomes. Identity resolution ties those outcomes to real people (email, phone, address). Attribution models tie ad clicks (fbclid, gclid, and others) to those people even when the conversion happens days or weeks later. dbt-nexus normalizes events and touchpoints so that logic lives in SQL you can test and review — not in tag manager UIs or bespoke servers.

Reverse ETL is the delivery layer: sync rows from warehouse tables shaped like each destination's API (Facebook CAPI, Google Ads Enhanced Conversions, etc.) to the platform. The conversion pipeline below produces those tables; your tool (Census, Hightouch, Segment Reverse ETL, custom jobs) only maps columns to the API — no business logic in the sync tool.

Why this approach wins for technical teams:

• Single source of truth — Values and eligibility come from the same models finance and operations trust.
• Backfill and recovery — Historical conversions can be sent when the pipeline goes live; if a sync or API fails, data remains in the warehouse and you re-sync after fixing the issue.
• Observability — Build dashboards that make auditing easy: e.g. conversion volume over time by event name, row-level views of the fields headed to each platform, and reconciliation to reverse ETL send logs when your tool writes them back. Examples below (illustrative sample data).
• Version control — Every change to mappings and eligibility is a git commit with review and tests.
• Destination-agnostic — Add a new platform with a thin Layer 2 projection; business rules stay in Layer 1 once.

For a non-technical overview of why this works in practice — including composite client examples — see Send Complete Conversions to Facebook CAPI and Google Ads.

Architecture

flowchart LR
  src[Source Systems] --> nexusEvents[nexus_events]
  nexusEvents --> layer1[marketing_conversions_canonical]
  layer1 --> fb[marketing_conversions_facebook]
  layer1 --> ga[marketing_conversions_google_ads]
  layer1 --> other[other destinations]
  fb --> tools[Reverse ETL / Extraction]
  ga --> tools
  other --> tools

The pipeline has two layers:

Layer	Model	Materialization	Purpose
1	marketing_conversions_canonical	table	Centralized business logic, enrichment, and cross-destination mappings
2	marketing_conversions_facebook, marketing_conversions_google_ads, etc.	view	Thin destination-specific projections with filtering and delivery-native column names

All mapping logic belongs in Layer 1. Layer 2 models are filtered views that rename columns to destination-native names, apply destination-specific eligibility rules (recency windows, send-log exclusion), and generate deterministic destination_sync_id values. They should not re-implement business logic.

Directory Structure

Group all marketing conversion models in a single directory so they sort together in the warehouse and are easy to find in the project:

models/
  outputs/
    marketing-platform-conversions/
      marketing_conversions_canonical.sql    -- Layer 1
      marketing_conversions_facebook.sql     -- Layer 2: Facebook CAPI
      marketing_conversions_google_ads.sql   -- Layer 2: Google Ads
      marketing_conversions_reddit.sql       -- Layer 2: Reddit (if needed)

The shared marketing_conversions_ prefix ensures all models appear as a group in the data warehouse's table listing, making it obvious which tables belong to this pipeline.

---

Layer 1: marketing_conversions_canonical

This is the canonical conversion model. It owns:

• Event eligibility filtering
• Person/entity enrichment
• Attribution identifier joins (fbclid, gclid, etc.)
• Deduplication
• Cross-destination field mappings (event type names, conversion action IDs)

Prerequisites

The model depends on these upstream nexus tables:

Model	Purpose
nexus_events	Core event log (filter by event_name, source)
nexus_event_dimensions	Event dimensions including source_record_id
nexus_event_measurements	Event measurements (e.g. revenue, premium)
nexus_entity_participants	Links events to person entities
nexus_entities	Person contact fields (email, phone, name, address)
last_fbclid	Attribution model for Facebook click IDs
last_gclid	Attribution model for Google click IDs

You will also join any source-specific models needed for your client (e.g. source event tables with client-specific fields).

CTE Pattern

The model follows a four-CTE pattern. Each CTE has a single responsibility:

{{ config(
    materialized='table',
    tags=['conversions', 'outputs']
) }}

{% set event_maps = {
    'facebook_event_name': {
        'your_event_a': "'Purchase'",
        'your_event_b': "'Lead'",
        'default': "'Other'",
    },
    'google_ads_conversion_action_id': {
        'your_event_a': "'CONVERSION_ACTION_ID_A'",
        'your_event_b': "'CONVERSION_ACTION_ID_B'",
    },
} -%}

WITH target_events AS (
    -- 1. Filter eligible events; join dimensions and measurements.
    SELECT
        e.event_id as nexus_event_id,
        e.occurred_at,
        e.event_name,
        ned.source_record_id,
        m.your_value_field,
        src.your_source_field
    FROM {{ ref('nexus_events') }} e
    INNER JOIN {{ ref('your_source_events') }} src
        ON e.event_id = src.event_id
    LEFT JOIN {{ ref('nexus_event_measurements') }} m
        ON e.event_id = m.event_id
    LEFT JOIN {{ ref('nexus_event_dimensions') }} ned
        ON e.event_id = ned.event_id
    WHERE e.event_name IN ('your_event_a', 'your_event_b')
      AND e.source = 'your_source'
),

event_participants AS (
    -- 2. Join person participants; generate conversion_sync_id.
    SELECT
        {{ nexus.create_nexus_id(
            'conversion_sync_id',
            ['nexus_event_id', 'ep.entity_id']
        ) }} as conversion_sync_id,
        te.*,
        ep.entity_id
    FROM target_events te
    INNER JOIN {{ ref('nexus_entity_participants') }} ep
        ON te.nexus_event_id = ep.event_id
        AND ep.entity_type = 'person'
    WHERE ep.role = 'primary_customer'
),

attribution AS (
    -- 3a. Pivot attribution results into one row per (event, entity).
    SELECT
        attributed_event_id,
        entity_id,
        MAX(CASE WHEN attribution_model_name = 'last_fbclid' THEN fbclid END) as fbclid,
        MAX(CASE WHEN attribution_model_name = 'last_fbclid' THEN touchpoint_occurred_at END) as fbclid_occurred_at,
        MAX(CASE WHEN attribution_model_name = 'last_gclid' THEN gclid END) as gclid,
        MAX(CASE WHEN attribution_model_name = 'last_gclid' THEN touchpoint_occurred_at END) as gclid_occurred_at
    FROM {{ ref('nexus_attribution_model_results') }}
    WHERE attribution_model_name IN ('last_fbclid', 'last_gclid')
    GROUP BY attributed_event_id, entity_id
),

marketing_conversions_joined AS (
    -- 3b. Enrich with person fields + attribution click IDs; dedupe.
    SELECT
        ep.*,
        -- User ID for cross-platform identity matching (see below)
        COALESCE(p.your_user_id, SHA2(LOWER(TRIM(p.email))), ep.entity_id) as user_id,
        p.email,
        p.full_name,
        COALESCE(p.phone, p.mobile_phone) as phone,
        p.first_name,
        p.last_name,
        p.city,
        p.state,
        p.postal_code,
        p.country,
        attr.fbclid,
        attr.fbclid_occurred_at,
        attr.gclid,
        attr.gclid_occurred_at,
        ROW_NUMBER() OVER (
            PARTITION BY ep.conversion_sync_id
            ORDER BY ep.occurred_at
        ) as rn
    FROM event_participants ep
    LEFT JOIN {{ ref('nexus_entities') }} p
        ON ep.entity_id = p.entity_id
    LEFT JOIN attribution attr
        ON ep.nexus_event_id = attr.attributed_event_id
        AND ep.entity_id = attr.entity_id
),

marketing_conversions_mapped AS (
    -- 4. Apply destination mappings; rename to semantic output columns.
    SELECT
        -- Core event fields
        conversion_sync_id,
        nexus_event_id,
        user_id,
        entity_id,
        occurred_at,
        event_name,
        source_record_id,
        your_value_field as value,
        'USD' as currency,

        -- Person/contact fields
        email,
        full_name,
        phone,
        first_name,
        last_name,
        city,
        state,
        postal_code,
        country,

        -- Facebook fields (prefixed to avoid collision with nexus column names)
        {{ nexus.case_map('event_name', event_maps.facebook_event_name) }}
            as facebook_event_name,
        EXTRACT(EPOCH FROM occurred_at)::BIGINT as facebook_event_time,
        source_record_id as facebook_event_id,
        user_id as facebook_external_id,
        your_contract_id as facebook_subscription_id,
        'product' as facebook_content_type,
        'website' as action_source,

        -- Google Ads fields
        {{ nexus.case_map('event_name', event_maps.google_ads_conversion_action_id) }}
            as google_ads_conversion_action_id,
        source_record_id as google_ads_order_id,
        gclid,
        gclid_occurred_at,
        CAST(NULL AS VARCHAR) as gbraid,
        CAST(NULL AS VARCHAR) as wbraid,

        -- Facebook attribution
        fbclid,
        fbclid_occurred_at
    FROM marketing_conversions_joined
    WHERE rn = 1
)

SELECT * FROM marketing_conversions_mapped
ORDER BY occurred_at DESC

The sections below explain the key design decisions in this template.

Declarative Event Mapping with event_maps

Instead of scattering CASE statements throughout the model, define all event-to-destination mappings in a single Jinja dictionary at the top of the file:

{% set event_maps = {
    'facebook_event_name': {
        'enrollment': "'Purchase'",
        'contract renewed': "'Purchase'",
        'default': "'Other'",
    },
    'google_ads_conversion_action_id': {
        'enrollment': "'12345'",
        'contract renewed': "'67890'",
    },
} -%}

Each top-level key is a mapping name. Each nested dictionary maps event_name values to SQL expressions. The optional 'default' key provides a fallback (defaults to null if omitted).

Use nexus.case_map to apply a mapping:

{{ nexus.case_map('event_name', event_maps.facebook_event_name) }}
    as facebook_event_name

This compiles to:

case
    when event_name = 'enrollment' then 'Purchase'
    when event_name = 'contract renewed' then 'Purchase'
    else 'Other'
end as facebook_event_name

Benefits:

• All event-specific logic is visible in one place at the top of the file
• Adding a new event or destination mapping is a dictionary edit, not a scattered CASE change
• The macro is generic (works on any column, not just event_name); see the case_map macro reference for details

source_record_id as the Dedup Key

source_record_id is a dimension from nexus_event_dimensions representing the source system's business record identifier for an event. Examples:

• Contract management system: contract_number (new contracts) / contract_sequence_id (renewals)
• Stripe: invoice_id
• Shopify: order_id

This is usually the correct cross-destination deduplication key:

Destination	Layer 1 column	Destination field
Facebook CAPI	source_record_id	event_id
Google Ads Enhanced Conversions	source_record_id	order_id

nexus_events.event_id is an internal identifier not available in the browser, so it must not be used as a destination dedup key. Retain it as nexus_event_id for QA and reconciliation.

See the source_record_id dimension reference for details on how this dimension flows through the pipeline.

user_id for Cross-Platform Identity

Some destinations (notably Facebook CAPI) accept an external_id for identity matching. This field serves two purposes:

1. Browser-to-server matching -- linking a pixel event to a CAPI event for the same user session. This only works when the browser and server send the same value.
2. Cross-event user stitching -- linking multiple server-side events to the same user, even without a browser match. Facebook uses external_id to build a user profile across conversions, which improves attribution modeling, audience building, and ad optimization.

Because of (2), a consistent non-null user_id is always better than null -- even if the browser never sees the value. Sending three conversions with the same external_id tells Facebook they're the same person.

Fallback chain:

COALESCE(
    p.your_user_id,                     -- best: genuine user ID known to the browser
    SHA2(LOWER(TRIM(p.email))),         -- good: deterministic hash the browser can reproduce
    ep.entity_id                        -- fallback: server-only, still links events together
) as user_id

Priority	Value	Browser can match?	Cross-event stitching?
1	Real user ID (CRM, account ID)	Yes	Yes
2	SHA2(LOWER(TRIM(email)))	Yes (if front end hashes the same way)	Yes
3	entity_id	No	Yes
--	NULL	No	No

The front end must apply the same normalization (lowercase, trim) and hash algorithm for priorities 1 and 2 so both sides produce the same value.

This user_id is computed in marketing_conversions_joined and carried through to the final output. Destination models map it to destination-native fields:

Destination	Layer 1 column	Destination field
Facebook CAPI	user_id	external_id
Google Ads Enhanced Conversions	N/A	(no equivalent -- uses hashed PII directly)

Destination-Prefixed Columns

Layer 1 contains columns for multiple destinations alongside nexus-native columns. Without a prefix, names collide -- both nexus and Facebook have an event_name, both have an event_id, etc.

The convention is to prefix destination-specific mapped columns with the destination name:

Layer 1 column	Purpose
event_name	nexus event name (enrollment, contract renewed)
facebook_event_name	Facebook standard event (Purchase, Lead)
facebook_event_id	Facebook dedup key (from source_record_id)
facebook_external_id	Facebook identity match (from user_id)
google_ads_conversion_action_id	Google Ads conversion action ID
google_ads_order_id	Google Ads dedup key (from source_record_id)

Layer 2 destination models strip the prefix so their output tables mirror the actual API payload (e.g. facebook_event_name becomes event_name). See the Facebook CAPI guide for a concrete example.

action_source

Facebook requires action_source on every event to indicate where the conversion originated. For server-side conversion events sent via CAPI, this is almost always 'website'. Hard-code it in Layer 1:

'website' as action_source

This value flows through to Layer 2 without renaming since it already matches the Facebook parameter name. If you have non-web events (e.g. in-store, phone), use event_maps to map different event names to different action sources.

Recency Windows (Backfill Limits)

Each destination enforces a maximum event age. Layer 2 models should filter on occurred_at to stay within these limits:

Destination	Max age	Filter
Facebook CAPI	7 days	WHERE occurred_at >= DATEADD('day', -7, CURRENT_TIMESTAMP())
Google Ads Enhanced Conversions	90 days	WHERE occurred_at >= DATEADD('day', -90, CURRENT_TIMESTAMP())

Facebook rejects the entire batch if any event has an event_time older than 7 days. Google Ads silently drops conversions older than 90 days from the click date. Apply these filters in Layer 2 so Layer 1 retains the full historical record.

Hashing

PII fields (email, phone, first_name, last_name, city, state, zip_code, country) are stored unhashed in both Layer 1 and Layer 2. The reverse ETL tool or delivery adapter is responsible for applying destination-specific hashing (e.g. SHA-256 for Facebook, SHA-256 for Google Ads) at send time. This keeps the dbt models readable and the data queryable for QA.

Materialization

Use table for Layer 1. The model performs multiple joins (events, dimensions, measurements, participants, entities, attribution) and is read by every Layer 2 destination model. Materializing as a table avoids redundant computation.

Use view for Layer 2 destination models. They are thin projections with minimal logic and benefit from always reflecting the latest Layer 1 data.

---

Layer 2: Destination Projections

Each destination gets its own model that reads from marketing_conversions_canonical. These models are intentionally thin:

{{ config(materialized='view', tags=['facebook', 'capi', 'outputs']) }}

SELECT
    -- strip facebook_ prefix so columns match the CAPI payload
    mpc.facebook_event_name as event_name,
    mpc.facebook_event_time as event_time,
    mpc.facebook_event_id as event_id,
    mpc.facebook_external_id as external_id,
    mpc.action_source,
    mpc.value,
    mpc.currency,
    -- ... person fields, attribution fields ...
FROM {{ ref('marketing_conversions_canonical') }} mpc
WHERE mpc.facebook_event_name IS NOT NULL
  AND mpc.occurred_at >= DATEADD('day', -7, CURRENT_TIMESTAMP())

What belongs in Layer 2

• Column renaming to destination-native names
• Destination-specific filtering (recency windows, send-log exclusion)
• Deterministic destination_sync_id generation
• Nullable placeholders for fields the destination expects but you don't populate yet (e.g. fbp, client_ip_address)

What does NOT belong in Layer 2

• Business logic (event eligibility, value calculation)
• Cross-destination mappings (event type names, conversion action IDs)
• Attribution joins
• Deduplication

This boundary keeps destination models auditable: if a mapping changes, the diff is in Layer 1 where it is versioned, reviewed, and tested once.

See the destination-specific guides for full templates:

• Facebook CAPI
• Google Ads Enhanced Conversions

Adding a New Destination

1. Add any new mapping entries to the event_maps dictionary in marketing_conversions_canonical
2. Add destination-specific columns to the marketing_conversions_mapped CTE if needed (e.g. a new reddit_event_type)
3. Create a new Layer 2 model that selects from marketing_conversions_canonical with destination-native column names
4. Add schema tests for the new destination model

---

Data Quality

Schema tests for Layer 1

models:
  - name: marketing_conversions_canonical
    columns:
      - name: conversion_sync_id
        tests:
          - not_null
          - unique
      - name: nexus_event_id
        tests:
          - not_null
      - name: occurred_at
        tests:
          - not_null
      - name: source_record_id
        description: >
          Source-system business record identifier. Used as the
          cross-destination dedup key.

Schema tests for Layer 2

Each destination model should test its own sync ID:

- name: marketing_conversions_facebook
  columns:
    - name: facebook_event_sync_id
      tests:
        - not_null
        - unique
    - name: destination_sync_id
      tests:
        - not_null

---

Delivery Tooling

This pattern works with any reverse ETL or extraction tool (Segment, Census, Hightouch, custom scripts). Point the tool at the Layer 2 destination table and configure a simple column sync -- no mapping logic in the tool.

Layer 2 tables represent what should be sent on the next run, making expected-vs-actual reconciliation straightforward: compare the Layer 2 output to send logs from your delivery tool.

---

Reference

• Facebook CAPI Parameters
• Google Ads Enhanced Conversions for Web
• nexus.case_map macro
• source_record_id dimension