Catching fake referrals: four queries and one fix that beats them all

Referral programmes get abused more reliably than anything else you can ship. The reward is instant, the qualifying action is cheap, and the whole thing is designed to be passed around.

What makes it hard is that each account looks fine. Someone signed up, used a code, did what the offer asked. Nothing about that account is wrong.

What is wrong is that the same person owns the account that referred it.

So checking accounts one at a time finds nothing. You have to look at the shape of the tree.

Why your current checks miss it#

One account per email? Email is free and unlimited. Dots and plus tags give one Gmail user endless addresses that all deliver to the same inbox.

One per IP? A residential proxy costs about a dollar. And blocking IPs punishes households and mobile users who legitimately share one.

One per device? Closest, and still not enough. On our own live traffic, one iPhone fingerprint covered 2.31 different people, because identical phones make identical fingerprints.

So they change whatever you check, reuse whatever you do not, and every account passes.

Four queries#

Run these against your own tables. Rename the columns to match yours.

1. People who referred each other#

The laziest version, and it still catches people.

SELECT a.referrer_id, a.user_id, a.created_at
FROM referrals a
JOIN referrals b
  ON a.referrer_id = b.user_id
 AND a.user_id     = b.referrer_id
WHERE a.referrer_id < a.user_id   -- each pair once, not twice
ORDER BY a.created_at DESC;

On a healthy programme this returns almost nothing. Every row is worth a look.

2. Referrals that die the day they pay#

This is the strong one.

A real referral is a person who wanted your product. A fake one is a job that ends when it pays.

SELECT r.referrer_id,
       count(*)                                                                 AS referred,
       count(*) FILTER (WHERE u.last_seen_at < r.paid_at + interval '24 hours') AS went_quiet,
       sum(r.bounty_amount)                                                     AS paid_out
FROM referrals r
JOIN users u ON u.id = r.user_id
WHERE r.paid_at IS NOT NULL
  AND r.paid_at > now() - interval '90 days'
GROUP BY r.referrer_id
HAVING count(*) >= 5
   AND count(*) FILTER (WHERE u.last_seen_at < r.paid_at + interval '24 hours')::float
       / count(*) > 0.8
ORDER BY paid_out DESC;

Ten referrals where eight vanished within a day of payout is not a retention problem. That is one person doing a job.

3. Referrals arriving in a burst#

Real ones trickle in as people mention you. Fake ones arrive in one sitting, because someone sat down and did them.

SELECT referrer_id,
       date_trunc('hour', created_at) AS hour,
       count(*)                       AS in_that_hour
FROM referrals
WHERE created_at > now() - interval '30 days'
GROUP BY referrer_id, hour
HAVING count(*) >= 5
ORDER BY in_that_hour DESC;

4. Where the money ends up#

The one people skip, and the one that turns a hunch into a finding. Accounts that share nothing at signup often share a wallet at payout.

SELECT payout_destination,
       count(DISTINCT user_id) AS accounts,
       sum(amount)             AS total
FROM payouts
WHERE created_at > now() - interval '90 days'
GROUP BY payout_destination
HAVING count(DISTINCT user_id) > 3
ORDER BY total DESC;

What a fraud tool adds#

Be clear about the split, because it decides what is worth buying.

We can tell you two accounts are connected. Same device, same network, or the same real inbox once you strip out the dots and plus tags. That needs someone holding all the other accounts to compare against, which is the part you cannot do alone.

We cannot see your referral tree. We do not take a referrer field and we do not store your referrals. Those queries stay yours. That is your data and it should stay that way.

The case gets made by putting both halves together.

const r = await kaidn.score({
  event: "referral_claim",
  user_id: referredUser.id,
  ip: req.ip,
  email: referredUser.email,
  device_id: body.kaidn_device_id,
});

// our half: does this account look like someone we have seen?
const linked =
  r.reasons.includes("email_reuse") ||
  (r.reasons.includes("device_reuse") && (r.device?.account_count_same_network ?? 0) > 1);

// your half: query 1 above
const reciprocal = await db.referrals.isReciprocal(referrer.id, referredUser.id);

if (linked || reciprocal) return holdBounty(referredUser.id, r.event_id);

Note that network check. A device seen on three different networks is often a shared laptop or an office, not a farm. On one network the coincidence gets much harder to believe. Acting on a bare device match is how you ban someone's flatmate.

What to do when you find it#

Do not pay the bonus. Do not ban the account.

The maths is simple. Wrongly refusing a bonus costs you a bonus and an email. Wrongly banning an account costs you a customer, a support ticket, and sometimes a public complaint.

Let them keep using the product. If you were wrong, they barely notice. If you were right, you took away the reward without creating a queue of appeals.

Save bans for cases where several independent signals agree.

The fix that beats all four queries#

Detection is the wrong first move here.

Pay the bonus for something that costs real money or real time to fake. Not for signing up. Not for confirming an email. For a deposit, a subscription payment, or thirty days of actual use.

The moment the reward needs something expensive, the maths collapses for every farmer at once, and you did not detect anything. Someone will happily run forty accounts for forty easy bonuses. They will not run forty real deposits.

That is a product change. It costs nothing to run and it holds forever.

It will also cost you some genuine referrals, because a delayed reward is a weaker reward. That trade is a business call, not a technical one.

Do this today#

Run query 2. It takes an hour and tells you whether you have a problem worth spending money on.

That beats any vendor demo, ours included.

More on this: how to stop multi-accounting for the general case, and bonus abuse for the wider pattern.