About one abandoned cart in eight comes back through the recovery email.

Recovery email conversion

WITH checkout_abandoned AS (
  -- Carts with no order within two hours: they left the checkout.
  SELECT c.id AS cart_id, c.customer_id, c.created_at
  FROM carts c
  LEFT JOIN orders o ON o.cart_id = c.id
                    AND datetime(o.ordered_at) <= datetime(c.created_at, '+2 hours')
  WHERE o.id IS NULL
    AND datetime(c.created_at) < datetime((SELECT MAX(created_at) FROM carts), '-2 days')
),
email_views AS MATERIALIZED (
  -- Only the email-referred views matter. Materializing the filtered
  -- set lets SQLite build one index for the correlated lookup.
  SELECT customer_id, datetime(at) AS at FROM pageviews WHERE referrer = 'email'
),
recovered AS (
  SELECT a.cart_id
  FROM checkout_abandoned a
  JOIN orders o ON o.cart_id = a.cart_id
  WHERE EXISTS (SELECT 1 FROM email_views p
                WHERE p.customer_id = a.customer_id
                  AND p.at BETWEEN datetime(a.created_at) AND datetime(o.ordered_at))
)
SELECT (SELECT COUNT(*) FROM checkout_abandoned)                                  AS abandoned_at_checkout,
       (SELECT COUNT(*) FROM recovered)                                           AS recovered,
       ROUND(100.0 * (SELECT COUNT(*) FROM recovered)
                   / (SELECT COUNT(*) FROM checkout_abandoned), 1)                AS recovered_pct;

holds on the current window