The formulas

Randomize and count at the same level. If the tool assigns visitors, analyze visitors, not sessions or page views. A visitor who comes back five times is one visitor, and counting their sessions separately makes the result look more certain than it is.

The funnel by device

-- sessions reaching each step, last 28 days, by device
-- events: one row per event, with session_id, device, name, ts
SELECT device,
       COUNT(DISTINCT session_id)                                         AS sessions,
       COUNT(DISTINCT session_id) FILTER (WHERE name = 'view_item')       AS viewed_product,
       COUNT(DISTINCT session_id) FILTER (WHERE name = 'add_to_cart')     AS added_to_cart,
       COUNT(DISTINCT session_id) FILTER (WHERE name = 'begin_checkout')  AS began_checkout,
       COUNT(DISTINCT session_id) FILTER (WHERE name = 'add_shipping_info') AS entered_shipping,
       COUNT(DISTINCT session_id) FILTER (WHERE name = 'add_payment_info') AS entered_payment,
       COUNT(DISTINCT session_id) FILTER (WHERE name = 'purchase')        AS purchased
FROM events
WHERE ts >= CURRENT_DATE - INTERVAL '28 days'
GROUP BY device;

The event names follow Google Analytics 4’s recommended ecommerce events; rename them to match your setup. The syntax is Postgres; in BigQuery, write COUNT(DISTINCT IF(name = 'add_to_cart', session_id, NULL)). Divide each column by the one before to get each step’s pass rate, and compare phones with desktop step by step. Express wallets can skip the shipping and payment events, so a step above 100% means an event is missing, not a miracle.

The split check

-- visitors assigned to each version, counted once each
SELECT variant, COUNT(DISTINCT visitor_id) AS visitors
FROM exposures
WHERE experiment_id = 'pdp-delivery-date'
GROUP BY variant;

Put the two counts into the reader in chapter 5. Run the check daily in the first days of any test, not only at the end: a broken split caught on day two costs two days.

Page traffic for the traffic table

-- visitors, orders and conversion for visitors who saw a page type
SELECT page_type,
       COUNT(DISTINCT s.visitor_id) AS visitors,
       COUNT(DISTINCT o.order_id)   AS orders,
       COUNT(DISTINCT o.order_id)::numeric
         / COUNT(DISTINCT s.visitor_id) AS orders_per_visitor
FROM page_views s
LEFT JOIN orders o
  ON o.visitor_id = s.visitor_id
 AND o.ordered_at BETWEEN s.viewed_at AND s.viewed_at + INTERVAL '1 day'
WHERE s.viewed_at >= CURRENT_DATE - INTERVAL '28 days'
GROUP BY page_type
ORDER BY visitors DESC;

This counts an order toward a page if it came within a day of the visit. That’s an approximation, and a generous one, but it’s the right shape for deciding which pages can carry a test.