Product List Conversion Rate

{
  "digitalData": {
    "event": [
      {
        "eventInfo": {
          "eventName": "view_item_list",
          "attributes": {
            "traffic_type": "external"
          }
        },
        "listInfo": {
          "listID": "cat_running-shoes",
          "listName": "Running Shoes Category"
        }
      },
      {
        "eventInfo": {
          "eventName": "select_item",
          "attributes": {
            "traffic_type": "external"
          }
        },
        "product": [
          {
            "productInfo": {
              "productID": "SKU-4821",
              "productName": "Trail Running Shoe"
            },
            "listInfo": {
              "listID": "cat_running-shoes",
              "listName": "Running Shoes Category"
            }
          }
        ]
      },
      {
        "eventInfo": {
          "eventName": "add_to_cart",
          "attributes": {
            "traffic_type": "external"
          }
        },
        "product": [
          {
            "productInfo": {
              "productID": "SKU-4821",
              "productName": "Trail Running Shoe"
            },
            "listInfo": {
              "listID": "cat_running-shoes",
              "listName": "Running Shoes Category"
            },
            "quantity": 1,
            "price": 149.98
          }
        ]
      },
      {
        "eventInfo": {
          "eventName": "purchase",
          "attributes": {
            "traffic_type": "external"
          }
        }
      }
    ],
    "transaction": {
      "transactionID": "T-10293",
      "total": {
        "transactionTotal": 149.98,
        "currency": "USD"
      },
      "item": [
        {
          "productInfo": {
            "productID": "SKU-4821",
            "productName": "Trail Running Shoe"
          },
          "listInfo": {
            "listID": "cat_running-shoes",
            "listName": "Running Shoes Category"
          },
          "quantity": 1,
          "price": 149.98
        }
      ]
    }
  }
}

{
  "view_item_list": {
    "event": "view_item_list",
    "traffic_type": "external",
    "page_type": "category",
    "ecommerce": {
      "item_list_id": "cat_running-shoes",
      "item_list_name": "Running Shoes Category",
      "items": [
        {
          "item_id": "SKU-4821",
          "item_name": "Trail Running Shoe",
          "item_list_id": "cat_running-shoes",
          "item_list_name": "Running Shoes Category"
        }
      ]
    }
  },
  "select_item": {
    "event": "select_item",
    "traffic_type": "external",
    "page_type": "category",
    "ecommerce": {
      "item_list_id": "cat_running-shoes",
      "item_list_name": "Running Shoes Category",
      "items": [
        {
          "item_id": "SKU-4821",
          "item_name": "Trail Running Shoe",
          "item_list_id": "cat_running-shoes",
          "item_list_name": "Running Shoes Category"
        }
      ]
    }
  },
  "add_to_cart": {
    "event": "add_to_cart",
    "traffic_type": "external",
    "page_type": "product_detail",
    "ecommerce": {
      "cart_id": "cart_9981",
      "add_method": "manual",
      "add_source": "list_select",
      "items": [
        {
          "item_id": "SKU-4821",
          "item_name": "Trail Running Shoe",
          "item_list_id": "cat_running-shoes",
          "item_list_name": "Running Shoes Category",
          "price": 149.98,
          "quantity": 1
        }
      ]
    }
  },
  "purchase": {
    "event": "purchase",
    "traffic_type": "external",
    "ecommerce": {
      "transaction_id": "T-10293",
      "value": 149.98,
      "currency": "USD",
      "items": [
        {
          "item_id": "SKU-4821",
          "item_name": "Trail Running Shoe",
          "item_list_id": "cat_running-shoes",
          "item_list_name": "Running Shoes Category",
          "price": 149.98,
          "quantity": 1
        }
      ]
    }
  }
}

{
  "view_item_list": {
    "event": "view_item_list",
    "traffic_type": "external",
    "productListItems": [
      {
        "SKU": "SKU-4821",
        "name": "Trail Running Shoe",
        "listID": "cat_running-shoes",
        "listName": "Running Shoes Category"
      }
    ]
  },
  "select_item": {
    "event": "select_item",
    "traffic_type": "external",
    "productListItems": [
      {
        "SKU": "SKU-4821",
        "name": "Trail Running Shoe",
        "listID": "cat_running-shoes",
        "listName": "Running Shoes Category"
      }
    ]
  },
  "add_to_cart": {
    "event": "scAdd",
    "traffic_type": "external",
    "commerce": {
      "productListAdds": {
        "value": 1
      }
    },
    "productListItems": [
      {
        "SKU": "SKU-4821",
        "name": "Trail Running Shoe",
        "quantity": 1,
        "priceTotal": 149.98,
        "listID": "cat_running-shoes",
        "listName": "Running Shoes Category"
      }
    ]
  },
  "purchase": {
    "event": "purchase",
    "traffic_type": "external",
    "commerce": {
      "purchases": {
        "value": 1
      },
      "order": {
        "purchaseID": "T-10293",
        "priceTotal": 149.98,
        "currencyCode": "USD"
      }
    },
    "productListItems": [
      {
        "SKU": "SKU-4821",
        "name": "Trail Running Shoe",
        "quantity": 1,
        "priceTotal": 149.98,
        "listID": "cat_running-shoes",
        "listName": "Running Shoes Category"
      }
    ]
  }
}

{
  "view_item_list": {
    "eventType": "view_item_list",
    "_tenant": {
      "traffic_type": "external"
    },
    "productListItems": [
      {
        "SKU": "SKU-4821",
        "name": "Trail Running Shoe",
        "listID": "cat_running-shoes",
        "listName": "Running Shoes Category"
      }
    ],
    "_experience": {
      "analytics": {
        "customDimensions": {
          "eVars": {
            "eVar1": "item_list_id",
            "eVar2": "item_list_name"
          }
        }
      }
    }
  },
  "select_item": {
    "eventType": "select_item",
    "_tenant": {
      "traffic_type": "external"
    },
    "productListItems": [
      {
        "SKU": "SKU-4821",
        "name": "Trail Running Shoe",
        "listID": "cat_running-shoes",
        "listName": "Running Shoes Category"
      }
    ],
    "_experience": {
      "analytics": {
        "customDimensions": {
          "eVars": {
            "eVar1": "item_list_id",
            "eVar2": "item_list_name"
          }
        }
      }
    }
  },
  "commerce.productListAdds": {
    "eventType": "commerce.productListAdds",
    "_tenant": {
      "traffic_type": "external"
    },
    "commerce": {
      "productListAdds": {
        "value": 1
      }
    },
    "productListItems": [
      {
        "SKU": "SKU-4821",
        "name": "Trail Running Shoe",
        "quantity": 1,
        "priceTotal": 149.98,
        "listID": "cat_running-shoes",
        "listName": "Running Shoes Category"
      }
    ],
    "_experience": {
      "analytics": {
        "customDimensions": {
          "eVars": {
            "eVar1": "item_list_id",
            "eVar2": "item_list_name"
          }
        }
      }
    }
  },
  "commerce.purchases": {
    "eventType": "commerce.purchases",
    "_tenant": {
      "traffic_type": "external"
    },
    "commerce": {
      "purchases": {
        "value": 1
      },
      "order": {
        "purchaseID": "T-10293",
        "priceTotal": 149.98
      }
    },
    "productListItems": [
      {
        "SKU": "SKU-4821",
        "name": "Trail Running Shoe",
        "quantity": 1,
        "priceTotal": 149.98,
        "listID": "cat_running-shoes",
        "listName": "Running Shoes Category"
      }
    ],
    "_experience": {
      "analytics": {
        "customDimensions": {
          "eVars": {
            "eVar1": "item_list_id",
            "eVar2": "item_list_name"
          }
        }
      }
    }
  }
}

-- ==========================================
-- 1. GOOGLE BIGQUERY SQL
-- ==========================================
-- Cancelled orders are not recorded in the GA4 export and cannot be excluded here; test orders are removed through traffic_type = 'test'.
DECLARE start_date DATE DEFAULT DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY);
DECLARE end_date DATE DEFAULT DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY);

WITH events AS (
  -- Funnel events in the period with session key and list id; internal, bot and test traffic removed
  SELECT
    user_pseudo_id,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session_id,
    event_name,
    event_timestamp,
    COALESCE((SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'item_list_id'), items[SAFE_OFFSET(0)].item_list_id) AS item_list_id,
    ecommerce.transaction_id,
    items
  FROM `your-project.analytics_123456789.events_*`
  WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', start_date) AND FORMAT_DATE('%Y%m%d', end_date)
    AND event_name IN ('page_view', 'view_item_list', 'select_item', 'add_to_cart', 'purchase')
    AND COALESCE((SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'traffic_type'), 'external') NOT IN ('internal', 'bot', 'test')
),

page_loads AS (
  -- Key every list view to the latest page_view before it in the same session (one page load)
  SELECT
    user_pseudo_id,
    ga_session_id,
    event_name,
    item_list_id,
    LAST_VALUE(IF(event_name = 'page_view', event_timestamp, NULL) IGNORE NULLS) OVER (
      PARTITION BY user_pseudo_id, ga_session_id
      ORDER BY event_timestamp, IF(event_name = 'page_view', 0, 1)
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS page_load_ts
  FROM events
  WHERE event_name IN ('page_view', 'view_item_list')
),

list_views AS (
  -- Denominator: one view per list per page load, so re-renders and lazy-loaded rows count once
  SELECT
    item_list_id,
    COUNT(DISTINCT FORMAT('%s|%t|%t', user_pseudo_id, ga_session_id, page_load_ts)) AS list_views
  FROM page_loads
  WHERE event_name = 'view_item_list'
    AND item_list_id IS NOT NULL
  GROUP BY item_list_id
),

event_items AS (
  -- One row per item on select_item, add_to_cart and purchase
  SELECT
    e.user_pseudo_id,
    e.ga_session_id,
    e.event_name,
    e.event_timestamp,
    e.transaction_id,
    i.item_id,
    COALESCE(i.item_list_id, e.item_list_id) AS item_list_id
  FROM events AS e, UNNEST(e.items) AS i
  WHERE e.event_name IN ('select_item', 'add_to_cart', 'purchase')
),

cart_adds AS (
  -- Last list wins: each added item takes the most recent select_item of that item earlier in the same session; adds outside a list keep no list
  SELECT
    a.user_pseudo_id,
    a.item_id,
    a.event_timestamp AS cart_ts,
    s.item_list_id
  FROM event_items AS a
  LEFT JOIN event_items AS s
    ON s.event_name = 'select_item'
   AND s.user_pseudo_id = a.user_pseudo_id
   AND s.ga_session_id = a.ga_session_id
   AND s.item_id = a.item_id
   AND s.event_timestamp <= a.event_timestamp
  WHERE a.event_name = 'add_to_cart'
  QUALIFY ROW_NUMBER() OVER (PARTITION BY a.user_pseudo_id, a.item_id, a.event_timestamp ORDER BY s.event_timestamp DESC) = 1
),

purchased_items AS (
  -- Each purchased item carries the list of its latest add_to_cart before the purchase
  SELECT
    p.transaction_id,
    c.item_list_id
  FROM event_items AS p
  JOIN cart_adds AS c
    ON c.user_pseudo_id = p.user_pseudo_id
   AND c.item_id = p.item_id
   AND c.cart_ts <= p.event_timestamp
  WHERE p.event_name = 'purchase'
    AND p.transaction_id IS NOT NULL
  QUALIFY ROW_NUMBER() OVER (PARTITION BY p.transaction_id, p.item_id ORDER BY c.cart_ts DESC) = 1
),

list_orders AS (
  -- Numerator: an order counts once per list; items without a list stay out
  SELECT
    item_list_id,
    COUNT(DISTINCT transaction_id) AS list_orders
  FROM purchased_items
  WHERE item_list_id IS NOT NULL
  GROUP BY item_list_id
)

-- Product List Conversion Rate per item_list_id
SELECT
  v.item_list_id,
  v.list_views,
  COALESCE(o.list_orders, 0) AS list_conversions,
  ROUND(100 * SAFE_DIVIDE(COALESCE(o.list_orders, 0), v.list_views), 2) AS product_list_conversion_rate_pct
FROM list_views AS v
LEFT JOIN list_orders AS o USING (item_list_id)
ORDER BY v.list_views DESC;

-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
-- Cancelled orders are not recorded in the web event dataset and cannot be excluded here; test orders are removed through traffic_type = 'test'.
WITH params AS (
  -- Reporting window: the last 90 complete days
  SELECT DATE_SUB(CURRENT_DATE, 90) AS start_date, DATE_SUB(CURRENT_DATE, 1) AS end_date
),

events AS (
  -- Funnel events in the period with a 30-minute session key and list id; internal, bot and test traffic removed
  SELECT
    e.identityMap['ECID'][0].id AS ecid,
    SESS_TIMEOUT(e.timestamp, 60 * 30) OVER (
      PARTITION BY e.identityMap['ECID'][0].id
      ORDER BY e.timestamp
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW).num AS session_num,
    e.eventType,
    e.timestamp,
    e._experience.analytics.customDimensions.eVars.eVar1 AS item_list_id,
    e.commerce.order.purchaseID AS transaction_id,
    e.productListItems
  FROM aep_web_events_dataset AS e
  CROSS JOIN params AS p
  WHERE CAST(e.timestamp AS DATE) BETWEEN p.start_date AND p.end_date
    AND e.eventType IN ('web.webpagedetails.pageViews', 'view_item_list', 'select_item', 'commerce.productListAdds', 'commerce.purchases')
    AND COALESCE(e._tenant.traffic_type, 'external') NOT IN ('internal', 'bot', 'test')
),

page_loads AS (
  -- Key every list view to the latest page view before it in the same session (one page load)
  SELECT
    ecid,
    session_num,
    eventType,
    item_list_id,
    LAST(IF(eventType = 'web.webpagedetails.pageViews', timestamp, NULL), true) OVER (
      PARTITION BY ecid, session_num
      ORDER BY timestamp, IF(eventType = 'web.webpagedetails.pageViews', 0, 1)
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS page_load_ts
  FROM events
  WHERE eventType IN ('web.webpagedetails.pageViews', 'view_item_list')
),

list_views AS (
  -- Denominator: one view per list per page load, so re-renders and lazy-loaded rows count once
  SELECT
    item_list_id,
    COUNT(DISTINCT CONCAT_WS('|', ecid, CAST(session_num AS STRING), COALESCE(CAST(page_load_ts AS STRING), 'none'))) AS list_views
  FROM page_loads
  WHERE eventType = 'view_item_list'
    AND item_list_id IS NOT NULL
  GROUP BY item_list_id
),

event_items AS (
  -- One row per item on select_item, add to cart and purchase
  SELECT
    ecid,
    session_num,
    eventType,
    timestamp,
    transaction_id,
    i.SKU AS item_id,
    item_list_id
  FROM events
  LATERAL VIEW explode(productListItems) t AS i
  WHERE eventType IN ('select_item', 'commerce.productListAdds', 'commerce.purchases')
),

cart_adds AS (
  -- Last list wins: each added item takes the most recent select_item of that item earlier in the same session; adds outside a list keep no list
  SELECT ecid, item_id, cart_ts, item_list_id
  FROM (
    SELECT
      a.ecid,
      a.item_id,
      a.timestamp AS cart_ts,
      s.item_list_id,
      ROW_NUMBER() OVER (PARTITION BY a.ecid, a.item_id, a.timestamp ORDER BY s.timestamp DESC NULLS LAST) AS rn
    FROM event_items AS a
    LEFT JOIN event_items AS s
      ON s.eventType = 'select_item'
     AND s.ecid = a.ecid
     AND s.session_num = a.session_num
     AND s.item_id = a.item_id
     AND s.timestamp <= a.timestamp
    WHERE a.eventType = 'commerce.productListAdds'
  ) ranked
  WHERE rn = 1
),

purchased_items AS (
  -- Each purchased item carries the list of its latest add to cart before the purchase
  SELECT transaction_id, item_list_id
  FROM (
    SELECT
      p.transaction_id,
      c.item_list_id,
      ROW_NUMBER() OVER (PARTITION BY p.transaction_id, p.item_id ORDER BY c.cart_ts DESC) AS rn
    FROM event_items AS p
    JOIN cart_adds AS c
      ON c.ecid = p.ecid
     AND c.item_id = p.item_id
     AND c.cart_ts <= p.timestamp
    WHERE p.eventType = 'commerce.purchases'
      AND p.transaction_id IS NOT NULL
  ) ranked
  WHERE rn = 1
),

list_orders AS (
  -- Numerator: an order counts once per list; items without a list stay out
  SELECT
    item_list_id,
    COUNT(DISTINCT transaction_id) AS list_orders
  FROM purchased_items
  WHERE item_list_id IS NOT NULL
  GROUP BY item_list_id
)

-- Product List Conversion Rate per item_list_id
SELECT
  v.item_list_id,
  v.list_views,
  COALESCE(o.list_orders, 0) AS list_conversions,
  ROUND(100 * COALESCE(o.list_orders, 0) / NULLIF(v.list_views, 0), 2) AS product_list_conversion_rate_pct
FROM list_views AS v
LEFT JOIN list_orders AS o ON o.item_list_id = v.item_list_id
ORDER BY v.list_views DESC;