Affiliate marketing attribution: reconcile sales credit

Affiliate marketing attribution assigns partner credit to an order, then reconciles that credit with recorded sales and the commission agreement. The practical result is a payable amount supported by order IDs, eligible revenue and documented reversals. A conversion total alone cannot prove that amount.

Set the payment rule before matching rows

Keep two questions separate: who receives analytical sales credit, and who is owed a commission? An affiliate contract answers the payment question. Your broader attribution policy answers the reporting question. Google's attribution documentation defines attribution as assigning credit along the path to an action; it does not define your affiliate payment obligation.

Write down the eligible interaction, the window before purchase, how conflicting affiliate claims are resolved, and the commission base. Specify whether shipping, taxes, discounts, returns and canceled orders affect that base. These are program terms to confirm, rather than assumptions to fill with a common industry rate.

Use the existing marketing attribution models comparison for general credit allocation. Here the narrower task is proving the commission ledger against sales.

For the example below, a fictional US distributor pays the agreed rate on merchandise sales after refunds. It excludes shipping and sales tax from every displayed sales amount. Only interactions within its documented eligibility window earn payment. Rates stay with their original orders when refunds occur.

Start with the exports you already have

Bring an affiliate conversion export, an order register, a refund export and the commission agreement. Keep the original order ID in each file. If refunds contain multiple rows per order, summarize them by order ID before joining them to the register.

The working sheet needs order ID, affiliate ID, interaction date, purchase date, gross merchandise sales, refunds, rate, eligibility reason and approval status. Preserve currency and the reporting cutoff too. Do not match on order amount alone: separate customers can place identical orders.

Use a distinct claim ID to identify exact retransmissions. An order claimed by different partners is a conflict requiring the credit rule, rather than an automatic duplicate deletion. Store the winning claim and the excluded claim so the payment reviewer can follow the decision.

Work from sales to approved commissions

The distributor reviews these unique claimed orders after removing an exact duplicate. Refunds are positive amounts deducted from sales. Currency figures are shown to whole dollars; commissions use the stated rates without intermediate rounding.

A105 is a real recorded sale, but its affiliate interaction fell outside the agreed window. It stays in sales and earns no commission. A103 was fully refunded. A102 earns commission only on the retained merchandise amount.

Lay the displayed table in columns A through I, with the first order in row 2. These formulas work in Excel:

D2=B2-C2
F2=B2*E2
G2=C2*E2
I2=F2-G2-H2

Column H contains the reviewed eligibility exclusion; it is not a plug used to force a total. Copy the formulas through row 6. To summarize approved commission for an affiliate, put affiliate IDs in a separate working column J and the selected affiliate in L2:

=SUMIFS(I2:I6,J2:J6,L2)

Microsoft documents SUMIFS as adding amounts that meet specified criteria. Use equal-sized ranges and include all working rows. Covirage is not affiliated with Microsoft or Google.

Prove both the payment and the revenue

The original conversion export also repeated A101's commission claim once. Keep that duplicate outside the unique-order table and show its removal in the reconciliation:

The payment check is 524 - 100 - 60 - 60 = $304. The independent sales check is 4,100 - 700 = $3,400. Commission-bearing net sales are 1,000 + 600 + 0 + 1,200 = $2,800; adding A105's excluded $600 restores the same $3,400 sales total.

Tie that sales cohort to its order-register subtotal. If your financial revenue report uses a different recognition date or includes other components, show that bridge separately. Do not silently call merchandise order value financial-statement revenue.

Where it goes wrong

• Paying duplicate claims. Repeated exports can repeat the same conversion. Reconcile claim IDs before calculating payment.
• Reversing at the current rate. A later rate change must not alter the reversal of an older commission unless the agreement requires it.
• Deleting excluded sales. A sale outside the affiliate window remains a sale. Exclude its commission, then preserve the revenue reconciliation.
• Mixing refund cutoffs. Label the orders included and the date through which refunds were captured. Refresh the same cohort as returns arrive.
• Treating commission credit as proof of impact. A payable commission establishes contractual credit, not a causal increase in sales.

The affiliate program manager owns claim eligibility; finance approves the sales tie and payable. After this reconciliation, affiliate marketing ROI evaluates the economics. If creator codes overlap with affiliate links, use the separate influencer marketing attribution reconciliation to prevent duplicated sales credit.

Reconcile your own affiliate file in Covirage

Bring a sample of your own order, affiliate and refund exports with the documented commission rules. Covirage maps the columns, and its tools compute and check the supplied sales and commission figures; the external AI model explains the result with every figure traced to its rows. Create a free Covirage account, which takes about a minute, then set up your business and bring your file.

See revenue driver analysis for the supporting business context.