trade-promotion-management

How it is built

Architecture overview

Trade promotion management (TPM) is the software a consumer-goods manufacturer uses to plan, fund, execute and settle the discounts it gives retailers. A promotion is an agreement such as "feature this product in the weekly flyer for three weeks, with a lower shelf price, in return for an off-invoice allowance and a lump-sum fee". The system holds that agreement as a structured deal, predicts what it will do to sales, promises money for it (an accrual), pays it out later (as a credit note, a bill-back or by short-paying invoices, a deduction) and finally compares what the money bought with what a no-promotion world would have sold (post-event ROI). Pharma gross-to-net rebate management is a variant of the same loop with contracts and rebates instead of flyers and allowances.

Three things make the system hard. The plan is a hierarchy (customer, account, product group, SKU, week) and people edit it at any level, so a number typed at a total must flow down to the leaves without breaking the totals. Money is owed in the future on rules that change retroactively, so every amount needs valid time as well as record time. And the retailer's side of the story arrives as messy documents: short-payments with cryptic reason codes that have to be matched back to deals, sometimes months later.

Parts:

• Baseline and demand forecast — expected sales without promotion, per product, customer and week — Time-series forecasting (baselines, model families, backtests), built on Exponential smoothing (ETS, Holt-Winters), STL decomposition (seasonal-trend with loess) and Time-series cross-validation (rolling origin); deep-forecasting-models (no page yet) for many related series; Gradient-boosted decision trees (GBDT) for feature-rich tabular forecasts
• Uplift and ROI model — what a promotion adds on top of the baseline, and what it cost — Linear and logistic regression and Gradient-boosted decision trees (GBDT) for the response, propensity-score-matching (no page yet) for effects from observational data
• Promotion optimiser — picks which events to run within a budget and capacity — Simplex method and Assignment problem (Hungarian / Jonker–Volgenant)
• Planning cube — hierarchical plan with edit-anywhere, write-down and roll-up — closure-table (no page yet) for the hierarchies, olap-cube-write-back (no page yet) for edits at aggregate cells, Rollup aggregation for the totals, largest-remainder-rounding (no page yet) to keep integer splits exact
• Deal rules and accruals — which deal applies to which sale, how much to accrue — Rete rules engine, bitemporal-data (no page yet), Money representation, Double-entry ledger
• Workflow and approvals — drives a promotion from draft to settled — DAG workflow execution and Durable workflow execution, with dual-control-approval (no page yet), built on Durable job queue
• Retailer data and settlement — takes in shipments, scan data and deductions and matches them to deals — x12-edi-messaging (no page yet), record-linkage (no page yet), Reconciliation, Coverage-guided fuzzing of the file parsers
• Access control — which user sees which customer — Row-level security (multi-tenant isolation in the data layer), Mutual TLS for service and retailer-facing connections
• Operations — keeps planning and settlement running, fast, correct and recoverable — hdr-histogram (no page yet), Process supervision, Graceful draining, Primary-backup replication, Point-in-time recovery, Deterministic simulation testing

 retailer POS/scan data ─┐                       ┌─► optimiser (LP / assignment)
 shipments (EDI 852/867)─┼─► baseline forecast ──┤
 history of events ──────┘      │   ▲             └─► uplift model ──► predicted volume, cost, ROI
                                ▼   │ actuals                              │
 planner ──► planning cube (hierarchy, write-down, roll-up) ◄───────────────┘
                 │ approved plan
                 ▼
          deal rules (Rete) ──► accrual postings ──► ledger (double entry)
                 ▲                                        │
 invoices, credit notes, deductions (EDI 810/812/820) ──► matching ──► reconcile ──► settle or dispute
                 └──── workflow + approvals drive every step; row-level security wraps all reads ────┘

The system has two clocks. The planning clock is interactive: a changed discount should give a new forecast, cost and ROI within seconds. The settlement clock is slow batch: deductions arrive for weeks after an event ends, and each must find its deal.

How the parts fit together

Baseline and demand forecast

A promotion's uplift is measured against a baseline, so the baseline comes first. Each series (a product at a customer, per week) is forecast by the recipe in Time-series forecasting (baselines, model families, backtests): fix the horizon and the uncertainty, beat naive baselines, pick a model family and validate with rolling-origin backtests. Weekly retail sales have trend, a yearly season and holiday spikes. Exponential smoothing (ETS, Holt-Winters) keeps a level, trend and seasonal state per series, one pass and O(1) memory, which matters when there are millions of series; STL decomposition (seasonal-trend with loess) separates trend and seasonality with iterated loess so that past promotion weeks can be cleaned out of the history before the baseline is fitted. Promoted weeks must not be learned as normal demand, or the baseline absorbs the uplift it is compared against.

Validation uses Time-series cross-validation (rolling origin): train on the past, test on the next h weeks, move the origin forward. Intermittent items that sell zero most weeks need a specialist such as Croston's method; ARIMA is the classical alternative.

When there are many related series, a global model beats one model per series. Neural forecasters such as DeepAR (Salinas et al. 2017), N-BEATS (Oreshkin et al. 2019) and the Temporal Fusion Transformer (Lim et al. 2019) share parameters across series and emit quantiles, so the plan can show a range instead of a point. That is deep-forecasting-models (no page yet); the cheaper family above is the alternative, chosen per series group on backtest error. Gradient-boosted decision trees (GBDT) fit the same problem as a tabular regression on lag, calendar, price and promotion features, and are strong when many external drivers exist.

The uplift model receives a baseline per cell plus its error distribution; actual shipments and scan data flow back as new history after every cycle.

Uplift and ROI model

For a planned event the system predicts incremental volume as a function of discount depth, mechanic (price cut, display, feature, multi-buy), duration and calendar. The simplest version is a Linear and logistic regression on the log of volume with promotion features and customer-level effects; it is explainable to a sales team, which matters because planners override numbers they do not trust. Gradient-boosted decision trees (GBDT) capture interactions (a deep discount on a feature week is worth more than the sum of the two) at the cost of explainability.

Two effects must be modelled or ROI is overstated: pull-forward (the weeks after the event dip) and cannibalisation (a promoted pack takes volume from siblings).

Historical promotions are not randomised: retailers promote products that were selling well anyway. propensity-score-matching (no page yet) estimates how likely each product-week was to be promoted given its characteristics and then compares promoted with similar un-promoted weeks, or reweights by the inverse propensity; the propensity score is due to Rosenbaum and Rubin (1983). It is the tool to check whether the uplift in the data is causal or just selection.

ROI per event is incremental margin minus promotion cost (allowance times units, fees, display), divided by the cost.

Promotion optimiser

Given a budget, a calendar and a list of candidate events, the optimiser answers "which events, which weeks, which depths". When the response is approximated as linear in the decision variables, the problem is a linear program solved by Simplex method: maximise predicted margin subject to total spend, per-customer limits, minimum and maximum events per week and no overlapping events on one SKU. Where each slot takes exactly one event from a candidate list, the structure is a matching and Assignment problem (Hungarian / Jonker–Volgenant) solves it in O(n³). Nonlinear response curves are approximated piecewise-linearly, which keeps the problem an LP. The system must show the constraint that binds, because that is what planners negotiate.

Planning cube

The plan lives in a multi-dimensional structure: customer hierarchy (country, banner, store group), product hierarchy (category, brand, SKU) and time (year, month, week). closure-table (no page yet) stores each hierarchy relationally with one row per ancestor-descendant pair, so "all SKUs under this brand" is a single indexed lookup, and moving a branch is a bounded update. Rollup aggregation keeps partial sums per bucket and merges them upward, so a quarterly total is a handful of reads and an edit changes only the buckets on its path.

The distinctive operation is write-back at an aggregate cell: the planner types a new total for a brand and quarter, and olap-cube-write-back (no page yet) spreads the change to the leaf cells proportionally to a driver (last year's volume, the baseline), evenly or by a rule, while locked cells stay fixed. Spreading produces fractions, and plans in units or cents must stay integers that add up exactly: largest-remainder-rounding (no page yet) gives each part its floor and hands the leftover units to the parts with the largest remainders, so the leaves sum to the typed total.

Deal rules and accruals

A deal is a set of conditions and payments: products and customers it covers, the dates it is valid, the mechanic (off-invoice per case, scan-based per unit sold at the register, bill-back, lump sum), tiers and caps. Each shipment or sale must be tested against all active deals to decide which accrue and how much. A Rete rules engine compiles the rules into a network that caches partial matches, so a new sale is matched against thousands of deals by touching only the nodes it can affect, rather than rescanning every rule.

Because rules and facts both change retroactively (a deal is extended after the event, a price is corrected, a late scan file arrives), every record needs two time axes: when it was true in the business (valid time) and when the system learned it (transaction time). bitemporal-data (no page yet) keeps both, which makes "what did we think we owed at quarter end, and what do we owe now" two queries on the same table and keeps audit trails honest.

Amounts are stored as integer minor units with an explicit currency, rounded once with a named mode (Money representation); per-unit rates multiplied by large quantities are computed in wider integers. Every accrual becomes postings in a Double-entry ledger: debit promotion expense, credit accrued liability, with the deal id on the posting. Payment and release reverse the liability. The ledger is append-only, so corrections are new postings, never edits. Balances (accrued, paid, remaining per deal) are derived from it.

Workflow and approvals

A promotion moves through states: draft, submitted, approved, agreed with the retailer, executing, ended, claims open, settled. Some steps are a dependency graph, such as "forecast and ROI must exist before approval, approval before the deal is published to the retailer portal". DAG workflow execution releases each task when its inputs are done and skips tasks whose inputs failed. Steps that wait on people and external parties for weeks (the retailer's confirmation, a claim window of 60 or 90 days) are long-lived processes that must survive deploys and crashes; Durable workflow execution logs each side-effect result and replays the code from the log to resume. Both sit on a Durable job queue for leased, retried work. The DAG engine suits batch pipelines (nightly forecast, accrual run), the durable workflow the per-promotion life cycle.

dual-control-approval (no page yet) (maker-checker) protects the sensitive steps: the person who creates or changes a deal above a threshold cannot approve it, and large settlements need a second approver with a quorum policy.

Retailer data and settlement

Trade data arrives mostly as EDI. x12-edi-messaging (no page yet) parses ANSI X12 interchanges: segment and element delimiters, the ISA/GS/ST envelopes, control numbers for duplicate and gap detection, and the functional acknowledgements (997) that tell the sender a file was received. Typical document types in this domain are invoice (810), credit/debit adjustment (812), payment order and remittance advice (820), product activity data (852) and product transfer and resale report (867).

The core settlement problem is deduction matching. The retailer short-pays an invoice by a round amount with a reason code and a reference to its own promotion number. The manufacturer must find the deal, accept or dispute the claim and clear the receivable. Reference numbers are often missing or mistyped, so record-linkage (no page yet) scores candidate matches on retailer, product, date window and amount, with blocking to keep the candidate set small, and routes low-confidence matches to a person. Reconciliation then matches payments and deductions against ledger postings by reference, then amount and date window; every unmatched or mismatched item becomes an exception in a work queue with an owner and an age. Valid claims release the accrual, invalid ones become disputes with evidence from the deal master (what was agreed, when).

The parsers for retailer files are the system's exposed input surface: an EDI interchange or a portal export is written by someone outside the company, with their own quirks, truncations and encodings. Coverage-guided fuzzing targets exactly these readers (the X12 envelope and segment parser and the delimited-file importers). It mutates real sample files, runs the instrumented parser, and keeps every input that reaches a new code edge, so malformed control numbers, missing trailers and oversized fields are found before a retailer sends one. A crash or a silently half-read file here would otherwise surface as a missing or double-counted deduction.

Access control

A key account manager should see only their customers, and one customer's terms must never leak to another. Row-level security (multi-tenant isolation in the data layer) appends a policy predicate to every query in the database, so a forgotten WHERE customer_group = ? in application code cannot expose rows.

Row-level security answers which rows a user sees; it says nothing about which machines may talk to the system at all. Mutual TLS covers that side: the portal that receives retailer files, the EDI gateway, the forecast and rule-engine workers and the ledger database each present a certificate, so every internal call and every retailer connection carries a verified, rotatable machine identity. A retailer's certificate maps to one customer group, which sets the database session's customer scope before row-level security applies, so a compromised partner connection can only ever reach that partner's rows.

Operations

Responsiveness. hdr-histogram (no page yet) records the latency of the key interactive flow: a planner edits a total and the write-down, roll-up and ROI recomputation come back. A log-linear histogram keeps bounded relative error across milliseconds to seconds, so p50 and p99 of cube edits are watched per hierarchy depth and per customer size. This is how the "cube edits bite first" limit under Scaling is seen before planners complain, and it is the number the partial-aggregate and batching changes are judged against.

Keeping workers alive. The forecast batch, the rule engine, the EDI ingest and the workflow workers run as long-lived processes under process supervision: a parent starts them, reaps exits and restarts them under a delay and burst limit, so a crashing ingest worker does not loop on the same bad file and flood the queue. On a deploy it stops them with SIGTERM before SIGKILL. Graceful draining makes that stop safe: a worker stops taking new jobs and new approvals, finishes the posting or write-back it holds until a deadline, and hands unfinished work back to the durable queue, so a release never leaves a half-posted accrual.

Ledger availability and recovery. The ledger and deal store are the system of record, so they are held by primary-backup replication: one node orders all writes and ships them to a standby, and the acknowledgement rule decides what a crash may lose. Postings are acknowledged only once the standby has them. Replication does not protect against a wrong write, though, since a bad bulk edit is copied faithfully. Point-in-time recovery does: base backups plus an archived change log rebuild the data as of any instant, for example just before a mis-applied retroactive deal change re-opened months of postings. The recovered copy is compared with the bitemporal history and the correcting entries are posted from there.

Testing the rules. Deterministic simulation testing runs the whole settlement path (EDI arrival, rule firing, posting, duplicate screening, retries, worker crashes) in one thread under a seeded scheduler with simulated clock and storage. A rare ordering, such as a deduction arriving before its shipment or the same file sent twice while a worker restarts, then replays exactly from its seed, which is how double posting and totals drift are caught.

Data and control flow

Planning one promotion:

1. A planner picks customer, products, dates and mechanic in the planning cube; the deal draft gets an id and a version.
2. The baseline for each affected cell is read from the forecast; the uplift model predicts volume, cost and ROI; pull-forward and cannibalisation terms adjust neighbours.
3. Totals roll up the hierarchy; if the planner overrides a total, write-back spreads it down and rounding keeps integers exact.
4. The optimiser may suggest a better schedule under the budget.
5. Approval runs (dual control above thresholds); the approved version is frozen and published to the rule engine and the customer.

Settling it:

1. Shipments and sell-out arrive (EDI or portal files); the rule engine decides what accrues; postings go into the ledger.
2. Retailer invoices, credit notes and deductions arrive; control numbers screen duplicates.
3. Each deduction is linked to a deal by reference or by record linkage; matches are reconciled with the ledger.
4. Valid claims are paid or offset, invalid ones disputed, and accruals released when the claim window closes.
5. Post-event analysis compares actual volume with the baseline and writes actuals back as history for the forecast.

Scaling

1. Cube edits bite first: write-back across a wide hierarchy touches many leaf cells. Levers: partial aggregates per bucket so only the edit's path is recomputed, columnar storage, batched write-down.
2. Forecasting volume: millions of series per cycle. Levers: simple per-series models where they win, a global model for related series, a weekly batch, refits only for series whose actuals changed.
3. Rule matching: deals times shipments grows fast at a large customer. Levers: the Rete network's caching, bucketing deals by customer and product, batching shipments.
4. Deduction backlog: matching is manual at the tail. Tune the match threshold so the human queue holds only genuinely ambiguous items; measure queue age, not only volume.
5. Retroactive changes: a changed deal can re-open months of postings; bitemporal storage re-computes the delta and posts only the difference.

Failure modes

Real implementations

Sources

• R. Hyndman and G. Athanasopoulos, Forecasting: Principles and Practice, 3rd ed.: baselines, exponential smoothing, rolling-origin evaluation. book
• D. Salinas et al., DeepAR: Probabilistic Forecasting with Autoregressive Recurrent Networks, 2017. paper
• B. Oreshkin et al., N-BEATS: Neural basis expansion analysis for interpretable time series forecasting, 2019. paper
• B. Lim et al., Temporal Fusion Transformers for Interpretable Multi-horizon Time Series Forecasting, 2019. paper
• P. Rosenbaum and D. Rubin, The central role of the propensity score in observational studies for causal effects, Biometrika 70(1), 1983.
• I. Fellegi and A. Sunter, A theory for record linkage, Journal of the American Statistical Association 64, 1969.
• ASC X12, the standards body for the EDI transaction sets named above: x12.org