From Sales to Service Delivery: Revenue Attribution and Partner Commissions
In health services, training, and membership businesses, an order amount is not necessarily revenue earned.
A member may buy a stored-value card today and use it weeks later to purchase a course package. The package may be paid for before any sessions have been delivered. Refunds, partial consumption, and settlement across multiple months make the relationship more complicated.
A partner order list brings these issues together. It needs to display the original sale, the value attributed to completed services, and the partner’s commission—without counting the same money twice.
The difficult part is not adding more joins. It is building an explainable chain from service delivery back to the original sale.
This article develops that model using a membership-system scenario. Monetary examples are illustrative, and the proposed ledger and rule-versioning features are design recommendations rather than claims about an already deployed implementation.
Define the Settlement Policy Before Writing the Query
In this article, confirmed value means the business amount attributed to valid service fulfillment under an agreed settlement policy. It is the commission basis in the examples.
That operational measure should align with the organization’s accounting requirements, but it is not automatically identical to financial-statement revenue.
For example, IFRS 15 connects revenue recognition to satisfying performance obligations, rather than simply receiving payment or creating an appointment. Depending on the obligation, recognition may occur at a point in time or over time. IFRS 15 overview
Before implementation, agree on:
- Which event establishes valid service fulfillment.
- Which amount forms the allocation basis.
- How cancellations, refunds, and price adjustments affect that basis.
- Whether commissions depend on sales, cash receipts, or fulfillment.
The examples below assume fulfillment-based commissions. That is a chosen business policy, not a universal rule for membership products.
An appointment booking is therefore insufficient on its own. The calculation needs an accepted fulfillment event, such as a completed session with valid redemption evidence.
Separate Four Kinds of Facts
A single order status cannot describe the entire lifecycle.
| Fact | Question it answers | Typical information |
|---|---|---|
| Sales order | Who sold what, and which partner owns the sale? | Seller, product, order amount |
| Payment and refund | What money actually entered or left the business? | Payment records, refund records, completion times |
| Entitlement and funding source | What can the member use, and where did it come from? | Entitlement details, source orders, balances or units |
| Fulfillment and consumption | What was delivered, and which entitlements were consumed? | Fulfillment events, consumption lines, effective times |
In the source project, partner sales are identified by:
archiver_order.seller_type = 3
archiver_order.seller_id -> business_partner.idThese fields establish sales attribution. They do not establish that the entire order value has been earned through service delivery.
A direct course order may lead straight to entitlement consumption. A stored-value card introduces another step: balance is transferred into a later purchase before the resulting service is delivered.
That internal transfer must remain distinguishable from a new external payment.
Follow One Stored-Value Card Through Its Lifecycle
Assume a member buys a card through a partner and then purchases a course package.
| Date | Event | Amount or quantity |
|---|---|---|
| June 30 | Stored-value card payment completes | 1,000 |
| July 2 | Card balance buys a five-session package | 500 |
| July 5 | First session is completed and redeemed | One session |
| July 12 | Second session is completed and redeemed | One session |
| July 20 | Three unused sessions are returned to card balance | 300 |
All amounts use the same currency. Assume no discounts, bonus credits, or tax-basis differences, and a confirmed value of 100 per session.
The external cash receipt is 1,000 in June.
The package purchase in July transfers 500 from card balance to course entitlements. It does not generate another 500 of external cash or establish that five sessions have been delivered.
The two completed sessions produce a confirmed value of 200.
Returning the unused sessions restores 300 to the card. It does not automatically reverse the two completed sessions. The remaining card balance becomes 800.
If the applicable commission rate is 10%:
Confirmed value = 100 + 100 = 200
Partner commission = 200 × 10% = 20Under these simplified assumptions, the original amount also reconciles:
Original receipt: 1,000
= Attributed fulfilled value: 200
+ Remaining stored value: 800
The solid arrows show the business sequence. The dashed arrow shows attribution back to the original sale, not a cash refund. The later return of unused sessions is omitted from the diagram.
Bonus credits, discounts, fees, and external refunds require additional components. The simplified reconciliation should not be applied unchanged to those cases.
Trace Fulfillment Back to Its Source
The evidence for an allocation should begin with an actual fulfillment or consumption event:
Follow each funding source separately. Allocate the service value using the agreed policy, preserve the total, and apply the appropriate historical commission rule.
A sales-order page can query forward from the sale to related activity. The amount still needs supporting evidence from the fulfillment end of the chain.
For a direct order, the original sales order and the entitlement order may be the same. For a stored-value purchase, they usually differ.
Transfers, package exchanges, and entitlement conversions may add intermediate steps. Preserve those relationships rather than guessing the source from a member ID, similar timestamp, or matching product category.
If the source cannot be established, flag the allocation for investigation. Defaulting to the member’s most recent order produces plausible numbers without reliable evidence.
Mixed Payments Make Attribution Many-to-Many
Consider a course package funded by:
Stored-value card A: 300
Stored-value card B: 150
External payment: 50
Total: 500If the agreed policy allocates proportionally by funding contribution, a fulfilled service with a basis of 100 is attributed as follows:
| Funding source | Share | Attributed value |
|---|---|---|
| Card A | 60% | 60 |
| Card B | 30% | 30 |
| External payment | 10% | 10 |
Each source follows its own sales attribution. Only the portion belonging to a particular partner enters that partner’s commission basis.
Proportional allocation is an explicit assumption here. A policy that specifies funding priority or particular entitlements may require a different method.
The data model therefore needs more than one source order ID attached to an appointment. Useful detail fields include:
source_order_id
entitlement_order_id
source_payment_or_transfer_id
benefit_detail_id
consumption_detail_id
fulfillment_id
allocated_amountIf the same card receives multiple top-ups from different sales sources, keep funding-lot details. An undifferentiated balance cannot reliably explain how much fulfilled value belongs to each source.
Make Allocation Deterministic and Reconciliable
First define the amount being allocated.
List price, order total, external payment, entitlement face value, and fulfillment basis may differ. A promotion that provides 1,200 of spending credit for a 1,000 payment does not by itself establish 1,200 of externally funded revenue.
For an event whose allocation basis has been established:
Sum of source allocations = Event allocation basisSource-level limits should use the same valuation policy and include adjustments. Check cumulative allocations, not just the current report window. Otherwise, separate monthly calculations may each assume that the original full capacity remains available.
Use integer minor units or decimal arithmetic such as Java’s BigDecimal. Define the scale and rounding method for intermediate division and final amounts.
For example:
100.00 split across three equal shares
= 33.33 + 33.33 + 33.34Assign the residual using a stable ordering so that replaying the same facts produces the same result.
An exceeded limit should generate an explainable exception. Silently capping the result can conceal duplicated relationships, missing adjustments, or incorrect source data.
Divide Database and Service Responsibilities by Data Grain
UNION ALL is not inherently unsuitable for this problem. The important questions are whether its branches return the same kind of fact and whether they overlap.
Combining order-level amounts with appointment-level amounts and then summing by order can multiply value even when the SQL is syntactically correct.
A practical division of responsibilities is:
| Responsibility | Suitable location |
|---|---|
| Tenant, seller, and order-range filtering | Database |
| Batch retrieval of payments, entitlements, and consumption | Database |
| Well-defined joins and set aggregation | Database |
| Complex source traversal and policy-driven allocation | Service layer |
| Durable entries, transactions, and uniqueness enforcement | Database and service together |
Java is not automatically better at deduplication or aggregation. Its advantage is expressing complex rules, preserving calculation explanations, and handling exceptions.
Likewise, an in-memory map only manages the current calculation. It does not provide durable protection against retries or concurrent posting.
Organize Facts Before Calculating Totals
For a page filtered and sorted only by sales-order attributes, the service can follow this sequence:
- Fetch a candidate sales-order page.
- Batch-load payments and refunds.
- Resolve entitlement and funding relationships.
- Batch-load fulfillment and consumption facts.
- Allocate value to sources.
- Select applicable historical commission rules.
- Assemble totals and detail links.
Illustrative Java orchestration might look like this:
OrderPage page = querySourceOrders(orderFilter);
TraceContext traces = loadTraceContext(page.orderIds());
List<ConsumptionFact> facts =
loadConsumptionFacts(traces, reportingWindow);
AllocationResult allocation =
allocationService.allocate(facts, traces, policyVersion);
CommissionResult commission =
commissionService.calculate(allocation, ruleSnapshots);
return assembleOrderPage(page, allocation, commission);These names describe responsibilities, not a drop-in implementation.
AllocationResult should include detail lines, policy versions, unallocated amounts, and exception reasons—not only a total.
This workflow also has an important boundary: it works when financial calculations do not determine which records belong on the page. Amount-based filtering and sorting need a different pagination strategy.
Give Each Time Field One Meaning
An order can legitimately belong to different periods for different purposes.
| Field | Meaning |
|---|---|
| paidAt | External payment completion |
| fulfilledAt | Actual accepted service fulfillment |
| refundCompletedAt | Completed refund |
| recordedAt | When the fact entered the system |
| postedAt | Period attribution under the posting policy |
Booking creation time, scheduled appointment time, and actual completion time should not be interchangeable.
“June sales and their July fulfillment” applies both a sales cohort and a fulfillment window.
“All partner fulfillment in July” must also include orders sold before July. Automatically restricting it to July payments would omit valid activity.
These are different reports:
- A sales analysis starts with sales orders.
- A period settlement starts with fulfillment or posting facts.
Use consistent time zones and half-open ranges such as [start, end) to avoid overlapping period boundaries.
Late entry requires additional care. A session completed on July 5 and recorded on July 10 has two meaningful dates. If the period has closed, the posting policy must determine how to handle the late fact.
Separate Refunds from Fulfillment Adjustments
A refunded order status does not explain what happened economically.
| Event | Facts to record |
|---|---|
| Unused sessions returned to card balance | Entitlement reversal and balance restoration |
| Unused sessions refunded externally | Entitlement reversal and external refund |
| Incorrect redemption cancelled | Reversal of the related confirmation |
| Price concession for a delivered service | Policy-specific adjustment to the related value |
| Full order refund after some fulfillment | Refund reason and fulfillment history reviewed separately |
Do not subtract every refund from the current period’s confirmed value.
Preserve established entries and associate adjustments with their originals:
Original confirmation: +100.00
Fulfillment reversal: -100.00Whether an adjustment affects the original period or a current adjustment period depends on the organization’s close and adjustment policy. Retain both the original business date and the adjustment posting date.
Display rules are separate again. A commercial sales list may hide some inactive orders. An audit view may still need a zero-net order to explain its original entries and reversals.
A zero balance does not mean nothing happened.
Deduplicate Events, Not Similar-Looking Rows
The combination of source order, entitlement order, and appointment ID is not necessarily unique.
One appointment may consume multiple entitlements. Each entitlement may have several funding sources. Deduplicating solely on those three IDs can delete legitimate allocations.
Start with the smallest meaningful posting unit, such as:
Consumption detail
× Source allocation detail
× Entry typeUse identifiers for that unit to define an idempotency key. Reversal entries also need the original entry reference and a distinct adjustment-event identity so that legitimate partial reversals remain possible.
Retries must not create another posting for the same event.
For persisted calculations, combine database uniqueness with transaction handling. If concurrent operations draw from shared allocation capacity, protect that capacity as well; deduplication alone does not prevent over-allocation.
A policy upgrade is not a reason to post the same event again under a different version. Handle recalculation through an explicit replacement or adjustment process.
Preserve the Commission Rule Used
Suppose the commission rate changes from 10% to 12%. Should a historical query produce a different result?
That depends on the agreement. The rule may be selected by sale date, fulfillment date, settlement period, product, location, or partner.
The current configuration value is insufficient evidence.
Alongside the calculation, preserve a snapshot such as:
commission_rule_id
commission_rule_version
commission_rate
commission_base
commission_amount
rounding_policy
settlement_periodDefine rule precedence and reject ambiguous matches.
Also decide whether to round each detail commission before aggregation or calculate commission after aggregating the basis. These approaches can differ by minor units.
Reversals should reference the original commission and its policy. Applying the latest rate to an old reversal can leave a residual that has no business justification.
Apply Eligibility Before Pagination
A common implementation fetches twenty sales orders, calculates confirmed value, and removes orders whose result is zero.
If fifteen rows are removed, the page contains only five results even though later records may qualify. The total count is wrong as well.
Sorting only the current page by confirmed value has the same problem: it is not a global sort.
| Requirement | Appropriate strategy |
|---|---|
| Sales-field filtering; financial values are display-only | Page first, then calculate in batches |
| Only orders with qualifying consumption | Apply eligibility before pagination |
| Filter or sort by net confirmed value | Page over aggregation results or a summary table |
| Full export or period settlement | Dedicated aggregation or batch processing |
An existence check can establish that a qualifying detail exists. It cannot establish a positive net amount after reversals without considering those adjustments.
Use a deterministic sales sort, such as payment time followed by order ID, to resolve ties consistently.
Evolve Toward a Ledger and Reporting Model
Real-time traversal is useful while rules are being established and for limited detail queries.
As lists, totals, exports, and monthly settlement repeatedly reconstruct the same history, consider a durable confirmation ledger:
Proposed architecture: preserve the detail behind every total and reconcile using the same scope, policy version, and data cutoff.
Every summary should trace to entries, and every entry should trace to source facts.
For reporting consistency, share both policy versions and a data cutoff. Two queries may use identical logic but still disagree if new redemptions arrive between them.
Record late facts, adjustment batches, and their affected periods. This explains why a historical report changed rather than leaving users to assume the calculation is unstable.
Avoid loading all history into memory through an unlimited page size. Use dedicated aggregation, bounded batches, or maintained summaries appropriate to the workload.
Test the Business Invariants
The important tests verify relationships and conservation of value, not merely successful HTTP responses.
| Scenario | Expected property |
|---|---|
| Multiple sessions against one order | No repeated addition of the full order price |
| Package purchased but not delivered | Internal transfer does not establish fulfillment |
| Mixed-source payment | Source allocations sum to the event basis |
| Sale and fulfillment in different months | Each fact uses its correct reporting window |
| Unused entitlement returned | Completed service is not automatically reversed |
| Redemption reversal | Adjustment references the original entry |
| Duplicate delivery and concurrent processing | No duplicate posting or excess allocation |
| Commission rate changes | Historical selection remains reproducible |
| Amount-based pagination | Rows, totals, and ordering agree |
| Missing source or cyclic relationship | Explicit exception instead of guessed attribution |
| Rounding residuals | Deterministic replay and preserved totals |
Reconcile detail totals to order summaries and order summaries to partner totals using the same scope, policy version, and cutoff.
Monitor unresolved sources, insufficient capacity, conflicting rules, and cross-tenant relationships. These are operational exceptions that need ownership, not just debug log entries.
Make Every Displayed Amount Explainable
An API returning one vaguely named actualAmount creates avoidable ambiguity.
Distinguish order amount, external receipts, external refunds, period confirmed value, lifetime confirmed value, and period commission. Include calculation status and data cutoff where relevant.
Then provide a detail path:
Confirmed value: 200
-> July 5 session: 100
-> July 12 session: 100Each line should expose the consumed entitlement, funding source, applied rule, and subsequent adjustments.
In the source project, the relevant integration points include FncBusinessPartnerService, FncBusinessPartnerMonthServiceImpl, ArchiverOrderMapper.xml, and fnc_store_commission_rule. These identify where queries and calculations connect; they do not establish that every ledger feature proposed here already exists.
The goal is a reliable evidence chain from the number on the screen to the allocation, from the allocation to consumption, and from consumption to fulfillment and the original sale.
That chain lets the system answer the questions that matter: where the value came from, why it belongs in this period, and why this partner receives the commission.
Related reading: SQL JOIN double counting and accurate totals | MySQL and ClickHouse consistency recovery