FOR YOUR ANALYST

Every pull below runs on an order table and a customer table. Resolve identity first, the way The Second Order's appendix does: customer ID, then normalized email, then phone. A split identity turns one referred customer into a stranger and a self-referral into a win.

The loop's data contract

Six pulls

1. Loop shareNew customers in the period with a non-empty referral_source, over all new customers. Bad reading: a share that jumps the month a coupon forum finds your code.
2. K, trailing 90 daysReferred customers with a completed, unrefunded first order in the window, over customers active in the window. Bad reading: counting signups.
3. Cycle timeMedian days from each inviter's first order to their first referred customer's first order. Bad reading: using the invite date instead of the order date.
4. Referred cohort quality90-day second-order rate and refund rate, referred against paid, same acquisition months. Bad reading: comparing a referred cohort from last year with a paid cohort from last month.
5. ConcentrationShare of referred customers brought by the top tenth of inviters. Bad reading: ranking on invites sent instead of customers brought.
6. Known before referralShare of referred customers who were on your list, had visited, or had ordered under another identity before the referral. That's a floor on "would have come anyway."

K in one query

-- referred customers per active customer, trailing 90 days
WITH active AS (
  SELECT DISTINCT customer_id FROM orders
  WHERE status = 'fulfilled' AND ordered_at >= CURRENT_DATE - 90
),
referred AS (
  SELECT c.customer_id FROM customers c
  JOIN orders o ON o.customer_id = c.customer_id AND o.is_first_order
  WHERE c.referred_by IS NOT NULL
    AND o.status = 'fulfilled' AND o.refunded_at IS NULL
    AND o.ordered_at >= CURRENT_DATE - 90
)
SELECT (SELECT COUNT(*) FROM referred) * 1.0
     / NULLIF((SELECT COUNT(*) FROM active), 0) AS k_90d;

Adapt the names to your warehouse. The logic is the point: completed first orders over active customers, same window.

Small-file rules

• Under ten expected referred customers per arm, don't run the test. Decide another way and write down how.
• Report every rate with its count. "12% of referred customers reordered" means little without "(n = 41)."
• Fix the read date before the test starts, and only look early to check the groups are clean.