Facebook Conversions as a destination

Your table in Nekt  →  Nekt destination  →  Pixel / Dataset  →  attribution and campaign optimization

{
  "data": [
    {
      "event_name": "Purchase",
      "event_time": 1756742400,
      "action_source": "physical_store",
      "user_data": {
        "em": "5504a61f3ca7140339278c6dabcc059695c852b2aba44c4d00b7ca8c5716106a"
      },
      "custom_data": {
        "value": 1250.0,
        "order_id": "8842"
      }
    }
  ]
}

SELECT
    'Purchase'                                                   AS event_name,
    unix_timestamp(o.paid_at)                                    AS event_time,
    'physical_store'                                             AS action_source,
    concat('erp-order-', o.order_id)                             AS event_id,

    -- Identifiers. Normalized here, hashed by Nekt before sending.
    lower(trim(c.email))                                         AS em,
    CASE
        WHEN length(regexp_replace(c.phone, '[^0-9]', '')) BETWEEN 10 AND 11
            THEN concat('55', regexp_replace(c.phone, '[^0-9]', ''))
        ELSE regexp_replace(c.phone, '[^0-9]', '')
    END                                                          AS ph,
    lower(trim(c.first_name))                                    AS fn,
    lower(trim(c.last_name))                                     AS ln,
    regexp_replace(lower(trim(c.city)), '[^\\p{L}]', '')         AS ct,
    lower(c.state)                                               AS st,
    lower(regexp_replace(c.zip_code, '[^0-9a-zA-Z]', ''))        AS zp,
    'br'                                                         AS country,
    cast(c.customer_id AS string)                                AS external_id,

    -- Event value
    o.total_amount                                               AS value,
    'BRL'                                                        AS currency,
    cast(o.order_id AS string)                                   AS order_id
FROM silver.erp_orders o
JOIN silver.erp_customers c ON c.customer_id = o.customer_id
WHERE o.status = 'paid'
  AND o.paid_at >= current_timestamp() - INTERVAL 6 DAYS

SELECT
    'QualifiedLead'                              AS event_name,
    unix_timestamp(d.qualified_at)               AS event_time,
    'system_generated'                           AS action_source,
    concat('crm-deal-', d.deal_id)               AS event_id,

    cast(l.lead_id AS string)                    AS lead_id,
    lower(trim(l.email))                         AS em,

    d.expected_revenue                           AS value,
    'BRL'                                        AS currency
FROM silver.crm_deals d
JOIN silver.facebook_leads l ON l.id = d.facebook_lead_id
WHERE d.stage = 'qualified'
  AND d.qualified_at >= current_timestamp() - INTERVAL 6 DAYS

SELECT
    'Purchase'                       AS event_name,
    unix_timestamp(o.created_at)     AS event_time,
    'website'                        AS action_source,
    o.checkout_id                    AS event_id,          -- same value the Pixel sent as eventID
    o.landing_url                    AS event_source_url,

    lower(trim(o.customer_email))    AS em,
    o.fbc_cookie                     AS fbc,               -- captured from _fbc, not hashed
    o.fbp_cookie                     AS fbp,               -- captured from _fbp, not hashed
    o.client_ip                      AS client_ip_address,
    o.user_agent                     AS client_user_agent,

    o.total_amount                   AS value,
    'BRL'                            AS currency
FROM silver.ecommerce_orders o
WHERE o.created_at >= current_timestamp() - INTERVAL 6 DAYS

SELECT
    o.order_id,
    collect_list(named_struct(
        'id',         cast(i.sku AS string),
        'quantity',   i.quantity,
        'item_price', i.unit_price
    ))                          AS contents,
    sum(i.quantity)             AS num_items
FROM silver.erp_order_items i
JOIN silver.erp_orders o ON o.order_id = i.order_id
GROUP BY o.order_id