How customers reach our website is important (and important for every business). What is more important and interesting is what they purchase depending on how they get to our site.

I answer lots of questions like this, so I will keep it as focused as possible on each post. This question was posed to me because we recently changed our Paid Search strategy by Product Type funnel.

In our data, we luckily track the first and last attributions of many different variables of how users reach our website in our GA4 data. So I am able to see if a customer gets to our site via a specific campaign group, and if they purchase under the same campaign group or under a different product type.

All data and variables are aliased:

WITH paid_session_base AS (
  SELECT
      e.event_date                                   AS event_date,             
      e.session_key,
      e.user_pseudo_id,
      ANY_VALUE(e.device_type)                       AS device_type,          
      ANY_VALUE(e.traffic_medium)                    AS traffic_medium,       

      /* Keep both potential transaction/order ids as strings */
      MAX(TO_VARCHAR(e.order_id_primary))            AS primary_order_id_str,  
      MAX(NULLIF(TRIM(e.order_id_alt), ''))          AS alt_order_id_str,    

      /* Unified id across sources */
      COALESCE(
        MAX(NULLIF(TRIM(e.order_id_alt), '')),
        MAX(TO_VARCHAR(e.order_id_primary))
      ) AS unified_order_ref_str

  FROM ANALYTICS.WEB.EVENTS_FLAT e
  WHERE e.property_id = :p_property_id
    AND e.traffic_medium = :p_medium                -- e.g., 'paid'
    AND e.event_date > TO_DATE(:p_start_date)

    /* Toggle this block off if you want ALL sessions (orders + non-orders) */
    AND (
          :p_include_only_sessions_with_orders = 'N'
          OR NULLIF(TRIM(e.order_id_alt), '') IS NOT NULL
          OR e.order_id_primary IS NOT NULL
        )

  GROUP BY e.session_key, e.user_pseudo_id, e.event_date
),

/* Session-level attribution rollup (first/last touch, campaign ids, etc.) */
attribution_by_session AS (
  SELECT
    s.session_key,
    ANY_VALUE(s.first_channel)               AS first_channel,
    ANY_VALUE(s.last_channel)                AS last_channel,
    ANY_VALUE(s.first_campaign_code)         AS first_campaign_code,
    ANY_VALUE(s.last_campaign_code)          AS last_campaign_code,
    ANY_VALUE(s.first_external_campaign_id)  AS first_external_campaign_id,
    ANY_VALUE(s.last_external_campaign_id)   AS last_external_campaign_id
  FROM ANALYTICS.WEB.SESSION_ATTRIBUTION s
  WHERE s.session_key IS NOT NULL
  GROUP BY s.session_key
),

/* Latest campaign names by external id (handles changing names over time) */
latest_campaign AS (
  SELECT
    CAST(c.external_campaign_id AS STRING) AS external_campaign_id_str,
    c.campaign_name
  FROM MARKETING.ADS.CAMPAIGN_SNAPSHOT c
  QUALIFY ROW_NUMBER() OVER (
    PARTITION BY c.external_campaign_id
    ORDER BY c.snapshot_date DESC
  ) = 1
),

/* Order rollups from Source A (e.g., internal commerce system) */
source_a_order_stats AS (
  SELECT
      o.order_id::NUMBER               AS order_id,
      o.order_ref::STRING              AS order_ref,
      CAST(o.order_date AS DATE)       AS order_date,
      ARRAY_AGG(DISTINCT i.product_type) AS product_types,
      'SOURCE_A'                       AS order_channel
  FROM COMMERCE.CORE.ORDERS o
  JOIN COMMERCE.CORE.ORDER_ITEMS i
    ON i.order_id = o.order_id
  GROUP BY o.order_id, o.order_ref, CAST(o.order_date AS DATE)
),

/* Order rollups from Source B (e.g., downstream export / external processor) */
source_b_order_stats AS (
  SELECT
      e.order_id::NUMBER               AS order_id,
      e.order_ref::STRING              AS order_ref,
      CAST(e.order_date AS DATE)       AS order_date,
      ARRAY_CONSTRUCT('CATEGORY_A')    AS product_types,  
      'SOURCE_B'                       AS order_channel
  FROM COMMERCE.EXPORTS.ORDER_FEED e
),

/* Union both order sources */
combined_order_stats AS (
  SELECT * FROM source_a_order_stats
  UNION ALL
  SELECT * FROM source_b_order_stats
),

/* Base join of paid sessions + attribution + campaign names + orders */
base AS (
  SELECT
    b.event_date,
    b.session_key,
    b.user_pseudo_id,
    b.device_type,
    b.traffic_medium,
    b.unified_order_ref_str,

    a.first_channel,
    a.first_campaign_code,
    a.last_channel,
    a.last_campaign_code,
    a.first_external_campaign_id,
    a.last_external_campaign_id,

    lc1.campaign_name AS first_campaign_name,
    lc2.campaign_name AS last_campaign_name,
    oref.order_ref,
    oref.product_types,

   
    CASE
      WHEN COALESCE(lc1.campaign_name, a.first_campaign_code) ILIKE '%CATEGORY_B%'
      THEN 1 ELSE 0
    END AS is_first_campaign_category_b,

    CASE
      WHEN ARRAY_CONTAINS(TO_VARIANT('CATEGORY_A'), COALESCE(oref.product_types, ARRAY_CONSTRUCT()))
      THEN 1 ELSE 0
    END AS has_category_a_in_order,

    CASE
      WHEN COALESCE(lc1.campaign_name, a.first_campaign_code) ILIKE '%CATEGORY_B%'
       AND ARRAY_CONTAINS(TO_VARIANT('CATEGORY_A'), COALESCE(oref.product_types, ARRAY_CONSTRUCT()))
      THEN 1 ELSE 0
    END AS is_first_campaign_category_b_and_category_a_order

  FROM paid_session_base b
  LEFT JOIN attribution_by_session a
    ON a.session_key = b.session_key
  LEFT JOIN latest_campaign lc1
    ON TRIM(CAST(a.first_external_campaign_id AS STRING)) = lc1.external_campaign_id_str
  LEFT JOIN latest_campaign lc2
    ON TRIM(CAST(a.last_external_campaign_id  AS STRING)) = lc2.external_campaign_id_str
  LEFT JOIN combined_order_stats oref
    ON b.unified_order_ref_str = oref.order_ref
)

/* Aggregated session/user/order flags */
SELECT
  COUNT(DISTINCT session_key)                          AS total_paid_sessions,
  COUNT(DISTINCT user_pseudo_id)                       AS total_users,
  SUM(is_first_campaign_category_b)                    AS total_first_campaign_cat_b,
  SUM(has_category_a_in_order)                         AS total_orders_with_cat_a,
  SUM(is_first_campaign_category_b_and_category_a_order) AS total_first_cat_b_and_order_cat_a
FROM base;

I was able to return to management that X% of users came through a hotel paid search campaign but purchased a show.