FOR YOUR ANALYST — Close the Loop

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

Field	On	Written when	Rule
`referral_code`	Customer	Account creation	One per customer, never reused
`referred_by`	Customer	First order	The inviter's customer ID; set once, never overwritten
`referral_source`	Customer	First order	Code, link, gift, shared item, marketplace follow
`holdout_digit`	Customer	Profile creation	Random 0 to 9, set once, never recomputed
`first_order_delivered_at`	Customer	Carrier delivery event	The event that starts the return window
`reward_issued_at`, `reward_voided_reason`	Reward	Payout or void	Every void gets a reason: self-referral, refund, duplicate, cap
`lifetime_score`	Customer	Every scoring event	Games only: authoritative total from the system of record, never a delta

Six pulls

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.
K, trailing 90 daysReferred customers with a completed, unrefunded first order in the window, over customers active in the window. Bad reading: counting signups.
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.
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.
ConcentrationShare of referred customers brought by the top tenth of inviters. Bad reading: ranking on invites sent instead of customers brought.
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.

This is one chapter of Close the Loop, which is free and readable in full on a single page with no form in front of it.