marketing_conversions_facebook is a Layer 2 destination model that projects marketing_conversions_canonical into the Facebook Conversions API payload format. It should contain minimal logic -- all business rules and mappings live in Layer 1.

Column names in this model should match the Facebook CAPI parameter names as closely as possible so the table resembles the actual payload. Layer 1 uses a facebook_ prefix on mapped columns (e.g. facebook_event_name, facebook_event_id) to distinguish them from nexus-native columns. Layer 2 strips that prefix, producing destination-native names like event_name, event_id, external_id, etc.

Template

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

WITH facebook_projection AS (
    SELECT
        -- Sync identifiers
        conversion_sync_id,
        nexus_event_id,

        -- Server event parameters
        facebook_event_name as event_name,
        facebook_event_time as event_time,
        facebook_event_id as event_id,
        action_source,
        'https://www.your-domain.com' as event_source_url,

        -- Custom data
        value,
        currency,
        facebook_content_type as content_type,
        contents,

        -- Customer information parameters
        facebook_external_id as external_id,
        facebook_subscription_id as subscription_id,
        entity_id,
        email,
        full_name,
        phone,
        first_name,
        last_name,
        city,
        state,
        zip_code,
        country,

        -- Attribution
        fbclid,
        fbclid_occurred_at,
        fbc,
        fbp
    FROM {{ ref('marketing_conversions_canonical') }}
    WHERE occurred_at >= DATEADD('day', -7, CURRENT_TIMESTAMP())
),

already_sent AS (
    SELECT facebook_event_sync_id as conversion_sync_id
    FROM {{ ref('nexus_facebook_capi_send_log') }}
    WHERE status = 'sent'
)

SELECT fp.*,
    CASE
        WHEN already_sent.conversion_sync_id IS NOT NULL THEN TRUE
        ELSE FALSE
    END as already_sent
FROM facebook_projection fp
LEFT JOIN already_sent
    ON fp.conversion_sync_id = already_sent.conversion_sync_id
WHERE already_sent.conversion_sync_id IS NULL

Hashing

PII fields (email, phone, first_name, last_name, city, state, zip_code, country) are sent unhashed from this model. The reverse ETL tool or delivery adapter is expected to apply SHA-256 hashing per Facebook's normalization requirements before sending to the CAPI endpoint. This keeps the dbt model readable and the data queryable for QA.

external_id is the one exception -- Facebook recommends (but does not require) hashing it. Since it is already computed as SHA2(LOWER(TRIM(email))) in Layer 1, it arrives pre-hashed.

Design Notes

Why the facebook_ Prefix in Layer 1

Layer 1 (marketing_conversions_canonical) 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 facebook_ prefix disambiguates in Layer 1:

Layer 1: event_name             = nexus event name (e.g. 'enrollment')
Layer 1: facebook_event_name    = Facebook event name (e.g. 'Purchase')

Layer 2 strips the prefix so the output table mirrors the actual CAPI payload:

Layer 2: event_name  = 'Purchase'   (from facebook_event_name)
Layer 2: event_time  = 1710300000   (from facebook_event_time)
Layer 2: event_id    = '13189672'   (from facebook_event_id)

event_time

Facebook requires event_time as a Unix timestamp in seconds. Layer 1 computes this from occurred_at:

EXTRACT(EPOCH FROM occurred_at)::BIGINT as facebook_event_time

7-Day Recency Window

Facebook rejects the entire batch if any event has an event_time older than 7 days. Filter in the projection CTE:

WHERE occurred_at >= DATEADD('day', -7, CURRENT_TIMESTAMP())

Layer 1 retains the full historical record. This filter belongs in Layer 2 only.

event_source_url

Facebook requires event_source_url for events with action_source = 'website'. Since these are server-side conversions without a specific page URL, hard-code your client's primary domain:

'https://www.your-domain.com' as event_source_url

This satisfies the requirement and improves event quality scoring.

subscription_id

For subscription-based businesses, subscription_id helps Facebook identify recurring revenue events. This should be the stable contract identifier that persists across renewals (e.g. contract_number), not the per-event source_record_id (which may differ between enrollments and renewals).

Layer 1 maps this as contract_number as facebook_subscription_id.

Pixel Deduplication

Facebook compares browser pixel events and CAPI events using event_id. Both must send the same value for deduplication to work.

nexus_events.event_id is an internal nexus identifier not available in the browser, so it cannot be used. Instead, use source_record_id from Layer 1, which maps to the source system's business record (e.g. contract number, invoice ID). This value should also be emitted by the browser pixel.

Layer 1: source_record_id       -->  Layer 2: event_id
Layer 1: nexus_event_id         -->  Layer 2: nexus_event_id (QA only)

external_id

Facebook uses external_id for two things:

1. Browser-to-server matching -- linking a pixel event to a CAPI event for the same session.
2. Cross-event user stitching -- linking multiple server-side conversions to the same user, improving attribution, audience building, and optimization.

Because of (2), a consistent non-null value is always better than null, even if the browser can't reproduce it. Layer 1 computes user_id with a fallback chain: real user ID > email hash > entity_id. Layer 2 strips the prefix:

facebook_external_id as external_id

When the value is an email hash or real user ID, the front end must apply the same normalization and hashing so both sides match. When the fallback is entity_id, browser matching won't work on this field, but cross-event stitching still benefits.

See the user_id section in the index for the full fallback chain.

fbc Formatting

If your Layer 1 model does not already compute fbc, add it in Layer 1's marketing_conversions_mapped CTE:

CASE
    WHEN fbclid IS NOT NULL AND fbclid_occurred_at IS NOT NULL THEN
        'fb.1.' ||
        CAST(EXTRACT(EPOCH FROM fbclid_occurred_at) * 1000 AS BIGINT)::VARCHAR ||
        '.' ||
        fbclid
    ELSE NULL
END as fbc

The format is fb.1.<click_timestamp_ms>.<fbclid>. See Facebook docs.

fbp Placeholder

fbp (Facebook browser ID) is a client-side cookie value. It is not available in server-side data. Include it as CAST(NULL AS VARCHAR) to keep the table contract stable. If your delivery tool or pixel can populate it, it improves match quality.

Send-Log Exclusion

The already_sent CTE prevents re-sending events. This requires a send-log model (e.g. nexus_facebook_capi_send_log) that records facebook_event_sync_id and status for each delivered event.

If you don't have a send log yet, omit the join. The model will output all eligible events on every run (idempotent delivery depends on your reverse ETL tool's dedup behavior).

Schema Tests

models:
  - name: marketing_conversions_facebook
    columns:
      - name: event_id
        description: >
          Source record ID used as Facebook CAPI deduplication event_id. Sourced
          from marketing_conversions_canonical.facebook_event_id.
        tests:
          - not_null
      - name: event_name
        description: Facebook standard event name (e.g. Purchase, Lead).
        tests:
          - not_null
      - name: event_time
        description: Unix timestamp in seconds.
        tests:
          - not_null
      - name: nexus_event_id
        description: Internal nexus event ID for reconciliation.
        tests:
          - not_null

Reference

• Facebook CAPI Parameters
• Customer Information Parameters
• Server Event Parameters
• fbp and fbc Parameters