REF-004

Filter out referrals that were never real

Count active referrals, not registered ones. Four queries that separate a genuine promoter from someone with a script.

Referral counts are the most inflated number on any faucet. Signing up twenty accounts through your own link takes minutes, and if the count is what pays, that is what gets gamed.

The defence is to define a referral as active rather than registered — some minimum of real activity across some minimum of days — and to display and pay on that number only.

Then look at the shape. Genuine referrals arrive irregularly from varied devices; farmed ones arrive in a burst from a handful of fingerprints and stop.

PHP

// Active referrals only: several sessions, on different days, over time.
function active_referral_count(PDO $pdo, int $referrerId): int
{
    $st = $pdo->prepare(
        'SELECT COUNT(*) FROM (
            SELECT u.id
            FROM users u
            JOIN ledger l ON l.user_id = u.id AND l.kind = "claim"
            WHERE u.referrer_id = ?
              AND u.created_at < DATE_SUB(NOW(), INTERVAL 2 DAY)
            GROUP BY u.id
            HAVING COUNT(*) >= 20 AND COUNT(DISTINCT DATE(l.created_at)) >= 3
         ) t'
    );
    $st->execute([$referrerId]);
    return (int) $st->fetchColumn();
}

// Shape check: how many distinct devices are behind this person's referrals?
function referral_device_spread(PDO $pdo, int $referrerId): array
{
    $st = $pdo->prepare(
        'SELECT COUNT(DISTINCT u.id) AS refs,
                COUNT(DISTINCT a.visitor_key) AS devices,
                COUNT(DISTINCT DATE(u.created_at)) AS signup_days
         FROM users u
         LEFT JOIN activity a ON a.user_id = u.id
         WHERE u.referrer_id = ?'
    );
    $st->execute([$referrerId]);
    $r = $st->fetch(PDO::FETCH_ASSOC) ?: ['refs' => 0, 'devices' => 0, 'signup_days' => 0];

    $r['suspicious'] = (int) $r['refs'] >= 5
        && ((int) $r['devices'] <= 2 || (int) $r['signup_days'] <= 1);

    return $r;
}

// Active referrals only: several sessions, on different days, over time.
function active_referral_count(PDO $pdo, int $referrerId): int
{
    $st = $pdo->prepare(
        'SELECT COUNT(*) FROM (
            SELECT u.id
            FROM users u
            JOIN ledger l ON l.user_id = u.id AND l.kind = "claim"
            WHERE u.referrer_id = ?
              AND u.created_at < DATE_SUB(NOW(), INTERVAL 2 DAY)
            GROUP BY u.id
            HAVING COUNT(*) >= 20 AND COUNT(DISTINCT DATE(l.created_at)) >= 3
         ) t'
    );
    $st->execute([$referrerId]);
    return (int) $st->fetchColumn();
}

// Shape check: how many distinct devices are behind this person's referrals?
function referral_device_spread(PDO $pdo, int $referrerId): array
{
    $st = $pdo->prepare(
        'SELECT COUNT(DISTINCT u.id) AS refs,
                COUNT(DISTINCT a.visitor_key) AS devices,
                COUNT(DISTINCT DATE(u.created_at)) AS signup_days
         FROM users u
         LEFT JOIN activity a ON a.user_id = u.id
         WHERE u.referrer_id = ?'
    );
    $st->execute([$referrerId]);
    $r = $st->fetch(PDO::FETCH_ASSOC) ?: ['refs' => 0, 'devices' => 0, 'signup_days' => 0];

    $r['suspicious'] = (int) $r['refs'] >= 5
        && ((int) $r['devices'] <= 2 || (int) $r['signup_days'] <= 1);

    return $r;
}

Using it

Show both numbers to the user: registered and active. Hiding the difference produces accusations of theft; explaining it produces better promotion.

Set the activity bar low enough that a real user clears it in a day or two. Too high and honest promoters give up.

Review the spread report before paying any large referral bonus, rather than after.

What bites people

Families and shared computers produce genuine referrals from one device. Suspicious means look, not ban.

A two-day age requirement stops the burst-and-cash-out pattern and costs an honest promoter nothing.

Do not retroactively remove commission already paid unless the fraud is unambiguous. Clawing back an honest user's earnings costs more than the fraud did.