Calculate affiliate revenue from settled payments, refunds and reversals with a transaction ledger, worked formula, QA tests and payout controls.

Short answer: answer: Start with successfully captured eligible order value, subtract successful refunds and chargebacks under the commission policy, then calculate commission on that settled basis—not on checkout value. Keep order-cohort and cash-period views, and delay final payout until the return window or apply documented clawbacks. Reconcile every amount to Shopify refund transactions or Stripe balance transactions before calling it settled.

Gross checkout value is convenient because it appears immediately. It is also a poor payout basis when orders can fail, cancel, refund, exchange or dispute after the creator dashboard has credited them.

The opposite extreme—waiting forever for every possible reversal—is not operational. A sound policy defines maturity, a provisional amount and a correction mechanism, then makes the residual visible.

The Settled-Revenue Reconciliation

CDM's Settled-Revenue Reconciliation moves each affiliate order through five states: attributed, captured, adjusted, matured and paid. Our position is explicit: affiliate payout and performance should use settled, refund-adjusted contribution—not gross checkout value. If the partner contract uses another basis, report that contractual measure separately rather than relabeling it economics.

State 1: attribute an eligible order

Create an immutable affiliate assignment containing order_id, affiliate or creator ID, campaign ID, evidence type, evidence timestamp, attribution rule version and confidence. Define cookie window, coupon precedence, last-click behavior, self-referral exclusions and cross-device limits in the agreement.

An eligible order must pass basic controls: not a test, not fraudulent under the approved rule, not internally placed, within the campaign term and carrying valid evidence. Do not delete excluded orders; retain them with reason codes so creator statements can be reproduced.

State 2: recognize successful capture

Model payments, not just orders. In Shopify, retrieve order transactions and money sets in shop and presentment currencies. In Stripe, balance transactions represent movements through the Stripe balance and expose gross amount, positive fee, net = amount − fee, currency, status, available_on, source and reporting category.

Choose the affiliate revenue base contractually. Common components are product value after discounts, with tax, shipping, gift cards and tips excluded. A reasonable definition is:

eligible_captured_value = successful captured product value after discounts − excluded components

Do not subtract discounts again when the selected captured field already reflects them. Keep integer minor currency units until presentation.

State 3: subtract successful reversals

Link every refund to its original order, payment and, where possible, line item. Shopify's Refund object can include line items, shipping, taxes, duties, fees and transactions. Shopify warns that creating or seeing a refund object does not guarantee money has returned; inspect the associated transaction status.

Stripe creates refund and dispute-related balance transactions. Use accounting-oriented reporting_category for classification and retain the source object. A refund that is pending is a forecast adjustment; a successful negative balance movement is settled. Treat failed refunds according to actual financial state, not intent.

Define allocation for partial refunds. If exact line linkage exists, remove eligible value for those lines. Otherwise, document a proportional method. Handle exchanges separately: a return plus replacement may be economically neutral, partially refundable or a new order, depending on policy.

State 4: mature the amount

Maintain two ledgers:

  • order-cohort ledger: adjustments restate the original order's affiliate economics;
  • cash-period ledger: adjustments enter on the transaction or availability date.

The cohort view answers “how valuable were March acquisitions?” The cash view answers “what should this payout cycle transfer?” They reconcile over time but need not match in a month.

Set a maturity rule such as 30 days after fulfillment plus successful payment, or the category's actual return window. Before maturity, label commission provisional. After payout, later refunds enter a clawback or reserve process permitted by the contract and local law. Avoid silently taking a negative amount from an unrelated campaign.

State 5: calculate commission and contribution

Use these definitions:

settled_eligible_revenue = eligible_captured_value − successful_eligible_refunds − eligible_chargebacks

affiliate_commission = max(0, settled_eligible_revenue × contractual_rate) + approved_bonus − approved_clawback

affiliate_contribution = settled_eligible_revenue − cost_of_goods − fulfillment − payment_fees − affiliate_commission − other_variable_costs

Whether commission floors at zero is a contractual choice. Some programs carry a negative balance. State that rule directly.

Worked example: an order contains $105 of products after discounts, $10 tax and $5 shipping, for a $120 checkout. The agreement excludes tax and shipping, so captured eligible value is $105. A successful $35 product refund makes settled eligible revenue $70. At a 10% rate, commission is $7, not $12 or $10.50.

If product cost on retained goods is $28, fulfillment and payment fees total $9, affiliate contribution is $26. The example illustrates the formula; it is not a benchmark.

This transaction-grain query pattern is illustrative and unexecuted:

```sql SELECT affiliate_id, order_id, SUM(CASE WHEN transaction_class = 'eligible_capture' AND status = 'succeeded' THEN amount_minor ELSE 0 END)

  • SUM(CASE WHEN transaction_class IN ('eligible_refund','chargeback')

AND status = 'succeeded' THEN ABS(amount_minor) ELSE 0 END) AS settled_eligible_revenue_minor FROM affiliate_transaction_ledger WHERE currency = 'usd' GROUP BY 1, 2; ```

Never net currencies in one group. Convert through an approved rate table with the rate type and date preserved, or report each currency separately.

Data model, QA and reconciliation

Build five tables:

  • affiliate_assignment: immutable order attribution and rule version;
  • order_component: product, discount, tax, shipping, tip and currency components;
  • payment_transaction: capture, refund, dispute and reversal with status and source ID;
  • commission_ledger: provisional, matured, paid, clawed back and reserved entries;
  • payout_reconciliation: opening payable, earned, adjustments, payments and closing payable.

Run eight controls:

  1. one active assignment per order under a rule version;
  2. captured transactions are unique by provider transaction ID;
  3. successful refunds do not exceed eligible captured value without an approved exception;
  4. order, presentment and settlement currencies remain distinct;
  5. test, cancelled and excluded orders never mature;
  6. commission recomputes from ledger components and contract rate;
  7. payout statement opening plus activity equals closing payable;
  8. processor net reconciles separately from Shopify order value and finance cash.

Shopify, Stripe and finance answer different questions. Shopify may show an order total before returns; Stripe's balance transaction includes fees and availability; finance may recognize revenue on fulfillment and classify tax as a liability. Build a bridge instead of choosing the largest number.

For a decision example, Affiliate A shows $50,000 checkout value and Affiliate B $42,000. After maturation, A has $12,000 refunds and $4,000 chargebacks; B has $3,000 refunds and no chargebacks. A's settled eligible revenue falls below B's, and contribution after commission is materially worse. The next budget goes to B, while A's fraud, product-fit and traffic quality receive diagnosis. The program can explain the change order by order.

The reader asset: affiliate payout statement schema

Each statement should contain order ID, order date, evidence, eligible captured components, refund or dispute IDs, status, maturity date, currency, rate, commission, prior payout, clawback and closing payable. Include the contract and attribution-rule versions and a dispute deadline. The partner should be able to reproduce the arithmetic without receiving customer personal data.

Related guides

Frequently asked questions

Should affiliate commission be based on gross or net revenue?

Use the basis stated in the contract, but CDM recommends successful, refund-adjusted eligible product value rather than gross checkout value. Exclude tax and other pass-through components unless the agreement deliberately includes them. This aligns payout with retained economics.

The caveat is legal and commercial: changing an existing basis requires notice and agreement, and some networks define “net sales” differently. Preserve the contractual calculation even when management also reviews contribution. Management can compare both bases, but only the contracted one determines the statement.

When should a refund reduce affiliate revenue?

Reduce performance in the original order cohort when the refund successfully settles; reflect cash payable in the period the adjustment becomes effective under the policy. Pending refund intent should be shown as provisional, not silently treated as completed cash.

The caveat is operational lag: a return may be approved before money moves, and a failed refund can later retry. Store every status transition and use the processor or transaction ledger to identify settlement. Use a status-aware reserve so payout does not outrun settlement.

How do partial refunds affect creator commission?

Remove the eligible value of the refunded lines or components, then recalculate commission at the contractual rate. If line linkage is unavailable, apply a documented proportional allocation and flag the estimate. Do not reduce commission for refunded tax if tax was never commissionable.

The caveat is bonuses and tiers: a refund can move the affiliate below a threshold. The agreement must say whether tiers are recalculated retroactively, prospectively or only at statement close. Show the original and revised tier on the statement.

Is Shopify total refunded enough for reconciliation?

It is a useful control total, but inspect refund components and associated transaction status for settlement. Shopify documents that a Refund object itself does not guarantee money has returned. Order-level totals can also mix tax, shipping and other components that the affiliate policy treats differently.

The caveat is a simple program with whole-order refunds and one currency: order totals may be operationally sufficient, but the policy and exception handling should still be documented. Retain component-level evidence for any material payout dispute.

What does Stripe net mean for affiliate reporting?

Stripe balance-transaction net is the gross amount minus Stripe fees for that balance movement. It helps reconcile processor cash, but it is not automatically affiliate-eligible revenue or contribution. Map its source and reporting category to the order and policy components.

The caveat is Connect: refunding a platform charge may not automatically reverse associated transfers in separate-charges-and-transfers setups, so creator payouts and platform cash can diverge until a transfer reversal or later offset occurs. Reconcile both gross and fee components before explaining profitability.

How long should we wait before paying affiliates?

Use a maturity period tied to fulfillment, payment availability, observed return lag and the contractual dispute process. Thirty days may work for some categories but is not universal. Show provisional earnings before maturity and maintain a reserve or explicit clawback rule for late reversals.

The caveat is partner experience and law: an excessively long hold damages trust and may conflict with agreements. Measure actual refund timing and set the shortest defensible window. Clearly publish the maturity clock on every provisional partner statement.

Next decision: Why Do Ad Platforms, GA4, CRM and Finance Disagree—and How Do You Reconcile Them?

Related reading: How Do You Measure Creator Cohorts and Repeat Purchase With Shopify and Klaviyo? · How Do You Configure Salesforce Campaign Influence Rules Finance Can Audit? · How Do You Build a Marketing Measurement Warehouse With GA4 and BigQuery?

Sources and research notes

Limitations: CDM did not access a reader's Shopify store, Stripe account, affiliate contract, tax configuration or general ledger and did not execute the SQL. Money-field selection, refund status, exchange treatment, clawbacks and local legal requirements need implementation-specific review.

CDM Editorial

This article is editorial guidance. Apply the principles in proportion to your market, evidence, and responsibilities.