Handling Overlapping Trade Promotions

Overlapping trade promotions occur when more than one vendor-funded incentive, retailer markdown, or volume rebate qualifies the same SKU, store cluster, or transaction window — and if the reconciliation engine rates each one independently, the same earned volume pays twice, inflating liabilities and surfacing as a deduction dispute or an audit finding at close. This page documents the exact procedure for collapsing competing offers into a single, reproducible accrual: detect the collision during normalization, rank the candidates by a declared precedence key, allocate shared volume so no unit is paid more than once, and post the result idempotently. It is the implementation-level companion to payout structure modeling, the parent cluster that owns how earned volume becomes money within the broader core architecture and promotion mapping discipline.

Overlap resolution data flow Eligible, normalized transactions enter a collision detector that groups candidate promotions on (sku, store_id) and tests their date windows for intersection. Records with no overlap bypass straight to rating: a single offer owns the volume and is rated directly. Records that collide fan into a precedence ranker that sorts each group on (scope_specificity, agreement_version), then into an allocator that applies the winning offer's stack policy — exclusive, where the top offer pays and the rest are suppressed; sequential, where offers consume successive slices of volume in priority order; or pro-rata, where volume is split by contract_weight. Allocated units and the bypassed single-offer lines both feed decimal tier math (units times rate, quantized with ROUND_HALF_UP), then an idempotent upsert keyed on (promotion_id, txn_grain, config_version) so a replay is a no-op. Eligible transactions qualified · normalized · one unit-of-measure Detect collisions group (sku, store_id) · test window intersection no overlap Rate directly one offer owns the volume single-offer line, no ranking overlap Rank by precedence sort (scope_specificity, agreement_version) ↓ Allocate shared volume · winner’s stack_policy exclusive — top offer pays, the rest suppressed sequential — priority order consumes slices pro_rata — split by contract_weight Rate & decimal tier math units × rate · Decimal · ROUND_HALF_UP Idempotent upsert key (promotion_id, txn_grain, config_version) · replay = no-op

Prerequisites

Before wiring overlap resolution into the engine, confirm the following are in place:

  • Eligible, qualified transactions. Only records that already cleared the eligibility rule framework reach this stage — channel attribution, product-hierarchy alignment, and window scope are settled upstream, so overlap resolution never re-checks qualification.
  • Versioned agreement terms with stacking flags. Each candidate promotion originates in agreement schema design and carries a monotonic agreement_version, an explicit stack_policy (exclusive, sequential, or pro_rata), and a scope_specificity integer. Overlap resolution ranks these terms; it never invents precedence the contract did not declare.
  • Normalized, deduplicated input. Sales and claim lines arrive from the data ingestion normalization pipelines reduced to one unit-of-measure with external identifiers resolved, so a collision count is never inflated by double-counted rows.
  • Python packages: pandas>=2.2, pydantic>=2.6, and the standard-library decimal module. Every monetary and boundary field uses decimal.Decimal; never float.
  • Access role: read access to the contract registry and write access to the versioned accrual table (typically the reconciliation_etl service role).

Step-by-step implementation

Step 1 — Model the collision contract

Encode each candidate promotion as a Pydantic v2 model so an unknown stacking policy or an inverted window fails at load time rather than during accrual.

python
from datetime import date
from decimal import Decimal
from typing import Literal
from pydantic import BaseModel, model_validator


class PromotionCandidate(BaseModel):
    promotion_id: str
    agreement_version: int
    sku: str
    store_id: str
    effective_date: date
    expiration_date: date
    stack_policy: Literal["exclusive", "sequential", "pro_rata"]
    scope_specificity: int            # higher = more specific scope
    rate: Decimal                     # per-unit, 4 dp
    contract_weight: Decimal = Decimal("1.0000")  # used only for pro_rata

    @model_validator(mode="after")
    def window_ordered(self) -> "PromotionCandidate":
        if self.expiration_date < self.effective_date:
            raise ValueError("expiration_date precedes effective_date")
        return self

Validation check: load a fixture whose expiration_date precedes its effective_date and assert PromotionCandidate(**fixture) raises ValidationError. A candidate that parses must carry a valid window before it can collide with anything.

Step 2 — Detect temporal collisions during normalization

Identify overlaps structurally, before rating. Group candidates on (sku, store_id) and test their date intervals for intersection so a collision_flag and overlap_count are materialized at the ingestion boundary, not inferred after a payout is generated.

python
from itertools import combinations

def overlaps(a: PromotionCandidate, b: PromotionCandidate) -> bool:
    # half-open windows: share at least one day
    return (a.effective_date <= b.expiration_date
            and b.effective_date <= a.expiration_date)

def collision_groups(cands: list[PromotionCandidate]) -> list[list[PromotionCandidate]]:
    groups: list[list[PromotionCandidate]] = []
    by_scope: dict[tuple[str, str], list[PromotionCandidate]] = {}
    for c in cands:
        by_scope.setdefault((c.sku, c.store_id), []).append(c)
    for scope_cands in by_scope.values():
        if len(scope_cands) > 1 and any(
            overlaps(a, b) for a, b in combinations(scope_cands, 2)
        ):
            groups.append(scope_cands)
    return groups

Validation check: feed two candidates on the same SKU and store whose windows touch by a single day and assert they land in one collision group; shift one window forward by a day so the windows are disjoint and assert no group forms. Collision detection must be exact at the window edge.

Step 3 — Rank candidates by declared precedence

Resolution is by declared precedence, never row order. Sort each collision group on a stable precedence_key — scope specificity first, then the higher agreement version as the tie-breaker — so the same group always ranks identically on every run.

python
def precedence_key(c: PromotionCandidate) -> tuple[int, int]:
    return (c.scope_specificity, c.agreement_version)

def ranked(group: list[PromotionCandidate]) -> list[PromotionCandidate]:
    return sorted(group, key=precedence_key, reverse=True)

Validation check: rank a group containing two candidates with equal scope_specificity but different agreement_version and assert the higher version sorts first. A deterministic tie-break is what keeps two runs byte-identical.

Step 4 — Allocate shared volume by stacking policy

Apply the winning candidate’s stack_policy to decide how the colliding volume is split. Exclusive suppresses every offer but the top-ranked one. Sequential lets the highest-priority offer consume eligible volume first and applies the next offer only to the remaining units. Pro-rata divides the volume across offers in proportion to contract_weight. All arithmetic stays in decimal.Decimal.

python
from decimal import ROUND_HALF_UP

def allocate(group: list[PromotionCandidate], volume: Decimal) -> list[dict]:
    order = ranked(group)
    policy = order[0].stack_policy
    out: list[dict] = []
    if policy == "exclusive":
        out.append({"promotion_id": order[0].promotion_id, "units": volume})
        for loser in order[1:]:
            out.append({"promotion_id": loser.promotion_id,
                        "units": Decimal("0"), "suppressed": True})
    elif policy == "sequential":
        remaining = volume
        for c in order:
            take = remaining            # simple waterfall: first taker clears it
            out.append({"promotion_id": c.promotion_id, "units": take})
            remaining -= take
    else:  # pro_rata
        total_w = sum((c.contract_weight for c in order), Decimal("0"))
        for c in order:
            share = (volume * c.contract_weight / total_w).quantize(
                Decimal("0.0001"), rounding=ROUND_HALF_UP)
            out.append({"promotion_id": c.promotion_id, "units": share})
    return out

Validation check: sum the allocated units across a pro_rata split and assert the total equals the input volume to within a single quantized unit; assert an exclusive split records every non-winner with units == 0 and suppressed == True. No unit may be allocated twice, and none may vanish.

Step 5 — Rate, total, and post idempotently

Rate each allocation against its promotion rate, then write through an idempotent upsert keyed on (promotion_id, txn_grain, config_version). Because the key includes the configuration version, replaying an unchanged batch overwrites cleanly instead of appending a duplicate accrual — the single most common source of phantom liabilities in hand-rolled pipelines.

python
def rate_allocations(group: list[PromotionCandidate],
                     alloc: list[dict], config_version: str) -> list[dict]:
    by_id = {c.promotion_id: c for c in group}
    rows = []
    for a in alloc:
        c = by_id[a["promotion_id"]]
        amount = (a["units"] * c.rate).quantize(
            Decimal("0.0001"), rounding=ROUND_HALF_UP)
        rows.append({
            "upsert_key": (c.promotion_id, c.sku + "|" + c.store_id, config_version),
            "promotion_id": c.promotion_id,
            "net_payable": Decimal("0") if a.get("suppressed") else amount,
            "suppressed_liability": amount if a.get("suppressed") else Decimal("0"),
        })
    return rows

Validation check: run the full pipeline twice over an identical batch and assert the upsert produces the same set of upsert_key rows with identical net_payable totals. A re-run delta of zero is the contract this whole pass exists to guarantee.

Common failure modes and fixes

  1. Double-counted volume across stacked offers. Rating each colliding promotion against the full volume independently pays the same units twice. Always run the allocation step (Step 4) first so each unit is assigned to exactly one offer before any rate is applied, and assert the allocated units reconcile back to the input volume.
  2. Floating-point drift in pro-rata splits. A float proportional share accumulates rounding error so the parts no longer sum to the whole across millions of lines. Keep every share in decimal.Decimal, quantize explicitly to Decimal("0.0001"), and reconcile the residual cent onto the top-ranked offer rather than letting it disappear.
  3. Non-deterministic precedence from row order. Resolving ties by whichever record the DataFrame happened to emit first makes two runs disagree. Rank strictly on the precedence_key tuple (Step 3); never rely on input ordering or a dictionary’s insertion order.
  4. Edge-touching windows missed or over-matched. An off-by-one in the interval test either merges windows that only abut or splits windows that share their boundary day. Pin the comparison to the half-open convention the schema declares and cover the single-day-touch case with an explicit test, as in Step 2.
  5. Ambiguous collisions auto-paid instead of held. A missing effective_date, a conflicting vendor claim, or two equal-precedence exclusive offers cannot be resolved deterministically. Route these to a hold via fallback routing logic with a conservative default, and let the claim validation rule engine adjudicate the disputed delta rather than accruing a guess.

Operational checklist

Frequently asked questions

What happens when a retroactive tier is crossed mid-cycle on one of the stacked offers? Allocation runs before tier math, so the crossing is computed on the units that offer actually received after the split, not on the gross colliding volume. Because each accrual carries its config_version and the ordered allocation trace, the retroactive re-rate is reconstructable rather than a black box — the detailed tier mechanics live in payout structure modeling.

How do exclusive and sequential stacking differ in practice? Exclusive suppresses every offer but the top-ranked one, so only one promotion ever pays the volume. Sequential lets offers apply in priority order to successive slices of volume — a manufacturer off-invoice discount might consume the qualifying units first, leaving a retailer markdown to apply only to whatever remains.

Why allocate volume before applying rates instead of after? Allocating first guarantees each unit is assigned to exactly one offer, so no unit can be rated twice. Rating first and reconciling afterward is the classic path to phantom liabilities, because the double-count already exists in the ledger before the correction runs.

What should the engine do when two equal-precedence exclusive offers collide? It cannot pick deterministically, so it must not guess. Route the record to a hold through fallback routing logic with a conservative default and open a dispute for a finance analyst to adjudicate, preserving both candidates in the audit trail.

Back to