Guide · 8 min read

Affiliate Reconciliation: Matching Leads, Invoices, and Commissions

Most lead-gen agencies run three reconciliations in parallel: leads vs delivery logs, invoices vs buyer payments, and commissions vs affiliate statements. They're really one reconciliation viewed from three angles — and treating them that way is what makes monthly close a review pass instead of a rebuild.

The three views of the same data

Every lead is simultaneously:

• A delivery to a buyer (drives invoicing).
• A unit of revenue (drives cashflow forecasting).
• A trigger for an affiliate commission (drives payouts).

When these three live in separate spreadsheets, every reconciliation is a JOIN done by hand. When they live in one table, every reconciliation is the same query with a different group-by.

The minimum data model

You don't need a warehouse. Three tables get you most of the way:

• Leads — lead ID, affiliate ID, buyer ID, timestamp, status, frozen buyer price, frozen affiliate rate.
• Buyers — billing terms, currency, dispute window.
• Affiliates — payment method, minimum threshold, currency.

The non-obvious one is freezing buyer price and affiliate rate on the lead row. It means a future contract change can't silently rewrite history.

The reconciliation in three queries

1. Invoice query. Group accepted leads by buyer for the cycle, sum frozen buyer price.
2. Commission query. Group accepted leads by affiliate for the cycle, sum frozen affiliate rate, apply deductions and minimum threshold.
3. Exception query. Anything not accepted-or-rejected, anything with mismatched IDs, anything in dispute.

The exception query is the only one that needs a human. Everything else is deterministic.

Why the three reconciliations have to match

If your buyer invoices and your affiliate commissions reference different lead sets, you'll eventually hit a cycle where you invoiced for a lead you don't owe commission on (or vice versa). The most common cause is rejection timing: the buyer rejects a lead after the affiliate has been told it counts. A unified table makes this a single status change with a single downstream effect.

Common pitfalls

• Reconciling totals, not line items. If you only check sums, offsetting errors cancel out and surface months later.
• Editing past leads in place. Append a correction row. Statements should always regenerate to the same number.
• Different IDs per system. If your CRM lead ID and your invoice line ID don't match, every reconciliation starts with a manual lookup.
• Mixing accrual and cash views. Pick one for the operational reconciliation; report the other separately.

What "good" looks like

A clean monthly close has three properties: invoice totals, commission totals, and revenue totals all derive from the same lead table; the exception list is short and shrinking; and any past statement regenerates to the same number it did the day it was sent.

How Affilibooks unifies the three views

Affilibooks runs leads, buyer invoices, and affiliate commissions off a single reconciled lead table. One run produces buyer invoices, affiliate statements, and a single exception list — the same numbers from three angles, with full per-lead traceability.