FOR YOUR ANALYST

Every pull below runs on an orders table, an order-lines table and a customers table. Resolve identity first: customer ID, then normalized email, then phone. A customer who checked out as a guest and later made an account is one customer, and counting them twice turns a repeat buyer into two one-timers.

The offer’s data contract

Five pulls

1. The door tableFor each product in first orders: count of first orders, share reordering within 180 days, 180-day revenue per customer. Only customers whose first order is at least 180 days old.
2. The one-and-done splitAll customers ever, grouped by order count (1, 2, 3 to 5, 6 or more): customers, revenue, share of each. Check that the counts fall as you require more history.
3. First-offer historyOne row per first offer: new customers, first-order contribution, 90-day reorder rate, 180-day contribution per customer, and orders from existing customers who used it.
4. The reorder curveFor customers who reordered within 365 days, the cumulative share who had done it by day 15, 30, 45, 60, 90, 120, 180, split by first product.
5. Discount depth and reordersCustomers acquired in the same quarters, split by first-order discount (none, under 20%, 20% or more), with 12-month reorder rate. Your own version of Lewis against Anderson and Simester.

The door table in one query

-- first products and what their buyers did next
WITH firsts AS (
  SELECT customer_id, MIN(ordered_at) AS first_at
  FROM orders WHERE status = 'paid'
  GROUP BY customer_id
  HAVING MIN(ordered_at) <= CURRENT_DATE - 180
),
first_lines AS (
  SELECT f.customer_id, l.product_id
  FROM firsts f
  JOIN orders o ON o.customer_id = f.customer_id AND o.ordered_at = f.first_at
  JOIN order_lines l ON l.order_id = o.order_id
),
after AS (
  SELECT f.customer_id,
         COUNT(o.order_id) FILTER (WHERE o.ordered_at > f.first_at
               AND o.ordered_at <= f.first_at + 180) AS reorders,
         SUM(o.net_revenue) FILTER (WHERE o.ordered_at <= f.first_at + 180) AS rev_180
  FROM firsts f JOIN orders o ON o.customer_id = f.customer_id
  WHERE o.status = 'paid'
  GROUP BY f.customer_id
)
SELECT fl.product_id,
       COUNT(DISTINCT fl.customer_id)                           AS first_orders,
       AVG((a.reorders > 0)::int)                               AS reorder_rate_180,
       AVG(a.rev_180)                                           AS revenue_180
FROM first_lines fl JOIN after a USING (customer_id)
GROUP BY fl.product_id
ORDER BY first_orders DESC;

A customer whose first order contains two products counts toward both. That’s intended: you want to know what each door leads to. For a clean split, rerun it on single-product first orders only and compare.