turnley.dev

PHP and MySQL code for faucet operators

Tools & Calculators

FCT-006

Measure what a claim costs, do not model it

Compute cost per claim from payouts that actually happened. Two accounting bugs make the modelled figure wrong, and both are easy to ship without noticing.

The tempting way to price a claim is to simulate the reward table. It is also the way to be confidently wrong, because the live figure drifts from the model as soon as anything changes.

Measure it instead: total paid out divided by claims taken, over a window. Simple — and there are two bugs that will quietly corrupt it.

The first is dividing by a count that only exists when a rollup job ran. If cron misses a day, that day has payouts and zero claims, and the ratio climbs without limit. Count claims from the claims table over the same window instead.

The second is pooling unrelated sources into one payout figure. If a credit is counted when it is awarded and again when it is withdrawn, every claim is charged twice and the daily budget drains at double speed.

PHP 
/**
 * Cost per claim, measured. Both windows read the same tables so a missed
 * rollup cannot skew the divisor.
 */
function cost_per_claim(PDO $pdo, int $days = 7): array
{
    // Count claims from the claims table, NOT from a rollup that may not have run.
    $st = $pdo->prepare('SELECT COUNT(*) FROM claims WHERE created_at >= DATE_SUB(NOW(), INTERVAL ? DAY)');
    $st->execute([$days]);
    $claims = (int) $st->fetchColumn();

    // Only the sources that are the COST OF A CLAIM. A withdrawal moves money
    // the user already owned — counting it here charges the house twice.
    $st = $pdo->prepare(
        'SELECT COALESCE(SUM(units), 0) FROM house_ledger
          WHERE source IN ("claim_award", "autocash")
            AND created_at >= DATE_SUB(NOW(), INTERVAL ? DAY)'
    );
    $st->execute([$days]);
    $paid = (int) $st->fetchColumn();

    $perClaim = $claims > 0 ? $paid / $claims : 0.0;

    return [
        'claims'        => $claims,
        'paid_units'    => $paid,
        'per_claim'     => $perClaim,
        'measured_over' => $days . ' days',
        'usable'        => $claims >= 500,            // below that it is noise
    ];
}

/** What you can afford to grant per completion, from real numbers. */
function grants_per_completion(PDO $pdo, float $revenuePerCompletionUsd, float $usdPerUnit): array
{
    $cost = cost_per_claim($pdo);
    $costUsd = $cost['per_claim'] * $usdPerUnit;

    if (!$cost['usable'] || $costUsd <= 0) {
        return ['ready' => false, 'why' => 'not enough claims measured yet'];
    }

    $margin = setting_int('earn_margin_percent', 40) / 100;
    $affordable = ($revenuePerCompletionUsd * (1 - $margin)) / $costUsd;

    return ['ready' => true, 'recommend' => (int) floor($affordable), 'cost_usd_per_claim' => $costUsd];
}

Using it

Show the measured figure in admin next to the modelled one. When they diverge, the model is what is wrong.

Require a minimum sample before the recommendation appears. A figure from forty claims will send you in the wrong direction confidently.

Roll up the last several days each run, not just today, so one missed run heals itself.

What bites people

Ledger sources must be separable. If award, withdrawal, referral and admin adjustment all land in one bucket, no correct measurement is possible.

Index the claims table on created_at alone. Without it this query gets slow exactly when the site gets busy.

A float ratio is fine for display, but keep every stored amount an integer.

This one touches real money. Point it at a throwaway wallet and watch a full cycle before you trust it with a live balance.

Also in Faucet Mechanics