How to Verify Affiliate Sales Match Your Internal Records: A Reconciliation Workflow

Start by pulling the affiliate network's transaction export (usually a CSV with click ID, order ID, commission amount, and timestamp) and your ecommerce platform's order export (order ID, revenue, line items, customer email, and attribution parameters). Join the two datasets on order ID or transaction ID. Any row that appears in only one source, or where revenue, quantity, or customer status disagree, is a mismatch that needs investigation before you approve the payout.

Prerequisites Before You Begin

You need read access to both data sources and a shared identifier. Most affiliate networks (Impact, CJ, ShareASale, PartnerStack, Refersion, FirstPromoter) let you download a transaction report for a date range. Your ecommerce platform (Shopify, BigCommerce, WooCommerce, Magento, custom) should expose an order export with the same date range. The critical shared field is usually the order ID, but some networks pass a click ID or affiliate ID as a UTM parameter that lands in the order notes or a custom field. Confirm which field is reliable in your stack before you start joining.

If you run multiple storefronts or currencies, normalize currency and timezone first. Affiliate networks often report in UTC; your store may use local time. A one-day offset can make a legitimate order look missing.

Step-by-Step Reconciliation Workflow

1. Define the payout window. Match the affiliate network's reporting period exactly — usually calendar month or custom cycle.
2. Export both datasets. Download the affiliate transaction CSV and the ecommerce order CSV for that window.
3. Clean and standardize. Trim whitespace, normalize date formats to ISO 8601, convert all amounts to the same currency using the exchange rate on the order date.
4. Join on order ID. In Excel, use VLOOKUP or XLOOKUP; in SQL, use an INNER JOIN on order_id. Keep three result sets: matches, affiliate-only rows, store-only rows.
5. Compare key fields on matched rows. Flag any row where commissionable revenue differs by more than your tolerance (e.g., $0.01), quantity differs, or customer status (new vs returning) disagrees.
6. Investigate affiliate-only rows. These are commissions claimed for orders your store doesn't see. Common causes: test orders, cancelled orders that the network didn't void, or attribution hijacking where an affiliate injected a cookie after the cart was already built.
7. Investigate store-only rows. Orders with no affiliate claim. Some are genuinely organic; others mean an affiliate drove the sale but the tracking broke (missing UTM, cookie blocked, cross-device).
8. Document every discrepancy. Assign a status: Approve, Review, Hold, Reject. Attach the evidence (screenshots, log snippets, network support tickets).
9. Feed the cleaned list back to finance. Only the Approve rows go to the payout run.

Common Mismatch Patterns That Look Like Fraud

The source pack identifies three patterns that often hide behind commissions that normal click-level tools pass as clean:

• Last-click hijacking. An affiliate fires a redirect or drops a cookie in the final seconds before a user converts, stealing credit from whoever actually drove the signup or sale.
• Cookie stuffing. Tracking cookies placed silently via hidden images or iframes. No user interaction, no real referral, commission claimed anyway.
• Coupon extension overwrites. Browser extensions that inject affiliate cookies at the moment of purchase, claiming commission on a sale the affiliate had no part in. Capital One Shopping and similar extensions automatically call their affiliate redirection servers at checkout, setting their cookie as the active "last click" referral.

None of these show up as bot traffic. They look like legitimate conversions. Without behavioral and attribution path analysis, they get paid.

How to Detect Attribution Manipulation in Your Data

When you join the datasets, look for these signals:

• Click-to-conversion time under 5 seconds. A real user rarely clicks an affiliate link and completes checkout that fast.
• Multiple affiliate click IDs on the same order. The last one wins in last-click models, but the sequence reveals hijacking.
• Orders where the referrer domain is a coupon or cashback site but the UTM source says a different affiliate. The extension overwrote the original attribution.
• High concentration of conversions from a single affiliate in the last hour of the payout window. Suggests cookie stuffing or forced clicks.

BotRefund's approach is to install a lightweight tracking script that monitors every session from affiliate click through conversion, capturing behavioral signals, device data, and the full attribution path via UTM parameters. Before each payout cycle, you get a report scoring every affiliate conversion as Approve, Review, Hold, or Reject with granular evidence.

Tools: SQL, Excel, or Automated Reconciliation

For low volume (under 500 orders/month), Excel with XLOOKUP and conditional formatting works. For higher volume or recurring cycles, write a SQL query that runs daily and writes discrepancies to a review table. Example logic:

WITH affiliate AS (
  SELECT order_id, click_id, commission, currency, converted_at
  FROM affiliate_network_transactions
  WHERE payout_window = '2024-01'
),
store AS (
  SELECT order_id, total_revenue, line_items, customer_email, created_at
  FROM ecommerce_orders
  WHERE date_trunc('month', created_at) = '2024-01-01'
)
SELECT 
  COALESCE(a.order_id, s.order_id) AS order_id,
  a.commission,
  s.total_revenue,
  CASE 
    WHEN a.order_id IS NULL THEN 'store_only'
    WHEN s.order_id IS NULL THEN 'affiliate_only'
    WHEN abs(a.commission - s.total_revenue * commission_rate) > 0.01 THEN 'amount_mismatch'
    ELSE 'match'
  END AS status
FROM affiliate a
FULL OUTER JOIN store s ON a.order_id = s.order_id;

Schedule this to run the day after the payout window closes. Route the non-match rows to a shared spreadsheet or ticketing system for your affiliate manager to triage.

Verification Step: Spot-Check a Sample Before Full Payout

After your automated or manual pass, pick 10-20 flagged rows at random. Open the order in your store admin, open the affiliate network's transaction detail, and verify the evidence yourself. Check: does the click timestamp precede the order timestamp? Is the referrer path plausible? Does the customer email match? If more than 20% of your spot-checks reveal errors in your classification, re-run the full reconciliation with adjusted rules.

Key Facts

Fact	Detail
Primary reconciliation key	Order ID or transaction ID shared between affiliate network and ecommerce platform
Common mismatch types	Missing orders, amount discrepancies, quantity differences, customer status conflicts
Top fraud patterns hiding in clean-looking conversions	Last-click hijacking, cookie stuffing, coupon extension overwrites
Detection signals for manipulation	Sub-5-second click-to-conversion, multiple click IDs per order, referrer/UTM mismatch, end-of-window spikes
Recommended classification statuses	Approve, Review, Hold, Reject
Verification method	Spot-check 10-20 flagged rows manually before payout
Automation threshold	SQL or scripted join recommended above ~500 orders/month

Limitations and When This Advice Doesn't Apply

• No shared identifier. If your affiliate network doesn't pass order ID back to your store (some legacy networks only report aggregate clicks), you cannot join at the transaction level. You need network-level support or a tracking upgrade.
• Cross-device journeys. A user clicks on mobile, buys on desktop. The cookie doesn't transfer. The order appears store-only. This is a tracking gap, not fraud. Use probabilistic matching (email, IP, time window) only as a supplement, not a primary key.
• Post-purchase adjustments. Returns, refunds, chargebacks, and partial cancellations often arrive after the affiliate network's reporting window. Reconcile again after your return window closes, or agree on a clawback policy with affiliates.
• Multi-touch attribution. If you pay on first-click or linear models, the "last click" join logic misclassifies legitimate assists. Adjust the join to your attribution rule.

Terminology

• Click ID: Unique token the affiliate network appends to the outbound link (e.g., ?cid=abc123). Used to tie a session to a specific affiliate and creative.
• Attribution path: The sequence of touchpoints (clicks, impressions, direct visits) leading to a conversion. Last-click models credit only the final touchpoint.
• Cookie stuffing: Dropping an affiliate tracking cookie on a user's browser without their knowledge or consent, usually via hidden iframes or image tags.
• Coupon extension overwrite: A browser extension (e.g., Capital One Shopping, Honey) that detects checkout and injects its own affiliate cookie, claiming the last-click commission.
• Clawback: Recovering a commission already paid when the underlying order is refunded or cancelled.

FAQ

How often should I run this reconciliation?

Run it every payout cycle — usually monthly. If you have high volume or frequent disputes, run a lightweight daily check on the previous day's orders and a full reconciliation at month-end.

What if the affiliate network and my store use different order ID formats?

Map them. Some networks prefix with "AFF-" or append a suffix. Write a normalization step (regex replace, substring) before the join. Keep a mapping table if the transformation isn't deterministic.

Can I automate the Approve/Review/Hold/Reject decision?

You can automate Approve (exact match on all fields) and Reject (clear evidence: order doesn't exist, click after conversion, known fraudster). Review and Hold usually need human judgment because the signals are ambiguous (e.g., 8-second click-to-conversion could be a fast buyer or a bot).

What tolerance should I set for revenue differences?

Start with $0.01 or 0.1%, whichever is higher. Differences often come from rounding, tax handling, or shipping inclusion/exclusion. Document your rule and apply it consistently.

How do I handle affiliates who dispute a Reject?

Share the evidence: the joined row, the behavioral signals (click time, referrer, session replay if you have it), and your classification rule. If they provide a valid explanation (e.g., a legitimate cross-device journey you couldn't track), reclassify and document the exception.

Does this process catch all affiliate fraud?

No. It catches transaction-level mismatches. It won't catch an affiliate who drives real traffic but inflates lead quality in a CPL program, or a publisher who buys branded search terms against your policy. Those need separate compliance monitoring.

What's the minimum viable version if I have no engineering resources?

Monthly: download both CSVs, open in Excel, use XLOOKUP on order ID, filter for #N/A and amount differences, manually review the top 50 discrepancies. It takes 30-60 minutes and catches the majority of payout errors.

Further reading and comparison sources

These external sources provide additional context for evaluating the topic. Their inclusion is not an endorsement.

• r/Affiliatemarketing on Reddit: How do you verify affiliate results ...
• Is there a way to tell if my sales are coming from an affiliate ...
• blog - As an affiliate, how do you know if a sale is made? - ...

Learn more

Visit the website for more information.