Conversion Rate

window.digitalData.event.push({
  eventInfo: {
    eventName: "conversion_complete",
    eventAction: "conversion",
    type: "conversion",
    timeStamp: new Date().toISOString()
  },
  attributes: {
    conversionGoal: "{{conversion_goal}}",   // purchase, signup, lead, add_to_cart, etc.
    conversionId: "{{conversion_id}}",
    conversionValue: 0,
    currency: "USD",
    conversionStatus: "complete",
    entityScope: "session"                  // session, user, account, visit, lead
  }
});

dataLayer.push({
  event: "{{conversion_event_name}}",
  conversion: {
    conversion_goal: "{{conversion_goal}}", // purchase, signup, lead, add_to_cart, etc.
    conversion_id: "{{conversion_id}}",
    conversion_value: 0,
    currency: "USD"
  }
});

adobeDataLayer.push({
  event: "conversion.complete",
  conversion: {
    goal: "{{conversion_goal}}", // purchase, signup, lead, add_to_cart, etc.
    id: "{{conversion_id}}",
    value: 0,
    currency: "USD"
  }
});

xdm: {
  eventType: "conversion.complete",
  _tenant: {
    conversion: {
      goal: "{{conversion_goal}}", // purchase, signup, lead, add_to_cart, etc.
      id: "{{conversion_id}}",
      value: 0
    }
  }
}

DECLARE conversion_event_names ARRAY<STRING> DEFAULT ['purchase'];
DECLARE eligibility_event_names ARRAY<STRING> DEFAULT ['session_start'];

WITH event_rows AS (
  SELECT
    user_pseudo_id,
    user_id,
    CAST((
      SELECT value.int_value
      FROM UNNEST(event_params)
      WHERE key = 'ga_session_id'
    ) AS STRING) AS ga_session_id,
    event_name
  FROM `project.dataset.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20260101' AND '20260131'
),

session_flags AS (
  SELECT
    user_pseudo_id,
    user_id,
    ga_session_id,
    MAX(CASE WHEN event_name IN UNNEST(eligibility_event_names) THEN 1 ELSE 0 END) AS is_eligible,
    MAX(CASE WHEN event_name IN UNNEST(conversion_event_names) THEN 1 ELSE 0 END) AS is_converted
  FROM event_rows
  WHERE ga_session_id IS NOT NULL
  GROUP BY user_pseudo_id, user_id, ga_session_id
)

SELECT
  SAFE_DIVIDE(
    COUNTIF(is_eligible = 1 AND is_converted = 1),
    COUNTIF(is_eligible = 1)
  ) * 100 AS conversion_rate
FROM session_flags;

WITH event_rows AS (
  SELECT
    COALESCE(
      identityMap['ECID'][0].id,
      identityMap['CRMID'][0].id
    ) AS entity_id,
    eventType,
    _tenant.conversion.goal AS conversion_goal,
    timestamp
  FROM aep_experience_event_dataset
  WHERE timestamp >= TIMESTAMP '2026-01-01 00:00:00'
    AND timestamp <  TIMESTAMP '2026-02-01 00:00:00'
),

entity_flags AS (
  SELECT
    entity_id,

    MAX(CASE
      WHEN eventType = 'web.webpagedetails.pageViews'
      THEN 1 ELSE 0
    END) AS is_eligible,

    MAX(CASE
      WHEN eventType = 'commerce.purchases'
        OR (
          eventType = 'conversion.complete'
          AND conversion_goal IN ('purchase', 'signup', 'lead')
        )
      THEN 1 ELSE 0
    END) AS is_converted

  FROM event_rows
  WHERE entity_id IS NOT NULL
  GROUP BY entity_id
)

SELECT
  SUM(CASE WHEN is_eligible = 1 AND is_converted = 1 THEN 1 ELSE 0 END) * 100.0
  / NULLIF(SUM(CASE WHEN is_eligible = 1 THEN 1 ELSE 0 END), 0) AS conversion_rate
FROM entity_flags;