Nexus Data

marketing_conversions_google_ads is a Layer 2 destination model that projects marketing_conversions_canonical into the Google Ads Enhanced Conversions format. Like all Layer 2 models, it should contain minimal logic.

Column names strip the google_ads_ prefix from Layer 1 so the output resembles the actual upload payload (e.g. google_ads_order_id becomes order_id, google_ads_conversion_action_id becomes conversion_action_id).

Template

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

WITH google_ads_projection AS (
    SELECT
        -- Conversion identifiers
        conversion_sync_id,
        nexus_event_id,
        google_ads_order_id as order_id,
        google_ads_conversion_action_id as conversion_action_id,
        occurred_at as conversion_date_time,

        -- Value
        value as conversion_value,
        currency as currency_code,

        -- Click attribution
        gclid,
        gclid_occurred_at,
        gbraid,
        wbraid,

        -- Enhanced conversion identity fields (hashed by reverse ETL at send time)
        entity_id,
        email,
        phone,
        first_name,
        last_name,
        zip_code,
        country
    FROM {{ ref('marketing_conversions_canonical') }}
    WHERE occurred_at >= DATEADD('day', -90, CURRENT_TIMESTAMP())
)

SELECT *
FROM google_ads_projection
ORDER BY conversion_date_time DESC

Design Notes

No `event_name`

Unlike Facebook CAPI, Google Ads does not accept an event name string in the upload payload. Conversions are routed entirely by conversion_action_id, which maps to a conversion action configured in the Google Ads account. The nexus event_name is not included in this model.

90-Day Recency Window

Google Ads silently drops conversions uploaded more than 90 days after the associated click. Filter in the projection CTE:

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

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

Click ID Filtering (Optional)

Enhanced Conversions do not require a gclid to upload -- they can match conversions to ad interactions using hashed PII (email, phone, name, address) alone. A gclid improves attribution accuracy when present, but filtering to gclid IS NOT NULL would discard valid conversions that can still be matched via PII.

If your use case only needs click-attributed conversions (e.g. for return-on-ad-spend reporting), you can add an optional filter:

WHERE (gclid IS NOT NULL OR gbraid IS NOT NULL OR wbraid IS NOT NULL)
  AND occurred_at >= DATEADD('day', -90, CURRENT_TIMESTAMP())

For most implementations, omit the click ID filter and let Google match via Enhanced Conversions PII fields.

`order_id` Deduplication

Google Ads uses order_id to deduplicate conversions. This maps to source_record_id from Layer 1 (aliased as google_ads_order_id), which represents the source system's business record identifier (e.g. contract number, invoice ID).

Layer 1: google_ads_order_id  -->  Layer 2: order_id
Layer 1: nexus_event_id       -->  Layer 2: nexus_event_id (QA only)

Conversion Action Routing

conversion_action_id comes from the event_maps dictionary in Layer 1 (via nexus.case_map). Each event type maps to a specific Google Ads conversion action ID configured in the Google Ads account.

`gbraid` / `wbraid`

Google's privacy-safe click identifiers for iOS app and web attribution. These are defined as CAST(NULL AS VARCHAR) placeholders in Layer 1. When your attribution models capture these IDs, wire them through Layer 1 and they will flow through automatically.

No `external_id` / `user_id` Equivalent

Unlike Facebook CAPI, Google Ads Enhanced Conversions does not accept an external_id or user_id field. The UserIdentifier for conversion uploads only accepts hashed_email, hashed_phone_number, and address_info. There is a third_party_user_id field, but it is only accepted for Customer Match and Store Sales -- not for Enhanced Conversions.

Identity matching is done entirely through hashed PII fields. The user_id from Layer 1 is not mapped in this model.

Enhanced Conversion Identity Fields

Google Ads Enhanced Conversions use hashed PII (email, phone, name, address) to match conversions to ad interactions. The reverse ETL tool or delivery adapter handles SHA-256 hashing per Google's normalization requirements.

Include these fields from Layer 1:

email
phone
first_name, last_name
zip_code, country

No Send-Log Exclusion (Default)

Google Ads Enhanced Conversions are typically idempotent by order_id + conversion_action_id. A send-log exclusion join is optional. Add one if your delivery pipeline requires it.

Schema Tests

models:
  - name: marketing_conversions_google_ads
    columns:
      - name: conversion_sync_id
        description: Sync identifier from Layer 1.
        tests:
          - not_null
          - unique
      - name: nexus_event_id
        description: Internal nexus event ID for reconciliation.
        tests:
          - not_null
      - name: order_id
        description: >
          Source record ID used as Google Ads deduplication order_id. Sourced
          from marketing_conversions_canonical.google_ads_order_id.
      - name: conversion_date_time
        tests:
          - not_null
      - name: gclid
        description: Google Click ID. May be null for PII-matched conversions.