When your affiliate program hits 40–50 active partners, spreadsheets stop working. Commission rules fragment across tabs. Payout records drift from your GL. Refunds post days late and erase affiliate earnings retroactively. You lose 8–12% annually just reconciling who owes whom. The fix is not a bigger spreadsheet. It's a rules engine that tracks every sale, applies commission logic in real-time, flags fraud before payout, and routes money to the right account on schedule. This playbook walks you through structure, automation, and the audit checklist that catches leaks. Why spreadsheets fail at 40+ affiliates A single shared Google Sheet works fine for 8–12 partners. Everyone can see the same commission tracker. But the moment you cross 30 affiliates, three problems emerge: Manual rule application fails. You have 15 commission tiers (referral vs. direct sale, product category, geographic region, seasonal rates). Spreadsheet formulas can handle some of this, but edge cases explode. Did Alice's sale qualify for Q3 bonus before the region changed? What about the refund that posted in month two of a three-month lookback? Refunds and chargebacks orphan tracking. A customer pays through Affiliate X, you invoice them, they request a chargeback in month two. By then, you've already calculated and paid X's commission. Now you owe them a clawback. But you've paid out 12 other affiliates and your ledger is a mess. GL reconciliation becomes monthly pain. Your finance team needs to know what landed in your bank, what went to affiliates, and what difference exists. Spreadsheet totals rarely match bank feeds, settlement reports, and your invoice ledger simultaneously. Real costs: A mid-market SaaS with 50 affiliates averages ₹2–4L annually in reconciliation labor, chargebacks that go unrecovered, and duplicate payments because no one owned the audit. Build the payout architecture: four data flows Stop thinking of affiliate payouts as a monthly reporting task. Think of it as a data pipeline with four stages. Each stage has a single job and hands off clean data to the next. 1. Sale capture and commission calculation The moment a customer converts, you need to record: Affiliate ID (unique, immutable) Sale ID (link to invoice or order record) Commission type (referral, channel, reseller, etc.) Sale amount (revenue only; tax-exclusive) Applicable rules (tier, category, campaign, seasonal rate) Calculated commission (amount or percentage) Status (earned, clawed back, paid) This record must flow directly from your billing system (or a webhook your payment processor fires). Do not manually enter it. If your invoicing platform has an API, connect it. If it doesn't, a Zapier or Make automation can poll for new invoices and write them to your affiliate ledger. The key: calculate commission at the moment of sale , not retroactively. If rules change next month, do not recalculate past sales. Grandfather them. This prevents the endless audits and affiliate complaints that kill program trust. 2. Refund and fraud detection Every refund, chargeback, or reversal must automatically trigger a clawback. Set up two watches: Refund sync: Connect your billing system to your affiliate ledger via webhook or daily batch. When a refund posts, mark the associated commission as "clawed back" and deduct it from the affiliate's rolling balance. Fraud flags: Create rules that auto-flag suspicious patterns. Examples: same customer email converting through five different affiliates in 24 hours; affiliate referring themselves; sales from the affiliate's own IP address. These don't auto-clawback—they queue for manual review. But they stop you from paying out until verified. Test your clawback logic with a real scenario. Simulate an affiliate earning a ₹5000 commission on Jan 15, then a customer refund posting Feb 2. Does the system deduct ₹5000 from the affiliate's Feb payout? Or does it create a negative balance that rolls forward? Decide, document, and enforce it consistently. 3. Payout scheduling and batching Payouts happen on a schedule, not on demand. Standard cadences are: Monthly (most common): Cut on the 5th of the following month. Affiliates see earnings for the full prior month. Weekly: Higher volume programs (200+ partners) sometimes do weekly to improve cash flow for top performers. Threshold-based: Pay out only when an affiliate's balance hits ₹10K (avoids small, expensive transfers). Pick one and stick to it. Communicate it clearly. Then automate it: create a scheduled job (e.g., every 5th at 9 AM) that: Pulls all affiliate balances from the prior period. Deducts any clawbacks, taxes, or fees. Generates a payout batch with beneficiary name, account number, and amount. Hands it off to your payment processor (Razorpay, Stripe, 2Checkout bulk payout API, or bank transfer). Logs the transaction ID and status. Sends each affiliate a payout receipt (email template with date, amount, and next payout date). 4. GL reconciliation and audit trail Every pa